[
https://issues.apache.org/jira/browse/CALCITE-6007?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17765371#comment-17765371
]
Wenrui Meng edited comment on CALCITE-6007 at 9/14/23 9:44 PM:
---------------------------------------------------------------
The fix could be just add one more SqlKind in the registerFrom function to
attach the alias to the SqlWith subquery. I'm not sure whether it's intended to
exclude the SqlWith from the alias attachment. If so, anyone can explain about
it?
Here is what I proposed change. The *CASE WITH:* is the added code
{code:java}
if (alias == null) {
switch (kind) {
case IDENTIFIER:
case OVER:
alias = SqlValidatorUtil.alias(node);
if (alias == null) {
alias = SqlValidatorUtil.alias(node, nextGeneratedId++);
}
if (config.identifierExpansion()) {
newNode = SqlValidatorUtil.addAlias(node, alias);
}
break;
case SELECT:
case UNION:
case INTERSECT:
case EXCEPT:
case VALUES:
case UNNEST:
case OTHER_FUNCTION:
case COLLECTION_TABLE:
case PIVOT:
case UNPIVOT:
case MATCH_RECOGNIZE:
case WITH:
// give this anonymous construct a name since later
// query processing stages rely on it
alias = SqlValidatorUtil.alias(node, nextGeneratedId++);
if (config.identifierExpansion()) {
// Since we're expanding identifiers, we should make the
// aliases explicit too, otherwise the expanded query
// will not be consistent if we convert back to SQL, e.g.
// "select EXPR$1.EXPR$2 from values (1)".
newNode = SqlValidatorUtil.addAlias(node, alias);
}
break;
default:
break;
}
}
{code}
was (Author: wenruimeng):
The fix could be just add one more SqlKind in the registerFrom function to
attach the alias to the SqlWith subquery. I'm not sure whether it's intended to
exclude the SqlWith from the alias attachment. If so, anyone can explain about
it?
Here is what I proposed change. The red line is the added code
{code:java}
if (alias == null) {
switch (kind) {
case IDENTIFIER:
case OVER:
alias = SqlValidatorUtil.alias(node);
if (alias == null) {
alias = SqlValidatorUtil.alias(node, nextGeneratedId++);
}
if (config.identifierExpansion()) {
newNode = SqlValidatorUtil.addAlias(node, alias);
}
break;
case SELECT:
case UNION:
case INTERSECT:
case EXCEPT:
case VALUES:
case UNNEST:
case OTHER_FUNCTION:
case COLLECTION_TABLE:
case PIVOT:
case UNPIVOT:
case MATCH_RECOGNIZE:
{color:red}case WITH:{color}
// give this anonymous construct a name since later
// query processing stages rely on it
alias = SqlValidatorUtil.alias(node, nextGeneratedId++);
if (config.identifierExpansion()) {
// Since we're expanding identifiers, we should make the
// aliases explicit too, otherwise the expanded query
// will not be consistent if we convert back to SQL, e.g.
// "select EXPR$1.EXPR$2 from values (1)".
newNode = SqlValidatorUtil.addAlias(node, alias);
}
break;
default:
break;
}
}
{code}
> Sqlwith as subquery without alias doesn't have correct alias setup
> ------------------------------------------------------------------
>
> Key: CALCITE-6007
> URL: https://issues.apache.org/jira/browse/CALCITE-6007
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.34.0, 1.35.0
> Reporter: Wenrui Meng
> Priority: Major
>
> {code:java}
> SELECT
> a,
> b
> FROM (
> WITH
> sub AS (
> SELECT
> 1 AS a,
> 2 AS b)
> SELECT
> *
> FROM
> sub)
> WHERE
> a IS NOT null
> {code}
> It will generate the following SQL statement after validation
> {code:java}
> SELECT
> EXPR$0.a,
> EXPR$0.b
> FROM (
> WITH
> sub AS (
> SELECT
> 1 AS a,
> 2 AS b)
> SELECT
> sub.a AS a, sub.b AS b
> FROM
> sub)
> WHERE
> EXPR$0.a IS NOT null
> {code}
> The validated SQL become invalid since there is no EXPR$0 alias append for
> the SqlWith sub query but used in the expression outside.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
