List:Internals« Previous MessageNext Message »
From:Guilhem Bichot Date:September 21 2009 12:48pm
Subject:Re: Bug#47217: Simple Inner join gives wrong result when order by is
used
View as plain text  
Hi Oysten,

Øystein Grøvlen a écrit, Le 21.09.2009 11:05:
> Hi,
> 
> I am looking into Bug#47217 where a simple join goes wrong because
> NULL values for the join keys are treated as equal.  The join query is
> as follows:
> 
>   select * from t1 join t2 on t1.v = t2.v order by 1;

In the bug report you mentioned:

**************
  [18 Sep 16:25] Oystein Grovlen

The different behavior in 5.1 and 6.0 is related to the following 
changed code in
part_of_refkey:

       if (field->eq(key_part->field) &&
-	  !(key_part->key_part_flag & (HA_PART_KEY_SEG | HA_NULL_PART)))
+	  !(key_part->key_part_flag & HA_PART_KEY_SEG))
	return table->reginfo.join_tab->ref.items[part];

In 5.1, HA_NULL_PART is set and no ref_item is returned so that 
test_if_ref does not find
references that can be eliminated.  In 6.0, HA_NULL_PART is neither set 
nor tested for.
**************

And it's interesting, because I hit the same exact line when debugging 
my BUG#46743 "Azalea processing correlated, aggregate SELECT subqueries 
incorrectly". I found that if I put back HA_NULL_PART, my bug goes away. 
But I didn't decide that it was a correct solution, because if I create 
a tree of MySQL 6.0 code at the revision which introduced this change
(bzr branch -r revid:sp1r-sergefp@stripped), the 
test passes; which means that at that moment, removing HA_NULL_PART 
didn't introduce my bug. So the bug must have happened as an effect of 
"something later".

-- 
Mr. Guilhem Bichot <guilhem@stripped>
Sun Microsystems / MySQL, Lead Software Engineer
Bordeaux, France
www.sun.com / www.mysql.com
Thread
Bug#47217: Simple Inner join gives wrong result when order by is usedØystein Grøvlen21 Sep
  • Re: Bug#47217: Simple Inner join gives wrong result when order by isusedGuilhem Bichot21 Sep
  • Re: Bug#47217: Simple Inner join gives wrong result when order by isusedØystein Grøvlen24 Sep