Skip to content

Commit 10ccc3e

Browse files
adriangbclaude
andcommitted
fix: keep outer references when matching the IN value to a correlation
`PullUpCorrelatedExpr::correlated_filters` came from `find_join_exprs`, which strips the outer references. A subquery column with the same qualified name as the outer `IN` value then read as that value, so a correlation such as `a.x = b.k` inside `FROM t AS a` was taken to keep a NULL outer `a.x` out of the scope. The join was then not null-aware, and the query gave FALSE, or kept the row under `NOT IN`, where the answer is UNKNOWN. Record the correlated conjuncts with their outer references, and match each side of a conjunct against the side of the join that the key is on. Co-Authored-By: Claude Opus 5.5 <[email protected]>
1 parent 97d3cff commit 10ccc3e

3 files changed

Lines changed: 74 additions & 13 deletions

File tree

‎datafusion/optimizer/src/decorrelate.rs‎

Lines changed: 8 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -76,7 +76,9 @@ pub struct PullUpCorrelatedExpr {
7676
pub pulled_up_scalar_agg: bool,
7777
/// Every correlated conjunct that a `Filter` of the subquery applies,
7878
/// before `remove_duplicated_filter` drops the ones that the `IN`
79-
/// predicate already covers.
79+
/// predicate already covers. The outer references are kept, so that a
80+
/// subquery column and an outer column with the same qualified name stay
81+
/// apart.
8082
///
8183
/// `join_filters` holds only the conjuncts that the join still needs.
8284
/// This list is what the subquery enforces on its own rows. The caller
@@ -193,13 +195,13 @@ impl TreeNodeRewriter for PullUpCorrelatedExpr {
193195
.iter()
194196
.filter(|e| e.contains_outer())
195197
.all(|&e| can_pullup_over_aggregation(e));
196-
let (mut join_filters, subquery_filters) =
197-
find_join_exprs(subquery_filter_exprs)?;
198-
for expr in &join_filters {
199-
if !self.correlated_filters.contains(expr) {
200-
self.correlated_filters.push(expr.clone());
198+
for expr in &subquery_filter_exprs {
199+
if expr.contains_outer() && !self.correlated_filters.contains(expr) {
200+
self.correlated_filters.push((*expr).clone());
201201
}
202202
}
203+
let (mut join_filters, subquery_filters) =
204+
find_join_exprs(subquery_filter_exprs)?;
203205
if let Some(in_predicate) = &self.in_predicate_opt {
204206
// in_predicate may be already included in the join filters, remove it from the join filters first.
205207
join_filters = remove_duplicated_filter(join_filters, in_predicate)?;

‎datafusion/optimizer/src/decorrelate_predicate_subquery.rs‎

Lines changed: 33 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -32,7 +32,9 @@ use datafusion_common::{
3232
Column, DFSchema, NullEquality, Result, ScalarValue, assert_or_internal_err, plan_err,
3333
};
3434
use datafusion_expr::expr::{Exists, InSubquery};
35-
use datafusion_expr::expr_rewriter::create_col_from_scalar_expr;
35+
use datafusion_expr::expr_rewriter::{
36+
create_col_from_scalar_expr, strip_outer_reference,
37+
};
3638
use datafusion_expr::logical_plan::{JoinType, Subquery};
3739
use datafusion_expr::utils::{conjunction, expr_to_columns, split_conjunction_owned};
3840
use datafusion_expr::{
@@ -556,10 +558,12 @@ impl InValue {
556558
Ok(key_may_be_null_in_scope(
557559
self.value.nullable(left_schema)?,
558560
&self.value_as_written,
561+
true,
559562
scope_filters,
560563
) || key_may_be_null_in_scope(
561564
self.subquery_column.nullable(right_schema)?,
562565
&self.output_expr,
566+
false,
563567
scope_filters,
564568
))
565569
}
@@ -588,18 +592,40 @@ impl InValue {
588592
/// is a comparison or an `IS NOT NULL` on the key expression itself, casts
589593
/// aside. Any other conjunct is assumed to let a NULL through, which keeps
590594
/// the join null-aware.
591-
fn key_may_be_null_in_scope(nullable: bool, key: &Expr, scope_filters: &[Expr]) -> bool {
595+
///
596+
/// `key_is_outer` tells on which side of the join the key is. The scope
597+
/// filters keep their outer references, and a side of a conjunct matches an
598+
/// outer key only if it names outer columns alone. A subquery column can have
599+
/// the same qualified name as an outer column (`FROM t AS a` inside a query
600+
/// over `a`), and it must not be read as the outer key.
601+
fn key_may_be_null_in_scope(
602+
nullable: bool,
603+
key: &Expr,
604+
key_is_outer: bool,
605+
scope_filters: &[Expr],
606+
) -> bool {
592607
if !nullable {
593608
return false;
594609
}
595610
let key = strip_casts(key);
596611
!scope_filters
597612
.iter()
598-
.any(|filter| filter_rejects_null(filter, key))
613+
.any(|filter| filter_rejects_null(filter, key, key_is_outer))
599614
}
600615

601-
/// Is `filter` never TRUE when `key` is NULL?
602-
fn filter_rejects_null(filter: &Expr, key: &Expr) -> bool {
616+
/// Is `filter` never TRUE when `key` is NULL? See
617+
/// [`key_may_be_null_in_scope`] for `key_is_outer`.
618+
fn filter_rejects_null(filter: &Expr, key: &Expr, key_is_outer: bool) -> bool {
619+
let is_key = |side: &Expr| {
620+
let side = strip_casts(side);
621+
if key_is_outer {
622+
side.contains_outer()
623+
&& side.column_refs().is_empty()
624+
&& &strip_outer_reference(side.clone()) == key
625+
} else {
626+
!side.contains_outer() && side == key
627+
}
628+
};
603629
match filter {
604630
Expr::BinaryExpr(BinaryExpr { left, op, right }) => {
605631
matches!(
@@ -610,9 +636,9 @@ fn filter_rejects_null(filter: &Expr, key: &Expr) -> bool {
610636
| Operator::LtEq
611637
| Operator::Gt
612638
| Operator::GtEq
613-
) && (strip_casts(left) == key || strip_casts(right) == key)
639+
) && (is_key(left) || is_key(right))
614640
}
615-
Expr::IsNotNull(expr) => strip_casts(expr) == key,
641+
Expr::IsNotNull(expr) => is_key(expr),
616642
_ => false,
617643
}
618644
}

‎datafusion/sqllogictest/test_files/subquery_projection.slt‎

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -595,3 +595,36 @@ DROP TABLE co;
595595

596596
statement ok
597597
DROP TABLE ci;
598+
599+
# A subquery column with the same qualified name as the outer `IN` value.
600+
#
601+
# Inside the subquery, `a` is `sh_t`, so `a.x = b.k` bounds `sh_t.x`, not the
602+
# outer `a.x`. It does not keep a NULL outer value out of the scope, and the
603+
# join must stay null-aware. The subquery is `{7}` for every outer row, so the
604+
# NULL row is UNKNOWN. Results verified with DuckDB 1.5.2.
605+
statement ok
606+
CREATE TABLE sh_o(x INT, k INT) AS VALUES (NULL, 1), (7, 1), (8, 1);
607+
608+
statement ok
609+
CREATE TABLE sh_t(x INT, y INT NOT NULL) AS VALUES (1, 7);
610+
611+
query IB
612+
SELECT a.x, a.x IN (SELECT a.y FROM sh_t AS a WHERE a.x = b.k) AS r
613+
FROM sh_o AS a, (SELECT 1 AS k) AS b ORDER BY 1;
614+
----
615+
7 true
616+
8 false
617+
NULL NULL
618+
619+
# The `NOT IN` filter drops the UNKNOWN row.
620+
query I
621+
SELECT a.x FROM sh_o AS a, (SELECT 1 AS k) AS b
622+
WHERE a.x NOT IN (SELECT a.y FROM sh_t AS a WHERE a.x = b.k);
623+
----
624+
8
625+
626+
statement ok
627+
DROP TABLE sh_o;
628+
629+
statement ok
630+
DROP TABLE sh_t;

0 commit comments

Comments
 (0)