[#MCOL-1620] Update with subquery treated as unsupported cartesian join, but multi-table update works

If we create the following tables:

create table t1(c1 int, c2 int) engine=columnstore;

insert into t1 value (1,2);

create table t2(c1 int, c2 int, n char(6))engine=columnstore;

insert into t2 values (1,2, 'ABCDEF');

The following update fails:

update t2 set n = 'ABCDEG' where (c1, c2) in (select c1, c2 from t1);

ERROR 1815 (HY000): Internal error: CAL0002: Update Failed: ExeMgr Error

And the error log seems to indicate that this is treated as a cartesian join, which is unsupported (MCOL-131):

Aug 1 18:21:42 centos1 dmlpackageproc[3598]: 42.664759 |18|26|0| D 21

CAL0001: Start SQL statement: update t2 set n = 'ABCDEG' where (c1, c2) in

(select c1, c2 from t1);|test|

Aug 1 18:21:42 centos1 ExeMgr[3426]: 42.711186 |18|0|0| D 16 CAL0041: Start

SQL statement: update t2 set n = 'ABCDEG' where (c1, c2) in (select c1, c2

from t1); ||

Aug 1 18:21:42 centos1 joblist[3426]: 42.714476 |18|0|0| D 05 CAL0059:

JobListFactory makeJoblist error: IDB-1000: 't2' and 'sub-query' are not

joined.

Aug 1 18:21:42 centos1 ExeMgr[3426]: 42.714594 |18|0|0| D 16 CAL0042: End

SQL statement

Aug 1 18:21:42 centos1 dmlpackageproc[3598]: 42.714679 |18|26|0| D 21

CAL0001: End SQL statement with error

Aug 1 18:21:42 centos1 dmlpackageproc[3598]: 42.728648 |0|0|0| E 21

CAL0002: Update Failed: ExeMgr Error 1000

Aug 1 18:21:42 centos1 dmlpackageproc[3598]: 42.736177 |18|26|0| D 21

CAL0001: Start SQL statement: ROLLBACK

Aug 1 18:21:42 centos1 dmlpackageproc[3598]: 42.739211 |18|26|0| D 21

CAL0001: End SQL statement

But if it is rewritten with the multi-table update syntax, then it works fine:

update t2, t1 set t2.n = 'ABCDEG' where t2.c1 = t1.c1 and t2.c2 = t1.c2;

Query OK, 1 row affected (0.13 sec)

select * from t2;

+------+------+--------+

| c1 | c2 | n |

+------+------+--------+

| 1 | 2 | ABCDEG |

+------+------+--------+

1 row in set (0.02 sec)

[MCOL-1620] Update with subquery treated as unsupported cartesian join, but multi-table update works Created: 2018-08-02 Updated: 2021-12-21 Resolved: 2021-01-15
Status:	Closed
Project:	MariaDB ColumnStore
Component/s:	N/A
Affects Version/s:	1.1.5
Fix Version/s:	5.5.1