MySQL Bugs: #67585: Sub SELECTs not working as expected (or in compare with 5.1.XX)

Bug #67585	Sub SELECTs not working as expected (or in compare with 5.1.XX)
Submitted:	14 Nov 2012 9:55	Modified:	14 Nov 2012 13:07
Reporter:	Eimantas Jatkonis	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	5.5.28	OS:	Linux (x86-64)
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
SELECT itself is not returning any results,
but if with using with SUB select it returns results.

(MyISAM, others not tested)

How to repeat:
CREATE TABLE A 
(
  ID INT NOT NULL AUTO_INCREMENT, 
  Data DATE, 
  I INT NOT NULL DEFAULT 0, 
  J INT NOT NULL DEFAULT 0, 
  PRIMARY KEY (ID),
  KEY `LK_Data` (`Data`)
);

INSERT INTO A (Data, i, j) VALUES ('2012.11.11', 2, 10), ('2012.11.12', 3, 20), ('2012.11.13', 4, 30);

SELECT @X := 1;

SELECT ID
FROM A
WHERE 
      Data >= DATE('2012.10.31') AND
      (
        I = @X OR
        J = @X
      )
GROUP BY ID;

SELECT BAD.*
FROM
(
  SELECT ID
  FROM A
  WHERE 
        Data >= DATE('2012.10.31') AND
        (
          I = @X OR
          J = @X
        )
  GROUP BY ID
) AS BAD;

Suggested fix:
Downgrade to 5.1.XX :) any other?

Adding output info:
(unable edit original post)

mysql> CREATE TABLE A
    -> (
    ->   ID INT NOT NULL AUTO_INCREMENT,
    ->   Data DATE,
    ->   I INT NOT NULL DEFAULT 0,
    ->   J INT NOT NULL DEFAULT 0,
    ->   PRIMARY KEY (ID),
    ->   KEY `LK_Data` (`Data`)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> INSERT INTO A (Data, i, j) VALUES ('2012.11.11', 2, 10), ('2012.11.12', 3, 20), ('2012.11.13', 4, 30);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT @X := 1;
+---------+
| @X := 1 |
+---------+
|       1 |
+---------+
1 row in set (0.01 sec)

mysql>
mysql> SELECT ID
    -> FROM A
    -> WHERE
    ->       Data >= DATE('2012.10.31') AND
    ->       (
    ->         I = @X OR
    ->         J = @X
    ->       )
    -> GROUP BY ID;
Empty set (0.01 sec)

mysql>
mysql> SELECT BAD.*
    -> FROM
    -> (
    ->   SELECT ID
    ->   FROM A
    ->   WHERE
    ->         Data >= DATE('2012.10.31') AND
    ->         (
    ->           I = @X OR
    ->           J = @X
    ->         )
    ->   GROUP BY ID
    -> ) AS BAD;
+----+
| ID |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.01 sec)

Looks like MyISAM engine issue:

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.28-log MySQL Community Server (GPL)

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> create database d11;
Query OK, 1 row affected (0.01 sec)

mysql> use d11
Database changed
mysql> CREATE TABLE A
    -> (
    ->   ID INT NOT NULL AUTO_INCREMENT,
    ->   Data DATE,
    ->   I INT NOT NULL DEFAULT 0,
    ->   J INT NOT NULL DEFAULT 0,
    ->   PRIMARY KEY (ID),
    ->   KEY `LK_Data` (`Data`)
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql>
mysql> INSERT INTO A (Data, i, j) VALUES ('2012.11.11', 2, 10), ('2012.11.12', 3, 20), ('2012.11.13', 4, 30);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT @X := 1;
+---------+
| @X := 1 |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT ID
    -> FROM A
    -> WHERE
    ->       Data >= DATE('2012.10.31') AND
    ->       (
    ->         I = @X OR
    ->         J = @X
    ->       )
    -> GROUP BY ID;
Empty set (0.00 sec)

mysql>
mysql> SELECT BAD.*
    -> FROM
    -> (
    ->   SELECT ID
    ->   FROM A
    ->   WHERE
    ->         Data >= DATE('2012.10.31') AND
    ->         (
    ->           I = @X OR
    ->           J = @X
    ->         )
    ->   GROUP BY ID
    -> ) AS BAD;
Empty set (0.00 sec)

mysql> alter table A engine = MyISAM;
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT ID
    -> FROM A
    -> WHERE
    ->       Data >= DATE('2012.10.31') AND
    ->       (
    ->         I = @X OR
    ->         J = @X
    ->       )
    -> GROUP BY ID;
Empty set (0.02 sec)

mysql>
mysql> SELECT BAD.*
    -> FROM
    -> (
    ->   SELECT ID
    ->   FROM A
    ->   WHERE
    ->         Data >= DATE('2012.10.31') AND
    ->         (
    ->           I = @X OR
    ->           J = @X
    ->         )
    ->   GROUP BY ID
    -> ) AS BAD;
+----+
| ID |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

mysql>

Thank you for the bug report.

d:\dbs>d:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.68-Win X64 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.1 >use d11
Database changed
mysql 5.1 >SHOW CREATE TABLE A\G
*************************** 1. row ***************************
       Table: A
Create Table: CREATE TABLE `a` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Data` date DEFAULT NULL,
  `I` int(11) NOT NULL DEFAULT '0',
  `J` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `LK_Data` (`Data`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

mysql 5.1 >SELECT @X := 1;
+---------+
| @X := 1 |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

mysql 5.1 >
mysql 5.1 >SELECT ID
    -> FROM A
    -> WHERE
    ->       Data >= DATE('2012.10.31') AND
    ->       (
    ->         I = @X OR
    ->         J = @X
    ->       )
    -> GROUP BY ID;
Empty set (0.00 sec)

mysql 5.1 >
mysql 5.1 >SELECT BAD.*
    -> FROM
    -> (
    ->   SELECT ID
    ->   FROM A
    ->   WHERE
    ->         Data >= DATE('2012.10.31') AND
    ->         (
    ->           I = @X OR
    ->           J = @X
    ->         )
    ->   GROUP BY ID
    -> ) AS BAD;
Empty set (0.00 sec)

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 3
Server version: 5.5.30-log 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 d11
Database changed
mysql 5.5 >SHOW CREATE TABLE A\G
*************************** 1. row ***************************
       Table: A
Create Table: CREATE TABLE `a` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Data` date DEFAULT NULL,
  `I` int(11) NOT NULL DEFAULT '0',
  `J` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `LK_Data` (`Data`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

mysql 5.5 >SELECT BAD.*
    -> FROM
    -> (
    ->   SELECT ID
    ->   FROM A
    ->   WHERE
    ->         Data >= DATE('2012.10.31') AND
    ->         (
    ->           I = @X OR
    ->           J = @X
    ->         )
    ->   GROUP BY ID
    -> ) AS BAD;
+----+
| ID |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.01 sec)

mysql 5.5 >ALTER TABLE A ENGINE = InnoDB;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.5 >SELECT BAD.*
    -> FROM
    -> (
    ->   SELECT ID
    ->   FROM A
    ->   WHERE
    ->         Data >= DATE('2012.10.31') AND
    ->         (
    ->           I = @X OR
    ->           J = @X
    ->         )
    ->   GROUP BY ID
    -> ) AS BAD;
Empty set (0.01 sec)

mysql 5.5 >