Bug #66878 | Wrong resultset with subqueries and variable | ||
---|---|---|---|
Submitted: | 19 Sep 2012 16:30 | Modified: | 7 Feb 2013 14:46 |
Reporter: | Patrick SUBLAURIER | Email Updates: | |
Status: | Duplicate | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
Version: | 5.5.27 | OS: | Windows (x64) |
Assigned to: | CPU Architecture: | Any |
[19 Sep 2012 16:30]
Patrick SUBLAURIER
[19 Sep 2012 16:41]
MySQL Verification Team
Thank you for the bug report. Please provide the complete test case (dump with insert data, print the wrong result and the expected). Thanks in advance.
[19 Sep 2012 18:05]
Patrick SUBLAURIER
/* I use the Mysql 5.5.27 (x64) following options on Windows x64: [mysqld] innodb_file_per_table innodb_file_format=Barracuda */ CREATE TABLE `mytable` ( `RECNO` int(10) unsigned NOT NULL AUTO_INCREMENT, `CODE` varchar(2) NOT NULL DEFAULT '', `STATUS` varchar(1) NOT NULL DEFAULT '', `DATE` date DEFAULT NULL, `DESCRIPTION` varchar(100) NOT NULL DEFAULT '', PRIMARY KEY (`RECNO`), KEY `CODE` (`CODE`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 /* -------------------------------------------------------------------------------------------------------------------- */ SELECT @RECNO := 0; /* repeat this line 1000 times */ INSERT INTO `mytable` SELECT @RECNO := @RECNO + 1 AS RECNO, "", "", 20100101, "Comment" /* and then */ INSERT INTO `mytable` (`RECNO`,`CODE`,`STATUS`,`DATE`,`DESCRIPTION`) VALUES (25511,'C1','T','2011-01-01','Comment1'); INSERT INTO `mytable` (`RECNO`,`CODE`,`STATUS`,`DATE`,`DESCRIPTION`) VALUES (25512,'C1','T','2011-01-02','Comment2'); INSERT INTO `mytable` (`RECNO`,`CODE`,`STATUS`,`DATE`,`DESCRIPTION`) VALUES (25589,'C1','T','2011-01-03','Comment3'); INSERT INTO `mytable` (`RECNO`,`CODE`,`STATUS`,`DATE`,`DESCRIPTION`) VALUES (25723,'C1','T','2011-01-04','Comment4'); INSERT INTO `mytable` (`RECNO`,`CODE`,`STATUS`,`DATE`,`DESCRIPTION`) VALUES (25804,'C1','A','2011-01-05','Comment5'); INSERT INTO `mytable` (`RECNO`,`CODE`,`STATUS`,`DATE`,`DESCRIPTION`) VALUES (25821,'C1','A','2011-01-06','Comment6'); INSERT INTO `mytable` (`RECNO`,`CODE`,`STATUS`,`DATE`,`DESCRIPTION`) VALUES (26035,'C1','A','2012-01-07','Comment7'); INSERT INTO `mytable` (`RECNO`,`CODE`,`STATUS`,`DATE`,`DESCRIPTION`) VALUES (26036,'C1','A','2012-01-08','Comment8'); /* -------------------------------------------------------------------------------------------------------------------- */ SELECT t1.RECNO, t1.CODE, t1.STATUS, t1.DATE, t2._NO_ FROM `mytable` t1 INNER JOIN (SELECT RECNO AS PrimaryKey, (@_NO_ := CAST(@_NO_ AS SIGNED) + 1) AS _NO_ FROM `mytable`, (SELECT @_NO_ := 0) no WHERE (`DATE` >= 20120101 AND `CODE` IN ('C1', 'C2') AND `STATUS` = 'A') ORDER BY `DATE`, `RECNO` LIMIT 200) t2 ON (t1.RECNO = t2.PrimaryKey) /* -------------------------------------------------------------------------------------------------------------------- */ /* I get the following result with MySQL 5.5.27: +-------+------+--------+------------+------+ | RECNO | CODE | STATUS | DATE | _NO_ | +-------+------+--------+------------+------+ | 25511 | C1 | T | 2011-01-01 | 1 | | 25512 | C1 | T | 2011-01-02 | 2 | | 25589 | C1 | T | 2011-01-03 | 3 | | 25723 | C1 | T | 2011-01-04 | 4 | | 25804 | C1 | A | 2011-01-05 | 5 | | 25821 | C1 | A | 2011-01-06 | 6 | | 26035 | C1 | A | 2012-01-07 | 7 | | 26036 | C1 | A | 2012-01-08 | 8 | +-------+------+--------+------------+------+ wheras I should obiously obtain (MySQL 5.5.25a): +-------+------+--------+------------+------+ | RECNO | CODE | STATUS | DATE | _NO_ | +-------+------+--------+------------+------+ | 26035 | C1 | A | 2012-01-07 | 7 | | 26036 | C1 | A | 2012-01-08 | 8 | +-------+------+--------+------------+------+ */
[19 Sep 2012 20:18]
MySQL Verification Team
Thank you for the feedback. Microsoft Windows [Version 6.1.7601] Copyright (c) 2009 Microsoft Corporation. All rights reserved. d:\dbs>55 d:\dbs>d:\dbs\5.5\bin\mysql -uroot --port=3541 --prompt="mysql 5.5 >" Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.5.29 Source distribution Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.5 >use test Database changed mysql 5.5 >CREATE TABLE `mytable` ( -> `RECNO` int(10) unsigned NOT NULL AUTO_INCREMENT, -> `CODE` varchar(2) NOT NULL DEFAULT '', -> `STATUS` varchar(1) NOT NULL DEFAULT '', -> `DATE` date DEFAULT NULL, -> `DESCRIPTION` varchar(100) NOT NULL DEFAULT '', -> PRIMARY KEY (`RECNO`), -> KEY `CODE` (`CODE`) -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; Query OK, 0 rows affected (0.04 sec) mysql 5.5 >SELECT @RECNO := 0; +-------------+ | @RECNO := 0 | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec) <cut> mysql 5.5 >INSERT INTO `mytable` SELECT @RECNO := @RECNO + 1 AS RECNO, "", "", 20100101, "Comment"; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql 5.5 >INSERT INTO `mytable` SELECT @RECNO := @RECNO + 1 AS RECNO, "", "", 20100101, "Comment"; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql 5.5 >INSERT INTO `mytable` (`RECNO`,`CODE`,`STATUS`,`DATE`,`DESCRIPTION`) VALUES (25511,'C1','T','2011-01-01','Comment1'); Query OK, 1 row affected (0.00 sec) mysql 5.5 >INSERT INTO `mytable` (`RECNO`,`CODE`,`STATUS`,`DATE`,`DESCRIPTION`) VALUES (25512,'C1','T','2011-01-02','Comment2'); Query OK, 1 row affected (0.01 sec) mysql 5.5 >INSERT INTO `mytable` (`RECNO`,`CODE`,`STATUS`,`DATE`,`DESCRIPTION`) VALUES (25589,'C1','T','2011-01-03','Comment3'); Query OK, 1 row affected (0.00 sec) mysql 5.5 >INSERT INTO `mytable` (`RECNO`,`CODE`,`STATUS`,`DATE`,`DESCRIPTION`) VALUES (25723,'C1','T','2011-01-04','Comment4'); Query OK, 1 row affected (0.00 sec) mysql 5.5 >INSERT INTO `mytable` (`RECNO`,`CODE`,`STATUS`,`DATE`,`DESCRIPTION`) VALUES (25804,'C1','A','2011-01-05','Comment5'); Query OK, 1 row affected (0.00 sec) mysql 5.5 >INSERT INTO `mytable` (`RECNO`,`CODE`,`STATUS`,`DATE`,`DESCRIPTION`) VALUES (25821,'C1','A','2011-01-06','Comment6'); Query OK, 1 row affected (0.00 sec) mysql 5.5 >INSERT INTO `mytable` (`RECNO`,`CODE`,`STATUS`,`DATE`,`DESCRIPTION`) VALUES (26035,'C1','A','2012-01-07','Comment7'); Query OK, 1 row affected (0.00 sec) mysql 5.5 >INSERT INTO `mytable` (`RECNO`,`CODE`,`STATUS`,`DATE`,`DESCRIPTION`) VALUES (26036,'C1','A','2012-01-08','Comment8'); Query OK, 1 row affected (0.00 sec) mysql 5.5 >SELECT -> t1.RECNO, t1.CODE, t1.STATUS, t1.DATE, t2._NO_ FROM -> `mytable` t1 -> INNER JOIN -> (SELECT -> RECNO AS PrimaryKey, -> (@_NO_ := CAST(@_NO_ AS SIGNED) + 1) AS _NO_ -> FROM -> `mytable`, (SELECT @_NO_ := 0) no -> WHERE -> (`DATE` >= 20120101 AND `CODE` IN ('C1', 'C2') AND `STATUS` = 'A') -> ORDER BY `DATE`, `RECNO` -> LIMIT 200) t2 -> ON (t1.RECNO = t2.PrimaryKey); +-------+------+--------+------------+------+ | RECNO | CODE | STATUS | DATE | _NO_ | +-------+------+--------+------------+------+ | 25511 | C1 | T | 2011-01-01 | 1 | | 25512 | C1 | T | 2011-01-02 | 2 | | 25589 | C1 | T | 2011-01-03 | 3 | | 25723 | C1 | T | 2011-01-04 | 4 | | 25804 | C1 | A | 2011-01-05 | 5 | | 25821 | C1 | A | 2011-01-06 | 6 | | 26035 | C1 | A | 2012-01-07 | 7 | | 26036 | C1 | A | 2012-01-08 | 8 | +-------+------+--------+------------+------+ 8 rows in set (0.00 sec) ---------------------------------------------------------------------------- d:\mysql-5.5.24-winx64\bin>mysql -uroot test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.24 MySQL Community Server (GPL) Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE TABLE `mytable` ( -> `RECNO` int(10) unsigned NOT NULL AUTO_INCREMENT, -> `CODE` varchar(2) NOT NULL DEFAULT '', -> `STATUS` varchar(1) NOT NULL DEFAULT '', -> `DATE` date DEFAULT NULL, -> `DESCRIPTION` varchar(100) NOT NULL DEFAULT '', -> PRIMARY KEY (`RECNO`), -> KEY `CODE` (`CODE`) -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; Query OK, 0 rows affected (0.06 sec) mysql> SELECT @RECNO := 0; +-------------+ | @RECNO := 0 | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec) <cut> mysql> INSERT INTO `mytable` (`RECNO`,`CODE`,`STATUS`,`DATE`,`DESCRIPTION`) VALUES (26035,'C1','A','2012-01-07','Comment7'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `mytable` (`RECNO`,`CODE`,`STATUS`,`DATE`,`DESCRIPTION`) VALUES (26036,'C1','A','2012-01-08','Comment8'); Query OK, 1 row affected (0.00 sec) mysql> SELECT -> t1.RECNO, t1.CODE, t1.STATUS, t1.DATE, t2._NO_ FROM -> `mytable` t1 -> INNER JOIN -> (SELECT -> RECNO AS PrimaryKey, -> (@_NO_ := CAST(@_NO_ AS SIGNED) + 1) AS _NO_ -> FROM -> `mytable`, (SELECT @_NO_ := 0) no -> WHERE -> (`DATE` >= 20120101 AND `CODE` IN ('C1', 'C2') AND `STATUS` = 'A') -> ORDER BY `DATE`, `RECNO` -> LIMIT 200) t2 -> ON (t1.RECNO = t2.PrimaryKey); +-------+------+--------+------------+------+ | RECNO | CODE | STATUS | DATE | _NO_ | +-------+------+--------+------------+------+ | 26035 | C1 | A | 2012-01-07 | 1 | | 26036 | C1 | A | 2012-01-08 | 2 | +-------+------+--------+------------+------+ 2 rows in set (0.00 sec)
[7 Feb 2013 11:16]
Patrick SUBLAURIER
I think the bug was the same than #66845 Tested with MySQL 5.5.30, the issue is resolved.