Bug #5310 subselect returns wrong results

• Submitted: 31 Aug 2004 3:11
• Modified: 31 Aug 2004 4:01
• Reporter: Dave Pullin (Basic Quality Contributor)
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.3beta
• OS: Windows (Windows 2000)
• CPU Architecture: Any

[31 Aug 2004 3:11] Dave Pullin

Description:
drop table if exists AASQLbug ;
create table AASQLbug (x int  
,primary key(x) /* primary key required for bug to show */
 );

insert into AASQLbug values( 1),(2),(3);

select  a.x ,(select b.x from AASQLbug as b where b.x=a.x) as bad 
from  AASQLbug  as a group by a.x /* group by is required for bug to show */;

Yields this:
+---+-----+
| x | bad |
+---+-----+
| 1 |   2 |
| 2 |   3 |
| 3 |   3 |
+---+-----+

The column 'bad' should be equal to the column 'x'. 

How to repeat:
Run the sql in the description.

(This sql is reduced to the simplest form that shows the bug, from a very complex real example).

[31 Aug 2004 4:01] MySQL Verification Team

Thank you for the bug report. I was able to repeat with server 4.1.3
however the bug was already fixed in the current BK source tree and
the fix will be present in the next release:

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.4-gamma-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists AASQLbug ;
Query OK, 0 rows affected (0.05 sec)

mysql> create table AASQLbug (x int
    -> ,primary key(x) /* primary key required for bug to show */
    ->  );
Query OK, 0 rows affected (0.14 sec)

mysql>
mysql> insert into AASQLbug values( 1),(2),(3);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> select  a.x ,(select b.x from AASQLbug as b where b.x=a.x) as bad
    -> from  AASQLbug  as a group by a.x /* group by is required for bug to show */;
+---+-----+
| x | bad |
+---+-----+
| 1 |   1 |
| 2 |   2 |
| 3 |   3 |
+---+-----+
3 rows in set (0.04 sec)

mysql>