With resultSet.getMetaData().getColumnTypeName(...) I get that CITY is a
VARCHAR datatype:
| EMPNO(INTEGER) | NAME(VARCHAR) | DEPTNO(INTEGER) | GENDER(VARCHAR) |
CITY(VARCHAR) | EMPID(INTEGER) | AGE(INTEGER) | SLACKER(BOOLEAN) |
MANAGER(BOOLEAN) | JOINEDAT(DATE) |
where CITY =' ' or CITY =' ' or CITY=CAST(' ' AS VARCHAR(1)) or CITY=CAST(''
AS VARCHAR(1)) gives the same results.
I have not tried with postgres but I have tested with hsqldb and mysql (with
JDBC adapter), the queries were pushed down to the dbs and it seems identifier
and literal are both trimmed prior comparison:
SQL: SELECT * from "t1"
| I1(INTEGER) | V1(VARCHAR(30)) | V2(VARCHAR(30)) |
|_|0|_|null value|_|null|_|
|_|1|_|a|_|aa|_|
|_|2|_|noBlank|_||_|
|_|3|_|oneBlank|_| |_|
|_|4|_|ltrOneBlank|_|aa |_|
SQL: SELECT * from "t1" where v2 = ''
| I1(INTEGER) | V1(VARCHAR(30)) | V2(VARCHAR(30)) |
|_|2|_|noBlank|_||_|
|_|3|_|oneBlank|_| |_|
I also observed that the blank string literal is trimmed after converting
SqlNode to RelNode:
Jun 12, 2015 3:32:39 PM org.apache.calcite.sql.validate.SqlValidatorImpl
validateScopedExpression
FINER: After validation: SELECT `EMPS`.`NAME`, `EMPS`.`CITY`
FROM `SALES`.`EMPS` AS `EMPS`
WHERE `EMPS`.`CITY` = ' '
Jun 12, 2015 3:32:39 PM org.apache.calcite.sql2rel.SqlToRelConverter
convertQuery
FINE: Plan after converting SqlNode to RelNode
LogicalProject(NAME=[$1], CITY=[$4])
LogicalFilter(condition=[=(CAST($4):VARCHAR(1) CHARACTER SET "ISO-8859-1"
COLLATE "ISO-8859-1$en_US$primary", '')])
LogicalTableScan(table=[[SALES, EMPS]])
Jun 12, 2015 3:26:42 PM org.apache.calcite.sql.validate.SqlValidatorImpl
validateScopedExpression
FINER: After validation: SELECT `EMPS`.`NAME`, `EMPS`.`CITY`
FROM `SALES`.`EMPS` AS `EMPS`
WHERE `EMPS`.`CITY` = CAST(' ' AS VARCHAR(1))
Jun 12, 2015 3:26:42 PM org.apache.calcite.sql2rel.SqlToRelConverter
convertQuery
FINE: Plan after converting SqlNode to RelNode
LogicalProject(NAME=[$1], CITY=[$4])
LogicalFilter(condition=[=(CAST($4):VARCHAR(1) CHARACTER SET "ISO-8859-1"
COLLATE "ISO-8859-1$en_US$primary", '')])
LogicalTableScan(table=[[SALES, EMPS]])
Jun 12, 2015 3:21:49 PM org.apache.calcite.sql.validate.SqlValidatorImpl
validateScopedExpression
FINER: After validation: SELECT `T1`.`I1`, `T1`.`V1`, `T1`.`V2`
FROM `BASEJDBC`.`T1` AS `T1`
WHERE `T1`.`V2` = ' '
Jun 12, 2015 3:21:49 PM org.apache.calcite.sql2rel.SqlToRelConverter
convertQuery
FINE: Plan after converting SqlNode to RelNode
LogicalProject(I1=[$0], V1=[$1], V2=[$2])
LogicalFilter(condition=[=($2, '')])
JdbcTableScan(table=[[BASEJDBC, T1]])
Thanks & Regards,
Yuri Au Yong
Software Engineer - NPM | [email protected] | Desk: +60 3 7663 8372
Persistent Systems Ltd. | Partners in Innovation | www.persistentsys.com
-----Original Message-----
From: Julian Hyde [mailto:[email protected]]
Sent: Thursday, June 11, 2015 1:37 AM
To: [email protected]
Subject: Re: Comparing untrimmed Identifier to string literal with trimmed
trailing space
I think it may be due to the fact that character literals are of type CHAR, not
VARCHAR, in compliance with the SQL standard. When CHAR values are compared
trailing spaces are ignored.
Can you try some experiments? Try
WHERE city = CAST(‘ ‘ AS VARCHAR(1))
etc. Also see whether it is caused by the CSV adapter by running t he query
against ‘VALUES (100, ‘Fred’, …), …, (1, ‘oneblank’, 1, …)’.
Does Postgres have the same behavior?
Julian
> On Jun 10, 2015, at 10:24 AM, Yuri Au Yong <[email protected]> wrote:
>
> When querying with a comparison operator predicate that compares an
> identifier to a string literal in sqlline , I notice that the trailing space
> of the string literal is trimmed prior comparison. The trailing space of the
> identifier however was not trimmed prior comparison which causes inaccurate
> results (see example below). Any pointers on where can I add the trailing
> space trim for the identifier before comparison?
>
> Example:
> I have edited the EMPS.cvs with additional rows, “noblank” and “oneblank”:
>
> EMPNO:int,NAME:string,DEPTNO:int,GENDER:string,CITY:string,EMPID:int,A
> GE:int,SLACKER:boolean,MANAGER:boolean,JOINEDAT:date
> 100,"Fred",10,,,30,25,true,false,"1996-08-03"
> 110,"Eric",20,"M","San Francisco",3,80,,false,"2001-01-01"
> 110,"John",40,"M","Vancouver",2,,false,true,"2002-05-03"
> 120,"Wilma",20,"F",,1,5,,true,"2005-09-07"
> 130,"Alice",40,"F","Vancouver",2,,false,true,"2007-01-01"
> 0,"noblank",0,"","",0,0,false,false,"2015-06-09"
> 1,"oneblank",1," "," ",1,1,false,false,"2015-06-09"
>
> #where CITY ='' or CITY =' '
> 0: jdbc:calcite:model=target/test-classes/mod> select NAME, CITY from
> EMPS where CITY ='';
> +------+------+
> | NAME | CITY |
> +------+------+
> | Fred | |
> | Wilma | |
> | noblank | |
> +------+------+
> 3 rows selected (3.002 seconds)
>
> #where CITY <>' ' or CITY <>' '
> 0: jdbc:calcite:model=target/test-classes/mod> select NAME, CITY from
> EMPS where CITY <>' ';
> +------+------+
> | NAME | CITY |
> +------+------+
> | Eric | San Francisco |
> | John | Vancouver |
> | Alice | Vancouver |
> | oneblank | |
> +------+------+
> 4 rows selected (0.304 seconds)
>
>
> Thanks & Regards,
>
> Yuri Au Yong
> Software Engineer - NPM | [email protected]
> <mailto:[email protected]> | Desk: +60 3 7663 8372 Persistent
> Systems Ltd. | Partners in Innovation | www.persistentsys.com
> <http://www.persistentsys.com/>
> <https://tnpmsupport.persistentsys.com/training>
>
> DISCLAIMER ========== This e-mail may contain privileged and confidential
> information which is the property of Persistent Systems Ltd. It is intended
> only for the use of the individual or entity to which it is addressed. If you
> are not the intended recipient, you are not authorized to read, retain, copy,
> print, distribute or use this message. If you have received this
> communication in error, please notify the sender and delete all copies of
> this message. Persistent Systems Ltd. does not accept any liability for virus
> infected mails.
>
DISCLAIMER
==========
This e-mail may contain privileged and confidential information which is the
property of Persistent Systems Ltd. It is intended only for the use of the
individual or entity to which it is addressed. If you are not the intended
recipient, you are not authorized to read, retain, copy, print, distribute or
use this message. If you have received this communication in error, please
notify the sender and delete all copies of this message. Persistent Systems
Ltd. does not accept any liability for virus infected mails.
