dongjoon-hyun commented on code in PR #59965: URL: https://github.com/apache/spark/pull/59965#discussion_r4447198150
########## docs/sql-migration-guide.md: ########## @@ -70,6 +70,8 @@ license: | - Since Spark 4.4, `CREATE TABLE`, `REPLACE TABLE`, and `ALTER TABLE ... ADD CONSTRAINT` on a Data Source V2 table fail with `USER_DEFINED_FUNCTION_IN_CHECK_CONSTRAINT` when the condition of a CHECK constraint calls a user-defined function, including a Data Source V2 function. `ALTER TABLE ... ADD CONSTRAINT` keeps failing with `UNSUPPORTED_SQL_UDF_USAGE` on a SQL function and with `INVALID_WHERE_CONDITION` on an aggregate function. Previously, a write could resolve the function name in its own session, so that it enforced another rule or failed (SPARK-60777). To run these statements, change the condition to call only built-in functions. Existing constraints that call a user-defined function are not rejected. - Since Spark 4.4, a write to a Data Source V2 table that parses the SQL text of a CHECK constraint binds an unqualified built-in function name to `system.builtin`, regardless of the SQL path and `spark.sql.functionResolution.sessionOrder` (SPARK-60777). Previously, an existing constraint could call another function with the same name. - Since Spark 4.4, `CREATE TABLE`, `REPLACE TABLE`, and `ALTER TABLE ... ADD CONSTRAINT` on a Data Source V2 table fail with `SUBQUERY_IN_CHECK_CONSTRAINT` when the condition of a CHECK constraint contains a subquery. `ALTER TABLE ... ADD CONSTRAINT` keeps failing with `UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.HIGHER_ORDER_FUNCTION` on a subquery in a lambda function. Previously, most of these statements failed with `INTERNAL_ERROR`, but a subquery that needed no resolution, such as `id > (SELECT 0 AS a)`, was accepted and every write evaluated it again (SPARK-60781). Existing constraints that contain a subquery are not rejected. +- Since Spark 4.4, a write to a Data Source V2 table parses and analyzes the stored text of a generation expression with ANSI mode on and the default values of the SQL configs that change how a text reads, such as `spark.sql.parser.escapedStringLiterals`, regardless of the session of the write. Only the following still follow that session: the session time zone, case sensitivity, `spark.sql.timestampType`, `spark.sql.decimalOperations.allowPrecisionLoss`, the configs that a view also takes from the session that reads it, the functions found through `SET PATH`, and the configs that an expression reads during evaluation, such as `spark.sql.legacy.timeParserPolicy`. A text that does not parse or resolve under these settings, such as `flag = 1` on a `BOOLEAN` column, is read under that session. Previously, the session of the write decided how the text read, so a write could silently store a wrong value (SPARK-60757). As a result, with `spark.sql.ansi.enabled=false`, also for a table cre ated with that setting, a write can fail with an error such as `CAST_INVALID_INPUT` where Spark 4.3 stored `NULL`, and an implicit cast can change the stored value. A table created with a non-default value of a config such as `spark.sql.parser.escapedStringLiterals` also gets the default reading of its text on every write, without an error. A row that Spark 4.3 stored differently can fail with such an error or with `CHECK_CONSTRAINT_VIOLATION` when a later write provides it again, such as an `INSERT ... SELECT` from the same table. A `DELETE` does not check the rows that it carries. To fix such a table, create a new table with a text that reads as intended under these settings, and copy the other columns into it with `INSERT ... SELECT`. Review Comment: Applied your wording in e4311984c08. -- 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]
