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     |--MATERIALIZE 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     |--MATERIALIZE cnt
 107     |  |--SETUP
 108     |  |  `--SCAN CONSTANT ROW
 109     |  `--RECURSIVE STEP
 110     |     `--SCAN cnt
 111     |--SCAN y1
 112     `--SEARCH cnt USING AUTOMATIC COVERING INDEX (i=?)
 113   }]
 114 }
 115
 116 do_execsql_test 3.2.1 {
 117   CREATE TABLE w1(pk INTEGER PRIMARY KEY, x INTEGER);
 118   CREATE TABLE w2(pk INTEGER PRIMARY KEY);
 119 }
 120
 121 do_eqp_test 3.2.2 {
 122   WITH RECURSIVE c(w,id) AS (SELECT 0, (SELECT pk FROM w2 LIMIT 1)
 123      UNION ALL SELECT c.w + 1, x FROM w1, c LIMIT 1)
 124      SELECT * FROM c, w2, w1
 125      WHERE c.id=w2.pk AND c.id=w1.pk;
 126 } {
 127   QUERY PLAN
 128   |--MATERIALIZE c
 129   |  |--SETUP
 130   |  |  |--SCAN CONSTANT ROW
 131   |  |  `--SCALAR SUBQUERY xxxxxx
 132   |  |     `--SCAN w2
 133   |  `--RECURSIVE STEP
 134   |     |--SCAN w1
 135   |     `--SCAN c
 136   |--SCAN c
 137   |--SEARCH w2 USING INTEGER PRIMARY KEY (rowid=?)
 138   `--SEARCH w1 USING INTEGER PRIMARY KEY (rowid=?)
 139 }
 140
 141 do_execsql_test 4.0 {
 142   WITH t5(t5col1) AS (
 143     SELECT (
 144       WITH t3(t3col1) AS (
 145         WITH t2 AS (
 146           WITH t1 AS (SELECT 1 AS c1 GROUP BY 1)
 147           SELECT a.c1 FROM t1 AS a, t1 AS b
 148           WHERE anoncol1 = 1
 149         )
 150         SELECT (SELECT 1 FROM t2) FROM t2
 151       )
 152       SELECT t3col1 FROM t3 WHERE t3col1
 153     ) FROM (SELECT 1 AS anoncol1)
 154   )
 155   SELECT t5col1, t5col1 FROM t5
 156 } {1 1}
 157 do_execsql_test 4.1 {
 158   SELECT EXISTS (
 159     WITH RECURSIVE Table0 AS (
 160       WITH RECURSIVE Table0(Col0) AS (SELECT ALL 1  )
 161       SELECT ALL (
 162         WITH RECURSIVE Table0 AS (
 163           WITH RECURSIVE Table0 AS (
 164             WITH RECURSIVE Table0 AS (SELECT DISTINCT 1  GROUP BY 1  )
 165             SELECT DISTINCT * FROM Table0 NATURAL INNER JOIN Table0
 166             WHERE Col0 = 1
 167           )
 168           SELECT ALL (SELECT DISTINCT * FROM Table0) FROM Table0 WHERE Col0 = 1
 169         )
 170         SELECT ALL * FROM Table0  NATURAL INNER JOIN  Table0
 171       ) FROM Table0 )
 172       SELECT DISTINCT * FROM Table0  NATURAL INNER JOIN  Table0
 173     );
 174 } {1}
 175
 176 # 2020-01-18 chrome ticket 1043236
 177 # Correct handling of the sequence:
 178 #    OP_OpenEphem
 179 #    OP_OpenDup
 180 #    Op_OpenEphem
 181 #    OP_OpenDup
 182 #
 183 do_execsql_test 4.2 {
 184   SELECT (
 185     WITH t1(a) AS (VALUES(1))
 186     SELECT (
 187       WITH t2(b) AS (
 188         WITH t3(c) AS (
 189           WITH t4(d) AS (VALUES('elvis'))
 190           SELECT t4a.d FROM t4 AS t4a JOIN t4 AS t4b LEFT JOIN t4 AS t4c
 191         )
 192         SELECT c FROM t3 WHERE a = 1
 193       )
 194       SELECT t2a.b FROM t2 AS t2a JOIN t2 AS t2x
 195     )
 196     FROM t1 GROUP BY 1
 197   )
 198   GROUP BY 1;
 199 } {elvis}
 200
 201 # 2021-02-13
 202 # Avoid manifesting the same CTE multiple times.
 203 #
 204 do_eqp_test 5.1 {
 205   WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<1)
 206   SELECT x1.x||x2.x||x3.x||x4.x FROM c AS x1, c AS x2, c AS x3, c AS x4
 207   ORDER BY 1;
 208 } {
 209   QUERY PLAN
 210   |--MATERIALIZE c
 211   |  |--SETUP
 212   |  |  `--SCAN CONSTANT ROW
 213   |  `--RECURSIVE STEP
 214   |     `--SCAN c
 215   |--SCAN x1
 216   |--SCAN x2
 217   |--SCAN x3
 218   |--SCAN x4
 219   `--USE TEMP B-TREE FOR ORDER BY
 220 }
 221 do_execsql_test 5.2 {
 222   WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<1)
 223   SELECT x1.x||x2.x||x3.x||x4.x FROM c AS x1, c AS x2, c AS x3, c AS x4
 224   ORDER BY 1;
 225 } {0000 0001 0010 0011 0100 0101 0110 0111 1000 1001 1010 1011 1100 1101 1110 1111}
 226
 227 #-------------------------------------------------------------------------
 228 # At one point this would incorrectly report "circular reference: cte1"
 229 #
 230 do_catchsql_test 6.0 {
 231   with
 232     cte1(x, y) AS ( select 1, 2, 3 ),
 233     cte2(z) as ( select 1 from cte1 )
 234   select * from cte2, cte1;
 235 } {1 {table cte1 has 3 values for 2 columns}}
 236
 237 do_catchsql_test 6.1 {
 238   with
 239     cte1(x, y) AS ( select 1, 2, 3 ),
 240     cte2(z) as ( select 1 from cte1 UNION ALL SELECT z+1 FROM cte2 WHERE z<5)
 241   select * from cte2, cte1;
 242 } {1 {table cte1 has 3 values for 2 columns}}
 243
 244
 245
 246
 247 finish_test