Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-41868

SET col = IGNORE writes NULL in multi-table UPDATE

    XMLWordPrintable

Details

    • Unexpected results
    • SET col = IGNORE writes NULL in multi-table UPDATE

    Description

      CREATE TABLE t1 (id INT) ENGINE=InnoDB;                                                                                                                                                                                                                   
      CREATE TABLE t2 (id INT, v INT DEFAULT 7, w INT) ENGINE=InnoDB;                                                                                                                                                                                           
      INSERT INTO t1 VALUES (1),(2);                                                                                                                                                                                                                            
      INSERT INTO t2 (id,v) VALUES (1,10),(2,20),(3,30);                                                                                                                                                                                                        
      EXPLAIN UPDATE t1, t2 SET t2.v = IGNORE, t2.w = t2.id + 5 WHERE t1.id=1;                                                                                                                                                                                  
      id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra                                                                                                                                                             
      1       SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    2       Using where                                                                                                                                                                       
      1       SIMPLE  t2      ALL     NULL    NULL    NULL    NULL    3                                                                                                                                                                                         
      UPDATE t1, t2 SET t2.v = IGNORE, t2.w = t2.id + 5 WHERE t1.id=1;                                                                                                                                                                                          
      select * from t2;                                                                                                                                                                                                                                         
      id      v       w                                                                                                                                                                                                                                         
      1       NULL    6                                                                                                                                                                                                                                         
      2       NULL    7                                                                                                                                                                                                                                         
      3       NULL    8                                                                                                                                                                                                                                         
      DROP TABLE t1, t2;
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              Johnston Rex Johnston
              Oleksandr Byelkin Oleksandr Byelkin
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Time Tracking

                  Estimated:
                  Original Estimate - 2.5d
                  2.5d
                  Remaining:
                  Remaining Estimate - 2.5d
                  2.5d
                  Logged:
                  Time Spent - Not Specified
                  Not Specified

                  Git Integration

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