1# 2014 January 11 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 with2 18 19do_execsql_test 1.0 { 20 CREATE TABLE t1(a); 21 INSERT INTO t1 VALUES(1); 22 INSERT INTO t1 VALUES(2); 23} 24 25do_execsql_test 1.1 { 26 WITH x1 AS (SELECT * FROM t1) 27 SELECT sum(a) FROM x1; 28} {3} 29 30do_execsql_test 1.2 { 31 WITH x1 AS (SELECT * FROM t1) 32 SELECT (SELECT sum(a) FROM x1); 33} {3} 34 35do_execsql_test 1.3 { 36 WITH x1 AS (SELECT * FROM t1) 37 SELECT (SELECT sum(a) FROM x1); 38} {3} 39 40do_execsql_test 1.4 { 41 CREATE TABLE t2(i); 42 INSERT INTO t2 VALUES(2); 43 INSERT INTO t2 VALUES(3); 44 INSERT INTO t2 VALUES(5); 45 46 WITH x1 AS (SELECT i FROM t2), 47 i(a) AS ( 48 SELECT min(i)-1 FROM x1 UNION SELECT a+1 FROM i WHERE a<10 49 ) 50 SELECT a FROM i WHERE a NOT IN x1 51} {1 4 6 7 8 9 10} 52 53do_execsql_test 1.5 { 54 WITH x1 AS (SELECT a FROM t1), 55 x2 AS (SELECT i FROM t2), 56 x3 AS (SELECT * FROM x1, x2 WHERE x1.a IN x2 AND x2.i IN x1) 57 SELECT * FROM x3 58} {2 2} 59 60do_execsql_test 1.6 { 61 CREATE TABLE t3 AS SELECT 3 AS x; 62 CREATE TABLE t4 AS SELECT 4 AS x; 63 64 WITH x1 AS (SELECT * FROM t3), 65 x2 AS ( 66 WITH t3 AS (SELECT * FROM t4) 67 SELECT * FROM x1 68 ) 69 SELECT * FROM x2; 70} {3} 71 72do_execsql_test 1.7 { 73 WITH x1 AS (SELECT * FROM t1) 74 SELECT (SELECT sum(a) FROM x1), (SELECT max(a) FROM x1); 75} {3 2} 76 77do_execsql_test 1.8 { 78 WITH x1 AS (SELECT * FROM t1) 79 SELECT (SELECT sum(a) FROM x1), (SELECT max(a) FROM x1), a FROM x1; 80} {3 2 1 3 2 2} 81 82do_execsql_test 1.9 { 83 WITH 84 i(x) AS ( 85 WITH 86 j(x) AS ( SELECT * FROM i ), 87 i(x) AS ( SELECT * FROM t1 ) 88 SELECT * FROM j 89 ) 90 SELECT * FROM i; 91} {1 2} 92 93#--------------------------------------------------------------------------- 94# Check that variables can be used in CTEs. 95# 96set ::min [expr 3] 97set ::max [expr 9] 98do_execsql_test 2.1 { 99 WITH i(x) AS ( 100 VALUES($min) UNION ALL SELECT x+1 FROM i WHERE x < $max 101 ) 102 SELECT * FROM i; 103} {3 4 5 6 7 8 9} 104 105do_execsql_test 2.2 { 106 WITH i(x) AS ( 107 VALUES($min) UNION ALL SELECT x+1 FROM i WHERE x < $max 108 ) 109 SELECT x FROM i JOIN i AS j USING (x); 110} {3 4 5 6 7 8 9} 111 112#--------------------------------------------------------------------------- 113# Check that circular references are rejected. 114# 115do_catchsql_test 3.1 { 116 WITH i(x, y) AS ( VALUES(1, (SELECT x FROM i)) ) 117 SELECT * FROM i; 118} {1 {circular reference: i}} 119 120do_catchsql_test 3.2 { 121 WITH 122 i(x) AS ( SELECT * FROM j ), 123 j(x) AS ( SELECT * FROM k ), 124 k(x) AS ( SELECT * FROM i ) 125 SELECT * FROM i; 126} {1 {circular reference: i}} 127 128do_catchsql_test 3.3 { 129 WITH 130 i(x) AS ( SELECT * FROM (SELECT * FROM j) ), 131 j(x) AS ( SELECT * FROM (SELECT * FROM i) ) 132 SELECT * FROM i; 133} {1 {circular reference: i}} 134 135do_catchsql_test 3.4 { 136 WITH 137 i(x) AS ( SELECT * FROM (SELECT * FROM j) ), 138 j(x) AS ( SELECT * FROM (SELECT * FROM i) ) 139 SELECT * FROM j; 140} {1 {circular reference: j}} 141 142do_catchsql_test 3.5 { 143 WITH 144 i(x) AS ( 145 WITH j(x) AS ( SELECT * FROM i ) 146 SELECT * FROM j 147 ) 148 SELECT * FROM i; 149} {1 {circular reference: i}} 150 151#--------------------------------------------------------------------------- 152# Try empty and very long column lists. 153# 154do_catchsql_test 4.1 { 155 WITH x() AS ( SELECT 1,2,3 ) 156 SELECT * FROM x; 157} {1 {near ")": syntax error}} 158 159proc genstmt {n} { 160 for {set i 1} {$i<=$n} {incr i} { 161 lappend cols "c$i" 162 lappend vals $i 163 } 164 return " 165 WITH x([join $cols ,]) AS (SELECT [join $vals ,]) 166 SELECT (c$n == $n) FROM x 167 " 168} 169 170do_execsql_test 4.2 [genstmt 10] 1 171do_execsql_test 4.3 [genstmt 100] 1 172do_execsql_test 4.4 [genstmt 255] 1 173set nLimit [sqlite3_limit db SQLITE_LIMIT_COLUMN -1] 174do_execsql_test 4.5 [genstmt [expr $nLimit-1]] 1 175do_execsql_test 4.6 [genstmt $nLimit] 1 176do_catchsql_test 4.7 [genstmt [expr $nLimit+1]] {1 {too many columns in index}} 177 178finish_test 179 180