Skip to content

Commit e27b6c6

Browse files
authored
feat: Analyze VALUES for nullability (#22089)
## Which issue does this PR close? - Closes #22088. ## Rationale for this change `LogicalPlanBuilder::infer_data` (the inference path for `VALUES` clauses without a target schema) hard-coded every inferred column's nullability to `true`, regardless of whether any row actually contained a NULL. This is inconsistent with how nullability is computed for other, similar situations (e.g., `SELECT 1`). In addition to improving internal consistency (and theoretically allowing better query optimization), this also makes it easier to write tests for nullability-related behavior without using a scratch table. ## What changes are included in this PR? * `LogicalPlanBuilder::infer_data` now tracks per-column nullability while iterating values, marking the column nullable iff any row's value expression returns `nullable() == true`. Note that this only changes behavior for `VALUES` without a schema; `INSERT INTO VALUES`, for example, already computed nullability. * Add SLT tests for this behavior * Update expected SLT tests where this change results in updating a schema. Note that some Parquet files are slightly smaller now, which caused byte-count metrics in a few places to change. ## Are these changes tested? Yes, with new tests added. ## Are there any user-facing changes? The inferred schema for `CREATE TABLE AS VALUES (...)` will now change, although if we also fix #22087 then the original behavior will be preserved.
1 parent b382ecd commit e27b6c6

12 files changed

Lines changed: 110 additions & 64 deletions

‎datafusion/expr/src/logical_plan/builder.rs‎

Lines changed: 13 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -302,6 +302,7 @@ impl LogicalPlanBuilder {
302302
for j in 0..n_cols {
303303
let mut common_type: Option<DataType> = None;
304304
let mut common_metadata: Option<FieldMetadata> = None;
305+
let mut nullable = false;
305306
for (i, row) in values.iter().enumerate() {
306307
let value = &row[j];
307308
let metadata = value.metadata(&schema)?;
@@ -316,13 +317,17 @@ impl LogicalPlanBuilder {
316317
} else {
317318
common_metadata = Some(metadata.clone());
318319
}
320+
if !nullable && value.nullable(&schema)? {
321+
nullable = true;
322+
}
319323
let data_type = value.get_type(&schema)?;
320324
if data_type == DataType::Null {
321325
continue;
322326
}
323327

324328
if let Some(prev_type) = common_type {
325-
// get common type of each column values.
329+
// Widen the running type so that it can hold both the
330+
// previously seen rows and this row's value.
326331
let data_types = vec![prev_type.clone(), data_type.clone()];
327332
let Some(new_type) = type_union_resolution(&data_types) else {
328333
return plan_err!(
@@ -334,13 +339,13 @@ impl LogicalPlanBuilder {
334339
common_type = Some(data_type);
335340
}
336341
}
337-
// assuming common_type was not set, and no error, therefore the type should be NULL
338-
// since the code loop skips NULL
339-
fields.push_with_metadata(
340-
common_type.unwrap_or(DataType::Null),
341-
true,
342-
common_metadata,
343-
);
342+
// If common_type is not set, every value in this column had type
343+
// NULL. A DataType::Null field is always nullable.
344+
let (data_type, nullable) = match common_type {
345+
Some(t) => (t, nullable),
346+
None => (DataType::Null, true),
347+
};
348+
fields.push_with_metadata(data_type, nullable, common_metadata);
344349
}
345350

346351
Self::infer_inner(values, fields, &schema)

‎datafusion/sqllogictest/test_files/ddl.slt‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -654,7 +654,7 @@ LOCATION 'test_files/scratch/ddl/test_table';
654654
query TTT
655655
DESCRIBE aggregate_table;
656656
----
657-
id Int64 YES
657+
id Int64 NO
658658

659659
# Should insert into an empty table
660660
statement ok

‎datafusion/sqllogictest/test_files/describe.slt‎

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -142,3 +142,44 @@ name_count Int64 NO
142142
# Describing a statement that's not a query is not supported
143143
statement error Describing statements other than SELECT not supported
144144
DESCRIBE CREATE TABLE test_desc_table (id INT, name VARCHAR);
145+
146+
##########
147+
# VALUES nullability inference
148+
#
149+
# Inferred VALUES schemas should be non-nullable when no row contributes a
150+
# NULL in that column position, and nullable when at least one does.
151+
##########
152+
153+
# All-non-null VALUES: every column is non-nullable.
154+
query TTT rowsort
155+
DESCRIBE SELECT * FROM (VALUES (1, 'a'), (2, 'b')) AS t(x, y);
156+
----
157+
x Int64 NO
158+
y Utf8 NO
159+
160+
# Untyped NULL in one row makes that column nullable; sibling all-non-null
161+
# columns remain non-nullable.
162+
query TTT rowsort
163+
DESCRIBE SELECT * FROM (VALUES (1, 'a'), (NULL, 'b')) AS t(x, y);
164+
----
165+
x Int64 YES
166+
y Utf8 NO
167+
168+
# Typed NULL has the same effect as untyped NULL on column nullability.
169+
query TTT
170+
DESCRIBE SELECT * FROM (VALUES (1), (CAST(NULL AS BIGINT))) AS t(x);
171+
----
172+
x Int64 YES
173+
174+
# All-NULL column is nullable; the inferred type is Null.
175+
query TTT
176+
DESCRIBE SELECT * FROM (VALUES (NULL), (NULL)) AS t(x);
177+
----
178+
x Null YES
179+
180+
# A Null-typed value sourced from a non-nullable expression must still
181+
# produce a nullable column: a DataType::Null field is always nullable.
182+
query TTT
183+
DESCRIBE SELECT * FROM (VALUES (arrow_cast(1, 'Null'))) AS t(x);
184+
----
185+
x Null YES

‎datafusion/sqllogictest/test_files/dictionary.slt‎

Lines changed: 14 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -80,12 +80,12 @@ SELECT * FROM m1;
8080
query TTT
8181
DESCRIBE m1;
8282
----
83-
tag_id Dictionary(Int32, Utf8) YES
84-
f1 Float64 YES
85-
f2 Utf8 YES
86-
f3 Utf8 YES
87-
f4 Float64 YES
88-
time Timestamp(ns) YES
83+
tag_id Dictionary(Int32, Utf8) NO
84+
f1 Float64 NO
85+
f2 Utf8 NO
86+
f3 Utf8 NO
87+
f4 Float64 NO
88+
time Timestamp(ns) NO
8989

9090
# in list with dictionary input
9191
query BBB
@@ -154,10 +154,10 @@ passive 1000 1000 2023-12-04T01:30:00
154154
query TTT
155155
DESCRIBE m2;
156156
----
157-
type Dictionary(Int32, Utf8) YES
158-
tag_id Dictionary(Int32, Utf8) YES
159-
f5 Float64 YES
160-
time Timestamp(ns) YES
157+
type Dictionary(Int32, Utf8) NO
158+
tag_id Dictionary(Int32, Utf8) NO
159+
f5 Float64 NO
160+
time Timestamp(ns) NO
161161

162162
query I
163163
select count(*) from m1 where tag_id = '1000' and time < '2024-01-03T14:46:35+01:00';
@@ -492,10 +492,10 @@ LOCATION 'test_files/scratch/dictionary/dict_hash_10.parquet';
492492
query TTT
493493
DESCRIBE dict_hash_10;
494494
----
495-
id Int64 YES
496-
payload_hash Dictionary(Int32, Utf8) YES
497-
metric Float64 YES
498-
ts Timestamp(ns) YES
495+
id Int64 NO
496+
payload_hash Dictionary(Int32, Utf8) NO
497+
metric Float64 NO
498+
ts Timestamp(ns) NO
499499

500500
query II
501501
SELECT COUNT(*), COUNT(DISTINCT payload_hash)

‎datafusion/sqllogictest/test_files/explain.slt‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -682,7 +682,7 @@ logical_plan
682682
11)--subgraph cluster_3
683683
12)--{
684684
13)----graph[label="Detailed LogicalPlan"]
685-
14)----4[shape=box label="Values: (Int64(1))\nSchema: [column1:Int64;N]"]
685+
14)----4[shape=box label="Values: (Int64(1))\nSchema: [column1:Int64]"]
686686
15)--}
687687
16)}
688688
17)// End DataFusion GraphViz Plan

