| Bug #22458 | INSERT INTO SELECT - different result the second time | ||
|---|---|---|---|
| Submitted: | 18 Sep 2006 21:38 | Modified: | 19 Oct 2006 19:20 |
| Reporter: | Martin Sperl | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0 BK, 5.1 BK, 4.1 BK | OS: | Linux (RH4) |
| Assigned to: | CPU Architecture: | Any | |
[19 Sep 2006 5:15]
Martin Sperl
Actually there is a reason for this: I did not do a: set @lid=-99999;set @c=0; And I do not need to use the INSERT Clauses, just a Select shows the same result as well. The second time will show the right results. I have to conceed, that propper initialization is helpfull. But this behaviour still seems strange to me. I would expect, that the only thing that would change with this ommission is that the first line(or lines with the first ID) would give a bad result, but as soon as the first time the "HelperID" changes everything should act normally... I have reduced severity and it might also be better to change the subject... One possible conclusion is to add a warning, that a variable is used uninitialized on the command line... Martin
[20 Sep 2006 19:29]
Sveta Smirnova
Thank you for the report.
Verified as described in second comment on Linux using last BK sources. All versions are affected.
mysql> SELECT ID,HelperID,@lid,@lid:=HelperID
-> FROM empty
-> ORDER BY HelperID,ServerID,ServiceID;
+-----+----------+------+----------------+
| ID | HelperID | @lid | @lid:=HelperID |
+-----+----------+------+----------------+
| 237 | 3 | NULL | 3 |
| 239 | 3 | NULL | 3 |
| 238 | 3 | NULL | 3 |
| 236 | 3 | NULL | 3 |
| 235 | 3 | NULL | 3 |
| 234 | 3 | NULL | 3 |
| 233 | 3 | NULL | 3 |
| 232 | 3 | NULL | 3 |
| 240 | 3 | NULL | 3 |
.....
49 rows in set (0.00 sec)
mysql> SELECT ID,HelperID,@lid,@lid:=HelperID
-> FROM empty
-> ORDER BY HelperID,ServerID,ServiceID;
+-----+----------+------+----------------+
| ID | HelperID | @lid | @lid:=HelperID |
+-----+----------+------+----------------+
| 237 | 3 | 27 | 3 |
| 239 | 3 | 3 | 3 |
| 238 | 3 | 3 | 3 |
| 236 | 3 | 3 | 3 |
| 235 | 3 | 3 | 3 |
| 234 | 3 | 3 | 3 |
| 233 | 3 | 3 | 3 |
| 232 | 3 | 3 | 3 |
| 240 | 3 | 3 | 3 |
....
[19 Oct 2006 19:20]
Timothy Smith
Hi. This is either "not a bug", or we will not fix it, depending on your interpretation. Please see the documentation here: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html ------------------- The general rule is to never assign a value to a user variable in one part of a statement and use the same variable in some other part the same statement. You might get the results you expect, but this is not guaranteed. ------------------- Also, see bug #1739 and bug #19792 for more details. Regards, Timothy
Description: Doing a "INSERT INTO table SELECT ..." twice can yield 2 different result-tables (with a truncate in between). And only the second one is correct and contains the same output as doing the select without an insert! This may possibly have to do with the "if function" in conjuction with variables in the select part. How to repeat: done with mysql -tvv .... mysql> source o; -------------- CREATE TEMPORARY TABLE `empty` ( `ID` int(11) NOT NULL auto_increment, `RRDKeyID` bigint(20) NOT NULL, `ServerID` bigint(20) NOT NULL, `ServiceID` bigint(20) NOT NULL, `HelperID` int(11) NOT NULL default '0', PRIMARY KEY (`ID`), UNIQUE KEY `RRDKeyID` (`RRDKeyID`), KEY `ServerID` (`ServerID`,`ServiceID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -------------- Query OK, 0 rows affected (0.00 sec) -------------- INSERT INTO `empty` (`ID`, `RRDKeyID`, `ServerID`, `ServiceID`, `HelperID`) VALUES (225,1141464142320198274,1141461855267868274,1141461855275884274,27),(226,1141464224781572274,1140615357727787274,9,25),(227,1141464224796520274,1140615357727787274,9,25),(228,1141464224814694274,1140615357727787274,9,25),(229,1141464224826871274,1140615357727787274,9,25),(230,1141464224836974274,1140615357727787274,9,25),(231,1141464224847159274,1140615357727787274,9,25),(232,1141587935991085274,1137004983994784274,4,3),(233,1141587936003093274,1137004983994784274,4,3),(234,1141587936019831274,1137004983994784274,4,3),(235,1141587936031692274,1137004983994784274,4,3),(236,1141587936043846274,1137004983994784274,4,3),(237,1141587936055778274,1137004983994784274,4,3),(238,1141587936067956274,1137004983994784274,4,3),(239,1141587936080256274,1137004983994784274,4,3),(240,1141587936092756274,1137004983994784274,4,3),(241,1141587939643786274,1137004983994784274,5,4),(242,1141587939659268274,1137004983994784274,5,4),(243,1141587939671300274,1137004983994784274,5,4),(244,1141587939694762274,1137004983994784274,5,4),(245,1141587939713016274,1137004983994784274,5,4),(246,1141587939727019274,1137004983994784274,5,4),(247,1141587939750028274,1137004983994784274,5,4),(248,1141587939771777274,1137004983994784274,5,4),(249,1141587939789148274,1137004983994784274,5,4),(250,1141588036420173274,1137419625559614274,4,11),(251,1141588036453795274,1137419625559614274,4,11),(252,1141588036465129274,1137419625559614274,4,11),(253,1141588036484933274,1137419625559614274,4,11),(254,1141588036495141274,1137419625559614274,4,11),(255,1141588036524852274,1137419625559614274,4,11),(256,1141588036548694274,1137419625559614274,4,11),(257,1141588041654449274,1137419625559614274,5,12),(258,1141588041666568274,1137419625559614274,5,12),(259,1141588041679998274,1137419625559614274,5,12),(260,1141588041692711274,1137419625559614274,5,12),(261,1141588041721034274,1137419625559614274,5,12),(262,1141588041731921274,1137419625559614274,5,12),(263,1141588041744549274,1137419625559614274,5,12),(264,1141588089482760274,1140615357727787274,4,22),(265,1141588089513600274,1140615357727787274,4,22),(266,1141588089540671274,1140615357727787274,4,22),(267,1141588089578659274,1140615357727787274,4,22),(268,1141588089610243274,1140615357727787274,4,22),(269,1141588089622777274,1140615357727787274,4,22),(270,1141588111233676274,1140615357727787274,5,23),(271,1141588111249042274,1140615357727787274,5,23),(272,1141588111269127274,1140615357727787274,5,23),(273,1141588111281591274,1140615357727787274,5,23) -------------- Query OK, 49 rows affected (0.00 sec) Records: 49 Duplicates: 0 Warnings: 0 -------------- CREATE TEMPORARY TABLE emptymap ( ID INTEGER NOT NULL, HelperID INTEGER NOT NULL, Counter INTEGER NOT NULL, PRIMARY KEY (ID) ) -------------- Query OK, 0 rows affected (0.00 sec) -------------- INSERT INTO emptymap (ID,Counter,HelperID) SELECT ID,@c:=if(@lid=HelperID,@c+1,1),@lid:=HelperID FROM empty ORDER BY HelperID,ServerID,ServiceID -------------- Query OK, 49 rows affected (0.00 sec) Records: 49 Duplicates: 0 Warnings: 0 -------------- select * from emptymap -------------- +-----+----------+---------+ | ID | HelperID | Counter | +-----+----------+---------+ | 237 | 3 | 1 | | 239 | 3 | 1 | | 238 | 3 | 1 | | 236 | 3 | 1 | | 235 | 3 | 1 | | 234 | 3 | 1 | | 233 | 3 | 1 | | 232 | 3 | 1 | | 240 | 3 | 1 | | 243 | 4 | 1 | | 244 | 4 | 1 | | 245 | 4 | 1 | | 246 | 4 | 1 | | 242 | 4 | 1 | | 247 | 4 | 1 | | 248 | 4 | 1 | | 241 | 4 | 1 | | 249 | 4 | 1 | | 252 | 11 | 1 | | 254 | 11 | 1 | | 251 | 11 | 1 | | 250 | 11 | 1 | | 255 | 11 | 1 | | 256 | 11 | 1 | | 253 | 11 | 1 | | 257 | 12 | 1 | | 258 | 12 | 1 | | 259 | 12 | 1 | | 260 | 12 | 1 | | 263 | 12 | 1 | | 262 | 12 | 1 | | 261 | 12 | 1 | | 269 | 22 | 1 | | 268 | 22 | 1 | | 266 | 22 | 1 | | 265 | 22 | 1 | | 264 | 22 | 1 | | 267 | 22 | 1 | | 272 | 23 | 1 | | 271 | 23 | 1 | | 270 | 23 | 1 | | 273 | 23 | 1 | | 231 | 25 | 1 | | 228 | 25 | 1 | | 226 | 25 | 1 | | 229 | 25 | 1 | | 230 | 25 | 1 | | 227 | 25 | 1 | | 225 | 27 | 1 | +-----+----------+---------+ 49 rows in set (0.00 sec) -------------- TRUNCATE emptymap -------------- Query OK, 0 rows affected (0.00 sec) -------------- INSERT INTO emptymap (ID,Counter,HelperID) SELECT ID,@c:=if(@lid=HelperID,@c+1,1),@lid:=HelperID FROM empty ORDER BY HelperID,ServerID,ServiceID -------------- Query OK, 49 rows affected (0.00 sec) Records: 49 Duplicates: 0 Warnings: 0 -------------- select * from emptymap -------------- +-----+----------+---------+ | ID | HelperID | Counter | +-----+----------+---------+ | 237 | 3 | 1 | | 239 | 3 | 2 | | 238 | 3 | 3 | | 236 | 3 | 4 | | 235 | 3 | 5 | | 234 | 3 | 6 | | 233 | 3 | 7 | | 232 | 3 | 8 | | 240 | 3 | 9 | | 243 | 4 | 1 | | 244 | 4 | 2 | | 245 | 4 | 3 | | 246 | 4 | 4 | | 242 | 4 | 5 | | 247 | 4 | 6 | | 248 | 4 | 7 | | 241 | 4 | 8 | | 249 | 4 | 9 | | 252 | 11 | 1 | | 254 | 11 | 2 | | 251 | 11 | 3 | | 250 | 11 | 4 | | 255 | 11 | 5 | | 256 | 11 | 6 | | 253 | 11 | 7 | | 257 | 12 | 1 | | 258 | 12 | 2 | | 259 | 12 | 3 | | 260 | 12 | 4 | | 263 | 12 | 5 | | 262 | 12 | 6 | | 261 | 12 | 7 | | 269 | 22 | 1 | | 268 | 22 | 2 | | 266 | 22 | 3 | | 265 | 22 | 4 | | 264 | 22 | 5 | | 267 | 22 | 6 | | 272 | 23 | 1 | | 271 | 23 | 2 | | 270 | 23 | 3 | | 273 | 23 | 4 | | 231 | 25 | 1 | | 228 | 25 | 2 | | 226 | 25 | 3 | | 229 | 25 | 4 | | 230 | 25 | 5 | | 227 | 25 | 6 | | 225 | 27 | 1 | +-----+----------+---------+ 49 rows in set (0.00 sec) mysql>