From 01a6c16e0c5bdfa5c2d36f5d9a401dd54dd95c78 Mon Sep 17 00:00:00 2001 From: drh <> Date: Mon, 18 Apr 2022 13:08:17 +0000 Subject: [PATCH] New RIGHT JOIN test cases, one of which is currently failing. FossilOrigin-Name: 9168cbbedd1c9d735279295a78212d830e24269f6d7e445e71faa2c06973223d --- manifest | 12 +++---- manifest.uuid | 2 +- test/join9.test | 95 ++++++++++++++++++++++++++++++++++++++++++++++--- 3 files changed, 98 insertions(+), 11 deletions(-) diff --git a/manifest b/manifest index ed0f446fb0..1eb18fd580 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Change\sthe\srow-value\sinitialization\ssubroutine\scall\sfrom\swithin\sthe\nright-join\sbody\ssubroutine\sfrom\san\sissue\sto\san\soptimization\sopportunity. -D 2022-04-18T11:22:46.211 +C New\sRIGHT\sJOIN\stest\scases,\sone\sof\swhich\sis\scurrently\sfailing. +D 2022-04-18T13:08:17.738 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724 @@ -1148,7 +1148,7 @@ F test/join5.test c4df54e2e204d7f1417bfbdd21ca324b4b07415c647595cc47798eacfddc96 F test/join6.test f809c025fa253f9e150c0e9afd4cef8813257bceeb6f46e04041228c9403cc2c F test/join7.test e5c9b1b729d7e1d0b4195e99833e0ff0cf2d88e7fdd32b49af1044f4c76f72d9 F test/join8.test 6ed7eed8993e060e5e9d07f6bcadf1bb634742a03b9824afbcfa193adf7f9965 -F test/join9.test afd899f0bda3db6d5fefae2275ad91802d056c7dcd109030100b87e0b75f47ff +F test/join9.test f5db0954fe5aee36fbecb75a80c764d5f0a4b35335016c5829b0d2616039df6e F test/journal1.test c7b768041b7f494471531e17abc2f4f5ebf9e5096984f43ed17c4eb80ba34497 F test/journal2.test 9dac6b4ba0ca79c3b21446bbae993a462c2397c4 F test/journal3.test 7c3cf23ffc77db06601c1fcfc9743de8441cb77db9d1aa931863d94f5ffa140e @@ -1948,8 +1948,8 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P 1549dcd2353903b70abadd428eeef971ab940df04fb05a6b83b04ee30932db6d -R bf21b74f8101c167d0386cef1bd86b5d +P 829d49f27c041651ffeadc1397e4dd87a9994c9290e13b93542dbd2c514769cd +R 6347c73ed0e7137db9741231b5b4504b U drh -Z 5ad80f89a1f49f3de1c0f70362c7802e +Z e66211318cb81105e5a2b7fa53682c62 # Remove this line to create a well-formed Fossil manifest. diff --git a/manifest.uuid b/manifest.uuid index f65776145d..bb4aeed99f 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -829d49f27c041651ffeadc1397e4dd87a9994c9290e13b93542dbd2c514769cd \ No newline at end of file +9168cbbedd1c9d735279295a78212d830e24269f6d7e445e71faa2c06973223d \ No newline at end of file diff --git a/test/join9.test b/test/join9.test index 69f7dcbd5f..e0edb7bc7b 100644 --- a/test/join9.test +++ b/test/join9.test @@ -33,13 +33,76 @@ foreach {id schema} { CREATE TABLE t4(id INT PRIMARY KEY, x TEXT) WITHOUT ROWID; CREATE TABLE t5(id INT PRIMARY KEY, y TEXT) WITHOUT ROWID; CREATE TABLE t6(id INT PRIMARY KEY, z INT) WITHOUT ROWID; - CREATE VIEW dual(dummy) AS VALUES('x'); + CREATE TABLE dual(dummy TEXT); + INSERT INTO dual(dummy) VALUES('x'); INSERT INTO t3(id,w) VALUES(2,'two'),(3,'three'),(6,'six'),(7,'seven'); INSERT INTO t4(id,x) VALUES(2,'alice'),(4,'bob'),(6,'cindy'),(8,'dave'); INSERT INTO t5(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow'),(4,'green'), (5,'blue'); INSERT INTO t6(id,z) VALUES(3,333),(4,444),(5,555),(0,1000),(9,999); } + 3 { + CREATE TABLE t3x(id INTEGER PRIMARY KEY, w TEXT); + CREATE TABLE t4x(id INTEGER PRIMARY KEY, x TEXT); + CREATE TABLE t5x(id INTEGER PRIMARY KEY, y TEXT); + CREATE TABLE t6x(id INTEGER PRIMARY KEY, z INT); + CREATE VIEW dual(dummy) AS VALUES('x'); + INSERT INTO t3x(id,w) VALUES(2,'two'),(3,'three'),(6,'six'),(7,'seven'); + INSERT INTO t4x(id,x) VALUES(2,'alice'),(4,'bob'),(6,'cindy'),(8,'dave'); + INSERT INTO t5x(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow'),(4,'green'), + (5,'blue'); + INSERT INTO t6x(id,z) VALUES(3,333),(4,444),(5,555),(0,1000),(9,999); + CREATE VIEW t3 AS SELECT * FROM t3x LIMIT 1000; + CREATE VIEW t4 AS SELECT * FROM t4x LIMIT 1000; + CREATE VIEW t5 AS SELECT * FROM t5x LIMIT 1000; + CREATE VIEW t6 AS SELECT * FROM t6x LIMIT 1000; + } + 4 { + CREATE TABLE t3a(id INTEGER PRIMARY KEY, w TEXT); + CREATE TABLE t3b(id INTEGER PRIMARY KEY, w TEXT); + CREATE TABLE t4a(id INTEGER PRIMARY KEY, x TEXT); + CREATE TABLE t4b(id INTEGER PRIMARY KEY, x TEXT); + CREATE TABLE t5a(id INTEGER PRIMARY KEY, y TEXT); + CREATE TABLE t5b(id INTEGER PRIMARY KEY, y TEXT); + CREATE TABLE t6a(id INTEGER PRIMARY KEY, z INT); + CREATE TABLE t6b(id INTEGER PRIMARY KEY, z INT); + CREATE VIEW dual(dummy) AS VALUES('x'); + INSERT INTO t3a(id,w) VALUES(2,'two'),(3,'three'); + INSERT INTO t3b(id,w) VALUES(6,'six'),(7,'seven'); + INSERT INTO t4a(id,x) VALUES(2,'alice'),(4,'bob'); + INSERT INTO t4b(id,x) VALUES(6,'cindy'),(8,'dave'); + INSERT INTO t5a(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow'); + INSERT INTO t5b(id,y) VALUES(4,'green'),(5,'blue'); + INSERT INTO t6a(id,z) VALUES(3,333),(4,444); + INSERT INTO t6b(id,z) VALUES(5,555),(0,1000),(9,999); + CREATE VIEW t3 AS SELECT * FROM t3a UNION ALL SELECT * FROM t3b; + CREATE VIEW t4 AS SELECT * FROM t4a UNION ALL SELECT * FROM t4b; + CREATE VIEW t5 AS SELECT * FROM t5a UNION ALL SELECT * FROM t5b; + CREATE VIEW t6 AS SELECT * FROM t6a UNION ALL SELECT * FROM t6b; + } + 5 { + CREATE TABLE t3a(id INTEGER PRIMARY KEY, w TEXT) WITHOUT ROWID; + CREATE TABLE t3b(id INTEGER PRIMARY KEY, w TEXT); + CREATE TABLE t4a(id INTEGER PRIMARY KEY, x TEXT) WITHOUT ROWID; + CREATE TABLE t4b(id INTEGER PRIMARY KEY, x TEXT) WITHOUT ROWID; + CREATE TABLE t5a(id INTEGER PRIMARY KEY, y TEXT); + CREATE TABLE t5b(id INTEGER PRIMARY KEY, y TEXT) WITHOUT ROWID; + CREATE TABLE t6a(id INTEGER PRIMARY KEY, z INT); + CREATE TABLE t6b(id INTEGER PRIMARY KEY, z INT); + CREATE VIEW dual(dummy) AS VALUES('x'); + INSERT INTO t3a(id,w) VALUES(2,'two'),(3,'three'); + INSERT INTO t3b(id,w) VALUES(6,'six'),(7,'seven'); + INSERT INTO t4a(id,x) VALUES(2,'alice'),(4,'bob'); + INSERT INTO t4b(id,x) VALUES(6,'cindy'),(8,'dave'); + INSERT INTO t5a(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow'); + INSERT INTO t5b(id,y) VALUES(4,'green'),(5,'blue'); + INSERT INTO t6a(id,z) VALUES(3,333),(4,444); + INSERT INTO t6b(id,z) VALUES(5,555),(0,1000),(9,999); + CREATE VIEW t3 AS SELECT * FROM t3a UNION ALL SELECT * FROM t3b; + CREATE VIEW t4 AS SELECT * FROM t4a UNION ALL SELECT * FROM t4b LIMIT 50; + CREATE VIEW t5 AS SELECT * FROM t5a UNION ALL SELECT * FROM t5b LIMIT 100; + CREATE VIEW t6 AS SELECT * FROM t6a UNION ALL SELECT * FROM t6b; + } } { reset_db db nullvalue - @@ -257,12 +320,12 @@ foreach {id schema} { # Verified by PG-14 using case 1 do_execsql_test join9-$id.600 { SELECT id, w, x, y, z - FROM t3 JOIN dual ON true + FROM t3 JOIN dual AS d1 ON true FULL JOIN t4 USING(id) JOIN dual AS d2 ON true NATURAL FULL JOIN t5 - JOIN dual AS d3 ON true FULL - JOIN t6 USING(id) + JOIN dual AS d3 ON true + FULL JOIN t6 USING(id) CROSS JOIN dual AS d4 ORDER BY 1; } { @@ -277,5 +340,29 @@ foreach {id schema} { 8 - dave - - 9 - - - 999 } + + # Verified by PG-14 using case 1 + do_execsql_test join9-$id.700 { + SELECT id, w, x, y, z + FROM t3 JOIN dual AS d1 ON true + FULL JOIN t4 USING(id) + JOIN dual AS d2 ON true + NATURAL FULL JOIN t5 + JOIN dual AS d3 ON true + FULL JOIN t6 USING(id) + CROSS JOIN dual AS d4 + WHERE x<>'bob' OR x IS NULL + ORDER BY 1; + } { + 0 - - - 1000 + 1 - - red - + 2 two alice orange - + 3 three - yellow 333 + 5 - - blue 555 + 6 six cindy - - + 7 seven - - - + 8 - dave - - + 9 - - - 999 + } } finish_test -- 2.47.2