test/distinct.test

   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