Yang Jie created SPARK-60454:
--------------------------------
Summary: ALTER TABLE ADD CONSTRAINT CHECK on a view adds the
constraint to its base table
Key: SPARK-60454
URL: https://issues.apache.org/jira/browse/SPARK-60454
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 5.0.0
Reporter: Yang Jie
Only the CHECK branch of AstBuilder.visitAddTableConstraint resolves the target
with createUnresolvedRelation, which follows views. The other constraints use
UnresolvedTable, which fails with EXPECT_TABLE_NOT_VIEW. So ALTER TABLE ... ADD
CONSTRAINT ... CHECK on a view validates the rows the view returns and adds the
constraint to the base table:
{noformat}
-- cat.ns.t has a row with id = 5
CREATE TEMPORARY VIEW v AS SELECT * FROM cat.ns.t WHERE id > 50;
ALTER TABLE v ADD CONSTRAINT c CHECK (id > 10);
-- succeeds: the validation reads no row, and c is added to cat.ns.t as VALID
{noformat}
The row id = 5 violates c. From reading the code, with a view that computes a
column, such as SELECT id * 100 AS id FROM t, the validation checks one
expression and the stored constraint another.
ALTER TABLE ... ADD CONSTRAINT ... CHECK should reject a view the way the other
constraints do.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
