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

["Jacob Roldan (Jira)" <ji...@apache.org>]

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)