From 57c3067868d0a1da90ec0f2201dd91f031241274 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sun, 19 Mar 2023 11:22:04 +0100 Subject: Adding upstream version 11.4.1. Signed-off-by: Daniel Baumann --- tests/fixtures/optimizer/canonicalize.sql | 16 ++++++++++++ tests/fixtures/optimizer/optimizer.sql | 31 +++++++++++++++++++++++ tests/fixtures/optimizer/pushdown_projections.sql | 3 +++ 3 files changed, 50 insertions(+) (limited to 'tests/fixtures/optimizer') diff --git a/tests/fixtures/optimizer/canonicalize.sql b/tests/fixtures/optimizer/canonicalize.sql index 8c7cd45..50fee7f 100644 --- a/tests/fixtures/optimizer/canonicalize.sql +++ b/tests/fixtures/optimizer/canonicalize.sql @@ -9,3 +9,19 @@ SELECT CAST(1 AS VARCHAR) AS "a" FROM "w" AS "w"; SELECT CAST(1 + 3.2 AS DOUBLE) AS a FROM w AS w; SELECT 1 + 3.2 AS "a" FROM "w" AS "w"; + +-------------------------------------- +-- Ensure boolean predicates +-------------------------------------- + +SELECT a FROM x WHERE b; +SELECT "x"."a" AS "a" FROM "x" AS "x" WHERE "x"."b" <> 0; + +SELECT a FROM x GROUP BY a HAVING SUM(b); +SELECT "x"."a" AS "a" FROM "x" AS "x" GROUP BY "x"."a" HAVING SUM("x"."b") <> 0; + +SELECT a FROM x GROUP BY a HAVING SUM(b) AND TRUE; +SELECT "x"."a" AS "a" FROM "x" AS "x" GROUP BY "x"."a" HAVING SUM("x"."b") <> 0 AND TRUE; + +SELECT a FROM x WHERE 1; +SELECT "x"."a" AS "a" FROM "x" AS "x" WHERE 1 <> 0; diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql index a14e325..0b5504d 100644 --- a/tests/fixtures/optimizer/optimizer.sql +++ b/tests/fixtures/optimizer/optimizer.sql @@ -386,6 +386,29 @@ SELECT "x"."b" + 1 AS "c" FROM "x" AS "x"; +# title: unqualified struct element is selected in the outer query +# execute: false +WITH "cte" AS ( + SELECT + FROM_JSON("value", 'STRUCT>>>') AS "struct" + FROM "tbl" +) SELECT "struct"."f1"."f2"."f3"."f4" AS "f4" FROM "cte"; +SELECT + FROM_JSON("tbl"."value", 'STRUCT>>>')."f1"."f2"."f3"."f4" AS "f4" +FROM "tbl" AS "tbl"; + +# title: qualified struct element is selected in the outer query +# execute: false +WITH "cte" AS ( + SELECT + FROM_JSON("value", 'STRUCT, STRUCT>') AS "struct" + FROM "tbl" +) SELECT "cte"."struct"."f1"."f2" AS "f2", "cte"."struct"."f1"."f3" AS "f3" FROM "cte"; +SELECT + FROM_JSON("tbl"."value", 'STRUCT, STRUCT>')."f1"."f2" AS "f2", + FROM_JSON("tbl"."value", 'STRUCT, STRUCT>')."f1"."f3" AS "f3" +FROM "tbl" AS "tbl"; + # title: left join doesnt push down predicate to join in merge subqueries # execute: false SELECT @@ -430,3 +453,11 @@ LEFT JOIN "unlocked" AS "unlocked" WHERE CASE WHEN "unlocked"."company_id" IS NULL THEN 0 ELSE 1 END = FALSE AND NOT "company_table_2"."id" IS NULL; + +# title: db.table alias clash +# execute: false +select * from db1.tbl, db2.tbl; +SELECT + * +FROM "db1"."tbl" AS "tbl" +CROSS JOIN "db2"."tbl" AS "tbl_2"; diff --git a/tests/fixtures/optimizer/pushdown_projections.sql b/tests/fixtures/optimizer/pushdown_projections.sql index f3b1a69..6ff9383 100644 --- a/tests/fixtures/optimizer/pushdown_projections.sql +++ b/tests/fixtures/optimizer/pushdown_projections.sql @@ -4,6 +4,9 @@ SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0; SELECT 1 FROM (SELECT * FROM x) WHERE b = 2; SELECT 1 AS "1" FROM (SELECT x.b AS b FROM x AS x) AS _q_0 WHERE _q_0.b = 2; +SELECT a, b, a from x; +SELECT x.a AS a, x.b AS b, x.a AS a FROM x AS x; + SELECT (SELECT c FROM y WHERE q.b = y.b) FROM (SELECT * FROM x) AS q; SELECT (SELECT y.c AS c FROM y AS y WHERE q.b = y.b) AS _col_0 FROM (SELECT x.b AS b FROM x AS x) AS q; -- cgit v1.2.3