drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mick Bisignani <mbisign...@gmail.com>
Subject Apache DRILL v1.11.0 handling Postgres citext columns with Inconsistency
Date Mon, 18 Sep 2017 00:59:55 GMT
*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
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message