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,AGE: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/> [sign1]<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.
