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
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 4.1.16, 5.0.21
• OS: Linux (linux)
• 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:[e-mail]-20100505145753-ivlt4hclbrjy8eye) (version source revid:[e-mail]-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:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:18] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 6:46] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-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:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-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:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-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:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)