Path: news.netfront.net!goblin1!goblin.stu.neva.ru!eternal-september.org!feeder.eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail From: Lennart Jonsson Newsgroups: comp.databases.mysql Subject: Re: Question about bug 73953 Date: Fri, 10 Oct 2014 08:10:33 +0200 Organization: A noiseless patient Spider Lines: 94 Message-ID: References: <5436d1ba$0$2951$e4fe514c@news.xs4all.nl> Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8bit Injection-Date: Fri, 10 Oct 2014 06:10:31 +0000 (UTC) Injection-Info: mx02.eternal-september.org; posting-host="7e2da11d2f2fa04799963f04fc962890"; logging-data="17690"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/wpucgyFasPMYzvF2Np43Y" User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:31.0) Gecko/20100101 Thunderbird/31.1.0 In-Reply-To: Cancel-Lock: sha1://m2pyCKoF5LMWcfhuGgHXm8kUk= X-TagToolbar-Keys: D20141010081032995 Xref: news.netfront.net comp.databases.mysql:2180 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