Bug #20916 | ambigous field with INSERT ... SELECT ... ON DUPLICATE KEY UPDATE | ||
---|---|---|---|
Submitted: | 8 Jul 2006 17:35 | Modified: | 30 Oct 2006 19:08 |
Reporter: | Daniel Kinzler | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
Version: | 4.1.13, 5.0.21 | OS: | Linux (Linux) |
Assigned to: | Paul DuBois | CPU Architecture: | Any |
[8 Jul 2006 17:35]
Daniel Kinzler
[8 Jul 2006 17:52]
Daniel Kinzler
I just found a workaround: wrap the select in another select to rename the fields (just aliasing does not work) AND do not specify a field list for the INSERT. Then it works. Here's the code: INSERT INTO meta2_neighbours (frommeaning, tomeaning, conflict, level, via) SELECT f,t,c,l,v FROM ( SELECT newmeaning as f, tomeaning as t, conflict as c, level as l, FROM meta2_neighbours JOIN meta2_tmp_alias ON oldmeaning = frommeaning ) as X ON DUPLICATE KEY UPDATE level = IF(level < VALUES(level), level, VALUES(level)), conflict = IF(conflict > VALUES(conflict), conflict, VALUES(conflict)) Or, using the more terse example: insert into ma select x from (select a as x from ma) as Z on duplicate key update a=a;
[14 Jul 2006 12:44]
Valeriy Kravchuk
With 5.0.25-BK I've got: openxs@suse:~/dbs/5.0> bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 5.0.25 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE TABLE `meta2_tmp_alias` ( -> `oldmeaning` int(12) NOT NULL default '0', -> `newmeaning` int(12) NOT NULL default '0' -> ) ENGINE=InnoDB DEFAULT CHARSET=binary ; Query OK, 0 rows affected (0.10 sec) mysql> CREATE TABLE `meta2_neighbours` ( -> `frommeaning` int(12) NOT NULL default '0', -> `tomeaning` int(12) NOT NULL default '0', -> `conflict` tinyint(4) NOT NULL default '0', -> `level` tinyint(4) NOT NULL default '1', -> `via` varbinary(255) default NULL, -> UNIQUE KEY `fromto` (`frommeaning`,`tomeaning`), -> KEY `src` (`frommeaning`,`conflict`), -> KEY `tgt` (`tomeaning`,`conflict`), -> KEY `conflict` (`conflict`), -> KEY `level` (`level`) -> ) ENGINE=InnoDB DEFAULT CHARSET=binary; Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO meta2_neighbours (frommeaning, tomeaning, conflict, level) -> SELECT newmeaning as frommeaning, -> tomeaning, -> conflict, -> level -> FROM meta2_neighbours -> JOIN meta2_tmp_alias ON oldmeaning = from meaning -> ON DUPLICATE KEY UPDATE level = IF(level < -> VALUES(level), level, VALUES(level)), -> conflict = IF(conf lict > -> VALUES(conflict), conflict, VALUES(conflict)); ERROR 1052 (23000): Column 'level' in field list is ambiguous mysql> INSERT INTO meta2_neighbours (frommeaning, tomeaning, conflict, level) -> SELECT f, t, c, l FROM ( SELECT -> X.newmeaning as f, -> N.tomeaning as t, -> N.conflict as c, -> N.level as l -> FROM meta2_neighbours as N -> JOIN meta2_tmp_alias as X ON oldmeaning = -> frommeaning ) as Y -> ON DUPLICATE KEY UPDATE level = 1, conflic t = 2; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> INSERT INTO meta2_neighbours (frommeaning, tomeaning, conflict, level) -> SELECT N.frommeaning as f, -> N.tomeaning as t, -> N.conflict as c, -> N.level as l -> FROM meta2_neighbours as N -> WHERE N.frommeaning = 1234 -> ON DUPLICATE KEY UPDATE level = 1, conflic t = 2; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create table ma (a int); Query OK, 0 rows affected (0.02 sec) mysql> insert into ma select a from ma on duplicate key update a=a; ERROR 1052 (23000): Column 'a' in field list is ambiguous mysql> insert into ma select a from ma on duplicate key update a=1; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into ma select a from ma on duplicate key update a=ma.a; ERROR 1052 (23000): Column 'ma.a' in field list is ambiguous mysql> insert into ma select a from ma x on duplicate key update a=values(a)+ma .a; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 While with 4.1.21-BK I've got: openxs@suse:~/dbs/4.1> bin/mysql -uroot test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.21 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table ma (a int); Query OK, 0 rows affected (0.01 sec) mysql> insert into ma select a from ma x on duplicate key update a=values(a)+ma .a; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> CREATE TABLE `meta2_tmp_alias` ( -> `oldmeaning` int(12) NOT NULL default '0', -> `newmeaning` int(12) NOT NULL default '0' -> ) ENGINE=InnoDB DEFAULT CHARSET=binary; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE `meta2_neighbours` ( -> `frommeaning` int(12) NOT NULL default '0', -> `tomeaning` int(12) NOT NULL default '0', -> `conflict` tinyint(4) NOT NULL default '0', -> `level` tinyint(4) NOT NULL default '1', -> `via` varbinary(255) default NULL, -> UNIQUE KEY `fromto` (`frommeaning`,`tomeaning`), -> KEY `src` (`frommeaning`,`conflict`), -> KEY `tgt` (`tomeaning`,`conflict`), -> KEY `conflict` (`conflict`), -> KEY `level` (`level`) -> ) ENGINE=InnoDB DEFAULT CHARSET=binary; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO meta2_neighbours (frommeaning, tomeaning, conflict, level) -> SELECT newmeaning as frommeaning, -> tomeaning, -> conflict, -> level -> FROM meta2_neighbours -> JOIN meta2_tmp_alias ON oldmeaning = from meaning -> ON DUPLICATE KEY UPDATE level = IF(level < -> VALUES(level), level, VALUES(level)), -> conflict = IF(conf lict > -> VALUES(conflict), conflict, VALUES(conflict)); ERROR 1052 (23000): Column 'level' in field list is ambiguous mysql> INSERT INTO meta2_neighbours (frommeaning, tomeaning, conflict, level) -> SELECT f, t, c, l FROM ( SELECT -> X.newmeaning as f, -> N.tomeaning as t, -> N.conflict as c, -> N.level as l -> FROM meta2_neighbours as N -> JOIN meta2_tmp_alias as X ON oldmeaning = -> frommeaning ) as Y -> ON DUPLICATE KEY UPDATE level = 1, conflic t = 2; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 So, both 5.0-BK and 4.1-BK are consistent now. I think, this is not a bug: we have a way to write the statement. It just have to be documented explicitely.
[30 Oct 2006 19:08]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products. I'll add a note to: http://dev.mysql.com/doc/refman/5.0/en/insert-select.html pointing out the need to use table and column aliases in the SELECT part to avoid ambiguous column errors.