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

LEFT OUTER JOIN table data is lost in ON DUPLICATE KEY UPDATE section

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.3.12, 5.5.33a, 5.5.34, 10.0.6
    • Fix Version/s: 5.5.35, 10.0.7, 5.3.13
    • Component/s: None
    • Labels:
      None
    • Environment:
      CentOS 64 bits

      Description

      The following sequence of statements gives the wrong result:

      drop table if exists t1;
      create table t1 (
      	id int(10) unsigned NOT NULL DEFAULT '0',
      	v int(10) unsigned DEFAULT '0',
      	PRIMARY KEY (id)
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
       
      drop table if exists t2;
      create table t2 (
      	id int(10) unsigned NOT NULL DEFAULT '0',
      	PRIMARY KEY (id)
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
       
      drop table if exists t3;
      create table t3 (
      	id int(10) unsigned NOT NULL DEFAULT '0',
      	v int(10) unsigned DEFAULT '0',
      	PRIMARY KEY (id)
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
       
       
      insert into t1 values (1, 10), (2, 10);
      insert into t2 values (1), (2);
      insert into t3 values (1, 20);
       
      insert into t1 
      select t2.id, 5 from t2 LEFT OUTER JOIN t3 ON t2.id = t3.id
      on duplicate key update t1.v = t3.v;
       
      select * from t1;

      The result is:

      MariaDB [tim]> select * from t1;
      +----+------+
      | id | v    |
      +----+------+
      |  1 | NULL |
      |  2 | NULL |
      +----+------+
      2 rows in set (0.00 sec)

      While on MySQL 5.1.41 the result is as expected:

      mysql> select * from t1;
      +----+------+
      | id | v    |
      +----+------+
      |  1 |   20 |
      |  2 | NULL |
      +----+------+
      2 rows in set (0.00 sec)

      It seems that the value t3.v is always NULL in the ON DUPLICATE KEY UPDATE section, instead of the value returned from the left outer join.

      With a regular JOIN the statements work as expected and give the same result as on MySQL (but in that case we loose the update on t1 record with id 2)

        Attachments

          Activity

            People

            • Assignee:
              psergey Sergei Petrunia
              Reporter:
              timruhl Tim Ruhl
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: