summaryrefslogtreecommitdiffstats
path: root/tests/fixtures/optimizer
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-06-22 18:53:31 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-06-22 18:53:31 +0000
commit20d090151fbc2e75394fc456f49f0078e59752d8 (patch)
tree084494962f092ff80f5ef8fdba1b917206abbc83 /tests/fixtures/optimizer
parentAdding upstream version 16.2.1. (diff)
downloadsqlglot-20d090151fbc2e75394fc456f49f0078e59752d8.tar.xz
sqlglot-20d090151fbc2e75394fc456f49f0078e59752d8.zip
Adding upstream version 16.4.0.upstream/16.4.0
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/fixtures/optimizer')
-rw-r--r--tests/fixtures/optimizer/optimizer.sql35
-rw-r--r--tests/fixtures/optimizer/simplify.sql6
2 files changed, 41 insertions, 0 deletions
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index 0cb1a58..214535a 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -646,3 +646,38 @@ CROSS JOIN LATERAL (
"l"."log_date" DESC NULLS LAST
LIMIT 1
) AS "l";
+
+# title: bigquery column identifiers are case-insensitive
+# execute: false
+# dialect: bigquery
+WITH cte AS (
+ SELECT
+ refresh_date AS `reFREsh_date`,
+ term AS `TeRm`,
+ `rank`
+ FROM `bigquery-public-data.GooGle_tReNDs.TOp_TeRmS`
+)
+SELECT
+ refresh_date AS `Day`,
+ term AS Top_Term,
+ rank,
+FROM cte
+WHERE
+ rank = 1
+ AND refresh_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 2 WEEK)
+GROUP BY `dAy`, `top_term`, rank
+ORDER BY `DaY` DESC;
+SELECT
+ `TOp_TeRmS`.`refresh_date` AS `day`,
+ `TOp_TeRmS`.`term` AS `top_term`,
+ `TOp_TeRmS`.`rank` AS `rank`
+FROM `bigquery-public-data`.`GooGle_tReNDs`.`TOp_TeRmS` AS `TOp_TeRmS`
+WHERE
+ `TOp_TeRmS`.`rank` = 1
+ AND CAST(`TOp_TeRmS`.`refresh_date` AS DATE) >= DATE_SUB(CURRENT_DATE, INTERVAL 2 WEEK)
+GROUP BY
+ `TOp_TeRmS`.`refresh_date`,
+ `TOp_TeRmS`.`term`,
+ `TOp_TeRmS`.`rank`
+ORDER BY
+ `day` DESC;
diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql
index 5c8d371..e0aded4 100644
--- a/tests/fixtures/optimizer/simplify.sql
+++ b/tests/fixtures/optimizer/simplify.sql
@@ -240,6 +240,12 @@ A AND B AND C;
SELECT x WHERE TRUE;
SELECT x;
+SELECT x FROM y LEFT JOIN z ON TRUE;
+SELECT x FROM y CROSS JOIN z;
+
+SELECT x FROM y JOIN z USING (x);
+SELECT x FROM y JOIN z USING (x);
+
--------------------------------------
-- Parenthesis removal
--------------------------------------