Re: Question about bug 73953

From: Lennart Jonsson <erik.lennart.jonsson_at_gmail.com>
Date: Fri, 10 Oct 2014 08:10:33 +0200
Message-ID: <m17t8n$h8q$1_at_dont-email.me>

On 10/09/2014 10:40 PM, Jerry Stuckle wrote: [...]

> The reply is correct - there is no bug.  When you do "a left join b",
> you will get all of the values from a, whether or not the value exists in b.
> 
> Since that is not what you want, you should do a simple "a join b" instead.
> 

I did not read the article and the comments thoroghly so I might have missed something, but it is an interesting topic. I have used:

case when b.my_val <> 98 then 0 else 1 end as is_x

instead of if since I wanted to compare with other DBMS:s implementation.

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.

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)

is_x evaluates to null due to the left join in the view, but the case expression outside the view evaluates to 1 due to the else. So:

select * from a left join b_view on b_view.a_id = a.id where case when b_view.my_val <> 98 then 0 else 1 end = 1; +----+------+------+--------+------+
| id | id | a_id | my_val | is_x |

+----+------+------+--------+------+
| 1 | 1 | 1 | 98 | 1 |
| 4 | 4 | 4 | 98 | 1 |
| 7 | NULL | NULL | NULL | NULL |
| 8 | NULL | NULL | NULL | NULL |
| 9 | NULL | NULL | NULL | NULL |
| 10 | NULL | NULL | NULL | NULL |

+----+------+------+--------+------+
6 rows in set (0.00 sec)

/Lennart Received on Fri Oct 10 2014 - 08:10:33 CEST