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/10/22 19:34:13 UTC
(datafusion) 01/01: IN LIST: retain short integer, floating-point, and fixed-size binary lists (#25187)
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-25187-1240ad72934c9acf51ac328edbdda14599f18bf0
in repository https://gitbox.apache.org/repos/asf/datafusion.git
commit 99fc2e82fd6be6d392894dc334b70906defc895c
Author: Geoffrey Claude <ge...@datadoghq.com>
AuthorDate: Thu Oct 22 19:33:27 2026 +0000
IN LIST: retain short integer, floating-point, and fixed-size binary lists (#25187)
## Which issue does this PR close?
- Part of #19241.
## Rationale for this change
DataFusion currently rewrites short `IN` lists like `x IN (1, 2)` as `x
= 1 OR x = 2`. Evaluating `IN` directly can be faster.
This PR keeps the `IN` form for the types below, so short lists can use
DataFusion's existing optimized `IN` filters.
## What changes are included in this PR?
- Keep two- and three-value `IN` and `NOT IN` lists for `Int32`,
`Int64`, `UInt64`, `Float32`, `Float64`, and `FixedSizeBinary` columns
with widths of 1, 2, 4, 8, or 16 bytes. The list must contain constant
values of the column's type.
- Add benchmarks that use normal optimization to compare performance
before and after this change.
- Add optimizer and SQL logic tests covering list sizes, types, NULLs,
and non-constant list values.
## What is the testing strategy for this PR?
All 935 optimizer unit tests and all 533 SQL logic files passed,
including TPC-H. Formatting, Clippy, and lint checks passed. All 336
benchmark cases passed on both builds.
The benchmark bot's ARM
[two-value](https://github.com/apache/datafusion/pull/25187#issuecomment-6085097362)
and
[three-value](https://github.com/apache/datafusion/pull/25187#issuecomment-6085097845)
results show:
- Integer lists take about 32–55% less time when half the rows match or
none do. In the extreme case where every row matches the first value,
they can take up to about twice as long.
- Floating-point and fixed-size binary lists are faster in every tested
case, including NULLs and small batches.
<details>
<summary>Benchmark setup</summary>
Both builds use the same 336 cases. The
[baseline](https://github.com/apache/datafusion/commit/31a333da6952047803dcd93b7a80e59ef056ce8e)
contains `main` at `06aa131cda0` plus the same benchmark code as the
[PR](https://github.com/apache/datafusion/commit/8aea24b85b1e16e0c0e13773935b130e67160853).
Queries use normal optimization. Timings cover running the chosen
expressions; query planning and table setup happen before timing.
</details>
## Are there any user-facing changes?
`EXPLAIN` shows `IN` or `NOT IN` for these short lists in place of the
expanded `OR` or `AND` comparisons.
This performance PR aligns constant two- and three-value
`Float32`/`Float64` lists with existing behavior for 4+ values,
including the signed-zero bug (`+0.0` versus `-0.0`). That bug [predates
this optimization
series](https://github.com/apache/datafusion/pull/7378) and already
affects longer lists with constant or column values; fixing it is left
to #25186.
---------
Co-authored-by: Andrew Lamb <an...@nerdnetworks.org>
---
datafusion/core/Cargo.toml | 4 +
datafusion/core/benches/in_list_rewrite.rs | 417 +++++++++++++++++++++
.../src/simplify_expressions/expr_simplifier.rs | 87 ++++-
.../src/simplify_expressions/inlist_simplifier.rs | 42 ++-
.../test_files/eliminate_outer_join.slt | 30 +-
datafusion/sqllogictest/test_files/in_list.slt | 220 ++++++++++-
datafusion/sqllogictest/test_files/pwmj.slt | 4 +-
.../sqllogictest/test_files/range_partitioning.slt | 4 +-
.../test_files/string_numeric_coercion.slt | 4 +-
datafusion/sqllogictest/test_files/union.slt | 8 +-
datafusion/sqllogictest/test_files/window.slt | 4 +-
11 files changed, 784 insertions(+), 40 deletions(-)
diff --git a/datafusion/core/Cargo.toml b/datafusion/core/Cargo.toml
index 7275c4b674..89f4276153 100644
--- a/datafusion/core/Cargo.toml
+++ b/datafusion/core/Cargo.toml
@@ -245,6 +245,10 @@ name = "math_query_sql"
harness = false
name = "filter_query_sql"
+[[bench]]
+harness = false
+name = "in_list_rewrite"
+
[[bench]]
harness = false
name = "struct_query_sql"
diff --git a/datafusion/core/benches/in_list_rewrite.rs b/datafusion/core/benches/in_list_rewrite.rs
new file mode 100644
index 0000000000..078c8f5e7d
--- /dev/null
+++ b/datafusion/core/benches/in_list_rewrite.rs
@@ -0,0 +1,417 @@
+// 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.
+
+//! Benchmarks short SQL `IN` lists using the default optimizer. Comparing the
+//! same cases across commits measures changes to the chosen evaluation strategy.
+//! Criterion times expression evaluation, with planning and table setup outside
+//! the measured loop. Input types and expected Boolean results are checked first.
+//!
+//! All types cover misses, balanced matches, and uniform or skewed
+//! first-item hits. Floating-point and fixed-size-binary types also cover
+//! nulls and small batches; a misaligned binary batch exercises alignment copies.
+
+use std::hint::black_box;
+use std::sync::Arc;
+use std::time::Duration;
+
+use arrow::array::{Array, ArrayRef, AsArray, BooleanArray, FixedSizeBinaryArray};
+use arrow::buffer::{Buffer, MutableBuffer};
+use arrow::datatypes::{DataType, Field, Schema};
+use arrow::record_batch::RecordBatch;
+use criterion::{Criterion, Throughput, criterion_group, criterion_main};
+use datafusion::prelude::SessionContext;
+use datafusion_common::ScalarValue;
+use datafusion_expr::LogicalPlan;
+use datafusion_physical_expr::PhysicalExpr;
+use rand::prelude::*;
+use tokio::runtime::Runtime;
+
+const MISS_VALUE_BASE: usize = 10_000;
+
+#[derive(Debug, Clone, Copy, PartialEq, Eq)]
+enum ValueKind {
+ Int32,
+ Int64,
+ UInt64,
+ Float32,
+ Float64,
+ Utf8,
+ Utf8View,
+ FixedSizeBinary(i32),
+}
+
+impl ValueKind {
+ const ALL: [Self; 12] = [
+ Self::Int32,
+ Self::Int64,
+ Self::UInt64,
+ Self::Float32,
+ Self::Float64,
+ Self::Utf8,
+ Self::Utf8View,
+ Self::FixedSizeBinary(1),
+ Self::FixedSizeBinary(2),
+ Self::FixedSizeBinary(4),
+ Self::FixedSizeBinary(8),
+ Self::FixedSizeBinary(16),
+ ];
+
+ fn name(self) -> String {
+ match self {
+ Self::Int32 => "int32",
+ Self::Int64 => "int64",
+ Self::UInt64 => "uint64",
+ Self::Float32 => "float32",
+ Self::Float64 => "float64",
+ Self::Utf8 => "utf8",
+ Self::Utf8View => "utf8view_inline",
+ Self::FixedSizeBinary(width) => return format!("fixed_size_binary_{width}"),
+ }
+ .to_owned()
+ }
+
+ fn seed_tag(self) -> u64 {
+ match self {
+ Self::Int32 => 1,
+ Self::Float64 => 2,
+ Self::Utf8 => 3,
+ Self::Utf8View => 4,
+ Self::FixedSizeBinary(8) => 5,
+ Self::Int64 => 6,
+ Self::Float32 => 7,
+ Self::UInt64 => 8,
+ Self::FixedSizeBinary(width) => 20 + width as u64,
+ }
+ }
+
+ // The same typed values build batches and bind SQL parameters, avoiding
+ // implicit SQL literal widening for Float32 and UInt64.
+ fn scalar(self, value: Option<usize>) -> ScalarValue {
+ match self {
+ Self::Int32 => ScalarValue::Int32(value.map(|v| v as i32)),
+ Self::Int64 => ScalarValue::Int64(value.map(|v| v as i64)),
+ Self::UInt64 => ScalarValue::UInt64(value.map(|v| v as u64)),
+ Self::Float32 => ScalarValue::Float32(value.map(|v| v as f32)),
+ Self::Float64 => ScalarValue::Float64(value.map(|v| v as f64)),
+ Self::Utf8 => ScalarValue::Utf8(value.map(string_value)),
+ Self::Utf8View => ScalarValue::Utf8View(value.map(string_value)),
+ Self::FixedSizeBinary(width) => ScalarValue::FixedSizeBinary(
+ width,
+ value.map(|v| {
+ // Narrow misses stay disjoint from the hit values 0, 1, and 2.
+ let v = if width == 1 && v >= MISS_VALUE_BASE {
+ 4 + (v - MISS_VALUE_BASE) % 252
+ } else {
+ v
+ };
+ (v as u128).to_be_bytes()[16 - width as usize..].to_vec()
+ }),
+ ),
+ }
+ }
+
+ fn data_type(self) -> DataType {
+ self.scalar(Some(0)).data_type()
+ }
+
+ fn make_array(self, values: &[Option<usize>]) -> ArrayRef {
+ let array =
+ ScalarValue::iter_to_array(values.iter().map(|&v| self.scalar(v))).unwrap();
+ if let Self::FixedSizeBinary(width) = self {
+ let binary = array
+ .as_any()
+ .downcast_ref::<FixedSizeBinaryArray>()
+ .unwrap();
+ assert_eq!(
+ binary.values().as_ptr().align_offset(width as usize),
+ 0,
+ "builder-created binary buffer must be aligned"
+ );
+ }
+ array
+ }
+}
+
+// Eight-byte strings use Arrow's inline byte-view representation.
+fn string_value(value: usize) -> String {
+ format!("v{value:07}")
+}
+
+#[derive(Clone, Copy)]
+enum Profile {
+ Miss,
+ Balanced,
+ AllFirstHit,
+ SkewedHit,
+ Nullable,
+ SmallBatch,
+ SingleMiss,
+ SingleHit,
+ ListNull,
+ Unaligned,
+}
+
+impl Profile {
+ const ALL: [Self; 10] = [
+ Self::Miss,
+ Self::Balanced,
+ Self::AllFirstHit,
+ Self::SkewedHit,
+ Self::Nullable,
+ Self::SmallBatch,
+ Self::SingleMiss,
+ Self::SingleHit,
+ Self::ListNull,
+ Self::Unaligned,
+ ];
+
+ fn name(self) -> &'static str {
+ match self {
+ Self::Miss => "miss",
+ Self::Balanced => "balanced",
+ Self::AllFirstHit => "all_first_hit",
+ Self::SkewedHit => "skewed_hit",
+ Self::Nullable => "balanced_nullable",
+ Self::SmallBatch => "balanced_small_batch",
+ Self::SingleMiss => "single_row_miss",
+ Self::SingleHit => "single_row_hit",
+ Self::ListNull => "list_null_balanced",
+ Self::Unaligned => "balanced_unaligned",
+ }
+ }
+
+ fn batch_size(self) -> usize {
+ match self {
+ Self::SmallBatch => 64,
+ Self::SingleMiss | Self::SingleHit => 1,
+ _ => 8192,
+ }
+ }
+
+ fn null_percent(self) -> usize {
+ if matches!(self, Self::Nullable) {
+ 20
+ } else {
+ 0
+ }
+ }
+
+ fn match_percent(self) -> usize {
+ match self {
+ Self::Miss | Self::SingleMiss => 0,
+ Self::AllFirstHit | Self::SingleHit => 100,
+ Self::SkewedHit => 99,
+ _ => 50,
+ }
+ }
+
+ fn permits_kind(self, kind: ValueKind) -> bool {
+ match self {
+ Self::Unaligned => kind == ValueKind::FixedSizeBinary(16),
+ Self::Miss | Self::Balanced | Self::AllFirstHit | Self::SkewedHit => true,
+ _ => matches!(
+ kind,
+ ValueKind::Float32 | ValueKind::Float64 | ValueKind::FixedSizeBinary(_)
+ ),
+ }
+ }
+}
+
+// Project the predicate so optimization preserves its Boolean result, including
+// NULLs, rather than simplifying it based on WHERE semantics.
+fn plan_in_list(
+ ctx: &SessionContext,
+ runtime: &Runtime,
+ kind: ValueKind,
+ batch: &RecordBatch,
+ list_len: usize,
+ negated: bool,
+ list_has_null: bool,
+) -> Arc<dyn PhysicalExpr> {
+ ctx.deregister_table(kind.name()).unwrap();
+ ctx.register_batch(kind.name(), batch.clone()).unwrap();
+ let params = (0..list_len)
+ .map(|i| kind.scalar((!list_has_null || i + 1 < list_len).then_some(i)))
+ .collect::<Vec<_>>();
+ assert!(
+ params
+ .iter()
+ .all(|value| value.data_type() == kind.data_type())
+ );
+ let placeholders = (1..=list_len).map(|i| format!("${i}")).collect::<Vec<_>>();
+ let sql = format!(
+ "SELECT a {}IN ({}) AS matches FROM {}",
+ if negated { "NOT " } else { "" },
+ placeholders.join(", "),
+ kind.name()
+ );
+ let plan = runtime
+ .block_on(ctx.sql(&sql))
+ .unwrap()
+ .with_param_values(params)
+ .unwrap()
+ .into_optimized_plan()
+ .unwrap();
+ let LogicalPlan::Projection(projection) = plan else {
+ panic!("SQL must project the predicate");
+ };
+ assert_eq!(projection.expr.len(), 1);
+ assert_eq!(
+ projection.input.schema().field(0).data_type(),
+ &kind.data_type()
+ );
+ let expr = ctx
+ .create_physical_expr(
+ projection.expr[0].clone().unalias(),
+ projection.input.schema(),
+ )
+ .unwrap();
+ let schema = batch.schema();
+ assert_eq!(expr.data_type(schema.as_ref()).unwrap(), DataType::Boolean);
+ // Check types without requiring either IN or comparison-chain expressions.
+ let mut nodes = vec![Arc::clone(&expr)];
+ while let Some(node) = nodes.pop() {
+ let data_type = node.data_type(schema.as_ref()).unwrap();
+ assert!(data_type == DataType::Boolean || data_type == kind.data_type());
+ nodes.extend(node.children().into_iter().cloned());
+ }
+ expr
+}
+
+fn make_batch(
+ kind: ValueKind,
+ profile: Profile,
+ profile_index: usize,
+ list_len: usize,
+ negated: bool,
+) -> (RecordBatch, BooleanArray) {
+ let null_count = profile.batch_size() * profile.null_percent() / 100;
+ let non_null_count = profile.batch_size() - null_count;
+ let match_count = non_null_count * profile.match_percent() / 100;
+ let list_has_null = matches!(profile, Profile::ListNull);
+ let first_hits = matches!(
+ profile,
+ Profile::AllFirstHit | Profile::SkewedHit | Profile::SingleHit
+ );
+ let mut values = Vec::with_capacity(profile.batch_size());
+ values.extend(std::iter::repeat_n(None, null_count));
+ values.extend((0..match_count).map(|i| {
+ Some(if first_hits {
+ 0
+ } else {
+ i % (list_len - usize::from(list_has_null))
+ })
+ }));
+ values.extend((0..non_null_count - match_count).map(|i| Some(MISS_VALUE_BASE + i)));
+ let seed = 0x1A11_1575_5EED_u64
+ ^ (kind.seed_tag() << 48)
+ ^ ((profile_index as u64) << 32)
+ ^ list_len as u64;
+ values.shuffle(&mut StdRng::seed_from_u64(seed));
+ let expected = values
+ .iter()
+ .map(|value| {
+ value.and_then(|v| {
+ if v < list_len - usize::from(list_has_null) {
+ Some(!negated)
+ } else if list_has_null {
+ None
+ } else {
+ Some(negated)
+ }
+ })
+ })
+ .collect::<BooleanArray>();
+ let mut array = kind.make_array(&values);
+ if matches!(profile, Profile::Unaligned) {
+ let binary = array
+ .as_any()
+ .downcast_ref::<FixedSizeBinaryArray>()
+ .unwrap();
+ let mut bytes = MutableBuffer::with_capacity(binary.values().len() + 1);
+ bytes.push(0_u8);
+ bytes.extend_from_slice(binary.values());
+ let buffer = Buffer::from(bytes).slice(1);
+ assert_ne!(buffer.as_ptr().align_offset(16), 0);
+ array = Arc::new(FixedSizeBinaryArray::new(
+ 16,
+ buffer,
+ binary.nulls().cloned(),
+ ));
+ }
+ let schema = Arc::new(Schema::new(vec![Field::new("a", kind.data_type(), true)]));
+ (RecordBatch::try_new(schema, vec![array]).unwrap(), expected)
+}
+
+fn criterion_benchmark(c: &mut Criterion) {
+ let runtime = Runtime::new().unwrap();
+ let ctx = SessionContext::new();
+ for kind in ValueKind::ALL {
+ for (profile_index, profile) in Profile::ALL.into_iter().enumerate() {
+ if !profile.permits_kind(kind) {
+ continue;
+ }
+ let mut group = c.benchmark_group(format!(
+ "in_list_rewrite/{}/{}/batch={}/nulls={}%/match={}%",
+ kind.name(),
+ profile.name(),
+ profile.batch_size(),
+ profile.null_percent(),
+ profile.match_percent()
+ ));
+ group.throughput(Throughput::Elements(profile.batch_size() as u64));
+ for list_len in [2, 3] {
+ for negated in [false, true] {
+ let (batch, expected) =
+ make_batch(kind, profile, profile_index, list_len, negated);
+ let expr = plan_in_list(
+ &ctx,
+ &runtime,
+ kind,
+ &batch,
+ list_len,
+ negated,
+ matches!(profile, Profile::ListNull),
+ );
+ let output = expr
+ .evaluate(&batch)
+ .unwrap()
+ .into_array(batch.num_rows())
+ .unwrap();
+ assert_eq!(output.as_boolean(), &expected);
+ let case = format!(
+ "{}/list={list_len}",
+ if negated { "not_in" } else { "in" }
+ );
+ group.bench_function(case, |b| {
+ b.iter(|| black_box(expr.evaluate(black_box(&batch)).unwrap()))
+ });
+ }
+ }
+ group.finish();
+ }
+ }
+}
+
+criterion_group! {
+ name = benches;
+ config = Criterion::default()
+ .warm_up_time(Duration::from_millis(100))
+ .measurement_time(Duration::from_millis(500));
+ targets = criterion_benchmark
+}
+criterion_main!(benches);
diff --git a/datafusion/optimizer/src/simplify_expressions/expr_simplifier.rs b/datafusion/optimizer/src/simplify_expressions/expr_simplifier.rs
index ad23bb3c25..63929263d4 100644
--- a/datafusion/optimizer/src/simplify_expressions/expr_simplifier.rs
+++ b/datafusion/optimizer/src/simplify_expressions/expr_simplifier.rs
@@ -117,6 +117,12 @@ pub struct ExprSimplifier {
max_simplifier_cycles: u32,
}
+/// Largest `IN` list considered for expansion into binary comparisons.
+///
+/// The final decision also accounts for the measured performance of the column
+/// representation and whether the list values are literals.
+/// See [PR #25187](https://github.com/apache/datafusion/pull/25187) for the
+/// benchmark evidence.
pub const THRESHOLD_INLINE_INLIST: usize = 3;
pub const DEFAULT_MAX_SIMPLIFIER_CYCLES: u32 = 3;
@@ -199,7 +205,8 @@ impl ExprSimplifier {
let mut simplifier = Simplifier::new(&self.info);
let config_options = Some(Arc::clone(self.info.config_options()));
let mut const_evaluator = ConstEvaluator::try_new(config_options)?;
- let mut shorten_in_list_simplifier = ShortenInListSimplifier::new();
+ let mut shorten_in_list_simplifier =
+ ShortenInListSimplifier::new(self.info.schema());
let guarantees_map: HashMap<&Expr, &NullableInterval> =
self.guarantees.iter().map(|(k, v)| (k, v)).collect();
@@ -5006,6 +5013,8 @@ mod tests {
};
let left_list = literals(&[0.0, 1.0, 2.0, 3.0])?;
let right_list = literals(&[-0.0, 4.0, 5.0, 6.0])?;
+ let retains_short_lists =
+ matches!(data_type, DataType::Float32 | DataType::Float64);
let schema = Arc::new(Schema::new(vec![Field::new("x", data_type, false)]));
let batch = RecordBatch::try_new(schema, vec![values])?;
let schema = batch.schema().to_dfschema_ref()?;
@@ -5030,7 +5039,7 @@ mod tests {
(left(true).or(right(true)), vec![false, false]),
] {
let simplified = simplifier.simplify(expr.clone())?;
- if list_len > THRESHOLD_INLINE_INLIST {
+ if list_len > THRESHOLD_INLINE_INLIST || retains_short_lists {
// Floating-point literals must bypass structural set rewrites.
assert_eq!(simplified, expr);
} else {
@@ -5099,6 +5108,80 @@ mod tests {
}
}
+ #[test]
+ fn simplify_short_inlist_types() {
+ for (data_type, retain_short) in [
+ (DataType::Int32, true),
+ (DataType::Int64, true),
+ (DataType::UInt64, true),
+ (DataType::Float32, true),
+ (DataType::Float64, true),
+ (DataType::FixedSizeBinary(1), true),
+ (DataType::FixedSizeBinary(2), true),
+ (DataType::FixedSizeBinary(4), true),
+ (DataType::FixedSizeBinary(8), true),
+ (DataType::FixedSizeBinary(16), true),
+ (DataType::Float16, false),
+ (DataType::FixedSizeBinary(3), false),
+ (DataType::Utf8View, false),
+ (
+ DataType::Dictionary(
+ Box::new(DataType::Int8),
+ Box::new(DataType::Float64),
+ ),
+ false,
+ ),
+ ] {
+ let values = (1..=3)
+ .map(|value| {
+ lit(match &data_type {
+ DataType::FixedSizeBinary(width) => ScalarValue::FixedSizeBinary(
+ *width,
+ Some(vec![value; *width as usize]),
+ ),
+ DataType::Utf8View => {
+ ScalarValue::Utf8View(Some(value.to_string()))
+ }
+ _ => ScalarValue::Int32(Some(i32::from(value)))
+ .cast_to(&data_type)
+ .unwrap(),
+ })
+ })
+ .collect::<Vec<_>>();
+ let schema = Schema::new(vec![Field::new("value", data_type.clone(), true)])
+ .to_dfschema_ref()
+ .unwrap();
+ let simplifier = ExprSimplifier::new(
+ SimplifyContext::builder().with_schema(schema).build(),
+ );
+ for list_len in [2, 3] {
+ for negated in [false, true] {
+ let expr =
+ in_list(col("value"), values[..list_len].to_vec(), negated);
+ let simplified = simplifier.simplify(expr.clone()).unwrap();
+ if retain_short {
+ assert_eq!(simplified, expr, "{data_type:?}");
+ } else {
+ let op = if negated { Operator::And } else { Operator::Or };
+ assert!(
+ matches!(simplified, Expr::BinaryExpr(binary) if binary.op == op),
+ "{data_type:?}, list_len={list_len}, negated={negated}"
+ );
+ }
+ }
+ }
+ }
+ }
+
+ #[test]
+ fn simplify_short_inlist_coerced_literals() {
+ for negated in [false, true] {
+ let expr = in_list(col("c3"), vec![lit(1_i32), lit(2_i32)], negated);
+ let expected = in_list(col("c3"), vec![lit(1_i64), lit(2_i64)], negated);
+ assert_eq!(simplify(coerce(expr)), expected);
+ }
+ }
+
#[test]
fn simplify_inlist_set_operation_propagates_nullability_errors() {
let info = SimplifyContext::builder()
diff --git a/datafusion/optimizer/src/simplify_expressions/inlist_simplifier.rs b/datafusion/optimizer/src/simplify_expressions/inlist_simplifier.rs
index 17112d4f0a..087fb192c9 100644
--- a/datafusion/optimizer/src/simplify_expressions/inlist_simplifier.rs
+++ b/datafusion/optimizer/src/simplify_expressions/inlist_simplifier.rs
@@ -19,24 +19,49 @@
use super::THRESHOLD_INLINE_INLIST;
-use datafusion_common::Result;
+use arrow::datatypes::DataType;
use datafusion_common::tree_node::{Transformed, TreeNodeRewriter};
-use datafusion_expr::Expr;
+use datafusion_common::{DFSchema, Result};
use datafusion_expr::expr::InList;
+use datafusion_expr::{Expr, ExprSchemable};
-pub(super) struct ShortenInListSimplifier {}
+pub(super) struct ShortenInListSimplifier<'a> {
+ schema: &'a DFSchema,
+}
+
+impl<'a> ShortenInListSimplifier<'a> {
+ pub(super) fn new(schema: &'a DFSchema) -> Self {
+ Self { schema }
+ }
-impl ShortenInListSimplifier {
- pub(super) fn new() -> Self {
- Self {}
+ /// Benchmarks in `in_list_rewrite` favor evaluating short literal lists as
+ /// membership expressions over OR/AND chains for these types on batches with
+ /// a mix of matches and misses, or no matches. Integer chains can still be
+ /// faster when every row matches the first value, allowing the remaining
+ /// comparisons to short circuit.
+ ///
+ /// See [PR #25187](https://github.com/apache/datafusion/pull/25187) for measurements.
+ fn should_retain_short_inlist(&self, expr: &Expr, list: &[Expr]) -> bool {
+ let Ok(data_type) = expr.get_type(self.schema) else {
+ return false;
+ };
+ matches!(
+ data_type,
+ DataType::Int32
+ | DataType::Int64
+ | DataType::UInt64
+ | DataType::Float32
+ | DataType::Float64
+ | DataType::FixedSizeBinary(1 | 2 | 4 | 8 | 16)
+ ) && list.iter().all(|expr| expr.as_literal().is_some())
}
}
-impl TreeNodeRewriter for ShortenInListSimplifier {
+impl TreeNodeRewriter for ShortenInListSimplifier<'_> {
type Node = Expr;
fn f_up(&mut self, expr: Expr) -> Result<Transformed<Expr>> {
- // if expr is a single column reference:
+ // Rewrite eligible short lists to left-deep comparison chains:
// expr IN (A, B, ...) --> (expr = A) OR (expr = B) OR (expr = C)
if let Expr::InList(InList {
ref expr,
@@ -52,6 +77,7 @@ impl TreeNodeRewriter for ShortenInListSimplifier {
list.len() == 1
|| list.len() <= THRESHOLD_INLINE_INLIST
&& expr.try_as_col().is_some()
+ && !self.should_retain_short_inlist(expr, list)
)
{
let first_val = list[0].clone();
diff --git a/datafusion/sqllogictest/test_files/eliminate_outer_join.slt b/datafusion/sqllogictest/test_files/eliminate_outer_join.slt
index a17ce11a0b..7e4d100b74 100644
--- a/datafusion/sqllogictest/test_files/eliminate_outer_join.slt
+++ b/datafusion/sqllogictest/test_files/eliminate_outer_join.slt
@@ -45,9 +45,9 @@ explain select * from t1 left join t2 on t1.a = t2.x where t2.x in (1, 2, 3);
----
logical_plan
01)Inner Join: t1.a = t2.x
-02)--Filter: t1.a = Int32(1) OR t1.a = Int32(2) OR t1.a = Int32(3)
+02)--Filter: t1.a IN ([Int32(1), Int32(2), Int32(3)])
03)----TableScan: t1 projection=[a, b, c]
-04)--Filter: t2.x = Int32(1) OR t2.x = Int32(2) OR t2.x = Int32(3)
+04)--Filter: t2.x IN ([Int32(1), Int32(2), Int32(3)])
05)----TableScan: t2 projection=[x, y, z]
# Verify result correctness
@@ -63,9 +63,9 @@ explain select * from t1 right join t2 on t1.a = t2.x where t1.a in (1, 2);
----
logical_plan
01)Inner Join: t1.a = t2.x
-02)--Filter: t1.a = Int32(1) OR t1.a = Int32(2)
+02)--Filter: t1.a IN ([Int32(1), Int32(2)])
03)----TableScan: t1 projection=[a, b, c]
-04)--Filter: t2.x = Int32(1) OR t2.x = Int32(2)
+04)--Filter: t2.x IN ([Int32(1), Int32(2)])
05)----TableScan: t2 projection=[x, y, z]
query IITIIT rowsort
@@ -80,9 +80,9 @@ explain select * from t1 full join t2 on t1.a = t2.x where t1.a in (1, 2) and t2
----
logical_plan
01)Inner Join: t1.a = t2.x
-02)--Filter: t1.a = Int32(1) OR t1.a = Int32(2)
+02)--Filter: t1.a IN ([Int32(1), Int32(2)])
03)----TableScan: t1 projection=[a, b, c]
-04)--Filter: t2.x = Int32(1) OR t2.x = Int32(2)
+04)--Filter: t2.x IN ([Int32(1), Int32(2)])
05)----TableScan: t2 projection=[x, y, z]
query IITIIT rowsort
@@ -97,9 +97,9 @@ explain select * from t1 left join t2 on t1.a = t2.x where t2.x in (1, 2, null);
----
logical_plan
01)Inner Join: t1.a = t2.x
-02)--Filter: t1.a = Int32(1) OR t1.a = Int32(2)
+02)--Filter: t1.a IN ([Int32(1), Int32(2), Int32(NULL)])
03)----TableScan: t1 projection=[a, b, c]
-04)--Filter: t2.x = Int32(1) OR t2.x = Int32(2)
+04)--Filter: t2.x IN ([Int32(1), Int32(2), Int32(NULL)])
05)----TableScan: t2 projection=[x, y, z]
query IITIIT rowsort
@@ -168,9 +168,9 @@ explain select * from t1 left join t2 on t1.a = t2.x where t1.a in (1, 2, 3);
----
logical_plan
01)Left Join: t1.a = t2.x
-02)--Filter: t1.a = Int32(1) OR t1.a = Int32(2) OR t1.a = Int32(3)
+02)--Filter: t1.a IN ([Int32(1), Int32(2), Int32(3)])
03)----TableScan: t1 projection=[a, b, c]
-04)--Filter: t2.x = Int32(1) OR t2.x = Int32(2) OR t2.x = Int32(3)
+04)--Filter: t2.x IN ([Int32(1), Int32(2), Int32(3)])
05)----TableScan: t2 projection=[x, y, z]
query IITIIT rowsort
@@ -185,7 +185,7 @@ query TT
explain select * from t1 left join t2 on t1.a = t2.x where t2.x in (1, 2) or t2.x is null;
----
logical_plan
-01)Filter: t2.x = Int32(1) OR t2.x = Int32(2) OR t2.x IS NULL
+01)Filter: t2.x IN ([Int32(1), Int32(2)]) OR t2.x IS NULL
02)--Left Join: t1.a = t2.x
03)----TableScan: t1 projection=[a, b, c]
04)----TableScan: t2 projection=[x, y, z]
@@ -459,9 +459,9 @@ explain select * from t1 full join t2 on t1.a = t2.x where t2.x in (1, 2);
----
logical_plan
01)Right Join: t1.a = t2.x
-02)--Filter: t1.a = Int32(1) OR t1.a = Int32(2)
+02)--Filter: t1.a IN ([Int32(1), Int32(2)])
03)----TableScan: t1 projection=[a, b, c]
-04)--Filter: t2.x = Int32(1) OR t2.x = Int32(2)
+04)--Filter: t2.x IN ([Int32(1), Int32(2)])
05)----TableScan: t2 projection=[x, y, z]
query IITIIT rowsort
@@ -512,9 +512,9 @@ explain select * from t1 left join t2 on t1.a = t2.x where t2.x in (1, 2) and t2
----
logical_plan
01)Inner Join: t1.a = t2.x
-02)--Filter: t1.a = Int32(1) OR t1.a = Int32(2)
+02)--Filter: t1.a IN ([Int32(1), Int32(2)])
03)----TableScan: t1 projection=[a, b, c]
-04)--Filter: (t2.x = Int32(1) OR t2.x = Int32(2)) AND t2.y >= Int32(50) AND t2.y <= Int32(250)
+04)--Filter: t2.x IN ([Int32(1), Int32(2)]) AND t2.y >= Int32(50) AND t2.y <= Int32(250)
05)----TableScan: t2 projection=[x, y, z]
query IITIIT rowsort
diff --git a/datafusion/sqllogictest/test_files/in_list.slt b/datafusion/sqllogictest/test_files/in_list.slt
index 8fde8b11fe..2a41eb3426 100644
--- a/datafusion/sqllogictest/test_files/in_list.slt
+++ b/datafusion/sqllogictest/test_files/in_list.slt
@@ -20,9 +20,11 @@
#
# This file focuses on the IN operator and its various specializations
#
-# Note that "short" IN LISTS do not go through the InList implementation at all,
-# instead they are rewritten into a series of OR expressions. See:
-# https://github.com/apache/datafusion/blob/ed37b6c9555bc278130dc774ed833b8c0bd29bfa/datafusion/optimizer/src/simplify_expressions/inlist_simplifier.rs#L39-L88
+# One-item IN lists are rewritten to a comparison. Two- and three-item literal
+# lists on Int32, Int64, UInt64, Float32, Float64, or FixedSizeBinary columns
+# with widths 1, 2, 4, 8, or 16 retain their static InList filters. Other short
+# lists on columns are rewritten to OR/AND expressions. See:
+# https://github.com/apache/datafusion/blob/main/datafusion/optimizer/src/simplify_expressions/inlist_simplifier.rs
##########
@@ -211,6 +213,69 @@ CREATE TABLE in_list_ints_nullable (
('no_match', 7, 7, 7, 7, 7, 7, 7, 7),
('nulls', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
+# Measured integer representations retain two- and three-item literal lists.
+query TBBBBBB
+SELECT label,
+ i32 IN (arrow_cast(3, 'Int32'), arrow_cast(11, 'Int32')),
+ i32 NOT IN (arrow_cast(3, 'Int32'), arrow_cast(4, 'Int32'), arrow_cast(11, 'Int32')),
+ i64 IN (arrow_cast(3, 'Int64'), arrow_cast(4, 'Int64'), arrow_cast(11, 'Int64')),
+ i64 NOT IN (arrow_cast(3, 'Int64'), arrow_cast(11, 'Int64')),
+ u64 IN (arrow_cast(3, 'UInt64'), arrow_cast(11, 'UInt64')),
+ u64 NOT IN (arrow_cast(3, 'UInt64'), arrow_cast(4, 'UInt64'), arrow_cast(11, 'UInt64'))
+FROM in_list_ints_nullable
+ORDER BY label
+----
+match true false true false true false
+no_match false true false true false true
+nulls NULL NULL NULL NULL NULL NULL
+
+query TBBBBBB
+SELECT label,
+ i32 IN (arrow_cast(NULL, 'Int32'), arrow_cast(11, 'Int32')),
+ i32 NOT IN (arrow_cast(NULL, 'Int32'), arrow_cast(3, 'Int32'), arrow_cast(11, 'Int32')),
+ i64 IN (arrow_cast(NULL, 'Int64'), arrow_cast(3, 'Int64'), arrow_cast(11, 'Int64')),
+ i64 NOT IN (arrow_cast(NULL, 'Int64'), arrow_cast(11, 'Int64')),
+ u64 IN (arrow_cast(NULL, 'UInt64'), arrow_cast(11, 'UInt64')),
+ u64 NOT IN (arrow_cast(NULL, 'UInt64'), arrow_cast(3, 'UInt64'), arrow_cast(11, 'UInt64'))
+FROM in_list_ints_nullable
+ORDER BY label
+----
+match true false true false true false
+no_match NULL NULL NULL NULL NULL NULL
+nulls NULL NULL NULL NULL NULL NULL
+
+# A column-valued list keeps the comparison path for these representations.
+query TBBBBBB
+SELECT label,
+ i32 IN (arrow_cast(3, 'Int32'), i16),
+ i32 NOT IN (arrow_cast(3, 'Int32'), i16),
+ i64 IN (arrow_cast(3, 'Int64'), arrow_cast(4, 'Int64'), i32),
+ i64 NOT IN (arrow_cast(3, 'Int64'), arrow_cast(4, 'Int64'), i32),
+ u64 IN (arrow_cast(3, 'UInt64'), u32),
+ u64 NOT IN (arrow_cast(3, 'UInt64'), u32)
+FROM in_list_ints_nullable
+ORDER BY label
+----
+match true false true false true false
+no_match true false true false true false
+nulls NULL NULL NULL NULL NULL NULL
+
+query TT
+EXPLAIN SELECT i32 IN (arrow_cast(3, 'Int32'), arrow_cast(11, 'Int32')) AS i32_in_two,
+ i64 NOT IN (arrow_cast(3, 'Int64'), arrow_cast(4, 'Int64'), arrow_cast(11, 'Int64')) AS i64_not_in_three,
+ u64 IN (arrow_cast(3, 'UInt64'), arrow_cast(4, 'UInt64'), arrow_cast(11, 'UInt64')) AS u64_in_three,
+ i64 IN (arrow_cast(1, 'Int64')) AS i64_in_one,
+ i64 IN (arrow_cast(3, 'Int64'), i32) AS i64_in_column,
+ i64 NOT IN (arrow_cast(3, 'Int64'), i32) AS i64_not_in_column
+FROM in_list_ints_nullable;
+----
+logical_plan
+01)Projection: in_list_ints_nullable.i32 IN ([Int32(3), Int32(11)]) AS i32_in_two, in_list_ints_nullable.i64 NOT IN ([Int64(3), Int64(4), Int64(11)]) AS i64_not_in_three, in_list_ints_nullable.u64 IN ([UInt64(3), UInt64(4), UInt64(11)]) AS u64_in_three, in_list_ints_nullable.i64 = Int64(1) AS i64_in_one, in_list_ints_nullable.i64 = Int64(3) OR in_list_ints_nullable.i64 = CAST(in_list_ints_nullable.i32 AS Int64) AS i64_in_column, in_list_ints_nullable.i64 != Int64(3) AND in_list_ints_null [...]
+02)--TableScan: in_list_ints_nullable projection=[i32, i64, u64]
+physical_plan
+01)ProjectionExec: expr=[i32@0 IN (SET) ([3, 11]) as i32_in_two, i64@1 NOT IN (SET) ([3, 4, 11]) as i64_not_in_three, u64@2 IN (SET) ([3, 4, 11]) as u64_in_three, i64@1 = 1 as i64_in_one, i64@1 = 3 OR i64@1 = CAST(i32@0 AS Int64) as i64_in_column, i64@1 != 3 AND i64@1 != CAST(i32@0 AS Int64) as i64_not_in_column]
+02)--DataSourceExec: partitions=1, partition_sizes=[1]
+
# Null input values return NULL when the IN list has no nulls.
query TBBBBBBBB
SELECT
@@ -308,6 +373,79 @@ FROM (VALUES
('nulls', NULL)
) AS t(label, value);
+# Short Float32 and Float64 lists retain membership filters for IN and NOT IN.
+query TBBBBBBBB
+SELECT
+ label,
+ f32 IN (arrow_cast(3.0, 'Float32'), arrow_cast(11.0, 'Float32')),
+ f32 NOT IN (arrow_cast(3.0, 'Float32'), arrow_cast(11.0, 'Float32')),
+ f32 IN (arrow_cast(3.0, 'Float32'), arrow_cast(4.0, 'Float32'), arrow_cast(11.0, 'Float32')),
+ f32 NOT IN (arrow_cast(3.0, 'Float32'), arrow_cast(4.0, 'Float32'), arrow_cast(11.0, 'Float32')),
+ f32 IN (NULL, arrow_cast(11.0, 'Float32')),
+ f32 NOT IN (NULL, arrow_cast(11.0, 'Float32')),
+ f32 IN (NULL, arrow_cast(3.0, 'Float32'), arrow_cast(11.0, 'Float32')),
+ f32 NOT IN (NULL, arrow_cast(3.0, 'Float32'), arrow_cast(11.0, 'Float32'))
+FROM in_list_floats
+ORDER BY label
+----
+match true false true false true false true false
+no_match false true false true NULL NULL NULL NULL
+nulls NULL NULL NULL NULL NULL NULL NULL NULL
+
+query TBBBBBBBB
+SELECT
+ label,
+ f64 IN (3.0, 11.0),
+ f64 NOT IN (3.0, 11.0),
+ f64 IN (3.0, 4.0, 11.0),
+ f64 NOT IN (3.0, 4.0, 11.0),
+ f64 IN (NULL, 11.0),
+ f64 NOT IN (NULL, 11.0),
+ f64 IN (NULL, 3.0, 11.0),
+ f64 NOT IN (NULL, 3.0, 11.0)
+FROM in_list_floats
+ORDER BY label
+----
+match true false true false true false true false
+no_match false true false true NULL NULL NULL NULL
+nulls NULL NULL NULL NULL NULL NULL NULL NULL
+
+# A list containing a column is evaluated through comparisons.
+query TBBBB
+SELECT label,
+ f32 IN (arrow_cast(3.0, 'Float32'), f16),
+ f32 NOT IN (arrow_cast(3.0, 'Float32'), f16),
+ f64 IN (3.0, f32), f64 NOT IN (3.0, f32)
+FROM in_list_floats
+ORDER BY label
+----
+match true false true false
+no_match true false true false
+nulls NULL NULL NULL NULL
+
+query TT
+EXPLAIN SELECT f32 IN (arrow_cast(3.0, 'Float32'), arrow_cast(11.0, 'Float32')) AS in_two,
+ f32 NOT IN (arrow_cast(3.0, 'Float32'), arrow_cast(4.0, 'Float32'), arrow_cast(11.0, 'Float32')) AS not_in_three
+FROM in_list_floats;
+----
+logical_plan
+01)Projection: in_list_floats.f32 IN ([Float32(3), Float32(11)]) AS in_two, in_list_floats.f32 NOT IN ([Float32(3), Float32(4), Float32(11)]) AS not_in_three
+02)--TableScan: in_list_floats projection=[f32]
+physical_plan
+01)ProjectionExec: expr=[f32@0 IN (SET) ([3, 11]) as in_two, f32@0 NOT IN (SET) ([3, 4, 11]) as not_in_three]
+02)--DataSourceExec: partitions=1, partition_sizes=[1]
+
+query TT
+EXPLAIN SELECT f64 IN (3.0, 11.0) AS in_two, f64 NOT IN (3.0, 4.0, 11.0) AS not_in_three
+FROM in_list_floats;
+----
+logical_plan
+01)Projection: in_list_floats.f64 IN ([Float64(3), Float64(11)]) AS in_two, in_list_floats.f64 NOT IN ([Float64(3), Float64(4), Float64(11)]) AS not_in_three
+02)--TableScan: in_list_floats projection=[f64]
+physical_plan
+01)ProjectionExec: expr=[f64@0 IN (SET) ([3, 11]) as in_two, f64@0 NOT IN (SET) ([3, 4, 11]) as not_in_three]
+02)--DataSourceExec: partitions=1, partition_sizes=[1]
+
# Five element IN lists cover the specialized Float16/32/64 paths.
query TTBBB
SELECT
@@ -616,6 +754,82 @@ LIMIT 1
----
FixedSizeBinary(1) FixedSizeBinary(2) FixedSizeBinary(3) FixedSizeBinary(4) FixedSizeBinary(8) FixedSizeBinary(16)
+# Short lists at the width endpoints cover both list lengths and typed nulls.
+query TBBBBBB
+SELECT
+ label,
+ fsb1 IN (arrow_cast(X'03', 'FixedSizeBinary(1)'), arrow_cast(X'0B', 'FixedSizeBinary(1)')),
+ fsb1 NOT IN (arrow_cast(X'03', 'FixedSizeBinary(1)'), arrow_cast(X'04', 'FixedSizeBinary(1)'), arrow_cast(X'0B', 'FixedSizeBinary(1)')),
+ fsb1 IN (arrow_cast(NULL, 'FixedSizeBinary(1)'), arrow_cast(X'0B', 'FixedSizeBinary(1)')),
+ fsb16 IN (arrow_cast(X'00000000000000000000000000000003', 'FixedSizeBinary(16)'), arrow_cast(X'00000000000000000000000000000004', 'FixedSizeBinary(16)'), arrow_cast(X'0000000000000000000000000000000B', 'FixedSizeBinary(16)')),
+ fsb16 NOT IN (arrow_cast(X'00000000000000000000000000000003', 'FixedSizeBinary(16)'), arrow_cast(X'0000000000000000000000000000000B', 'FixedSizeBinary(16)')),
+ fsb16 NOT IN (arrow_cast(NULL, 'FixedSizeBinary(16)'), arrow_cast(X'00000000000000000000000000000003', 'FixedSizeBinary(16)'), arrow_cast(X'0000000000000000000000000000000B', 'FixedSizeBinary(16)'))
+FROM in_list_fsb
+ORDER BY label
+----
+match true false true true false false
+no_match false true NULL false true NULL
+nulls NULL NULL NULL NULL NULL NULL
+
+# A column-valued list retains the comparison path.
+query TBBBB
+SELECT l.label,
+ l.fsb1 IN (arrow_cast(X'03', 'FixedSizeBinary(1)'), r.fsb1),
+ l.fsb1 NOT IN (arrow_cast(X'03', 'FixedSizeBinary(1)'), r.fsb1),
+ l.fsb16 IN (arrow_cast(X'00000000000000000000000000000003', 'FixedSizeBinary(16)'), arrow_cast(X'00000000000000000000000000000004', 'FixedSizeBinary(16)'), r.fsb16),
+ l.fsb16 NOT IN (arrow_cast(X'00000000000000000000000000000003', 'FixedSizeBinary(16)'), arrow_cast(X'00000000000000000000000000000004', 'FixedSizeBinary(16)'), r.fsb16)
+FROM in_list_fsb l CROSS JOIN in_list_fsb r
+WHERE r.label = 'match'
+ORDER BY l.label
+----
+match true false true false
+no_match false true false true
+nulls NULL NULL NULL NULL
+
+query TT
+EXPLAIN SELECT fsb1 IN (arrow_cast(X'03', 'FixedSizeBinary(1)'), arrow_cast(X'0B', 'FixedSizeBinary(1)')) AS in_two,
+ fsb16 NOT IN (arrow_cast(X'00000000000000000000000000000003', 'FixedSizeBinary(16)'), arrow_cast(X'00000000000000000000000000000004', 'FixedSizeBinary(16)'), arrow_cast(X'0000000000000000000000000000000B', 'FixedSizeBinary(16)')) AS not_in_three
+FROM in_list_fsb;
+----
+logical_plan
+01)Projection: in_list_fsb.fsb1 IN ([FixedSizeBinary(1, "3"), FixedSizeBinary(1, "11")]) AS in_two, in_list_fsb.fsb16 NOT IN ([FixedSizeBinary(16, "0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,3"), FixedSizeBinary(16, "0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,4"), FixedSizeBinary(16, "0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,11")]) AS not_in_three
+02)--TableScan: in_list_fsb projection=[fsb1, fsb16]
+physical_plan
+01)ProjectionExec: expr=[fsb1@0 IN (SET) ([03, 0B]) as in_two, fsb16@1 NOT IN (SET) ([00000000000000000000..., 00000000000000000000..., 00000000000000000000...]) as not_in_three]
+02)--DataSourceExec: partitions=1, partition_sizes=[1]
+
+# Short eight-byte lists retain membership filters, including list nulls.
+query TBBBBBBBB
+SELECT
+ label,
+ fsb8 IN (arrow_cast(X'0000000000000003', 'FixedSizeBinary(8)'), arrow_cast(X'000000000000000B', 'FixedSizeBinary(8)')),
+ fsb8 NOT IN (arrow_cast(X'0000000000000003', 'FixedSizeBinary(8)'), arrow_cast(X'000000000000000B', 'FixedSizeBinary(8)')),
+ fsb8 IN (arrow_cast(X'0000000000000003', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000004', 'FixedSizeBinary(8)'), arrow_cast(X'000000000000000B', 'FixedSizeBinary(8)')),
+ fsb8 NOT IN (arrow_cast(X'0000000000000003', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000004', 'FixedSizeBinary(8)'), arrow_cast(X'000000000000000B', 'FixedSizeBinary(8)')),
+ fsb8 IN (arrow_cast(NULL, 'FixedSizeBinary(8)'), arrow_cast(X'000000000000000B', 'FixedSizeBinary(8)')),
+ fsb8 NOT IN (arrow_cast(NULL, 'FixedSizeBinary(8)'), arrow_cast(X'000000000000000B', 'FixedSizeBinary(8)')),
+ fsb8 IN (arrow_cast(NULL, 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000003', 'FixedSizeBinary(8)'), arrow_cast(X'000000000000000B', 'FixedSizeBinary(8)')),
+ fsb8 NOT IN (arrow_cast(NULL, 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000003', 'FixedSizeBinary(8)'), arrow_cast(X'000000000000000B', 'FixedSizeBinary(8)'))
+FROM in_list_fsb
+ORDER BY label
+----
+match true false true false true false true false
+no_match false true false true NULL NULL NULL NULL
+nulls NULL NULL NULL NULL NULL NULL NULL NULL
+
+query TT
+EXPLAIN SELECT
+ fsb8 IN (arrow_cast(X'0000000000000003', 'FixedSizeBinary(8)'), arrow_cast(X'000000000000000B', 'FixedSizeBinary(8)')) AS in_two,
+ fsb8 NOT IN (arrow_cast(X'0000000000000003', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000004', 'FixedSizeBinary(8)'), arrow_cast(X'000000000000000B', 'FixedSizeBinary(8)')) AS not_in_three
+FROM in_list_fsb;
+----
+logical_plan
+01)Projection: in_list_fsb.fsb8 IN ([FixedSizeBinary(8, "0,0,0,0,0,0,0,3"), FixedSizeBinary(8, "0,0,0,0,0,0,0,11")]) AS in_two, in_list_fsb.fsb8 NOT IN ([FixedSizeBinary(8, "0,0,0,0,0,0,0,3"), FixedSizeBinary(8, "0,0,0,0,0,0,0,4"), FixedSizeBinary(8, "0,0,0,0,0,0,0,11")]) AS not_in_three
+02)--TableScan: in_list_fsb projection=[fsb8]
+physical_plan
+01)ProjectionExec: expr=[fsb8@0 IN (SET) ([0000000000000003, 000000000000000B]) as in_two, fsb8@0 NOT IN (SET) ([0000000000000003, 0000000000000004, 000000000000000B]) as not_in_three]
+02)--DataSourceExec: partitions=1, partition_sizes=[1]
+
# Eight element lists, all matching or none matching
query TBBBBBB
SELECT
diff --git a/datafusion/sqllogictest/test_files/pwmj.slt b/datafusion/sqllogictest/test_files/pwmj.slt
index 104b38e1a5..619ebab6ba 100644
--- a/datafusion/sqllogictest/test_files/pwmj.slt
+++ b/datafusion/sqllogictest/test_files/pwmj.slt
@@ -257,7 +257,7 @@ logical_plan
01)Sort: t1.t1_id ASC NULLS LAST, t2.t2_id ASC NULLS LAST
02)--Inner Join: Filter: t1.t1_id <= t2.t2_id
03)----SubqueryAlias: t1
-04)------Filter: join_t1.t1_id = Int32(11) OR join_t1.t1_id = Int32(44)
+04)------Filter: join_t1.t1_id IN ([Int32(11), Int32(44)])
05)--------TableScan: join_t1 projection=[t1_id]
06)----SubqueryAlias: t2
07)------Projection: join_t2.t2_id
@@ -268,7 +268,7 @@ physical_plan
02)--SortExec: expr=[t1_id@0 ASC NULLS LAST, t2_id@1 ASC NULLS LAST], preserve_partitioning=[true]
03)----PiecewiseMergeJoin: operator=LtEq, join_type=Inner, on=(t1_id <= t2_id)
04)------SortExec: expr=[t1_id@0 DESC NULLS LAST], preserve_partitioning=[false]
-05)--------FilterExec: t1_id@0 = 11 OR t1_id@0 = 44
+05)--------FilterExec: t1_id@0 IN (SET) ([11, 44])
06)----------DataSourceExec: partitions=1, partition_sizes=[1]
07)------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1
08)--------FilterExec: t2_name@1 != y, projection=[t2_id@0]
diff --git a/datafusion/sqllogictest/test_files/range_partitioning.slt b/datafusion/sqllogictest/test_files/range_partitioning.slt
index 56184845a6..21f9f439a2 100644
--- a/datafusion/sqllogictest/test_files/range_partitioning.slt
+++ b/datafusion/sqllogictest/test_files/range_partitioning.slt
@@ -1928,7 +1928,7 @@ JOIN range_partitioned p ON b.range_key = p.range_key;
----
physical_plan
01)HashJoinExec: mode=Partitioned, join_type=Inner, on=[(range_key@0, range_key@0)], projection=[range_key@0, value@1, value@3]
-02)--DataSourceExec: file_groups=<slt:ignore>, projection=[range_key, value], output_partitioning=Range([range_key@0 ASC], [(10), (20), (30)], 4), file_type=parquet, predicate=range_key@0 = 5 OR range_key@0 = 20, pruning_predicate=range_key_null_count@2 != row_count@3 AND range_key_min@0 <= 5 AND 5 <= range_key_max@1 OR range_key_null_count@2 != row_count@3 AND range_key_min@0 <= 20 AND 20 <= range_key_max@1, required_guarantees=[range_key in (20, 5)]
+02)--DataSourceExec: file_groups=<slt:ignore>, projection=[range_key, value], output_partitioning=Range([range_key@0 ASC], [(10), (20), (30)], 4), file_type=parquet, predicate=range_key@0 IN (SET) ([5, 20]), pruning_predicate=range_key_null_count@2 != row_count@3 AND range_key_min@0 <= 5 AND 5 <= range_key_max@1 OR range_key_null_count@2 != row_count@3 AND range_key_min@0 <= 20 AND 20 <= range_key_max@1, required_guarantees=[range_key in (20, 5)]
03)--DataSourceExec: file_groups=<slt:ignore>, projection=[range_key, value], output_partitioning=Range([range_key@0 ASC], [(10), (20), (30)], 4), file_type=parquet, predicate=DynamicFilter [ empty ], dynamic_rg_pruning=eligible
query TT
@@ -1942,7 +1942,7 @@ JOIN range_partitioned p ON b.range_key = p.range_key;
----
Plan with Metrics
01)HashJoinExec: mode=Partitioned<slt:ignore>metrics=[output_rows=2,<slt:ignore>]
-02)--DataSourceExec: <slt:ignore>file_type=parquet, predicate=range_key@0 = 5 OR range_key@0 = 20<slt:ignore>metrics=[output_rows=2,<slt:ignore>]
+02)--DataSourceExec: <slt:ignore>file_type=parquet, predicate=range_key@0 IN (SET) ([5, 20])<slt:ignore>metrics=[output_rows=2,<slt:ignore>]
03)--DataSourceExec: <slt:ignore>file_type=parquet, predicate=DynamicFilter [ CASE range_partition WHEN 0 THEN range_key@0 >= 5 AND range_key@0 <= 5 AND range_key@0 IN (SET) ([5]) WHEN 2 THEN range_key@0 >= 20 AND range_key@0 <= 20 AND range_key@0 IN (SET) ([20]) ELSE false END ]<slt:ignore>metrics=[output_rows=2,<slt:ignore>]
query III
diff --git a/datafusion/sqllogictest/test_files/string_numeric_coercion.slt b/datafusion/sqllogictest/test_files/string_numeric_coercion.slt
index 196da416a2..c09f2c986e 100644
--- a/datafusion/sqllogictest/test_files/string_numeric_coercion.slt
+++ b/datafusion/sqllogictest/test_files/string_numeric_coercion.slt
@@ -229,10 +229,10 @@ query TT
EXPLAIN SELECT * FROM t_int WHERE column1 IN ('5', '325');
----
logical_plan
-01)Filter: t_int.column1 = Int64(5) OR t_int.column1 = Int64(325)
+01)Filter: t_int.column1 IN ([Int64(5), Int64(325)])
02)--TableScan: t_int projection=[column1]
physical_plan
-01)FilterExec: column1@0 = 5 OR column1@0 = 325
+01)FilterExec: column1@0 IN (SET) ([5, 325])
02)--DataSourceExec: partitions=1, partition_sizes=[1]
# Error on invalid string in IN list
diff --git a/datafusion/sqllogictest/test_files/union.slt b/datafusion/sqllogictest/test_files/union.slt
index ca83e4b652..2c706596b5 100644
--- a/datafusion/sqllogictest/test_files/union.slt
+++ b/datafusion/sqllogictest/test_files/union.slt
@@ -365,14 +365,14 @@ EXPLAIN SELECT id, name FROM t1 WHERE id = 1 UNION SELECT id, name FROM t1 WHERE
----
logical_plan
01)Aggregate: groupBy=[[id, name]], aggr=[[]]
-02)--Filter: t1.id = Int32(1) OR t1.id = Int32(2)
+02)--Filter: t1.id IN ([Int32(1), Int32(2)])
03)----TableScan: t1 projection=[id, name]
physical_plan
01)AggregateExec: mode=FinalPartitioned, gby=[id@0 as id, name@1 as name], aggr=[]
02)--RepartitionExec: partitioning=Hash([id@0, name@1], 4), input_partitions=4
03)----AggregateExec: mode=Partial, gby=[id@0 as id, name@1 as name], aggr=[]
04)------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1
-05)--------FilterExec: id@0 = 1 OR id@0 = 2
+05)--------FilterExec: id@0 IN (SET) ([1, 2])
06)----------DataSourceExec: partitions=1, partition_sizes=[1]
# Regression: schema recomputation must preserve the unqualified UNION
@@ -415,14 +415,14 @@ SELECT x.id FROM t1 AS x WHERE x.id = 2
logical_plan
01)Aggregate: groupBy=[[id]], aggr=[[]]
02)--SubqueryAlias: x
-03)----Filter: t1.id = Int32(1) OR t1.id = Int32(2)
+03)----Filter: t1.id IN ([Int32(1), Int32(2)])
04)------TableScan: t1 projection=[id]
physical_plan
01)AggregateExec: mode=FinalPartitioned, gby=[id@0 as id], aggr=[]
02)--RepartitionExec: partitioning=Hash([id@0], 4), input_partitions=4
03)----AggregateExec: mode=Partial, gby=[id@0 as id], aggr=[]
04)------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1
-05)--------FilterExec: id@0 = 1 OR id@0 = 2
+05)--------FilterExec: id@0 IN (SET) ([1, 2])
06)----------DataSourceExec: partitions=1, partition_sizes=[1]
# Matching computed projections remain eligible for the rewrite and must
diff --git a/datafusion/sqllogictest/test_files/window.slt b/datafusion/sqllogictest/test_files/window.slt
index b7a6ad531b..864a6e56bf 100644
--- a/datafusion/sqllogictest/test_files/window.slt
+++ b/datafusion/sqllogictest/test_files/window.slt
@@ -5673,7 +5673,7 @@ logical_plan
01)Sort: t1.c1 ASC NULLS LAST, t1.c2 ASC NULLS LAST, rank ASC NULLS LAST
02)--Projection: t1.c1, t1.c2, rank() PARTITION BY [t1.c1] ORDER BY [t1.c2 ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW AS rank
03)----WindowAggr: windowExpr=[[rank() PARTITION BY [t1.c1] ORDER BY [t1.c2 ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW]]
-04)------Filter: t1.c1 = Int32(2) OR t1.c1 = Int32(3)
+04)------Filter: t1.c1 IN ([Int32(2), Int32(3)])
05)--------TableScan: t1 projection=[c1, c2]
physical_plan
01)SortPreservingMergeExec: [c1@0 ASC NULLS LAST, c2@1 ASC NULLS LAST, rank@2 ASC NULLS LAST]
@@ -5681,7 +5681,7 @@ physical_plan
03)----BoundedWindowAggExec: wdw=[rank() PARTITION BY [t1.c1] ORDER BY [t1.c2 ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Field { "rank() PARTITION BY [t1.c1] ORDER BY [t1.c2 ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW": UInt64 }, frame: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW], mode=[Sorted]
04)------SortExec: expr=[c1@0 ASC NULLS LAST, c2@1 ASC NULLS LAST], preserve_partitioning=[true]
05)--------RepartitionExec: partitioning=Hash([c1@0], 2), input_partitions=2
-06)----------FilterExec: c1@0 = 2 OR c1@0 = 3
+06)----------FilterExec: c1@0 IN (SET) ([2, 3])
07)------------RepartitionExec: partitioning=RoundRobinBatch(2), input_partitions=1
08)--------------DataSourceExec: partitions=1, partition_sizes=[1]
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org