| Bug #24010 | INSERT INTO ... SELECT fails on unique constraint with data it doesn't select | ||
|---|---|---|---|
| Submitted: | 6 Nov 2006 13:46 | Modified: | 6 Mar 2007 19:34 |
| Reporter: | Taco van den Broek | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S2 (Serious) |
| Version: | 5.0.34-BK, 5.0.26 | OS: | Linux (Linux) |
| Assigned to: | Martin Hansson | CPU Architecture: | Any |
[6 Nov 2006 13:50]
Taco van den Broek
Bug also occurs in versions 5.0.26 and 5.0.27 on Mac OSX (i686) In version 5.1.12-beta on Mac OSX (i686) the query causes the following warning: +-------+------+-------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------+ | Error | 1264 | Out of range value for column '(null)' at row 1 | +-------+------+-------------------------------------------------+
[7 Nov 2006 12:09]
Heikki Tuuri
Taco, could this be due to rounding errors? You seem to insert the values of the UNIQUE KEY using some complex arithmetic formula. Regards, Heikki
[7 Nov 2006 12:22]
Taco van den Broek
Heikki, That's what I thought at first too. The first time this bug occurred it would say that the unique value 1-10 already existed. Given the fact that the query tries to insert the value 1-11, a rounding error sounded logic at that moment. However, within this minimal test case the server says that the value 1-2 already exists, which doesn't come close to 1-11 even with rounding errors. It is really hard to guess what is causing the problem since every change to either the data(structure) or the query makes the bug dissapear. The reason that the unique key, existing of integers, is inserted as decimal(31,30) is because of the precision we need for the qux and quux columns, rounding the @newBar or @newBaz values will cause the qux and quux columns to loose precision (26 decimals will be lost).
[26 Dec 2006 14:40]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described, with 5.0.34-BK on Linux:
mysql> drop table foo;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE `foo` (
-> `foo_id` int(11) NOT NULL auto_increment,
-> `bar` int(11) NOT NULL,
-> `baz` int(11) NOT NULL,
-> `qux` decimal(31,30) NOT NULL,
-> `quux` decimal(31,30) NOT NULL,
-> `corge` int(11) default NULL,
-> PRIMARY KEY (`foo_id`),
-> UNIQUE KEY `bar` (`bar`,`baz`),
-> KEY `quux` (`quux`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO `foo` (`foo_id`, `bar`, `baz`, `qux`, `quux`, `corge`)
-> VALUES (1, 1, 2, 0.500000000000000000000000000000,
-> 1.000000000000000000000000000000, 5),
-> (2, 2, 3, 0.666666666666666666666666666667, 1.000000000000000000000000000000,
-> 1),
-> (3, 1, 3, 0.333333333333333333333333333333, 0.500000000000000000000000000000,
-> 6),
-> (4, 3, 8, 0.375000000000000000000000000000, 0.400000000000000000000000000000,
-> 2),
-> (5, 2, 5, 0.400000000000000000000000000000, 0.500000000000000000000000000000,
-> 287),
-> (178, 311, 2021, 0.153884215734784760019792182088,
-> 0.153884462151394422310756972112, 698),
-> (187, 329, 2138, 0.153882132834424695977549111319,
-> 0.153882352941176470588235294118, 707),
-> (205, 1, 10, 0.100000000000000000000000000000, 0.111111111111111111111111111111,
-> 743);
Query OK, 8 rows affected (0.02 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> INSERT INTO foo (corge,bar,baz,qux,quux)
-> SELECT F.corge,
-> (@newBar:=(ROUND(13,30) * (@oldBar:=F.bar) + -2 * (@oldBaz:=F.baz)))
as
-> bar,
-> (@newBaz:=(ROUND(-1995,30) * @oldBar + 307 * @oldBaz)) as baz,
-> (@newBar / @newBaz) as qux,
-> ((ROUND(13,30) * (@oldRBar:=@oldBar) + ROUND(-2,30) * (@oldRBaz:=(@o
ldBaz -
-> 1))) / (ROUND(-1995,30) * @oldRBar + ROUND(307,30) * @oldRBaz)) as quux
-> FROM foo F
-> WHERE F.qux >= ROUND(CAST(329 AS DECIMAL(31,30)) / 2138, 30)
-> AND F.quux <= ROUND(CAST(327 AS DECIMAL(31,30)) / 2125, 30);
ERROR 1062 (23000): Duplicate entry '1-2' for key 2
mysql> SELECT F.corge, (@newBar:=(ROUND(13,30) * (@oldBar:=F.bar) + -2 * (@
oldBaz:=F.baz))) as bar, (@newBaz:=(ROUND(-1995,30) * @oldBar + 307 * @oldB
az)) as baz, (@newBar / @newBaz) as qux, ((ROUND(13,30) * (@oldRBar:=@o
ldBar) + ROUND(-2,30) * (@oldRBaz:=(@oldBaz - 1))) / (ROUND(-1995,30) * @oldRBa
r + ROUND(307,30) * @oldRBaz)) as quux FROM foo F WHERE F.qux >= ROUND(CAST(329
AS DECIMAL(31,30)) / 2138, 30) AND F.quux <= ROUND(CAST(327 AS DECIMAL(31,
30)) / 2125, 30);
+-------+----------------------------------+-----------------------------------+
-------------------+--------------------+
| corge | bar | baz |
qux | quux |
+-------+----------------------------------+-----------------------------------+
-------------------+--------------------+
| 707 | 1.000000000000000000000000000000 | 11.000000000000000000000000000000 |
0.090909090909091 | -0.010135135135135 |
+-------+----------------------------------+-----------------------------------+
-------------------+--------------------+
1 row in set (0.01 sec)
mysql> INSERT INTO foo (corge,bar,baz,qux,quux)
-> VALUES (707,1.000000000000000000000000000000, 11.00000000000000000000000
0000000, 0.090909090909091, -0.010135135135135);
Query OK, 1 row affected (0.01 sec)
mysql> select version();
+--------------+
| version() |
+--------------+
| 5.0.34-debug |
+--------------+
1 row in set (0.01 sec)
MyISAM table gives exactly the same bug.
[13 Feb 2007 8:18]
Martin Hansson
Here is a *much* simpler test case:
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
bar int(11) NOT NULL,
baz int(11) NOT NULL,
quux decimal(31,30) NOT NULL,
UNIQUE KEY bar (bar,baz),
KEY quux (quux)
);
INSERT INTO
foo ( bar, baz, quux )
VALUES
( 1, 1, 1 ),
( 1, 2, 0.1 );
INSERT INTO foo( bar, baz )
SELECT
bar,
@newBaz := 1 + baz
FROM
foo
WHERE
quux <= 0.1;
As we can see, the assignment leads to the expression 1 + baz to evaluate to 2 instead of 3, which is not what you'd expected. A SELECT without the surrounding INSERT will work as expected.
[13 Feb 2007 8:28]
Martin Hansson
This bug appears to be fixed in 5.0.36 as well as 5.1.15-beta-debug.
[15 Feb 2007 12:55]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/19940 ChangeSet@1.2410, 2007-02-15 14:00:01+01:00, mhansson@linux-st28.site +2 -0 Bug #24010: INSERT INTO ... SELECT fails on unique constraint with data it doesn't select. This bug was fixed along with bug #16861: User defined variable can have a wrong value if a tmp table was used. There the fix consisted of Item_func_set_user_var overloading the method Item::save_in_field. Consider the query from the test case: INSERT INTO foo( bar, baz ) SELECT bar, @newBaz := 1 + baz FROM foo WHERE quux <= 0.1; Here the assignment expression '@newBaz := 1 + baz' is represented by an Item_func_set_user_var. Its member method save_in_field, which writes the value of this assignment into the result field, writes the val_xxx() value, which is not updated at this point. In the fix introduced by the patch, the save_in_field method reads the actual variable value instead. See also comment for
[22 Feb 2007 8:45]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/20344 ChangeSet@1.2410, 2007-02-22 09:50:02+01:00, mhansson@linux-st28.site +2 -0 Bug #24010: INSERT INTO ... SELECT fails on unique constraint with data it doesn't select. This bug was fixed along with bug #16861: User defined variable can have a wrong value if a tmp table was used. There the fix consisted of Item_func_set_user_var overloading the method Item::save_in_field. Consider the query from the test case: INSERT INTO foo( bar, baz ) SELECT bar, @newBaz := 1 + baz FROM foo WHERE quux <= 0.1; Here the assignment expression '@newBaz := 1 + baz' is represented by an Item_func_set_user_var. Its member method save_in_field, which writes the value of this assignment into the result field, writes the val_xxx() value, which is not updated at this point. In the fix introduced by the patch, the save_in_field method reads the actual variable value instead. See also comment for
[22 Feb 2007 13:06]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/20355 ChangeSet@1.2410, 2007-02-22 14:11:01+01:00, mhansson@linux-st28.site +2 -0 Bug #24010: INSERT INTO ... SELECT fails on unique constraint with data it doesn't select. This bug was fixed along with bug #16861: User defined variable can have a wrong value if a tmp table was used. There the fix consisted of Item_func_set_user_var overloading the method Item::save_in_field. Consider the query from the test case: INSERT INTO foo( bar, baz ) SELECT bar, @newBaz := 1 + baz FROM foo WHERE quux <= 0.1; Here the assignment expression '@newBaz := 1 + baz' is represented by an Item_func_set_user_var. Its member method save_in_field, which writes the value of this assignment into the result field, writes the val_xxx() value, which is not updated at this point. In the fix introduced by the patch, the save_in_field method reads the actual variable value instead. See also comment for
[28 Feb 2007 17:45]
Sergei Glukhov
Fixed in 5.0.38, 5.1.17-beta
[6 Mar 2007 19:34]
Paul DuBois
Noted in 5.0.38, 5.1.17 changelogs. A user-defined variable could be assigned an incorrect value if a temporary table was employed in obtaining the result of the query used to determine its value.
Description: In my database I've created a situation in which a INSERT INTO ... SELECT behaves differently than doing the SELECT first and then inserting the data. I've removed all other tables and rows for as long as the bug kept occuring, the below test data and query are the absolute minimum for which (in this case) the bug occurs. The SELECT returns data 1-11 while MySQL casts an error about data tuple 1-2 already existing. How to repeat: -- Table creation CREATE TABLE `foo` ( `foo_id` int(11) NOT NULL auto_increment, `bar` int(11) NOT NULL, `baz` int(11) NOT NULL, `qux` decimal(31,30) NOT NULL, `quux` decimal(31,30) NOT NULL, `corge` int(11) default NULL, PRIMARY KEY (`foo_id`), UNIQUE KEY `bar` (`bar`,`baz`), KEY `quux` (`quux`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- Test data INSERT INTO `foo` (`foo_id`, `bar`, `baz`, `qux`, `quux`, `corge`) VALUES (1, 1, 2, 0.500000000000000000000000000000, 1.000000000000000000000000000000, 5), (2, 2, 3, 0.666666666666666666666666666667, 1.000000000000000000000000000000, 1), (3, 1, 3, 0.333333333333333333333333333333, 0.500000000000000000000000000000, 6), (4, 3, 8, 0.375000000000000000000000000000, 0.400000000000000000000000000000, 2), (5, 2, 5, 0.400000000000000000000000000000, 0.500000000000000000000000000000, 287), (178, 311, 2021, 0.153884215734784760019792182088, 0.153884462151394422310756972112, 698), (187, 329, 2138, 0.153882132834424695977549111319, 0.153882352941176470588235294118, 707), (205, 1, 10, 0.100000000000000000000000000000, 0.111111111111111111111111111111, 743); -- Failing query INSERT INTO foo (corge,bar,baz,qux,quux) SELECT F.corge, (@newBar:=(ROUND(13,30) * (@oldBar:=F.bar) + -2 * (@oldBaz:=F.baz))) as bar, (@newBaz:=(ROUND(-1995,30) * @oldBar + 307 * @oldBaz)) as baz, (@newBar / @newBaz) as qux, ((ROUND(13,30) * (@oldRBar:=@oldBar) + ROUND(-2,30) * (@oldRBaz:=(@oldBaz - 1))) / (ROUND(-1995,30) * @oldRBar + ROUND(307,30) * @oldRBaz)) as quux FROM foo F WHERE F.qux >= ROUND(CAST(329 AS DECIMAL(31,30)) / 2138, 30) AND F.quux <= ROUND(CAST(327 AS DECIMAL(31,30)) / 2125, 30); -- Successful INSERT of selected data INSERT INTO foo (corge,bar,baz,qux,quux) VALUES (707,1.000000000000000000000000000000, 11.000000000000000000000000000000, 0.090909090909090909090909090909, -0.010135135135135135135135135135);