Description:
insert into bug (approx)
select approx as approx1 from (select false as approx) as data
on duplicate key update approx=approx;
Reports ERROR 1052 (23000): Column 'approx' in field list is ambiguous.
The problem goes away if any of:
1. the subselect is removed.
insert into bug (approx) select false as approx on duplicate key update approx=approx;
2. the name of the column in the subselect (and its reference in the super-select) is changed:
insert into bug (approx) select approx2 as approx1 from (select false as approx2) as data on duplicate key update approx=approx ;
3. the =approx is changed to =values(approx)
insert into bug (approx) select approx as approx1 from (select false as approx) as data on duplicate key update approx=values(approx);
=========console log =====================
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.16 |
+-----------+
1 row in set (0.00 sec)
mysql> connect test;drop table if exists bug; create table bug (approx boolean);
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Connection id: 9
Current database: test
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> insert into bug (approx) select approx as approx1 from (select false as approx) as data on duplicate key update approx=approx;
ERROR 1052 (23000): Column 'approx' in field list is ambiguous
mysql>
mysql> insert into bug (approx) select false as approx on duplicate key update approx=approx;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql>
mysql> insert into bug (approx) select approx2 as approx1 from (select false as approx2) as data on duplicate key update approx=approx;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql> insert into bug (approx) select approx as approx1 from (select false as approx) as data on duplicate key update approx=values(approx);
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql>
How to repeat:
select version();
connect test;drop table if exists bug; create table bug (approx boolean);
insert into bug (approx) select approx as approx1 from (select false as approx) as data on duplicate key update approx=approx;
insert into bug (approx) select false as approx on duplicate key update approx=approx;
insert into bug (approx) select approx2 as approx1 from (select false as approx2) as data on duplicate key update approx=approx;
insert into bug (approx) select approx as approx1 from (select false as approx) as data on duplicate key update approx=values(approx);
insert into bug (approx) select approx as approx1 from (select false as approx) as data on duplicate key update approx=approx;
Suggested fix:
The column reference "approx" in the on duplicate key update clause is supposed to refer either to the current value in the row ("approx") or to the value in the inserted data ("values(approx)") but can never refer to the columns in the select or subselect, and therefore is not ambiguous.
Description: insert into bug (approx) select approx as approx1 from (select false as approx) as data on duplicate key update approx=approx; Reports ERROR 1052 (23000): Column 'approx' in field list is ambiguous. The problem goes away if any of: 1. the subselect is removed. insert into bug (approx) select false as approx on duplicate key update approx=approx; 2. the name of the column in the subselect (and its reference in the super-select) is changed: insert into bug (approx) select approx2 as approx1 from (select false as approx2) as data on duplicate key update approx=approx ; 3. the =approx is changed to =values(approx) insert into bug (approx) select approx as approx1 from (select false as approx) as data on duplicate key update approx=values(approx); =========console log ===================== mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.16 | +-----------+ 1 row in set (0.00 sec) mysql> connect test;drop table if exists bug; create table bug (approx boolean); Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Connection id: 9 Current database: test Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> insert into bug (approx) select approx as approx1 from (select false as approx) as data on duplicate key update approx=approx; ERROR 1052 (23000): Column 'approx' in field list is ambiguous mysql> mysql> insert into bug (approx) select false as approx on duplicate key update approx=approx; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> mysql> insert into bug (approx) select approx2 as approx1 from (select false as approx2) as data on duplicate key update approx=approx; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> mysql> mysql> insert into bug (approx) select approx as approx1 from (select false as approx) as data on duplicate key update approx=values(approx); Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> How to repeat: select version(); connect test;drop table if exists bug; create table bug (approx boolean); insert into bug (approx) select approx as approx1 from (select false as approx) as data on duplicate key update approx=approx; insert into bug (approx) select false as approx on duplicate key update approx=approx; insert into bug (approx) select approx2 as approx1 from (select false as approx2) as data on duplicate key update approx=approx; insert into bug (approx) select approx as approx1 from (select false as approx) as data on duplicate key update approx=values(approx); insert into bug (approx) select approx as approx1 from (select false as approx) as data on duplicate key update approx=approx; Suggested fix: The column reference "approx" in the on duplicate key update clause is supposed to refer either to the current value in the row ("approx") or to the value in the inserted data ("values(approx)") but can never refer to the columns in the select or subselect, and therefore is not ambiguous.