[BUG] Incorrect handling of BETWEEN predicate, EXISTS, NOT EXISTS, IN, NOT IN subqueries

[pkommoju]

This test is from NIST test suite. RunNistTests needs to be run but asserts are suppressed. NIST Test dml014. SELECT PNUM FROM PROJ WHERE BUDGET >= 40000 AND BUDGET <= 60000; Should return only one row, with 'Vienna' but returns four: Deale, Vienna, Deale, Akorn

SELECT CITY FROM STAFF WHERE NOT(GRADE BETWEEN 12 AND 13); Also should return one row but returns many.

Similar problem. SELECT WORKS.HOURS FROM WORKS WHERE WORKS.PNUM NOT IN (SELECT PROJ.PNUM FROM PROJ WHERE PROJ.BUDGET BETWEEN 5000 AND 40000); Returns more than one row.

SELECT HOURS FROM WORKS WHERE PNUM NOT IN (SELECT PNUM FROM WORKS WHERE PNUM IN ('P1','P2','P4','P5','P6')); Returns more than one row.

SELECT STAFF.EMPNAME FROM STAFF WHERE NOT EXISTS (SELECT * FROM PROJ WHERE NOT EXISTS (SELECT * FROM WORKS WHERE STAFF.EMPNUM = WORKS.EMPNUM AND WORKS.PNUM=PROJ.PNUM)); Returns more than one row.

SUM() in WHERE is not supported but this is valid SQL. SELECT PNUM, SUM(HOURS) FROM WORKS GROUP BY PNUM HAVING EXISTS (SELECT PNAME FROM PROJ WHERE PROJ.PNUM = WORKS.PNUM AND SUM(WORKS.HOURS) > PROJ.BUDGET / 200); Returns error: WHERE condition must be a boolean expression and no aggregation is allowed.

-- BUG: Assertion goes off. Problem with BUDGET - GRADE * HOURS * 100 IN (-4400, -1000, 4000) not being a BinExpr. It should have been one but it is InListExpr.

SELECT MIN(PNAME) FROM PROJ, WORKS, STAFF WHERE PROJ.PNUM = WORKS.PNUM AND WORKS.EMPNUM = STAFF.EMPNUM AND BUDGET - GRADE * HOURS * 100 IN (-4400, -1000, 4000); LogicJoin.CreateKeyList() line 620 LogicJoin.AddFilter(Expr filter) line 571 <>c__DisplayClass8_0.<PushJoinFilter>b__0(LogicNode n) line 400 TreeNode1.VisitEachExists(Func2 callback, List1 excluding) line 115 TreeNode1.VisitEachExists(Func2 callback, List1 excluding) line 119 TreeNode1.VisitEachExists(Func2 callback, List1 excluding) line 119 FilterHelper.PushJoinFilter(LogicNode plan, Expr filter) line 384 SelectStmt.pushdownFilter(LogicNode plan, Expr filter, Boolean pushJoinFilter) line 598 <>c__DisplayClass43_0.<FilterPushDown>b__0(Expr e) line 654 List1.RemoveAll(Predicate`1 match) SelectStmt.FilterPushDown(LogicNode plan, Boolean pushJoinFilter) line 647 SelectStmt.SubstitutionOptimize() line 777

BUG: SELECT EMPNUM, SUM (HOURS) FROM WORKS OWORKS GROUP BY EMPNUM HAVING EMPNUM IN ( SELECT WORKS.EMPNUM FROM WORKS JOIN STAFF ON WORKS.EMPNUM = STAFF.EMPNUM AND HOURS < SUM (OWORKS.HOURS) / 3 AND GRADE > 10) ORDER BY EMPNUM;

qpmodel.dll!qpmodel.utils.TreeNode<qpmodel.logic.LogicNode>.VisitEachT<qpmodel.logic.LogicNode>(System.Action<qpmodel.logic.LogicNode> callback) Line 77 C# qpmodel.dll!qpmodel.utils.TreeNode<qpmodel.logic.LogicNode>.VisitEach(System.Action<qpmodel.logic.LogicNode> callback) Line 80 C# qpmodel.dll!qpmodel.logic.SelectStmt.CreateSinglePlan() Line 577 C# qpmodel.dll!qpmodel.logic.SelectStmt.CreatePlan() Line 485 C# qpmodel.dll!qpmodel.logic.SQLStatement.Exec() Line 96 C# qpmodel.dll!qpmodel.logic.SQLStatement.ExecSQL(qpmodel.logic.SQLStatement stmt, out string physicplan, qpmodel.logic.QueryOption option) Line 145 C# qpmodel.dll!qpmodel.logic.SQLStatement.ExecSQL(string sql, out qpmodel.logic.SQLStatement stmt, out string physicplan, out string error, qpmodel.logic.QueryOption option) Line 157 C# qpmodel.dll!qpmodel.logic.SQLStatement.ExecSQL(string sql, out string physicplan, out string error, qpmodel.logic.QueryOption option) Line 185 C# test.dll!qpmodel.unittest.TU.ExecuteSQL(string sql, out string physicplan, qpmodel.logic.QueryOption option) Line 57 C# test.dll!qpmodel.unittest.TU.ExecuteSQL(string sql) Line 53 C# test.dll!qpmodel.unittest.NistTests.dml073() Line 3881 C# test.dll!qpmodel.unittest.NistTests.RunNistTests() Line 3299 C# [External Code] And this one too SELECT EMPNUM, SUM (HOURS) FROM WORKS OWORKS GROUP BY EMPNUM HAVING EMPNUM IN ( SELECT WORKS.EMPNUM FROM WORKS JOIN STAFF ON WORKS.EMPNUM = STAFF.EMPNUM AND HOURS >= 10 + AVG (OWORKS.HOURS) AND CITY = 'Deale') ORDER BY EMPNUM;

[zhouqingqing]

how to repro the tests? can you add the NIST as part of the unittest?

[pkommoju]

My branch has it but I think I will separate the Tests into a separate branch and push it so that you can merge into master.

[pkommoju]

branch nist_tests has these tests.

[9DemonFox]

@zhouruiapple ，the NOT IN subqueres is handling in #220