Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.3.12, 5.5.33a, 5.5.34, 10.0.6
-
None
-
None
-
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)