Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-762

Trailing spaces on character literals

    XMLWordPrintableJSON

    Details

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

      linux

      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 */ 
      

        Attachments

        1. TestSchemaSpec.java
          4 kB
          Yuri Au Yong
        2. CALCITE-762.1.patch
          10 kB
          Julian Hyde

          Activity

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              YAY Yuri Au Yong
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: