Changeset: 719d4bcf9581 for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=719d4bcf9581 Added Files: sql/test/SQLite_regress/sqllogictest/Original_source_URLs.txt sql/test/SQLite_regress/sqllogictest/Tests/All sql/test/SQLite_regress/sqllogictest/Tests/select1.test.sql sql/test/SQLite_regress/sqllogictest/Tests/select1.test.stable.err sql/test/SQLite_regress/sqllogictest/Tests/select1.test.stable.out sql/test/SQLite_regress/sqllogictest/Tests/select2.test.sql sql/test/SQLite_regress/sqllogictest/Tests/select2.test.stable.err sql/test/SQLite_regress/sqllogictest/Tests/select2.test.stable.out sql/test/SQLite_regress/sqllogictest/Tests/select3.test.sql sql/test/SQLite_regress/sqllogictest/Tests/select3.test.stable.err sql/test/SQLite_regress/sqllogictest/Tests/select3.test.stable.out sql/test/SQLite_regress/sqllogictest/Tests/select3.test.timeout sql/test/SQLite_regress/sqllogictest/Tests/select4.test.sql sql/test/SQLite_regress/sqllogictest/Tests/select4.test.stable.err sql/test/SQLite_regress/sqllogictest/Tests/select4.test.stable.out sql/test/SQLite_regress/sqllogictest/Tests/select5.test.sql sql/test/SQLite_regress/sqllogictest/Tests/select5.test.stable.err sql/test/SQLite_regress/sqllogictest/Tests/select5.test.stable.out sql/test/SQLite_regress/sqllogictest/Tests/select5.test.timeout sql/test/SQLite_regress/sqllogictest/select1.test sql/test/SQLite_regress/sqllogictest/select2.test sql/test/SQLite_regress/sqllogictest/select3.test sql/test/SQLite_regress/sqllogictest/select4.test sql/test/SQLite_regress/sqllogictest/select5.test Branch: Jul2015 Log Message:
Adding 5 SQLite sqllogic regression tests The test files and the *.stable.out files are quite big, so it takes some time to execute: Running in sql/test/SQLite_regress/sqllogictest 14:50:34> select1.test 14.084s OK OK 14:50:50> select2.test 13.082s OK OK 14:51:04> select3.test 46.272s OK OK 14:51:54> select4.test 25.216s OK OK 14:52:56> select5.test 236.130s OK OK No differences encountered during testing. Stefan and Sjoerd agreed to check these in so they are shared and tested on all platforms. If needed some of the tests (eg select5.test) could be tuned by reducing the number of SELECTs. diffs (truncated from 1268650 to 300 lines): diff --git a/sql/test/SQLite_regress/sqllogictest/Original_source_URLs.txt b/sql/test/SQLite_regress/sqllogictest/Original_source_URLs.txt new file mode 100644 --- /dev/null +++ b/sql/test/SQLite_regress/sqllogictest/Original_source_URLs.txt @@ -0,0 +1,25 @@ +The SQLite sqllogictest *.test files were copied from +http://www.sqlite.org/sqllogictest/dir?name=test + +The *.test files were next converted into *.test.sql variants which + can be processed by mclient (or any other SQL client tool which accepts .sql scripts). + +They are placed in the Tests/ subdir with an All file such that they can be run using Mtest.py + +The following issues where detected (and fixed in Jul2015 SP1 release) using the 5 select*.test.sql tests: + https://www.monetdb.org/bugzilla/show_bug.cgi?id=3705 + https://www.monetdb.org/bugzilla/show_bug.cgi?id=3706 + https://www.monetdb.org/bugzilla/show_bug.cgi?id=3719 + https://www.monetdb.org/bugzilla/show_bug.cgi?id=3760 + https://www.monetdb.org/bugzilla/show_bug.cgi?id=3798 + https://www.monetdb.org/bugzilla/show_bug.cgi?id=3799 + https://www.monetdb.org/bugzilla/show_bug.cgi?id=3803 + https://www.monetdb.org/bugzilla/show_bug.cgi?id=3809 + https://www.monetdb.org/bugzilla/show_bug.cgi?id=3819 + + +Note: we currently do not use the sqllogictest test program to do the tests. +This could be something to add in the future (if desired). +See: http://www.sqlite.org/sqllogictest/doc/trunk/about.wiki +and http://www.sqlite.org/sqllogictest/dir?name=src + diff --git a/sql/test/SQLite_regress/sqllogictest/Tests/All b/sql/test/SQLite_regress/sqllogictest/Tests/All new file mode 100644 --- /dev/null +++ b/sql/test/SQLite_regress/sqllogictest/Tests/All @@ -0,0 +1,6 @@ +select1.test +select2.test +select3.test +select4.test +select5.test + diff --git a/sql/test/SQLite_regress/sqllogictest/Tests/select1.test.sql b/sql/test/SQLite_regress/sqllogictest/Tests/select1.test.sql new file mode 100644 --- /dev/null +++ b/sql/test/SQLite_regress/sqllogictest/Tests/select1.test.sql @@ -0,0 +1,12129 @@ +CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER, d INTEGER, e INTEGER); +INSERT INTO t1(e,c,b,d,a) VALUES(103,102,100,101,104); +INSERT INTO t1(a,c,d,e,b) VALUES(107,106,108,109,105); +INSERT INTO t1(e,d,b,a,c) VALUES(110,114,112,111,113); +INSERT INTO t1(d,c,e,a,b) VALUES(116,119,117,115,118); +INSERT INTO t1(c,d,b,e,a) VALUES(123,122,124,120,121); +INSERT INTO t1(a,d,b,e,c) VALUES(127,128,129,126,125); +INSERT INTO t1(e,c,a,d,b) VALUES(132,134,131,133,130); +INSERT INTO t1(a,d,b,e,c) VALUES(138,136,139,135,137); +INSERT INTO t1(e,c,d,a,b) VALUES(144,141,140,142,143); +INSERT INTO t1(b,a,e,d,c) VALUES(145,149,146,148,147); +INSERT INTO t1(b,c,a,d,e) VALUES(151,150,153,154,152); +INSERT INTO t1(c,e,a,d,b) VALUES(155,157,159,156,158); +INSERT INTO t1(c,b,a,d,e) VALUES(161,160,163,164,162); +INSERT INTO t1(b,d,a,e,c) VALUES(167,169,168,165,166); +INSERT INTO t1(d,b,c,e,a) VALUES(171,170,172,173,174); +INSERT INTO t1(e,c,a,d,b) VALUES(177,176,179,178,175); +INSERT INTO t1(b,e,a,d,c) VALUES(181,180,182,183,184); +INSERT INTO t1(c,a,b,e,d) VALUES(187,188,186,189,185); +INSERT INTO t1(d,b,c,e,a) VALUES(190,194,193,192,191); +INSERT INTO t1(a,e,b,d,c) VALUES(199,197,198,196,195); +INSERT INTO t1(b,c,d,a,e) VALUES(200,202,203,201,204); +INSERT INTO t1(c,e,a,b,d) VALUES(208,209,205,206,207); +INSERT INTO t1(c,e,a,d,b) VALUES(214,210,213,212,211); +INSERT INTO t1(b,c,a,d,e) VALUES(218,215,216,217,219); +INSERT INTO t1(b,e,d,a,c) VALUES(223,221,222,220,224); +INSERT INTO t1(d,e,b,a,c) VALUES(226,227,228,229,225); +INSERT INTO t1(a,c,b,e,d) VALUES(234,231,232,230,233); +INSERT INTO t1(e,b,a,c,d) VALUES(237,236,239,235,238); +INSERT INTO t1(e,c,b,a,d) VALUES(242,244,240,243,241); +INSERT INTO t1(e,d,c,b,a) VALUES(246,248,247,249,245); + +-- query I nosort +SELECT CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END + FROM t1 + ORDER BY 1 +; +-- 30 values hashing to 3c13dee48d9356ae19af2515e05e6b54 + +-- query II nosort +SELECT a+b*2+c*3+d*4+e*5, + (a+b+c+d+e)/5 + FROM t1 + ORDER BY 1,2 +; +-- 60 values hashing to 808146289313018fce25f1a280bd8c30 + +-- query IIIII nosort +SELECT a+b*2+c*3+d*4+e*5, + CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222 + WHEN a<b+3 THEN 333 ELSE 444 END, + abs(b-c), + (a+b+c+d+e)/5, + a+b*2+c*3 + FROM t1 + WHERE (e>c OR e<d) + AND d>e + AND EXISTS(SELECT 1 FROM t1 AS x WHERE x.b<t1.b) + ORDER BY 4,2,1,3,5 +; +-- 80 values hashing to f588aa173060543daffc54d07638516f + +-- query IIIII nosort +SELECT c, + d-e, + CASE a+1 WHEN b THEN 111 WHEN c THEN 222 + WHEN d THEN 333 WHEN e THEN 444 ELSE 555 END, + a+b*2+c*3+d*4, + e + FROM t1 + WHERE d NOT BETWEEN 110 AND 150 + OR c BETWEEN b-2 AND d+2 + OR (e>c OR e<d) + ORDER BY 1,5,3,2,4 +; +-- 145 values hashing to 1e4da6adbf79506920a0b1e379d830d8 + +-- query IIIIII nosort +SELECT a+b*2+c*3+d*4, + (a+b+c+d+e)/5, + abs(a), + e, + CASE a+1 WHEN b THEN 111 WHEN c THEN 222 + WHEN d THEN 333 WHEN e THEN 444 ELSE 555 END, + d + FROM t1 + WHERE b>c + AND c>d + ORDER BY 3,4,5,1,2,6 +; +-- 24 values hashing to 425542fc8d1ec04f89534ae98d59a74d + +-- query IIII nosort +SELECT a+b*2+c*3+d*4, + CASE a+1 WHEN b THEN 111 WHEN c THEN 222 + WHEN d THEN 333 WHEN e THEN 444 ELSE 555 END, + (SELECT count(*) FROM t1 AS x WHERE x.c>t1.c AND x.d<t1.d), + c + FROM t1 + WHERE (c<=d-2 OR c>=d+2) + ORDER BY 4,2,1,3 +; +-- 60 values hashing to a2af299d7b2197866b7c8f6854b77ab5 + +-- query I nosort +SELECT CASE a+1 WHEN b THEN 111 WHEN c THEN 222 + WHEN d THEN 333 WHEN e THEN 444 ELSE 555 END + FROM t1 + ORDER BY 1 +; +-- 30 values hashing to f2bf77f8cfb62666ab72c866ed4d4f1a + +-- query I nosort +SELECT (SELECT count(*) FROM t1 AS x WHERE x.b<t1.b) + FROM t1 + WHERE (a>b-2 AND a<b+2) + OR c>d + ORDER BY 1 +; +-- 20 values hashing to 1d6b8ed1db696a5f1c8d126facddd077 + +-- query IIIII nosort +SELECT a, + (SELECT count(*) FROM t1 AS x WHERE x.c>t1.c AND x.d<t1.d), + b-c, + a-b, + CASE a+1 WHEN b THEN 111 WHEN c THEN 222 + WHEN d THEN 333 WHEN e THEN 444 ELSE 555 END + FROM t1 + WHERE c BETWEEN b-2 AND d+2 + OR a>b + OR b>c + ORDER BY 1,4,3,2,5 +; +-- 145 values hashing to 65042db64d506f67a37c85f825cdd11f + +-- query IIIIIII nosort +SELECT CASE a+1 WHEN b THEN 111 WHEN c THEN 222 + WHEN d THEN 333 WHEN e THEN 444 ELSE 555 END, + CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222 + WHEN a<b+3 THEN 333 ELSE 444 END, + a+b*2+c*3+d*4, + a+b*2+c*3, + c, + CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END, + abs(b-c) + FROM t1 + WHERE EXISTS(SELECT 1 FROM t1 AS x WHERE x.b<t1.b) + OR b>c + OR d NOT BETWEEN 110 AND 150 + ORDER BY 4,1,5,2,6,3,7 +; +-- 210 values hashing to a259991ed1248a55a07838ce36a7c257 + +-- query IIIIII nosort +SELECT CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END, + CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222 + WHEN a<b+3 THEN 333 ELSE 444 END, + a+b*2+c*3, + (SELECT count(*) FROM t1 AS x WHERE x.c>t1.c AND x.d<t1.d), + c, + b-c + FROM t1 + WHERE EXISTS(SELECT 1 FROM t1 AS x WHERE x.b<t1.b) + OR a>b + OR d NOT BETWEEN 110 AND 150 + ORDER BY 5,3,6,1,2,4 +; +-- 180 values hashing to 631b9506abcdd5dcea64c7ed2a3799ed + +-- query IIIII nosort +SELECT (SELECT count(*) FROM t1 AS x WHERE x.c>t1.c AND x.d<t1.d), + d, + a+b*2+c*3+d*4, + a-b, + (SELECT count(*) FROM t1 AS x WHERE x.b<t1.b) + FROM t1 + WHERE c BETWEEN b-2 AND d+2 + OR c>d + ORDER BY 3,5,4,1,2 +; +-- 125 values hashing to 58b4ab36ed442f3837188b38cd02486a + +-- query IIIIII nosort +SELECT CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END, + a-b, + abs(b-c), + c, + CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222 + WHEN a<b+3 THEN 333 ELSE 444 END, + a+b*2+c*3+d*4 + FROM t1 + ORDER BY 4,3,6,2,5,1 +; +-- 180 values hashing to 705c6f14a9e50f5542ad5d2fd225e6f1 + +-- query IIIIIII nosort +SELECT a+b*2, + a, + a+b*2+c*3+d*4+e*5, + (a+b+c+d+e)/5, + e, + c-d, + CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222 + WHEN a<b+3 THEN 333 ELSE 444 END + FROM t1 + WHERE a>b + ORDER BY 5,4,6,2,1,7,3 +; +-- 133 values hashing to af6179f0918bfe7e3a9cd1940fbb3f75 + +-- query II nosort +SELECT a, + b + FROM t1 + ORDER BY 1,2 +; +-- 60 values hashing to f88a6f6656b30fc5b3c4ede940008ff2 + +-- query III nosort +SELECT c, + a-b, + (SELECT count(*) FROM t1 AS x WHERE x.b<t1.b) + FROM t1 + WHERE c BETWEEN b-2 AND d+2 + ORDER BY 1,2,3 +; +-- 66 values hashing to 49b01d3ad62fdaa733104a9c70dfb87c + +-- query IIIIII nosort +SELECT CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END, + a+b*2+c*3+d*4+e*5, + abs(b-c), + b-c, + CASE a+1 WHEN b THEN 111 WHEN c THEN 222 + WHEN d THEN 333 WHEN e THEN 444 ELSE 555 END, + a-b + FROM t1 + WHERE b>c + AND a>b + AND d NOT BETWEEN 110 AND 150 + ORDER BY 2,1,6,4,3,5 +; +-- 42 values hashing to 6921d8dad7855bee57bd82dfd5fcbabc + +-- query IIIIII nosort +SELECT a+b*2+c*3+d*4+e*5, + abs(a), + b-c, + c-d, + CASE a+1 WHEN b THEN 111 WHEN c THEN 222 + WHEN d THEN 333 WHEN e THEN 444 ELSE 555 END, + a + FROM t1 _______________________________________________ checkin-list mailing list [email protected] https://www.monetdb.org/mailman/listinfo/checkin-list
