You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Yuri Au Yong (JIRA)" <ji...@apache.org> on 2015/06/20 06:11:00 UTC
[jira] [Updated] (CALCITE-762) Comparing untrimmed Identifier to
string literal with trimmed trailing space
[ https://issues.apache.org/jira/browse/CALCITE-762?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Yuri Au Yong updated CALCITE-762:
---------------------------------
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 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}
> Comparing untrimmed Identifier to string literal with trimmed trailing space
> ----------------------------------------------------------------------------
>
> 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
>
> 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 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)