Re: [PR] fix: UNIQUE constraint with NULLs incorrectly collapses GROUP BY groups [datafusion]
neilconway commented on PR #21715: URL: https://github.com/apache/datafusion/pull/21715#issuecomment-4622322987 @xiedeyantu FYI, I think this work is still worth pursuing. I've been busy but I was planning to give it another review pass in the next day or two. -- 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]
Re: [PR] fix: UNIQUE constraint with NULLs incorrectly collapses GROUP BY groups [datafusion]
xiedeyantu closed pull request #21715: fix: UNIQUE constraint with NULLs incorrectly collapses GROUP BY groups URL: https://github.com/apache/datafusion/pull/21715 -- 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]
Re: [PR] fix: UNIQUE constraint with NULLs incorrectly collapses GROUP BY groups [datafusion]
xiedeyantu commented on PR #21715: URL: https://github.com/apache/datafusion/pull/21715#issuecomment-4549918978 > @xiedeyantu Yeah, I think it's fine to defer that cleanup to a future PR. Can you resolve the merge conflicts? I have resolved the conflict. -- 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]
Re: [PR] fix: UNIQUE constraint with NULLs incorrectly collapses GROUP BY groups [datafusion]
neilconway commented on PR #21715: URL: https://github.com/apache/datafusion/pull/21715#issuecomment-4547902388 @xiedeyantu Yeah, I think it's fine to defer that cleanup to a future PR. Can you resolve the merge conflicts? -- 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]
Re: [PR] fix: UNIQUE constraint with NULLs incorrectly collapses GROUP BY groups [datafusion]
xiedeyantu commented on PR #21715: URL: https://github.com/apache/datafusion/pull/21715#issuecomment-4518533053 > After this PR, do we still need the concept of a functional dependency with `nullable` as `true`? I wonder if we can get eliminate that concept entirely. @neilconway To be honest, my initial goal was to fix this with minimal changes. Removing nullable would likely require a much wider refactor. I've addressed your other comments, but I'm unsure how to proceed here. Would it be better to leave this for a future cleanup? -- 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]
Re: [PR] fix: UNIQUE constraint with NULLs incorrectly collapses GROUP BY groups [datafusion]
neilconway commented on code in PR #21715:
URL: https://github.com/apache/datafusion/pull/21715#discussion_r3255319904
##
datafusion/expr/src/logical_plan/plan.rs:
##
@@ -2434,7 +2437,14 @@ impl SubqueryAlias {
// Requalify fields with the new `alias`.
let fields = plan.schema().fields().clone();
let meta_data = plan.schema().metadata().clone();
-let func_dependencies =
plan.schema().functional_dependencies().clone();
+// Recursive queries do not expose the anchor's functional
dependencies to
+// the outer schema — the recursive term can produce rows that violate
+// those dependencies, so they are intentionally dropped here.
+let func_dependencies = if is_recursive_query {
+FunctionalDependencies::empty()
+} else {
+plan.schema().functional_dependencies().clone()
+};
Review Comment:
Can we mention this change in the PR description?
##
datafusion/expr/src/logical_plan/plan.rs:
##
@@ -354,7 +354,6 @@ impl LogicalPlan {
LogicalPlan::Ddl(ddl) => ddl.schema(),
LogicalPlan::Unnest(Unnest { schema, .. }) => schema,
LogicalPlan::RecursiveQuery(RecursiveQuery { static_term, .. }) =>
{
-// we take the schema of the static term as the schema of the
entire recursive query
Review Comment:
Why remove this?
##
datafusion/sqllogictest/test_files/group_by.slt:
##
@@ -3565,33 +3565,27 @@ SELECT r.sn, r.amount, SUM(r.amount)
GROUP BY r.sn
ORDER BY r.sn
-# left semi join should propagate constraint of left side as is.
-query IRR
+# left semi join with a nullable UNIQUE key cannot safely propagate the
Review Comment:
I wonder if it would be better to mark the UNIQUE column as `NOT NULL`, so
we don't lose the intent of the original test. (Here and below)
##
datafusion/common/src/functional_dependencies.rs:
##
Review Comment:
Seems like `Constraint::Unique` can produce non-nullable FDs now, so we
should update this comment?
##
datafusion/expr/src/logical_plan/plan.rs:
##
@@ -2434,7 +2437,14 @@ impl SubqueryAlias {
// Requalify fields with the new `alias`.
let fields = plan.schema().fields().clone();
let meta_data = plan.schema().metadata().clone();
-let func_dependencies =
plan.schema().functional_dependencies().clone();
+// Recursive queries do not expose the anchor's functional
dependencies to
+// the outer schema — the recursive term can produce rows that violate
+// those dependencies, so they are intentionally dropped here.
Review Comment:
Seems a bit fragile to leave FDs on `plan.schema()` but only strip them out
here when they are being used. Should there be FDs on `plan.schema()` in the
first place?
BTW this might be affected by / related to #22037
##
datafusion/common/src/functional_dependencies.rs:
##
@@ -484,9 +505,12 @@ pub fn aggregate_functional_dependencies(
if !group_by_expr_names.is_empty() {
let count = group_by_expr_names.len();
let source_indices = (0..count).collect::>();
-let nullable = source_indices
-.iter()
-.any(|idx| aggr_fields[*idx].is_nullable());
+// Aggregation with GROUP BY always produces unique output rows for
+// each distinct combination of GROUP BY keys. The nullable flag is
+// set to false here so that subsequent expansion (e.g. a second
+// GROUP BY on the aggregate output) is never blocked by source
+// field nullability.
+let nullable = false;
Review Comment:
I'm trying to understand the motivation for this change. Can you add a test
case that covers this behavior, perhaps?
--
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]
Re: [PR] fix: UNIQUE constraint with NULLs incorrectly collapses GROUP BY groups [datafusion]
xiedeyantu commented on PR #21715: URL: https://github.com/apache/datafusion/pull/21715#issuecomment-4464544908 > > @nuno-faria Please let me know if there’s anything else I need to do. > > LGTM. @neilconway would you mind reviewing again? Thanks for your review @nuno-faria ! Let's wait for @neilconway 's comments. -- 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]
Re: [PR] fix: UNIQUE constraint with NULLs incorrectly collapses GROUP BY groups [datafusion]
nuno-faria commented on PR #21715: URL: https://github.com/apache/datafusion/pull/21715#issuecomment-4462643288 > @nuno-faria Please let me know if there’s anything else I need to do. LGTM. @neilconway would you mind reviewing again? -- 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]
Re: [PR] fix: UNIQUE constraint with NULLs incorrectly collapses GROUP BY groups [datafusion]
xiedeyantu commented on PR #21715: URL: https://github.com/apache/datafusion/pull/21715#issuecomment-4458733022 @nuno-faria Please let me know if there’s anything else I need to do. -- 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]
Re: [PR] fix: UNIQUE constraint with NULLs incorrectly collapses GROUP BY groups [datafusion]
xiedeyantu commented on PR #21715:
URL: https://github.com/apache/datafusion/pull/21715#issuecomment-4446047115
This error should be unrelated to PR.
```
Run cargo test --profile ci -p datafusion-ffi --lib --tests --features
integration-tests
cargo test --profile ci -p datafusion-ffi --lib --tests --features
integration-tests
shell: /bin/bash --noprofile --norc -e -o pipefail {0}
env:
CACHE_ON_FAILURE: false
CARGO_INCREMENTAL: 0
RUST_BACKTRACE: 1
RUSTFLAGS: -C debuginfo=line-tables-only -C incremental=false
error: error: unexpected argument 'test' found
Usage: rustup-init[EXE] [OPTIONS]
For more information, try '--help'.
Stack backtrace:
0: ::capture
1: anyhow::kind::Adhoc::new
2: rustup::cli::setup_mode::main::{{closure}}::{{closure}}
3: rustup_init::run_rustup_inner::{{closure}}::{{closure}}
4: rustup_init::run_rustup::{{closure}}::{{closure}}
5: rustup_init::main::{{closure}}
6: tokio::runtime::runtime::Runtime::block_on
7: rustup_init::main
8: std::sys::backtrace::__rust_begin_short_backtrace
9: _main
```
--
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]
Re: [PR] fix: UNIQUE constraint with NULLs incorrectly collapses GROUP BY groups [datafusion]
xiedeyantu commented on code in PR #21715: URL: https://github.com/apache/datafusion/pull/21715#discussion_r3237975630 ## datafusion/sqllogictest/test_files/cte.slt: ## @@ -1319,3 +1319,34 @@ RESET datafusion.execution.enable_recursive_ctes; statement ok RESET datafusion.sql_parser.enable_ident_normalization; + +# Regression test: functional dependencies from the static (anchor) term of a +# recursive CTE must NOT be propagated to the outer SubqueryAlias. The +# recursive term can produce rows that violate any uniqueness constraint that +# holds for the anchor alone. Without this guard, Filter(pk = const) on the +# CTE result would be mis-identified as scalar (at most 1 row) and return only +# one row instead of all matching rows. +statement ok +CREATE TABLE pk_table(id INT NOT NULL, val INT NOT NULL, PRIMARY KEY(id)); + +statement ok +INSERT INTO pk_table VALUES (1, 100), (2, 200); + +# The recursive term produces a second row with id=1 (val=300). Without the +# FD fix, Filter(nodes.id = 1) would be deemed scalar and return only the +# first matching row. +query II rowsort +WITH RECURSIVE nodes AS ( +SELECT id, val FROM pk_table +UNION ALL +SELECT 1 AS id, 300 AS val +FROM nodes +WHERE nodes.id = 2 +) +SELECT id, val FROM nodes WHERE id = 1 + +1 100 +1 300 Review Comment: Done! -- 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]
Re: [PR] fix: UNIQUE constraint with NULLs incorrectly collapses GROUP BY groups [datafusion]
nuno-faria commented on code in PR #21715: URL: https://github.com/apache/datafusion/pull/21715#discussion_r3236659519 ## datafusion/sqllogictest/test_files/cte.slt: ## @@ -1319,3 +1319,34 @@ RESET datafusion.execution.enable_recursive_ctes; statement ok RESET datafusion.sql_parser.enable_ident_normalization; + +# Regression test: functional dependencies from the static (anchor) term of a +# recursive CTE must NOT be propagated to the outer SubqueryAlias. The +# recursive term can produce rows that violate any uniqueness constraint that +# holds for the anchor alone. Without this guard, Filter(pk = const) on the +# CTE result would be mis-identified as scalar (at most 1 row) and return only +# one row instead of all matching rows. +statement ok +CREATE TABLE pk_table(id INT NOT NULL, val INT NOT NULL, PRIMARY KEY(id)); + +statement ok +INSERT INTO pk_table VALUES (1, 100), (2, 200); + +# The recursive term produces a second row with id=1 (val=300). Without the +# FD fix, Filter(nodes.id = 1) would be deemed scalar and return only the +# first matching row. +query II rowsort +WITH RECURSIVE nodes AS ( +SELECT id, val FROM pk_table +UNION ALL +SELECT 1 AS id, 300 AS val +FROM nodes +WHERE nodes.id = 2 +) +SELECT id, val FROM nodes WHERE id = 1 + +1 100 +1 300 Review Comment: Thanks, that new test shows the error. Could you also remove the original one? -- 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]
Re: [PR] fix: UNIQUE constraint with NULLs incorrectly collapses GROUP BY groups [datafusion]
xiedeyantu commented on code in PR #21715: URL: https://github.com/apache/datafusion/pull/21715#discussion_r3230804642 ## datafusion/sqllogictest/test_files/cte.slt: ## @@ -1319,3 +1319,34 @@ RESET datafusion.execution.enable_recursive_ctes; statement ok RESET datafusion.sql_parser.enable_ident_normalization; + +# Regression test: functional dependencies from the static (anchor) term of a +# recursive CTE must NOT be propagated to the outer SubqueryAlias. The +# recursive term can produce rows that violate any uniqueness constraint that +# holds for the anchor alone. Without this guard, Filter(pk = const) on the +# CTE result would be mis-identified as scalar (at most 1 row) and return only +# one row instead of all matching rows. +statement ok +CREATE TABLE pk_table(id INT NOT NULL, val INT NOT NULL, PRIMARY KEY(id)); + +statement ok +INSERT INTO pk_table VALUES (1, 100), (2, 200); + +# The recursive term produces a second row with id=1 (val=300). Without the +# FD fix, Filter(nodes.id = 1) would be deemed scalar and return only the +# first matching row. +query II rowsort +WITH RECURSIVE nodes AS ( +SELECT id, val FROM pk_table +UNION ALL +SELECT 1 AS id, 300 AS val +FROM nodes +WHERE nodes.id = 2 +) +SELECT id, val FROM nodes WHERE id = 1 + +1 100 +1 300 Review Comment: Thank you for pointing that out. I’ve gone through it again and added a new test case. Could you please take another look? Thanks! -- 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]
Re: [PR] fix: UNIQUE constraint with NULLs incorrectly collapses GROUP BY groups [datafusion]
nuno-faria commented on code in PR #21715: URL: https://github.com/apache/datafusion/pull/21715#discussion_r321478 ## datafusion/sqllogictest/test_files/cte.slt: ## @@ -1319,3 +1319,34 @@ RESET datafusion.execution.enable_recursive_ctes; statement ok RESET datafusion.sql_parser.enable_ident_normalization; + +# Regression test: functional dependencies from the static (anchor) term of a +# recursive CTE must NOT be propagated to the outer SubqueryAlias. The +# recursive term can produce rows that violate any uniqueness constraint that +# holds for the anchor alone. Without this guard, Filter(pk = const) on the +# CTE result would be mis-identified as scalar (at most 1 row) and return only +# one row instead of all matching rows. +statement ok +CREATE TABLE pk_table(id INT NOT NULL, val INT NOT NULL, PRIMARY KEY(id)); + +statement ok +INSERT INTO pk_table VALUES (1, 100), (2, 200); + +# The recursive term produces a second row with id=1 (val=300). Without the +# FD fix, Filter(nodes.id = 1) would be deemed scalar and return only the +# first matching row. +query II rowsort +WITH RECURSIVE nodes AS ( +SELECT id, val FROM pk_table +UNION ALL +SELECT 1 AS id, 300 AS val +FROM nodes +WHERE nodes.id = 2 +) +SELECT id, val FROM nodes WHERE id = 1 + +1 100 +1 300 Review Comment: > When the outer query applied `WHERE id = 1`, the optimizer deemed it a scalar lookup (at most one row) and short-circuited execution — returning only `(1, 100)` and silently dropping `(1, 300)` produced by the recursive term. I don't think the `FilterExec` works like this. I tested that query without the fix and it returned the correct results. -- 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]
Re: [PR] fix: UNIQUE constraint with NULLs incorrectly collapses GROUP BY groups [datafusion]
xiedeyantu commented on code in PR #21715: URL: https://github.com/apache/datafusion/pull/21715#discussion_r3213225589 ## datafusion/sqllogictest/test_files/cte.slt: ## @@ -1319,3 +1319,34 @@ RESET datafusion.execution.enable_recursive_ctes; statement ok RESET datafusion.sql_parser.enable_ident_normalization; + +# Regression test: functional dependencies from the static (anchor) term of a +# recursive CTE must NOT be propagated to the outer SubqueryAlias. The +# recursive term can produce rows that violate any uniqueness constraint that +# holds for the anchor alone. Without this guard, Filter(pk = const) on the +# CTE result would be mis-identified as scalar (at most 1 row) and return only +# one row instead of all matching rows. +statement ok +CREATE TABLE pk_table(id INT NOT NULL, val INT NOT NULL, PRIMARY KEY(id)); + +statement ok +INSERT INTO pk_table VALUES (1, 100), (2, 200); + +# The recursive term produces a second row with id=1 (val=300). Without the +# FD fix, Filter(nodes.id = 1) would be deemed scalar and return only the +# first matching row. +query II rowsort +WITH RECURSIVE nodes AS ( +SELECT id, val FROM pk_table +UNION ALL +SELECT 1 AS id, 300 AS val +FROM nodes +WHERE nodes.id = 2 +) +SELECT id, val FROM nodes WHERE id = 1 + +1 100 +1 300 Review Comment: Done! -- 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]
Re: [PR] fix: UNIQUE constraint with NULLs incorrectly collapses GROUP BY groups [datafusion]
xiedeyantu commented on code in PR #21715: URL: https://github.com/apache/datafusion/pull/21715#discussion_r3214106649 ## datafusion/sqllogictest/test_files/cte.slt: ## @@ -1319,3 +1319,34 @@ RESET datafusion.execution.enable_recursive_ctes; statement ok RESET datafusion.sql_parser.enable_ident_normalization; + +# Regression test: functional dependencies from the static (anchor) term of a +# recursive CTE must NOT be propagated to the outer SubqueryAlias. The +# recursive term can produce rows that violate any uniqueness constraint that +# holds for the anchor alone. Without this guard, Filter(pk = const) on the +# CTE result would be mis-identified as scalar (at most 1 row) and return only +# one row instead of all matching rows. +statement ok +CREATE TABLE pk_table(id INT NOT NULL, val INT NOT NULL, PRIMARY KEY(id)); + +statement ok +INSERT INTO pk_table VALUES (1, 100), (2, 200); + +# The recursive term produces a second row with id=1 (val=300). Without the +# FD fix, Filter(nodes.id = 1) would be deemed scalar and return only the +# first matching row. +query II rowsort +WITH RECURSIVE nodes AS ( +SELECT id, val FROM pk_table +UNION ALL +SELECT 1 AS id, 300 AS val +FROM nodes +WHERE nodes.id = 2 +) +SELECT id, val FROM nodes WHERE id = 1 + +1 100 +1 300 Review Comment: The anchor term `SELECT id, val FROM pk_table` carries a primary-key uniqueness constraint on `id`. The optimizer incorrectly propagated that constraint onto the outer `SubqueryAlias: nodes`, treating the entire recursive CTE result as if `id` were still unique. When the outer query applied `WHERE id = 1`, the optimizer deemed it a scalar lookup (at most one row) and short-circuited execution — returning only `(1, 100)` and silently dropping `(1, 300)` produced by the recursive term. The fix ensures that functional dependencies from the anchor are **not** forwarded past the `RecursiveQuery` node, since recursive terms can freely produce duplicate values for any column that was unique in the anchor alone. -- 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]
Re: [PR] fix: UNIQUE constraint with NULLs incorrectly collapses GROUP BY groups [datafusion]
xiedeyantu commented on code in PR #21715:
URL: https://github.com/apache/datafusion/pull/21715#discussion_r3213225974
##
datafusion/common/src/functional_dependencies.rs:
##
@@ -196,15 +196,25 @@ impl FunctionalDependencies {
}
/// Creates a new `FunctionalDependencies` object from the given
constraints.
+///
+/// `nullable_flags` must contain one entry per field in the relation,
+/// indicating whether that field is nullable. A `UNIQUE` constraint whose
+/// source columns include any nullable field is **not** a functional
+/// dependency — because SQL treats `NULL` values as distinct, multiple
rows
+/// may carry `NULL` in a unique-key column without violating the
constraint.
+/// Such constraints are therefore omitted entirely. When all source
columns
+/// are non-nullable a `UNIQUE` constraint is equivalent to a primary key
and
+/// is recorded with `nullable = false`.
pub fn new_from_constraints(
constraints: Option<&Constraints>,
-n_field: usize,
+nullable_flags: &[bool],
) -> Self {
Review Comment:
Added!
--
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]
Re: [PR] fix: UNIQUE constraint with NULLs incorrectly collapses GROUP BY groups [datafusion]
xiedeyantu commented on code in PR #21715: URL: https://github.com/apache/datafusion/pull/21715#discussion_r3213225589 ## datafusion/sqllogictest/test_files/cte.slt: ## @@ -1319,3 +1319,34 @@ RESET datafusion.execution.enable_recursive_ctes; statement ok RESET datafusion.sql_parser.enable_ident_normalization; + +# Regression test: functional dependencies from the static (anchor) term of a +# recursive CTE must NOT be propagated to the outer SubqueryAlias. The +# recursive term can produce rows that violate any uniqueness constraint that +# holds for the anchor alone. Without this guard, Filter(pk = const) on the +# CTE result would be mis-identified as scalar (at most 1 row) and return only +# one row instead of all matching rows. +statement ok +CREATE TABLE pk_table(id INT NOT NULL, val INT NOT NULL, PRIMARY KEY(id)); + +statement ok +INSERT INTO pk_table VALUES (1, 100), (2, 200); + +# The recursive term produces a second row with id=1 (val=300). Without the +# FD fix, Filter(nodes.id = 1) would be deemed scalar and return only the +# first matching row. +query II rowsort +WITH RECURSIVE nodes AS ( +SELECT id, val FROM pk_table +UNION ALL +SELECT 1 AS id, 300 AS val +FROM nodes +WHERE nodes.id = 2 +) +SELECT id, val FROM nodes WHERE id = 1 + +1 100 +1 300 Review Comment: Done! -- 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]
Re: [PR] fix: UNIQUE constraint with NULLs incorrectly collapses GROUP BY groups [datafusion]
nuno-faria commented on code in PR #21715:
URL: https://github.com/apache/datafusion/pull/21715#discussion_r3213121882
##
datafusion/sqllogictest/test_files/cte.slt:
##
@@ -1319,3 +1319,34 @@ RESET datafusion.execution.enable_recursive_ctes;
statement ok
RESET datafusion.sql_parser.enable_ident_normalization;
+
+# Regression test: functional dependencies from the static (anchor) term of a
+# recursive CTE must NOT be propagated to the outer SubqueryAlias. The
+# recursive term can produce rows that violate any uniqueness constraint that
+# holds for the anchor alone. Without this guard, Filter(pk = const) on the
+# CTE result would be mis-identified as scalar (at most 1 row) and return only
+# one row instead of all matching rows.
+statement ok
+CREATE TABLE pk_table(id INT NOT NULL, val INT NOT NULL, PRIMARY KEY(id));
+
+statement ok
+INSERT INTO pk_table VALUES (1, 100), (2, 200);
+
+# The recursive term produces a second row with id=1 (val=300). Without the
+# FD fix, Filter(nodes.id = 1) would be deemed scalar and return only the
+# first matching row.
+query II rowsort
+WITH RECURSIVE nodes AS (
+SELECT id, val FROM pk_table
+UNION ALL
+SELECT 1 AS id, 300 AS val
+FROM nodes
+WHERE nodes.id = 2
+)
+SELECT id, val FROM nodes WHERE id = 1
+
+1 100
+1 300
Review Comment:
I'm not sure the filter will test the bug here. Shouldn't it be a group by
like `count(*) from nodes group by id, val`?
##
datafusion/common/src/functional_dependencies.rs:
##
@@ -196,15 +196,25 @@ impl FunctionalDependencies {
}
/// Creates a new `FunctionalDependencies` object from the given
constraints.
+///
+/// `nullable_flags` must contain one entry per field in the relation,
+/// indicating whether that field is nullable. A `UNIQUE` constraint whose
+/// source columns include any nullable field is **not** a functional
+/// dependency — because SQL treats `NULL` values as distinct, multiple
rows
+/// may carry `NULL` in a unique-key column without violating the
constraint.
+/// Such constraints are therefore omitted entirely. When all source
columns
+/// are non-nullable a `UNIQUE` constraint is equivalent to a primary key
and
+/// is recorded with `nullable = false`.
pub fn new_from_constraints(
constraints: Option<&Constraints>,
-n_field: usize,
+nullable_flags: &[bool],
) -> Self {
Review Comment:
Since the signature of this pub function has been changed, I think we should
add an entry to the upgrade guide
(docs/source/library-user-guide/upgrading/54.0.0.md).
--
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]
Re: [PR] fix: UNIQUE constraint with NULLs incorrectly collapses GROUP BY groups [datafusion]
xiedeyantu commented on code in PR #21715:
URL: https://github.com/apache/datafusion/pull/21715#discussion_r3189372323
##
datafusion/expr/src/logical_plan/plan.rs:
##
@@ -2431,7 +2434,14 @@ impl SubqueryAlias {
// Requalify fields with the new `alias`.
let fields = plan.schema().fields().clone();
let meta_data = plan.schema().metadata().clone();
-let func_dependencies =
plan.schema().functional_dependencies().clone();
+// Recursive queries do not expose the anchor's functional
dependencies to
+// the outer schema — the recursive term can produce rows that violate
+// those dependencies, so they are intentionally dropped here.
+let func_dependencies = if is_recursive_query {
+FunctionalDependencies::empty()
+} else {
+plan.schema().functional_dependencies().clone()
+};
Review Comment:
Sure, I have added a test.
--
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]
Re: [PR] fix: UNIQUE constraint with NULLs incorrectly collapses GROUP BY groups [datafusion]
xiedeyantu commented on PR #21715: URL: https://github.com/apache/datafusion/pull/21715#issuecomment-4380411593 > What about taking a different approach here: I'd argue a UNIQUE constraint on a nullable column should not be an FD in the first place. Rather than producing such constraints and then filtering them out in the various places that use the FDs, why not change `new_from_constraints` to not return an FD in this circumstance to begin with? I've tried making the modifications based on your suggestions; please check if they meet your requirements. -- 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]
Re: [PR] fix: UNIQUE constraint with NULLs incorrectly collapses GROUP BY groups [datafusion]
neilconway commented on code in PR #21715: URL: https://github.com/apache/datafusion/pull/21715#discussion_r3124192712 ## datafusion/sqllogictest/test_files/group_by.slt: ## @@ -5641,3 +5635,32 @@ set datafusion.execution.target_partitions = 4; statement count 0 drop table t; + +# Test that GROUP BY with a UNIQUE constraint does not incorrectly collapse +# NULL rows. UNIQUE allows multiple NULLs (NULLs are not equal in SQL), so +# a UNIQUE column cannot be used to eliminate other GROUP BY columns. +# Regression test for https://github.com/apache/datafusion/issues/21507 + +statement ok +CREATE TABLE t_unique_null(a INT, b INT, c INT, UNIQUE(a)); + +statement ok +INSERT INTO t_unique_null VALUES (1, 10, 100), (NULL, 20, 200), (NULL, 30, 300); + +# The two NULL rows must stay in separate groups (grouped by b as well). +query II rowsort +SELECT a, SUM(c) AS total FROM t_unique_null GROUP BY a, b; + +1 100 +NULL 200 +NULL 300 + +# GROUP BY on the UNIQUE column alone must still merge the NULL rows into one group. Review Comment: Can we add a test case for the `ORDER BY` case as well? #21362 -- 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]
Re: [PR] fix: UNIQUE constraint with NULLs incorrectly collapses GROUP BY groups [datafusion]
nuno-faria commented on code in PR #21715:
URL: https://github.com/apache/datafusion/pull/21715#discussion_r3115841265
##
datafusion/sqllogictest/test_files/group_by.slt:
##
@@ -3565,33 +3565,27 @@ SELECT r.sn, r.amount, SUM(r.amount)
GROUP BY r.sn
ORDER BY r.sn
-# left semi join should propagate constraint of left side as is.
-query IRR
+# left semi join with a nullable UNIQUE key cannot safely propagate the
+# constraint for expansion, because UNIQUE allows multiple NULLs.
+statement error DataFusion error: Error during planning: Column in SELECT must
be in GROUP BY or an aggregate function: While expanding wildcard, column
"l\.amount" must appear in the GROUP BY clause or must be part of an aggregate
function, currently only "l\.sn, sum\(l\.amount\)" appears in the SELECT clause
satisfies this requirement
SELECT l.sn, l.amount, SUM(l.amount)
FROM (SELECT *
FROM sales_global_with_unique as l
LEFT SEMI JOIN sales_global_with_unique as r
ON l.amount >= r.amount + 10)
GROUP BY l.sn
ORDER BY l.sn
-
-1 50 50
-2 75 75
-3 200 200
-4 100 100
-NULL 100 100
-# Similarly, left anti join should propagate constraint of left side as is.
-query IRR
+# Similarly, left anti join with a nullable UNIQUE key cannot safely propagate
+# the constraint for expansion.
+statement error DataFusion error: Error during planning: Column in SELECT must
be in GROUP BY or an aggregate function: While expanding wildcard, column
"l\.amount" must appear in the GROUP BY clause or must be part of an aggregate
function, currently only "l\.sn, sum\(l\.amount\)" appears in the SELECT clause
satisfies this requirement
SELECT l.sn, l.amount, SUM(l.amount)
FROM (SELECT *
FROM sales_global_with_unique as l
LEFT ANTI JOIN sales_global_with_unique as r
ON l.amount >= r.amount + 10)
GROUP BY l.sn
ORDER BY l.sn
Review Comment:
My guess is that these tests change due to not calling
`with_add_implicit_group_by_exprs` anymore. I think this is ok, and will now be
close to Postgres and DuckDB. But it's probably best to add it to the upgrade
guide.
##
datafusion/expr/src/logical_plan/plan.rs:
##
@@ -2431,7 +2434,14 @@ impl SubqueryAlias {
// Requalify fields with the new `alias`.
let fields = plan.schema().fields().clone();
let meta_data = plan.schema().metadata().clone();
-let func_dependencies =
plan.schema().functional_dependencies().clone();
+// Recursive queries do not expose the anchor's functional
dependencies to
+// the outer schema — the recursive term can produce rows that violate
+// those dependencies, so they are intentionally dropped here.
+let func_dependencies = if is_recursive_query {
+FunctionalDependencies::empty()
+} else {
+plan.schema().functional_dependencies().clone()
+};
Review Comment:
It's not clear to me why is this change necessary for recursive queries?
Could we add a test case for this?
##
datafusion/sql/src/select.rs:
##
@@ -966,12 +968,13 @@ impl SqlToRel<'_, S> {
group_by_exprs: &[Expr],
aggr_exprs: &[Expr],
) -> Result {
+let group_by_exprs =
+self.add_required_group_by_exprs_from_dependencies(input,
group_by_exprs)?;
+
// create the aggregate plan
-let options =
-
LogicalPlanBuilderOptions::new().with_add_implicit_group_by_exprs(true);
let plan = LogicalPlanBuilder::from(input.clone())
-.with_options(options)
-.aggregate(group_by_exprs.to_vec(), aggr_exprs.to_vec())?
Review Comment:
I think not adding `with_add_implicit_group_by_exprs` anymore will change
existing queries. Maybe we should add this to the upgrade guide.
--
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]
Re: [PR] fix: UNIQUE constraint with NULLs incorrectly collapses GROUP BY groups [datafusion]
xiedeyantu commented on PR #21715: URL: https://github.com/apache/datafusion/pull/21715#issuecomment-4276120499 > Thanks @xiedeyantu. While this fixes the original issue, it now treats all nulls as different when aggregating, which I don't think is correct. For example: > > ```sql > CREATE TABLE t(a INT, b INT, c INT, UNIQUE(a)); > INSERT INTO t VALUES (1, 10, 100), (NULL, 20, 200), (NULL, 30, 300); > SELECT a, SUM(c) AS total FROM t GROUP BY a; > +--+---+ > | a| total | > +--+---+ > | 1| 100 | > | NULL | 200 | > | NULL | 300 | > +--+---+ > ``` @nuno-faria Thank you for your comprehensive analysis. I have refactored the code and added the test cases you provided; please review it again. -- 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]
