Please log a jira case and I'll take a look. On Thu, Jun 18, 2015 at 11:04 PM, Yuri Au Yong <[email protected]> wrote:
> Hi Julian, > > Query through JdbcAdapter is fine as the query is pushed down and handled > by databases. > > However, the issue is when the queries are resolved in calcite. Attached > is a reflective schema test case which prints the results of queries below > where both V1 and V2 columns are VARCHARs. > > SQL: select * from test.t1 > | ID(INTEGER) | V1(VARCHAR) | V2(VARCHAR) | > |_|0|_|null value|_|null|_| > |_|1|_|a|_|aa|_| > |_|2|_|noBlank|_||_| > |_|3|_|oneBlank|_| |_| > |_|4|_|ltrOneBlank|_|aa |_| > > ##These queries should return rows ID=2 and ID=3 > SQL: select * from test.t1 where v2 = '' > | ID(INTEGER) | V1(VARCHAR) | V2(VARCHAR) | > |_|2|_|noBlank|_||_| > SQL: select * from test.t1 where v2 = ' ' > | ID(INTEGER) | V1(VARCHAR) | V2(VARCHAR) | > |_|2|_|noBlank|_||_| > > ## These queries should return rows ID=1 and ID=4 > SQL: select * from test.t1 where v2 = 'aa' > | ID(INTEGER) | V1(VARCHAR) | V2(VARCHAR) | > |_|1|_|a|_|aa|_| > SQL: select * from test.t1 where v2 = 'aa ' > | ID(INTEGER) | V1(VARCHAR) | V2(VARCHAR) | > |_|1|_|a|_|aa|_| > > > 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: Wednesday, June 17, 2015 4:14 AM > To: [email protected] > Subject: Re: Comparing untrimmed Identifier to string literal with trimmed > trailing space > > It sounds as if we are good, then. > > A related issue was fixed in > https://github.com/apache/incubator-calcite/commit/8d20f71. > > And if you do > > SELECT CAST(x AS VARCHAR(10)) FROM VALUES 'xyz', 'abcde' AS t(x) > > Calcite has to convert the CHAR(3) and CHAR(5) literals to VARCHAR(3) and > VARCHAR(5) before it converts them to VARCHAR(10). If it converted them > both to CHAR(5), 'xyz' would end up with two trailing spaces. > > Julian > > > > On Jun 12, 2015, at 12:40 AM, Yuri Au Yong <[email protected]> > wrote: > > > 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. > > > 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. > >
