test/with3.test

   1 # 2015-11-07
   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 file is testing the WITH clause.
  13 #
  14
  15 set testdir [file dirname $argv0]
  16 source $testdir/tester.tcl
  17 set ::testprefix with3
  18
  19 ifcapable {!cte} {
  20   finish_test
  21   return
  22 }
  23
  24 # Test problems found by Kostya Serebryany using
  25 # LibFuzzer.  (http://llvm.org/docs/LibFuzzer.html)
  26 #
  27 do_catchsql_test 1.0 {
  28   WITH i(x) AS (
  29     WITH j AS (SELECT 10)
  30     SELECT 5 FROM t0 UNION SELECT 8 FROM m
  31   )
  32   SELECT * FROM i;
  33 } {1 {no such table: m}}
  34
  35 # 2019-11-09 dbfuzzcheck find
  36 do_catchsql_test 1.1 {
  37   CREATE VIEW v1(x,y) AS
  38     WITH t1(a,b) AS (VALUES(1,2))
  39     SELECT * FROM nosuchtable JOIN t1;
  40   SELECT * FROM v1;
  41 } {1 {no such table: main.nosuchtable}}
  42
  43 # Additional test cases that came out of the work to
  44 # fix for Kostya's problem.
  45 #
  46 do_execsql_test 2.0 {
  47  WITH
  48   x1 AS (SELECT 10),
  49   x2 AS (SELECT 11),
  50   x3 AS (
  51     SELECT * FROM x1 UNION ALL SELECT * FROM x2
  52   ),
  53   x4 AS (
  54     WITH
  55     x1 AS (SELECT 12),
  56     x2 AS (SELECT 13)
  57     SELECT * FROM x3
  58   )
  59   SELECT * FROM x4;
  60
  61 } {10 11}
  62
  63 do_execsql_test 2.1 {
  64   CREATE TABLE t1(x);
  65   WITH
  66     x1(a) AS (values(100))
  67   INSERT INTO t1(x)
  68     SELECT * FROM (WITH x2(y) AS (SELECT * FROM x1) SELECT y+a FROM x1, x2);
  69   SELECT * FROM t1;
  70 } {200}
  71
  72 #-------------------------------------------------------------------------
  73 # Test that the planner notices LIMIT clauses on recursive WITH queries.
  74 #
  75
  76 ifcapable analyze {
  77   do_execsql_test 3.1.1 {
  78     CREATE TABLE y1(a, b);
  79     CREATE INDEX y1a ON y1(a);
  80
  81     WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1000)
  82       INSERT INTO y1 SELECT i%10, i FROM cnt;
  83     ANALYZE;
  84
  85   }
  86
  87   do_eqp_test 3.1.2 {
  88     WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1)
  89     SELECT * FROM cnt, y1 WHERE i=a
  90   } [string map {"\n  " \n} {
  91     QUERY PLAN
  92     |--CO-ROUTINE cnt
  93     |  |--SETUP
  94     |  |  `--SCAN CONSTANT ROW
  95     |  `--RECURSIVE STEP
  96     |     `--SCAN cnt
  97     |--SCAN cnt
  98     `--SEARCH y1 USING INDEX y1a (a=?)
  99   }]
 100
 101   do_eqp_test 3.1.3 {
 102     WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1000000)
 103     SELECT * FROM cnt, y1 WHERE i=a
 104   } [string map {"\n  " \n} {
 105     QUERY PLAN
 106     |--CO-ROUTINE cnt
 107     |  |--SETUP
 108     |  |  `--SCAN CONSTANT ROW
 109     |  `--RECURSIVE STEP
 110     |     `--SCAN cnt
 111     |--SCAN y1
 112     |--BLOOM FILTER ON cnt (i=?)
 113     `--SEARCH cnt USING AUTOMATIC COVERING INDEX (i=?)
 114   }]
 115 }
 116
 117 do_execsql_test 3.2.1 {
 118   CREATE TABLE w1(pk INTEGER PRIMARY KEY, x INTEGER);
 119   CREATE TABLE w2(pk INTEGER PRIMARY KEY);
 120 }
 121
 122 do_eqp_test 3.2.2 {
 123   WITH RECURSIVE c(w,id) AS (SELECT 0, (SELECT pk FROM w2 LIMIT 1)
 124      UNION ALL SELECT c.w + 1, x FROM w1, c LIMIT 1)
 125      SELECT * FROM c, w2, w1
 126      WHERE c.id=w2.pk AND c.id=w1.pk;
 127 } {
 128   QUERY PLAN
 129   |--CO-ROUTINE c
 130   |  |--SETUP
 131   |  |  |--SCAN CONSTANT ROW
 132   |  |  `--SCALAR SUBQUERY xxxxxx
 133   |  |     `--SCAN w2
 134   |  `--RECURSIVE STEP
 135   |     |--SCAN w1
 136   |     `--SCAN c
 137   |--SCAN c
 138   |--SEARCH w2 USING INTEGER PRIMARY KEY (rowid=?)
 139   `--SEARCH w1 USING INTEGER PRIMARY KEY (rowid=?)
 140 }
 141
 142 do_execsql_test 4.0 {
 143   WITH t5(t5col1) AS (
 144     SELECT (
 145       WITH t3(t3col1) AS (
 146         WITH t2 AS (
 147           WITH t1 AS (SELECT 1 AS c1 GROUP BY 1)
 148           SELECT a.c1 FROM t1 AS a, t1 AS b
 149           WHERE anoncol1 = 1
 150         )
 151         SELECT (SELECT 1 FROM t2) FROM t2
 152       )
 153       SELECT t3col1 FROM t3 WHERE t3col1
 154     ) FROM (SELECT 1 AS anoncol1)
 155   )
 156   SELECT t5col1, t5col1 FROM t5
 157 } {1 1}
 158 do_execsql_test 4.1 {
 159   SELECT EXISTS (
 160     WITH RECURSIVE Table0 AS (
 161       WITH RECURSIVE Table0(Col0) AS (SELECT ALL 1  )
 162       SELECT ALL (
 163         WITH RECURSIVE Table0 AS (
 164           WITH RECURSIVE Table0 AS (
 165             WITH RECURSIVE Table0 AS (SELECT DISTINCT 1  GROUP BY 1  )
 166             SELECT DISTINCT * FROM Table0 NATURAL INNER JOIN Table0
 167             WHERE Col0 = 1
 168           )
 169           SELECT ALL (SELECT DISTINCT * FROM Table0) FROM Table0 WHERE Col0 = 1
 170         )
 171         SELECT ALL * FROM Table0  NATURAL INNER JOIN  Table0
 172       ) FROM Table0 )
 173       SELECT DISTINCT * FROM Table0  NATURAL INNER JOIN  Table0
 174     );
 175 } {1}
 176
 177 # 2020-01-18 chrome ticket 1043236
 178 # Correct handling of the sequence:
 179 #    OP_OpenEphem
 180 #    OP_OpenDup
 181 #    Op_OpenEphem
 182 #    OP_OpenDup
 183 #
 184 do_execsql_test 4.2 {
 185   SELECT (
 186     WITH t1(a) AS (VALUES(1))
 187     SELECT (
 188       WITH t2(b) AS (
 189         WITH t3(c) AS (
 190           WITH t4(d) AS (VALUES('elvis'))
 191           SELECT t4a.d FROM t4 AS t4a JOIN t4 AS t4b LEFT JOIN t4 AS t4c
 192         )
 193         SELECT c FROM t3 WHERE a = 1
 194       )
 195       SELECT t2a.b FROM t2 AS t2a JOIN t2 AS t2x
 196     )
 197     FROM t1 GROUP BY 1
 198   )
 199   GROUP BY 1;
 200 } {elvis}
 201
 202 # 2021-02-13
 203 # Avoid manifesting the same CTE multiple times.
 204 #
 205 do_eqp_test 5.1 {
 206   WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<1)
 207   SELECT x1.x||x2.x||x3.x||x4.x FROM c AS x1, c AS x2, c AS x3, c AS x4
 208   ORDER BY 1;
 209 } {
 210   QUERY PLAN
 211   |--MATERIALIZE c
 212   |  |--SETUP
 213   |  |  `--SCAN CONSTANT ROW
 214   |  `--RECURSIVE STEP
 215   |     `--SCAN c
 216   |--SCAN x1
 217   |--SCAN x2
 218   |--SCAN x3
 219   |--SCAN x4
 220   `--USE TEMP B-TREE FOR ORDER BY
 221 }
 222 do_execsql_test 5.2 {
 223   WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<1)
 224   SELECT x1.x||x2.x||x3.x||x4.x FROM c AS x1, c AS x2, c AS x3, c AS x4
 225   ORDER BY 1;
 226 } {0000 0001 0010 0011 0100 0101 0110 0111 1000 1001 1010 1011 1100 1101 1110 1111}
 227
 228 #-------------------------------------------------------------------------
 229 # At one point this would incorrectly report "circular reference: cte1"
 230 #
 231 do_catchsql_test 6.0 {
 232   with
 233     cte1(x, y) AS ( select 1, 2, 3 ),
 234     cte2(z) as ( select 1 from cte1 )
 235   select * from cte2, cte1;
 236 } {1 {table cte1 has 3 values for 2 columns}}
 237
 238 do_catchsql_test 6.1 {
 239   with
 240     cte1(x, y) AS ( select 1, 2, 3 ),
 241     cte2(z) as ( select 1 from cte1 UNION ALL SELECT z+1 FROM cte2 WHERE z<5)
 242   select * from cte2, cte1;
 243 } {1 {table cte1 has 3 values for 2 columns}}
 244
 245
 246
 247
 248 finish_test