MySQL Bugs: #52636: 6.0 allowing JOINs on NULL values w/ optimizer_join_cache

Bug #52636	6.0 allowing JOINs on NULL values w/ optimizer_join_cache_level = 5-8
Submitted:	6 Apr 2010 21:22	Modified:	23 Nov 2010 3:23
Reporter:	Patrick Crews	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	6.0-codebase-bugfixing	OS:	Any
Assigned to:	Guilhem Bichot	CPU Architecture:	Any
Tags:	optimizer_join_cache_level, optimizer_switch

Description:
6.0-codebase-bugfixing tree is allowing JOIN operations on NULL values with optimizer_join_cache_level=5-8

The server is working as expected for values 0-4:

For this query:
SELECT table1.pk, table2.col_int_key, table1.col_int_key, table2.pk 
FROM C table1  LEFT  JOIN C table2  ON table2 .`col_int_key`  = table1 .`col_int_key`
WHERE table2 .`pk`   ORDER BY table1.pk ;

We get these results:
 pk	col_int_key	col_int_key	pk
<snip>
+5	NULL	NULL	5

How to repeat:
MTR test case.  
set optimizer_join_cache_level = 0-4 to obtain correct results, then set the value to 5-8 to observe how NULL-NULL comparison is being allowed / processed for the JOIN's ON clause:

#/* Server0: MySQL 6.0.14-alpha-debug-log */

/*!50400 SET SESSION optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off,mrr=on,mrr_cost_based=off,index_condition_pushdown=on' */;

SET SESSION optimizer_join_cache_level = 0;

#/* Begin test case for query 0 */

--disable_warnings
DROP TABLE /*! IF EXISTS */ C;
--enable_warnings

CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,2,'w');
INSERT INTO `C` VALUES (2,9,'m');
INSERT INTO `C` VALUES (3,3,'m');
INSERT INTO `C` VALUES (4,9,'k');
INSERT INTO `C` VALUES (5,NULL,'r');
INSERT INTO `C` VALUES (6,9,'t');
INSERT INTO `C` VALUES (7,3,'j');
INSERT INTO `C` VALUES (8,8,'u');
INSERT INTO `C` VALUES (9,8,'h');
INSERT INTO `C` VALUES (10,53,'o');
INSERT INTO `C` VALUES (11,0,NULL);
INSERT INTO `C` VALUES (12,5,'k');
INSERT INTO `C` VALUES (13,166,'e');
INSERT INTO `C` VALUES (14,3,'n');
INSERT INTO `C` VALUES (15,0,'t');
INSERT INTO `C` VALUES (16,1,'c');
INSERT INTO `C` VALUES (17,9,'m');
INSERT INTO `C` VALUES (18,5,'y');
INSERT INTO `C` VALUES (19,6,'f');
INSERT INTO `C` VALUES (20,2,'d');

# We have this here to see what exactly is going wrong
# This query will show that NULL / NULL was processed for certain optimizer_join_cache_level values
SELECT table1.pk, table2.col_int_key, table1.col_int_key, table2.pk 
FROM C table1  LEFT  JOIN C table2  ON table2 .`col_int_key`  = table1 .`col_int_key`
WHERE table2 .`pk`   ORDER BY table1.pk ;

 
SELECT table1 .`pk`  
FROM C table1  LEFT  JOIN C table2  ON table2 .`col_int_key`  = table1 .`col_int_key`  
WHERE table2 .`pk`   ORDER BY table1.pk ;

#/* Diff: */

#/* --- /tmp//randgen1552-1270587588-server0.dump	2010-04-06 16:59:48.000000000 -0400
# +++ /tmp//randgen1552-1270587588-server1.dump	2010-04-06 16:59:48.000000000 -0400
# @@ -32,6 +32,7 @@
#  4
#  4
#  4
# +5
#  6
#  6
#  6 */

DROP TABLE C;
#/* End of test case for query 0 */

minimal testcase:
CREATE TABLE t1 (b int);
INSERT INTO t1 VALUES (NULL),(3);

