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

[19 Sep 2012 16:30] Patrick SUBLAURIER
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.
[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.
[7 Feb 2013 14:46] Olav Sandstå
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.