Question about bug 73953

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. Received on Thu Oct 09 2014 - 20:19:38 CEST