[
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)
