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
 183 finish_test