Bug #16798 MySQL shows different EXPLAIN and provides different results for the same query
Submitted: 26 Jan 2006 7:35 Modified: 20 Jun 2010 22:41
Reporter: Victoria Reznichenko Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:4.1.16, 5.0.21 OS:Linux (linux)
Assigned to: CPU Architecture:Any

[26 Jan 2006 7:35] Victoria Reznichenko
Description:
If you execute the same query twice in the same session MySQL provides different results and EXPLAIN shows different type. If you execute query first time you get empty result set and type: ref_or_null, if you execute this query again you get 12 as a result and EXPLAIN shows type: range.

I was able to reproduce it only with 4.1, correct results with 5.0

How to repeat:
1. restore tables
2. connect to MySQL server
3. execute query first time and check result
4. execute the same query again
[5 May 2006 9:56] Sergey Petrunya
The bug affects 5.0 also. 
Here is a way to reproduce on 5.0:

source optimzer_bug_060125.dump
select * from contacts2 where org_id is null;
mysql> SELECT si.slai_id
    -> FROM contacts2 c JOIN sla_instances si ON
    ->   ((si.owner_tbl = 3 AND si.owner_id = c.org_id) OR 
    ->    ( si.owner_tbl = 2 AND si.owner_id = c.c_id)) 
    -> WHERE 
    ->   c.c_id = 218 AND activedate < '2006-05-05 01:53:09' AND expiredate IS NULL;
Empty set (12.28 sec)
[5 May 2006 11:00] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6005
[6 May 2006 9:07] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6043
[6 May 2006 18:11] Sergey Petrunya
The fix has been pushed into 4.1.20 tree
[6 May 2006 18:31] Sergey Petrunya
Note for the changelog: 
The bug was that "ref" access optimizer could choose "ref_or_null" access method where it was not applicable. 

The cause was as follows: 
When "ref" access optimizer encounters "t.key=X OR t.key=Y" it would try to join them into ref_or_null access via "t.key=X OR NULL". In order to make this inference "ref" optimizer needs to check if Y<=>NULL.
The bug was that ref optimizer didn't check if value of Y was already known or not. If it wasn't, some random data was returned, and if that happened to be "Y IS NULL", then "ref" optimizer would erroneously conclude that "ref_or_null" access method was applicable. Subsequently, execution of the query will produce wrong results.
[7 May 2006 14:53] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6068
[8 May 2006 19:32] Paul DuBois
Noted in 4.1.20 changelog.
[5 May 2010 15:19] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 1:51] Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 5:48] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:18] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:46] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[30 May 2010 0:33] Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 11:49] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:27] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:14] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)