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
>
