MySQL Bugs: #66878: Wrong resultset with subqueries and variable

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:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.5.27	OS:	Windows (x64)
Assigned to:		CPU Architecture:	Any

Description:
When you execute a particuliar request in MySQL 5.5.25a the result returned is right. The same request executed in MySQL 5.5.27 gives erroneous result...

How to repeat:
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 (`DATE`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4

[mysqld]
innodb_file_per_table
innodb_file_format=Barracuda

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
        (`CODE` IN ('C1', 'C2') AND `STATUS` = 'A')
    ORDER BY `DATE`, `RECNO`
    LIMIT 200) t2
    ON (t1.RECNO = t2.PrimaryKey)

In my case, the MySQL 5.5.27 returned rows with the STATUS other than 'A' whereas MySQL 5.5.25a returned the right result !!!

Suggested fix:
If you omit the variable @_NO_ in the query, the problem disappears.

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.

/* 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 |
+-------+------+--------+------------+------+ 
*/

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)

I think the bug was the same than #66845

Tested with MySQL 5.5.30, the issue is resolved.

I can confirm that this is the same issue as reported in Bug#66845 and that it is solved by the fix for Bug#66845. This fix went into 5.5.30. I am going to close this bug as a duplicate of Bug#66845.