[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.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