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