‎datafusion/sqllogictest/test_files/explain_analyze.slt‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -247,7 +247,7 @@ explain analyze select * from cat_tracking where species > 'M' AND s >= 50 order
247247
----
248248
Plan with Metrics
249249
01)SortExec: TopK(fetch=3), expr=[species@0 ASC NULLS LAST], preserve_partitioning=[false], filter=[species@0 < Nlpine Sheep], metrics=[output_rows=3]
250-
02)--DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/explain_analyze/data.parquet]]}, projection=[species, s], file_type=parquet, predicate=species@0 > M AND s@1 >= 50 AND DynamicFilter [ species@0 < Nlpine Sheep ], sort_order_for_reorder=[species@0 ASC NULLS LAST], pruning_predicate=species_null_count@1 != row_count@2 AND species_max@0 > M AND s_null_count@4 != row_count@2 AND s_max@3 >= 50 AND species_null_count@1 != row_count@2 AND species_min@5 < Nlpine Sheep, required_guarantees=[], metrics=[output_rows=3, files_ranges_pruned_statistics=1 total → 1 matched, row_groups_pruned_statistics=4 total → 3 matched -> 1 fully matched, row_groups_pruned_bloom_filter=3 total → 3 matched, page_index_pages_pruned=2 total → 2 matched, page_index_pages_skipped_by_fully_matched=1, limit_pruned_row_groups=0 total → 0 matched, scan_efficiency_ratio=22.13% (521/2.35 K)]
250+
02)--DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/explain_analyze/data.parquet]]}, projection=[species, s], file_type=parquet, predicate=species@0 > M AND s@1 >= 50 AND DynamicFilter [ species@0 < Nlpine Sheep ], sort_order_for_reorder=[species@0 ASC NULLS LAST], pruning_predicate=species_null_count@1 != row_count@2 AND species_max@0 > M AND s_null_count@4 != row_count@2 AND s_max@3 >= 50 AND species_null_count@1 != row_count@2 AND species_min@5 < Nlpine Sheep, required_guarantees=[], metrics=[output_rows=3, files_ranges_pruned_statistics=1 total → 1 matched, row_groups_pruned_statistics=4 total → 3 matched -> 1 fully matched, row_groups_pruned_bloom_filter=3 total → 3 matched, page_index_pages_pruned=2 total → 2 matched, page_index_pages_skipped_by_fully_matched=1, limit_pruned_row_groups=0 total → 0 matched, scan_efficiency_ratio=21.75% (485/2.23 K)]
251251

