| 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: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.0.9-beta-nt/5.0.11 BK | OS: | Windows (Windows XP and Linux) |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
[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>
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.