Description:
If you do the following:
Create a table (e.g. "small")
Create a view (e.g. "small_view") on that table, including an if() command within the view definition.
Create another table (e.g. "big")
Create another view (e.g. "big_view"), left joining the second table onto the first view.
Where there is a row in big which has no row to join to in small, the if() column has a value of the third parameter passed to if(), rather than null.
How to repeat:
---- Run the following script to demonstrate the bug
create database test4;
use test4;
create table small
(
id integer not null,
primary key ( id )
);
create table big
(
id integer not null,
primary key ( id )
);
insert into small values ( 1 ), ( 2 );
insert into big values ( 1 ), ( 2 ), ( 3 ), ( 4 );
create or replace view small_view as select *, if ( id % 2 = 1, 1, 0 ) as is_odd from small;
create or replace view big_view as select big.*, small_view.id as small_id, small_view.is_odd from big left join small_view on small_view.id = big.id;
select * from big_view;
---- Output on my system
mysql> create database test4;
Query OK, 1 row affected (0.00 sec)
mysql> use test4;
Database changed
mysql>
mysql> create table small
-> (
-> id integer not null,
-> primary key ( id )
-> );
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql> create table big
-> (
-> id integer not null,
-> primary key ( id )
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> insert into small values ( 1 ), ( 2 );
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into big values ( 1 ), ( 2 ), ( 3 ), ( 4 );
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>
mysql> create or replace view small_view as select *, if ( id % 2 = 1, 1, 0 ) as
is_odd from small;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> create or replace view big_view as select big.*, small_view.id as small_i
d, small_view.is_odd
-> from big left join small_view on small_view.id = big.id;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> select * from big_view;
+----+----------+--------+
| id | small_id | is_odd |
+----+----------+--------+
| 1 | 1 | 1 |
| 2 | 2 | 0 |
| 3 | NULL | 0 |
| 4 | NULL | 0 |
+----+----------+--------+
4 rows in set (0.00 sec)
---- Comment
The third column should read 1, 0, NULL, NULL.
Description: If you do the following: Create a table (e.g. "small") Create a view (e.g. "small_view") on that table, including an if() command within the view definition. Create another table (e.g. "big") Create another view (e.g. "big_view"), left joining the second table onto the first view. Where there is a row in big which has no row to join to in small, the if() column has a value of the third parameter passed to if(), rather than null. How to repeat: ---- Run the following script to demonstrate the bug create database test4; use test4; create table small ( id integer not null, primary key ( id ) ); create table big ( id integer not null, primary key ( id ) ); insert into small values ( 1 ), ( 2 ); insert into big values ( 1 ), ( 2 ), ( 3 ), ( 4 ); create or replace view small_view as select *, if ( id % 2 = 1, 1, 0 ) as is_odd from small; create or replace view big_view as select big.*, small_view.id as small_id, small_view.is_odd from big left join small_view on small_view.id = big.id; select * from big_view; ---- Output on my system mysql> create database test4; Query OK, 1 row affected (0.00 sec) mysql> use test4; Database changed mysql> mysql> create table small -> ( -> id integer not null, -> primary key ( id ) -> ); Query OK, 0 rows affected (0.04 sec) mysql> mysql> create table big -> ( -> id integer not null, -> primary key ( id ) -> ); Query OK, 0 rows affected (0.01 sec) mysql> mysql> insert into small values ( 1 ), ( 2 ); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into big values ( 1 ), ( 2 ), ( 3 ), ( 4 ); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> mysql> create or replace view small_view as select *, if ( id % 2 = 1, 1, 0 ) as is_odd from small; Query OK, 0 rows affected (0.01 sec) mysql> mysql> create or replace view big_view as select big.*, small_view.id as small_i d, small_view.is_odd -> from big left join small_view on small_view.id = big.id; Query OK, 0 rows affected (0.02 sec) mysql> mysql> select * from big_view; +----+----------+--------+ | id | small_id | is_odd | +----+----------+--------+ | 1 | 1 | 1 | | 2 | 2 | 0 | | 3 | NULL | 0 | | 4 | NULL | 0 | +----+----------+--------+ 4 rows in set (0.00 sec) ---- Comment The third column should read 1, 0, NULL, NULL.