summaryrefslogtreecommitdiffstats
path: root/tests/fixtures/optimizer
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-05-03 09:12:24 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-05-03 09:12:24 +0000
commit98d5537435b2951b36c45f1fda667fa27c165794 (patch)
treed26b4dfa6cf91847100fe10a94a04dcc2ad36a86 /tests/fixtures/optimizer
parentAdding upstream version 11.5.2. (diff)
downloadsqlglot-98d5537435b2951b36c45f1fda667fa27c165794.tar.xz
sqlglot-98d5537435b2951b36c45f1fda667fa27c165794.zip
Adding upstream version 11.7.1.upstream/11.7.1
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/fixtures/optimizer')
-rw-r--r--tests/fixtures/optimizer/canonicalize.sql2
-rw-r--r--tests/fixtures/optimizer/normalize.sql3
-rw-r--r--tests/fixtures/optimizer/qualify_columns.sql23
-rw-r--r--tests/fixtures/optimizer/simplify.sql3
-rw-r--r--tests/fixtures/optimizer/tpc-ds/tpc-ds.sql8
5 files changed, 34 insertions, 5 deletions
diff --git a/tests/fixtures/optimizer/canonicalize.sql b/tests/fixtures/optimizer/canonicalize.sql
index 7582f3a..ccf2f16 100644
--- a/tests/fixtures/optimizer/canonicalize.sql
+++ b/tests/fixtures/optimizer/canonicalize.sql
@@ -11,7 +11,7 @@ SELECT CAST(1 + 3.2 AS DOUBLE) AS a FROM w AS w;
SELECT 1 + 3.2 AS "a" FROM "w" AS "w";
SELECT CAST("2022-01-01" AS DATE) + INTERVAL '1' day;
-SELECT CAST("2022-01-01" AS DATE) + INTERVAL '1' "day" AS "_col_0";
+SELECT CAST("2022-01-01" AS DATE) + INTERVAL '1' day AS "_col_0";
--------------------------------------
-- Ensure boolean predicates
diff --git a/tests/fixtures/optimizer/normalize.sql b/tests/fixtures/optimizer/normalize.sql
index a84fadf..803a474 100644
--- a/tests/fixtures/optimizer/normalize.sql
+++ b/tests/fixtures/optimizer/normalize.sql
@@ -39,3 +39,6 @@ A OR ((((B OR C) AND (B OR D)) OR C) AND (((B OR C) AND (B OR D)) OR D));
(A AND B) OR (C OR (D AND E));
(A OR C OR D) AND (A OR C OR E) AND (B OR C OR D) AND (B OR C OR E);
+
+SELECT * FROM x WHERE (A AND B) OR C;
+SELECT * FROM x WHERE (A OR C) AND (B OR C);
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
index 74e2d0a..3013bba 100644
--- a/tests/fixtures/optimizer/qualify_columns.sql
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -131,6 +131,14 @@ SELECT DATE_TRUNC(x.a, MONTH) AS a FROM x AS x;
SELECT x FROM READ_PARQUET('path.parquet', hive_partition=1);
SELECT _q_0.x AS x FROM READ_PARQUET('path.parquet', hive_partition = 1) AS _q_0;
+# execute: false
+select * from (values (1, 2));
+SELECT _q_0._col_0 AS _col_0, _q_0._col_1 AS _col_1 FROM (VALUES (1, 2)) AS _q_0(_col_0, _col_1);
+
+# execute: false
+select * from (values (1, 2)) x;
+SELECT x._col_0 AS _col_0, x._col_1 AS _col_1 FROM (VALUES (1, 2)) AS x(_col_0, _col_1);
+
--------------------------------------
-- Derived tables
--------------------------------------
@@ -317,6 +325,21 @@ SELECT COALESCE(y.b, z.b) AS b, COALESCE(y.c, z.c) AS c FROM y AS y JOIN z AS z
SELECT * FROM y JOIN z USING(b, c) WHERE b = 2 AND c = 3;
SELECT COALESCE(y.b, z.b) AS b, COALESCE(y.c, z.c) AS c FROM y AS y JOIN z AS z ON y.b = z.b AND y.c = z.c WHERE COALESCE(y.b, z.b) = 2 AND COALESCE(y.c, z.c) = 3;
+-- We can safely convert `b` to `x.b` in the following two queries, because the original queries
+-- would be invalid if `b` also existed in `t`'s schema (which we don't know), due to ambiguity.
+
+# execute: false
+SELECT b FROM x JOIN t USING(a);
+SELECT x.b AS b FROM x AS x JOIN t AS t ON x.a = t.a;
+
+# execute: false
+SELECT b FROM t JOIN x USING(a);
+SELECT x.b AS b FROM t AS t JOIN x AS x ON t.a = x.a;
+
+# execute: false
+SELECT a FROM t1 JOIN t2 USING(a);
+SELECT COALESCE(t1.a, t2.a) AS a FROM t1 AS t1 JOIN t2 AS t2 ON t1.a = t2.a;
+
--------------------------------------
-- Hint with table reference
--------------------------------------
diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql
index 54ec64b..a2cd859 100644
--- a/tests/fixtures/optimizer/simplify.sql
+++ b/tests/fixtures/optimizer/simplify.sql
@@ -572,3 +572,6 @@ x > 3;
'a' < 'b';
TRUE;
+
+x = 2018 OR x <> 2018;
+x <> 2018 OR x = 2018; \ No newline at end of file
diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
index d9a06cc..9168508 100644
--- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
+++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
@@ -2500,7 +2500,7 @@ JOIN "date_dim" AS "date_dim"
ON "catalog_sales"."cs_ship_date_sk" = "date_dim"."d_date_sk"
AND "date_dim"."d_date" >= '2002-3-01'
AND CAST("date_dim"."d_date" AS DATE) <= (
- CAST('2002-3-01' AS DATE) + INTERVAL '60' "day"
+ CAST('2002-3-01' AS DATE) + INTERVAL '60' day
)
JOIN "customer_address" AS "customer_address"
ON "catalog_sales"."cs_ship_addr_sk" = "customer_address"."ca_address_sk"
@@ -9420,7 +9420,7 @@ JOIN "date_dim" AS "date_dim_2"
AND "date_dim_2"."d_year" = 2002
JOIN "date_dim" AS "date_dim_3"
ON "catalog_sales"."cs_ship_date_sk" = "date_dim_3"."d_date_sk"
- AND "date_dim_3"."d_date" > CONCAT("date_dim_2"."d_date", INTERVAL '5' "day")
+ AND "date_dim_3"."d_date" > CONCAT("date_dim_2"."d_date", INTERVAL '5' day)
LEFT JOIN "promotion" AS "promotion"
ON "catalog_sales"."cs_promo_sk" = "promotion"."p_promo_sk"
LEFT JOIN "catalog_returns" AS "catalog_returns"
@@ -12200,7 +12200,7 @@ JOIN "date_dim" AS "date_dim"
ON "date_dim"."d_date" >= '2000-3-01'
AND "web_sales"."ws_ship_date_sk" = "date_dim"."d_date_sk"
AND CAST("date_dim"."d_date" AS DATE) <= (
- CAST('2000-3-01' AS DATE) + INTERVAL '60' "day"
+ CAST('2000-3-01' AS DATE) + INTERVAL '60' day
)
JOIN "customer_address" AS "customer_address"
ON "customer_address"."ca_state" = 'MT'
@@ -12295,7 +12295,7 @@ JOIN "date_dim" AS "date_dim"
ON "date_dim"."d_date" >= '2000-4-01'
AND "web_sales"."ws_ship_date_sk" = "date_dim"."d_date_sk"
AND CAST("date_dim"."d_date" AS DATE) <= (
- CAST('2000-4-01' AS DATE) + INTERVAL '60' "day"
+ CAST('2000-4-01' AS DATE) + INTERVAL '60' day
)
JOIN "customer_address" AS "customer_address"
ON "customer_address"."ca_state" = 'IN'