summaryrefslogtreecommitdiffstats
path: root/tests/fixtures
diff options
context:
space:
mode:
Diffstat (limited to 'tests/fixtures')
-rw-r--r--tests/fixtures/optimizer/qualify_columns.sql4
-rw-r--r--tests/fixtures/optimizer/qualify_tables.sql4
-rw-r--r--tests/fixtures/optimizer/simplify.sql21
-rw-r--r--tests/fixtures/optimizer/tpc-ds/tpc-ds.sql112
-rw-r--r--tests/fixtures/pretty.sql16
5 files changed, 93 insertions, 64 deletions
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
index 7d58908..68c0caa 100644
--- a/tests/fixtures/optimizer/qualify_columns.sql
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -632,6 +632,7 @@ SELECT x.a + 1 AS i, x.a + 1 + 1 AS j, x.a + 1 + 1 + 1 AS k FROM x AS x;
# title: noop - reference comes before alias
# execute: false
+# validate_qualify_columns: false
SELECT i + 1 AS j, x.a + 1 AS i FROM x;
SELECT i + 1 AS j, x.a + 1 AS i FROM x AS x;
@@ -704,3 +705,6 @@ SELECT _q_0.a AS a, _q_0.b AS b, _q_1.b AS b, _q_1.c AS c FROM ((SELECT x.a AS a
SELECT b FROM ((SELECT a FROM x) INNER JOIN y ON a = b);
SELECT y.b AS b FROM ((SELECT x.a AS a FROM x AS x) AS _q_0 INNER JOIN y AS y ON _q_0.a = y.b);
+
+SELECT a, c FROM x TABLESAMPLE SYSTEM (10 ROWS) CROSS JOIN y TABLESAMPLE SYSTEM (10 ROWS);
+SELECT x.a AS a, y.c AS c FROM x AS x TABLESAMPLE SYSTEM (10 ROWS) CROSS JOIN y AS y TABLESAMPLE SYSTEM (10 ROWS);
diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql
index 4b7d33d..61d0b96 100644
--- a/tests/fixtures/optimizer/qualify_tables.sql
+++ b/tests/fixtures/optimizer/qualify_tables.sql
@@ -180,3 +180,7 @@ 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;
COPY INTO (SELECT * FROM x) TO 'data' WITH (FORMAT 'CSV');
COPY INTO (SELECT * FROM c.db.x AS x) TO 'data' WITH (FORMAT 'CSV');
+
+# title: tablesample
+SELECT 1 FROM x TABLESAMPLE SYSTEM (10 PERCENT) CROSS JOIN y TABLESAMPLE SYSTEM (10 PERCENT);
+SELECT 1 FROM c.db.x AS x TABLESAMPLE SYSTEM (10 PERCENT) CROSS JOIN c.db.y AS y TABLESAMPLE SYSTEM (10 PERCENT);
diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql
index 6035ee6..ab8f963 100644
--- a/tests/fixtures/optimizer/simplify.sql
+++ b/tests/fixtures/optimizer/simplify.sql
@@ -85,6 +85,27 @@ NULL;
NULL = NULL;
NULL;
+1 AND 0;
+FALSE;
+
+0 AND 1;
+FALSE;
+
+0 OR 1;
+TRUE;
+
+0 OR NULL;
+NULL;
+
+NULL OR 0;
+NULL;
+
+0 AND NULL;
+FALSE;
+
+NULL AND 0;
+FALSE;
+
-- Can't optimize this because different engines do different things
-- mysql converts to 0 and 1 but tsql does true and false
NULL <=> NULL;
diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
index 5b004fa..7bbeb27 100644
--- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
+++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
@@ -852,10 +852,10 @@ SELECT
SUM("x"."profit") AS "profit"
FROM "x" AS "x"
GROUP BY
-ROLLUP (
- "x"."channel",
- "x"."id"
-)
+ ROLLUP (
+ "x"."channel",
+ "x"."id"
+ )
ORDER BY
"channel",
"id"
@@ -2294,12 +2294,12 @@ SELECT
SUM("y"."number_sales") AS "_col_5"
FROM "y" AS "y"
GROUP BY
-ROLLUP (
- "y"."channel",
- "y"."i_brand_id",
- "y"."i_class_id",
- "y"."i_category_id"
-)
+ ROLLUP (
+ "y"."channel",
+ "y"."i_brand_id",
+ "y"."i_class_id",
+ "y"."i_category_id"
+ )
ORDER BY
"channel",
"i_brand_id",
@@ -2608,12 +2608,12 @@ JOIN "customer_address" AS "customer_address"
ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
AND "customer_address"."ca_state" IN ('KS', 'IA', 'AL', 'UT', 'VA', 'NC', 'TX')
GROUP BY
-ROLLUP (
- "item"."i_item_id",
- "customer_address"."ca_country",
- "customer_address"."ca_state",
- "customer_address"."ca_county"
-)
+ ROLLUP (
+ "item"."i_item_id",
+ "customer_address"."ca_country",
+ "customer_address"."ca_state",
+ "customer_address"."ca_county"
+ )
ORDER BY
"ca_country",
"ca_state",
@@ -2876,12 +2876,12 @@ JOIN "item" AS "item"
JOIN "warehouse" AS "warehouse"
ON "inventory"."inv_warehouse_sk" = "warehouse"."w_warehouse_sk"
GROUP BY
-ROLLUP (
- "item"."i_product_name",
- "item"."i_brand",
- "item"."i_class",
- "item"."i_category"
-)
+ ROLLUP (
+ "item"."i_product_name",
+ "item"."i_brand",
+ "item"."i_class",
+ "item"."i_category"
+ )
ORDER BY
"qoh",
"i_product_name",
@@ -3347,10 +3347,10 @@ JOIN "store" AS "store"
ON "store"."s_state" IN ('TN', 'TN', 'TN', 'TN', 'TN', 'TN')
AND "store"."s_store_sk" = "store_sales"."ss_store_sk"
GROUP BY
-ROLLUP (
- "item"."i_item_id",
- "store"."s_state"
-)
+ ROLLUP (
+ "item"."i_item_id",
+ "store"."s_state"
+ )
ORDER BY
"i_item_id",
"s_state"
@@ -4463,10 +4463,10 @@ JOIN "store" AS "store"
ON "store"."s_state" IN ('TN', 'TN', 'TN', 'TN', 'TN', 'TN', 'TN', 'TN')
AND "store"."s_store_sk" = "store_sales"."ss_store_sk"
GROUP BY
-ROLLUP (
- "item"."i_category",
- "item"."i_class"
-)
+ ROLLUP (
+ "item"."i_category",
+ "item"."i_class"
+ )
ORDER BY
"lochierarchy" DESC,
CASE WHEN "lochierarchy" = 0 THEN "i_category" END,
@@ -9017,16 +9017,16 @@ WITH "dw1" AS (
JOIN "store" AS "store"
ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
GROUP BY
- ROLLUP (
- "item"."i_category",
- "item"."i_class",
- "item"."i_brand",
- "item"."i_product_name",
- "date_dim"."d_year",
- "date_dim"."d_qoy",
- "date_dim"."d_moy",
- "store"."s_store_id"
- )
+ ROLLUP (
+ "item"."i_category",
+ "item"."i_class",
+ "item"."i_brand",
+ "item"."i_product_name",
+ "date_dim"."d_year",
+ "date_dim"."d_qoy",
+ "date_dim"."d_moy",
+ "store"."s_store_id"
+ )
), "dw2" AS (
SELECT
"dw1"."i_category" AS "i_category",
@@ -9396,10 +9396,10 @@ LEFT JOIN "_u_0" AS "_u_0"
WHERE
NOT "_u_0"."s_state" IS NULL
GROUP BY
-ROLLUP (
- "store"."s_state",
- "store"."s_county"
-)
+ ROLLUP (
+ "store"."s_state",
+ "store"."s_county"
+ )
ORDER BY
"lochierarchy" DESC,
CASE WHEN "lochierarchy" = 0 THEN "s_state" END,
@@ -10433,10 +10433,10 @@ SELECT
SUM("x"."profit") AS "profit"
FROM "x" AS "x"
GROUP BY
-ROLLUP (
- "x"."channel",
- "x"."id"
-)
+ ROLLUP (
+ "x"."channel",
+ "x"."id"
+ )
ORDER BY
"channel",
"id"
@@ -10937,10 +10937,10 @@ SELECT
SUM("x"."profit") AS "profit"
FROM "x" AS "x"
GROUP BY
-ROLLUP (
- "x"."channel",
- "x"."id"
-)
+ ROLLUP (
+ "x"."channel",
+ "x"."id"
+ )
ORDER BY
"channel",
"id"
@@ -11539,10 +11539,10 @@ JOIN "date_dim" AS "d1"
JOIN "item" AS "item"
ON "item"."i_item_sk" = "web_sales"."ws_item_sk"
GROUP BY
-ROLLUP (
- "item"."i_category",
- "item"."i_class"
-)
+ ROLLUP (
+ "item"."i_category",
+ "item"."i_class"
+ )
ORDER BY
"lochierarchy" DESC,
CASE WHEN "lochierarchy" = 0 THEN "i_category" END,
diff --git a/tests/fixtures/pretty.sql b/tests/fixtures/pretty.sql
index d4eb3b9..8e10517 100644
--- a/tests/fixtures/pretty.sql
+++ b/tests/fixtures/pretty.sql
@@ -99,14 +99,14 @@ WITH cte1 AS (
FOO(CASE WHEN a AND b THEN c AND d ELSE 3 END)
GROUP BY
x,
- GROUPING SETS (
- a,
- (b, c)
- ),
- CUBE (
- y,
- z
- )
+ GROUPING SETS (
+ a,
+ (b, c)
+ ),
+ CUBE (
+ y,
+ z
+ )
) AS x
)
SELECT
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-07 22:39:08 +0000