drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kunal Khatua <kkha...@mapr.com>
Subject RE: Apache DRILL v1.11.0 handling Postgres citext columns with Inconsistency
Date Mon, 18 Sep 2017 18:14:29 GMT
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:mbisignani@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
Mime
View raw message