[jira] [Commented] (CALCITE-762) Trailing spaces on character literals

["Julian Hyde (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-762?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14600461#comment-14600461 ] 

Julian Hyde commented on CALCITE-762:
-------------------------------------

Per the email thread http://postgresql.nabble.com/String-comparison-and-the-SQL-standard-td5740721.html "String comparison and the SQL standard", Postgres deviates from the standard in two significant ways: character literals are of type UNKNOWN not CHAR;  'a'='a   ' returns FALSE.

So, it seems that Calcite is already in compliance with the standard.

> Trailing spaces on character literals
> -------------------------------------
>
>                 Key: CALCITE-762
>                 URL: https://issues.apache.org/jira/browse/CALCITE-762
>             Project: Calcite
>          Issue Type: Bug
>    Affects Versions: 1.3.0-incubating
>         Environment: linux
>            Reporter: Yuri Au Yong
>            Assignee: Julian Hyde
>         Attachments: TestSchemaSpec.java
>
>
> 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|https://issues.apache.org/jira/secure/attachment/12740795/TestSchemaSpec.java] is a reflective schema test case which prints the results of queries below where both V1 and V2 columns are VARCHARs.
> {noformat}
> 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|_|
> {noformat}
> h4. Generated Code:
> {noformat}
> 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 */ 
> {noformat}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)