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
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
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
|
