Debian Codegraph @

test/selectH.test

raw
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