dongjoon-hyun opened a new pull request, #60005:
URL: https://github.com/apache/spark/pull/60005
### What changes were proposed in this pull request?
This PR makes `CREATE TABLE` and `REPLACE TABLE` on a Data Source V2 table
reject a CHECK constraint whose condition calls an aggregate or window
function. They fail with the new error condition
`AGGREGATE_OR_WINDOW_FUNCTION_IN_CHECK_CONSTRAINT`, and the query context of
the error points at the call.
1. `ResolveTableSpec` looks for these expressions in the resolved condition:
- `AggregateExpression`: built-in and user-defined aggregate functions,
including Hive and V2 aggregate functions.
- `Grouping` and `GroupingID`: the aggregate functions `grouping` and
`grouping_id`.
- `WindowExpression`: any function with an `OVER` clause.
- `WindowFunction`: a window function without an `OVER` clause, such as
`row_number()`.
2. `ResolveTableSpec` does this before
`CheckConstraint.failIfCallsUserDefinedFunction`, so a user-defined aggregate
function fails like a built-in one. The `V2Aggregator` branch of that method is
removed because `CREATE TABLE` and `REPLACE TABLE` now fail earlier, and `ALTER
TABLE` already fails at its `Filter`.
3. The SQL migration guide describes this change.
`ALTER TABLE ... ADD CONSTRAINT` already rejects these conditions, and this
PR does not change its errors. The check is in the DDL only, so a write to a
table with an existing constraint fails as before.
The errors of `ALTER TABLE` are not reused because they describe a `WHERE`
clause that the statement does not have, and the first one shows the internal
form of the condition:
```
[INVALID_WHERE_CONDITION] The WHERE condition "(NOT (max(id) > 0))" contains
invalid expressions: max(cat.ns.t.id).
[WINDOW_FUNCTION_NOT_ALLOWED_IN_CLAUSE] It is not allowed to use window
functions inside WHERE clause.
```
### Why are the changes needed?
The `Check` Javadoc says that a condition cannot contain aggregates, but
`CREATE TABLE` and `REPLACE TABLE` accept them. No write to the table can
succeed after that. A window function fails in the same way:
```sql
CREATE TABLE cat.ns.t1 (id INT, CONSTRAINT c1 CHECK (max(id) > 0)) USING
foo; -- succeeds
INSERT INTO cat.ns.t1 VALUES (1); -- INVALID_WHERE_CONDITION
CREATE TABLE cat.ns.t2 (id INT, CONSTRAINT c1 CHECK (row_number() OVER
(ORDER BY id) > 0)) USING foo; -- succeeds
INSERT INTO cat.ns.t2 VALUES (1); -- WINDOW_FUNCTION_NOT_ALLOWED_IN_CLAUSE
```
https://github.com/apache/spark/pull/59992 rejected user-defined functions
in these statements, but not built-in aggregate and window functions.
### Does this PR introduce _any_ user-facing change?
Yes.
- Before, `CREATE TABLE` and `REPLACE TABLE` accepted these conditions, and
every later write to the table failed. After, the statements fail:
```
[AGGREGATE_OR_WINDOW_FUNCTION_IN_CHECK_CONSTRAINT] The check constraint
`max(id) > 0` calls an aggregate or window function. Check constraints apply to
each row and cannot call these functions. SQLSTATE: 42621
```
- Compared to the unreleased master branch, a user-defined aggregate
function in `CREATE TABLE` and `REPLACE TABLE` fails with this error instead of
`USER_DEFINED_FUNCTION_IN_CHECK_CONSTRAINT`.
- `CREATE TABLE` on a V1 table with such a condition fails with this error
instead of `UNSUPPORTED_FEATURE.TABLE_OPERATION`.
Existing constraints that call these functions are not rejected, and a write
to their tables keeps failing with the same error. Released Spark versions
accept these conditions since Apache Spark 4.1.0.
### How was this patch tested?
Pass the CIs with the newly added test case in `CheckConstraintSuite`. It
covers `CREATE TABLE` and `REPLACE TABLE` for a built-in aggregate function,
`grouping`, `grouping_id`, a window function with and without an `OVER` clause,
a function that is not a window function with an `OVER` clause, a Scala
aggregate function, and a V2 aggregate function.
### 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]
