dongjoon-hyun opened a new pull request, #59999: URL: https://github.com/apache/spark/pull/59999
### What changes were proposed in this pull request? This PR rejects a CHECK constraint whose condition contains a subquery with a new error condition, `SUBQUERY_IN_CHECK_CONSTRAINT` (SQLSTATE `42621`), in `ALTER TABLE ... ADD CONSTRAINT`, `CREATE TABLE`, and `REPLACE TABLE`. - `CheckConstraint.failIfContainsSubquery` raises the error. - `ResolveTableSpec` calls it for `CREATE TABLE` and `REPLACE TABLE` before its `childrenResolved` check. - `CheckAnalysis` calls it for `ALTER TABLE ... ADD CONSTRAINT` in the first top-down traversal, because the parser also puts the condition in the child `Filter` of the command, and the bottom-up traversal visits that `Filter` first. The write path is not changed, so a constraint that is already stored is enforced as before. ### Why are the changes needed? The Javadoc of `Check` says that the search condition cannot contain subqueries, but the analyzer does not enforce it. `ResolveSubquery` does not visit these commands, so the result depends on the shape of the subquery. The following results are from a V2 catalog, and `REPLACE TABLE` takes the same path as `CREATE TABLE`. | Condition | `ALTER TABLE` | `CREATE TABLE` | |---|---|---| | `id > (SELECT 0)` | `INTERNAL_ERROR` | `INTERNAL_ERROR` | | `EXISTS (SELECT 1)` | `INTERNAL_ERROR` | `INTERNAL_ERROR` | | `id IN (SELECT 5)` | `INTERNAL_ERROR` | `INTERNAL_ERROR` | | `EXISTS (SELECT 1 FROM t2)` where `t2` exists | `TABLE_OR_VIEW_NOT_FOUND` | `TABLE_OR_VIEW_NOT_FOUND` | | `id > (SELECT 0 AS a)` | Accepted | Accepted | | `EXISTS (SELECT 1 AS a)` | `UNSUPPORTED_IN_EXISTS_SUBQUERY` | Accepted | The internal error is `Found the unresolved operator: 'Project [unresolvedalias(0)]`. The accepted conditions contain a subquery that is resolved when it is parsed, so it passes the analysis and the table stores the condition. Every later write evaluates that subquery. ### Does this PR introduce _any_ user-facing change? Yes. A CHECK condition with a subquery now fails with the following error instead of the results above. ``` [SUBQUERY_IN_CHECK_CONSTRAINT] The check constraint `id > (SELECT 0)` contains a subquery. Check constraints cannot contain subqueries. SQLSTATE: 42621 ``` - DDL that used to succeed, such as `CHECK (id > (SELECT 0 AS a))`, now fails. This is a change from the released versions that have this code path, from 4.1.0. Only `master` was run. - This error is now reported ahead of other errors of the same statement, such as an unresolved column in the condition. The same applies to a missing table in `ALTER TABLE`. ### How was this patch tested? Added two tests to `CheckConstraintSuite`. They fail with `INTERNAL_ERROR` without the fix. ``` build/sbt 'sql/testOnly *command.v2.CheckConstraintSuite' build/sbt 'core/testOnly *SparkThrowableSuite' ``` ### Was this patch authored or co-authored using generative AI tooling? Generated-by: Claude Opus 5.5 -- 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]
