[CALCITE-3826] UPDATE assigns wrong type to bind variables

• Type: Bug
• Status: Closed
• Priority: Blocker
• Resolution: Fixed
• Affects Version/s: 1.22.0
• Fix Version/s: 1.22.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Danny Chen
• Reporter: Enrico Olivelli
• Created: 26/Feb/20 07:00
• Updated: 06/Mar/20 04:31
• Resolved: 29/Feb/20 02:45

Description

In 1.22.0rc1 I have found a problem about EnumerableTableModify#getSourceExpressionList

It looks like it is not mapping correctly the expected datatypes of
bind variables in queries like UPDATE table set a=?,b=? where pk=?.

You can see the full SQL here in this commit in my test branch here
https://github.com/diennea/herddb/pull/563/commits/157f927c9efe85cf7cac1370e1637b1c7ec46dff#diff-5d7594bc81ae0c92bbd33dee6c0d189aR2301

My case is the following:

Create a table:
CREATE TABLE t1 (
field0 int PRIMARY KEY,
field1 VARCHAR(10),
field2 VARCHAR(10),
field3 INT,
field4 INT,
field5 VARCHAR(10)
)

UPDATE t1 SET field3 =?, field2=?, field4=?, field5=? where field0=?

The Update maps to this Calcite plan:

EnumerableTableModify(table=[[tblspace1, ip]], operation=[UPDATE],
updateColumnList=[[field3, field2, field4, field5]],
sourceExpressionList=[[?0, ?1, ?2, ?3]], flattened=[true]): rowcount =
1.0, cumulative cost =

{2.5 rows, 10.5 cpu, 0.0 io}

, id = 62
EnumerableProject(field0=[$0], field1=[$1], field2=[$2],
field3=[$3], field4=[$4], field5=[$5], EXPR$0=[?0], EXPR$1=[?1],
EXPR$2=[?2], EXPR$3=[?3]): rowcount = 1.0, cumulative cost =

{1.5 rows, 10.5 cpu, 0.0 io}

, id = 61
EnumerableInterpreter: rowcount = 1.0, cumulative cost =

{0.5 rows, 0.5 cpu, 0.0 io}

, id = 60
BindableTableScan(table=[[tblspace1, ip]], filters=[[=($0,
?4)]]): rowcount = 1.0, cumulative cost =

{0.005 rows, 0.01 cpu, 0.0 io}

, id = 45

In particular the obeserved problem is:

• the updateColumnList field is: field3, field2, field4, field5
• but the bind variables have wrong type: VARCHAR, VARCHAR, INT, INT,
  expecting INT VARCHAR, INT VARCHAR

even by changing the UPDATE command the types of bind variables stays the same,
and they are the in the same order as in the CREATE TABLE STATEMENT,
skipping the PK.

It may be a regression of CALCITE-3672

Issue Links

• is caused by: CALCITE-3658 TableModify of Update contains correlated variable by mistake.
• links to: GitHub Pull Request #1838