252252
statement ok
253253
reset datafusion.explain.analyze_categories;

‎datafusion/sqllogictest/test_files/information_schema.slt‎

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -561,8 +561,8 @@ CREATE OR REPLACE TABLE some_table AS VALUES (1,2),(3,4);
561561
query TTT rowsort
562562
DESCRIBE some_table
563563
----
564-
column1 Int64 YES
565-
column2 Int64 YES
564+
column1 Int64 NO
565+
column2 Int64 NO
566566

567567
statement ok
568568
DROP TABLE public.some_table;
@@ -575,8 +575,8 @@ CREATE OR REPLACE TABLE public.some_table AS VALUES (1,2),(3,4);
575575
query TTT rowsort
576576
DESCRIBE public.some_table
577577
----
578-
column1 Int64 YES
579-
column2 Int64 YES
578+
column1 Int64 NO
579+
column2 Int64 NO
580580

581581
statement ok
582582
DROP TABLE public.some_table;
@@ -589,8 +589,8 @@ CREATE OR REPLACE TABLE datafusion.public.some_table AS VALUES (1,2),(3,4);
589589
query TTT rowsort
590590
DESCRIBE datafusion.public.some_table
591591
----
592-
column1 Int64 YES
593-
column2 Int64 YES
592+
column1 Int64 NO
593+
column2 Int64 NO
594594

