gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/distinct.test (about) 1 # 2011 July 1 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. The 12 # focus of this script is the DISTINCT modifier. 13 # 14 15 set testdir [file dirname $argv0] 16 source $testdir/tester.tcl 17 18 ifcapable !compound { 19 finish_test 20 return 21 } 22 23 set testprefix distinct 24 25 26 proc is_distinct_noop {sql} { 27 set sql1 $sql 28 set sql2 [string map {DISTINCT ""} $sql] 29 30 set program1 [list] 31 set program2 [list] 32 db eval "EXPLAIN $sql1" { 33 if {$opcode != "Noop" && $opcode != "Explain"} { lappend program1 $opcode } 34 } 35 db eval "EXPLAIN $sql2" { 36 if {$opcode != "Noop" && $opcode != "Explain"} { lappend program2 $opcode } 37 } 38 return [expr {$program1==$program2}] 39 } 40 41 proc do_distinct_noop_test {tn sql} { 42 uplevel [list do_test $tn [list is_distinct_noop $sql] 1] 43 } 44 proc do_distinct_not_noop_test {tn sql} { 45 uplevel [list do_test $tn [list is_distinct_noop $sql] 0] 46 } 47 48 proc do_temptables_test {tn sql temptables} { 49 uplevel [list do_test $tn [subst -novar { 50 set ret "" 51 db eval "EXPLAIN [set sql]" { 52 if {$opcode == "OpenEphemeral" || $opcode == "SorterOpen"} { 53 if {$p5!=8 && $p5!=0} { error "p5 = $p5" } 54 if {$p5==8} { 55 lappend ret hash 56 } else { 57 lappend ret btree 58 } 59 } 60 } 61 set ret 62 }] $temptables] 63 } 64 65 66 #------------------------------------------------------------------------- 67 # The following tests - distinct-1.* - check that the planner correctly 68 # detects cases where a UNIQUE index means that a DISTINCT clause is 69 # redundant. Currently the planner only detects such cases when there 70 # is a single table in the FROM clause. 71 # 72 do_execsql_test 1.0 { 73 CREATE TABLE t1(a, b, c, d); 74 CREATE UNIQUE INDEX i1 ON t1(b, c); 75 CREATE UNIQUE INDEX i2 ON t1(d COLLATE nocase); 76 77 CREATE TABLE t2(x INTEGER PRIMARY KEY, y); 78 79 CREATE TABLE t3(c1 PRIMARY KEY NOT NULL, c2 NOT NULL); 80 CREATE INDEX i3 ON t3(c2); 81 82 CREATE TABLE t4(a, b NOT NULL, c NOT NULL, d NOT NULL); 83 CREATE UNIQUE INDEX t4i1 ON t4(b, c); 84 CREATE UNIQUE INDEX t4i2 ON t4(d COLLATE nocase); 85 } 86 foreach {tn noop sql} { 87 88 1.1 0 "SELECT DISTINCT b, c FROM t1" 89 1.2 1 "SELECT DISTINCT b, c FROM t4" 90 2.1 0 "SELECT DISTINCT c FROM t1 WHERE b = ?" 91 2.2 1 "SELECT DISTINCT c FROM t4 WHERE b = ?" 92 3 1 "SELECT DISTINCT rowid FROM t1" 93 4 1 "SELECT DISTINCT rowid, a FROM t1" 94 5 1 "SELECT DISTINCT x FROM t2" 95 6 1 "SELECT DISTINCT * FROM t2" 96 7 1 "SELECT DISTINCT * FROM (SELECT * FROM t2)" 97 98 8.1 0 "SELECT DISTINCT * FROM t1" 99 8.2 1 "SELECT DISTINCT * FROM t4" 100 101 8 0 "SELECT DISTINCT a, b FROM t1" 102 103 9 0 "SELECT DISTINCT c FROM t1 WHERE b IN (1,2)" 104 10 0 "SELECT DISTINCT c FROM t1" 105 11 0 "SELECT DISTINCT b FROM t1" 106 107 12.1 0 "SELECT DISTINCT a, d FROM t1" 108 12.2 0 "SELECT DISTINCT a, d FROM t4" 109 13.1 0 "SELECT DISTINCT a, b, c COLLATE nocase FROM t1" 110 13.2 0 "SELECT DISTINCT a, b, c COLLATE nocase FROM t4" 111 14.1 0 "SELECT DISTINCT a, d COLLATE nocase FROM t1" 112 14.2 1 "SELECT DISTINCT a, d COLLATE nocase FROM t4" 113 114 15 0 "SELECT DISTINCT a, d COLLATE binary FROM t1" 115 16.1 0 "SELECT DISTINCT a, b, c COLLATE binary FROM t1" 116 16.2 1 "SELECT DISTINCT a, b, c COLLATE binary FROM t4" 117 118 16 0 "SELECT DISTINCT t1.rowid FROM t1, t2" 119 17 0 { /* Technically, it would be possible to detect that DISTINCT 120 ** is a no-op in cases like the following. But SQLite does not 121 ** do so. */ 122 SELECT DISTINCT t1.rowid FROM t1, t2 WHERE t1.rowid=t2.rowid } 123 124 18 1 "SELECT DISTINCT c1, c2 FROM t3" 125 19 1 "SELECT DISTINCT c1 FROM t3" 126 20 1 "SELECT DISTINCT * FROM t3" 127 21 0 "SELECT DISTINCT c2 FROM t3" 128 129 22 0 "SELECT DISTINCT * FROM (SELECT 1, 2, 3 UNION SELECT 4, 5, 6)" 130 131 24 0 "SELECT DISTINCT rowid/2 FROM t1" 132 25 1 "SELECT DISTINCT rowid/2, rowid FROM t1" 133 26.1 0 "SELECT DISTINCT rowid/2, b FROM t1 WHERE c = ?" 134 26.2 1 "SELECT DISTINCT rowid/2, b FROM t4 WHERE c = ?" 135 } { 136 if {$noop} { 137 do_distinct_noop_test 1.$tn $sql 138 } else { 139 do_distinct_not_noop_test 1.$tn $sql 140 } 141 } 142 143 #------------------------------------------------------------------------- 144 # The following tests - distinct-2.* - test cases where an index is 145 # used to deliver results in order of the DISTINCT expressions. 146 # 147 drop_all_tables 148 do_execsql_test 2.0 { 149 CREATE TABLE t1(a, b, c); 150 151 CREATE INDEX i1 ON t1(a, b); 152 CREATE INDEX i2 ON t1(b COLLATE nocase, c COLLATE nocase); 153 154 INSERT INTO t1 VALUES('a', 'b', 'c'); 155 INSERT INTO t1 VALUES('A', 'B', 'C'); 156 INSERT INTO t1 VALUES('a', 'b', 'c'); 157 INSERT INTO t1 VALUES('A', 'B', 'C'); 158 } 159 160 foreach {tn sql temptables res} { 161 1 "a, b FROM t1" {} {A B a b} 162 2 "b, a FROM t1" {} {B A b a} 163 3 "a, b, c FROM t1" {hash} {A B C a b c} 164 4 "a, b, c FROM t1 ORDER BY a, b, c" {btree} {A B C a b c} 165 5 "b FROM t1 WHERE a = 'a'" {} {b} 166 6 "b FROM t1 ORDER BY +b COLLATE binary" {btree hash} {B b} 167 7 "a FROM t1" {} {A a} 168 8 "b COLLATE nocase FROM t1" {} {b} 169 9 "b COLLATE nocase FROM t1 ORDER BY b COLLATE nocase" {} {b} 170 } { 171 do_execsql_test 2.$tn.1 "SELECT DISTINCT $sql" $res 172 do_temptables_test 2.$tn.2 "SELECT DISTINCT $sql" $temptables 173 } 174 175 do_execsql_test 2.A { 176 SELECT (SELECT DISTINCT o.a FROM t1 AS i) FROM t1 AS o ORDER BY rowid; 177 } {a A a A} 178 179 do_test 3.0 { 180 db eval { 181 CREATE TABLE t3(a INTEGER, b INTEGER, c, UNIQUE(a,b)); 182 INSERT INTO t3 VALUES 183 (null, null, 1), 184 (null, null, 2), 185 (null, 3, 4), 186 (null, 3, 5), 187 (6, null, 7), 188 (6, null, 8); 189 SELECT DISTINCT a, b FROM t3 ORDER BY +a, +b; 190 } 191 } {{} {} {} 3 6 {}} 192 do_test 3.1 { 193 regexp {OpenEphemeral} [db eval { 194 EXPLAIN SELECT DISTINCT a, b FROM t3 ORDER BY +a, +b; 195 }] 196 } {0} 197 198 #------------------------------------------------------------------------- 199 # Ticket [fccbde530a6583bf2748400919f1603d5425995c] (2014-01-08) 200 # The logic that computes DISTINCT sometimes thinks that a zeroblob() 201 # and a blob of all zeros are different when they should be the same. 202 # 203 do_execsql_test 4.1 { 204 DROP TABLE IF EXISTS t1; 205 DROP TABLE IF EXISTS t2; 206 CREATE TABLE t1(a INTEGER); 207 INSERT INTO t1 VALUES(3); 208 INSERT INTO t1 VALUES(2); 209 INSERT INTO t1 VALUES(1); 210 INSERT INTO t1 VALUES(2); 211 INSERT INTO t1 VALUES(3); 212 INSERT INTO t1 VALUES(1); 213 CREATE TABLE t2(x); 214 INSERT INTO t2 215 SELECT DISTINCT 216 CASE a WHEN 1 THEN x'0000000000' 217 WHEN 2 THEN zeroblob(5) 218 ELSE 'xyzzy' END 219 FROM t1; 220 SELECT quote(x) FROM t2 ORDER BY 1; 221 } {'xyzzy' X'0000000000'} 222 223 #---------------------------------------------------------------------------- 224 # Ticket [c5ea805691bfc4204b1cb9e9aa0103bd48bc7d34] (2014-12-04) 225 # Make sure that DISTINCT works together with ORDER BY and descending 226 # indexes. 227 # 228 do_execsql_test 5.1 { 229 DROP TABLE IF EXISTS t1; 230 CREATE TABLE t1(x); 231 INSERT INTO t1(x) VALUES(3),(1),(5),(2),(6),(4),(5),(1),(3); 232 CREATE INDEX t1x ON t1(x DESC); 233 SELECT DISTINCT x FROM t1 ORDER BY x ASC; 234 } {1 2 3 4 5 6} 235 do_execsql_test 5.2 { 236 SELECT DISTINCT x FROM t1 ORDER BY x DESC; 237 } {6 5 4 3 2 1} 238 do_execsql_test 5.3 { 239 SELECT DISTINCT x FROM t1 ORDER BY x; 240 } {1 2 3 4 5 6} 241 do_execsql_test 5.4 { 242 DROP INDEX t1x; 243 CREATE INDEX t1x ON t1(x ASC); 244 SELECT DISTINCT x FROM t1 ORDER BY x ASC; 245 } {1 2 3 4 5 6} 246 do_execsql_test 5.5 { 247 SELECT DISTINCT x FROM t1 ORDER BY x DESC; 248 } {6 5 4 3 2 1} 249 do_execsql_test 5.6 { 250 SELECT DISTINCT x FROM t1 ORDER BY x; 251 } {1 2 3 4 5 6} 252 253 #------------------------------------------------------------------------- 254 # 2015-11-23. Problem discovered by Kostya Serebryany using libFuzzer 255 # 256 db close 257 sqlite3 db :memory: 258 do_execsql_test 6.1 { 259 CREATE TABLE jjj(x); 260 SELECT (SELECT 'mmm' UNION SELECT DISTINCT max(name) ORDER BY 1) 261 FROM sqlite_master; 262 } {jjj} 263 do_execsql_test 6.2 { 264 CREATE TABLE nnn(x); 265 SELECT (SELECT 'mmm' UNION SELECT DISTINCT max(name) ORDER BY 1) 266 FROM sqlite_master; 267 } {mmm} 268 269 #------------------------------------------------------------------------- 270 # Ticket [9c944882] 271 # 272 reset_db 273 do_execsql_test 7.0 { 274 CREATE TABLE t1(a INTEGER PRIMARY KEY); 275 CREATE TABLE t3(a INTEGER PRIMARY KEY); 276 277 CREATE TABLE t4(x); 278 CREATE TABLE t5(y); 279 280 INSERT INTO t5 VALUES(1), (2), (2); 281 INSERT INTO t1 VALUES(2); 282 INSERT INTO t3 VALUES(2); 283 INSERT INTO t4 VALUES(2); 284 } 285 286 do_execsql_test 7.1 { 287 WITH t2(b) AS ( 288 SELECT DISTINCT y FROM t5 ORDER BY y 289 ) 290 SELECT * FROM 291 t4 CROSS JOIN t3 CROSS JOIN t1 292 WHERE (t1.a=t3.a) AND (SELECT count(*) FROM t2 AS y WHERE t4.x!='abc')=t1.a 293 } {2 2 2} 294 295 # 2021-04-06 forum post https://sqlite.org/forum/forumpost/66954e9ece 296 reset_db 297 do_execsql_test 8.0 { 298 CREATE TABLE person ( pid INT) ; 299 CREATE UNIQUE INDEX idx ON person ( pid ) WHERE pid == 1; 300 INSERT INTO person VALUES (1), (10), (10); 301 SELECT DISTINCT pid FROM person where pid = 10; 302 } {10} 303 304 #------------------------------------------------------------------------- 305 reset_db 306 do_execsql_test 9.0 { 307 CREATE TABLE t1(a, b); 308 INSERT INTO t1 VALUES('a', 'a'); 309 INSERT INTO t1 VALUES('a', 'b'); 310 INSERT INTO t1 VALUES('a', 'c'); 311 312 INSERT INTO t1 VALUES('b', 'a'); 313 INSERT INTO t1 VALUES('b', 'b'); 314 INSERT INTO t1 VALUES('b', 'c'); 315 316 INSERT INTO t1 VALUES('a', 'a'); 317 INSERT INTO t1 VALUES('b', 'b'); 318 319 INSERT INTO t1 VALUES('A', 'A'); 320 INSERT INTO t1 VALUES('B', 'B'); 321 } 322 323 foreach {tn idx} { 324 1 { } 325 2 { CREATE INDEX i1 ON t1(a, b); } 326 3 { CREATE INDEX i1 ON t1(b, a); } 327 4 { CREATE INDEX i1 ON t1(a COLLATE nocase, b COLLATE nocase); } 328 5 { CREATE INDEX i1 ON t1(b COLLATE nocase, a COLLATE nocase); } 329 } { 330 331 execsql { DROP INDEX IF EXISTS i1 } 332 execsql $idx 333 334 do_execsql_test 9.$tn.1 { 335 SELECT DISTINCT a, b FROM t1 ORDER BY a, b 336 } { 337 A A B B 338 a a a b a c 339 b a b b b c 340 } 341 342 do_execsql_test 9.$tn.1 { 343 SELECT DISTINCT a COLLATE nocase, b COLLATE nocase FROM t1 344 ORDER BY a COLLATE nocase, b COLLATE nocase 345 } { 346 a a a b a c 347 b a b b b c 348 } 349 } 350 351 352 finish_test