Re: Question about bug 73953

In article <m16rrb$3pb$1_at_dont-email.me>, Jerry Stuckle <jstucklex_at_attglobal.net> 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_at_softins.co.uk - http://www.softins.co.uk
Play: tony_at_mountifield.org - http://tony.mountifield.org