You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "findepi (via GitHub)" <gi...@apache.org> on 2025/05/21 11:37:47 UTC
[I] Scalar subquery (`Expr::ScalarSubquery`) does not fail when subquery is not scalar [datafusion]
findepi opened a new issue, #16134:
URL: https://github.com/apache/datafusion/issues/16134
### Describe the bug
When subquery is used in expression context, it must be a scalar subquery, i.e. it must produce a singe column and _at most_ one row.
### To Reproduce
```
SELECT
s.*,
-- not a scalar subquery
(SELECT * FROM (VALUES (1), (2)) t(x)) sub
FROM (VALUES ('a'), ('b')) s(a);
```
```
+---+-----+
| a | sub |
+---+-----+
| a | 1 |
| a | 2 |
| b | 1 |
| b | 2 |
+---+-----+
```
### Expected behavior
an error like the one produced by e.g. Snowflake "Single-row subquery returns more than one row."
### Additional context
_No response_
--
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: github-unsubscribe@datafusion.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Scalar subquery (`Expr::ScalarSubquery`) does not fail when subquery is not scalar [datafusion]
Posted by "u70b3 (via GitHub)" <gi...@apache.org>.
u70b3 commented on issue #16134:
URL: https://github.com/apache/datafusion/issues/16134#issuecomment-5019659970
Verified on current main (1e589287c): this no longer reproduces. The repro query now fails with a proper execution error instead of silently producing a cross product:
```
SELECT s.*, (SELECT * FROM (VALUES (1), (2)) t(x)) sub FROM (VALUES ('a'), ('b')) s(a);
-- Execution error: Scalar subquery returned more than one row
```
The check is enforced by `ScalarSubqueryExec` since #22530. Suggest closing.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Scalar subquery (`Expr::ScalarSubquery`) does not fail when subquery is not scalar [datafusion]
Posted by "irenjj (via GitHub)" <gi...@apache.org>.
irenjj commented on issue #16134:
URL: https://github.com/apache/datafusion/issues/16134#issuecomment-2897724592
take
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Scalar subquery (`Expr::ScalarSubquery`) does not fail when subquery is not scalar [datafusion]
Posted by "kakiuwang-ui (via GitHub)" <gi...@apache.org>.
kakiuwang-ui commented on issue #16134:
URL: https://github.com/apache/datafusion/issues/16134#issuecomment-5664371906
Correcting myself: I was wrong to say this should stay open. The remaining case is documented and deliberate, and I should have read the option's own documentation before posting.
`enable_physical_uncorrelated_scalar_subquery`'s config description already states that turning it off "restores pre #21240 behavior, which silently produces incorrect results for multi-row subqueries", and the slt section covering that path says the same in more detail — "multi-row subqueries silently return wrong results ... Those cases are intentionally not covered here". So it is an accepted limitation of an escape hatch that is itself slated for removal, not an oversight.
The facts in my previous comment still hold — the query does reproduce with the option off, by the mechanism I described — but the conclusion does not. @buraksenn and @u70b3 are right: the bug this issue reports is fixed on the supported path, and closing is appropriate. Sorry for the noise.
The one thing that might still be worth a line: the existing coverage pins the cases where both paths agree, so nothing currently fails if the default path ever stops raising the cardinality error. `subquery.slt` does already assert it for SELECT, WHERE and function-argument positions, so that is covered — no action needed unless you want the option-off path pinned to its documented-wrong behaviour on purpose, which I would not recommend.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Scalar subquery (`Expr::ScalarSubquery`) does not fail when subquery is not scalar [datafusion]
Posted by "buraksenn (via GitHub)" <gi...@apache.org>.
buraksenn commented on issue #16134:
URL: https://github.com/apache/datafusion/issues/16134#issuecomment-4914825174
From what I can see this was fixed by #21240.
```
➜ datafusion git:(main) cargo run --bin datafusion-cli -p datafusion-cli
Finished [`dev` profile [unoptimized + debuginfo]](https://doc.rust-lang.org/cargo/reference/profiles.html#default-profiles) target(s) in 5.44s
Running `target/debug/datafusion-cli`
DataFusion CLI v54.0.0
> SELECT s.*, (SELECT * FROM (VALUES (1),(2)) t(x)) sub FROM (VALUES ('a'),('b')) s(a);
Execution error: Scalar subquery returned more than one row
> \?
```
If datafusion.optimizer.enable_physical_uncorrelated_scalar_subquery flag is set to `false` but that is not recommended by the docs.
I think there are two actions:
- Close this issue
- Add a regression test for this exact query to subquery.slt, then close.
WDYT?
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Scalar subquery (`Expr::ScalarSubquery`) does not fail when subquery is not scalar [datafusion]
Posted by "kakiuwang-ui (via GitHub)" <gi...@apache.org>.
kakiuwang-ui commented on issue #16134:
URL: https://github.com/apache/datafusion/issues/16134#issuecomment-5664333402
Please hold off on closing this — it is only half fixed. The physical-side check added in #21240 / #22530 covers the default configuration, but the original behaviour is still reachable.
Reproduced on released 55.1.0:
```
$ datafusion-cli
> SELECT s.*, (SELECT * FROM (VALUES (1), (2)) t(x)) sub FROM (VALUES ('a'), ('b')) s(a);
Execution error: Scalar subquery returned more than one row -- correct
> set datafusion.optimizer.enable_physical_uncorrelated_scalar_subquery = false;
> SELECT s.*, (SELECT * FROM (VALUES (1), (2)) t(x)) sub FROM (VALUES ('a'), ('b')) s(a);
+---+-----+
| a | sub |
+---+-----+
| a | 1 |
| b | 1 |
| a | 2 |
| b | 2 |
+---+-----+
4 row(s) fetched. -- the bug this issue reports
```
The code path is unchanged on current main (7b00b63).
**Why:** the cardinality check lives only in `ScalarSubqueryExec` (`physical-plan/src/scalar_subquery.rs`). `create_initial_plan` deliberately skips `collect_scalar_subqueries` when the option is off, on the assumption that `ScalarSubqueryToJoin` rewrote every uncorrelated scalar subquery into a join, so that operator is never created. `build_join` then rewrites the subquery as `Left Join: Filter: Boolean(true)`, and its "at most one row" assumption — stated in the comment there — only actually holds for an aggregate with no grouping. For a subquery like `SELECT * FROM (VALUES (1), (2))` the join simply multiplies the outer rows.
**Proposed fix**, unless you would rather it went another way: only decorrelate an uncorrelated scalar subquery when `subquery.max_rows()` is `Some(n)` with `n <= 1` — which is exactly the aggregate-without-grouping case the existing comment relies on — and let anything else fall through to the physical path so `ScalarSubqueryExec` raises the same error the default configuration gives. That means `create_initial_plan` should collect scalar subqueries regardless of the option; the current skip is only an optimisation, since the collection returns nothing when the rule did rewrite them all.
The alternative would be to reject such subqueries at plan time rather than at execution time, which would be a different error message from the default path. I would rather match the default path, but say the word if you prefer otherwise.
I would like to work on this, along with slt coverage for both settings of the option. @buraksenn @u70b3 — flagging since you both looked at this.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Scalar subquery (`Expr::ScalarSubquery`) does not fail when subquery is not scalar [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb closed issue #16134: Scalar subquery (`Expr::ScalarSubquery`) does not fail when subquery is not scalar
URL: https://github.com/apache/datafusion/issues/16134
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org