Re: Question about bug 73953

From: Jerry Stuckle <jstucklex_at_attglobal.net>
Date: Sat, 11 Oct 2014 10:43:20 -0400
Message-ID: <m1bfm8$j7b$1_at_dont-email.me>

On 10/11/2014 2:21 AM, Lennart Jonsson wrote:
> On 10/10/2014 05:35 PM, Jerry Stuckle wrote:

>> On 10/10/2014 2:10 AM, Lennart Jonsson wrote:

>
> [...]
>

>>> select * from a left join b_view on b_view.a_id = a.id;
>>> +----+------+------+--------+------+
>>> | id | id   | a_id | my_val | is_x |
>>> +----+------+------+--------+------+
>>> |  1 |    1 |    1 |     98 |    1 |
>>> |  2 |    2 |    2 |     99 |    0 |
>>> |  3 |    3 |    3 |    100 |    0 |
>>> |  4 |    4 |    4 |     98 |    1 |
>>> |  5 |    5 |    5 |     99 |    0 |
>>> |  6 |    6 |    6 |    100 |    0 |
>>> |  7 | NULL | NULL |   NULL | NULL |
>>> |  8 | NULL | NULL |   NULL | NULL |
>>> |  9 | NULL | NULL |   NULL | NULL |
>>> | 10 | NULL | NULL |   NULL | NULL |
>>> +----+------+------+--------+------+
>>> 10 rows in set (0.00 sec)
>>>
>>>
>>> select * from a left join b_view on b_view.a_id = a.id where
>>> b_view.is_x = 1;
>>> +----+------+------+--------+------+
>>> | id | id   | a_id | my_val | is_x |
>>> +----+------+------+--------+------+
>>> |  1 |    1 |    1 |     98 |    1 |
>>> |  4 |    4 |    4 |     98 |    1 |
>>> +----+------+------+--------+------+
>>> 2 rows in set (0.00 sec)
>>>
>>>
>>> MariaDB 5.5.39 works as expected contrary to Luuk's example.
>>>
>>>
>>
>> I would expect this from an INNER JOIN, but not a LEFT JOIN.  LEFT JOIN
>> should return all matching values in "a".  Values in "a" which have no
>> match in "b" should contain NULL.  Since you have no selection criteria
>> on table "a", all values should be returned.
>>

>
> The where clause evaluates after the join so b_view.is_x is NULL at that
> time. I.e. b_view.is_x = 1 evaluates to NULL which disqualifies those
> rows. Compare with:

>

OK, I see now. It helps to look at it with after a good night's sleep :)

> select *
> from a
> left join b_view
> on b_view.a_id = a.id
> where b_view.is_x IS NULL
>
> <=> (almost, but the different is irrelevant for this discussion)
>
> select *
> from a
> where not exists (
> select 1 from b_view
> where b_view.a_id = a.id
> )
>
>

>>>
>>> But, and this is the interesting part:
>>>
>>>
>>> select b_view.my_val, is_x, case when b_view.my_val <> 98 then 0 else 1
>>> end from a left join b_view on b_view.a_id = a.id;
>>> +--------+------+--------------------------------------------------+
>>> | my_val | is_x | case when b_view.my_val <> 98 then 0 else 1 end  |
>>> +--------+------+--------------------------------------------------+
>>> |     98 |    1 |                                                1 |
>>> |     99 |    0 |                                                0 |
>>> |    100 |    0 |                                                0 |
>>> |     98 |    1 |                                                1 |
>>> |     99 |    0 |                                                0 |
>>> |    100 |    0 |                                                0 |
>>> |   NULL | NULL |                                                1 |
>>> |   NULL | NULL |                                                1 |
>>> |   NULL | NULL |                                                1 |
>>> |   NULL | NULL |                                                1 |
>>> +--------+------+--------------------------------------------------+
>>> 10 rows in set (0.00 sec)
u>>>
>>>
>>
>> This one I'd have to think about for a bit.  My first impression would
>> be the result is incorrect because you should never have NULL values on
>> the left side of the LEFT JOIN.  However, since you are joining the same
>> table, it could be valid - the values could be coming from the right
>> side.  Something to play with.
>>

>
> Not sure what you mean here, no values from A (left side) is exposed in
> the query.
>
> [...]
>
>
> But to get back to the original question, I consider the behaviour that
> Luuk describes as a bug (that does not exists in my MariaDB version).
>
>
> /Lennart
>
>
>

You're right.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex_at_attglobal.net
==================

Received on Sat Oct 11 2014 - 16:43:20 CEST