diff options
Diffstat (limited to 'test/selectH.test')
| -rw-r--r-- | test/selectH.test | 145 |
1 files changed, 145 insertions, 0 deletions
diff --git a/test/selectH.test b/test/selectH.test new file mode 100644 index 0000000..41f0999 --- /dev/null +++ b/test/selectH.test @@ -0,0 +1,145 @@ +# 2023-02-16 +# +# 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. +# +#*********************************************************************** +# +# Test cases for the omit-unused-subquery-column optimization. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix selectH + +do_execsql_test 1.1 { + CREATE TABLE t1( + c0, c1, c2, c3, c4, c5, c6, c7, c8, c9, + c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, + c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, + c30, c31, c32, c33, c34, c35, c36, c37, c38, c39, + c40, c41, c42, c43, c44, c45, c46, c47, c48, c49, + c50, c51, c52, c53, c54, c55, c56, c57, c58, c59, + c60, c61, c62, c63, c64, c65 + ); + INSERT INTO t1 VALUES( + 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, + 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, + 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, + 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, + 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, + 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, + 60, 61, 62, 63, 64, 65 + ); + CREATE INDEX t1c60 ON t1(c60); +} + +# The SQL counter(N) function adjusts the value of the global +# TCL variable ::selectH_cnt by the value N and returns the new +# value. By putting calls to counter(N) as unused columns in a +# view or subquery, we can check to see if the counter gets incremented, +# and if not that means that the unused column was omitted. +# +unset -nocomplain selectH_cnt +set selectH_cnt 0 +proc selectH_counter {amt} { + global selectH_cnt + incr selectH_cnt $amt + return $selectH_cnt +} +db func counter selectH_counter + +do_execsql_test 1.2 { + SELECT DISTINCT c44 FROM ( + SELECT c0 AS a, *, counter(1) FROM t1 + UNION ALL + SELECT c1 AS a, *, counter(1) FROM t1 + ) WHERE c60=60; +} {44} +do_test 1.3 { + set ::selectH_cnt +} {0} + +do_execsql_test 2.1 { + SELECT a FROM ( + SELECT counter(1) AS cnt, c15 AS a, *, c62 AS b FROM t1 + UNION ALL + SELECT counter(1) AS cnt, c16 AS a, *, c61 AS b FROM t1 + ORDER BY b + ); +} {16 15} +do_test 2.2 { + set ::selectH_cnt +} {0} + +do_execsql_test 3.1 { + CREATE VIEW v1 AS + SELECT c16 AS a, *, counter(1) AS x FROM t1 + UNION ALL + SELECT c17 AS a, *, counter(1) AS x FROM t1 + UNION ALL + SELECT c18 AS a, *, counter(1) AS x FROM t1 + UNION ALL + SELECT c19 AS a, *, counter(1) AS x FROM t1; + SELECT count(*) FROM v1 WHERE c60=60; +} {4} +do_test 3.2 { + set ::selectH_cnt +} {0} +do_execsql_test 3.3 { + SELECT count(a) FROM v1 WHERE c60=60; +} {4} +do_execsql_test 3.4 { + SELECT a FROM v1 WHERE c60=60; +} {16 17 18 19} +do_test 3.5 { + set ::selectH_cnt +} {0} +do_execsql_test 3.6 { + SELECT x FROM v1 WHERE c60=60; +} {1 2 3 4} +do_test 3.7 { + set ::selectH_cnt +} {4} + +# 2023-02-25 dbsqlfuzz bf1d3ed6e0e0dd8766027797d43db40c776d2b15 +# +do_execsql_test 4.1 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT); + SELECT 1 FROM (SELECT DISTINCT name COLLATE rtrim FROM sqlite_schema + UNION ALL SELECT a FROM t1); +} {1 1} + +do_execsql_test 4.2 { + SELECT DISTINCT name COLLATE rtrim FROM sqlite_schema + UNION ALL + SELECT a FROM t1 +} {v1 t1} + +#------------------------------------------------------------------------- +# forum post https://sqlite.org/forum/forumpost/b83c7b2168 +# +reset_db +do_execsql_test 5.0 { + CREATE TABLE t1 (val1); + INSERT INTO t1 VALUES(4); + INSERT INTO t1 VALUES(5); + CREATE TABLE t2 (val2); +} +do_execsql_test 5.1 { + SELECT DISTINCT val1 FROM t1 UNION ALL SELECT val2 FROM t2; +} { + 4 5 +} +do_execsql_test 5.2 { + SELECT count(1234) FROM ( + SELECT DISTINCT val1 FROM t1 UNION ALL SELECT val2 FROM t2 + ) +} {2} + +finish_test |