[ https://issues.apache.org/jira/browse/CALCITE-3772?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17040212#comment-17040212 ]
Feng Zhu commented on CALCITE-3772: ----------------------------------- It's not safe to trim fields [1] when the Plan contains correlated variables. *Before trim:* {code:java} LogicalProject(A=[$0], EXPR$1=[$SCALAR_QUERY({ LogicalAggregate(group=[{}], EXPR$0=[COUNT()]) LogicalProject($f0=[0]) LogicalFilter(condition=[<($1, $cor0.B)]) LogicalTableScan(table=[[BUG, T1]]) })]) LogicalFilter(condition=[>($4, 100)]) LogicalTableScan(table=[[BUG, T1]]) {code} *After trim:* {code:java} LogicalProject(A=[$0], EXPR$1=[$SCALAR_QUERY({ LogicalAggregate(group=[{}], EXPR$0=[COUNT()]) LogicalProject($f0=[0]) LogicalFilter(condition=[<($1, $cor0.B)]) LogicalTableScan(table=[[BUG, T1]]) })]) LogicalFilter(condition=[>($1, 100)]) LogicalProject(A=[$0], E=[$4]) LogicalTableScan(table=[[BUG, T1]]) {code} Consequently, when decoralating the plan, it produces the incorrect RelNode for _*{color:#FF0000}$cor0.B{color}*_: {code:java} LogicalAggregate(group=[{0}]) LogicalProject(E=[$4]) LogicalFilter(condition=[>($4, 100)]) LogicalTableScan(table=[[BUG, T1]]) {code} [1]https://github.com/apache/calcite/blob/2ffd74abb665f4119ff30926f3944070d8a9d0ac/core/src/main/java/org/apache/calcite/prepare/Prepare.java#L281 > 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 > Assignee: Feng Zhu > 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)