ODBC Tableau null dates

[eabuchan]

Using ODBC driver, a date dimension in Tableau with null values gives the below error. ClickHouse server version: 20.1.4.14 Tableau Server Version: 2019.1.3

An error occurred while communicating with the Other Databases (ODBC) data source.

Bad Connection: Tableau could not connect to the data source. HTTP status code: 500 Received error: Code: 349, e.displayText() = DB::Exception: Cannot convert NULL value to non-Nullable type (version 20.1.4.14 (official build))

SELECT CAST({fn TRUNCATE(EXTRACT(YEAR FROM CAST(`warehouse_qa`.`qapatient_dob` AS DATE)),0)} AS INTEGER) AS `yr_qapatient_dob_ok`
FROM `warehouse_qa`
GROUP BY `yr_qapatient_dob_ok`

ClickHouse Table:

CREATE TABLE warehouse_qa (
	  id String
	, _offset UInt64
	, zero Nullable(UInt8)
	, encounter_month Nullable(String)
	, encounter_month_year Nullable(UInt16)
	, encounter_month_quarter Nullable(UInt8)
	, encounter_month_quarter_name Nullable(String)
	, encounter_month_number Nullable(UInt8)
	, encounter_month_name Nullable(String)
	, counter Nullable(UInt16)
	, back_pain Nullable(UInt8)
	, age Nullable(UInt8)
	, qasite_id Nullable(String)
	, qasite_name Nullable(String)
	, qasite_state Nullable(String)
	, qaencounter_id Nullable(String)
	, qaencounter_time Nullable(DateTime)
	, qapatient_id Nullable(String)
	, qapatient_latino Nullable(UInt8)
	, qapatient_dob Nullable(String)
	, qapatient_dob_year Nullable(UInt16)
	, qapatient_dob_quarter Nullable(UInt8)
	, qapatient_dob_quarter_name Nullable(String)
	, qapatient_dob_month Nullable(UInt8)
	, qapatient_dob_month_name Nullable(String)
	, qapatient_dob_day Nullable(UInt8)
	, qapatient_dob_day_name Nullable(String)
	, qapatient_dob_day_of_week Nullable(UInt8)
	, qapatient_dob_day_of_year Nullable(UInt16)
	, qapatient_sex Nullable(String)
)
ENGINE = ReplacingMergeTree(_offset)
ORDER BY (id)

[traceon]

Related to #236

[traceon]

Could you please check with the recent version of the driver? https://github.com/ClickHouse/clickhouse-odbc/releases/tag/v1.1.8.20200710

[eabuchan]

@traceon I tried updating the driver, but I'm still getting the same error. I did not update the server, is a server upgrade required?

[eabuchan]

@traceon Currently on 20.1.13.105

[traceon]

@eabuchan could you please send the SQL query and the HTTP request from ODBC driver, as logged at server side. You should see the log entry right before the error.

[eabuchan]

@traceon

