MySQL Bugs: #9404: information_schema: Weird error messages with SELECT SUM() ... GROUP BY queries

Bug #9404	information_schema: Weird error messages with SELECT SUM() ... GROUP BY queries
Submitted:	25 Mar 2005 16:05	Modified:	12 May 2005 2:56
Reporter:	Alexander M. Turek	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.3-beta-nightly-20050324	OS:	Linux (Linux 2.6)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
Please have a look at the output below. All three queries should be correct, but the third one - which in a way is a combination of the first two - fails with a strange error:

ERROR 1062 (23000): Duplicate entry 'information_schema' for key 1

I would expect to get such an error when inserting or updating records, but not when performing a SELECT query.

How to repeat:
mysql> SELECT SUM(`DATA_LENGTH`) FROM `information_schema`.`TABLES`;
+--------------------+
| SUM(`DATA_LENGTH`) |
+--------------------+
| 1099038            |
+--------------------+
1 row in set (0.11 sec)

mysql> SELECT `TABLE_SCHEMA` FROM `information_schema`.`TABLES` GROUP BY `TABLE_SCHEMA`;
+--------------------+
| TABLE_SCHEMA       |
+--------------------+
[correct list of all databases that contain tables]
+--------------------+
14 rows in set (0.11 sec)

mysql> SELECT SUM(`DATA_LENGTH`) FROM `information_schema`.`TABLES` GROUP BY `TABLE_SCHEMA`;
ERROR 1062 (23000): Duplicate entry 'information_schema' for key 1

Thanks for your bug report.

Seems to be triggered by the addition of an aggregate
function in the select list.  In the following queries, only
the ones that include an aggregate function fail, even
though all of them have a GROUP BY clause.

mysql> select table_name,count(*) from information_schema.columns group by table_name;
ERROR 1062 (23000): Duplicate entry 'TABLES' for key 1
mysql> select table_name from information_schema.columns group by table_name;+---------------------------------------+
| table_name                            |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| columns_priv                          |
| COLUMN_PRIVILEGES                     |
| db                                    |
| func                                  |
| help_category                         |
| help_keyword                          |
| help_relation                         |
| help_topic                            |
| host                                  |
| KEY_COLUMN_USAGE                      |
| proc                                  |
| procs_priv                            |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| STATISTICS                            |
| t                                     |
| TABLES                                |
| tables_priv                           |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| time_zone                             |
| time_zone_leap_second                 |
| time_zone_name                        |
| time_zone_transition                  |
| time_zone_transition_type             |
| user                                  |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+---------------------------------------+
33 rows in set (0.02 sec)

mysql> select table_schema, table_name,count(*) from information_schema.columns group by table_schema, table_name;
ERROR 1062 (23000): Duplicate entry 'information_schema-SCHEMATA' for key 1
mysql> select table_schema, table_name from information_schema.columns group by table_schema, table_name;
+--------------------+---------------------------------------+
| table_schema       | table_name                            |
+--------------------+---------------------------------------+
| information_schema | CHARACTER_SETS                        |
| information_schema | COLLATIONS                            |
| information_schema | COLLATION_CHARACTER_SET_APPLICABILITY |
| information_schema | COLUMNS                               |
| information_schema | COLUMN_PRIVILEGES                     |
| information_schema | KEY_COLUMN_USAGE                      |
| information_schema | ROUTINES                              |
| information_schema | SCHEMATA                              |
| information_schema | SCHEMA_PRIVILEGES                     |
| information_schema | STATISTICS                            |
| information_schema | TABLES                                |
| information_schema | TABLE_CONSTRAINTS                     |
| information_schema | TABLE_PRIVILEGES                      |
| information_schema | USER_PRIVILEGES                       |
| information_schema | VIEWS                                 |
| mysql              | columns_priv                          |
| mysql              | db                                    |
| mysql              | func                                  |
| mysql              | help_category                         |
| mysql              | help_keyword                          |
| mysql              | help_relation                         |
| mysql              | help_topic                            |
| mysql              | host                                  |
| mysql              | proc                                  |
| mysql              | procs_priv                            |
| mysql              | tables_priv                           |
| mysql              | time_zone                             |
| mysql              | time_zone_leap_second                 |
| mysql              | time_zone_name                        |
| mysql              | time_zone_transition                  |
| mysql              | time_zone_transition_type             |
| mysql              | user                                  |
| test               | t                                     |
+--------------------+---------------------------------------+
33 rows in set (0.02 sec)

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

fixed in 5.0.6 tree

Noted in 5.0.6 changelog.

I have the same problem on the following select:

SELECT
          order_id,
   order_no,
   sum(quantity)
FROM
        view_order_detail

        GROUP BY
           order_id,
   order_no

I am running mysql-max-5.0.15.  Is this problem not fixed in the production code even though it was found in beta?  Please explain.

Could you provide more detailed test case?
i.e.
create table view_order_detail (...)
insetr into view_order_detail ....
select ....
And it's better to report new bug if it is not related to information schema tables.
Thanks!