xref: /sqlite-3.40.0/test/with3.test (revision 87f500ce) 
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  } {
83    3 0 0 {SCAN TABLE cnt}
84    1 0 0 {COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)}
85    0 0 0 {SCAN SUBQUERY 1}
86    0 1 1 {SEARCH TABLE y1 USING INDEX y1a (a=?)}
87  }
88
89  do_eqp_test 3.1.3 {
90    WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1000000)
91    SELECT * FROM cnt, y1 WHERE i=a
92  } {
93    3 0 0 {SCAN TABLE cnt}
94    1 0 0 {COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)}
95    0 0 1 {SCAN TABLE y1}
96    0 1 0 {SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (i=?)}
97  }
98}
99
100do_execsql_test 3.2.1 {
101  CREATE TABLE w1(pk INTEGER PRIMARY KEY, x INTEGER);
102  CREATE TABLE w2(pk INTEGER PRIMARY KEY);
103}
104
105do_eqp_test 3.2.2 {
106  WITH RECURSIVE c(w,id) AS (SELECT 0, (SELECT pk FROM w2 LIMIT 1)
107     UNION ALL SELECT c.w + 1, x FROM w1, c LIMIT 1)
108     SELECT * FROM c, w2, w1
109     WHERE c.id=w2.pk AND c.id=w1.pk;
110} {
111  2 0 0 {EXECUTE SCALAR SUBQUERY 3}
112  3 0 0 {SCAN TABLE w2}
113  4 0 0 {SCAN TABLE w1}
114  4 1 1 {SCAN TABLE c}
115  1 0 0 {COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)} 0 0 0 {SCAN SUBQUERY 1}
116  0 1 1 {SEARCH TABLE w2 USING INTEGER PRIMARY KEY (rowid=?)}
117  0 2 2 {SEARCH TABLE w1 USING INTEGER PRIMARY KEY (rowid=?)}
118}
119
120finish_test
121