You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@datafusion.apache.org by gi...@apache.org on 2026/06/16 08:13:38 UTC
(datafusion) branch gh-readonly-queue/main/pr-22938-3c6734ebf0146e7407f3c35c236e38c20100de24 created (now 8cda78b646)
This is an automated email from the ASF dual-hosted git repository.
github-merge-queue[bot] pushed a change to branch gh-readonly-queue/main/pr-22938-3c6734ebf0146e7407f3c35c236e38c20100de24
in repository https://gitbox.apache.org/repos/asf/datafusion.git
at 8cda78b646 Remove orphaned `snowflake_flatten_validation.sql` script (#22938)
This branch includes the following new commits:
new 8cda78b646 Remove orphaned `snowflake_flatten_validation.sql` script (#22938)
The 1 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org
(datafusion) 01/01: Remove orphaned `snowflake_flatten_validation.sql` script (#22938)
Posted by gi...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
github-merge-queue[bot] pushed a commit to branch gh-readonly-queue/main/pr-22938-3c6734ebf0146e7407f3c35c236e38c20100de24
in repository https://gitbox.apache.org/repos/asf/datafusion.git
commit 8cda78b6461d3ead75c7f16d2b99776628ebf2f2
Author: Adam Gutglick <ad...@gmail.com>
AuthorDate: Tue Jun 16 09:13:25 2026 +0100
Remove orphaned `snowflake_flatten_validation.sql` script (#22938)
## Which issue does this PR close?
- Closes #.
## Rationale for this change
This file seems to be a leftover of #21593. I see what its trying to do,
but most developers don't have access to a Snowflake instance, and the
transformations are already all coded in tests.
I looked into turning it into some sort of SLT tests, but I couldn't
find a Snowflake-provided mock server.
## What changes are included in this PR?
- Remove `snowflake_flatten_validation.sql`
## Are these changes tested?
Existing tests. I ran all the tests in `datafusion-sql` to make sure
there's no hidden dependency I missed.
## Are there any user-facing changes?
None
Signed-off-by: Adam Gutglick <ad...@gmail.com>
---
snowflake_flatten_validation.sql | 219 ---------------------------------------
1 file changed, 219 deletions(-)
diff --git a/snowflake_flatten_validation.sql b/snowflake_flatten_validation.sql
deleted file mode 100644
index cae6f5ea59..0000000000
--- a/snowflake_flatten_validation.sql
+++ /dev/null
@@ -1,219 +0,0 @@
--- ============================================================================
--- Snowflake LATERAL FLATTEN validation queries
---
--- Run this file against a real Snowflake instance to verify that the
--- Unparser-generated SQL is syntactically and semantically correct.
---
--- Each section shows:
--- 1. The DataFusion input (SQL parsed by the planner)
--- 2. The Snowflake SQL produced by the Unparser
---
--- NOTE: The Unparser emits array literals as [1, 2, 3] (DataFusion syntax).
--- Snowflake requires ARRAY_CONSTRUCT(1, 2, 3). The queries below use
--- ARRAY_CONSTRUCT so they can run directly on Snowflake. The exact Unparser
--- output is shown in the "Unparser output:" comment above each query.
--- ============================================================================
-
--- ----------------------------------------------------------------------------
--- Setup: create and seed test tables
--- ----------------------------------------------------------------------------
-
-CREATE OR REPLACE TABLE source (
- items ARRAY
-);
-
-INSERT INTO source SELECT PARSE_JSON('[1, 2, 3]');
-INSERT INTO source SELECT PARSE_JSON('["a", "b"]');
-INSERT INTO source SELECT NULL;
-
-CREATE OR REPLACE TABLE unnest_table (
- array_col ARRAY
-);
-
-INSERT INTO unnest_table SELECT PARSE_JSON('[10, 20, 30]');
-INSERT INTO unnest_table SELECT PARSE_JSON('[40, 50]');
-INSERT INTO unnest_table SELECT NULL;
-
-CREATE OR REPLACE TABLE multi_array_table (
- column_a ARRAY,
- column_b ARRAY
-);
-
-INSERT INTO multi_array_table SELECT PARSE_JSON('[1, 2, 3]'), PARSE_JSON('["x", "y"]');
-INSERT INTO multi_array_table SELECT PARSE_JSON('[4]'), PARSE_JSON('["z"]');
-
--- ============================================================================
--- Roundtrip tests: SQL parsed → plan → Snowflake SQL
--- ============================================================================
-
--- --------------------------------------------------------------------------
--- Test: snowflake_unnest_to_lateral_flatten_simple
--- DataFusion input: SELECT * FROM UNNEST([1,2,3])
--- Unparser output: SELECT "_unnest_1"."VALUE" FROM LATERAL FLATTEN(INPUT => [1, 2, 3]) AS "_unnest_1"
--- --------------------------------------------------------------------------
-SELECT "_unnest_1"."VALUE"
-FROM LATERAL FLATTEN(INPUT => ARRAY_CONSTRUCT(1, 2, 3)) AS "_unnest_1";
-
--- --------------------------------------------------------------------------
--- Test: snowflake_flatten_implicit_from
--- DataFusion input: SELECT UNNEST([1,2,3])
--- Unparser output: SELECT "_unnest_1"."VALUE" FROM LATERAL FLATTEN(INPUT => [1, 2, 3]) AS "_unnest_1"
--- --------------------------------------------------------------------------
-SELECT "_unnest_1"."VALUE"
-FROM LATERAL FLATTEN(INPUT => ARRAY_CONSTRUCT(1, 2, 3)) AS "_unnest_1";
-
--- --------------------------------------------------------------------------
--- Test: snowflake_flatten_string_array
--- DataFusion input: SELECT * FROM UNNEST(['a','b','c'])
--- Unparser output: SELECT "_unnest_1"."VALUE" FROM LATERAL FLATTEN(INPUT => ['a', 'b', 'c']) AS "_unnest_1"
--- --------------------------------------------------------------------------
-SELECT "_unnest_1"."VALUE"
-FROM LATERAL FLATTEN(INPUT => ARRAY_CONSTRUCT('a', 'b', 'c')) AS "_unnest_1";
-
--- --------------------------------------------------------------------------
--- Test: snowflake_flatten_select_unnest_with_alias
--- DataFusion input: SELECT UNNEST([1,2,3]) as c1
--- Unparser output: SELECT "_unnest_1"."VALUE" AS "c1" FROM LATERAL FLATTEN(INPUT => [1, 2, 3]) AS "_unnest_1"
--- --------------------------------------------------------------------------
-SELECT "_unnest_1"."VALUE" AS "c1"
-FROM LATERAL FLATTEN(INPUT => ARRAY_CONSTRUCT(1, 2, 3)) AS "_unnest_1";
-
--- --------------------------------------------------------------------------
--- Test: snowflake_flatten_from_unnest_with_table_alias
--- DataFusion input: SELECT * FROM UNNEST([1,2,3]) AS t1 (c1)
--- Unparser output: SELECT "t1"."VALUE" FROM LATERAL FLATTEN(INPUT => [1, 2, 3]) AS "t1"
--- --------------------------------------------------------------------------
-SELECT "t1"."VALUE"
-FROM LATERAL FLATTEN(INPUT => ARRAY_CONSTRUCT(1, 2, 3)) AS "t1";
-
--- ============================================================================
--- Plan-built tests: LogicalPlan → Snowflake SQL
--- These use a table called "source" with an ARRAY column "items".
--- ============================================================================
-
--- --------------------------------------------------------------------------
--- Test: snowflake_flatten_limit_between_projection_and_unnest
--- Plan: Projection → Limit → Unnest → Projection → TableScan
--- Unparser output: SELECT "_unnest_1"."VALUE" AS "item" FROM "source" CROSS JOIN LATERAL FLATTEN(INPUT => "source"."items", OUTER => true) AS "_unnest_1" LIMIT 5
--- --------------------------------------------------------------------------
-SELECT "_unnest_1"."VALUE" AS "item"
-FROM "source"
-CROSS JOIN LATERAL FLATTEN(INPUT => "source"."items", OUTER => true) AS "_unnest_1"
-LIMIT 5;
-
--- --------------------------------------------------------------------------
--- Test: snowflake_flatten_sort_between_projection_and_unnest
--- Plan: Projection → Sort → Unnest → Projection → TableScan
--- Unparser output: SELECT "_unnest_1"."VALUE" AS "item" FROM "source" CROSS JOIN LATERAL FLATTEN(INPUT => "source"."items", OUTER => true) AS "_unnest_1" ORDER BY "_unnest_1"."VALUE" ASC NULLS FIRST
--- --------------------------------------------------------------------------
-SELECT "_unnest_1"."VALUE" AS "item"
-FROM "source"
-CROSS JOIN LATERAL FLATTEN(INPUT => "source"."items", OUTER => true) AS "_unnest_1"
-ORDER BY "_unnest_1"."VALUE" ASC NULLS FIRST;
-
--- --------------------------------------------------------------------------
--- Test: snowflake_flatten_limit_between_projection_and_unnest_with_subquery_alias
--- Plan: Projection → Limit → Unnest → SubqueryAlias → Projection → TableScan
--- Unparser output: SELECT "_unnest_1"."VALUE" AS "item" FROM "source" CROSS JOIN LATERAL FLATTEN(INPUT => "source"."items", OUTER => true) AS "_unnest_1" LIMIT 10
--- --------------------------------------------------------------------------
-SELECT "_unnest_1"."VALUE" AS "item"
-FROM "source"
-CROSS JOIN LATERAL FLATTEN(INPUT => "source"."items", OUTER => true) AS "_unnest_1"
-LIMIT 10;
-
--- --------------------------------------------------------------------------
--- Test: snowflake_flatten_composed_expression_wrapping_unnest
--- Plan: Projection(CAST(placeholder AS Int64)) → Unnest → Projection → TableScan
--- Unparser output: SELECT CAST("_unnest_1"."VALUE" AS BIGINT) AS "item_id" FROM "source" CROSS JOIN LATERAL FLATTEN(INPUT => "source"."items", OUTER => true) AS "_unnest_1"
--- --------------------------------------------------------------------------
-SELECT CAST("_unnest_1"."VALUE" AS BIGINT) AS "item_id"
-FROM "source"
-CROSS JOIN LATERAL FLATTEN(INPUT => "source"."items", OUTER => true) AS "_unnest_1";
-
--- --------------------------------------------------------------------------
--- Test: snowflake_flatten_composed_expression_with_limit
--- Plan: Projection(CAST) → Limit → Unnest → Projection → TableScan
--- Unparser output: SELECT CAST("_unnest_1"."VALUE" AS BIGINT) AS "item_id" FROM "source" CROSS JOIN LATERAL FLATTEN(INPUT => "source"."items", OUTER => true) AS "_unnest_1" LIMIT 5
--- --------------------------------------------------------------------------
-SELECT CAST("_unnest_1"."VALUE" AS BIGINT) AS "item_id"
-FROM "source"
-CROSS JOIN LATERAL FLATTEN(INPUT => "source"."items", OUTER => true) AS "_unnest_1"
-LIMIT 5;
-
--- --------------------------------------------------------------------------
--- Test: snowflake_flatten_multi_expression_projection
--- Plan: Projection([CAST AS Int64, CAST AS Utf8]) → Unnest → Projection → TableScan
--- Unparser output: SELECT CAST("_unnest_1"."VALUE" AS BIGINT) AS "a", CAST("_unnest_1"."VALUE" AS VARCHAR) AS "b" FROM "source" CROSS JOIN LATERAL FLATTEN(INPUT => "source"."items", OUTER => true) AS "_unnest_1"
--- --------------------------------------------------------------------------
-SELECT CAST("_unnest_1"."VALUE" AS BIGINT) AS "a",
- CAST("_unnest_1"."VALUE" AS VARCHAR) AS "b"
-FROM "source"
-CROSS JOIN LATERAL FLATTEN(INPUT => "source"."items", OUTER => true) AS "_unnest_1";
-
--- --------------------------------------------------------------------------
--- Test: snowflake_flatten_multi_expression_with_limit
--- Plan: Projection([CAST, CAST]) → Limit → Unnest → Projection → TableScan
--- Unparser output: SELECT CAST("_unnest_1"."VALUE" AS BIGINT) AS "a", CAST("_unnest_1"."VALUE" AS VARCHAR) AS "b" FROM "source" CROSS JOIN LATERAL FLATTEN(INPUT => "source"."items", OUTER => true) AS "_unnest_1" LIMIT 10
--- --------------------------------------------------------------------------
-SELECT CAST("_unnest_1"."VALUE" AS BIGINT) AS "a",
- CAST("_unnest_1"."VALUE" AS VARCHAR) AS "b"
-FROM "source"
-CROSS JOIN LATERAL FLATTEN(INPUT => "source"."items", OUTER => true) AS "_unnest_1"
-LIMIT 10;
-
--- --------------------------------------------------------------------------
--- Test: snowflake_unnest_through_subquery_alias
--- Plan: Projection → Unnest → SubqueryAlias → Projection → TableScan
--- Unparser output: SELECT "_unnest_1"."VALUE" AS "item" FROM "source" CROSS JOIN LATERAL FLATTEN(INPUT => "source"."items", OUTER => true) AS "_unnest_1"
--- --------------------------------------------------------------------------
-SELECT "_unnest_1"."VALUE" AS "item"
-FROM "source"
-CROSS JOIN LATERAL FLATTEN(INPUT => "source"."items", OUTER => true) AS "_unnest_1";
-
--- ============================================================================
--- Roundtrip tests with table columns
--- ============================================================================
-
--- --------------------------------------------------------------------------
--- Test: snowflake_flatten_unnest_from_subselect
--- DataFusion input: SELECT UNNEST(array_col) FROM (SELECT array_col FROM unnest_table WHERE array_col IS NOT NULL LIMIT 3)
--- Unparser output: SELECT "_unnest_1"."VALUE" FROM (SELECT "unnest_table"."array_col" FROM "unnest_table" WHERE "unnest_table"."array_col" IS NOT NULL LIMIT 3) CROSS JOIN LATERAL FLATTEN(INPUT => "unnest_table"."array_col") AS "_unnest_1"
--- --------------------------------------------------------------------------
-SELECT "_unnest_1"."VALUE"
-FROM (
- SELECT "unnest_table"."array_col"
- FROM "unnest_table"
- WHERE "unnest_table"."array_col" IS NOT NULL
- LIMIT 3
-) CROSS JOIN LATERAL FLATTEN(INPUT => "unnest_table"."array_col") AS "_unnest_1";
-
--- --------------------------------------------------------------------------
--- Test: snowflake_flatten_cross_join_unnest_table_column
--- DataFusion input: SELECT * FROM multi_array_table CROSS JOIN UNNEST(column_a) AS a (a)
--- Unparser output: SELECT "multi_array_table"."column_a", "multi_array_table"."column_b", "a"."VALUE" FROM "multi_array_table" CROSS JOIN LATERAL FLATTEN(INPUT => "multi_array_table"."column_a") AS "a"
--- --------------------------------------------------------------------------
-SELECT "multi_array_table"."column_a",
- "multi_array_table"."column_b",
- "a"."VALUE"
-FROM "multi_array_table"
-CROSS JOIN LATERAL FLATTEN(INPUT => "multi_array_table"."column_a") AS "a";
-
--- --------------------------------------------------------------------------
--- Test: snowflake_flatten_multiple_unnest_cross_join
--- DataFusion input: SELECT a.a, b.b FROM multi_array_table
--- CROSS JOIN UNNEST(column_a) AS a (a)
--- CROSS JOIN UNNEST(column_b) AS b (b)
--- Unparser output: SELECT "a"."VALUE", "b"."VALUE" FROM "multi_array_table" CROSS JOIN LATERAL FLATTEN(INPUT => "multi_array_table"."column_a") AS "a" CROSS JOIN LATERAL FLATTEN(INPUT => "multi_array_table"."column_b") AS "b"
--- --------------------------------------------------------------------------
-SELECT "a"."VALUE",
- "b"."VALUE"
-FROM "multi_array_table"
-CROSS JOIN LATERAL FLATTEN(INPUT => "multi_array_table"."column_a") AS "a"
-CROSS JOIN LATERAL FLATTEN(INPUT => "multi_array_table"."column_b") AS "b";
-
--- ============================================================================
--- Cleanup
--- ============================================================================
--- DROP TABLE IF EXISTS source;
--- DROP TABLE IF EXISTS unnest_table;
--- DROP TABLE IF EXISTS multi_array_table;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org