MySQL Bugs: #60317: equivalent queries give different results

Bug #60317	equivalent queries give different results
Submitted:	3 Mar 2011 16:20	Modified:	3 Apr 2011 16:56
Reporter:	Kevin Ricords	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1.30-community-log	OS:	Linux (centos)
Assigned to:		CPU Architecture:	Any
Tags:	inconsistent query results, index_merge, intersect

Description:
2 equivalent queries give different results.  Query plans are different.

Bug appears in 5.1.30-community-log (does not appear to exist in 5.5.8).

How to repeat:
CREATE TABLE a( x INT PRIMARY KEY );
INSERT a VALUES( 9 );

CREATE TABLE b( y INT );
INSERT b VALUES( 8 ),( 8 );

CREATE TABLE c( z INT PRIMARY KEY, x INT, y INT, KEY( x ), KEY( y ) );
INSERT c VALUES( 0,9,0 ),( 1,9,0 ),( 2,0,6 ),( 3,0,6 );

SELECT *           #incorrect result
FROM  b, a
WHERE a.x=9
    AND(
       NOT EXISTS( select 1 from c where c.x=a.x AND c.y=6 )
       OR EXISTS( SELECT 1 FROM c WHERE c.y=b.y )
    );

SELECT *           #correct result
FROM  b, a
WHERE /*a.x=9
    AND*/(
       NOT EXISTS( select 1 from c where c.x=a.x AND c.y=6 )
       OR EXISTS( SELECT 1 FROM c WHERE c.y=b.y )
    );

Suggested fix:
In the incorrect query, explain shows 'index_merge', 'Using intersect(x,y)', 'rows=1', but it seems like rows should be 2.

On 5.1.55 I get identical results for both queries

     y     x  
------  ------
     8       9
     8       9

Peter
(not a MySQL person)

Please, check with recent released 5.1.x version, 5.1.55. We do not fix bugs that are repeatable only in older versions.

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.57-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE a( x INT PRIMARY KEY );
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT a VALUES( 9 );
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> CREATE TABLE b( y INT );
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT b VALUES( 8 ),( 8 );
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> 
mysql> CREATE TABLE c( z INT PRIMARY KEY, x INT, y INT, KEY( x ), KEY( y ) );
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT c VALUES( 0,9,0 ),( 1,9,0 ),( 2,0,6 ),( 3,0,6 );
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> 
mysql> SELECT *           #incorrect result
    -> FROM  b, a
    -> WHERE a.x=9
    ->     AND(
    ->        NOT EXISTS( select 1 from c where c.x=a.x AND c.y=6 )
    ->        OR EXISTS( SELECT 1 FROM c WHERE c.y=b.y )
    ->     );
+------+---+
| y    | x |
+------+---+
|    8 | 9 |
|    8 | 9 |
+------+---+
2 rows in set (0.11 sec)

mysql> SELECT *           #correct result
    -> FROM  b, a
    -> WHERE /*a.x=9
   /*>     AND*/(
    ->        NOT EXISTS( select 1 from c where c.x=a.x AND c.y=6 )
    ->        OR EXISTS( SELECT 1 FROM c WHERE c.y=b.y )
    ->     );
+------+---+
| y    | x |
+------+---+
|    8 | 9 |
|    8 | 9 |
+------+---+
2 rows in set (0.00 sec)

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".