[
https://issues.apache.org/jira/browse/CALCITE-3772?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mihai Budiu resolved CALCITE-3772.
----------------------------------
Fix Version/s: 1.39.0
Resolution: Fixed
Fix validated in
[https://github.com/apache/calcite/commit/d0384ce5edcf0cc182d6bbab8a58aea70d9fe5fa]
Thank you for the test [~nobigo]
> RelFieldTrimmer incorrectly trims fields when the query includes
> correlated-subquery
> ------------------------------------------------------------------------------------
>
> Key: CALCITE-3772
> URL: https://issues.apache.org/jira/browse/CALCITE-3772
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.21.0
> Environment: [^T1.csv]
> Reporter: Jacob Roldan
> Priority: Critical
> Labels: pull-request-available
> Fix For: 1.39.0
>
> 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.20.10#820010)
