MySQL Bugs: #47669: Query showed by EXPLAIN EXTENDED gives different result from original query

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

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)

Thank you for the report.

Verified as described. In version 4.1 bug does not exist.

I reported another example of this 3 months ago:
http://bugs.mysql.com/bug.php?id=44932

Peter,

thank you for the feedback. Closed as duplicate of bug #44932

Reverted back to "Verified" due to internal discussion

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).

it also Verified as described in MYSQL  5.1.40 on Linux RHEL 5.3

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.

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.

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)

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.

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100326081116-m3v4l34yhr43mtsv) (version source revid:alik@sun.com-20100325072612-4sds00ix8ajo1e84) (pib:16)

Noted in 5.5.4 changelog.

Setting report to Need Merge pending push into 5.1.x.

5.5.4 changelog entry was moved to 5.5.5.

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)

Note in 5.1.46 changelog.

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)

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)

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)