gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/selectC.test (about) 1 # 2008 September 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 # This file implements regression tests for SQLite library. 12 # 13 # $Id: selectC.test,v 1.5 2009/05/17 15:26:21 drh Exp $ 14 15 set testdir [file dirname $argv0] 16 source $testdir/tester.tcl 17 set testprefix selectC 18 19 # Ticket # 20 do_test selectC-1.1 { 21 execsql { 22 CREATE TABLE t1(a, b, c); 23 INSERT INTO t1 VALUES(1,'aaa','bbb'); 24 INSERT INTO t1 SELECT * FROM t1; 25 INSERT INTO t1 VALUES(2,'ccc','ddd'); 26 27 SELECT DISTINCT a AS x, b||c AS y 28 FROM t1 29 WHERE y IN ('aaabbb','xxx'); 30 } 31 } {1 aaabbb} 32 do_test selectC-1.2 { 33 execsql { 34 SELECT DISTINCT a AS x, b||c AS y 35 FROM t1 36 WHERE b||c IN ('aaabbb','xxx'); 37 } 38 } {1 aaabbb} 39 do_test selectC-1.3 { 40 execsql { 41 SELECT DISTINCT a AS x, b||c AS y 42 FROM t1 43 WHERE y='aaabbb' 44 } 45 } {1 aaabbb} 46 do_test selectC-1.4 { 47 execsql { 48 SELECT DISTINCT a AS x, b||c AS y 49 FROM t1 50 WHERE b||c='aaabbb' 51 } 52 } {1 aaabbb} 53 do_test selectC-1.5 { 54 execsql { 55 SELECT DISTINCT a AS x, b||c AS y 56 FROM t1 57 WHERE x=2 58 } 59 } {2 cccddd} 60 do_test selectC-1.6 { 61 execsql { 62 SELECT DISTINCT a AS x, b||c AS y 63 FROM t1 64 WHERE a=2 65 } 66 } {2 cccddd} 67 do_test selectC-1.7 { 68 execsql { 69 SELECT DISTINCT a AS x, b||c AS y 70 FROM t1 71 WHERE +y='aaabbb' 72 } 73 } {1 aaabbb} 74 do_test selectC-1.8 { 75 execsql { 76 SELECT a AS x, b||c AS y 77 FROM t1 78 GROUP BY x, y 79 HAVING y='aaabbb' 80 } 81 } {1 aaabbb} 82 do_test selectC-1.9 { 83 execsql { 84 SELECT a AS x, b||c AS y 85 FROM t1 86 GROUP BY x, y 87 HAVING b||c='aaabbb' 88 } 89 } {1 aaabbb} 90 do_test selectC-1.10 { 91 execsql { 92 SELECT a AS x, b||c AS y 93 FROM t1 94 WHERE y='aaabbb' 95 GROUP BY x, y 96 } 97 } {1 aaabbb} 98 do_test selectC-1.11 { 99 execsql { 100 SELECT a AS x, b||c AS y 101 FROM t1 102 WHERE b||c='aaabbb' 103 GROUP BY x, y 104 } 105 } {1 aaabbb} 106 proc longname_toupper x {return [string toupper $x]} 107 db function uppercaseconversionfunctionwithaverylongname longname_toupper 108 do_test selectC-1.12.1 { 109 execsql { 110 SELECT DISTINCT upper(b) AS x 111 FROM t1 112 ORDER BY x 113 } 114 } {AAA CCC} 115 do_test selectC-1.12.2 { 116 execsql { 117 SELECT DISTINCT uppercaseconversionfunctionwithaverylongname(b) AS x 118 FROM t1 119 ORDER BY x 120 } 121 } {AAA CCC} 122 do_test selectC-1.13.1 { 123 execsql { 124 SELECT upper(b) AS x 125 FROM t1 126 GROUP BY x 127 ORDER BY x 128 } 129 } {AAA CCC} 130 do_test selectC-1.13.2 { 131 execsql { 132 SELECT uppercaseconversionfunctionwithaverylongname(b) AS x 133 FROM t1 134 GROUP BY x 135 ORDER BY x 136 } 137 } {AAA CCC} 138 do_test selectC-1.14.1 { 139 execsql { 140 SELECT upper(b) AS x 141 FROM t1 142 ORDER BY x DESC 143 } 144 } {CCC AAA AAA} 145 do_test selectC-1.14.2 { 146 execsql { 147 SELECT uppercaseconversionfunctionwithaverylongname(b) AS x 148 FROM t1 149 ORDER BY x DESC 150 } 151 } {CCC AAA AAA} 152 153 # The following query used to leak memory. Verify that has been fixed. 154 # 155 ifcapable trigger&&compound { 156 do_test selectC-2.1 { 157 catchsql { 158 CREATE TABLE t21a(a,b); 159 INSERT INTO t21a VALUES(1,2); 160 CREATE TABLE t21b(n); 161 CREATE TRIGGER r21 AFTER INSERT ON t21b BEGIN 162 SELECT a FROM t21a WHERE a>new.x UNION ALL 163 SELECT b FROM t21a WHERE b>new.x ORDER BY 1 LIMIT 2; 164 END; 165 INSERT INTO t21b VALUES(6); 166 } 167 } {1 {no such column: new.x}} 168 } 169 170 # Check that ticket [883034dcb5] is fixed. 171 # 172 do_test selectC-3.1 { 173 execsql { 174 CREATE TABLE person ( 175 org_id TEXT NOT NULL, 176 nickname TEXT NOT NULL, 177 license TEXT, 178 CONSTRAINT person_pk PRIMARY KEY (org_id, nickname), 179 CONSTRAINT person_license_uk UNIQUE (license) 180 ); 181 INSERT INTO person VALUES('meyers', 'jack', '2GAT123'); 182 INSERT INTO person VALUES('meyers', 'hill', 'V345FMP'); 183 INSERT INTO person VALUES('meyers', 'jim', '2GAT138'); 184 INSERT INTO person VALUES('smith', 'maggy', ''); 185 INSERT INTO person VALUES('smith', 'jose', 'JJZ109'); 186 INSERT INTO person VALUES('smith', 'jack', 'THX138'); 187 INSERT INTO person VALUES('lakeside', 'dave', '953OKG'); 188 INSERT INTO person VALUES('lakeside', 'amy', NULL); 189 INSERT INTO person VALUES('lake-apts', 'tom', NULL); 190 INSERT INTO person VALUES('acorn', 'hideo', 'CQB421'); 191 192 SELECT 193 org_id, 194 count((NOT (org_id IS NULL)) AND (NOT (nickname IS NULL))) 195 FROM person 196 WHERE (CASE WHEN license != '' THEN 1 ELSE 0 END) 197 GROUP BY 1; 198 } 199 } {acorn 1 lakeside 1 meyers 3 smith 2} 200 do_test selectC-3.2 { 201 execsql { 202 CREATE TABLE t2(a PRIMARY KEY, b); 203 INSERT INTO t2 VALUES('abc', 'xxx'); 204 INSERT INTO t2 VALUES('def', 'yyy'); 205 SELECT a, max(b || a) FROM t2 WHERE (b||b||b)!='value' GROUP BY a; 206 } 207 } {abc xxxabc def yyydef} 208 do_test selectC-3.3 { 209 execsql { 210 SELECT b, max(a || b) FROM t2 WHERE (b||b||b)!='value' GROUP BY a; 211 } 212 } {xxx abcxxx yyy defyyy} 213 214 215 proc udf {} { incr ::udf } 216 set ::udf 0 217 db function udf udf 218 219 do_execsql_test selectC-4.1 { 220 create table t_distinct_bug (a, b, c); 221 insert into t_distinct_bug values ('1', '1', 'a'); 222 insert into t_distinct_bug values ('1', '2', 'b'); 223 insert into t_distinct_bug values ('1', '3', 'c'); 224 insert into t_distinct_bug values ('1', '1', 'd'); 225 insert into t_distinct_bug values ('1', '2', 'e'); 226 insert into t_distinct_bug values ('1', '3', 'f'); 227 } {} 228 229 do_execsql_test selectC-4.2 { 230 select a from (select distinct a, b from t_distinct_bug) 231 } {1 1 1} 232 233 do_execsql_test selectC-4.3 { 234 select a, udf() from (select distinct a, b from t_distinct_bug) 235 } {1 1 1 2 1 3} 236 237 #------------------------------------------------------------------------- 238 # Test that the problem in ticket #190c2507 has been fixed. 239 # 240 do_execsql_test 5.0 { 241 CREATE TABLE x1(a); 242 CREATE TABLE x2(b); 243 CREATE TABLE x3(c); 244 CREATE VIEW vvv AS SELECT b FROM x2 ORDER BY 1; 245 246 INSERT INTO x1 VALUES('a'), ('b'); 247 INSERT INTO x2 VALUES(22), (23), (25), (24), (21); 248 INSERT INTO x3 VALUES(302), (303), (301); 249 } 250 251 do_execsql_test 5.1 { 252 CREATE TABLE x4 AS SELECT b FROM vvv UNION ALL SELECT c from x3; 253 SELECT * FROM x4; 254 } {21 22 23 24 25 302 303 301} 255 256 do_execsql_test 5.2 { 257 SELECT * FROM x1, x4 258 } { 259 a 21 a 22 a 23 a 24 a 25 a 302 a 303 a 301 260 b 21 b 22 b 23 b 24 b 25 b 302 b 303 b 301 261 } 262 263 do_execsql_test 5.3 { 264 SELECT * FROM x1, (SELECT b FROM vvv UNION ALL SELECT c from x3) ORDER BY 1,2; 265 } { 266 a 21 a 22 a 23 a 24 a 25 a 301 a 302 a 303 267 b 21 b 22 b 23 b 24 b 25 b 301 b 302 b 303 268 } 269 270 finish_test