595595
statement ok
596596
DROP TABLE datafusion.public.some_table;

‎datafusion/sqllogictest/test_files/insert_to_external.slt‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -48,8 +48,8 @@ create table dictionary_encoded_values as values
4848
query TTT
4949
describe dictionary_encoded_values;
5050
----
51-
column1 Utf8 YES
52-
column2 Dictionary(Int32, Utf8) YES
51+
column1 Utf8 NO
52+
column2 Dictionary(Int32, Utf8) NO
5353

5454
statement ok
5555
CREATE EXTERNAL TABLE dictionary_encoded_parquet_partitioned(

‎datafusion/sqllogictest/test_files/limit_pruning.slt‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -63,7 +63,7 @@ set datafusion.explain.analyze_level = summary;
6363
query TT
6464
explain analyze select * from tracking_data where species > 'M' AND s >= 50 limit 3;
6565
----
66-
Plan with Metrics DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/limit_pruning/data.parquet]]}, projection=[species, s], limit=3, file_type=parquet, predicate=species@0 > M AND s@1 >= 50, pruning_predicate=species_null_count@1 != row_count@2 AND species_max@0 > M AND s_null_count@4 != row_count@2 AND s_max@3 >= 50, required_guarantees=[], metrics=[output_rows=3, elapsed_compute=<slt:ignore>, output_bytes=<slt:ignore>, files_ranges_pruned_statistics=1 total → 1 matched, row_groups_pruned_statistics=4 total → 3 matched -> 1 fully matched, row_groups_pruned_bloom_filter=3 total → 3 matched, page_index_pages_pruned=0 total → 0 matched, page_index_pages_skipped_by_fully_matched=1, limit_pruned_row_groups=2 total → 0 matched, bytes_scanned=<slt:ignore>, metadata_load_time=<slt:ignore>, scan_efficiency_ratio=<slt:ignore> (171/2.35 K)]
66+
Plan with Metrics DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/limit_pruning/data.parquet]]}, projection=[species, s], limit=3, file_type=parquet, predicate=species@0 > M AND s@1 >= 50, pruning_predicate=species_null_count@1 != row_count@2 AND species_max@0 > M AND s_null_count@4 != row_count@2 AND s_max@3 >= 50, required_guarantees=[], metrics=[output_rows=3, elapsed_compute=<slt:ignore>, output_bytes=<slt:ignore>, files_ranges_pruned_statistics=1 total → 1 matched, row_groups_pruned_statistics=4 total → 3 matched -> 1 fully matched, row_groups_pruned_bloom_filter=3 total → 3 matched, page_index_pages_pruned=0 total → 0 matched, page_index_pages_skipped_by_fully_matched=1, limit_pruned_row_groups=2 total → 0 matched, bytes_scanned=<slt:ignore>, metadata_load_time=<slt:ignore>, scan_efficiency_ratio=<slt:ignore> (159/2.23 K)]
6767

