Bug #12154 SELECT FROM a VIEW fails with NULL column and DISTINCT
Submitted: 25 Jul 2005 14:32 Modified: 8 Aug 2005 15:38
Reporter: Jun Murai Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.9-beta-nt/5.0.11 BK OS:Microsoft Windows (Windows XP and Linux)
Assigned to: Igor Babaev CPU Architecture:Any

[25 Jul 2005 14:32] Jun Murai
Description:
With views shown below, 'select * from view' shows an error 'ERROR 1048 (23000): Column ... cannot be null'. 

How to repeat:
create table goods2price (
  goods int(12) not null,
  price varchar(128) not null );
insert into goods2price values (23, 2340), (26, 9900);

create table goods2shop (
  goods int(12), Name  varchar(50), Shop  char(2) );
insert into goods2shop values 
  (23, 'as300', 'fr'), (26, 'as600', 'fr');

create table groups (
  groupid int(12) not null, goodsid int(12) not null );
insert into groups values (3,23), (6,26);

create table grouplist ( groupid int(12) );
insert into grouplist values (1), (2), (3), (4), (5), (6);

create view vGroup2Goods as
select g.groupid groupid, p.goods goods,  
       p.Name name, p.Shop shop, 
       gp.price price
from (groups g join goods2shop p on g.goodsid = p.goods)
      join goods2price gp on p.goods = gp.goods;

select * from vGroup2Goods;

result:
groupid	goods	name	shop	price
3	23	as300	fr	2340
6	26	as600	fr	9900

create table cvGroup2Goods select * from vGroup2Goods;
  -- this is a copy of vGroup2Goods
  -- vGroup2Goods and cvGroup2Goods look same with 
  -- DESC ... and SELECT * from ...

-- View created from Table

create view mergedOk as 
select distinct g.groupid, fr.price from grouplist g
left join cvGroup2Goods fr on g.groupid = fr.groupid and fr.shop = 'fr';

select * from mergedOk;

this will produce the correct result:
groupid  price
  1	NULL
  2	NULL
  3	2340
  4	NULL
  5	NULL
  6	9900

-- View created from View

create view mergedError as 
select distinct g.groupid, fr.price from grouplist g
left join vGroup2Goods fr on g.groupid = fr.groupid and fr.shop = 'fr';

desc mergedError;

  Field	Type	Null	Default
  groupid	int(11)	YES	NULL	
  price	varchar(128)	YES	NULL	

this DESC shows that NULL is allowed in both groupid and price, but

select * from mergedError;

ERROR 1048 (23000): Column 'price' cannot be null
An error occurs with this statement. 

Executing following statement with unnamed view will also fail.
select * from (
select distinct g.groupid, fr.price
from grouplist g
left join vGroup2Goods fr
on g.groupid = fr.groupid and fr.shop = 'fr') dt; 

Suggested fix:
workarounds: 
No error occured without NULL fields or DISTINCT directive.
In windows XP, no error occures with ALGORITHM=TEMPTABLE. 

----

This test case was created to report another bug in 5.0.7 (fixed on 5.0.9) at first, so it may show other result if used in former version.
[5 Aug 2005 21:53] Igor Babaev
This problem can be demonstrated with a test without using any views:

mysql> CREATE TABLE t1 (goods int(12) NOT NULL, price varchar(128) NOT NULL);
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO t1 VALUES (23, 2340), (26, 9900);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE t2 (goods int(12), name varchar(50), shop char(2));
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t2 VALUES (23, 'as300', 'fr'), (26, 'as600', 'fr');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create table t3 (groupid int(12) NOT NULL, goodsid int(12) NOT NULL);
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO t3 VALUES (3,23), (6,26);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE t4 (groupid int(12));
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO t4 VALUES (1), (2), (3), (4), (5), (6);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM
    -> (SELECT DISTINCT gl.groupid, gp.price
    ->   FROM t4 gl
    ->        LEFT JOIN
    ->        (t3 g INNER JOIN t2 p ON g.goodsid = p.goods
    ->              INNER JOIN t1 gp ON p.goods = gp.goods)
    ->        ON gl.groupid = g.groupid and p.shop = 'fr') t;
ERROR 1048 (23000): Column 'price' cannot be null

The bug manifests itself when nested joins are used in the query directly or indirectly via views and a temprary table is employed to store the results
of a query with a nested outer join.
[5 Aug 2005 23:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27945
[7 Aug 2005 4:03] Igor Babaev
ChangeSet
  1.1951 05/08/05 16:43:35 igor@rurik.mysql.com +3 -0
  mysql_priv.h:
    Fixed bug #12154: a query returned: Column <name> cannot be null.
    The problem was due to a bug in the function setup_table_map:
    the flag maybe_null was set up incorrectly for inner tables of
    nested outer joins.
  join_nested.result, join_nested.test:
    Added a test case for bug #12154.

The fix will appear in 5.0.12.
[8 Aug 2005 15:38] Mike Hillyer
Added to 5.0.12 changelog:

<listitem><para><literal>NULL</literal> column definitions read incorrectly for inner tables of nested outer joins. (Bug #12154)</para></listitem>