diff options
Diffstat (limited to '')
| -rw-r--r-- | test/with3.test | 247 |
1 files changed, 247 insertions, 0 deletions
diff --git a/test/with3.test b/test/with3.test new file mode 100644 index 0000000..1aed92a --- /dev/null +++ b/test/with3.test @@ -0,0 +1,247 @@ +# 2015-11-07 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. The +# focus of this file is testing the WITH clause. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set ::testprefix with3 + +ifcapable {!cte} { + finish_test + return +} + +# Test problems found by Kostya Serebryany using +# LibFuzzer. (http://llvm.org/docs/LibFuzzer.html) +# +do_catchsql_test 1.0 { + WITH i(x) AS ( + WITH j AS (SELECT 10) + SELECT 5 FROM t0 UNION SELECT 8 FROM m + ) + SELECT * FROM i; +} {1 {no such table: m}} + +# 2019-11-09 dbfuzzcheck find +do_catchsql_test 1.1 { + CREATE VIEW v1(x,y) AS + WITH t1(a,b) AS (VALUES(1,2)) + SELECT * FROM nosuchtable JOIN t1; + SELECT * FROM v1; +} {1 {no such table: main.nosuchtable}} + +# Additional test cases that came out of the work to +# fix for Kostya's problem. +# +do_execsql_test 2.0 { + WITH + x1 AS (SELECT 10), + x2 AS (SELECT 11), + x3 AS ( + SELECT * FROM x1 UNION ALL SELECT * FROM x2 + ), + x4 AS ( + WITH + x1 AS (SELECT 12), + x2 AS (SELECT 13) + SELECT * FROM x3 + ) + SELECT * FROM x4; + +} {10 11} + +do_execsql_test 2.1 { + CREATE TABLE t1(x); + WITH + x1(a) AS (values(100)) + INSERT INTO t1(x) + SELECT * FROM (WITH x2(y) AS (SELECT * FROM x1) SELECT y+a FROM x1, x2); + SELECT * FROM t1; +} {200} + +#------------------------------------------------------------------------- +# Test that the planner notices LIMIT clauses on recursive WITH queries. +# + +ifcapable analyze { + do_execsql_test 3.1.1 { + CREATE TABLE y1(a, b); + CREATE INDEX y1a ON y1(a); + + WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1000) + INSERT INTO y1 SELECT i%10, i FROM cnt; + ANALYZE; + + } + + do_eqp_test 3.1.2 { + WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1) + SELECT * FROM cnt, y1 WHERE i=a + } [string map {"\n " \n} { + QUERY PLAN + |--MATERIALIZE cnt + | |--SETUP + | | `--SCAN CONSTANT ROW + | `--RECURSIVE STEP + | `--SCAN cnt + |--SCAN cnt + `--SEARCH y1 USING INDEX y1a (a=?) + }] + + do_eqp_test 3.1.3 { + WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1000000) + SELECT * FROM cnt, y1 WHERE i=a + } [string map {"\n " \n} { + QUERY PLAN + |--MATERIALIZE cnt + | |--SETUP + | | `--SCAN CONSTANT ROW + | `--RECURSIVE STEP + | `--SCAN cnt + |--SCAN y1 + `--SEARCH cnt USING AUTOMATIC COVERING INDEX (i=?) + }] +} + +do_execsql_test 3.2.1 { + CREATE TABLE w1(pk INTEGER PRIMARY KEY, x INTEGER); + CREATE TABLE w2(pk INTEGER PRIMARY KEY); +} + +do_eqp_test 3.2.2 { + WITH RECURSIVE c(w,id) AS (SELECT 0, (SELECT pk FROM w2 LIMIT 1) + UNION ALL SELECT c.w + 1, x FROM w1, c LIMIT 1) + SELECT * FROM c, w2, w1 + WHERE c.id=w2.pk AND c.id=w1.pk; +} { + QUERY PLAN + |--MATERIALIZE c + | |--SETUP + | | |--SCAN CONSTANT ROW + | | `--SCALAR SUBQUERY xxxxxx + | | `--SCAN w2 + | `--RECURSIVE STEP + | |--SCAN c + | `--SCAN w1 + |--SCAN c + |--SEARCH w2 USING INTEGER PRIMARY KEY (rowid=?) + `--SEARCH w1 USING INTEGER PRIMARY KEY (rowid=?) +} + +do_execsql_test 4.0 { + WITH t5(t5col1) AS ( + SELECT ( + WITH t3(t3col1) AS ( + WITH t2 AS ( + WITH t1 AS (SELECT 1 AS c1 GROUP BY 1) + SELECT a.c1 FROM t1 AS a, t1 AS b + WHERE anoncol1 = 1 + ) + SELECT (SELECT 1 FROM t2) FROM t2 + ) + SELECT t3col1 FROM t3 WHERE t3col1 + ) FROM (SELECT 1 AS anoncol1) + ) + SELECT t5col1, t5col1 FROM t5 +} {1 1} +do_execsql_test 4.1 { + SELECT EXISTS ( + WITH RECURSIVE Table0 AS ( + WITH RECURSIVE Table0(Col0) AS (SELECT ALL 1 ) + SELECT ALL ( + WITH RECURSIVE Table0 AS ( + WITH RECURSIVE Table0 AS ( + WITH RECURSIVE Table0 AS (SELECT DISTINCT 1 GROUP BY 1 ) + SELECT DISTINCT * FROM Table0 NATURAL INNER JOIN Table0 + WHERE Col0 = 1 + ) + SELECT ALL (SELECT DISTINCT * FROM Table0) FROM Table0 WHERE Col0 = 1 + ) + SELECT ALL * FROM Table0 NATURAL INNER JOIN Table0 + ) FROM Table0 ) + SELECT DISTINCT * FROM Table0 NATURAL INNER JOIN Table0 + ); +} {1} + +# 2020-01-18 chrome ticket 1043236 +# Correct handling of the sequence: +# OP_OpenEphem +# OP_OpenDup +# Op_OpenEphem +# OP_OpenDup +# +do_execsql_test 4.2 { + SELECT ( + WITH t1(a) AS (VALUES(1)) + SELECT ( + WITH t2(b) AS ( + WITH t3(c) AS ( + WITH t4(d) AS (VALUES('elvis')) + SELECT t4a.d FROM t4 AS t4a JOIN t4 AS t4b LEFT JOIN t4 AS t4c + ) + SELECT c FROM t3 WHERE a = 1 + ) + SELECT t2a.b FROM t2 AS t2a JOIN t2 AS t2x + ) + FROM t1 GROUP BY 1 + ) + GROUP BY 1; +} {elvis} + +# 2021-02-13 +# Avoid manifesting the same CTE multiple times. +# +do_eqp_test 5.1 { + WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<1) + SELECT x1.x||x2.x||x3.x||x4.x FROM c AS x1, c AS x2, c AS x3, c AS x4 + ORDER BY 1; +} { + QUERY PLAN + |--MATERIALIZE c + | |--SETUP + | | `--SCAN CONSTANT ROW + | `--RECURSIVE STEP + | `--SCAN c + |--SCAN x1 + |--SCAN x2 + |--SCAN x3 + |--SCAN x4 + `--USE TEMP B-TREE FOR ORDER BY +} +do_execsql_test 5.2 { + WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<1) + SELECT x1.x||x2.x||x3.x||x4.x FROM c AS x1, c AS x2, c AS x3, c AS x4 + ORDER BY 1; +} {0000 0001 0010 0011 0100 0101 0110 0111 1000 1001 1010 1011 1100 1101 1110 1111} + +#------------------------------------------------------------------------- +# At one point this would incorrectly report "circular reference: cte1" +# +do_catchsql_test 6.0 { + with + cte1(x, y) AS ( select 1, 2, 3 ), + cte2(z) as ( select 1 from cte1 ) + select * from cte2, cte1; +} {1 {table cte1 has 3 values for 2 columns}} + +do_catchsql_test 6.1 { + with + cte1(x, y) AS ( select 1, 2, 3 ), + cte2(z) as ( select 1 from cte1 UNION ALL SELECT z+1 FROM cte2 WHERE z<5) + select * from cte2, cte1; +} {1 {table cte1 has 3 values for 2 columns}} + + + + +finish_test |