test/with6.test

   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
  33   |--MATERIALIZE c
  34   |  `--SCAN 2 CONSTANT ROWS
  35   |--SCAN c1
  36   |--SCAN c2
  37   `--SCAN c3
  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
  49   |--MATERIALIZE c
  50   |  `--SCAN 2 CONSTANT ROWS
  51   |--SCAN c1
  52   |--SCAN c2
  53   `--SCAN c3
  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
  68   |--MATERIALIZE c
  69   |  `--SCAN 2 CONSTANT ROWS
  70   |--SCAN c1
  71   |--SCAN c2
  72   `--SCAN c3
  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
  90   |--MATERIALIZE c1
  91   |  |--CO-ROUTINE c
  92   |  |  `--SCAN 2 CONSTANT ROWS
  93   |  `--SCAN c
  94   |--MATERIALIZE c2
  95   |  |--CO-ROUTINE c
  96   |  |  `--SCAN 2 CONSTANT ROWS
  97   |  `--SCAN c
  98   |--MATERIALIZE c3
  99   |  |--CO-ROUTINE c
 100   |  |  `--SCAN 2 CONSTANT ROWS
 101   |  `--SCAN c
 102   |--SCAN c1
 103   |--SCAN c2
 104   `--SCAN c3
 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
 126   |--MATERIALIZE c1
 127   |  |--MATERIALIZE c
 128   |  |  `--SCAN 2 CONSTANT ROWS
 129   |  `--SCAN c
 130   |--MATERIALIZE c2
 131   |  `--SCAN c
 132   |--MATERIALIZE c3
 133   |  `--SCAN c
 134   |--SCAN c1
 135   |--SCAN c2
 136   `--SCAN c3
 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
 154   |--MATERIALIZE c1
 155   |  |--MATERIALIZE c
 156   |  |  `--SCAN 2 CONSTANT ROWS
 157   |  `--SCAN c
 158   |--MATERIALIZE c2
 159   |  `--SCAN c
 160   |--MATERIALIZE c3
 161   |  `--SCAN c
 162   |--SCAN c1
 163   |--SCAN c2
 164   `--SCAN c3
 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
 178   |--MATERIALIZE c
 179   |  `--SCAN 2 CONSTANT ROWS
 180   |--SCAN c2
 181   `--CORRELATED SCALAR SUBQUERY xxxxxx
 182      `--SCAN c
 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
 196   |--CO-ROUTINE c
 197   |  `--SCAN 2 CONSTANT ROWS
 198   |--SCAN c2
 199   `--CORRELATED SCALAR SUBQUERY xxxxxx
 200      |--CO-ROUTINE c
 201      |  `--SCAN 2 CONSTANT ROWS
 202      `--SCAN c
 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
 231   |--MATERIALIZE c1
 232   |  |--MATERIALIZE c
 233   |  |  `--SCAN 3 CONSTANT ROWS
 234   |  `--SCAN c
 235   |--MATERIALIZE c2
 236   |  `--SCAN c
 237   |--SCAN c1
 238   |--SCAN c2
 239   |--SCAN t1
 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