| 1 |
# 2023-02-16 |
| 2 |
# |
| 3 |
# The author disclaims copyright to this source code. In place of |
| 4 |
# a legal notice, here is a blessing: |
| 5 |
# |
| 6 |
# May you do good and not evil. |
| 7 |
# May you find forgiveness for yourself and forgive others. |
| 8 |
# May you share freely, never taking more than you give. |
| 9 |
# |
| 10 |
#*********************************************************************** |
| 11 |
# |
| 12 |
# Test cases for the omit-unused-subquery-column optimization. |
| 13 |
# |
| 14 |
|
| 15 |
set testdir [file dirname $argv0] |
| 16 |
source $testdir/tester.tcl |
| 17 |
set testprefix selectH |
| 18 |
|
| 19 |
do_execsql_test 1.1 { |
| 20 |
CREATE TABLE t1( |
| 21 |
c0, c1, c2, c3, c4, c5, c6, c7, c8, c9, |
| 22 |
c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, |
| 23 |
c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, |
| 24 |
c30, c31, c32, c33, c34, c35, c36, c37, c38, c39, |
| 25 |
c40, c41, c42, c43, c44, c45, c46, c47, c48, c49, |
| 26 |
c50, c51, c52, c53, c54, c55, c56, c57, c58, c59, |
| 27 |
c60, c61, c62, c63, c64, c65 |
| 28 |
); |
| 29 |
INSERT INTO t1 VALUES( |
| 30 |
0, 1, 2, 3, 4, 5, 6, 7, 8, 9, |
| 31 |
10, 11, 12, 13, 14, 15, 16, 17, 18, 19, |
| 32 |
20, 21, 22, 23, 24, 25, 26, 27, 28, 29, |
| 33 |
30, 31, 32, 33, 34, 35, 36, 37, 38, 39, |
| 34 |
40, 41, 42, 43, 44, 45, 46, 47, 48, 49, |
| 35 |
50, 51, 52, 53, 54, 55, 56, 57, 58, 59, |
| 36 |
60, 61, 62, 63, 64, 65 |
| 37 |
); |
| 38 |
CREATE INDEX t1c60 ON t1(c60); |
| 39 |
} |
| 40 |
|
| 41 |
# The SQL counter(N) function adjusts the value of the global |
| 42 |
# TCL variable ::selectH_cnt by the value N and returns the new |
| 43 |
# value. By putting calls to counter(N) as unused columns in a |
| 44 |
# view or subquery, we can check to see if the counter gets incremented, |
| 45 |
# and if not that means that the unused column was omitted. |
| 46 |
# |
| 47 |
unset -nocomplain selectH_cnt |
| 48 |
set selectH_cnt 0 |
| 49 |
proc selectH_counter {amt} { |
| 50 |
global selectH_cnt |
| 51 |
incr selectH_cnt $amt |
| 52 |
return $selectH_cnt |
| 53 |
} |
| 54 |
db func counter selectH_counter |
| 55 |
|
| 56 |
do_execsql_test 1.2 { |
| 57 |
SELECT DISTINCT c44 FROM ( |
| 58 |
SELECT c0 AS a, *, counter(1) FROM t1 |
| 59 |
UNION ALL |
| 60 |
SELECT c1 AS a, *, counter(1) FROM t1 |
| 61 |
) WHERE c60=60; |
| 62 |
} {44} |
| 63 |
do_test 1.3 { |
| 64 |
set ::selectH_cnt |
| 65 |
} {0} |
| 66 |
|
| 67 |
do_execsql_test 2.1 { |
| 68 |
SELECT a FROM ( |
| 69 |
SELECT counter(1) AS cnt, c15 AS a, *, c62 AS b FROM t1 |
| 70 |
UNION ALL |
| 71 |
SELECT counter(1) AS cnt, c16 AS a, *, c61 AS b FROM t1 |
| 72 |
ORDER BY b |
| 73 |
); |
| 74 |
} {16 15} |
| 75 |
do_test 2.2 { |
| 76 |
set ::selectH_cnt |
| 77 |
} {0} |
| 78 |
|
| 79 |
do_execsql_test 3.1 { |
| 80 |
CREATE VIEW v1 AS |
| 81 |
SELECT c16 AS a, *, counter(1) AS x FROM t1 |
| 82 |
UNION ALL |
| 83 |
SELECT c17 AS a, *, counter(1) AS x FROM t1 |
| 84 |
UNION ALL |
| 85 |
SELECT c18 AS a, *, counter(1) AS x FROM t1 |
| 86 |
UNION ALL |
| 87 |
SELECT c19 AS a, *, counter(1) AS x FROM t1; |
| 88 |
SELECT count(*) FROM v1 WHERE c60=60; |
| 89 |
} {4} |
| 90 |
do_test 3.2 { |
| 91 |
set ::selectH_cnt |
| 92 |
} {0} |
| 93 |
do_execsql_test 3.3 { |
| 94 |
SELECT count(a) FROM v1 WHERE c60=60; |
| 95 |
} {4} |
| 96 |
do_execsql_test 3.4 { |
| 97 |
SELECT a FROM v1 WHERE c60=60; |
| 98 |
} {16 17 18 19} |
| 99 |
do_test 3.5 { |
| 100 |
set ::selectH_cnt |
| 101 |
} {0} |
| 102 |
do_execsql_test 3.6 { |
| 103 |
SELECT x FROM v1 WHERE c60=60; |
| 104 |
} {1 2 3 4} |
| 105 |
do_test 3.7 { |
| 106 |
set ::selectH_cnt |
| 107 |
} {4} |
| 108 |
|
| 109 |
# 2023-02-25 dbsqlfuzz bf1d3ed6e0e0dd8766027797d43db40c776d2b15 |
| 110 |
# |
| 111 |
do_execsql_test 4.1 { |
| 112 |
DROP TABLE IF EXISTS t1; |
| 113 |
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT); |
| 114 |
SELECT 1 FROM (SELECT DISTINCT name COLLATE rtrim FROM sqlite_schema |
| 115 |
UNION ALL SELECT a FROM t1); |
| 116 |
} {1 1} |
| 117 |
|
| 118 |
do_execsql_test 4.2 { |
| 119 |
SELECT DISTINCT name COLLATE rtrim FROM sqlite_schema |
| 120 |
UNION ALL |
| 121 |
SELECT a FROM t1 |
| 122 |
} {v1 t1} |
| 123 |
|
| 124 |
#------------------------------------------------------------------------- |
| 125 |
# forum post https://sqlite.org/forum/forumpost/b83c7b2168 |
| 126 |
# |
| 127 |
reset_db |
| 128 |
do_execsql_test 5.0 { |
| 129 |
CREATE TABLE t1 (val1); |
| 130 |
INSERT INTO t1 VALUES(4); |
| 131 |
INSERT INTO t1 VALUES(5); |
| 132 |
CREATE TABLE t2 (val2); |
| 133 |
} |
| 134 |
do_execsql_test 5.1 { |
| 135 |
SELECT DISTINCT val1 FROM t1 UNION ALL SELECT val2 FROM t2; |
| 136 |
} { |
| 137 |
4 5 |
| 138 |
} |
| 139 |
do_execsql_test 5.2 { |
| 140 |
SELECT count(1234) FROM ( |
| 141 |
SELECT DISTINCT val1 FROM t1 UNION ALL SELECT val2 FROM t2 |
| 142 |
) |
| 143 |
} {2} |
| 144 |
|
| 145 |
finish_test |