Major Description
Calcite produces wrong plan, hence wrong results for correlated subquery which contains windowing function.
Query
select * from emps where empid-1 IN (select min(e.empid) over () from emps e where e.empid =emps.empid);
Expected
0 rows
Actual
+-------+------+--------+--------+------+-------+-----+---------+---------+----------+ | EMPNO | NAME | DEPTNO | GENDER | CITY | EMPID | AGE | SLACKER | MANAGER | JOINEDAT | +-------+------+--------+--------+------+-------+-----+---------+---------+----------+ | 110 | John | 40 | M | Vancouver | 2 | null | false | true | 2002-05-03 | | 130 | Alice | 40 | F | Vancouver | 2 | null | false | true | 2007-01-01 | +-------+------+--------+--------+------+-------+-----+---------+---------+----------+