Jacob Roldan created CALCITE-3772: ------------------------------------- Summary: 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 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)