Open pkommoju opened 3 years ago
how to repro the tests? can you add the NIST as part of the unittest?
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.
branch nist_tests has these tests.
@zhouruiapple ,the NOT IN subqueres is handling in #220
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 <>cDisplayClass8_0.b0(LogicNode n) line 400
TreeNodeb 0(Expr e) line 654
List
1.VisitEachExists(Func
2 callback, List1 excluding) line 115 TreeNode
1.VisitEachExists(Func2 callback, List
1 excluding) line 119 TreeNode1.VisitEachExists(Func
2 callback, List`1 excluding) line 119 FilterHelper.PushJoinFilter(LogicNode plan, Expr filter) line 384 SelectStmt.pushdownFilter(LogicNode plan, Expr filter, Boolean pushJoinFilter) line 598 <>cDisplayClass43_0.1.RemoveAll(Predicate
1 match) SelectStmt.FilterPushDown(LogicNode plan, Boolean pushJoinFilter) line 647 SelectStmt.SubstitutionOptimize() line 777BUG: 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;