| blob | ba9fdc70271e259d073651094119e8b3922bbc9f |
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 #
23 set testdir [file dirname $argv0]
24 source $testdir/tester.tcl
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 } {}
37 do_execsql_test tkt-99378-110 {
38 SELECT a,
39 SUM(1) AS t1,
40 SUM(CASE WHEN b->>'x'=1 THEN 1 END) AS t2,
41 SUM(c) AS t3,
42 SUM(CASE WHEN b->>'x'=1 THEN c END) AS t4
43 FROM t1
44 WHERE d BETWEEN 0 and 10
45 GROUP BY a;
46 } {
47 1 2 1 16 12
48 2 2 1 26 22
49 3 1 1 6 6
50 }
52 # The proof that the index on the expression is being used is in the
53 # fact that the byte code contains no "Function" opcodes. In other words,
54 # the ->> operator (which is implemented by a function) is never invoked.
55 # Instead, the b->>'x' value is pulled out of the index.
56 #
57 do_execsql_test tkt-99378-120 {
58 EXPLAIN
59 SELECT a,
60 SUM(1) AS t1,
61 SUM(CASE WHEN b->>'x'=1 THEN 1 END) AS t2,
62 SUM(c) AS t3,
63 SUM(CASE WHEN b->>'x'=1 THEN c END) AS t4
64 FROM t1
65 WHERE d BETWEEN 0 and 10
66 GROUP BY a;
67 } {~/Function/}
70 do_execsql_test tkt-99378-130 {
71 SELECT a,
72 SUM(1) AS t1,
73 SUM(CASE WHEN b->>'x'=1 THEN 1 END) AS t2,
74 SUM(c) AS t3,
75 SUM(CASE WHEN b->>'x'=1 THEN c END) AS t4
76 FROM t1
77 WHERE d BETWEEN 0 and 10
78 GROUP BY +a;
79 } {
80 1 2 1 16 12
81 2 2 1 26 22
82 3 1 1 6 6
83 }
84 do_execsql_test tkt-99378-140 {
85 EXPLAIN
86 SELECT a,
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 t1
92 WHERE d BETWEEN 0 and 10
93 GROUP BY +a;
94 } {~/Function/}
96 do_execsql_test tkt-99378-200 {
97 DROP INDEX t1x;
98 CREATE INDEX t1x ON t1(a, d, b->>'x', c);
99 }
100 do_execsql_test tkt-99378-210 {
101 SELECT a,
102 SUM(1) AS t1,
103 SUM(CASE WHEN b->>'x'=1 THEN 1 END) AS t2,
104 SUM(c) AS t3,
105 SUM(CASE WHEN b->>'x'=1 THEN c END) AS t4
106 FROM t1
107 WHERE d BETWEEN 0 and 10
108 GROUP BY a;
109 } {
110 1 2 1 16 12
111 2 2 1 26 22
112 3 1 1 6 6
113 }
114 do_execsql_test tkt-99378-220 {
115 EXPLAIN
116 SELECT a,
117 SUM(1) AS t1,
118 SUM(CASE WHEN b->>'x'=1 THEN 1 END) AS t2,
119 SUM(c) AS t3,
120 SUM(CASE WHEN b->>'x'=1 THEN c END) AS t4
121 FROM t1
122 WHERE d BETWEEN 0 and 10
123 GROUP BY a;
124 } {~/Function/}
125 do_execsql_test tkt-99378-230 {
126 SELECT a,
127 SUM(1) AS t1,
128 SUM(CASE WHEN b->>'x'=1 THEN 1 END) AS t2,
129 SUM(c) AS t3,
130 SUM(CASE WHEN b->>'x'=1 THEN c END) AS t4
131 FROM t1
132 WHERE d BETWEEN 0 and 10
133 GROUP BY a;
134 } {
135 1 2 1 16 12
136 2 2 1 26 22
137 3 1 1 6 6
138 }
139 do_execsql_test tkt-99378-240 {
140 EXPLAIN
141 SELECT a,
142 SUM(1) AS t1,
143 SUM(CASE WHEN b->>'x'=1 THEN 1 END) AS t2,
144 SUM(c) AS t3,
145 SUM(CASE WHEN b->>'x'=1 THEN c END) AS t4
146 FROM t1
147 WHERE d BETWEEN 0 and 10
148 GROUP BY a;
149 } {~/Function/}
151 # 2022-12-20 dbsqlfuzz a644e70d7683a7ca59c71861a153c1dccf8850b9
152 #
153 do_execsql_test tkt-99378-300 {
154 DROP TABLE IF EXISTS t1;
155 CREATE TABLE t1(a INT);
156 CREATE INDEX i1 ON t1(a,a=a);
157 INSERT INTO t1 VALUES(1),(2),(3),(4);
158 SELECT * FROM t1 NATURAL JOIN t1
159 WHERE a==1
160 OR (
161 (SELECT avg(
162 (SELECT sum((SELECT 1 FROM t1 NATURAL RIGHT JOIN t1 WHERE a=a)))) AS xyz
163 )
164 AND a==2
165 );
166 } {1 2}
167 do_execsql_test tkt-99378-310 {
168 DROP INDEX i1;
169 SELECT * FROM t1 NATURAL JOIN t1
170 WHERE a==1
171 OR (
172 (SELECT avg(
173 (SELECT sum((SELECT 1 FROM t1 NATURAL RIGHT JOIN t1 WHERE a=a)))) AS xyz
174 )
175 AND a==2
176 );
177 } {1 2}
179 # 2023-03-04 https://sqlite.org/forum/forumpost/a68313d054
180 #
181 # See also indexexpr1-2200 added on 2023-03-18.
182 #
183 do_execsql_test tkt-99378-400 {
184 DROP TABLE t1;
185 CREATE TABLE t0(w);
186 INSERT INTO t0(w) VALUES(1);
187 CREATE TABLE t1(x);
188 INSERT INTO t1(x) VALUES(1);
189 CREATE INDEX t1x ON t1(x > 0);
190 CREATE VIEW t2(y) AS SELECT avg(w) FROM t0 GROUP BY w>1;
191 CREATE VIEW t3(z) AS SELECT count(*) FROM t2 WHERE y BETWEEN 0 and 0;
192 SELECT count(*) FROM t1 NOT INDEXED WHERE (SELECT z FROM t3);
193 SELECT count(*) FROM t1 INDEXED BY t1x WHERE (SELECT z FROM t3);
194 } {0 0}
196 finish_test