xref: /sqlite-3.40.0/test/with3.test (revision b80bb6ce) 
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
15set testdir [file dirname $argv0]
16source $testdir/tester.tcl
17set ::testprefix with3
18
19ifcapable {!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#
27do_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# Additional test cases that came out of the work to
36# fix for Kostya's problem.
37#
38do_execsql_test 2.0 {
39 WITH
40  x1 AS (SELECT 10),
41  x2 AS (SELECT 11),
42  x3 AS (
43    SELECT * FROM x1 UNION ALL SELECT * FROM x2
44  ),
45  x4 AS (
46    WITH
47    x1 AS (SELECT 12),
48    x2 AS (SELECT 13)
49    SELECT * FROM x3
50  )
51  SELECT * FROM x4;
52
53} {10 11}
54
55do_execsql_test 2.1 {
56  CREATE TABLE t1(x);
57  WITH
58    x1(a) AS (values(100))
59  INSERT INTO t1(x)
60    SELECT * FROM (WITH x2(y) AS (SELECT * FROM x1) SELECT y+a FROM x1, x2);
61  SELECT * FROM t1;
62} {200}
63
64#-------------------------------------------------------------------------
65# Test that the planner notices LIMIT clauses on recursive WITH queries.
66#
67
68ifcapable analyze {
69  do_execsql_test 3.1.1 {
70    CREATE TABLE y1(a, b);
71    CREATE INDEX y1a ON y1(a);
72
73    WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1000)
74      INSERT INTO y1 SELECT i%10, i FROM cnt;
75    ANALYZE;
76
77  }
78
79  do_eqp_test 3.1.2 {
80    WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1)
81    SELECT * FROM cnt, y1 WHERE i=a
82  } [string map {"\n  " \n} {
83    QUERY PLAN
84    |--MATERIALIZE xxxxxx
85    |  |--SETUP
86    |  |  `--SCAN CONSTANT ROW
87    |  `--RECURSIVE STEP
88    |     `--SCAN TABLE cnt
89    |--SCAN SUBQUERY xxxxxx
90    `--SEARCH TABLE y1 USING INDEX y1a (a=?)
91  }]
92
93  do_eqp_test 3.1.3 {
94    WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1000000)
95    SELECT * FROM cnt, y1 WHERE i=a
96  } [string map {"\n  " \n} {
97    QUERY PLAN
98    |--MATERIALIZE xxxxxx
99    |  |--SETUP
100    |  |  `--SCAN CONSTANT ROW
101    |  `--RECURSIVE STEP
102    |     `--SCAN TABLE cnt
103    |--SCAN TABLE y1
104    `--SEARCH SUBQUERY xxxxxx USING AUTOMATIC COVERING INDEX (i=?)
105  }]
106}
107
108do_execsql_test 3.2.1 {
109  CREATE TABLE w1(pk INTEGER PRIMARY KEY, x INTEGER);
110  CREATE TABLE w2(pk INTEGER PRIMARY KEY);
111}
112
113do_eqp_test 3.2.2 {
114  WITH RECURSIVE c(w,id) AS (SELECT 0, (SELECT pk FROM w2 LIMIT 1)
115     UNION ALL SELECT c.w + 1, x FROM w1, c LIMIT 1)
116     SELECT * FROM c, w2, w1
117     WHERE c.id=w2.pk AND c.id=w1.pk;
118} {
119  QUERY PLAN
120  |--MATERIALIZE xxxxxx
121  |  |--SETUP
122  |  |  |--SCAN CONSTANT ROW
123  |  |  `--SCALAR SUBQUERY
124  |  |     `--SCAN TABLE w2
125  |  `--RECURSIVE STEP
126  |     |--SCAN TABLE w1
127  |     `--SCAN TABLE c
128  |--SCAN SUBQUERY xxxxxx
129  |--SEARCH TABLE w2 USING INTEGER PRIMARY KEY (rowid=?)
130  `--SEARCH TABLE w1 USING INTEGER PRIMARY KEY (rowid=?)
131}
132
133finish_test
134