Debian Codegraph @

test/tkt-99378177930f87bd.test

raw
1 # 2022-11-23
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 # This file implements regression tests for SQLite library.
12 #
13 # This file implements tests to verify that the enhancement
14 # request documented by ticket 99378177930f87bd is working.
15 #
16 # The enhancement is that if an aggregate query with a GROUP BY clause
17 # uses subexpressions in the arguments to aggregate functions that are
18 # also columns of an index, then the values are pulled from the index
19 # rather than being recomputed. This has the potential to make some
20 # indexed queries works as if the index were covering.
21 #
22
23 set testdir [file dirname $argv0]
24 source $testdir/tester.tcl
25
26 do_execsql_test tkt-99378-100 {
27 CREATE TABLE t1(a INT, b TEXT, c INT, d INT);
28 INSERT INTO t1(a,b,c,d) VALUES
29 (1, '{"x":1}', 12, 3),
30 (1, '{"x":2}', 4, 5),
31 (1, '{"x":1}', 6, 11),
32 (2, '{"x":1}', 22, 3),
33 (2, '{"x":2}', 4, 5),
34 (3, '{"x":1}', 6, 7);
35 CREATE INDEX t1x ON t1(d, a, b->>'x', c);
36 CREATE TABLE t2(y);
37 INSERT INTO t2(y) VALUES(9);
38 } {}
39 do_execsql_test tkt-99378-110 {
40 SELECT a,
41 SUM(1) AS t1,
42 SUM(CASE WHEN b->>'x'=1 THEN 1 END) AS t2,
43 SUM(c) AS t3,
44 SUM(CASE WHEN b->>'x'=1 THEN c END) AS t4
45 FROM t1
46 WHERE d BETWEEN 0 and 10
47 GROUP BY a;
48 } {
49 1 2 1 16 12
50 2 2 1 26 22
51 3 1 1 6 6
52 }
53 do_execsql_test tkt-99378-111 {
54 SELECT if(a,a,y),
55 SUM(1) AS t1,
56 SUM(CASE WHEN b->>'x'=1 THEN 1 END) AS t2,
57 SUM(c) AS t3,
58 SUM(CASE WHEN b->>'x'=1 THEN c END) AS t4
59 FROM t2 CROSS JOIN t1
60 WHERE d BETWEEN 0 and 10
61 GROUP BY a;
62 } {
63 1 2 1 16 12
64 2 2 1 26 22
65 3 1 1 6 6
66 }
67
68 # The proof that the index on the expression is being used is in the
69 # fact that the byte code contains no "Function" opcodes. In other words,
70 # the ->> operator (which is implemented by a function) is never invoked.
71 # Instead, the b->>'x' value is pulled out of the index.
72 #
73 do_execsql_test tkt-99378-120 {
74 EXPLAIN
75 SELECT a,
76 SUM(1) AS t1,
77 SUM(CASE WHEN b->>'x'=1 THEN 1 END) AS t2,
78 SUM(c) AS t3,
79 SUM(CASE WHEN b->>'x'=1 THEN c END) AS t4
80 FROM t1
81 WHERE d BETWEEN 0 and 10
82 GROUP BY a;
83 } {~/Function/}
84 do_execsql_test tkt-99378-121 {
85 EXPLAIN
86 SELECT if(a,a,y),
87 SUM(1) AS t1,
88 SUM(CASE WHEN b->>'x'=1 THEN 1 END) AS t2,
89 SUM(c) AS t3,
90 SUM(CASE WHEN b->>'x'=1 THEN c END) AS t4
91 FROM t2 CROSS JOIN t1
92 WHERE d BETWEEN 0 and 10
93 GROUP BY a;
94 } {~/Function/}
95
96
97 do_execsql_test tkt-99378-130 {
98 SELECT a,
99 SUM(1) AS t1,
100 SUM(CASE WHEN b->>'x'=1 THEN 1 END) AS t2,
101 SUM(c) AS t3,
102 SUM(CASE WHEN b->>'x'=1 THEN c END) AS t4
103 FROM t1
104 WHERE d BETWEEN 0 and 10
105 GROUP BY +a;
106 } {
107 1 2 1 16 12
108 2 2 1 26 22
109 3 1 1 6 6
110 }
111 do_execsql_test tkt-99378-140 {
112 EXPLAIN
113 SELECT a,
114 SUM(1) AS t1,
115 SUM(CASE WHEN b->>'x'=1 THEN 1 END) AS t2,
116 SUM(c) AS t3,
117 SUM(CASE WHEN b->>'x'=1 THEN c END) AS t4
118 FROM t1
119 WHERE d BETWEEN 0 and 10
120 GROUP BY +a;
121 } {~/Function/}
122
123 do_execsql_test tkt-99378-200 {
124 DROP INDEX t1x;
125 CREATE INDEX t1x ON t1(a, d, b->>'x', c);
126 }
127 do_execsql_test tkt-99378-210 {
128 SELECT a,
129 SUM(1) AS t1,
130 SUM(CASE WHEN b->>'x'=1 THEN 1 END) AS t2,
131 SUM(c) AS t3,
132 SUM(CASE WHEN b->>'x'=1 THEN c END) AS t4
133 FROM t1
134 WHERE d BETWEEN 0 and 10
135 GROUP BY a;
136 } {
137 1 2 1 16 12
138 2 2 1 26 22
139 3 1 1 6 6
140 }
141 do_execsql_test tkt-99378-220 {
142 EXPLAIN
143 SELECT a,
144 SUM(1) AS t1,
145 SUM(CASE WHEN b->>'x'=1 THEN 1 END) AS t2,
146 SUM(c) AS t3,
147 SUM(CASE WHEN b->>'x'=1 THEN c END) AS t4
148 FROM t1
149 WHERE d BETWEEN 0 and 10
150 GROUP BY a;
151 } {~/Function/}
152 do_execsql_test tkt-99378-230 {
153 SELECT a,
154 SUM(1) AS t1,
155 SUM(CASE WHEN b->>'x'=1 THEN 1 END) AS t2,
156 SUM(c) AS t3,
157 SUM(CASE WHEN b->>'x'=1 THEN c END) AS t4
158 FROM t1
159 WHERE d BETWEEN 0 and 10
160 GROUP BY a;
161 } {
162 1 2 1 16 12
163 2 2 1 26 22
164 3 1 1 6 6
165 }
166 do_execsql_test tkt-99378-240 {
167 EXPLAIN
168 SELECT a,
169 SUM(1) AS t1,
170 SUM(CASE WHEN b->>'x'=1 THEN 1 END) AS t2,
171 SUM(c) AS t3,
172 SUM(CASE WHEN b->>'x'=1 THEN c END) AS t4
173 FROM t1
174 WHERE d BETWEEN 0 and 10
175 GROUP BY a;
176 } {~/Function/}
177
178 # 2022-12-20 dbsqlfuzz a644e70d7683a7ca59c71861a153c1dccf8850b9
179 #
180 do_execsql_test tkt-99378-300 {
181 DROP TABLE IF EXISTS t1;
182 CREATE TABLE t1(a INT);
183 CREATE INDEX i1 ON t1(a,a=a);
184 INSERT INTO t1 VALUES(1),(2),(3),(4);
185 SELECT * FROM t1 NATURAL JOIN t1
186 WHERE a==1
187 OR (
188 (SELECT avg(
189 (SELECT sum((SELECT 1 FROM t1 NATURAL RIGHT JOIN t1 WHERE a=a)))) AS xyz
190 )
191 AND a==2
192 );
193 } {1 2}
194 do_execsql_test tkt-99378-310 {
195 DROP INDEX i1;
196 SELECT * FROM t1 NATURAL JOIN t1
197 WHERE a==1
198 OR (
199 (SELECT avg(
200 (SELECT sum((SELECT 1 FROM t1 NATURAL RIGHT JOIN t1 WHERE a=a)))) AS xyz
201 )
202 AND a==2
203 );
204 } {1 2}
205
206 # 2023-03-04 https://sqlite.org/forum/forumpost/a68313d054
207 #
208 # See also indexexpr1-2200 added on 2023-03-18.
209 #
210 do_execsql_test tkt-99378-400 {
211 DROP TABLE t1;
212 DROP TABLE t2;
213 CREATE TABLE t0(w);
214 INSERT INTO t0(w) VALUES(1);
215 CREATE TABLE t1(x);
216 INSERT INTO t1(x) VALUES(1);
217 CREATE INDEX t1x ON t1(x > 0);
218 CREATE VIEW t2(y) AS SELECT avg(w) FROM t0 GROUP BY w>1;
219 CREATE VIEW t3(z) AS SELECT count(*) FROM t2 WHERE y BETWEEN 0 and 0;
220 SELECT count(*) FROM t1 NOT INDEXED WHERE (SELECT z FROM t3);
221 SELECT count(*) FROM t1 INDEXED BY t1x WHERE (SELECT z FROM t3);
222 } {0 0}
223
224 finish_test