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)
Attachments
Activity
Elenst, you're right about table elimination being the cause of the problem. If I try to debug the INSERT .. SELECT statement, I can see that table t3 is eliminated. This is incorrect, because it is used in the ON DUPLICATE KEY UPDATE clause.
Fix pushed into 5.3 tree. It will be merged into 5.5 together with other fixes.
Tim, thanks for taking time to report this.
http://bazaar.launchpad.net/~maria-captains/maria/5.5/revision/3991
http://bazaar.launchpad.net/~maria-captains/maria/5.5/revision/2502.567.172
Thanks for reporting this.
For a workaround until the problem is fixed, you can try setting
SET optimizer_switch = 'table_elimination=off'.