MySQL Bugs: #24302: INSERT ...SELECT...ON DUPLICATE KEY not recognizing source field(s)

Bug #24302	INSERT ...SELECT...ON DUPLICATE KEY not recognizing source field(s)
Submitted:	14 Nov 2006 15:33	Modified:	15 Nov 2006 10:48
Reporter:	Darren Arrowsmith	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.21	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any

Description:
INSERT INTO t1 (UID_Product, Quantity)
  SELECT UID_Product, IF(ActualPrice<0,-1,+1) AS Qty
  FROM t2
ON DUPLICATE KEY UPDATE
  Quantity=Quantity+Qty;

gives error...
[1054] Unknown column 'Qty' in 'field list'.

I have tried also tried:
INSERT INTO t1 (UID_Product, Quantity)
  SELECT UID_Product, IF(ActualPrice<0,-1,+1) AS Qty
  FROM t2
ON DUPLICATE KEY UPDATE
  Quantity=Quantity+t2.Qty;

but this gives [1109] Unknown table 't2'.

Am I missing something here or am I not allowed to access any fields from the source [SELECT] query?

How to repeat:
CREATE TABLE t1 (
  UID_Product int(10) unsigned default '0',
  Quantity int(10) NOT NULL default '0',
  PRIMARY KEY  (UID_Product)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE t2 (
  UID_Product int(10) unsigned default '0',
  ActualPrice decimal(10,2) NOT NULL default '0.00',
  PRIMARY KEY  (UID_Product)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO t2 VALUES (1,1.99);
INSERT INTO t2 VALUES (2,-0.50);

INSERT INTO t1 (UID_Product, Quantity)
SELECT UID_Product, IF(ActualPrice<0,-1,+1) AS Qty
FROM t2
ON DUPLICATE KEY UPDATE
Quantity=Quantity+Qty;

This is expected behavior.
You are trying to use an alias instead of a column name.

To do what you expect, you can either repeat the expression you used before, or save it to a user variable.

Do either

INSERT INTO t1 (UID_Product, Quantity) 
SELECT UID_Product, IF(ActualPrice<0,-1,+1) AS Qty 
FROM t2 
ON DUPLICATE KEY 
UPDATE Quantity=Quantity+IF(ActualPrice<0,-1,+1);

or

INSERT INTO t1 (UID_Product, Quantity) 
SELECT UID_Product, @qty := IF(ActualPrice<0,-1,+1) 
FROM t2 
ON DUPLICATE KEY 
UPDATE Quantity=Quantity + @qty;

Regards
Giuseppe

As shown in my previous comment, this behavior is expected, and therefore this is not a bug.
See the manual:
http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html

Your first suggestion:
INSERT INTO t1 (UID_Product, Quantity) 
SELECT UID_Product, IF(ActualPrice<0,-1,+1) AS Qty 
FROM t2 
ON DUPLICATE KEY 
UPDATE Quantity=Quantity+IF(ActualPrice<0,-1,+1);

does not work.  It gives:
[1054] Unknown column 'ActualPrice' in 'field list'

and your second suggestion:
INSERT INTO t1 (UID_Product, Quantity) 
SELECT UID_Product, @qty := IF(ActualPrice<0,-1,+1) 
FROM t2 
ON DUPLICATE KEY 
UPDATE Quantity=Quantity + @qty

does not work either, it gives:
[1110] Column 'Quantity' specified twice

Using your table description and contents, the queries from my previous comment are working fine on both Linux and Windows. See for yourself:

mysql> select version();
+------------+
| version()  |
+------------+
| 4.1.21-max |
+------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1 (
    ->   UID_Product int(10) unsigned default '0',
    ->   Quantity int(10) NOT NULL default '0',
    ->   PRIMARY KEY  (UID_Product)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> CREATE TABLE t2 (
    ->   UID_Product int(10) unsigned default '0',
    ->   ActualPrice decimal(10,2) NOT NULL default '0.00',
    ->   PRIMARY KEY  (UID_Product)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.07 sec)

mysql>
mysql> INSERT INTO t2 VALUES (1,1.99);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 VALUES (2,-0.50);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 (UID_Product, Quantity)
    -> SELECT UID_Product, IF(ActualPrice<0,-1,+1) AS Qty
    -> FROM t2
    -> ON DUPLICATE KEY
    -> UPDATE Quantity=Quantity+IF(ActualPrice<0,-1,+1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1 (UID_Product, Quantity)
    -> SELECT UID_Product, @qty := IF(ActualPrice<0,-1,+1)
    -> FROM t2
    -> ON DUPLICATE KEY
    -> UPDATE Quantity=Quantity + @qty;
Query OK, 4 rows affected (0.00 sec)
Records: 2  Duplicates: 2  Warnings: 0

You can cut-and-paste the following script into a mysql command line client and report its output:

drop table if exists t1,t2;
select version();
CREATE TABLE t1 (
  UID_Product int(10) unsigned default '0',
  Quantity int(10) NOT NULL default '0',
  PRIMARY KEY  (UID_Product)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE t2 (
  UID_Product int(10) unsigned default '0',
  ActualPrice decimal(10,2) NOT NULL default '0.00',
  PRIMARY KEY  (UID_Product)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO t2 VALUES (1,1.99);
INSERT INTO t2 VALUES (2,-0.50);

select 't1' as t;
select * from t1;
select 't2' as t;
select * from t2;

INSERT INTO t1 (UID_Product, Quantity)
SELECT UID_Product, IF(ActualPrice<0,-1,+1) AS Qty
FROM t2
ON DUPLICATE KEY
UPDATE Quantity=Quantity+IF(ActualPrice<0,-1,+1);

select 't1' as t;
select * from t1;
select 't2' as t;
select * from t2;

INSERT INTO t1 (UID_Product, Quantity)
SELECT UID_Product, @qty := IF(ActualPrice<0,-1,+1)
FROM t2
ON DUPLICATE KEY
UPDATE Quantity=Quantity + @qty;

select 't1' as t;
select * from t1;
select 't2' as t;
select * from t2;

I must apologise profusely.  Our customer is running 4.1.21 - our development server is running 4.1.12 (I didn't notice the version difference as the numbers were so similar).

I have upgraded our server to 4.1.21 and concur that both your queries work - I must assume the problem has been fixed somewhere between 4.1.12 -> 4.1.21.

Thanks all for your help.
Darren