Bug #47669 Query showed by EXPLAIN EXTENDED gives different result from original query
Submitted: 28 Sep 2009 7:47 Modified: 20 Jun 2010 22:49
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0, 5.1, next -bzr OS:Linux
Assigned to: Sergei Glukhov CPU Architecture:Any
Tags: regression

[28 Sep 2009 7:47] Guilhem Bichot
Description:
mysql-5.1 revision-id:bjorn.munch@sun.com-20090903170735-679d64zp580wl39j
In EXPLAIN EXTENDED's warnings we normally see a transformed query equivalent to the original query, but in how-to-repeat the queries are not equivalent, they give different results.

http://dev.mysql.com/doc/refman/5.4/en/using-explain.html
"When the EXTENDED keyword is used, EXPLAIN produces extra information that can be viewed by issuing a SHOW WARNINGS statement following the EXPLAIN statement. This information displays how the optimizer qualifies table and column names in the SELECT  statement, what the SELECT looks like after the application of rewriting and optimization rules, and possibly other notes about the optimization process. "

This bug was found while working on BUG#46743 but exists in 5.1 as well as 6.0-codebase-bugfixing.

The key could be this '0' in the transformed query, where I rather expected NULL.

It is not 100% sure that this bug is a duplicate of BUG#30302 (30302 is about an excess FROM, the present bug is about '0'). If you wish to mark it as duplicate, please first verify that Evgeny's fix for 30302 does fix this one.

How to repeat:
CREATE TABLE C (c int);
INSERT INTO C VALUES (NULL);
CREATE TABLE D (d int);
INSERT INTO D VALUES (NULL), (0); # two rows needed for bug

SELECT (SELECT 1 FROM D WHERE d = c) FROM C ;
# returns NULL

EXPLAIN EXTENDED SELECT (SELECT 1 FROM D WHERE d = c) FROM C;

SHOW WARNINGS; # unneeded if in a .test file

then copy-paste the query returned by SHOW WARNINGS (it should be:
select (select 1 AS `1` from `test`.`D` where (`test`.`D`.`d` = '0')) AS `(SELECT 1 FROM D WHERE d = c)` from `test`.`C`)
and run it, it returns 1 (expected NULL)
[28 Sep 2009 7:55] Sveta Smirnova
Thank you for the report.

Verified as described. In version 4.1 bug does not exist.
[28 Sep 2009 8:09] Peter Laursen
I reported another example of this 3 months ago:
http://bugs.mysql.com/bug.php?id=44932
[28 Sep 2009 8:47] Sveta Smirnova
Peter,

thank you for the feedback. Closed as duplicate of bug #44932
[28 Sep 2009 9:14] Sveta Smirnova
Reverted back to "Verified" due to internal discussion
[28 Sep 2009 9:15] Sveta Smirnova
Quote from Guilhem's email:

could you please revert the "duplicate" status?
The reason is that my bug is about '0' which should be NULL, whereas Peter's bug is about a view which is improperly merged into a SELECT.
And BUG#30302 is about a wrong FROM clause, which I think it a third different problem.
I insist because I am almost sure that fixing Peter's bug will not fix mine (so if we leave mine as duplicate, mine will not be fixed).
[2 Nov 2009 8:12] He yunfei
it also Verified as described in MYSQL  5.1.40 on Linux RHEL 5.3
[16 Feb 2010 11:15] 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/100503

3343 Sergey Glukhov	2010-02-16
      Bug#47669 Query showed by EXPLAIN EXTENDED gives different result from original query
      Item_field::print method does not take into
      account fields whose values may be null.
      The fix is to print 'NULL' if field value is null.
     @ mysql-test/r/func_str.result
        result fix
     @ mysql-test/r/having.result
        result fix
     @ mysql-test/r/select.result
        result fix
     @ mysql-test/r/subselect.result
        result fix
     @ mysql-test/r/union.result
        result fix
     @ sql/item.cc
        print 'NULL' if field value is null.
[26 Feb 2010 13:40] 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/101655

3361 Sergey Glukhov	2010-02-26
      Bug#47669 Query showed by EXPLAIN EXTENDED gives different result from original query
      Item_field::print method does not take into
      account fields whose values may be null.
      The fix is to print 'NULL' if field value is null.
     @ mysql-test/r/explain.result
        test case
     @ mysql-test/r/func_str.result
        result fix
     @ mysql-test/r/having.result
        result fix
     @ mysql-test/r/select.result
        result fix
     @ mysql-test/r/subselect.result
        result fix
     @ mysql-test/r/union.result
        result fix
     @ mysql-test/t/explain.test
        test case
     @ sql/item.cc
        print 'NULL' if field value is null.
[2 Mar 2010 14:34] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100302142746-u1gxdf5yk2bjrq3e) (version source revid:alik@sun.com-20100301095421-4cz64ibem1h2quve) (merge vers: 6.0.14-alpha) (pib:16)
[3 Mar 2010 1:48] Paul Dubois
Noted in 6.0.14 changelog.

The query shown by EXPLAIN EXTENDED plus SHOW WARNINGS could produce
results different from the original query.

Setting report to Need Merge pending push to 5.1.x, Celosia.
[26 Mar 2010 8:22] Bugs System
Pushed into 5.5.4-m3 (revid:alik@sun.com-20100326080914-2pz8ns984e0spu03) (version source revid:alexey.kopytov@sun.com-20100307164059-cri8typa32cypq0l) (merge vers: 5.5.3-m2) (pib:16)
[26 Mar 2010 8:26] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100326081116-m3v4l34yhr43mtsv) (version source revid:alik@sun.com-20100325072612-4sds00ix8ajo1e84) (pib:16)
[26 Mar 2010 15:49] Paul Dubois
Noted in 5.5.4 changelog.

Setting report to Need Merge pending push into 5.1.x.
[31 Mar 2010 16:15] Paul Dubois
5.5.4 changelog entry was moved to 5.5.5.
[6 Apr 2010 7:59] Bugs System
Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:joro@sun.com-20100301084434-ytctk3ceebjvqo7a) (merge vers: 5.1.45) (pib:16)
[6 Apr 2010 14:14] Paul Dubois
Note in 5.1.46 changelog.
[17 Jun 2010 12:06] 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:51] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:33] 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)