MySQL Bugs: #14850: ERROR 1062 when a quering a view using a Group By on a column that can be null.

Bug #14850	ERROR 1062 when a quering a view using a Group By on a column that can be null.
Submitted:	10 Nov 2005 23:13	Modified:	9 Dec 2005 19:09
Reporter:	Thomas Healy	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.0.17-BK, 5.0.15	OS:	Linux (Linux)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
ERROR 1062 when a quering a view using a Group By on a column that has null values returns ERROR 1062.

How to repeat:
In the following query view_order_detail is a view.  order_no does have null's in it.  This query returns a ERROR 1062.  If I remove the sum function it works, if I replace order_no with another field that is not null it works.

SELECT
    order_no,
   sum(quantity)
FROM
 view_order_detail
GROUP BY
  order_no

Thank you for a bug report. Verified just as described on 5.0.15 and 5.0.17-BK (ChangeSet@1.1957, 2005-11-09 20:31:01+03:00, ...) on Linux:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.17

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>  create table t1(order_no int, quantity int);
Query OK, 0 rows affected (0,01 sec)

mysql> insert into t1 values (1, 10), (1, 2), (2,1), (null, 10), (null,2);
Query OK, 5 rows affected (0,01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select order_no, sum(quantity) from t1 group by order_no;
+----------+---------------+
| order_no | sum(quantity) |
+----------+---------------+
|     NULL |            12 |
|        1 |            12 |
|        2 |             1 |
+----------+---------------+
3 rows in set (0,01 sec)

mysql> create view v1 as select * from t1;
Query OK, 0 rows affected (0,01 sec)

mysql> select order_no, sum(quantity) from v1 group by order_no;
ERROR 1062 (23000): Duplicate entry 'NULL' for key 1
mysql> desc t1;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| order_no | int(11) | YES  |     | NULL    |       |
| quantity | int(11) | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+
2 rows in set (0,00 sec)

mysql> desc v1;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| order_no | int(11) | YES  |     | NULL    |       |
| quantity | int(11) | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+
2 rows in set (0,01 sec)

I see no reason why view should be treated differently than table in such a case. It is a bug.

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

Item_ref's null_value wasn't updated in save_org_in_field() causing reported
error.

Fixed in 5.0.17, cset 1.1952.17.6

Noted in 5.0.17 changelog.