comphead commented on code in PR #22100: URL: https://github.com/apache/datafusion/pull/22100#discussion_r3314427822
########## datafusion/sqllogictest/test_files/spark/generator/explode_outer.slt: ########## @@ -0,0 +1,205 @@ +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at + +# http://www.apache.org/licenses/LICENSE-2.0 + +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. + +####################################################################### +# Spark `explode_outer` / `explode` Tests +# +# `explode_outer(col)` matches Spark semantics: +# - rows whose input list is NULL or empty produce a single output row +# containing NULL, +# - rows with values are exploded element-by-element. +# `explode(col)` is a Spark alias for plain SQL `unnest(col)` and drops +# both NULL and empty input lists. +####################################################################### + +############################## +# 1. Int list: explode vs explode_outer +############################## + +statement ok +CREATE TABLE int_lists (id INT, xs INT[]) AS VALUES + (1, [10, 20, 30]), + (2, []), + (3, NULL), + (4, [40]), + (5, [NULL, 50]); + +# Plain explode: drops both NULL and empty rows. Inner NULL elements survive. +query II +SELECT id, explode(xs) AS x FROM int_lists ORDER BY id, x; +---- +1 10 +1 20 +1 30 +4 40 +5 50 +5 NULL + +# explode_outer: NULL and empty input lists each produce one NULL row. +query II +SELECT id, explode_outer(xs) AS x FROM int_lists ORDER BY id, x; +---- +1 10 +1 20 +1 30 +2 NULL +3 NULL +4 40 +5 50 +5 NULL + +############################## +# 2. String list with inner NULLs +############################## + +statement ok +CREATE TABLE str_lists (id TEXT, tags TEXT[]) AS VALUES + ('A', ['x', 'y']), + ('B', ['p', NULL, 'q']), + ('C', []), + ('D', NULL); + +# Inner NULL elements must survive (they are not the same as "empty"), +# while NULL and empty input lists become a single NULL output row. +query TT +SELECT id, explode_outer(tags) AS tag FROM str_lists ORDER BY id, tag; +---- +A x +A y +B p +B q +B NULL +C NULL +D NULL + +############################## +# 3. Mixed list lengths — verify row-wise expansion +############################## + +statement ok +CREATE TABLE varied_lists (id INT, xs INT[]) AS VALUES + (1, [1, 2, 3, 4]), + (2, [5]), + (3, []), + (4, NULL); + +query II +SELECT id, explode_outer(xs) AS x FROM varied_lists ORDER BY id, x; +---- +1 1 +1 2 +1 3 +1 4 +2 5 +3 NULL +4 NULL + +############################## +# 4. Aliased output column +############################## + +query II +SELECT id, explode_outer(xs) AS unwrapped FROM int_lists WHERE id = 2; +---- +2 NULL + +############################## +# 5. Mixing `unnest` / `explode` and `explode_outer` in one SELECT is an error +# UnnestOptions is per-UnnestExec, so we refuse to silently pick one mode. +############################## + +statement error DataFusion error: Error during planning: Cannot mix `unnest\(\.\.\.\)` \(or `explode\(\.\.\.\)`\) with `explode_outer\(\.\.\.\)` in the same SELECT +SELECT unnest(xs), explode_outer(xs) FROM int_lists; Review Comment: Spark actually doesnt support more than 1 generator per select ``` scala> spark.sql("select explode(array(1, 2, 3, null)), explode(array(1, 2, 3))").show(false) org.apache.spark.sql.AnalysisException: [UNSUPPORTED_GENERATOR.MULTI_GENERATOR] The generator is not supported: only one generator allowed per SELECT clause but found 2: "explode(array(1, 2, 3, NULL))", "explode(array(1, 2, 3))". ``` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
