Add a few simple test cases for MATERIALIZED and NOT MATERIALIZED.

FossilOrigin-Name: 64878124c160f790bc5861fd799ada03bd7db0c4426b8abc3b7ad1f7aa181168

3 files changed, 262 insertions, 7 deletions

diff --git a/manifest b/manifest
index 7cb01ef31..ab0ad06cf 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Implement\sthe\sMATERIALIZED\sand\sNOT\sMATERIALIZED\shints\son\scommon\ntable\sexpressions.
-D 2021-02-22T16:42:09.548
+C Add\sa\sfew\ssimple\stest\scases\sfor\sMATERIALIZED\sand\sNOT\sMATERIALIZED.
+D 2021-02-22T19:57:58.697
 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1
 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea
 F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724
@@ -1788,6 +1788,7 @@ F test/with2.test e0030e2f0267a910d6c0e4f46f2dfe941c1cc0d4f659ba69b3597728e7e8f1
 F test/with3.test 85e059bf4c2ef5626411ee59f399b4bb4b4a0f009bcb7db86f254e570ed11831
 F test/with4.test 257be66c0c67fee1defbbac0f685c3465e2cad037f21ce65f23f86084f198205
 F test/with5.test 6248213c41fab36290b5b73aa3f937309dfba337004d9d8434c3fabc8c7d4be8
+F test/with6.test 3001b59179cbdc26a8c67ff8f46944e3141fdece9ab064c49bbf08459b67b207
 F test/withM.test 693b61765f2b387b5e3e24a4536e2e82de15ff64
 F test/without_rowid1.test e4034c0849ccc2e8bb749c69f15bd69bb9fcf8fe77e8d17ce02369604242fe83
 F test/without_rowid2.test af260339f79d13cb220288b67cd287fbcf81ad99
@@ -1905,8 +1906,7 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93
 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc
 F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e
 F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0
-P bfd5bf2c73110fcb36db9ba2a949ff516131fbd3e89325f88fe9f5c2b4ed87b2 ace54c5bd50176db7c60b7e3cf9293a86d9ecf2fea897467044020b9684c0af3
-R 35005ad1dc5275fcaf2f3b302048786b
-T +closed ace54c5bd50176db7c60b7e3cf9293a86d9ecf2fea897467044020b9684c0af3
+P b5a0778cc5a98a864bea72670f83262da940aceb91fa4cdf46ec097337a38a95
+R a0e8651539d2046a2eacfb9a26cd3050
 U drh
-Z 8fbd20db3052ba56fcae9ee5726a7d36
+Z 9ff3dc4427ac90918959bed5bdbcba59
diff --git a/manifest.uuid b/manifest.uuid
index 1b94224fc..afadf1dc1 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-b5a0778cc5a98a864bea72670f83262da940aceb91fa4cdf46ec097337a38a95
\ No newline at end of file
+64878124c160f790bc5861fd799ada03bd7db0c4426b8abc3b7ad1f7aa181168
\ No newline at end of file
diff --git a/test/with6.test b/test/with6.test
new file mode 100644
index 000000000..ef63ec8be
--- /dev/null
+++ b/test/with6.test
@@ -0,0 +1,255 @@
+# 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 xxxxxx
+  |  `--SCAN 2 CONSTANT ROWS
+  |--SCAN SUBQUERY xxxxxx AS c1
+  |--SCAN SUBQUERY xxxxxx AS c2
+  `--SCAN SUBQUERY xxxxxx AS 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 xxxxxx
+  |  `--SCAN 2 CONSTANT ROWS
+  |--SCAN SUBQUERY xxxxxx AS c1
+  |--SCAN SUBQUERY xxxxxx AS c2
+  `--SCAN SUBQUERY xxxxxx AS 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 xxxxxx
+  |  `--SCAN 2 CONSTANT ROWS
+  |--SCAN SUBQUERY xxxxxx AS c1
+  |--SCAN SUBQUERY xxxxxx AS c2
+  `--SCAN SUBQUERY xxxxxx AS 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 xxxxxx
+  |  |--CO-ROUTINE xxxxxx
+  |  |  `--SCAN 2 CONSTANT ROWS
+  |  `--SCAN SUBQUERY xxxxxx
+  |--MATERIALIZE xxxxxx
+  |  |--CO-ROUTINE xxxxxx
+  |  |  `--SCAN 2 CONSTANT ROWS
+  |  `--SCAN SUBQUERY xxxxxx
+  |--MATERIALIZE xxxxxx
+  |  |--CO-ROUTINE xxxxxx
+  |  |  `--SCAN 2 CONSTANT ROWS
+  |  `--SCAN SUBQUERY xxxxxx
+  |--SCAN SUBQUERY xxxxxx AS c1
+  |--SCAN SUBQUERY xxxxxx AS c2
+  `--SCAN SUBQUERY xxxxxx AS 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 xxxxxx
+  |  |--MATERIALIZE xxxxxx
+  |  |  `--SCAN 2 CONSTANT ROWS
+  |  `--SCAN SUBQUERY xxxxxx
+  |--MATERIALIZE xxxxxx
+  |  `--SCAN SUBQUERY xxxxxx
+  |--MATERIALIZE xxxxxx
+  |  `--SCAN SUBQUERY xxxxxx
+  |--SCAN SUBQUERY xxxxxx AS c1
+  |--SCAN SUBQUERY xxxxxx AS c2
+  `--SCAN SUBQUERY xxxxxx AS 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 xxxxxx
+  |  |--MATERIALIZE xxxxxx
+  |  |  `--SCAN 2 CONSTANT ROWS
+  |  `--SCAN SUBQUERY xxxxxx
+  |--MATERIALIZE xxxxxx
+  |  `--SCAN SUBQUERY xxxxxx
+  |--MATERIALIZE xxxxxx
+  |  `--SCAN SUBQUERY xxxxxx
+  |--SCAN SUBQUERY xxxxxx AS c1
+  |--SCAN SUBQUERY xxxxxx AS c2
+  `--SCAN SUBQUERY xxxxxx AS 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 xxxxxx
+  |  `--SCAN 2 CONSTANT ROWS
+  |--SCAN SUBQUERY xxxxxx AS c2
+  `--CORRELATED SCALAR SUBQUERY xxxxxx
+     `--SCAN SUBQUERY xxxxxx
+}
+
+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 xxxxxx
+  |  `--SCAN 2 CONSTANT ROWS
+  |--SCAN SUBQUERY xxxxxx AS c2
+  `--CORRELATED SCALAR SUBQUERY xxxxxx
+     |--CO-ROUTINE xxxxxx
+     |  `--SCAN 2 CONSTANT ROWS
+     `--SCAN SUBQUERY xxxxxx
+}
+
+
+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 xxxxxx
+  |  |--MATERIALIZE xxxxxx
+  |  |  `--SCAN 3 CONSTANT ROWS
+  |  `--SCAN SUBQUERY xxxxxx
+  |--MATERIALIZE xxxxxx
+  |  `--SCAN SUBQUERY xxxxxx
+  |--SCAN SUBQUERY xxxxxx AS c1
+  |--SCAN SUBQUERY xxxxxx AS c2
+  |--SCAN TABLE 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}
+
+
+
+finish_test