It's odd that adding just a term_count column is causing an error but the other 2 columns (created, updated) don't seem to be... and gets resolved on removing the cast.
Can you provide the stack trace and error message? Also, what are the data types for the other columns? -----Original Message----- From: Mick Bisignani [mailto:mbisign...@gmail.com] Sent: Sunday, September 17, 2017 6:00 PM To: user@drill.apache.org Subject: Apache DRILL v1.11.0 handling Postgres citext columns with Inconsistency *Hi Everyone, I have found that it is necessary to use a CAST() operation when selecting from tables that contain citext in postgres tables.* *i have recently found that the following inconsistency / error * SELECT CAST(tb1.id AS INTEGER) AS id, CAST(tb1.term AS VARCHAR) as term, tb1.term as term1, tb1.created_at as created_at , tb1.updated_at as updated_at FROM my_postgres.public.p_keywords tb1 ; *note*: column term is defined as CITEXT SELECT returns the following +-------+---------------------------------+-------+--------------------------+--------------------------+ | id | term | term1 | created_at | updated_at | +-------+---------------------------------+-------+--------------------------+--------------------------+ | 1300 | tall herringbone shirt | null | 2017-08-29 09:11:39.261 | 2017-08-29 09:11:39.261 | | 1301 | short sleeve herringbone shirt | null | 2017-08-29 | 09:11:39.267 | 2017-08-29 09:11:39.267 | | 1302 | slim fit herringbone shirt | null | 2017-08-29 09:11:39.274 | 2017-08-29 09:11:39.274 | | 1303 | leather leggings | null | 2017-08-29 09:11:39.28 | 2017-08-29 09:11:39.28 | | 1304 | faux leather leggings | null | 2017-08-29 09:11:39.287 | 2017-08-29 09:11:39.287 | | 1305 | string bikini bottom | null | 2017-08-29 09:11:39.293 | 2017-08-29 09:11:39.293 | | 1306 | drawstring bikini bottom | null | 2017-08-29 09:11:39.299 | 2017-08-29 09:11:39.299 | | 1307 | dress shoes | null | 2017-08-29 09:11:39.306 | 2017-08-29 09:11:39.306 | | 1308 | lace up dress shoes | null | 2017-08-29 09:11:39.312 | 2017-08-29 09:11:39.312 | | 1309 | bowl pendant | null | 2017-08-29 09:11:39.319 | 2017-08-29 09:11:39.319 | | 1310 | shoe cabinet | null | 2017-08-29 09:11:39.325 | 2017-08-29 09:11:39.325 | | 1311 | shawl collar | null | 2017-08-29 09:11:39.331 | 2017-08-29 09:11:39.331 | +-------+---------------------------------+-------+--------------------------+--------------------------+ term1 is null as expected due to the lack of a specific CAST() operation on the column When I add another column (term_count) to the select statement, the first CAST also fails SELECT CAST(tb1.id AS INTEGER) AS id, CAST(tb1.term AS VARCHAR) as term, tb1.term as term1, tb1.term_count as term_count, tb1.created_at as created_at , tb1.updated_at as updated_at FROM my_postgres.public.p_keywords tb1 ; CAST(tb1.term AS VARCHAR) not being applied. +-------+-------+-------+------------+--------------------------+--------------------------+ | id | term | term1 | term_count | created_at | updated_at | +-------+-------+-------+------------+--------------------------+--------------------------+ | 1300 | null | null | 3 | 2017-08-29 09:11:39.261 | 2017-08-29 09:11:39.261 | | 1301 | null | null | 4 | 2017-08-29 09:11:39.267 | 2017-08-29 09:11:39.267 | | 1302 | null | null | 4 | 2017-08-29 09:11:39.274 | 2017-08-29 09:11:39.274 | | 1303 | null | null | 2 | 2017-08-29 09:11:39.28 | 2017-08-29 09:11:39.28 | | 1304 | null | null | 3 | 2017-08-29 09:11:39.287 | 2017-08-29 09:11:39.287 | | 1305 | null | null | 3 | 2017-08-29 09:11:39.293 | 2017-08-29 09:11:39.293 | | 1306 | null | null | 3 | 2017-08-29 09:11:39.299 | 2017-08-29 09:11:39.299 | | 1307 | null | null | 2 | 2017-08-29 09:11:39.306 | 2017-08-29 09:11:39.306 | | 1308 | null | null | 4 | 2017-08-29 09:11:39.312 | 2017-08-29 09:11:39.312 | | 1309 | null | null | 2 | 2017-08-29 09:11:39.319 | 2017-08-29 09:11:39.319 | | 1310 | null | null | 2 | 2017-08-29 09:11:39.325 | 2017-08-29 09:11:39.325 | | 1311 | null | null | 2 | 2017-08-29 09:11:39.331 | 2017-08-29 09:11:39.331 | +-------+-------+-------+------------+--------------------------+--------------------------+ is this a bug in version 1.11.0 ? Postgres is PostgreSQL 9.6.3 using jdbc driver version 42.1.4 Thanks mb
