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