CREATE TABLE t2 (a int, b int, KEY (b));
INSERT INTO t2 VALUES (5,NULL),(7,3);

SELECT t2.a as bar FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;

With "SET SESSION optimizer_join_cache_level = X;" where X < 5,
the SELECT returns two rows: 7 and NULL, which is correct (NULL=NULL is UNKNOWN so no t2 row matches t1's NULL and thus a NULL column for t2 is returned (as this is a left join).
With X>=5: 7 and 5 are returned, which is wrong.

optimizer_join_cache_level=5 and 8 have quite different code paths (JOIN_CACHE_BKA vs JOIN_CACHE_BKA_UNIQUE), make sure to test both in the pushed testcase.

The original test case (which has a WHERE clause) would be solved when BUG#53793 is fixed; but the minimal one which has no WHERE clause would remain.

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/108869

3182 Guilhem Bichot	2010-05-21
      Fix for BUG#52636 6.0 allowing JOINs on NULL values w/ optimizer_join_cache_level = 5-8
     @ mysql-test/r/join_cache.result
        Before the fix, the SELECTs would show:
        both first and 2nd SELECT: (100),(NULL); 3rd SELECT: (100),(100);
        both 4th and 5th: (100),(200),(NULL).
        For example 1st SELECT would find no match in t2 for t1's 3 (thus
        emit t2.a=NULL, as it's a left join) and a match for t1's NULL
        (the bug) thus emit t2.a=100.
     @ mysql-test/t/join_cache.test
        test for bug
     @ sql/handler.cc
        After fixing sql_join_cache.cc, if the referenced table (t1 in the
        testcase) has only NULL rows, it can now happen, in
        handler::multi_range_read_next(), that the first call to
        mrr_funcs.next() (== bka_(unique)?range_seq_next) finds no keys
        (because it now internally eliminates NULLs); so "result" needs to be
        initialized for this case, otherwise behaviour is random (segmentation
        fault).
     @ sql/sql_join_cache.cc
        What happened in the test's scenario
        SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
        is:
        - plan is to access t1, and then t2 with "ref" access on key t2.b to
        the reference t1.b.
        - make_cond_for_table_from_pred() recognizes that the ON equality can
        be discarded as it's a consequence of using "ref" access
        - this "ref" access is marked as null_rejecting.
        So far so good, and at execution with optimizer_join_cache_level<=4,
        there is no problem, "ref" access is done with join_read_always_key()
        which tests ref->null_rejecting and does not pass t1.b NULL values
        to t2's index lookup.
        At higher levels, JOIN_CACHE_BKA(_UNIQUE)? are used instead of
        join_read_always_key(), and they forgot to test ref->null_rejecting,
        so NULL values of t1.b where accidentally matched with NULL values of
        t2.b, leading to wrong results (see comment of join_cache.result).
        The fix is to make JOIN_CACHE_BKA(_UNIQUE)? observe
        null_rejecting: when they scan the cached rows of the outer table t1,
        collecting key values from those cached rows in order to send them
        to t2's multi-range-read, we now don't collect any NULL key value.
        For this, JOIN_CACHE_BKA gets the same test as join_read_always_key().
        But JOIN_CACHE_BKA_UNIQUE is different, it doesn't have up-to-date
        fields pointed to by "ref", testable with ref->items[i].is_null(); it
        only has a raw key value (left by a previous duplicate-key-eliminating
        phase), so we inspect this key.
     @ sql/sql_select.cc
        comments. The last one is verbose, as failing to observe it has resulted in the present
        bug as well as BUG 46743.
     @ sql/sql_select.h
        a helper which can be used to temporarily reduce a testcase when debugging
        (allows to have one-row tables and still follow the same execution as
        two-row tables).

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/109137

3182 Guilhem Bichot	2010-05-25
      Fix for BUG#52636 6.0 allowing JOINs on NULL values w/ optimizer_join_cache_level = 5-8
     @ mysql-test/r/join_cache.result
        Before the fix, the SELECTs would show:
        both first and 2nd SELECT: (100),(NULL); 3rd SELECT: (100),(100);
        both 4th and 5th: (100),(200),(NULL).
        For example 1st SELECT would find no match in t2 for t1's 3 (thus
        emit t2.a=NULL, as it's a left join) and a match for t1's NULL
        (the bug) thus emit t2.a=100.
     @ mysql-test/t/join_cache.test
        test for bug
     @ sql/handler.cc
        After fixing sql_join_cache.cc, if the referenced table (t1 in the
        testcase) has only NULL rows, it can now happen, in
        handler::multi_range_read_next(), that the first call to
        mrr_funcs.next() (== bka_(unique)?range_seq_next) finds no keys
        (because it now internally eliminates NULLs); so "result" needs to be
        initialized for this case, otherwise behaviour is random (segmentation
        fault).
     @ sql/sql_join_cache.cc
        What happened in the test's scenario
        SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
        is:
        - plan is to access t1, and then t2 with "ref" access on key t2.b to
        the reference t1.b.
        - make_cond_for_table_from_pred() recognizes that the ON equality can
        be discarded as it's a consequence of using "ref" access
        - this "ref" access is marked as null_rejecting.
        So far so good, and at execution with optimizer_join_cache_level<=4,
        there is no problem, "ref" access is done with join_read_always_key()
        which tests ref->null_rejecting and does not pass t1.b NULL values
        to t2's index lookup.
        At higher levels, JOIN_CACHE_BKA(_UNIQUE)? are used instead of
        join_read_always_key(), and they forgot to test ref->null_rejecting,
        so NULL values of t1.b where accidentally matched with NULL values of
        t2.b, leading to wrong results (see comment of join_cache.result).
        The fix is to make JOIN_CACHE_BKA(_UNIQUE)? observe
        null_rejecting: when they scan the cached rows of the outer table t1,
        collecting key values from those cached rows in order to send them
        to t2's multi-range-read, we now don't collect any NULL key value.
        For this, JOIN_CACHE_BKA gets the same test as join_read_always_key().
        But JOIN_CACHE_BKA_UNIQUE is different, it doesn't have up-to-date
        fields pointed to by "ref", testable with ref->items[i].is_null(); it
        only has a raw key value (left by a previous duplicate-key-eliminating
        phase), so we inspect this key.
     @ sql/sql_select.cc
        comments. The last one is verbose, as failing to observe it has resulted in the present
        bug as well as BUG 46743.
     @ sql/sql_select.h
        a helper which can be used to temporarily reduce a testcase when debugging
        (allows to have one-row tables and still follow the same execution as
        two-row tables).

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/109617

3182 Guilhem Bichot	2010-05-31
      Fix for BUG#52636 6.0 allowing JOINs on NULL values w/ optimizer_join_cache_level = 5-8
      Optimizer chose "ref" access for the LEFT JOIN ON condition, and considered that this
      access it implemented the condition's semantics; which would have been true if
      JOIN_CACHE_BKA and JOIN_CACHE_BKA_UNIQUE hadn't forgotten to eliminate NULL values.
     @ mysql-test/r/join_cache.result
        Before the fix, the SELECTs would show:
        both first and 2nd SELECT: (100),(NULL); 3rd SELECT: (100),(100);
        both 4th and 5th: (100),(200),(NULL).
        For example 1st SELECT would find no match in t2 for t1's 3 (thus
        emit t2.a=NULL, as it's a left join) and a match for t1's NULL
        (the bug) thus emit t2.a=100.
     @ mysql-test/t/join_cache.test
        test for bug
     @ sql/handler.cc
        After fixing sql_join_cache.cc, if the referenced table (t1 in the
        testcase) has only NULL rows, it can now happen, in
        handler::multi_range_read_next(), that the first call to
        mrr_funcs.next() (== bka_range_seq_next or bka_unique_range_seq_next)
        finds no keys (because it now internally eliminates NULLs); so
        "result" needs to be initialized for this case, otherwise behaviour
        is random (segmentation fault).
     @ sql/sql_join_cache.cc
        What happened in the test's scenario
        SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
        is:
        - plan is to access t1, and then t2 with "ref" access on key t2.b to
        the reference t1.b.
        - make_cond_for_table_from_pred() recognizes that the ON equality can
        be discarded as it's a consequence of using "ref" access
        - this "ref" access is marked as null_rejecting.
        So far so good, and at execution with optimizer_join_cache_level<=4,
        there is no problem, "ref" access is done with join_read_always_key()
        which tests ref->null_rejecting and does not pass t1.b NULL values
        to t2's index lookup.
        At higher levels, JOIN_CACHE_BKA or JOIN_CACHE_BKA_UNIQUE are used
        instead of join_read_always_key(), and they forgot to test
        ref->null_rejecting, so NULL values of t1.b where accidentally matched
        with NULL values of t2.b, leading to wrong results (see comment of
        join_cache.result).
        The fix is to make JOIN_CACHE_BKA and JOIN_CAHE_BKA_UNIQUE observe
        null_rejecting: when they scan the cached rows of the outer table t1,
        collecting key values from those cached rows in order to send them
        to t2's multi-range-read, we now don't collect any NULL key value.
        For this, JOIN_CACHE_BKA gets the same test as join_read_always_key().
        But JOIN_CACHE_BKA_UNIQUE is different, it doesn't have up-to-date
        fields pointed to by "ref", testable with ref->items[i].is_null(); it
        only has a raw key value (left by a previous duplicate-key-eliminating
        phase), so we inspect this key.
     @ sql/sql_select.cc
        comments. The last one is verbose, as failing to observe it has resulted in the present
        bug as well as BUG 46743.
     @ sql/sql_select.h
        A member function to reduce duplicated code.
        A helper which can be used to temporarily reduce a testcase when debugging
        (allows to have one-row tables and still follow the same execution as
        two-row tables).

Approved, but please consider my comments.

I may have hit this when testing the mysql-next-mr-opt-backporting branch. See Bug#54110.

BUG#54110 has been marked as duplicate of this one.

Hello John. 6.0-codebase-bugfixing suffers from BUG#52636 too; it's not surprising that applying the fix makes next-mr-opt-backporting give different results from 6.0-codebase-bugfixing. You may want to use 5.1 as "the good reference" for checking new results.

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/109775

3182 Guilhem Bichot	2010-06-01
      Fix for BUG#52636 6.0 allowing JOINs on NULL values w/ optimizer_join_cache_level = 5-8
      Optimizer chose "ref" access for the LEFT JOIN ON condition, and considered that this
      access it implemented the condition's semantics; which would have been true if
      JOIN_CACHE_BKA and JOIN_CACHE_BKA_UNIQUE hadn't forgotten to eliminate NULL values.
     @ mysql-test/r/join_cache.result
        Before the fix, the SELECTs would show:
        both first and 2nd SELECT: (100),(NULL); 3rd SELECT: (100),(100);
        both 4th and 5th: (100),(200),(NULL).
        For example 1st SELECT would find no match in t2 for t1's 3 (thus
        emit t2.a=NULL, as it's a left join) and a match for t1's NULL
        (the bug) thus emit t2.a=100.
     @ mysql-test/t/join_cache.test
        test for bug
     @ sql/handler.cc
        After fixing sql_join_cache.cc, if the referenced table (t1 in the
        testcase) has only NULL rows, it can now happen, in
        handler::multi_range_read_next(), that the first call to
        mrr_funcs.next() (== bka_range_seq_next or bka_unique_range_seq_next)
        finds no keys (because it now internally eliminates NULLs); so
        "result" needs to be initialized for this case, otherwise behaviour
        is random (segmentation fault).
     @ sql/sql_join_cache.cc
        What happened in the test's scenario
        SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
        is:
        - plan is to access t1, and then t2 with "ref" access on key t2.b to
        the reference t1.b.
        - make_cond_for_table_from_pred() recognizes that the ON equality can
        be discarded as it's a consequence of using "ref" access
        - this "ref" access is marked as null_rejecting.
        So far so good, and at execution with optimizer_join_cache_level<=4,
        there is no problem, "ref" access is done with join_read_always_key()
        which tests ref->null_rejecting and does not pass t1.b NULL values
        to t2's index lookup.
        At higher levels, JOIN_CACHE_BKA or JOIN_CACHE_BKA_UNIQUE are used
        instead of join_read_always_key(), and they forgot to test
        ref->null_rejecting, so NULL values of t1.b where accidentally matched
        with NULL values of t2.b, leading to wrong results (see comment of
        join_cache.result).
        The fix is to make JOIN_CACHE_BKA and JOIN_CAHE_BKA_UNIQUE observe
        null_rejecting: when they scan the cached rows of the outer table t1,
        collecting key values from those cached rows in order to send them
        to t2's multi-range-read, we now don't collect any NULL key value.
        For this, JOIN_CACHE_BKA gets the same test as join_read_always_key().
        But JOIN_CACHE_BKA_UNIQUE is different, it doesn't have up-to-date
        fields pointed to by "ref", testable with ref->items[i].is_null(); it
        only has a raw key value (left by a previous duplicate-key-eliminating
        phase), so we inspect this key.
     @ sql/sql_select.cc
        comments. The last one is verbose, as failing to observe it has resulted in the present
        bug as well as BUG 46743.
     @ sql/sql_select.h
        A member function to reduce duplicated code.
        A helper which can be used to temporarily reduce a testcase when debugging
        (allows to have one-row tables and still follow the same execution as
        two-row tables).

To John: the test for BUG#54110 uses InnoDB tables. It is normal that opt-backporting would suffer from this bug with InnoDB tables, more than 6.0-codebase-bugfixing: in the first tree, multi-range-read is enabled by default in InnoDB, which makes join buffering possible in the MySQL join execution code; and this bug is in the join buffering code. So in opt-backporting + InnoDB case, we probably go through the faulty code more often.
Still, the only way to quickly know what tree is correct is comparing with 5.1 results.

Guilhem:

I have a situation which is somewhat similar to this. I am not sure about the nitty gritty details of this bug yet, so can you tell if it is the same cause as this bug or Bug#53793, or something else?

------------------------
/*!50600 SET SESSION optimizer_join_cache_level = 8 */;

CREATE TABLE `O` (
  `pk` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`pk`),
);

