List:Internals« Previous MessageNext Message »
From:Øystein Grøvlen Date:September 24 2009 3:29pm
Subject:Re: Bug#47217: Simple Inner join gives wrong result when order by is
used
View as plain text  
Hi again,

A follow-up on my previous email on this.  I have made a patch based on 
alternative 3 in my email.  That is, I have changed 
add_ref_to_table_cond() to use Item_func_eq instead of Item_func_equal 
for the condition for const references if the reference is null_rejecting.

When I made the patch (http://lists.mysql.com/commits/84495), my idea 
was to use "join_tab->keyuse[i].null_rejecting" to check this condition. 
  However, I now see that add_not_null_conds() check the same in another 
way: "join_tab->ref.null_rejecting & (1 << i)"

It seems the same information is duplicated in both JOIN_TAB::keyuse and 
JOIN_TAB::ref as indicated by the comment above add_not_null_conds(). 
Do anyone know the reason for this duplication? Is one of them to be 
more trusted than the other?

Thanks,

--
Øystein

Øystein Grøvlen wrote:
> 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;
> 
> t1 contains two rows, one where the join key (v) is null, the other
> with a non-null join key.  t2 contains a single row where the join key
> (v) is null.  The issue is that for the 6.0 version of MySQL, the
> result contains a single row generated from the rows with NULL values
> for the join key.  The correct result, as given by 5.1, is an empty
> result set.
> 
> The query plan is included below.  As far as I understand, it handles
> t2 as a const table with a single row and replaces t2.v with NULL in
> the join condition and uses this for selection during filesort of t1.
> However, the equality operator used in that condition regards NULL
> values as equal.  (This is where 5.1 differ.  See bug report for
> details.)
> 
> I had expected that add_not_null_conds() would save the day by putting
> a not null condition on t1.v, but it turns out that it only puts such
> a condition on t2.v.  Since t2 is a const table, this condition is
> never evaluated.
> 
> I see several ways this problem could be solved, and I would like some
> advice on what option to choose:
> 
> 1. Choose another plan for such queries.  In this particular scenario
>    there are indexes on both v columns that could be used.
> 
> 2. Handle t2 as a zero row const table. Since the single t2 row has
>    null for t2.v, we know it should not be part of the join result.
> 
> 3. Use another comparison operator for the filesort select
>    condition. (Item_func_eq instead of Item_func_equal)
> 
> 4. Reintroduce 5.1 behavior by setting and checking the HA_NULL_PART
>    flag of key_part.
> 
> 5. Make add_not_null_conds() set a not null condition on t1.
>    Currently, not null conditions are only set on columns referred to
>    by non-const tables.
> 
> There are probably other options as well.  Any recommendation on which
> path to follow?
> 
> Thanks,
> 
> -- 
> Øystein
> 
> mysql> explain select * from t1 join t2 on t1.v = t2.v order by 1\G
> *************************** 1. row ***************************
>            id: 1
>   select_type: SIMPLE
>         table: t2
>          type: system
> possible_keys: ix2
>           key: NULL
>       key_len: NULL
>           ref: NULL
>          rows: 1
>         Extra: Using filesort
> *************************** 2. row ***************************
>            id: 1
>   select_type: SIMPLE
>         table: t1
>          type: ref
> possible_keys: ix1
>           key: ix1
>       key_len: 5
>           ref: const
>          rows: 1
>         Extra: Using where
> 2 rows in set (0.00 sec)
> 


-- 
Øystein Grøvlen, Senior Staff Engineer
Sun Microsystems, Database Group
Trondheim, Norway
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