Path: news.netfront.net!goblin3!goblin.stu.neva.ru!news.roellig-ltd.de!open-news-network.org!news.swapon.de!eternal-september.org!feeder.eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail From: tony@mountifield.org (Tony Mountifield) Newsgroups: comp.databases.mysql Subject: Re: Question about bug 73953 Date: Fri, 10 Oct 2014 15:47:28 +0000 (UTC) Organization: Software Insight Ltd., Winchester, UK Lines: 135 Message-ID: References: <5436d1ba$0$2951$e4fe514c@news.xs4all.nl> Injection-Info: mx02.eternal-september.org; posting-host="09af9bc7e07dd1219c95b5a7ae50821c"; logging-data="26528"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+jVRWQ3VDCha25YKuxsvorn70Q4zEzyw8=" Originator: tony@softins.co.uk () X-Newsreader: trn 4.0-test77 (Sep 1, 2010) Cancel-Lock: sha1:KWWBcvCHXSwYdGd280VODVvXm7Q= Xref: news.netfront.net comp.databases.mysql:2182 In article , Jerry Stuckle wrote: > On 10/9/2014 2:19 PM, Luuk wrote: > > I would like to post this bug here to find out what you all thing about > > this bug. > > > > Below is the first post of this bug, which should be, besided your > > knowledge of MySQL, be enough input for your opinion. > > > > My, and other responses to this bug report can be found here: > > http://bugs.mysql.com/73953 > > > > > > <== first post of this bug report ==> > > Description: > > If you create a view with an inequality in, then left join to that view, > > and test the value of the inequality in the where clause, you get too > > many rows back. > > > > How to repeat: > > Run the following: > > > > create table a > > ( > > id integer not null, > > primary key ( id ) > > ); > > > > create table b > > ( > > id integer not null, > > a_id integer not null, > > my_val integer not null, > > primary key ( id ), > > foreign key ( a_id ) references a( id ) > > ); > > > > insert into a values ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 ), ( 6 ), ( 7 ), ( > > 8 ), ( 9 ), ( 10 ); > > > > insert into b values ( 1, 1, 98 ), ( 2, 2, 99 ), ( 3, 3, 100 ), ( 4, 4, > > 98 ), ( 5, 5, 99 ), ( 6, 6, 100 ); > > > > create or replace view b_view as select b.id, b.a_id, b.my_val, if ( > > b.my_val <> 98, 0, 1 ) as is_x from b; > > > > select * from b_view; > > > > You get the following output: > > > > +----+------+--------+------+ > > | id | a_id | my_val | is_x | > > +----+------+--------+------+ > > | 1 | 1 | 98 | 1 | > > | 2 | 2 | 99 | 0 | > > | 3 | 3 | 100 | 0 | > > | 4 | 4 | 98 | 1 | > > | 5 | 5 | 99 | 0 | > > | 6 | 6 | 100 | 0 | > > +----+------+--------+------+ > > > > As expected, every row in b is listed, with is_x showing 1 for rows 1 > > and 4, and 0 for the other four rows. All OK so far. But > > > > select a.id from a left join b_view on b_view.a_id = a.id where > > b_view.is_x = 1; > > > > +----+ > > | id | > > +----+ > > | 1 | > > | 4 | > > | 7 | > > | 8 | > > | 9 | > > | 10 | > > +----+ > > > > This should only output two rows - rows 1 and 4. These are the only rows > > for which is_x = 1 in b_view. > > > > To prove I'm not doing something stupid, make a new table, copy the data > > from b_view into it, and do the equivalent query. > > > > create table c > > ( > > id integer not null, > > a_id integer not null, > > my_val integer not null, > > is_x integer not null, > > primary key ( id ), > > foreign key ( a_id ) references a( id ) > > ); > > > > insert into c select * from b_view; > > > > select a.id from a left join c on c.a_id = a.id where c.is_x = 1; > > > > +----+ > > | id | > > +----+ > > | 1 | > > | 4 | > > +----+ > > > > The result above should be the same as this, since c and b_view contain > > identical rows, and the query is the same. > > Luuk, > > 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. But that set of rows is then filtered on the "where b_view.is_x = 1", which should throw away those rows from a that didn't have a matching b_view row (for which b_view.is_x would be NULL), as well as those whose matching b_view row had is_x <> 1. To clarify, the test query was: select a.id from a left join b_view on b_view.a_id = a.id WHERE b_view.is_x = 1; and not: select a.id from a left join b_view on b_view.a_id = a.id AND b_view.is_x = 1; So I think the above report of a bug is correct. Your reply also doesn't address the difference between the result set with b_view and that with c. Cheers Tony -- Tony Mountifield Work: tony@softins.co.uk - http://www.softins.co.uk Play: tony@mountifield.org - http://tony.mountifield.org