From 18657a960e125336f704ea058e25c27bd3900dcb Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sun, 5 May 2024 19:28:19 +0200 Subject: Adding upstream version 3.40.1. Signed-off-by: Daniel Baumann --- test/with6.test | 322 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 322 insertions(+) create mode 100644 test/with6.test (limited to 'test/with6.test') diff --git a/test/with6.test b/test/with6.test new file mode 100644 index 0000000..20e518b --- /dev/null +++ b/test/with6.test @@ -0,0 +1,322 @@ +# 2021-02-22 +# +# 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 the MATERIALIZED hint to common table expressions +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set ::testprefix with6 + +ifcapable {!cte} { + finish_test + return +} + +do_execsql_test 100 { + WITH c(x) AS (VALUES(0),(1)) + SELECT c1.x||c2.x||c3.x FROM c c1, c c2, c c3; +} {000 001 010 011 100 101 110 111} +do_eqp_test 101 { + WITH c(x) AS (VALUES(0),(1)) + SELECT c1.x||c2.x||c3.x FROM c c1, c c2, c c3; +} { + QUERY PLAN + |--MATERIALIZE c + | `--SCAN 2 CONSTANT ROWS + |--SCAN c1 + |--SCAN c2 + `--SCAN c3 +} + +do_execsql_test 110 { + WITH c(x) AS MATERIALIZED (VALUES(0),(1)) + SELECT c1.x||c2.x||c3.x FROM c c1, c c2, c c3; +} {000 001 010 011 100 101 110 111} +do_eqp_test 111 { + WITH c(x) AS MATERIALIZED (VALUES(0),(1)) + SELECT c1.x||c2.x||c3.x FROM c c1, c c2, c c3; +} { + QUERY PLAN + |--MATERIALIZE c + | `--SCAN 2 CONSTANT ROWS + |--SCAN c1 + |--SCAN c2 + `--SCAN c3 +} + +# Even though the CTE is not materialized, the self-join optimization +# kicks in and does the materialization for us. +# +do_execsql_test 120 { + WITH c(x) AS NOT MATERIALIZED (VALUES(0),(1)) + SELECT c1.x||c2.x||c3.x FROM c c1, c c2, c c3; +} {000 001 010 011 100 101 110 111} +do_eqp_test 121 { + WITH c(x) AS NOT MATERIALIZED (VALUES(0),(1)) + SELECT c1.x||c2.x||c3.x FROM c c1, c c2, c c3; +} { + QUERY PLAN + |--MATERIALIZE c + | `--SCAN 2 CONSTANT ROWS + |--SCAN c1 + |--SCAN c2 + `--SCAN c3 +} + +do_execsql_test 130 { + WITH c(x) AS NOT MATERIALIZED (VALUES(0),(1)) + SELECT c1.x||c2.x||c3.x + FROM (SELECT x FROM c LIMIT 5) AS c1, + (SELECT x FROM c LIMIT 5) AS c2, + (SELECT x FROM c LIMIT 5) AS c3; +} {000 001 010 011 100 101 110 111} +do_eqp_test 131 { + WITH c(x) AS NOT MATERIALIZED (VALUES(0),(1)) + SELECT c1.x||c2.x||c3.x + FROM (SELECT x FROM c LIMIT 5) AS c1, + (SELECT x FROM c LIMIT 5) AS c2, + (SELECT x FROM c LIMIT 5) AS c3; +} { + QUERY PLAN + |--MATERIALIZE c1 + | |--CO-ROUTINE c + | | `--SCAN 2 CONSTANT ROWS + | `--SCAN c + |--MATERIALIZE c2 + | |--CO-ROUTINE c + | | `--SCAN 2 CONSTANT ROWS + | `--SCAN c + |--MATERIALIZE c3 + | |--CO-ROUTINE c + | | `--SCAN 2 CONSTANT ROWS + | `--SCAN c + |--SCAN c1 + |--SCAN c2 + `--SCAN c3 +} + +# The (SELECT x FROM c LIMIT N) subqueries get materialized once each. +# Show multiple materializations are shown. But there is only one +# materialization for c, shown by the "SCAN 2 CONSTANT ROWS" line. +# +do_execsql_test 140 { + WITH c(x) AS MATERIALIZED (VALUES(0),(1)) + SELECT c1.x||c2.x||c3.x + FROM (SELECT x FROM c LIMIT 5) AS c1, + (SELECT x FROM c LIMIT 6) AS c2, + (SELECT x FROM c LIMIT 7) AS c3; +} {000 001 010 011 100 101 110 111} +do_eqp_test 141 { + WITH c(x) AS MATERIALIZED (VALUES(0),(1)) + SELECT c1.x||c2.x||c3.x + FROM (SELECT x FROM c LIMIT 5) AS c1, + (SELECT x FROM c LIMIT 6) AS c2, + (SELECT x FROM c LIMIT 7) AS c3; +} { + QUERY PLAN + |--MATERIALIZE c1 + | |--MATERIALIZE c + | | `--SCAN 2 CONSTANT ROWS + | `--SCAN c + |--MATERIALIZE c2 + | `--SCAN c + |--MATERIALIZE c3 + | `--SCAN c + |--SCAN c1 + |--SCAN c2 + `--SCAN c3 +} + +do_execsql_test 150 { + WITH c(x) AS (VALUES(0),(1)) + SELECT c1.x||c2.x||c3.x + FROM (SELECT x FROM c LIMIT 5) AS c1, + (SELECT x FROM c LIMIT 6) AS c2, + (SELECT x FROM c LIMIT 7) AS c3; +} {000 001 010 011 100 101 110 111} +do_eqp_test 151 { + WITH c(x) AS (VALUES(0),(1)) + SELECT c1.x||c2.x||c3.x + FROM (SELECT x FROM c LIMIT 5) AS c1, + (SELECT x FROM c LIMIT 6) AS c2, + (SELECT x FROM c LIMIT 7) AS c3; +} { + QUERY PLAN + |--MATERIALIZE c1 + | |--MATERIALIZE c + | | `--SCAN 2 CONSTANT ROWS + | `--SCAN c + |--MATERIALIZE c2 + | `--SCAN c + |--MATERIALIZE c3 + | `--SCAN c + |--SCAN c1 + |--SCAN c2 + `--SCAN c3 +} + +do_execsql_test 160 { + WITH c(x) AS (VALUES(0),(1)) + SELECT c2.x + 100*(SELECT sum(x+1) FROM c WHERE c.x<=c2.x) + FROM c AS c2 WHERE c2.x<10; +} {100 301} +do_eqp_test 161 { + WITH c(x) AS (VALUES(0),(1)) + SELECT c2.x + 100*(SELECT sum(x+1) FROM c WHERE c.x<=c2.x) + FROM c AS c2 WHERE c2.x<10; +} { + QUERY PLAN + |--MATERIALIZE c + | `--SCAN 2 CONSTANT ROWS + |--SCAN c2 + `--CORRELATED SCALAR SUBQUERY xxxxxx + `--SCAN c +} + +do_execsql_test 170 { + WITH c(x) AS NOT MATERIALIZED (VALUES(0),(1)) + SELECT c2.x + 100*(SELECT sum(x+1) FROM c WHERE c.x<=c2.x) + FROM c AS c2 WHERE c2.x<10; +} {100 301} +do_eqp_test 171 { + WITH c(x) AS NOT MATERIALIZED (VALUES(0),(1)) + SELECT c2.x + 100*(SELECT sum(x+1) FROM c WHERE c.x<=c2.x) + FROM c AS c2 WHERE c2.x<10; +} { + QUERY PLAN + |--CO-ROUTINE c + | `--SCAN 2 CONSTANT ROWS + |--SCAN c2 + `--CORRELATED SCALAR SUBQUERY xxxxxx + |--CO-ROUTINE c + | `--SCAN 2 CONSTANT ROWS + `--SCAN c +} + + +do_execsql_test 200 { + CREATE TABLE t1(x); + INSERT INTO t1(x) VALUES(4); + CREATE VIEW t2(y) AS + WITH c(z) AS (VALUES(4),(5),(6)) + SELECT c1.z+c2.z*100+t1.x*10000 + FROM t1, + (SELECT z FROM c LIMIT 5) AS c1, + (SELECT z FROM c LIMIT 5) AS c2; + SELECT y FROM t2 ORDER BY y; +} {40404 40405 40406 40504 40505 40506 40604 40605 40606} +do_execsql_test 210 { + DROP VIEW t2; + CREATE VIEW t2(y) AS + WITH c(z) AS NOT MATERIALIZED (VALUES(4),(5),(6)) + SELECT c1.z+c2.z*100+t1.x*10000 + FROM t1, + (SELECT z FROM c LIMIT 5) AS c1, + (SELECT z FROM c LIMIT 5) AS c2; + SELECT y FROM t2 ORDER BY y; +} {40404 40405 40406 40504 40505 40506 40604 40605 40606} +do_eqp_test 211 { + SELECT y FROM t2 ORDER BY y; +} { + QUERY PLAN + |--MATERIALIZE c1 + | |--CO-ROUTINE c + | | `--SCAN 3 CONSTANT ROWS + | `--SCAN c + |--MATERIALIZE c2 + | |--CO-ROUTINE c + | | `--SCAN 3 CONSTANT ROWS + | `--SCAN c + |--SCAN c1 + |--SCAN c2 + |--SCAN t1 + `--USE TEMP B-TREE FOR ORDER BY +} +do_execsql_test 220 { + DROP VIEW t2; + CREATE VIEW t2(y) AS + WITH c(z) AS MATERIALIZED (VALUES(4),(5),(6)) + SELECT c1.z+c2.z*100+t1.x*10000 + FROM t1, + (SELECT z FROM c LIMIT 5) AS c1, + (SELECT z FROM c LIMIT 5) AS c2; + SELECT y FROM t2 ORDER BY y; +} {40404 40405 40406 40504 40505 40506 40604 40605 40606} + +# 2022-04-22: Do not allow flattening of a MATERIALIZED CTE into +# an outer query. +# +reset_db +db null - +do_execsql_test 300 { + CREATE TABLE t2(a INT,b INT,d INT); INSERT INTO t2 VALUES(4,5,6),(7,8,9); + CREATE TABLE t3(a INT,b INT,e INT); INSERT INTO t3 VALUES(3,3,3),(8,8,8); +} {} +do_execsql_test 310 { + WITH t23 AS MATERIALIZED (SELECT * FROM t2 FULL JOIN t3 USING(b)) + SELECT * FROM t23; +} { + 4 5 6 - - + 7 8 9 8 8 + - 3 - 3 3 +} +do_eqp_test 311 { + WITH t23 AS MATERIALIZED (SELECT * FROM t2 FULL JOIN t3 USING(b)) + SELECT * FROM t23; +} { + QUERY PLAN + |--MATERIALIZE t23 + | |--SCAN t2 + | |--SCAN t3 LEFT-JOIN + | `--RIGHT-JOIN t3 + | `--SCAN t3 + `--SCAN t23 +} +do_execsql_test 320 { + WITH t23 AS NOT MATERIALIZED (SELECT * FROM t2 FULL JOIN t3 USING(b)) + SELECT * FROM t23; +} { + 4 5 6 - - + 7 8 9 8 8 + - 3 - 3 3 +} +do_eqp_test 321 { + WITH t23 AS NOT MATERIALIZED (SELECT * FROM t2 FULL JOIN t3 USING(b)) + SELECT * FROM t23; +} { + QUERY PLAN + |--SCAN t2 + |--SCAN t3 LEFT-JOIN + `--RIGHT-JOIN t3 + `--SCAN t3 +} +do_execsql_test 330 { + WITH t23 AS (SELECT * FROM t2 FULL JOIN t3 USING(b)) + SELECT * FROM t23; +} { + 4 5 6 - - + 7 8 9 8 8 + - 3 - 3 3 +} +do_eqp_test 331 { + WITH t23 AS (SELECT * FROM t2 FULL JOIN t3 USING(b)) + SELECT * FROM t23; +} { + QUERY PLAN + |--SCAN t2 + |--SCAN t3 LEFT-JOIN + `--RIGHT-JOIN t3 + `--SCAN t3 +} + + +finish_test -- cgit v1.2.3