xref: /sqlite-3.40.0/test/with3.test (revision 7aa3ebee) 
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
64finish_test
65