Yang Jie created SPARK-60453:
--------------------------------
Summary: CHECK constraints checked through predicateSql use the
writer's session settings and stay VALID over time
Key: SPARK-60453
URL: https://issues.apache.org/jira/browse/SPARK-60453
Project: Spark
Issue Type: Improvement
Components: SQL
Affects Versions: 5.0.0
Reporter: Yang Jie
A CHECK constraint stored without a predicate is checked on every write by
parsing predicateSql against the write query (ResolveTableConstraints). CREATE
TABLE stores such constraints when the predicate cannot be translated to V2,
and after SPARK-60032 ALTER TABLE also stores them for a condition that reads
the current time, date, catalog, user and the like. Two things follow.
1. The whole condition is parsed and resolved under the writer's session
settings, not the ones it was added with. For example, CHECK ("created" <=
current_timestamp()) added under ANSI mode with
spark.sql.ansi.doubleQuotedIdentifiers=true refers to the column, but a writer
with the default settings parses "created" as a string, and every write fails
with CAST_INVALID_INPUT. Time zone and ANSI settings can change literals and
casts in the rest of the condition the same way. Views avoid this by storing
the confs they were created with.
2. A condition that a row satisfies today and fails later, such as d >=
current_date() or db = current_database(), is still stored as VALID, which
Check defines as "the existing data is guaranteed to satisfy the constraint".
Row-level operations that copy unchanged rows run them through the same check:
on a copy-on-write table with rows (1, 'ns') and (2, 'ns') and CHECK (db =
current_database()) added in namespace ns, DELETE FROM t WHERE id > 1 run in
namespace ns2 fails with CHECK_CONSTRAINT_VIOLATION on the copied row 1, and so
does UPDATE t SET db = 'ns2' WHERE id = 2.
Questions to settle: whether to store the confs with the constraint, whether
such a constraint should be VALID, and whether copied rows need checking.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
