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

              dleeyh Daniel Lee (Inactive)
              alebacq antoine
              Votes:
              2 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.