[
https://issues.apache.org/jira/browse/CALCITE-5122?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17530655#comment-17530655
]
Jiajun Xie commented on CALCITE-5122:
-------------------------------------
There are two reasons for exceptions:
1. SqlUndate$sourceExpressionList is always [$0] in SqlToRelConvert because
CALCITE-3658.
And there is an exists unit test has same problem: *min(empno)* should be
{*}$9{*}, but it is {*}$0{*}.
{code:java}
<TestCase name="testUpdateSubQuery">
<Resource name="sql">
<![CDATA[update emp
set empno = (
select min(empno) from emp as e where e.deptno = emp.deptno)]]>
</Resource>
<Resource name="plan">
<![CDATA[
LogicalTableModify(table=[[CATALOG, SALES, EMP]], operation=[UPDATE],
updateColumnList=[[EMPNO]], sourceExpressionList=[[$0]], flattened=[true])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EXPR$0=[$10])
LogicalJoin(condition=[=($7, $9)], joinType=[left])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0}], EXPR$0=[MIN($1)])
LogicalProject(DEPTNO=[$7], EMPNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>{code}
2. RelToSqlConvert not support update query with correlated. If there is only
one table, we can omit sourceSelect. However, multiple tables must to unparse
sourceSelect.
{code:java}
sql("update \"foodmart\".\"product\" set \"product_id\" = \"product_id\" + 1")
.ok("UPDATE \"foodmart\".\"product\" SET \"product_id\" = \"product_id\" +
1");
sql("update \"foodmart\".\"product\" a set \"product_id\" = "
+ "(select \"product_class_id\" from \"foodmart\".\"product_class\" b "
+ "where a.\"product_class_id\" = b.\"product_class_id\")")
.ok("UPDATE \"foodmart\".\"product\" SET \"product_id\" = (SELECT "
+ "\"product_class_id\"\n"
+ "FROM (SELECT \"product\".\"product_class_id\", \"product\""
+ ".\"product_id\", \"product\".\"brand_name\", \"product\""
+ ".\"product_name\", \"product\".\"SKU\", \"product\".\"SRP\", "
+ "\"product\".\"gross_weight\", \"product\".\"net_weight\", "
+ "\"product\".\"recyclable_package\", \"product\".\"low_fat\", "
+ "\"product\".\"units_per_case\", \"product\".\"cases_per_pallet\", "
+ "\"product\".\"shelf_width\", \"product\".\"shelf_height\", "
+ "\"product\".\"shelf_depth\", \"t0\".\"$f1\" AS \"EXPR$0\"\n"
+ "FROM \"foodmart\".\"product\"\n"
+ "LEFT JOIN (SELECT \"product_class_id\" AS \"product_class_id1\", "
+ "SINGLE_VALUE(\"product_class_id\") AS \"$f1\"\n"
+ "FROM \"foodmart\".\"product_class\"\n"
+ "GROUP BY \"product_class_id\") AS \"t0\" ON \"product\""
+ ".\"product_class_id\" = \"t0\".\"product_class_id1\") AS \"t1\")");
{code}
Here is my pr:[https://github.com/apache/calcite/pull/2788.] Welcome to comment.
> Update query with correlated throws AssertionError "field ordinal 0 out of
> range"
> ---------------------------------------------------------------------------------
>
> Key: CALCITE-5122
> URL: https://issues.apache.org/jira/browse/CALCITE-5122
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.30.0
> Reporter: cwb
> Assignee: Jiajun Xie
> Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> code:
> String sqlUpdate="update mysql2.user_info_2 a set name = (select account_name
> from mysql2.account_info b where b.account_id=a.id )";
> RelToSqlConverter relToSqlConverter = new
> RelToSqlConverter(AnsiSqlDialect.DEFAULT);
> SqlImplementor.Result visit;
> SqlNode sqlNodeTarget;
> String targetSql;
> sqlNode = planner.parse(sqlUpdate);
> planner.validate(sqlNode);
> relRoot = planner.rel(sqlNode);
> visit = relToSqlConverter.visitRoot(relRoot.rel);
> sqlNodeTarget = visit.asStatement();
>
> targetSql=sqlNodeTarget.toSqlString(MysqlSqlDialect.DEFAULT).getSql();
> error: (in public Result visit(TableModify modify) )
> Exception in thread "main" java.lang.AssertionError: field ordinal 0 out of
> range {}
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$AliasContext.field(SqlImplementor.java:1531)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:630)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.lambda$12(RelToSqlConverter.java:1066)
> at org.apache.calcite.util.Util$TransformingList.get(Util.java:2783)
> at java.util.AbstractList$Itr.next(AbstractList.java:358)
> at java.util.AbstractCollection.toArray(AbstractCollection.java:141)
> at java.util.ArrayList.<init>(ArrayList.java:178)
> at org.apache.calcite.sql.SqlNodeList.<init>(SqlNodeList.java:98)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.exprList(RelToSqlConverter.java:1065)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:1038)
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
