[jira] [Updated] (CALCITE-3772) Query returning bad results

["Julian Hyde (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-3772?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Julian Hyde updated CALCITE-3772:
---------------------------------
    Affects Version/s:     (was: next)

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