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 # 2023-03-16
 352 # https://sqlite.org/forum/forumpost/16ce2bb7a639e29b
 353 # ticket c36cdb4afd504dc1
 354 # ticket 4051a7f931d9ba24
 355 # ticket d6fd512f50513ab7
 356 #
 357 do_execsql_test 10.1 {
 358   SELECT  DISTINCT
 359     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,  1
 366   ORDER  BY
 367    '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}
 375 do_execsql_test 10.2 {
 376   EXPLAIN
 377   SELECT  DISTINCT
 378     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,  1
 385   ORDER  BY
 386    '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 /}
 394 do_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/}
 397 do_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}
 402 do_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
 408 finish_test