diff options
Diffstat (limited to 'test/tkt1473.test')
| -rw-r--r-- | test/tkt1473.test | 728 |
1 files changed, 728 insertions, 0 deletions
diff --git a/test/tkt1473.test b/test/tkt1473.test new file mode 100644 index 0000000..3950272 --- /dev/null +++ b/test/tkt1473.test @@ -0,0 +1,728 @@ +# 2005 September 19 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. +# +# This file implements tests to verify that ticket #1473 has been +# fixed. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +ifcapable !compound { + finish_test + return +} + +do_test tkt1473-1.1 { + execsql { + CREATE TABLE t1(a,b); + INSERT INTO t1 VALUES(1,2); + INSERT INTO t1 VALUES(3,4); + SELECT * FROM t1 + } +} {1 2 3 4} + +do_test tkt1473-1.2 { + execsql { + SELECT 1 FROM t1 WHERE a=1 UNION ALL SELECT 2 FROM t1 WHERE b=0 + } +} {1} +do_test tkt1473-1.3 { + execsql { + SELECT 1 FROM t1 WHERE a=1 UNION SELECT 2 FROM t1 WHERE b=0 + } +} {1} +do_test tkt1473-1.4 { + execsql { + SELECT 1 FROM t1 WHERE a=1 UNION ALL SELECT 2 FROM t1 WHERE b=4 + } +} {1 2} +do_test tkt1473-1.5 { + execsql { + SELECT 1 FROM t1 WHERE a=1 UNION SELECT 2 FROM t1 WHERE b=4 + } +} {1 2} +do_test tkt1473-1.6 { + execsql { + SELECT 1 FROM t1 WHERE a=0 UNION ALL SELECT 2 FROM t1 WHERE b=4 + } +} {2} +do_test tkt1473-1.7 { + execsql { + SELECT 1 FROM t1 WHERE a=0 UNION SELECT 2 FROM t1 WHERE b=4 + } +} {2} +do_test tkt1473-1.8 { + execsql { + SELECT 1 FROM t1 WHERE a=0 UNION ALL SELECT 2 FROM t1 WHERE b=0 + } +} {} +do_test tkt1473-1.9 { + execsql { + SELECT 1 FROM t1 WHERE a=0 UNION SELECT 2 FROM t1 WHERE b=0 + } +} {} + +# Everything from this point on depends on sub-queries. So skip it +# if sub-queries are not available. +ifcapable !subquery { + finish_test + return +} + +do_test tkt1473-2.2 { + execsql { + SELECT (SELECT 1 FROM t1 WHERE a=1 UNION ALL SELECT 2 FROM t1 WHERE b=0) + } +} {1} +do_test tkt1473-2.3 { + execsql { + SELECT (SELECT 1 FROM t1 WHERE a=1 UNION SELECT 2 FROM t1 WHERE b=0) + } +} {1} +do_test tkt1473-2.4 { + execsql { + SELECT (SELECT 1 FROM t1 WHERE a=1 UNION ALL SELECT 2 FROM t1 WHERE b=4) + } +} {1} +do_test tkt1473-2.5 { + execsql { + SELECT (SELECT 1 FROM t1 WHERE a=1 UNION SELECT 2 FROM t1 WHERE b=4) + } +} {1} +do_test tkt1473-2.6 { + execsql { + SELECT (SELECT 1 FROM t1 WHERE a=0 UNION ALL SELECT 2 FROM t1 WHERE b=4) + } +} {2} +do_test tkt1473-2.7 { + execsql { + SELECT (SELECT 1 FROM t1 WHERE a=0 UNION SELECT 2 FROM t1 WHERE b=4) + } +} {2} +do_test tkt1473-2.8 { + execsql { + SELECT (SELECT 1 FROM t1 WHERE a=0 UNION ALL SELECT 2 FROM t1 WHERE b=0) + } +} {{}} +do_test tkt1473-2.9 { + execsql { + SELECT (SELECT 1 FROM t1 WHERE a=0 UNION SELECT 2 FROM t1 WHERE b=0) + } +} {{}} + +do_test tkt1473-3.2 { + execsql { + SELECT EXISTS + (SELECT 1 FROM t1 WHERE a=1 UNION ALL SELECT 2 FROM t1 WHERE b=0) + } +} {1} +do_test tkt1473-3.3 { + execsql { + SELECT EXISTS + (SELECT 1 FROM t1 WHERE a=1 UNION SELECT 2 FROM t1 WHERE b=0) + } +} {1} +do_test tkt1473-3.4 { + execsql { + SELECT EXISTS + (SELECT 1 FROM t1 WHERE a=1 UNION ALL SELECT 2 FROM t1 WHERE b=4) + } +} {1} +do_test tkt1473-3.5 { + execsql { + SELECT EXISTS + (SELECT 1 FROM t1 WHERE a=1 UNION SELECT 2 FROM t1 WHERE b=4) + } +} {1} +do_test tkt1473-3.6 { + execsql { + SELECT EXISTS + (SELECT 1 FROM t1 WHERE a=0 UNION ALL SELECT 2 FROM t1 WHERE b=4) + } +} {1} +do_test tkt1473-3.7 { + execsql { + SELECT EXISTS + (SELECT 1 FROM t1 WHERE a=0 UNION SELECT 2 FROM t1 WHERE b=4) + } +} {1} +do_test tkt1473-3.8 { + execsql { + SELECT EXISTS + (SELECT 1 FROM t1 WHERE a=0 UNION ALL SELECT 2 FROM t1 WHERE b=0) + } +} {0} +do_test tkt1473-3.9 { + execsql { + SELECT EXISTS + (SELECT 1 FROM t1 WHERE a=0 UNION SELECT 2 FROM t1 WHERE b=0) + } +} {0} + +do_test tkt1473-4.1 { + execsql { + CREATE TABLE t2(x,y); + INSERT INTO t2 VALUES(1,2); + INSERT INTO t2 SELECT x+2, y+2 FROM t2; + INSERT INTO t2 SELECT x+4, y+4 FROM t2; + INSERT INTO t2 SELECT x+8, y+8 FROM t2; + INSERT INTO t2 SELECT x+16, y+16 FROM t2; + INSERT INTO t2 SELECT x+32, y+32 FROM t2; + INSERT INTO t2 SELECT x+64, y+64 FROM t2; + SELECT count(*), sum(x), sum(y) FROM t2; + } +} {64 4096 4160} +do_test tkt1473-4.2 { + execsql { + SELECT 1 FROM t2 WHERE x=0 + UNION ALL + SELECT 2 FROM t2 WHERE x=1 + UNION ALL + SELECT 3 FROM t2 WHERE x=2 + UNION ALL + SELECT 4 FROM t2 WHERE x=3 + UNION ALL + SELECT 5 FROM t2 WHERE x=4 + UNION ALL + SELECT 6 FROM t2 WHERE y=0 + UNION ALL + SELECT 7 FROM t2 WHERE y=1 + UNION ALL + SELECT 8 FROM t2 WHERE y=2 + UNION ALL + SELECT 9 FROM t2 WHERE y=3 + UNION ALL + SELECT 10 FROM t2 WHERE y=4 + } +} {2 4 8 10} +do_test tkt1473-4.3 { + execsql { + SELECT ( + SELECT 1 FROM t2 WHERE x=0 + UNION ALL + SELECT 2 FROM t2 WHERE x=1 + UNION ALL + SELECT 3 FROM t2 WHERE x=2 + UNION ALL + SELECT 4 FROM t2 WHERE x=3 + UNION ALL + SELECT 5 FROM t2 WHERE x=4 + UNION ALL + SELECT 6 FROM t2 WHERE y=0 + UNION ALL + SELECT 7 FROM t2 WHERE y=1 + UNION ALL + SELECT 8 FROM t2 WHERE y=2 + UNION ALL + SELECT 9 FROM t2 WHERE y=3 + UNION ALL + SELECT 10 FROM t2 WHERE y=4 + ) + } +} {2} +do_test tkt1473-4.4 { + execsql { + SELECT ( + SELECT 1 FROM t2 WHERE x=0 + UNION ALL + SELECT 2 FROM t2 WHERE x=-1 + UNION ALL + SELECT 3 FROM t2 WHERE x=2 + UNION ALL + SELECT 4 FROM t2 WHERE x=3 + UNION ALL + SELECT 5 FROM t2 WHERE x=4 + UNION ALL + SELECT 6 FROM t2 WHERE y=0 + UNION ALL + SELECT 7 FROM t2 WHERE y=1 + UNION ALL + SELECT 8 FROM t2 WHERE y=2 + UNION ALL + SELECT 9 FROM t2 WHERE y=3 + UNION ALL + SELECT 10 FROM t2 WHERE y=4 + ) + } +} {4} +do_test tkt1473-4.5 { + execsql { + SELECT ( + SELECT 1 FROM t2 WHERE x=0 + UNION ALL + SELECT 2 FROM t2 WHERE x=-1 + UNION ALL + SELECT 3 FROM t2 WHERE x=2 + UNION ALL + SELECT 4 FROM t2 WHERE x=-1 + UNION ALL + SELECT 5 FROM t2 WHERE x=4 + UNION ALL + SELECT 6 FROM t2 WHERE y=0 + UNION ALL + SELECT 7 FROM t2 WHERE y=1 + UNION ALL + SELECT 8 FROM t2 WHERE y=2 + UNION ALL + SELECT 9 FROM t2 WHERE y=3 + UNION ALL + SELECT 10 FROM t2 WHERE y=-4 + ) + } +} {8} +do_test tkt1473-4.6 { + execsql { + SELECT ( + SELECT 1 FROM t2 WHERE x=0 + UNION ALL + SELECT 2 FROM t2 WHERE x=-1 + UNION ALL + SELECT 3 FROM t2 WHERE x=2 + UNION ALL + SELECT 4 FROM t2 WHERE x=-2 + UNION ALL + SELECT 5 FROM t2 WHERE x=4 + UNION ALL + SELECT 6 FROM t2 WHERE y=0 + UNION ALL + SELECT 7 FROM t2 WHERE y=1 + UNION ALL + SELECT 8 FROM t2 WHERE y=-3 + UNION ALL + SELECT 9 FROM t2 WHERE y=3 + UNION ALL + SELECT 10 FROM t2 WHERE y=4 + ) + } +} {10} +do_test tkt1473-4.7 { + execsql { + SELECT ( + SELECT 1 FROM t2 WHERE x=0 + UNION ALL + SELECT 2 FROM t2 WHERE x=-1 + UNION ALL + SELECT 3 FROM t2 WHERE x=2 + UNION ALL + SELECT 4 FROM t2 WHERE x=-2 + UNION ALL + SELECT 5 FROM t2 WHERE x=4 + UNION ALL + SELECT 6 FROM t2 WHERE y=0 + UNION ALL + SELECT 7 FROM t2 WHERE y=1 + UNION ALL + SELECT 8 FROM t2 WHERE y=-3 + UNION ALL + SELECT 9 FROM t2 WHERE y=3 + UNION ALL + SELECT 10 FROM t2 WHERE y=-4 + ) + } +} {{}} + +do_test tkt1473-5.3 { + execsql { + SELECT EXISTS ( + SELECT 1 FROM t2 WHERE x=0 + UNION ALL + SELECT 2 FROM t2 WHERE x=1 + UNION ALL + SELECT 3 FROM t2 WHERE x=2 + UNION ALL + SELECT 4 FROM t2 WHERE x=3 + UNION ALL + SELECT 5 FROM t2 WHERE x=4 + UNION ALL + SELECT 6 FROM t2 WHERE y=0 + UNION ALL + SELECT 7 FROM t2 WHERE y=1 + UNION ALL + SELECT 8 FROM t2 WHERE y=2 + UNION ALL + SELECT 9 FROM t2 WHERE y=3 + UNION ALL + SELECT 10 FROM t2 WHERE y=4 + ) + } +} {1} +do_test tkt1473-5.4 { + execsql { + SELECT EXISTS ( + SELECT 1 FROM t2 WHERE x=0 + UNION ALL + SELECT 2 FROM t2 WHERE x=-1 + UNION ALL + SELECT 3 FROM t2 WHERE x=2 + UNION ALL + SELECT 4 FROM t2 WHERE x=3 + UNION ALL + SELECT 5 FROM t2 WHERE x=4 + UNION ALL + SELECT 6 FROM t2 WHERE y=0 + UNION ALL + SELECT 7 FROM t2 WHERE y=1 + UNION ALL + SELECT 8 FROM t2 WHERE y=2 + UNION ALL + SELECT 9 FROM t2 WHERE y=3 + UNION ALL + SELECT 10 FROM t2 WHERE y=4 + ) + } +} {1} + +do_test tkt1473-5.5 { + execsql { + SELECT EXISTS ( + SELECT 1 FROM t2 WHERE x=0 + UNION ALL + SELECT 2 FROM t2 WHERE x=-1 + UNION ALL + SELECT 3 FROM t2 WHERE x=2 + UNION ALL + SELECT 4 FROM t2 WHERE x=-1 + UNION ALL + SELECT 5 FROM t2 WHERE x=4 + UNION ALL + SELECT 6 FROM t2 WHERE y=0 + UNION ALL + SELECT 7 FROM t2 WHERE y=1 + UNION ALL + SELECT 8 FROM t2 WHERE y=2 + UNION ALL + SELECT 9 FROM t2 WHERE y=3 + UNION ALL + SELECT 10 FROM t2 WHERE y=-4 + ) + } +} {1} +do_test tkt1473-5.6 { + execsql { + SELECT EXISTS ( + SELECT 1 FROM t2 WHERE x=0 + UNION ALL + SELECT 2 FROM t2 WHERE x=-1 + UNION ALL + SELECT 3 FROM t2 WHERE x=2 + UNION ALL + SELECT 4 FROM t2 WHERE x=-2 + UNION ALL + SELECT 5 FROM t2 WHERE x=4 + UNION ALL + SELECT 6 FROM t2 WHERE y=0 + UNION ALL + SELECT 7 FROM t2 WHERE y=1 + UNION ALL + SELECT 8 FROM t2 WHERE y=-3 + UNION ALL + SELECT 9 FROM t2 WHERE y=3 + UNION ALL + SELECT 10 FROM t2 WHERE y=4 + ) + } +} {1} +do_test tkt1473-5.7 { + execsql { + SELECT EXISTS ( + SELECT 1 FROM t2 WHERE x=0 + UNION ALL + SELECT 2 FROM t2 WHERE x=-1 + UNION ALL + SELECT 3 FROM t2 WHERE x=2 + UNION ALL + SELECT 4 FROM t2 WHERE x=-2 + UNION ALL + SELECT 5 FROM t2 WHERE x=4 + UNION ALL + SELECT 6 FROM t2 WHERE y=0 + UNION ALL + SELECT 7 FROM t2 WHERE y=1 + UNION ALL + SELECT 8 FROM t2 WHERE y=-3 + UNION ALL + SELECT 9 FROM t2 WHERE y=3 + UNION ALL + SELECT 10 FROM t2 WHERE y=-4 + ) + } +} {0} + +do_test tkt1473-6.3 { + execsql { + SELECT EXISTS ( + SELECT 1 FROM t2 WHERE x=0 + UNION + SELECT 2 FROM t2 WHERE x=1 + UNION + SELECT 3 FROM t2 WHERE x=2 + UNION + SELECT 4 FROM t2 WHERE x=3 + UNION + SELECT 5 FROM t2 WHERE x=4 + UNION + SELECT 6 FROM t2 WHERE y=0 + UNION + SELECT 7 FROM t2 WHERE y=1 + UNION + SELECT 8 FROM t2 WHERE y=2 + UNION + SELECT 9 FROM t2 WHERE y=3 + UNION + SELECT 10 FROM t2 WHERE y=4 + ) + } +} {1} +do_test tkt1473-6.4 { + execsql { + SELECT EXISTS ( + SELECT 1 FROM t2 WHERE x=0 + UNION + SELECT 2 FROM t2 WHERE x=-1 + UNION + SELECT 3 FROM t2 WHERE x=2 + UNION + SELECT 4 FROM t2 WHERE x=3 + UNION + SELECT 5 FROM t2 WHERE x=4 + UNION + SELECT 6 FROM t2 WHERE y=0 + UNION + SELECT 7 FROM t2 WHERE y=1 + UNION + SELECT 8 FROM t2 WHERE y=2 + UNION + SELECT 9 FROM t2 WHERE y=3 + UNION + SELECT 10 FROM t2 WHERE y=4 + ) + } +} {1} + +do_test tkt1473-6.5 { + execsql { + SELECT EXISTS ( + SELECT 1 FROM t2 WHERE x=0 + UNION + SELECT 2 FROM t2 WHERE x=-1 + UNION + SELECT 3 FROM t2 WHERE x=2 + UNION + SELECT 4 FROM t2 WHERE x=-1 + UNION + SELECT 5 FROM t2 WHERE x=4 + UNION + SELECT 6 FROM t2 WHERE y=0 + UNION + SELECT 7 FROM t2 WHERE y=1 + UNION + SELECT 8 FROM t2 WHERE y=2 + UNION + SELECT 9 FROM t2 WHERE y=3 + UNION + SELECT 10 FROM t2 WHERE y=-4 + ) + } +} {1} +do_test tkt1473-6.6 { + execsql { + SELECT EXISTS ( + SELECT 1 FROM t2 WHERE x=0 + UNION + SELECT 2 FROM t2 WHERE x=-1 + UNION + SELECT 3 FROM t2 WHERE x=2 + UNION + SELECT 4 FROM t2 WHERE x=-2 + UNION + SELECT 5 FROM t2 WHERE x=4 + UNION + SELECT 6 FROM t2 WHERE y=0 + UNION + SELECT 7 FROM t2 WHERE y=1 + UNION + SELECT 8 FROM t2 WHERE y=-3 + UNION + SELECT 9 FROM t2 WHERE y=3 + UNION + SELECT 10 FROM t2 WHERE y=4 + ) + } +} {1} +do_test tkt1473-6.7 { + execsql { + SELECT EXISTS ( + SELECT 1 FROM t2 WHERE x=0 + UNION + SELECT 2 FROM t2 WHERE x=-1 + UNION + SELECT 3 FROM t2 WHERE x=2 + UNION + SELECT 4 FROM t2 WHERE x=-2 + UNION + SELECT 5 FROM t2 WHERE x=4 + UNION + SELECT 6 FROM t2 WHERE y=0 + UNION + SELECT 7 FROM t2 WHERE y=1 + UNION + SELECT 8 FROM t2 WHERE y=-3 + UNION + SELECT 9 FROM t2 WHERE y=3 + UNION + SELECT 10 FROM t2 WHERE y=-4 + ) + } +} {0} +do_test tkt1473-6.8 { + execsql { + SELECT EXISTS ( + SELECT 1 FROM t2 WHERE x=0 + UNION + SELECT 2 FROM t2 WHERE x=-1 + UNION + SELECT 3 FROM t2 WHERE x=2 + UNION + SELECT 4 FROM t2 WHERE x=-2 + UNION + SELECT 5 FROM t2 WHERE x=4 + UNION ALL + SELECT 6 FROM t2 WHERE y=0 + UNION + SELECT 7 FROM t2 WHERE y=1 + UNION + SELECT 8 FROM t2 WHERE y=-3 + UNION + SELECT 9 FROM t2 WHERE y=3 + UNION + SELECT 10 FROM t2 WHERE y=4 + ) + } +} {1} +do_test tkt1473-6.9 { + execsql { + SELECT EXISTS ( + SELECT 1 FROM t2 WHERE x=0 + UNION + SELECT 2 FROM t2 WHERE x=-1 + UNION + SELECT 3 FROM t2 WHERE x=2 + UNION + SELECT 4 FROM t2 WHERE x=-2 + UNION + SELECT 5 FROM t2 WHERE x=4 + UNION ALL + SELECT 6 FROM t2 WHERE y=0 + UNION + SELECT 7 FROM t2 WHERE y=1 + UNION + SELECT 8 FROM t2 WHERE y=-3 + UNION + SELECT 9 FROM t2 WHERE y=3 + UNION + SELECT 10 FROM t2 WHERE y=-4 + ) + } +} {0} + +do_test tkt1473-7.1 { + execsql { + SELECT 1 FROM t2 WHERE x=1 EXCEPT SELECT 2 FROM t2 WHERE y=2 + } +} {1} +do_test tkt1473-7.2 { + execsql { + SELECT ( + SELECT 1 FROM t2 WHERE x=1 EXCEPT SELECT 2 FROM t2 WHERE y=2 + ) + } +} {1} +do_test tkt1473-7.3 { + execsql { + SELECT EXISTS ( + SELECT 1 FROM t2 WHERE x=1 EXCEPT SELECT 2 FROM t2 WHERE y=2 + ) + } +} {1} +do_test tkt1473-7.4 { + execsql { + SELECT ( + SELECT 1 FROM t2 WHERE x=0 EXCEPT SELECT 2 FROM t2 WHERE y=2 + ) + } +} {{}} +do_test tkt1473-7.5 { + execsql { + SELECT EXISTS ( + SELECT 1 FROM t2 WHERE x=0 EXCEPT SELECT 2 FROM t2 WHERE y=2 + ) + } +} {0} + +do_test tkt1473-8.1 { + execsql { + SELECT 1 FROM t2 WHERE x=1 INTERSECT SELECT 2 FROM t2 WHERE y=2 + } +} {} +do_test tkt1473-8.1 { + execsql { + SELECT 1 FROM t2 WHERE x=1 INTERSECT SELECT 1 FROM t2 WHERE y=2 + } +} {1} +do_test tkt1473-8.3 { + execsql { + SELECT ( + SELECT 1 FROM t2 WHERE x=1 INTERSECT SELECT 2 FROM t2 WHERE y=2 + ) + } +} {{}} +do_test tkt1473-8.4 { + execsql { + SELECT ( + SELECT 1 FROM t2 WHERE x=1 INTERSECT SELECT 1 FROM t2 WHERE y=2 + ) + } +} {1} +do_test tkt1473-8.5 { + execsql { + SELECT EXISTS ( + SELECT 1 FROM t2 WHERE x=1 INTERSECT SELECT 2 FROM t2 WHERE y=2 + ) + } +} {0} +do_test tkt1473-8.6 { + execsql { + SELECT EXISTS ( + SELECT 1 FROM t2 WHERE x=1 INTERSECT SELECT 1 FROM t2 WHERE y=2 + ) + } +} {1} +do_test tkt1473-8.7 { + execsql { + SELECT ( + SELECT 1 FROM t2 WHERE x=0 INTERSECT SELECT 1 FROM t2 WHERE y=2 + ) + } +} {{}} +do_test tkt1473-8.8 { + execsql { + SELECT EXISTS ( + SELECT 1 FROM t2 WHERE x=1 INTERSECT SELECT 1 FROM t2 WHERE y=0 + ) + } +} {0} + + + + +finish_test |