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" && $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
  41 proc do_distinct_noop_test {tn sql} {
  42   uplevel [list do_test $tn [list is_distinct_noop $sql] 1]
  43 }
  44 proc do_distinct_not_noop_test {tn sql} {
  45   uplevel [list do_test $tn [list is_distinct_noop $sql] 0]
  46 }
  47
  48 proc 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 hash
  56         } else {
  57           lappend ret btree
  58         }
  59       }
  60     }
  61     set ret
  62   }] $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 there
  70 # is a single table in the FROM clause.
  71 #
  72 do_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 }
  86 foreach {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 DISTINCT
 120             ** is a no-op in cases like the following. But SQLite does not
 121             ** 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 $sql
 138   } else {
 139     do_distinct_not_noop_test 1.$tn $sql
 140   }
 141 }
 142
 143 #-------------------------------------------------------------------------
 144 # The following tests - distinct-2.* - test cases where an index is
 145 # used to deliver results in order of the DISTINCT expressions.
 146 #
 147 drop_all_tables
 148 do_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
 160 foreach {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" $res
 172   do_temptables_test 2.$tn.2 "SELECT DISTINCT $sql" $temptables
 173 }
 174
 175 do_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
 179 do_test 3.0 {
 180   db eval {
 181     CREATE TABLE t3(a INTEGER, b INTEGER, c, UNIQUE(a,b));
 182     INSERT INTO t3 VALUES
 183         (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 {}}
 192 do_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 #
 203 do_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 t2
 215     SELECT DISTINCT
 216       CASE a WHEN 1 THEN x'0000000000'
 217              WHEN 2 THEN zeroblob(5)
 218              ELSE 'xyzzy' END
 219       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 descending
 226 # indexes.
 227 #
 228 do_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}
 235 do_execsql_test 5.2 {
 236   SELECT DISTINCT x FROM t1 ORDER BY x DESC;
 237 } {6 5 4 3 2 1}
 238 do_execsql_test 5.3 {
 239   SELECT DISTINCT x FROM t1 ORDER BY x;
 240 } {1 2 3 4 5 6}
 241 do_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}
 246 do_execsql_test 5.5 {
 247   SELECT DISTINCT x FROM t1 ORDER BY x DESC;
 248 } {6 5 4 3 2 1}
 249 do_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 libFuzzer
 255 #
 256 db close
 257 sqlite3 db :memory:
 258 do_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}
 263 do_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 #
 272 reset_db
 273 do_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
 286 do_execsql_test 7.1 {
 287   WITH t2(b) AS (
 288     SELECT DISTINCT y FROM t5 ORDER BY y
 289   )
 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.a
 293 } {2 2 2}
 294
 295 # 2021-04-06 forum post https://sqlite.org/forum/forumpost/66954e9ece
 296 reset_db
 297 do_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 #-------------------------------------------------------------------------
 305 reset_db
 306 do_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
 323 foreach {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 $idx
 333
 334   do_execsql_test 9.$tn.1 {
 335     SELECT DISTINCT a, b FROM t1 ORDER BY a, b
 336   } {
 337     A A  B B
 338     a a  a b  a c
 339     b a  b b  b c
 340   }
 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 nocase
 345   } {
 346     a a  a b  a c
 347     b a  b b  b c
 348   }
 349 }
 350
 351
 352 finish_test