6868
statement ok
6969
CREATE TABLE fully_matched_limit_source AS VALUES
@@ -120,7 +120,7 @@ explain analyze select * from tracking_data where species > 'M' AND s >= 50 orde
120120
----
121121
Plan with Metrics
122122
01)SortExec: TopK(fetch=3), expr=[species@0 ASC NULLS LAST], preserve_partitioning=[false], filter=[species@0 < Nlpine Sheep], metrics=[output_rows=3, elapsed_compute=<slt:ignore>, output_bytes=<slt:ignore>]
123-
02)--DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/limit_pruning/data.parquet]]}, projection=[species, s], file_type=parquet, predicate=species@0 > M AND s@1 >= 50 AND DynamicFilter [ species@0 < Nlpine Sheep ], sort_order_for_reorder=[species@0 ASC NULLS LAST], pruning_predicate=species_null_count@1 != row_count@2 AND species_max@0 > M AND s_null_count@4 != row_count@2 AND s_max@3 >= 50 AND species_null_count@1 != row_count@2 AND species_min@5 < Nlpine Sheep, required_guarantees=[], metrics=[output_rows=3, elapsed_compute=<slt:ignore>, output_bytes=<slt:ignore>, files_ranges_pruned_statistics=1 total → 1 matched, row_groups_pruned_statistics=4 total → 3 matched -> 1 fully matched, row_groups_pruned_bloom_filter=3 total → 3 matched, page_index_pages_pruned=2 total → 2 matched, page_index_pages_skipped_by_fully_matched=1, limit_pruned_row_groups=0 total → 0 matched, bytes_scanned=<slt:ignore>, metadata_load_time=<slt:ignore>, scan_efficiency_ratio=<slt:ignore> (521/2.35 K)]
123+
02)--DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/limit_pruning/data.parquet]]}, projection=[species, s], file_type=parquet, predicate=species@0 > M AND s@1 >= 50 AND DynamicFilter [ species@0 < Nlpine Sheep ], sort_order_for_reorder=[species@0 ASC NULLS LAST], pruning_predicate=species_null_count@1 != row_count@2 AND species_max@0 > M AND s_null_count@4 != row_count@2 AND s_max@3 >= 50 AND species_null_count@1 != row_count@2 AND species_min@5 < Nlpine Sheep, required_guarantees=[], metrics=[output_rows=3, elapsed_compute=<slt:ignore>, output_bytes=<slt:ignore>, files_ranges_pruned_statistics=1 total → 1 matched, row_groups_pruned_statistics=4 total → 3 matched -> 1 fully matched, row_groups_pruned_bloom_filter=3 total → 3 matched, page_index_pages_pruned=2 total → 2 matched, page_index_pages_skipped_by_fully_matched=1, limit_pruned_row_groups=0 total → 0 matched, bytes_scanned=<slt:ignore>, metadata_load_time=<slt:ignore>, scan_efficiency_ratio=<slt:ignore> (485/2.23 K)]
124124

125125
statement ok
126126
drop table tracking_data;

‎datafusion/sqllogictest/test_files/listing_table_statistics.slt‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -35,7 +35,7 @@ query TT
3535
explain format indent select * from t;
3636
----
3737
logical_plan TableScan: t projection=[int_col, str_col]
38-
physical_plan DataSourceExec: file_groups={2 groups: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/listing_table_statistics/1.parquet], [WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/listing_table_statistics/2.parquet]]}, projection=[int_col, str_col], file_type=parquet, statistics=[Rows=Exact(4), Bytes=Absent, [(Col[0]: Min=Exact(Int64(-1)) Max=Exact(Int64(3)) Null=Exact(0) ScanBytes=Exact(32)),(Col[1]: Min=Exact(Utf8View("a")) Max=Exact(Utf8View("d")) Null=Exact(0) ScanBytes=Inexact(100))]]
38+
physical_plan DataSourceExec: file_groups={2 groups: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/listing_table_statistics/1.parquet], [WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/listing_table_statistics/2.parquet]]}, projection=[int_col, str_col], file_type=parquet, statistics=[Rows=Exact(4), Bytes=Absent, [(Col[0]: Min=Exact(Int64(-1)) Max=Exact(Int64(3)) Null=Exact(0) ScanBytes=Exact(32)),(Col[1]: Min=Exact(Utf8View("a")) Max=Exact(Utf8View("d")) Null=Exact(0) ScanBytes=Inexact(88))]]
3939

4040
statement ok
4141
drop table t;

0 commit comments

Comments
 (0)