[ https://issues.apache.org/jira/browse/CALCITE-3772?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17039846#comment-17039846 ]
Feng Zhu commented on CALCITE-3772: ----------------------------------- If we remove the predicate (e>100), the query runs well. There may be some issues when decorrelating the correlated-subquery. The plan generated is not correct. > Query returning bad results > --------------------------- > > Key: CALCITE-3772 > URL: https://issues.apache.org/jira/browse/CALCITE-3772 > Project: Calcite > Issue Type: Bug > Affects Versions: 1.21.0, next > Environment: [^T1.csv] > Reporter: Jacob Roldan > Priority: Critical > Attachments: T1.csv > > > Inspired in tests of sqllite, I have a query that it is returning bad values. > I've tested using CsvTest in 1.21.0 and the last master > > {code:sql} > SELECT a, (SELECT count(*) FROM t1 AS x WHERE x.b<t1.b) > FROM t1 > WHERE (e>100) > order by a > {code} > > and the result is: > {code:java} > 104, 1 > 107, 2 > 111, 2 > 115, 3 > 121, 4 {code} > Testing the same query in mysql, derby and postgres, the result is: > {code:java} > 104 0 > 107 1 > 111 2 > 115 3 > 121 4{code} > I've attached the csv file I've put in > ??calcite/example/csv/src/test/resources/bug/T1.csv?? > and the query test in CsvTest.java > {code:java} > @Test public void testQuery() throws SQLException { > sql("bug", "SELECT a, (SELECT count(*) FROM t1 AS x WHERE x.b<t1.b) FROM t1 > where e>100 order by a").ok(); > } > {code} > The explain plan: > {code:java} > EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], > expr#5=[0:BIGINT], expr#6=[CASE($t4, $t5, $t3)], A=[$t0], EXPR$1=[$t6]) > EnumerableHashJoin(condition=[=($1, $2)], joinType=[left]) > EnumerableCalc(expr#0..4=[{inputs}], A=[$t0], E=[$t4]) > EnumerableSort(sort0=[$0], dir0=[ASC]) > EnumerableCalc(expr#0..4=[{inputs}], expr#5=[100], expr#6=[>($t4, > $t5)], proj#0..4=[{exprs}], $condition=[$t6]) > EnumerableInterpreter > BindableTableScan(table=[[BUG, T1]]) > EnumerableAggregate(group=[{1}], EXPR$0=[COUNT()]) > EnumerableNestedLoopJoin(condition=[<($0, $1)], joinType=[inner]) > EnumerableCalc(expr#0..4=[{inputs}], B=[$t1]) > EnumerableInterpreter > BindableTableScan(table=[[BUG, T1]]) > EnumerableAggregate(group=[{4}]) > EnumerableCalc(expr#0..4=[{inputs}], expr#5=[100], expr#6=[>($t4, > $t5)], proj#0..4=[{exprs}], $condition=[$t6]) > EnumerableInterpreter > BindableTableScan(table=[[BUG, T1]]) > {code} > The T1.csv is very simple: > {code:java} > A:int,B:int,C:int,D:int,E:int > 104,100,102,101,103 > 107,105,106,108,109 > 111,112,113,114,110 > 115,118,119,116,117 > 121,124,123,122,120 > {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)