CREATE TABLE `K` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`)
);

CREATE TABLE `L` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  `col_datetime` datetime DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `test_idx` (`col_int_key`,`pk`) USING HASH
);

INSERT INTO `L` VALUES (49,-49414144,'2006-06-04 00:00:00');
INSERT INTO `L` VALUES (50,NULL,NULL);

SELECT table1 .`col_datetime` 
FROM L table1 
  LEFT  JOIN K table2 
    LEFT  JOIN O table3  ON table2 .`pk` 
  ON table1 .`col_int_key` 
WHERE  6  AND table1 .`col_int_key` OR  7 AND table3 .`pk`;

SELECT  FOUND_ROWS();

DROP TABLE O;
DROP TABLE K;
DROP TABLE L;

------------------------

Diff between 5.1-bugteam and next-mr-opt-backporting (with patch for this bug report applied) is:

 WHERE  6  AND table1 .`col_int_key` OR  7 AND table3 .`pk`;
 col_datetime
 2006-06-04 00:00:00
+NULL
 SELECT  FOUND_ROWS();
 FOUND_ROWS()
-1
+2
 DROP TABLE O;
 DROP TABLE K;
 DROP TABLE L;

Since it needs the WHERE clause to cause a diff, I interpreted above comments as this could be fixed when Bug#53793 is fixed. However, when I read Bug#53793 I get the impression that it merely has a performance/efficiency impact, and not wrong results. So I would appreciate if you would help me become less confused :)

Typo in SQL in previous comment: Please remove comma in 
PRIMARY KEY (`pk`),
for CREATE TABLE `O` to avoid errors.

Hello John. Good catch! I could repeat the problem in your last post (but only if I use InnoDB tables). It is independent of the patch for the present bug#52636 (not introduced by it, not fixed by it). I get the problem only with join_cache_level=4,6,8 (that's another indication that it's not bug#52636, which happens with 5,6,7,8). I suggest opening a new bug report.

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/110238

3185 Guilhem Bichot	2010-06-01
      Fix for BUG#52636 6.0 allowing JOINs on NULL values w/ optimizer_join_cache_level = 5-8
      Optimizer chose "ref" access for the LEFT JOIN ON condition, and considered that this
      access it implemented the condition's semantics; which would have been true if
      JOIN_CACHE_BKA and JOIN_CACHE_BKA_UNIQUE hadn't forgotten to eliminate NULL values.
     @ mysql-test/r/join_cache.result
        Before the fix, the SELECTs would show:
        both first and 2nd SELECT: (100),(NULL); 3rd SELECT: (100),(100);
        both 4th and 5th: (100),(200),(NULL).
        For example 1st SELECT would find no match in t2 for t1's 3 (thus
        emit t2.a=NULL, as it's a left join) and a match for t1's NULL
        (the bug) thus emit t2.a=100.
     @ mysql-test/t/join_cache.test
        test for bug
     @ sql/handler.cc
        After fixing sql_join_cache.cc, if the referenced table (t1 in the
        testcase) has only NULL rows, it can now happen, in
        handler::multi_range_read_next(), that the first call to
        mrr_funcs.next() (== bka_range_seq_next or bka_unique_range_seq_next)
        finds no keys (because it now internally eliminates NULLs); so
        "result" needs to be initialized for this case, otherwise behaviour
        is random (segmentation fault).
     @ sql/sql_join_cache.cc
        What happened in the test's scenario
        SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
        is:
        - plan is to access t1, and then t2 with "ref" access on key t2.b to
        the reference t1.b.
        - make_cond_for_table_from_pred() recognizes that the ON equality can
        be discarded as it's a consequence of using "ref" access
        - this "ref" access is marked as null_rejecting.
        So far so good, and at execution with optimizer_join_cache_level<=4,
        there is no problem, "ref" access is done with join_read_always_key()
        which tests ref->null_rejecting and does not pass t1.b NULL values
        to t2's index lookup.
        At higher levels, JOIN_CACHE_BKA or JOIN_CACHE_BKA_UNIQUE are used
        instead of join_read_always_key(), and they forgot to test
        ref->null_rejecting, so NULL values of t1.b where accidentally matched
        with NULL values of t2.b, leading to wrong results (see comment of
        join_cache.result).
        The fix is to make JOIN_CACHE_BKA and JOIN_CAHE_BKA_UNIQUE observe
        null_rejecting: when they scan the cached rows of the outer table t1,
        collecting key values from those cached rows in order to send them
        to t2's multi-range-read, we now don't collect any NULL key value.
        For this, JOIN_CACHE_BKA gets the same test as join_read_always_key().
        But JOIN_CACHE_BKA_UNIQUE is different, it doesn't have up-to-date
        fields pointed to by "ref", testable with ref->items[i].is_null(); it
        only has a raw key value (left by a previous duplicate-key-eliminating
        phase), so we inspect this key.
     @ sql/sql_select.cc
        comments. The last one is verbose, as failing to observe it has resulted in the present
        bug as well as BUG 46743.
     @ sql/sql_select.h
        A member function to reduce duplicated code.
        A helper which can be used to temporarily reduce a testcase when debugging
        (allows to have one-row tables and still follow the same execution as
        two-row tables).

queued to next-mr-opt-backporting

The problem reported by John a few posts above is now filed as BUG#54235

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)

Bug does not appear in any released 5.6.x version. No 5.6.1 changelog entry needed.