Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-1620

Update with subquery treated as unsupported cartesian join, but multi-table update works

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 1.1.5
    • Fix Version/s: 1.1.8
    • Component/s: None
    • Labels:
      None

      Description

      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
      1000
      

      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)
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                drrtuy Roman
                Reporter:
                GeoffMontee Geoff Montee
              • Votes:
                2 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated: