[
https://issues.apache.org/jira/browse/SPARK-59962?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Yang Jie reassigned SPARK-59962:
--------------------------------
Assignee: Yang Jie
> 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
> Assignee: Yang Jie
> Priority: Major
> Labels: pull-request-available
>
> 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]
