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 != "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# 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, c2); 81 CREATE INDEX i3 ON t3(c2); 82} 83foreach {tn noop sql} { 84 85 1 1 "SELECT DISTINCT b, c FROM t1" 86 2 1 "SELECT DISTINCT c FROM t1 WHERE b = ?" 87 3 1 "SELECT DISTINCT rowid FROM t1" 88 4 1 "SELECT DISTINCT rowid, a FROM t1" 89 5 1 "SELECT DISTINCT x FROM t2" 90 6 1 "SELECT DISTINCT * FROM t2" 91 7 1 "SELECT DISTINCT * FROM (SELECT * FROM t2)" 92 93 8 1 "SELECT DISTINCT * FROM t1" 94 95 8 0 "SELECT DISTINCT a, b FROM t1" 96 97 9 0 "SELECT DISTINCT c FROM t1 WHERE b IN (1,2)" 98 10 0 "SELECT DISTINCT c FROM t1" 99 11 0 "SELECT DISTINCT b FROM t1" 100 101 12 0 "SELECT DISTINCT a, d FROM t1" 102 13 0 "SELECT DISTINCT a, b, c COLLATE nocase FROM t1" 103 14 1 "SELECT DISTINCT a, d COLLATE nocase FROM t1" 104 15 0 "SELECT DISTINCT a, d COLLATE binary FROM t1" 105 16 1 "SELECT DISTINCT a, b, c COLLATE binary FROM t1" 106 107 16 0 "SELECT DISTINCT t1.rowid FROM t1, t2" 108 17 0 { /* Technically, it would be possible to detect that DISTINCT 109 ** is a no-op in cases like the following. But SQLite does not 110 ** do so. */ 111 SELECT DISTINCT t1.rowid FROM t1, t2 WHERE t1.rowid=t2.rowid } 112 113 18 1 "SELECT DISTINCT c1, c2 FROM t3" 114 19 1 "SELECT DISTINCT c1 FROM t3" 115 20 1 "SELECT DISTINCT * FROM t3" 116 21 0 "SELECT DISTINCT c2 FROM t3" 117 118 22 0 "SELECT DISTINCT * FROM (SELECT 1, 2, 3 UNION SELECT 4, 5, 6)" 119 23 1 "SELECT DISTINCT rowid FROM (SELECT 1, 2, 3 UNION SELECT 4, 5, 6)" 120 121 24 0 "SELECT DISTINCT rowid/2 FROM t1" 122 25 1 "SELECT DISTINCT rowid/2, rowid FROM t1" 123 26 1 "SELECT DISTINCT rowid/2, b FROM t1 WHERE c = ?" 124} { 125 if {$noop} { 126 do_distinct_noop_test 1.$tn $sql 127 } else { 128 do_distinct_not_noop_test 1.$tn $sql 129 } 130} 131 132#------------------------------------------------------------------------- 133# The following tests - distinct-2.* - test cases where an index is 134# used to deliver results in order of the DISTINCT expressions. 135# 136drop_all_tables 137do_execsql_test 2.0 { 138 CREATE TABLE t1(a, b, c); 139 140 CREATE INDEX i1 ON t1(a, b); 141 CREATE INDEX i2 ON t1(b COLLATE nocase, c COLLATE nocase); 142 143 INSERT INTO t1 VALUES('a', 'b', 'c'); 144 INSERT INTO t1 VALUES('A', 'B', 'C'); 145 INSERT INTO t1 VALUES('a', 'b', 'c'); 146 INSERT INTO t1 VALUES('A', 'B', 'C'); 147} 148 149foreach {tn sql temptables res} { 150 1 "a, b FROM t1" {} {A B a b} 151 2 "b, a FROM t1" {} {B A b a} 152 3 "a, b, c FROM t1" {hash} {a b c A B C} 153 4 "a, b, c FROM t1 ORDER BY a, b, c" {btree} {A B C a b c} 154 5 "b FROM t1 WHERE a = 'a'" {} {b} 155 6 "b FROM t1" {hash} {b B} 156 7 "a FROM t1" {} {A a} 157 8 "b COLLATE nocase FROM t1" {} {b} 158 9 "b COLLATE nocase FROM t1 ORDER BY b COLLATE nocase" {} {B} 159} { 160 do_execsql_test 2.$tn.1 "SELECT DISTINCT $sql" $res 161 do_temptables_test 2.$tn.2 "SELECT DISTINCT $sql" $temptables 162} 163 164do_execsql_test 2.A { 165 SELECT (SELECT DISTINCT o.a FROM t1 AS i) FROM t1 AS o; 166} {a A a A} 167 168 169 170 171finish_test 172