[
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
[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}
was:
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
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}
> 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
> 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)
