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

[25 Mar 2005 16:05] Alexander M. Turek
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
[25 Mar 2005 16:16] Jorge del Conde
Thanks for your bug report.
[22 Apr 2005 18:14] Paul Dubois
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)
[9 May 2005 11:25] 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/24727
[9 May 2005 13:39] Sergei Glukhov
fixed in 5.0.6 tree
[12 May 2005 2:56] Paul Dubois
Noted in 5.0.6 changelog.
[10 Nov 2005 22:19] Thomas Healy
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.
[11 Nov 2005 8:57] Sergei Glukhov
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!