# 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}} # 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 xxxxxx | |--SETUP | | `--SCAN CONSTANT ROW | `--RECURSIVE STEP | `--SCAN TABLE cnt |--SCAN SUBQUERY xxxxxx `--SEARCH TABLE 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 xxxxxx | |--SETUP | | `--SCAN CONSTANT ROW | `--RECURSIVE STEP | `--SCAN TABLE cnt |--SCAN TABLE y1 `--SEARCH SUBQUERY xxxxxx 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 xxxxxx | |--SETUP | | |--SCAN CONSTANT ROW | | `--SCALAR SUBQUERY xxxxxx | | `--SCAN TABLE w2 | `--RECURSIVE STEP | |--SCAN TABLE w1 | `--SCAN TABLE c |--SCAN SUBQUERY xxxxxx |--SEARCH TABLE w2 USING INTEGER PRIMARY KEY (rowid=?) `--SEARCH TABLE w1 USING INTEGER PRIMARY KEY (rowid=?) } finish_test