MySQL Bugs: #47693: VIEW with GROUP BY and WITH ROLLUP causes "Column can not be null" error.

Bug #47693	VIEW with GROUP BY and WITH ROLLUP causes "Column can not be null" error.
Submitted:	28 Sep 2009 22:38	Modified:	6 Mar 2018 17:25
Reporter:	Jon Armstrong	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.5.45	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	aggregation, VIEW, with rollup

Description:
Selecting from a VIEW which involves aggregation and WITH ROLLUP can result in the following error:

ERROR 1048 (23000) at line 45: Column 'pk' cannot be null

The expected output indeed should generate a null as a result of the ROLLUP operation, but this shouldn't be an error.

The following shell script can be used to duplicate the behavior and also contains SQL that adjusts the VIEW slightly to avoid the error.  The adjustment is really not an acceptable solution, but can be used until the behavior is corrected.

The actual and expected output is also shown below:

How to repeat:
#!/bin/sh

/* *********************

Summary: VIEW with GROUP BY and WITH ROLLUP causes "Column can not be null" error.

NOTE: While the output shown is against version 5.1.25-rc-log, this has been verified by others using the most recent (and older) versions.

************************ */

mysql -t -uroot test << EOF

SELECT VERSION();

DROP TABLE IF EXISTS t_view_test;
DROP TABLE IF EXISTS t_view_test2;

CREATE TABLE t_view_test (
   pk   int auto_increment primary key
);

CREATE TABLE t_view_test2 (
   pk   int auto_increment primary key
);

INSERT INTO t_view_test  () VALUES (), (), ();

INSERT INTO t_view_test2 () VALUES (), ();

SELECT * FROM t_view_test;
SELECT * FROM t_view_test2;

CREATE OR REPLACE VIEW v_view_test2 AS
SELECT t1.pk
     , t2.pk AS pk2
 FROM t_view_test AS t1
 CROSS JOIN t_view_test2 AS t2
;

-- The problem follows:
-- This VIEW treats the pk column as updatable (I'm guessing),
-- but also inherits a constraint of NOT NULL.
-- The WITH ROLLUP will cause nulls to be generated in that
-- column and this generates the "Column 'pk' cannot be null" error
-- when the SELECT (from that view) is executed.

CREATE OR REPLACE VIEW v_view_test AS
SELECT pk
     , SUM(pk2) AS pk2
 FROM v_view_test2
GROUP BY pk WITH ROLLUP
;

SELECT * FROM v_view_test;

/*

The above generates the following output:

[armstrong@localhost reports]$ ./tst.sh 
+---------------+
| VERSION()     |
+---------------+
| 5.1.25-rc-log | 
+---------------+
+----+
| pk |
+----+
|  1 | 
|  2 | 
|  3 | 
+----+
+----+
| pk |
+----+
|  1 | 
|  2 | 
+----+
ERROR 1048 (23000) at line 45: Column 'pk' cannot be null
[armstrong@localhost reports]$ 

The expected / correct output at the point of the error is:

+------+------+
| pk   | pk2  |
+------+------+
|    1 |    3 | 
|    2 |    3 | 
|    3 |    3 | 
| NULL |    9 | 
+------+------+

*/

-- The following shows a way to avoid the bug,
-- but this shouldn't be necessary.
-- I suspect the pk column should not be treated as
-- updatable (in the context of GROUP BY or WITH ROLLUP)
-- and should not inherit the NOT NULL constraint.

CREATE OR REPLACE VIEW v_view_test_fix AS
SELECT pk+0 AS pk
     , SUM(pk2) AS pk2
 FROM v_view_test2
GROUP BY pk WITH ROLLUP
;

SELECT * FROM v_view_test_fix;

EOF

Suggested fix:
Don't treat aggregated or grouped columns as updatable, at least when WITH ROLLUP is involved.  Also, the VIEW should not inherit the NOT NULL constraint in this case.

Correction:

The initial /* ... comment ... was intended to be within the MySQL input, not input to the shell */

Sorry about that.

Thank you for the bug report. Verified as described.

mysql 5.1 >SELECT * FROM v_view_test;
ERROR 1048 (23000): Column 'pk' cannot be null
mysql 5.1 >
mysql 5.1 >select version();
+----------------+
| version()      |
+----------------+
| 5.1.39-Win X64 |
+----------------+
1 row in set (0.00 sec)

This is still happening even as recently as 5.1.49.

This specifically happens only when the select joins 2 or more table and then uses a group by with a rollup .

The view creation itself is successful, But an attempt to write a select query on the view will fail.

The error displayed is 

ERROR 1356 (HY000): View 'XXXX' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

No longer repeatable in 5.6.

The original behavior is gone, but is now replaced with another bug.  The expected NULL in the rollup row is now replaced by 0 for the given test-case.

That's incorrect behavior.  According to the 5.7 documentation, NULL is still the expected value to be generated for the grouped column in the final row of the test-case.

Tested in 5.7.5-m15

Reopened based on returning wrong data.

Posted by developer:
 
Fixed in 8.0.4.

Selecting from a view that involved aggregation and WITH ROLLUP could
result in a spurious "Column col_name cannot be null" error.