summaryrefslogtreecommitdiffstats
path: root/tests/fixtures
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--tests/fixtures/identity.sql12
-rw-r--r--tests/fixtures/optimizer/optimizer.sql45
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;