| OLD | NEW |
|---|
| (Empty) |
| 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"} { 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 | |
| 42 proc do_distinct_noop_test {tn sql} { | |
| 43 uplevel [list do_test $tn [list is_distinct_noop $sql] 1] | |
| 44 } | |
| 45 proc do_distinct_not_noop_test {tn sql} { | |
| 46 uplevel [list do_test $tn [list is_distinct_noop $sql] 0] | |
| 47 } | |
| 48 | |
| 49 proc 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 != "08" && $p5!="00"} { error "p5 = $p5" } | |
| 55 if {$p5 == "08"} { | |
| 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 # | |
| 73 do_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 } | |
| 87 foreach {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 # | |
| 149 drop_all_tables | |
| 150 do_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 | |
| 162 foreach {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 | |
| 177 do_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 | |
| 181 do_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 {}} | |
| 194 do_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 # | |
| 205 do_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 # | |
| 230 do_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} | |
| 237 do_execsql_test 5.2 { | |
| 238 SELECT DISTINCT x FROM t1 ORDER BY x DESC; | |
| 239 } {6 5 4 3 2 1} | |
| 240 do_execsql_test 5.3 { | |
| 241 SELECT DISTINCT x FROM t1 ORDER BY x; | |
| 242 } {1 2 3 4 5 6} | |
| 243 do_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} | |
| 248 do_execsql_test 5.5 { | |
| 249 SELECT DISTINCT x FROM t1 ORDER BY x DESC; | |
| 250 } {6 5 4 3 2 1} | |
| 251 do_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 # | |
| 258 db close | |
| 259 sqlite3 db :memory: | |
| 260 do_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} | |
| 265 do_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 finish_test | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2846743003:
[sql] Remove SQLite 3.10.2 reference directory. (Closed)
