[
https://issues.apache.org/jira/browse/SPARK-60286?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Yang Jie updated SPARK-60286:
-----------------------------
Description:
ALTER TABLE ... ADD CONSTRAINT ... CHECK resolves the condition over the table
aliased by its name, so a column qualified by the table name (tbl.id) resolves
to the column id. Writes that check a constraint through its predicateSql,
which they do when the stored predicate is null, resolve the parsed condition
against the write's query, where that qualifier does not exist. CREATE TABLE
resolves the condition without the alias, so there tbl.id can only be a field
of a struct column named tbl.
The same predicateSql therefore means different columns depending on which
statement stored it. With a table (id BIGINT, tbl STRUCT<id: BIGINT>):
- ALTER TABLE ... CHECK (nullif(tbl.id, 3) > 0) checks the column id.
- CREATE TABLE ... CHECK (nullif(tbl.id, 3) > 0) checks the field tbl.id.
Both store predicateSql "nullif(tbl.id, 3) > 0", and Check records nothing
about which statement stored it.
Without such a struct column, an ALTER TABLE constraint on a qualified column
whose predicate cannot be translated to V2 makes every later write fail with
UNRESOLVED_COLUMN. On master this already happens for CHECK
(from_json(tbl.data, 'a INT').a IS NULL). After SPARK-60032 it also happens for
a condition that reads the current time and the like, such as CHECK (tbl.ts <=
current_timestamp()), which is now stored without a predicate. SPARK-60033
would make more constraints fall back to predicateSql.
A fix has to tell the two meanings apart, for example by resolving the
condition in ALTER TABLE the way CREATE TABLE and writes do, or by recording
how the stored condition was resolved.
> ALTER TABLE ADD CONSTRAINT CHECK and writes resolve a column qualified by the
> table name differently
> ----------------------------------------------------------------------------------------------------
>
> Key: SPARK-60286
> URL: https://issues.apache.org/jira/browse/SPARK-60286
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 5.0.0
> Reporter: Yang Jie
> Priority: Major
>
> ALTER TABLE ... ADD CONSTRAINT ... CHECK resolves the condition over the
> table aliased by its name, so a column qualified by the table name (tbl.id)
> resolves to the column id. Writes that check a constraint through its
> predicateSql, which they do when the stored predicate is null, resolve the
> parsed condition against the write's query, where that qualifier does not
> exist. CREATE TABLE resolves the condition without the alias, so there tbl.id
> can only be a field of a struct column named tbl.
> The same predicateSql therefore means different columns depending on which
> statement stored it. With a table (id BIGINT, tbl STRUCT<id: BIGINT>):
> - ALTER TABLE ... CHECK (nullif(tbl.id, 3) > 0) checks the column id.
> - CREATE TABLE ... CHECK (nullif(tbl.id, 3) > 0) checks the field tbl.id.
> Both store predicateSql "nullif(tbl.id, 3) > 0", and Check records nothing
> about which statement stored it.
> Without such a struct column, an ALTER TABLE constraint on a qualified column
> whose predicate cannot be translated to V2 makes every later write fail with
> UNRESOLVED_COLUMN. On master this already happens for CHECK
> (from_json(tbl.data, 'a INT').a IS NULL). After SPARK-60032 it also happens
> for a condition that reads the current time and the like, such as CHECK
> (tbl.ts <= current_timestamp()), which is now stored without a predicate.
> SPARK-60033 would make more constraints fall back to predicateSql.
> A fix has to tell the two meanings apart, for example by resolving the
> condition in ALTER TABLE the way CREATE TABLE and writes do, or by recording
> how the stored condition was resolved.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
