| 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 |
| Triage: | D4 (Minor) | ||
[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.
Description: When using INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, and the select references the table the insert points to, then any reference to a field in the UDPATE results in ERROR 1052 (23000): Column 'xxx' in field list is ambiguous. Since the UPDATE part can only refer to fields in the existing row (using an unqualified name) and in the row pending insertion (using VALUES(x) syntax), there can be no ambiguity. Any attempt to reference fields from the select appear misguided. I have tried several ways of aliasing/prefixing, to no avail. I also tried to wrap the select in a derived table, to rename all the fields. This results in a different error: ERROR 1110 (42000): COLUMN 'xxx' specified twice It should be notes that qualifying the field names in the UPDATE part with a table alias defined in the SELECT gets rid of the error in 5.0 but not in 4.1 - but that addresses the *wrong* value - i.e. not the one from the existing row! The examples below are what I originally had - they can probably be simplified. Also, it would be worth trying to reference a different table in the select, instead of doing a self-referntial insert. If the other table has a conflicting field name, this error may or may not also occur (i suspect it does but did not try). The following bugs seem related, but are marked as fixed: #12631, #8732, #13385, #10109 (the search in the bug tracker is pretty bad, btw) How to repeat: CREATE TABLE `meta2_tmp_alias` ( `oldmeaning` int(12) NOT NULL default '0', `newmeaning` int(12) NOT NULL default '0' ) ENGINE=InnoDB DEFAULT CHARSET=binary 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 INSERT INTO meta2_neighbours (frommeaning, tomeaning, conflict, level) SELECT newmeaning as frommeaning, tomeaning, conflict, level FROM meta2_neighbours JOIN meta2_tmp_alias ON oldmeaning = frommeaning ON DUPLICATE KEY UPDATE level = IF(level < VALUES(level), level, VALUES(level)), conflict = IF(conflict > VALUES(conflict), conflict, VALUES(conflict)) -- produces ERROR 1052 (23000): Column 'level' in field list is ambiguous -- to solve this, i fiddeled around a bit. -- all I manages was to get a different error: 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, conflict = 2 -- produces ERROR 1110 (42000): COLUMN 'level' specified twice -- this can also be produced using a simpler query, without join: 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, conflict = 2 -- produces ERROR 1110 (42000): COLUMN 'level' specified twice -- domas just supplied me with a much smaller test case: insert into ma select a from ma on duplicate key update a=a; -- produces ERROR 1052 (23000): Column 'a' in field list is ambiguous -- the following produces an error in 4.1, but not in 5.0; -- it doen NOT however express the desired logic, since ma.a is the -- same as VALUES(a), i.e. a value from the row pending insertion, -- not from teh row that is already there. insert into ma select a from ma x on duplicate key update a=values(a)+ma.a; Suggested fix: Inside an ON DUPLICATE KEY UPDATE clause, only the fields of the existing row (without prefix) and the ones from the (conflicting) row to be inserted (using VALUES(x)) should be visible. They refer to the fields given in the field list of the INSERT clause. No conflicts are possible. If access to values from the select are supposed to be visible in the UPDATE clause after all (for some strange reason), then a form like CURRENT(x) should be introduced to address fields of the existing row unambiguously.