Bug #67300 table left join view, unmatched rows problem where view contains an if
Submitted: 19 Oct 2012 14:29 Modified: 13 Jul 2015 16:57
Reporter: David Norman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.5.14-log, 5.0.97, 5.1.67, 5.5.30, 5.7.1 OS:Any (MS Windows Pro 7, Linux)
Assigned to: CPU Architecture:Any
Tags: IF, null, VIEW

[19 Oct 2012 14:29] David Norman
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.
[29 Nov 2012 21:37] Sveta Smirnova
Thank you for the report.

Verified as described.
[13 Jul 2015 16:57] Paul DuBois
Noted in 5.7.8, 5.8.0 changelogs.

Incorrect results could be produced tor views and derived tables on
the inner side of an outer join and from which non-nullable
expressions such as literals were selected.