[CALCITE-762] Trailing spaces on character literals

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Invalid
• Affects Version/s: 1.3.0-incubating
• Fix Version/s: 1.4.0-incubating

Environment

linux

• Assignee: Julian Hyde
• Reporter: Yuri Au Yong
• Created: 20/Jun/15 03:58
• Updated: 24/May/21 19:59
• Resolved: 10/Jul/15 03:42

Description

When comparing an identifier to a string literal with a comparison operator predicate, 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.

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 TestSchemaSpec.java 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|_|

Generated Code:

SQL: select * from test.t1 where v2 = 'aa '

/*   1 */ org.apache.calcite.DataContext root;
/*   2 */ 
/*   3 */ public org.apache.calcite.linq4j.Enumerable bind(final org.apache.calcite.DataContext root0) {
/*   4 */   root = root0;
/*   5 */   final org.apache.calcite.linq4j.Enumerable _inputEnumerable = org.apache.calcite.linq4j.Linq4j.asEnumerable(((org.apache.calcite.reflective.TestSchemaSpec.TestSchema) ((org.apache.calcite.adapter.java.ReflectiveSchema) root.getRootSchema().getSubSchema("TEST").unwrap(org.apache.calcite.adapter.java.ReflectiveSchema.class)).getTarget()).T1);
/*   6 */   return new org.apache.calcite.linq4j.AbstractEnumerable(){
/*   7 */       public org.apache.calcite.linq4j.Enumerator enumerator() {
/*   8 */         return new org.apache.calcite.linq4j.Enumerator(){
/*   9 */             public final org.apache.calcite.linq4j.Enumerator inputEnumerator = _inputEnumerable.enumerator();
/*  10 */             public void reset() {
/*  11 */               inputEnumerator.reset();
/*  12 */             }
/*  13 */ 
/*  14 */             public boolean moveNext() {
/*  15 */               while (inputEnumerator.moveNext()) {
/*  16 */                 final String inp2_ = ((org.apache.calcite.reflective.TestSchemaSpec.Table1) inputEnumerator.current()).V2;
/*  17 */                 if (inp2_ != null && org.apache.calcite.runtime.SqlFunctions.eq(inp2_, "aa")) {
/*  18 */                   return true;
/*  19 */                 }
/*  20 */               }
/*  21 */               return false;
/*  22 */             }
/*  23 */ 
/*  24 */             public void close() {
/*  25 */               inputEnumerator.close();
/*  26 */             }
/*  27 */ 
/*  28 */             public Object current() {
/*  29 */               final org.apache.calcite.reflective.TestSchemaSpec.Table1 current = (org.apache.calcite.reflective.TestSchemaSpec.Table1) inputEnumerator.current();
/*  30 */               return new Object[] {
/*  31 */                   current.ID,
/*  32 */                   current.V1,
/*  33 */                   current.V2};
/*  34 */             }
/*  35 */ 
/*  36 */           };
/*  37 */       }
/*  38 */ 
/*  39 */     };
/*  40 */ }
/*  41 */ 
/*  42 */ 
/*  43 */ public Class getElementType() {
/*  44 */   return java.lang.Object[].class;
/*  45 */ }
/*  46 */ 
/*  47 */ 

Issue Links

• relates to: CALCITE-4590 Incorrect query result with fixed-length string