[CALCITE-3658] TableModify of Update contains correlated variable by mistake.

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.22.0
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: Jin Xing
• Created: 01/Jan/20 11:23
• Updated: 05/Jun/22 15:29
• Resolved: 03/May/22 10:12

Description

UPDATE clause like below

update emp set empno = empno + 1

will be converted to

LogicalTableModify(table=[[CATALOG, SALES, EMP]], operation=[UPDATE], updateColumnList=[[EMPNO]], sourceExpressionList=[[+($cor0.EMPNO, 1)]], flattened=[true])
  LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EXPR$0=[+($0, 1)])
    LogicalTableScan(table=[[CATALOG, SALES, EMP]])

However the correlated variables $cor0.EMPNO should not exist in sourceExpressionList.
It also brings trouble when convert TableModify back to Sql string.

Issue Links

• causes: CALCITE-3826 UPDATE assigns wrong type to bind variables
• relates to: CALCITE-5122 Update query with correlated throws AssertionError "field ordinal 0 out of range"
• links to: GitHub Pull Request #1713