drh | dee1cb3 | 2021-02-22 19:57:58 +0000 | [diff] [blame] | 1 | # 2021-02-22 |
| 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 the MATERIALIZED hint to common table expressions |
| 13 | # |
| 14 | |
| 15 | set testdir [file dirname $argv0] |
| 16 | source $testdir/tester.tcl |
| 17 | set ::testprefix with6 |
| 18 | |
| 19 | ifcapable {!cte} { |
| 20 | finish_test |
| 21 | return |
| 22 | } |
| 23 | |
| 24 | do_execsql_test 100 { |
| 25 | WITH c(x) AS (VALUES(0),(1)) |
| 26 | SELECT c1.x||c2.x||c3.x FROM c c1, c c2, c c3; |
| 27 | } {000 001 010 011 100 101 110 111} |
| 28 | do_eqp_test 101 { |
| 29 | WITH c(x) AS (VALUES(0),(1)) |
| 30 | SELECT c1.x||c2.x||c3.x FROM c c1, c c2, c c3; |
| 31 | } { |
| 32 | QUERY PLAN |
drh | 2f2091b | 2021-03-20 15:46:01 +0000 | [diff] [blame] | 33 | |--MATERIALIZE c |
drh | dee1cb3 | 2021-02-22 19:57:58 +0000 | [diff] [blame] | 34 | | `--SCAN 2 CONSTANT ROWS |
drh | 8210233 | 2021-03-20 15:11:29 +0000 | [diff] [blame] | 35 | |--SCAN c1 |
| 36 | |--SCAN c2 |
| 37 | `--SCAN c3 |
drh | dee1cb3 | 2021-02-22 19:57:58 +0000 | [diff] [blame] | 38 | } |
| 39 | |
| 40 | do_execsql_test 110 { |
| 41 | WITH c(x) AS MATERIALIZED (VALUES(0),(1)) |
| 42 | SELECT c1.x||c2.x||c3.x FROM c c1, c c2, c c3; |
| 43 | } {000 001 010 011 100 101 110 111} |
| 44 | do_eqp_test 111 { |
| 45 | WITH c(x) AS MATERIALIZED (VALUES(0),(1)) |
| 46 | SELECT c1.x||c2.x||c3.x FROM c c1, c c2, c c3; |
| 47 | } { |
| 48 | QUERY PLAN |
drh | 2f2091b | 2021-03-20 15:46:01 +0000 | [diff] [blame] | 49 | |--MATERIALIZE c |
drh | dee1cb3 | 2021-02-22 19:57:58 +0000 | [diff] [blame] | 50 | | `--SCAN 2 CONSTANT ROWS |
drh | 8210233 | 2021-03-20 15:11:29 +0000 | [diff] [blame] | 51 | |--SCAN c1 |
| 52 | |--SCAN c2 |
| 53 | `--SCAN c3 |
drh | dee1cb3 | 2021-02-22 19:57:58 +0000 | [diff] [blame] | 54 | } |
| 55 | |
| 56 | # Even though the CTE is not materialized, the self-join optimization |
| 57 | # kicks in and does the materialization for us. |
| 58 | # |
| 59 | do_execsql_test 120 { |
| 60 | WITH c(x) AS NOT MATERIALIZED (VALUES(0),(1)) |
| 61 | SELECT c1.x||c2.x||c3.x FROM c c1, c c2, c c3; |
| 62 | } {000 001 010 011 100 101 110 111} |
| 63 | do_eqp_test 121 { |
| 64 | WITH c(x) AS NOT MATERIALIZED (VALUES(0),(1)) |
| 65 | SELECT c1.x||c2.x||c3.x FROM c c1, c c2, c c3; |
| 66 | } { |
| 67 | QUERY PLAN |
drh | 2f2091b | 2021-03-20 15:46:01 +0000 | [diff] [blame] | 68 | |--MATERIALIZE c |
drh | dee1cb3 | 2021-02-22 19:57:58 +0000 | [diff] [blame] | 69 | | `--SCAN 2 CONSTANT ROWS |
drh | 8210233 | 2021-03-20 15:11:29 +0000 | [diff] [blame] | 70 | |--SCAN c1 |
| 71 | |--SCAN c2 |
| 72 | `--SCAN c3 |
drh | dee1cb3 | 2021-02-22 19:57:58 +0000 | [diff] [blame] | 73 | } |
| 74 | |
| 75 | do_execsql_test 130 { |
| 76 | WITH c(x) AS NOT MATERIALIZED (VALUES(0),(1)) |
| 77 | SELECT c1.x||c2.x||c3.x |
| 78 | FROM (SELECT x FROM c LIMIT 5) AS c1, |
| 79 | (SELECT x FROM c LIMIT 5) AS c2, |
| 80 | (SELECT x FROM c LIMIT 5) AS c3; |
| 81 | } {000 001 010 011 100 101 110 111} |
| 82 | do_eqp_test 131 { |
| 83 | WITH c(x) AS NOT MATERIALIZED (VALUES(0),(1)) |
| 84 | SELECT c1.x||c2.x||c3.x |
| 85 | FROM (SELECT x FROM c LIMIT 5) AS c1, |
| 86 | (SELECT x FROM c LIMIT 5) AS c2, |
| 87 | (SELECT x FROM c LIMIT 5) AS c3; |
| 88 | } { |
| 89 | QUERY PLAN |
drh | 8210233 | 2021-03-20 15:11:29 +0000 | [diff] [blame] | 90 | |--MATERIALIZE c1 |
| 91 | | |--CO-ROUTINE c |
drh | dee1cb3 | 2021-02-22 19:57:58 +0000 | [diff] [blame] | 92 | | | `--SCAN 2 CONSTANT ROWS |
drh | 8210233 | 2021-03-20 15:11:29 +0000 | [diff] [blame] | 93 | | `--SCAN c |
| 94 | |--MATERIALIZE c2 |
| 95 | | |--CO-ROUTINE c |
drh | dee1cb3 | 2021-02-22 19:57:58 +0000 | [diff] [blame] | 96 | | | `--SCAN 2 CONSTANT ROWS |
drh | 8210233 | 2021-03-20 15:11:29 +0000 | [diff] [blame] | 97 | | `--SCAN c |
| 98 | |--MATERIALIZE c3 |
| 99 | | |--CO-ROUTINE c |
drh | dee1cb3 | 2021-02-22 19:57:58 +0000 | [diff] [blame] | 100 | | | `--SCAN 2 CONSTANT ROWS |
drh | 8210233 | 2021-03-20 15:11:29 +0000 | [diff] [blame] | 101 | | `--SCAN c |
| 102 | |--SCAN c1 |
| 103 | |--SCAN c2 |
| 104 | `--SCAN c3 |
drh | dee1cb3 | 2021-02-22 19:57:58 +0000 | [diff] [blame] | 105 | } |
| 106 | |
| 107 | # The (SELECT x FROM c LIMIT N) subqueries get materialized once each. |
| 108 | # Show multiple materializations are shown. But there is only one |
| 109 | # materialization for c, shown by the "SCAN 2 CONSTANT ROWS" line. |
| 110 | # |
| 111 | do_execsql_test 140 { |
| 112 | WITH c(x) AS MATERIALIZED (VALUES(0),(1)) |
| 113 | SELECT c1.x||c2.x||c3.x |
| 114 | FROM (SELECT x FROM c LIMIT 5) AS c1, |
| 115 | (SELECT x FROM c LIMIT 6) AS c2, |
| 116 | (SELECT x FROM c LIMIT 7) AS c3; |
| 117 | } {000 001 010 011 100 101 110 111} |
| 118 | do_eqp_test 141 { |
| 119 | WITH c(x) AS MATERIALIZED (VALUES(0),(1)) |
| 120 | SELECT c1.x||c2.x||c3.x |
| 121 | FROM (SELECT x FROM c LIMIT 5) AS c1, |
| 122 | (SELECT x FROM c LIMIT 6) AS c2, |
| 123 | (SELECT x FROM c LIMIT 7) AS c3; |
| 124 | } { |
| 125 | QUERY PLAN |
drh | 8210233 | 2021-03-20 15:11:29 +0000 | [diff] [blame] | 126 | |--MATERIALIZE c1 |
| 127 | | |--MATERIALIZE c |
drh | dee1cb3 | 2021-02-22 19:57:58 +0000 | [diff] [blame] | 128 | | | `--SCAN 2 CONSTANT ROWS |
drh | 8210233 | 2021-03-20 15:11:29 +0000 | [diff] [blame] | 129 | | `--SCAN c |
| 130 | |--MATERIALIZE c2 |
| 131 | | `--SCAN c |
| 132 | |--MATERIALIZE c3 |
| 133 | | `--SCAN c |
| 134 | |--SCAN c1 |
| 135 | |--SCAN c2 |
| 136 | `--SCAN c3 |
drh | dee1cb3 | 2021-02-22 19:57:58 +0000 | [diff] [blame] | 137 | } |
| 138 | |
| 139 | do_execsql_test 150 { |
| 140 | WITH c(x) AS (VALUES(0),(1)) |
| 141 | SELECT c1.x||c2.x||c3.x |
| 142 | FROM (SELECT x FROM c LIMIT 5) AS c1, |
| 143 | (SELECT x FROM c LIMIT 6) AS c2, |
| 144 | (SELECT x FROM c LIMIT 7) AS c3; |
| 145 | } {000 001 010 011 100 101 110 111} |
| 146 | do_eqp_test 151 { |
| 147 | WITH c(x) AS (VALUES(0),(1)) |
| 148 | SELECT c1.x||c2.x||c3.x |
| 149 | FROM (SELECT x FROM c LIMIT 5) AS c1, |
| 150 | (SELECT x FROM c LIMIT 6) AS c2, |
| 151 | (SELECT x FROM c LIMIT 7) AS c3; |
| 152 | } { |
| 153 | QUERY PLAN |
drh | 8210233 | 2021-03-20 15:11:29 +0000 | [diff] [blame] | 154 | |--MATERIALIZE c1 |
| 155 | | |--MATERIALIZE c |
drh | dee1cb3 | 2021-02-22 19:57:58 +0000 | [diff] [blame] | 156 | | | `--SCAN 2 CONSTANT ROWS |
drh | 8210233 | 2021-03-20 15:11:29 +0000 | [diff] [blame] | 157 | | `--SCAN c |
| 158 | |--MATERIALIZE c2 |
| 159 | | `--SCAN c |
| 160 | |--MATERIALIZE c3 |
| 161 | | `--SCAN c |
| 162 | |--SCAN c1 |
| 163 | |--SCAN c2 |
| 164 | `--SCAN c3 |
drh | dee1cb3 | 2021-02-22 19:57:58 +0000 | [diff] [blame] | 165 | } |
| 166 | |
| 167 | do_execsql_test 160 { |
| 168 | WITH c(x) AS (VALUES(0),(1)) |
| 169 | SELECT c2.x + 100*(SELECT sum(x+1) FROM c WHERE c.x<=c2.x) |
| 170 | FROM c AS c2 WHERE c2.x<10; |
| 171 | } {100 301} |
| 172 | do_eqp_test 161 { |
| 173 | WITH c(x) AS (VALUES(0),(1)) |
| 174 | SELECT c2.x + 100*(SELECT sum(x+1) FROM c WHERE c.x<=c2.x) |
| 175 | FROM c AS c2 WHERE c2.x<10; |
| 176 | } { |
| 177 | QUERY PLAN |
drh | 2f2091b | 2021-03-20 15:46:01 +0000 | [diff] [blame] | 178 | |--MATERIALIZE c |
drh | dee1cb3 | 2021-02-22 19:57:58 +0000 | [diff] [blame] | 179 | | `--SCAN 2 CONSTANT ROWS |
drh | 8210233 | 2021-03-20 15:11:29 +0000 | [diff] [blame] | 180 | |--SCAN c2 |
drh | dee1cb3 | 2021-02-22 19:57:58 +0000 | [diff] [blame] | 181 | `--CORRELATED SCALAR SUBQUERY xxxxxx |
drh | 8210233 | 2021-03-20 15:11:29 +0000 | [diff] [blame] | 182 | `--SCAN c |
drh | dee1cb3 | 2021-02-22 19:57:58 +0000 | [diff] [blame] | 183 | } |
| 184 | |
| 185 | do_execsql_test 170 { |
| 186 | WITH c(x) AS NOT MATERIALIZED (VALUES(0),(1)) |
| 187 | SELECT c2.x + 100*(SELECT sum(x+1) FROM c WHERE c.x<=c2.x) |
| 188 | FROM c AS c2 WHERE c2.x<10; |
| 189 | } {100 301} |
| 190 | do_eqp_test 171 { |
| 191 | WITH c(x) AS NOT MATERIALIZED (VALUES(0),(1)) |
| 192 | SELECT c2.x + 100*(SELECT sum(x+1) FROM c WHERE c.x<=c2.x) |
| 193 | FROM c AS c2 WHERE c2.x<10; |
| 194 | } { |
| 195 | QUERY PLAN |
drh | 2f2091b | 2021-03-20 15:46:01 +0000 | [diff] [blame] | 196 | |--CO-ROUTINE c |
drh | dee1cb3 | 2021-02-22 19:57:58 +0000 | [diff] [blame] | 197 | | `--SCAN 2 CONSTANT ROWS |
drh | 8210233 | 2021-03-20 15:11:29 +0000 | [diff] [blame] | 198 | |--SCAN c2 |
drh | dee1cb3 | 2021-02-22 19:57:58 +0000 | [diff] [blame] | 199 | `--CORRELATED SCALAR SUBQUERY xxxxxx |
drh | 8210233 | 2021-03-20 15:11:29 +0000 | [diff] [blame] | 200 | |--CO-ROUTINE c |
drh | dee1cb3 | 2021-02-22 19:57:58 +0000 | [diff] [blame] | 201 | | `--SCAN 2 CONSTANT ROWS |
drh | 8210233 | 2021-03-20 15:11:29 +0000 | [diff] [blame] | 202 | `--SCAN c |
drh | dee1cb3 | 2021-02-22 19:57:58 +0000 | [diff] [blame] | 203 | } |
| 204 | |
| 205 | |
| 206 | do_execsql_test 200 { |
| 207 | CREATE TABLE t1(x); |
| 208 | INSERT INTO t1(x) VALUES(4); |
| 209 | CREATE VIEW t2(y) AS |
| 210 | WITH c(z) AS (VALUES(4),(5),(6)) |
| 211 | SELECT c1.z+c2.z*100+t1.x*10000 |
| 212 | FROM t1, |
| 213 | (SELECT z FROM c LIMIT 5) AS c1, |
| 214 | (SELECT z FROM c LIMIT 5) AS c2; |
| 215 | SELECT y FROM t2 ORDER BY y; |
| 216 | } {40404 40405 40406 40504 40505 40506 40604 40605 40606} |
| 217 | do_execsql_test 210 { |
| 218 | DROP VIEW t2; |
| 219 | CREATE VIEW t2(y) AS |
| 220 | WITH c(z) AS NOT MATERIALIZED (VALUES(4),(5),(6)) |
| 221 | SELECT c1.z+c2.z*100+t1.x*10000 |
| 222 | FROM t1, |
| 223 | (SELECT z FROM c LIMIT 5) AS c1, |
| 224 | (SELECT z FROM c LIMIT 5) AS c2; |
| 225 | SELECT y FROM t2 ORDER BY y; |
| 226 | } {40404 40405 40406 40504 40505 40506 40604 40605 40606} |
| 227 | do_eqp_test 211 { |
| 228 | SELECT y FROM t2 ORDER BY y; |
| 229 | } { |
| 230 | QUERY PLAN |
drh | 8210233 | 2021-03-20 15:11:29 +0000 | [diff] [blame] | 231 | |--MATERIALIZE c1 |
| 232 | | |--MATERIALIZE c |
drh | dee1cb3 | 2021-02-22 19:57:58 +0000 | [diff] [blame] | 233 | | | `--SCAN 3 CONSTANT ROWS |
drh | 8210233 | 2021-03-20 15:11:29 +0000 | [diff] [blame] | 234 | | `--SCAN c |
| 235 | |--MATERIALIZE c2 |
| 236 | | `--SCAN c |
| 237 | |--SCAN c1 |
| 238 | |--SCAN c2 |
| 239 | |--SCAN t1 |
drh | dee1cb3 | 2021-02-22 19:57:58 +0000 | [diff] [blame] | 240 | `--USE TEMP B-TREE FOR ORDER BY |
| 241 | } |
| 242 | do_execsql_test 220 { |
| 243 | DROP VIEW t2; |
| 244 | CREATE VIEW t2(y) AS |
| 245 | WITH c(z) AS MATERIALIZED (VALUES(4),(5),(6)) |
| 246 | SELECT c1.z+c2.z*100+t1.x*10000 |
| 247 | FROM t1, |
| 248 | (SELECT z FROM c LIMIT 5) AS c1, |
| 249 | (SELECT z FROM c LIMIT 5) AS c2; |
| 250 | SELECT y FROM t2 ORDER BY y; |
| 251 | } {40404 40405 40406 40504 40505 40506 40604 40605 40606} |
| 252 | |
| 253 | |
| 254 | |
| 255 | finish_test |