Yang Jie created SPARK-61194:
--------------------------------
Summary: CHECK constraints added by ALTER TABLE still freeze the
current time and the like when the condition also has a part that a write reads
differently
Key: SPARK-61194
URL: https://issues.apache.org/jira/browse/SPARK-61194
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 5.0.0
Reporter: Yang Jie
After SPARK-60032, ALTER TABLE ... ADD CONSTRAINT ... CHECK stores a condition
that reads the current time, date, catalog, user and the like without a V2
predicate, so each write evaluates it. A condition that also has a part that a
write would resolve differently keeps the predicate built from the optimized
condition, where those values are literals of the time of the ALTER TABLE:
- a name such as CURRENT_DATE, CURRENT_TIMESTAMP or user without parentheses,
which a write can resolve to a column added later or read under other reserved
keywords (SPARK-60787);
- a name with more than one part, since a write does not resolve a column
qualified by the table name the way ALTER TABLE does (SPARK-60286);
- a variable or an IDENTIFIER clause, which a write looks up in its own session;
- a CHAR column, whose comparison with a shorter literal is padded only at the
ALTER TABLE (SPARK-60551);
- a parameter marker left by the legacy parameter substitution (SPARK-60457).
{noformat}
ALTER TABLE t ADD CONSTRAINT c CHECK (d <= CURRENT_DATE);
-- the stored predicate compares d with the date of the ALTER TABLE
-- the next day:
INSERT INTO t VALUES (1, current_date()); -- CHECK_CONSTRAINT_VIOLATION
{noformat}
The same check written d <= current_date() is evaluated at each write.
The multi-part rule also covers a field of a struct column, such as e.ts <=
current_timestamp(), which a write resolves the same way. It could keep the
predicate only for a name whose leading parts can name the table.
One option, raised in
https://github.com/apache/spark/pull/59545#discussion_r4506760254: build the
predicate from the optimized condition with the current-context reads left in
place as dedicated V2 scalar expressions, which writes convert back to the same
functions. Connectors would see these expression names in Check.predicate(), so
they need documenting, and a connector that does not know them has to fall back
to predicateSql(). A condition whose deferred form has no V2 translation, such
as ts >= CURRENT_TIMESTAMP - INTERVAL 30 DAYS, would still keep its values of
the ALTER TABLE.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
