summaryrefslogtreecommitdiffstats
path: root/tests/fixtures/optimizer
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-15 05:02:12 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-15 05:02:12 +0000
commit07115fb6b7dc48595d30c3d1568fbeff0388d096 (patch)
treef8017ffa5da7c1a44443bbf5fc6c3937b243ac5e /tests/fixtures/optimizer
parentAdding upstream version 23.7.0. (diff)
downloadsqlglot-07115fb6b7dc48595d30c3d1568fbeff0388d096.tar.xz
sqlglot-07115fb6b7dc48595d30c3d1568fbeff0388d096.zip
Adding upstream version 23.10.0.upstream/23.10.0
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/fixtures/optimizer')
-rw-r--r--tests/fixtures/optimizer/annotate_types.sql57
-rw-r--r--tests/fixtures/optimizer/optimizer.sql42
-rw-r--r--tests/fixtures/optimizer/qualify_columns.sql10
-rw-r--r--tests/fixtures/optimizer/qualify_tables.sql7
4 files changed, 116 insertions, 0 deletions
diff --git a/tests/fixtures/optimizer/annotate_types.sql b/tests/fixtures/optimizer/annotate_types.sql
new file mode 100644
index 0000000..e781765
--- /dev/null
+++ b/tests/fixtures/optimizer/annotate_types.sql
@@ -0,0 +1,57 @@
+5;
+INT;
+
+5.3;
+DOUBLE;
+
+'bla';
+VARCHAR;
+
+True;
+bool;
+
+false;
+bool;
+
+null;
+null;
+CASE WHEN x THEN NULL ELSE 1 END;
+INT;
+
+CASE WHEN x THEN 1 ELSE NULL END;
+INT;
+
+IF(true, 1, null);
+INT;
+
+IF(true, null, 1);
+INT;
+
+STRUCT(1 AS col);
+STRUCT<col INT>;
+
+STRUCT(1 AS col, 2.5 AS row);
+STRUCT<col INT, row DOUBLE>;
+
+STRUCT(1);
+STRUCT<INT>;
+
+STRUCT(1 AS col, 2.5 AS row, struct(3.5 AS inner_col, 4 AS inner_row) AS nested_struct);
+STRUCT<col INT, row DOUBLE, nested_struct STRUCT<inner_col DOUBLE, inner_row INT>>;
+
+STRUCT(1 AS col, 2.5, ARRAY[1, 2, 3] AS nested_array, 'foo');
+STRUCT<col INT, DOUBLE, nested_array ARRAY<INT>, VARCHAR>;
+
+STRUCT(1, 2.5, 'bar');
+STRUCT<INT, DOUBLE, VARCHAR>;
+
+STRUCT(1 AS "CaseSensitive");
+STRUCT<"CaseSensitive" INT>;
+
+# dialect: duckdb
+STRUCT_PACK(a := 1, b := 2.5);
+STRUCT<a INT, b DOUBLE>;
+
+# dialect: presto
+ROW(1, 2.5, 'foo');
+STRUCT<INT, DOUBLE, VARCHAR>;
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index cc72e6d..37ef4fd 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -1388,3 +1388,45 @@ WHERE
ORDER BY
COUNT(DISTINCT `cs1`.`cs_order_number`)
LIMIT 100;
+
+# execute: false
+SELECT
+ *
+FROM event
+WHERE priority = 'High' AND tagname IN (
+ SELECT
+ tag_input AS tagname
+ FROM cascade
+ WHERE tag_input = 'XXX' OR tag_output = 'XXX'
+ UNION
+ SELECT
+ tag_output AS tagname
+ FROM cascade
+ WHERE tag_input = 'XXX' OR tag_output = 'XXX'
+);
+WITH "_u_0" AS (
+ SELECT
+ "cascade"."tag_input" AS "tagname"
+ FROM "cascade" AS "cascade"
+ WHERE
+ "cascade"."tag_input" = 'XXX' OR "cascade"."tag_output" = 'XXX'
+ UNION
+ SELECT
+ "cascade"."tag_output" AS "tagname"
+ FROM "cascade" AS "cascade"
+ WHERE
+ "cascade"."tag_input" = 'XXX' OR "cascade"."tag_output" = 'XXX'
+), "_u_1" AS (
+ SELECT
+ "cascade"."tag_input" AS "tagname"
+ FROM "_u_0" AS "_u_0"
+ GROUP BY
+ "cascade"."tag_input"
+)
+SELECT
+ *
+FROM "event" AS "event"
+LEFT JOIN "_u_1" AS "_u_1"
+ ON "_u_1"."tagname" = "event"."tagname"
+WHERE
+ "event"."priority" = 'High' AND NOT "_u_1"."tagname" IS NULL;
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
index 289145b..8baf961 100644
--- a/tests/fixtures/optimizer/qualify_columns.sql
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -492,6 +492,11 @@ SELECT x AS x, offset AS offset FROM UNNEST([1, 2]) AS x WITH OFFSET AS offset;
select * from unnest ([1, 2]) as x with offset as y;
SELECT x AS x, y AS y FROM UNNEST([1, 2]) AS x WITH OFFSET AS y;
+# dialect: bigquery
+# execute: false
+select x, a, x.a from unnest([STRUCT(1 AS a)]) as x;
+SELECT x AS x, a AS a, x.a AS a FROM UNNEST([STRUCT(1 AS a)]) AS x;
+
# dialect: presto
SELECT x.a, i.b FROM x CROSS JOIN UNNEST(SPLIT(CAST(b AS VARCHAR), ',')) AS i(b);
SELECT x.a AS a, i.b AS b FROM x AS x CROSS JOIN UNNEST(SPLIT(CAST(x.b AS VARCHAR), ',')) AS i(b);
@@ -508,6 +513,11 @@ SELECT t.c1 AS c1, t.c2 AS c2, t.c3 AS c3 FROM FOO(bar) AS t(c1, c2, c3);
SELECT c1, c3 FROM foo(bar) AS t(c1, c2, c3);
SELECT t.c1 AS c1, t.c3 AS c3 FROM FOO(bar) AS t(c1, c2, c3);
+# dialect: redshift
+# execute: false
+SELECT c.f::VARCHAR(MAX) AS f, e AS e FROM a.b AS c, c.d AS e;
+SELECT CAST(c.f AS VARCHAR(MAX)) AS f, e AS e FROM a.b AS c, c.d AS e;
+
--------------------------------------
-- Window functions
--------------------------------------
diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql
index f651a87..104400e 100644
--- a/tests/fixtures/optimizer/qualify_tables.sql
+++ b/tests/fixtures/optimizer/qualify_tables.sql
@@ -166,3 +166,10 @@ WITH cte AS (SELECT b FROM c.db.y AS y) INSERT INTO c.db.s SELECT * FROM cte AS
# title: qualify wrapped query
(SELECT x FROM t);
(SELECT x FROM c.db.t AS t);
+
+# title: replace columns with db/catalog refs
+SELECT db1.a.id, db2.a.id FROM db1.a JOIN db2.a ON db1.a.id = db2.a.id;
+SELECT a.id, a_2.id FROM c.db1.a AS a JOIN c.db2.a AS a_2 ON a.id = a_2.id;
+
+SELECT cat.db1.a.id, db2.a.id FROM cat.db1.a JOIN db2.a ON cat.db1.a.id = db2.a.id;
+SELECT a.id, a_2.id FROM cat.db1.a AS a JOIN c.db2.a AS a_2 ON a.id = a_2.id;