Description:
I have an update statement that has a subquery in it. It looks to me like the subquery is only being evaluated once and then this value is the one used to update every row in the table, rather than being run once for each row of the table.
The problem presents itself in both InnoDB and MyISAM table types, but I am most interested in InnoDB as I need the transactional support of this table type.
How to repeat:
CREATE TABLE CHOICE_TYPE (
ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(200) NOT NULL,
DEFAULT_CHOICE_VALUE INTEGER UNSIGNED not NULL,
) TYPE = InnoDB;
insert into choice_type values (1, 'test1', 0);
insert into choice_type values (2, 'test2', 0);
CREATE TABLE CHOICE_VALUE (
ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
CHOICE_TYPE_ID INTEGER UNSIGNED NOT NULL,
VALUE VARCHAR(200) NOT NULL,
) TYPE = InnoDB;
insert into choice_value values (1, 1, 'test1-value1');
insert into choice_value values (2, 1, 'test1-value2');
insert into choice_value values (3, 2, 'test2-value1');
insert into choice_value values (4, 2, 'test2-value2');
Having created the values, now we update the CHOICE_TYPE table to have the correct values for the DEFAULT_CHOICE_VALUE field. (e.g. the ID of the first row from CHOICE_VALUE that has CHOICE_TYPE_ID set to the ID of the row in CHOICE_TYPE).
update CHOICE_TYPE
set DEFAULT_CHOICE_VALUE = (
select cv.ID
from CHOICE_VALUE cv
where cv.ID = ID
limit 1
);
Now we check the results:
select * from choice_type;
returns:
+----+-------+----------------------+
| ID | NAME | DEFAULT_CHOICE_VALUE |
+----+-------+----------------------+
| 1 | test1 | 1 |
| 2 | test2 | 1 |
+----+-------+----------------------+
Which is clearly wrong. ID 1 has the correct DEFAULT_CHOICE_VALUE id of 1, but ID 2 has the wrong DEFAULT_CHOICE_VALUE of 1, it should be 3. Seems that the subquery has only been run the once.
NOTE: I am aware that the update statement could be replaced with the following:
update CHOICE_TYPE ct, CHOICE_VALUE cv
set ct.DEFAULT_CHOICE_VALUE = cv.ID
where cv.CHOICE_TYPE_ID = ct.ID;
However, on small systems that can produce an out of memory error, and either way the original SQL still produces a bug.
Description: I have an update statement that has a subquery in it. It looks to me like the subquery is only being evaluated once and then this value is the one used to update every row in the table, rather than being run once for each row of the table. The problem presents itself in both InnoDB and MyISAM table types, but I am most interested in InnoDB as I need the transactional support of this table type. How to repeat: CREATE TABLE CHOICE_TYPE ( ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(200) NOT NULL, DEFAULT_CHOICE_VALUE INTEGER UNSIGNED not NULL, ) TYPE = InnoDB; insert into choice_type values (1, 'test1', 0); insert into choice_type values (2, 'test2', 0); CREATE TABLE CHOICE_VALUE ( ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, CHOICE_TYPE_ID INTEGER UNSIGNED NOT NULL, VALUE VARCHAR(200) NOT NULL, ) TYPE = InnoDB; insert into choice_value values (1, 1, 'test1-value1'); insert into choice_value values (2, 1, 'test1-value2'); insert into choice_value values (3, 2, 'test2-value1'); insert into choice_value values (4, 2, 'test2-value2'); Having created the values, now we update the CHOICE_TYPE table to have the correct values for the DEFAULT_CHOICE_VALUE field. (e.g. the ID of the first row from CHOICE_VALUE that has CHOICE_TYPE_ID set to the ID of the row in CHOICE_TYPE). update CHOICE_TYPE set DEFAULT_CHOICE_VALUE = ( select cv.ID from CHOICE_VALUE cv where cv.ID = ID limit 1 ); Now we check the results: select * from choice_type; returns: +----+-------+----------------------+ | ID | NAME | DEFAULT_CHOICE_VALUE | +----+-------+----------------------+ | 1 | test1 | 1 | | 2 | test2 | 1 | +----+-------+----------------------+ Which is clearly wrong. ID 1 has the correct DEFAULT_CHOICE_VALUE id of 1, but ID 2 has the wrong DEFAULT_CHOICE_VALUE of 1, it should be 3. Seems that the subquery has only been run the once. NOTE: I am aware that the update statement could be replaced with the following: update CHOICE_TYPE ct, CHOICE_VALUE cv set ct.DEFAULT_CHOICE_VALUE = cv.ID where cv.CHOICE_TYPE_ID = ct.ID; However, on small systems that can produce an out of memory error, and either way the original SQL still produces a bug.