MySQL Bugs: #12859: group_concat in subquery cause incorrect not null

Bug #12859	group_concat in subquery cause incorrect not null
Submitted:	29 Aug 2005 18:59	Modified:	6 Sep 2005 15:52
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.14, 5.0.11	OS:	Windows (Windows, freebsd)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
The first select returns the correct result.

running group_concat on a "not null" column or a constand, inside a subquery fails.
to repeat the table must be empty and no "group by" clause must be given (query will work fine, if a group by is given for the subquery)

the same applies to "create table select ..."

How to repeat:
create table t1 ( a int not null default 0, b int default 0);

select * from (select group_concat(b) from t1) t2;
#+-----------------+
#| group_concat(b) |
#+-----------------+
#| NULL            |
#+-----------------+
#1 row in set (0.00 sec)

select * from (select group_concat(a) from t1) t2;
#ERROR 1048 (23000): Column 'group_concat(a)' cannot be null

select * from (select group_concat('c') from t1) t2;
#ERROR 1048 (23000): Column 'group_concat('c')' cannot be null

create table tnew1 select group_concat('') from t1;
#Query OK, 1 row affected, 1 warning (0.01 sec)
#Records: 1  Duplicates: 0  Warnings: 1

show warnings;
#+---------+------+------------------------------------------------------------------------------+
#| Level   | Code | Message                                                                      |
#+---------+------+------------------------------------------------------------------------------+
#| Warning | 1263 | Data truncated; NULL supplied to NOT NULL column #'group_concat('')' at row 1 |
#+---------+------+------------------------------------------------------------------------------+
#1 row in set (0.00 sec)

create table tnew2 select group_concat(a) from t1;
#Query OK, 1 row affected, 1 warning (0.01 sec)
#Records: 1  Duplicates: 0  Warnings: 1

show warnings;
#+---------+------+-----------------------------------------------------------------------------+
#| Level   | Code | Message                                                                     |
#+---------+------+-----------------------------------------------------------------------------+
#| Warning | 1263 | Data truncated; NULL supplied to NOT NULL column #'group_concat(a)' at row 1 |
#+---------+------+-----------------------------------------------------------------------------+
#1 row in set (0.00 sec)

Suggested fix:
-

Verified on 5.0.11 also. The same applies to views:

mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.0.11-beta-nt |
+----------------+
1 row in set (0.00 sec)

mysql> create view v1 as select group_concat('c') from t1;
Query OK, 0 rows affected (0.00 sec)

mysql> desc v1;
+-------------------+----------+------+-----+---------+-------+
| Field             | Type     | Null | Key | Default | Extra |
+-------------------+----------+------+-----+---------+-------+
| group_concat('c') | longtext | NO   |     |         |       |
+-------------------+----------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> select * from v1;
ERROR 1048 (23000): Column 'group_concat('c')' cannot be null
mysql> create view v2 as select group_concat(b) from t1;
Query OK, 0 rows affected (0.01 sec)

mysql> desc v2;
+-----------------+----------+------+-----+---------+-------+
| Field           | Type     | Null | Key | Default | Extra |
+-----------------+----------+------+-----+---------+-------+
| group_concat(b) | longblob | YES  |     | NULL    |       |
+-----------------+----------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> select * from v2;
+-----------------+
| group_concat(b) |
+-----------------+
| NULL            |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from v1;
ERROR 1048 (23000): Column 'group_concat('c')' cannot be null

The problem is: why it is supposed that, say, group_concat('c') should never return NULLs, while it surely can (for empty table):

mysql> select group_concat('c') from t1;
+-------------------+
| group_concat('c') |
+-------------------+
| NULL              |
+-------------------+
1 row in set (0.00 sec)

Quote from the manual (http://dev.mysql.com/doc/mysql/en/group-by-functions.html):

"This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values."

No values mean no non-NULL values also, isn't it?

can also be experienced with UNION

select group_concat('x') UNION ALL select 1;

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/29327