test/distinct2.test

   1 # 2016-04-15
   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 DISTINCT queries using the skip-ahead
  13 # optimization.
  14 #
  15
  16 set testdir [file dirname $argv0]
  17 source $testdir/tester.tcl
  18
  19 set testprefix distinct2
  20
  21 do_execsql_test 100 {
  22   CREATE TABLE t1(x INTEGER PRIMARY KEY);
  23   INSERT INTO t1 VALUES(0),(1),(2);
  24   CREATE TABLE t2 AS
  25      SELECT DISTINCT a.x AS aa, b.x AS bb
  26       FROM t1 a, t1 b;
  27   SELECT *, '|' FROM t2 ORDER BY aa, bb;
  28 } {0 0 | 0 1 | 0 2 | 1 0 | 1 1 | 1 2 | 2 0 | 2 1 | 2 2 |}
  29 do_execsql_test 110 {
  30   DROP TABLE t2;
  31   CREATE TABLE t2 AS
  32      SELECT DISTINCT a.x AS aa, b.x AS bb
  33        FROM t1 a, t1 b
  34       WHERE a.x IN t1 AND b.x IN t1;
  35   SELECT *, '|' FROM t2 ORDER BY aa, bb;
  36 } {0 0 | 0 1 | 0 2 | 1 0 | 1 1 | 1 2 | 2 0 | 2 1 | 2 2 |}
  37 do_execsql_test 120 {
  38   CREATE TABLE t102 (i0 TEXT UNIQUE NOT NULL);
  39   INSERT INTO t102 VALUES ('0'),('1'),('2');
  40   DROP TABLE t2;
  41   CREATE TABLE t2 AS
  42     SELECT DISTINCT *
  43     FROM t102 AS t0
  44     JOIN t102 AS t4 ON (t2.i0 IN t102)
  45     NATURAL JOIN t102 AS t3
  46     JOIN t102 AS t1 ON (t0.i0 IN t102)
  47     JOIN t102 AS t2 ON (t2.i0=+t0.i0 OR (t0.i0<>500 AND t2.i0=t1.i0));
  48   SELECT *, '|' FROM t2 ORDER BY 1, 2, 3, 4, 5;
  49 } {0 0 0 0 | 0 0 1 0 | 0 0 1 1 | 0 0 2 0 | 0 0 2 2 | 0 1 0 0 | 0 1 1 0 | 0 1 1 1 | 0 1 2 0 | 0 1 2 2 | 0 2 0 0 | 0 2 1 0 | 0 2 1 1 | 0 2 2 0 | 0 2 2 2 | 1 0 0 0 | 1 0 0 1 | 1 0 1 1 | 1 0 2 1 | 1 0 2 2 | 1 1 0 0 | 1 1 0 1 | 1 1 1 1 | 1 1 2 1 | 1 1 2 2 | 1 2 0 0 | 1 2 0 1 | 1 2 1 1 | 1 2 2 1 | 1 2 2 2 | 2 0 0 0 | 2 0 0 2 | 2 0 1 1 | 2 0 1 2 | 2 0 2 2 | 2 1 0 0 | 2 1 0 2 | 2 1 1 1 | 2 1 1 2 | 2 1 2 2 | 2 2 0 0 | 2 2 0 2 | 2 2 1 1 | 2 2 1 2 | 2 2 2 2 |}
  50
  51 do_execsql_test 400 {
  52   CREATE TABLE t4(a,b,c,d,e,f,g,h,i,j);
  53   INSERT INTO t4 VALUES(0,1,2,3,4,5,6,7,8,9);
  54   INSERT INTO t4 SELECT * FROM t4;
  55   INSERT INTO t4 SELECT * FROM t4;
  56   CREATE INDEX t4x ON t4(c,d,e);
  57   SELECT DISTINCT a,b,c FROM t4 WHERE a=0 AND b=1;
  58 } {0 1 2}
  59 do_execsql_test 410 {
  60   SELECT DISTINCT a,b,c,d FROM t4 WHERE a=0 AND b=1;
  61 } {0 1 2 3}
  62 do_execsql_test 411 {
  63   SELECT DISTINCT d,a,b,c FROM t4 WHERE a=0 AND b=1;
  64 } {3 0 1 2}
  65 do_execsql_test 420 {
  66   SELECT DISTINCT a,b,c,d,e FROM t4 WHERE a=0 AND b=1;
  67 } {0 1 2 3 4}
  68 do_execsql_test 430 {
  69   SELECT DISTINCT a,b,c,d,e,f FROM t4 WHERE a=0 AND b=1;
  70 } {0 1 2 3 4 5}
  71
  72 do_execsql_test 500 {
  73   CREATE TABLE t5(a INT, b INT);
  74   CREATE UNIQUE INDEX t5x ON t5(a+b);
  75   INSERT INTO t5(a,b) VALUES(0,0),(1,0),(1,1),(0,3);
  76   CREATE TEMP TABLE out AS SELECT DISTINCT a+b FROM t5;
  77   SELECT * FROM out ORDER BY 1;
  78 } {0 1 2 3}
  79
  80 do_execsql_test 600 {
  81   CREATE TABLE t6a(x INTEGER PRIMARY KEY);
  82   INSERT INTO t6a VALUES(1);
  83   CREATE TABLE t6b(y INTEGER PRIMARY KEY);
  84   INSERT INTO t6b VALUES(2),(3);
  85   SELECT DISTINCT x, x FROM t6a, t6b;
  86 } {1 1}
  87
  88 do_execsql_test 700 {
  89   CREATE TABLE t7(a, b, c);
  90   WITH s(i) AS (
  91     SELECT 1 UNION ALL SELECT i+1 FROM s WHERE (i+1)<200
  92   )
  93   INSERT INTO t7 SELECT i/100, i/50, i FROM s;
  94 }
  95 do_execsql_test 710 {
  96   SELECT DISTINCT a, b FROM t7;
  97 } {
  98   0 0    0 1
  99   1 2    1 3
 100 }
 101 do_execsql_test 720 {
 102   SELECT DISTINCT a, b+1 FROM t7;
 103 } {
 104   0 1    0 2
 105   1 3    1 4
 106 }
 107 do_execsql_test 730 {
 108   CREATE INDEX i7 ON t7(a, b+1);
 109   ANALYZE;
 110   SELECT DISTINCT a, b+1 FROM t7;
 111 } {
 112   0 1    0 2
 113   1 3    1 4
 114 }
 115
 116 do_execsql_test 800 {
 117   CREATE TABLE t8(a, b, c);
 118   WITH s(i) AS (
 119     SELECT 1 UNION ALL SELECT i+1 FROM s WHERE (i+1)<100
 120   )
 121   INSERT INTO t8 SELECT i/40, i/20, i/40 FROM s;
 122 }
 123
 124 do_execsql_test 820 {
 125   SELECT DISTINCT a, b, c FROM t8;
 126 } {
 127   0 0 0    0 1 0
 128   1 2 1    1 3 1
 129   2 4 2
 130 }
 131
 132 do_execsql_test 820 {
 133   SELECT DISTINCT a, b, c FROM t8 WHERE b=3;
 134 } {1 3 1}
 135
 136 do_execsql_test 830 {
 137   CREATE INDEX i8 ON t8(a, c);
 138   ANALYZE;
 139   SELECT DISTINCT a, b, c FROM t8 WHERE b=3;
 140 } {1 3 1}
 141
 142 do_execsql_test 900 {
 143   CREATE TABLE t9(v);
 144   INSERT INTO t9 VALUES
 145     ('abcd'), ('Abcd'), ('aBcd'), ('ABcd'), ('abCd'), ('AbCd'), ('aBCd'),
 146     ('ABCd'), ('abcD'), ('AbcD'), ('aBcD'), ('ABcD'), ('abCD'), ('AbCD'),
 147     ('aBCD'), ('ABCD'),
 148     ('wxyz'), ('Wxyz'), ('wXyz'), ('WXyz'), ('wxYz'), ('WxYz'), ('wXYz'),
 149     ('WXYz'), ('wxyZ'), ('WxyZ'), ('wXyZ'), ('WXyZ'), ('wxYZ'), ('WxYZ'),
 150     ('wXYZ'), ('WXYZ');
 151 }
 152
 153 do_execsql_test 910 {
 154   SELECT DISTINCT v COLLATE NOCASE, v FROM t9 ORDER BY +v;
 155 } {
 156   ABCD ABCD ABCd ABCd ABcD ABcD ABcd ABcd AbCD
 157   AbCD AbCd AbCd AbcD AbcD Abcd Abcd
 158   WXYZ WXYZ WXYz WXYz WXyZ WXyZ WXyz WXyz WxYZ
 159   WxYZ WxYz WxYz WxyZ WxyZ Wxyz Wxyz
 160   aBCD aBCD aBCd aBCd aBcD aBcD aBcd aBcd abCD
 161   abCD abCd abCd abcD abcD abcd abcd
 162   wXYZ wXYZ wXYz wXYz wXyZ wXyZ wXyz wXyz wxYZ
 163   wxYZ wxYz wxYz wxyZ wxyZ wxyz wxyz
 164 }
 165
 166 do_execsql_test 920 {
 167   CREATE INDEX i9 ON t9(v COLLATE NOCASE, v);
 168   ANALYZE;
 169
 170   SELECT DISTINCT v COLLATE NOCASE, v FROM t9 ORDER BY +v;
 171 } {
 172   ABCD ABCD ABCd ABCd ABcD ABcD ABcd ABcd AbCD
 173   AbCD AbCd AbCd AbcD AbcD Abcd Abcd
 174   WXYZ WXYZ WXYz WXYz WXyZ WXyZ WXyz WXyz WxYZ
 175   WxYZ WxYz WxYz WxyZ WxyZ Wxyz Wxyz
 176   aBCD aBCD aBCd aBCd aBcD aBcD aBcd aBcd abCD
 177   abCD abCd abCd abcD abcD abcd abcd
 178   wXYZ wXYZ wXYz wXYz wXyZ wXyZ wXyz wXyz wxYZ
 179   wxYZ wxYz wxYz wxyZ wxyZ wxyz wxyz
 180 }
 181
 182 # Ticket https://sqlite.org/src/info/ef9318757b152e3a on 2017-11-21
 183 # Incorrect result due to a skip-ahead-distinct optimization on a
 184 # join where no rows of the inner loop appear in the result set.
 185 #
 186 db close
 187 sqlite3 db :memory:
 188 do_execsql_test 1000 {
 189   CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
 190   CREATE INDEX t1b ON t1(b);
 191   CREATE TABLE t2(x INTEGER PRIMARY KEY, y INTEGER);
 192   CREATE INDEX t2y ON t2(y);
 193   WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<49)
 194     INSERT INTO t1(b) SELECT x/10 - 1 FROM c;
 195   WITH RECURSIVE c(x) AS (VALUES(-1) UNION ALL SELECT x+1 FROM c WHERE x<19)
 196     INSERT INTO t2(x,y) SELECT x, 1 FROM c;
 197   SELECT DISTINCT y FROM t1, t2 WHERE b=x AND b<>-1;
 198   ANALYZE;
 199   SELECT DISTINCT y FROM t1, t2 WHERE b=x AND b<>-1;
 200 } {1 1}
 201 db close
 202 sqlite3 db :memory:
 203 do_execsql_test 1010 {
 204   CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
 205   CREATE INDEX t1b ON t1(b);
 206   CREATE TABLE t2(x INTEGER PRIMARY KEY, y INTEGER);
 207   CREATE INDEX t2y ON t2(y);
 208   WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<49)
 209     INSERT INTO t1(b) SELECT -(x/10 - 1) FROM c;
 210   WITH RECURSIVE c(x) AS (VALUES(-1) UNION ALL SELECT x+1 FROM c WHERE x<19)
 211     INSERT INTO t2(x,y) SELECT -x, 1 FROM c;
 212   SELECT DISTINCT y FROM t1, t2 WHERE b=x AND b<>1 ORDER BY y DESC;
 213   ANALYZE;
 214   SELECT DISTINCT y FROM t1, t2 WHERE b=x AND b<>1 ORDER BY y DESC;
 215 } {1 1}
 216 db close
 217 sqlite3 db :memory:
 218 do_execsql_test 1020 {
 219   CREATE TABLE t1(a, b);
 220   CREATE INDEX t1a ON t1(a, b);
 221   -- Lots of rows of (1, 'no'), followed by a single (1, 'yes').
 222   WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
 223     INSERT INTO t1(a, b) SELECT 1, 'no' FROM c;
 224   INSERT INTO t1(a, b) VALUES(1, 'yes');
 225   CREATE TABLE t2(x PRIMARY KEY);
 226   INSERT INTO t2 VALUES('yes');
 227   SELECT DISTINCT a FROM t1, t2 WHERE x=b;
 228   ANALYZE;
 229   SELECT DISTINCT a FROM t1, t2 WHERE x=b;
 230 } {1 1}
 231
 232 #-------------------------------------------------------------------------
 233 reset_db
 234
 235 do_execsql_test 2000 {
 236   CREATE TABLE t0 (c0, c1, c2, PRIMARY KEY (c0, c1));
 237   CREATE TABLE t1 (c2);
 238   INSERT INTO t0(c2) VALUES (0),(1),(3),(4),(5),(6),(7),(8),(9),(10),(11);
 239   INSERT INTO t0(c1) VALUES ('a');
 240   INSERT INTO t1(c2) VALUES (0);
 241 }
 242 do_execsql_test 2010 {
 243   SELECT DISTINCT t0.c0, t1._rowid_, t0.c1 FROM t1 CROSS JOIN t0 ORDER BY t0.c0;
 244 } {{} 1 {} {} 1 a}
 245 do_execsql_test 1.2 {
 246   ANALYZE;
 247 }
 248 do_execsql_test 2020 {
 249   SELECT DISTINCT t0.c0, t1._rowid_, t0.c1 FROM t1 CROSS JOIN t0 ORDER BY t0.c0;
 250 } {{} 1 {} {} 1 a}
 251
 252
 253 do_execsql_test 2030 {
 254   CREATE TABLE t2(a, b, c);
 255   CREATE INDEX t2ab ON t2(a, b);
 256
 257   WITH c(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM c WHERE i<64)
 258     INSERT INTO t2 SELECT 'one', i%2, 'one' FROM c;
 259
 260   WITH c(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM c WHERE i<64)
 261     INSERT INTO t2 SELECT 'two', i%2, 'two' FROM c;
 262
 263   CREATE TABLE t3(x INTEGER PRIMARY KEY);
 264   INSERT INTO t3 VALUES(1);
 265
 266   ANALYZE;
 267 }
 268 do_execsql_test 2040 {
 269   SELECT DISTINCT a, b, x FROM t3 CROSS JOIN t2 ORDER BY a, +b;
 270 } {
 271   one 0 1
 272   one 1 1
 273   two 0 1
 274   two 1 1
 275 }
 276
 277 #-------------------------------------------------------------------------
 278 #
 279 reset_db
 280 do_execsql_test 3000 {
 281   CREATE TABLE t0 (c0, c1 NOT NULL DEFAULT 1, c2, PRIMARY KEY (c0, c1));
 282   INSERT INTO t0(c2) VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL);
 283   INSERT INTO t0(c2) VALUES('a');
 284 }
 285
 286 do_execsql_test 3010 {
 287   SELECT DISTINCT * FROM t0 WHERE NULL IS t0.c0;
 288 } {
 289   {} 1 {}
 290   {} 1 a
 291 }
 292
 293 do_execsql_test 3020 {
 294   ANALYZE;
 295 }
 296
 297 do_execsql_test 3030 {
 298   SELECT DISTINCT * FROM t0 WHERE NULL IS c0;
 299 } {
 300   {} 1 {}
 301   {} 1 a
 302 }
 303
 304 finish_test