AryaWu/sqlite
0
1# 2011 July 12#3# The author disclaims copyright to this source code. In place of4# 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. The12# focus of this script is the DISTINCT modifier.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17 18ifcapable !compound {19 finish_test20 return21}22 23set testprefix distinct24 25 26proc is_distinct_noop {sql} {27 set sql1 $sql28 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 41proc do_distinct_noop_test {tn sql} {42 uplevel [list do_test $tn [list is_distinct_noop $sql] 1]43}44proc do_distinct_not_noop_test {tn sql} {45 uplevel [list do_test $tn [list is_distinct_noop $sql] 0]46}47 48proc 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 hash56 } else {57 lappend ret btree58 }59 }60 }61 set ret62 }] $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 there70# is a single table in the FROM clause.71#72do_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}86foreach {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 DISTINCT120 ** is a no-op in cases like the following. But SQLite does not121 ** 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 $sql138 } else {139 do_distinct_not_noop_test 1.$tn $sql140 }141}142 143#-------------------------------------------------------------------------144# The following tests - distinct-2.* - test cases where an index is145# used to deliver results in order of the DISTINCT expressions. 146#147drop_all_tables148do_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 160foreach {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" $res172 do_temptables_test 2.$tn.2 "SELECT DISTINCT $sql" $temptables173}174 175do_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 179do_test 3.0 {180 db eval {181 CREATE TABLE t3(a INTEGER, b INTEGER, c, UNIQUE(a,b));182 INSERT INTO t3 VALUES183 (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 {}}192do_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#203do_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 t2215 SELECT DISTINCT216 CASE a WHEN 1 THEN x'0000000000'217 WHEN 2 THEN zeroblob(5)218 ELSE 'xyzzy' END219 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 descending226# indexes.227#228do_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}235do_execsql_test 5.2 {236 SELECT DISTINCT x FROM t1 ORDER BY x DESC;237} {6 5 4 3 2 1}238do_execsql_test 5.3 {239 SELECT DISTINCT x FROM t1 ORDER BY x;240} {1 2 3 4 5 6}241do_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}246do_execsql_test 5.5 {247 SELECT DISTINCT x FROM t1 ORDER BY x DESC;248} {6 5 4 3 2 1}249do_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 libFuzzer255#256db close257sqlite3 db :memory:258do_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}263do_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#272reset_db273do_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 286do_execsql_test 7.1 {287 WITH t2(b) AS (288 SELECT DISTINCT y FROM t5 ORDER BY y289 )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.a293} {2 2 2}294 295# 2021-04-06 forum post https://sqlite.org/forum/forumpost/66954e9ece296reset_db297do_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#-------------------------------------------------------------------------305reset_db306do_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 323foreach {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 $idx333 334 do_execsql_test 9.$tn.1 {335 SELECT DISTINCT a, b FROM t1 ORDER BY a, b336 } {337 A A B B338 a a a b a c339 b a b b b c340 }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 nocase345 } {346 a a a b a c347 b a b b b c348 }349}350 351# 2023-03-16352# https://sqlite.org/forum/forumpost/16ce2bb7a639e29b353# ticket c36cdb4afd504dc1354# ticket 4051a7f931d9ba24355# ticket d6fd512f50513ab7356#357do_execsql_test 10.1 {358 SELECT DISTINCT359 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,360 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,361 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,362 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,363 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,364 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,365 1, 1, 1, 1, 1366 ORDER BY367 'x','x','x','x','x','x','x','x','x','x',368 'x','x','x','x','x','x','x','x','x','x',369 'x','x','x','x','x','x','x','x','x','x',370 'x','x','x','x','x','x','x','x','x','x',371 'x','x','x','x','x','x','x','x','x','x',372 'x','x','x','x','x','x','x','x','x','x',373 'x','x','x','x';374} {1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1}375do_execsql_test 10.2 {376 EXPLAIN377 SELECT DISTINCT378 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,379 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,380 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,381 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,382 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,383 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,384 1, 1, 1, 1, 1385 ORDER BY386 'x','x','x','x','x','x','x','x','x','x',387 'x','x','x','x','x','x','x','x','x','x',388 'x','x','x','x','x','x','x','x','x','x',389 'x','x','x','x','x','x','x','x','x','x',390 'x','x','x','x','x','x','x','x','x','x',391 'x','x','x','x','x','x','x','x','x','x',392 'x','x','x','x';393} {/0 Init 0 /}394do_execsql_test 10.3 {395 EXPLAIN CREATE TABLE t2 AS SELECT DISTINCT ':memory:', 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7 ORDER BY '%J%j%w%s', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', '%J%j%w%s', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 42e-300, 'unixepoch', 'unixepoch', 'unixepoch' LIMIT 0xda;396} {/0 Init 0/}397do_execsql_test 10.4 {398 DROP TABLE IF EXISTS t0;399 CREATE TABLE t0 AS SELECT DISTINCT 0xda, 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 0xda-0xda-42e-300, 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0' ORDER BY '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%Y-%m-%d', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', 'lit0', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', 'auto', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', ':memory:', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '';400 SELECT count(*) FROM t0;401} {1}402do_execsql_test 10.5 {403 DROP TABLE IF EXISTS t2;404 CREATE TABLE t2 AS SELECT DISTINCT ':memory:', 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 0.0*7/0, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7 ORDER BY '%J%j%w%s', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', '%J%j%w%s', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 42e-300, 'unixepoch', 'unixepoch', 'unixepoch' LIMIT 0xda;405 SELECT count(*) FROM t2;406} {1}407 408finish_test409