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

cross-engine update... join... not working when updating innodb tables

    XMLWordPrintable

    Details

      Description

      It seems that an update statement cannot update innodb tables with columnstore content
      (but an update statement can update columnstore tables with innodb content)

      here is an example:

      -- create a columnstore and an innodb table
      drop table if exists mcs; drop table if exists idb; 
      create table mcs(a int, b int) engine=columnstore;
      create table idb(a int, b int) engine=innodb;
       
      -- insert some rows
      insert into mcs(a,b) values(1,2),(2,3),(4,5);
      insert into idb(a,b) values(1,2),(2,3),(4,5);
       
      -- columnstore can be update with innodb
      update mcs dest join idb src on dest.a=src.a set dest.b=src.b;
       
      --- ERROR: innodb cannot be update with columnstore
      update idb dest join mcs src on dest.a=src.a set dest.b=src.b;
       
      => Error Code: 1815. Internal error: IDB-2006: 'my_db.idb' does not exist in Columnstore.
      
      

      a workaround is to create a temp innodb table, insert from columnstore into this table with an INSERT... SELECT..., then update from innodb to innodb, then drop the temp innodb table. But that's a bit complicated and lengthy

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              dleeyh Daniel Lee
              Reporter:
              alebacq antoine
              Votes:
              2 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration