Yang Jie created SPARK-59962:
--------------------------------
Summary: Generated columns, DELETE and CHECK constraints break on
NULLIF or BETWEEN over a non-cheap operand
Key: SPARK-59962
URL: https://issues.apache.org/jira/browse/SPARK-59962
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 5.0.0
Reporter: Yang Jie
RewriteWithExpression pre-evaluates a common expression's definition that is
referenced more than once and is not cheap in a Project over the first child
whose output covers the definition's references. A command's own expressions
are not evaluated over its children's rows: most are stored as metadata or
translated into source predicates. Several commands therefore end up reading a
_common_expr column that only the hoisted Project produces or, for CREATE TABLE
and a DELETE that deletes by filters, no longer match the planner. NULLIF and
BETWEEN over a non-cheap first operand trigger it with default settings:
{code:sql}
-- InMemoryTableCatalog as testcat
CREATE TABLE testcat.g (a INT, b INT GENERATED ALWAYS AS (nullif(a + 1, 3)));
-- [INTERNAL_ERROR] ... No plan for CreateTable ...
-- +- Project [a, b, (a + 1) AS _common_expr_0]
-- +- ResolvedIdentifier ..., g, [a, b]
DELETE FROM testcat.t WHERE (a + b) BETWEEN 0 AND 4;
-- [INTERNAL_ERROR] Unexpected table relation: Project [a, b, (a + b) AS
_common_expr_1] ...
-- group-based row-level table partitioned by dep
DELETE FROM rowcat.p WHERE (id + pk) BETWEEN 2 AND 4;
-- [INTERNAL_ERROR_ATTRIBUTE_NOT_FOUND] Could not find _common_expr_4 in [dep]
ALTER TABLE testcat.t ADD CONSTRAINT c1 CHECK (nullif(a + b, 3) > 0);
-- succeeds, but the V2 predicate stored in the catalog is
-- CASE WHEN _common_expr_1 = 3 THEN NULL ELSE _common_expr_1 > 0 END
{code}
With spark.sql.alwaysInlineCommonExpr=true, NULLIF and BETWEEN build no common
expression and the CREATE TABLE works; the DELETE on testcat then reports the
usual "Cannot delete from table ... where [...]".
DECLARE VARIABLE hits the same hoist and is tracked separately in SPARK-59954.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
