Bug #9681 rollup in from-subquery returns error "collumn cannot be null"
Submitted: 6 Apr 2005 15:28 Modified: 28 Apr 2005 15:41
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.1.11 OS:Any (*)
Assigned to: Igor Babaev CPU Architecture:Any

[6 Apr 2005 15:28] Martin Friebe
Description:
If the column that is used in the subquery's "group by" is declared or assumed "not null", the select will fail, because the rollup produces a NULL value.

This is similiar to bug 9360.

How to repeat:
select * from ( select a,  sum(a) m from (select 1 a union select 2  ) t1 group by a with rollup ) t2;

ERROR 1048 (23000): Column 'a' cannot be null

# with a table

create table t1 (a int(11) not null);
insert into t1 values (1),(2);
select * from ( select a,  sum(a) m from  t1 group by a with rollup ) t2;

ERROR 1048 (23000): Column 'a' cannot be null

Suggested fix:
-
[19 Apr 2005 23:54] 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/24154
[25 Apr 2005 23:46] Igor Babaev
ChangeSet
  1.2199 05/04/19 16:54:30 igor@rurik.mysql.com +3 -0
  sql_select.cc:
    Fixed bug #9681.
    The bug happened with queries using derived tables specified by
    a SELECT with ROLLUP, such as:
    SELECT * FROM (SELECT a, SUM(a) FROM t1 GROUP BY a WITH ROLLUP) t2,
    if column a of table t1 is declared as NOT NULL.
    This was to the fact that the first column of the temporary table
    created to contain the derived table erroneously inherited the NOT NULL
    attribute from column a.
  olap.result, olap.test:
    Added a test case for bug #9681.

The fix will appear in version 4.1.12 and 5.0.5
[28 Apr 2005 15:41] Paul Dubois
Noted in 4.1.12, 5.0.5 changelogs.