2 files changed, 56 insertions, 1 deletions
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index 5e2260c..0677a05 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -558,7 +558,7 @@ CREATE TABLE a, BEFORE JOURNAL, AFTER JOURNAL, FREESPACE=1, DEFAULT DATABLOCKSIZ
 CREATE TABLE a, DUAL JOURNAL, DUAL AFTER JOURNAL, MERGEBLOCKRATIO=1 PERCENT, DATABLOCKSIZE=10 KILOBYTES (a INT)
 CREATE TABLE a, DUAL BEFORE JOURNAL, LOCAL AFTER JOURNAL, MAXIMUM DATABLOCKSIZE, BLOCKCOMPRESSION=AUTOTEMP(c1 INT) (a INT)
 CREATE SET GLOBAL TEMPORARY TABLE a, NO BEFORE JOURNAL, NO AFTER JOURNAL, MINIMUM DATABLOCKSIZE, BLOCKCOMPRESSION=NEVER (a INT)
-CREATE MULTISET TABLE a, NOT LOCAL AFTER JOURNAL, FREESPACE=1 PERCENT, DATABLOCKSIZE=10 BYTES, WITH NO CONCURRENT ISOLATED LOADING FOR ALL (a INT)
+CREATE VOLATILE MULTISET TABLE a, NOT LOCAL AFTER JOURNAL, FREESPACE=1 PERCENT, DATABLOCKSIZE=10 BYTES, WITH NO CONCURRENT ISOLATED LOADING FOR ALL (a INT)
 CREATE ALGORITHM=UNDEFINED DEFINER=foo@% SQL SECURITY DEFINER VIEW a AS (SELECT a FROM b)
 CREATE TEMPORARY TABLE x AS SELECT a FROM d
 CREATE TEMPORARY TABLE IF NOT EXISTS x AS SELECT a FROM d
@@ -591,6 +591,8 @@ CREATE UNIQUE INDEX abc ON t (a, b, b)
 CREATE UNIQUE INDEX IF NOT EXISTS my_idx ON tbl (a, b)
 CREATE SCHEMA x
 CREATE SCHEMA IF NOT EXISTS y
+CREATE DATABASE x
+CREATE DATABASE IF NOT EXISTS y
 CREATE PROCEDURE IF NOT EXISTS a.b.c() AS 'DECLARE BEGIN; END'
 CREATE OR REPLACE STAGE
 DESCRIBE x
@@ -619,6 +621,7 @@ ALTER SEQUENCE IF EXISTS baz RESTART WITH boo
 ALTER TYPE electronic_mail RENAME TO email
 ALTER VIEW foo ALTER COLUMN bla SET DEFAULT 'NOT SET'
 ALTER DOMAIN foo VALIDATE CONSTRAINT bla
+ALTER schema doo
 ANALYZE a.y
 DELETE FROM x WHERE y > 1
 DELETE FROM y
@@ -671,6 +674,11 @@ UPDATE db.tbl_name SET foo = 123, foo_1 = 234 WHERE tbl_name.bar = 234
 TRUNCATE TABLE x
 OPTIMIZE TABLE y
 VACUUM FREEZE my_table
+COMMENT ON ACCESS METHOD gin IS 'GIN index access method'
+COMMENT ON COLUMN my_schema.my_table.my_column IS 'Employee ID number'
+COMMENT ON DATABASE my_database IS 'Development Database'
+COMMENT ON PROCEDURE my_proc(integer, integer) IS 'Runs a report'
+COMMENT ON TABLE my_schema.my_table IS 'Employee Information'
 WITH a AS (SELECT 1) INSERT INTO b SELECT * FROM a
 WITH a AS (SELECT * FROM b) UPDATE a SET col = 1
 WITH a AS (SELECT * FROM b) CREATE TABLE b AS SELECT * FROM a
@@ -753,3 +761,5 @@ SELECT RIGHT.FOO FROM BLA AS RIGHT
 SELECT LEFT FROM LEFT LEFT JOIN RIGHT RIGHT JOIN LEFT
 SELECT * FROM x WHERE name ILIKE ANY XXX('a', 'b')
 SELECT * FROM x WHERE name LIKE ANY XXX('a', 'b')
+a OVERLAPS b
+GRANT INSERT ON foo TO bla
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index 6ccf24e..a14e325 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -385,3 +385,48 @@ SELECT
   "x"."a" + 1 AS "b",
   "x"."b" + 1 AS "c"
 FROM "x" AS "x";
+
+# title: left join doesnt push down predicate to join in merge subqueries
+# execute: false
+SELECT
+  main_query.id,
+  main_query.score
+FROM (
+  SELECT
+    alias_1.id,
+    score
+  FROM (
+    SELECT
+      company_table.score AS score,
+      id
+    FROM company_table
+  ) AS alias_1
+  JOIN (
+    SELECT
+      id
+    FROM (
+      SELECT
+        company_table_2.id,
+        CASE WHEN unlocked.company_id IS NULL THEN 0 ELSE 1 END AS is_exported
+      FROM company_table AS company_table_2
+      LEFT JOIN unlocked AS unlocked
+        ON company_table_2.id = unlocked.company_id
+    )
+    WHERE
+      NOT id IS NULL AND is_exported = FALSE
+  ) AS alias_2
+    ON (
+      alias_1.id = alias_2.id
+    )
+) AS main_query;
+SELECT
+  "company_table"."id" AS "id",
+  "company_table"."score" AS "score"
+FROM "company_table" AS "company_table"
+JOIN "company_table" AS "company_table_2"
+  ON "company_table"."id" = "company_table_2"."id"
+LEFT JOIN "unlocked" AS "unlocked"
+  ON "company_table_2"."id" = "unlocked"."company_id"
+WHERE
+  CASE WHEN "unlocked"."company_id" IS NULL THEN 0 ELSE 1 END = FALSE
+  AND NOT "company_table_2"."id" IS NULL;