Kunal , no errors generated or is a stack generated when I run the commands. ..Just getting "null" for column term when it does not work.
just to recap , here is what I see # CITEXT must be CAST SELECT CAST(tb1.id AS INTEGER) AS id, tb1.term as term1, CAST(tb1.term AS VARCHAR) as term FROM my_postgres.public.p_keywords tb1 ; term1 is NULL term is OK # works SELECT CAST(tb1.id AS INTEGER) AS id, CAST(tb1.term AS VARCHAR) as term, tb1.created_at as created_at , tb1.updated_at as updated_at, tb1.term_count as term_count FROM my_postgres.public.p_keywords tb1 ; term is OK # works but does not contain CITEXT column SELECT CAST(tb1.id AS INTEGER) AS id, tb1.created_at as created_at , tb1.updated_at as updated_at, tb1.term_count as term_count, tb1.occurance as occurance, tb1.top_cats as top_cats, tb1.cats_by_priority as cats_by_priority FROM my_postgres.public.p_keywords tb1 ; # does not work - just added more columns beyond column term_count SELECT CAST(tb1.id AS INTEGER) AS id, CAST(tb1.term AS VARCHAR) as term, tb1.created_at as created_at , tb1.updated_at as updated_at, tb1.term_count as term_count, tb1.occurance as occurance, tb1.top_cats as top_cats, tb1.cats_by_priority as cats_by_priority FROM my_postgres.public.p_keywords tb1 ; term returns "NULL" structure of my_postgres.public.p_keywords table several columns omitted Column | Type | Modifiers | Storage | Stats target | Description -------------------+-----------------------------+---------------------------------------------------------------+----------+--------------+------------- id | integer | not null default nextval('product_keywords_id_seq'::regclass) | plain | | term | citext | | extended | | term_count | integer | | plain | | occurance | integer | | plain | | top_cats | character varying | | extended | | cats_by_priority | character varying | | extended | | created_at | timestamp without time zone | not null | plain | | updated_at | timestamp without time zone | not null | plain | | Drill Version 1.11.0 ? Postgres is .6.3 using jdbc driver version 42.1.4 Cheers On Mon, Sep 18, 2017 at 2:14 PM, Kunal Khatua <kkha...@mapr.com> wrote: > 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 >