2020.07.14 18:14:27.142531 [ 49 ] {7f098e6a-72ee-436c-96ff-abb8ca8018fb} <Error> executeQuery: Code: 349, e.displayText() = DB::Exception: Cannot convert NULL value to non-Nullable type (version 20.1.13.105 (official build)) (from 127.0.0.1:36630) (in query: SELECT CAST(trunc(EXTRACT(YEAR FROM CAST(`warehouse_qatest`.`qapatient_dob` AS DATE)),0) AS INTEGER) AS `yr_qapatient_dob_ok` FROM `warehouse_qatest` GROUP BY `yr_qapatient_dob_ok`), Stack trace (when copying this message, always include the lines below):
0. 0x102bb400 Poco::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int)  in /usr/bin/clickhouse
1. 0x8e885cd DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int)  in /usr/bin/clickhouse
2. 0x9167a64 std::__1::__function::__func<DB::FunctionCast::prepareRemoveNullable(std::__1::shared_ptr<DB::IDataType const> const&, std::__1::shared_ptr<DB::IDataType const> const&, bool) const::'lambda0'(DB::Block&, std::__1::vector<unsigned long, std::__1::allocator<unsigned long> > const&, unsigned long, unsigned long), std::__1::allocator<DB::FunctionCast::prepareRemoveNullable(std::__1::shared_ptr<DB::IDataType const> const&, std::__1::shared_ptr<DB::IDataType const> const&, bool) const::'lambda0'(DB::Block&, std::__1::vector<unsigned long, std::__1::allocator<unsigned long> > const&, unsigned long, unsigned long)>, void (DB::Block&, std::__1::vector<unsigned long, std::__1::allocator<unsigned long> > const&, unsigned long, unsigned long)>::operator()(DB::Block&, std::__1::vector<unsigned long, std::__1::allocator<unsigned long> > const&, unsigned long&&, unsigned long&&)  in /usr/bin/clickhouse
3. 0x91664e3 DB::ExecutableFunctionCast::execute(DB::Block&, std::__1::vector<unsigned long, std::__1::allocator<unsigned long> > const&, unsigned long, unsigned long)  in /usr/bin/clickhouse
4. 0x911c082 DB::ExecutableFunctionAdaptor::execute(DB::Block&, std::__1::vector<unsigned long, std::__1::allocator<unsigned long> > const&, unsigned long, unsigned long, bool)  in /usr/bin/clickhouse
5. 0xcf6ac0d DB::ExpressionAction::execute(DB::Block&, bool) const  in /usr/bin/clickhouse
6. 0xcf6c68d DB::ExpressionActions::execute(DB::Block&, bool) const  in /usr/bin/clickhouse
7. 0xd175d2c DB::ExpressionBlockInputStream::readImpl()  in /usr/bin/clickhouse
8. 0xcd552df DB::IBlockInputStream::read()  in /usr/bin/clickhouse
9. 0xd045fcd DB::ConcatBlockInputStream::readImpl()  in /usr/bin/clickhouse
10. 0xcd552df DB::IBlockInputStream::read()  in /usr/bin/clickhouse
11. 0xd08f996 DB::Aggregator::execute(std::__1::shared_ptr<DB::IBlockInputStream> const&, DB::AggregatedDataVariants&)  in /usr/bin/clickhouse
12. 0xd1fd9c7 DB::AggregatingBlockInputStream::readImpl()  in /usr/bin/clickhouse
13. 0xcd552df DB::IBlockInputStream::read()  in /usr/bin/clickhouse
14. 0xd175d0c DB::ExpressionBlockInputStream::readImpl()  in /usr/bin/clickhouse
15. 0xcd552df DB::IBlockInputStream::read()  in /usr/bin/clickhouse
16. 0xd175d0c DB::ExpressionBlockInputStream::readImpl()  in /usr/bin/clickhouse
17. 0xcd552df DB::IBlockInputStream::read()  in /usr/bin/clickhouse
18. 0xcd948a3 DB::copyData(DB::IBlockInputStream&, DB::IBlockOutputStream&, std::__1::atomic<bool>*)  in /usr/bin/clickhouse
19. 0xd41567f DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, DB::Context&, std::__1::function<void (std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&)>, std::__1::function<void (std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&)>)  in /usr/bin/clickhouse
20. 0x8f28b0f DB::HTTPHandler::processQuery(Poco::Net::HTTPServerRequest&, HTMLForm&, Poco::Net::HTTPServerResponse&, DB::HTTPHandler::Output&)  in /usr/bin/clickhouse
21. 0x8f2cba3 DB::HTTPHandler::handleRequest(Poco::Net::HTTPServerRequest&, Poco::Net::HTTPServerResponse&)  in /usr/bin/clickhouse
22. 0xe151404 Poco::Net::HTTPServerConnection::run()  in /usr/bin/clickhouse
23. 0xe1813eb Poco::Net::TCPServerConnection::start()  in /usr/bin/clickhouse
24. 0xe18186d Poco::Net::TCPServerDispatcher::run()  in /usr/bin/clickhouse
25. 0x10349567 Poco::PooledThread::run()  in /usr/bin/clickhouse
26. 0x1034536c Poco::ThreadImpl::runnableEntry(void*)  in /usr/bin/clickhouse
27. 0x10346d0d ?  in /usr/bin/clickhouse
28. 0x76db start_thread  in /lib/x86_64-linux-gnu/libpthread-2.27.so
29. 0x12188f __clone  in /lib/x86_64-linux-gnu/libc-2.27.so

[traceon]

@eabuchan Several messages right before this one should contain the HTTP request, with the SQL in its body, can you post those too?

[eabuchan]

@traceon I am not seeing the HTTP request body in the logs, but the SQL is in the above post:

SELECT CAST(trunc(EXTRACT(YEAR FROM CAST(`warehouse_qatest`.`qapatient_dob` AS DATE)),0) AS INTEGER) AS `yr_qapatient_dob_ok` FROM `warehouse_qatest` GROUP BY `yr_qapatient_dob_ok`)

2020.07.14 18:14:27.140809 [ 49 ] {7f098e6a-72ee-436c-96ff-abb8ca8018fb} <Debug> executeQuery: (from 127.0.0.1:36630, user: lbuchanan) SELECT CAST(trunc(EXTRACT(YEAR FROM CAST(`warehouse_qatest`.`qapatient_dob` AS DATE)),0) AS INTEGER) AS `yr_qapatient_dob_ok` FROM `warehouse_qatest` GROUP BY `yr_qapatient_dob_ok`
2020.07.14 18:14:27.141648 [ 49 ] {7f098e6a-72ee-436c-96ff-abb8ca8018fb} <Debug> amx_dev_arx.qatest_1594319250606 (SelectExecutor): Key condition: unknown
2020.07.14 18:14:27.141687 [ 49 ] {7f098e6a-72ee-436c-96ff-abb8ca8018fb} <Debug> amx_dev_arx.qatest_1594319250606 (SelectExecutor): Selected 1 parts by date, 1 parts by key, 1 marks to read from 1 ranges
2020.07.14 18:14:27.141902 [ 49 ] {7f098e6a-72ee-436c-96ff-abb8ca8018fb} <Debug> executeQuery: Query pipeline:
Expression
 Expression
  Aggregating
   Concat
    Expression
     Materializing
      Expression
       Expression
        MergeTreeThread

[traceon]

So maybe qapatient_dob value is NULL in CAST(warehouse_qatest.qapatient_dob AS DATE)?

[eabuchan]

Yes qapatient_dob column contains nullable string data. In Tableau it is converted to a Date type dimension.

[traceon]

So, I don't think this is a problem introduced by the driver.

[yurifal]

Hi @eabuchan

You may want to use a literal when casting Null to Date type, something like this (Tableau calc):

// qapatient_dob as Date Type
DATE( IFNULL([qapatient_dob], #1970-01-01#) )