Bug #39338 Fieldnames in INFORMATIONSCHEMA.PARTITIONS.PARTITION_EXPRESSION become unescaped

• Submitted: 9 Sep 2008 11:52
• Modified: 7 Jul 2010 19:45
• Reporter: Mikhail Oleynik
• Status: Closed
• Category: MySQL Server: Information schema
• Severity: S3 (Non-critical)
• Version: 5.1.26, 5.1, 6.0 bzr
• OS: Any (Linux, Windows Vista, XP, 2003 server)
• Assigned to: Mattias Jonsson
• CPU Architecture: Any
• Tags: information_schema, partitions

[9 Sep 2008 11:52] Mikhail Oleynik

Description:
Field names in INFORMATION_SCHEMA.PARTITIONS.PARTITION_EXPRESSION become unescaped. For example if I create table with "PARTITION BY KEY(`ID`,`aaaa,aaaaa`)" clause I'll get "ID,aaaa,aaaaa" as INFORMATION_SCHEMA.PARTITIONS.PARTITION_EXPRESSION field value. As you see there is no way to destinguish `aaaa` and `aaaa,aaaaa` field names.

How to repeat:
If I create table with "PARTITION BY KEY(`ID`,`aaaa,aaaaa`)" clause I'll get "ID,aaaa,aaaaa" as INFORMATION_SCHEMA.PARTITIONS.PARTITION_EXPRESSION field value.

Suggested fix:
Let partitioning expression to be stored as is, without field names unescaping.

[9 Sep 2008 17:33] Valeriy Kravchuk

Thank you for a problem report. Please, send the exact CREATE TABLE statement to use to repeat this problem.

[9 Sep 2008 17:44] Mikhail Oleynik

CREATE TABLE `test_table` (
  `ID` int(11) NOT NULL,
  `aaaa,aaaaa` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
  `ddddddddd` int(11) NOT NULL DEFAULT '0',
  `new_field0` varchar(50),
  PRIMARY KEY(`ID`, `aaaa,aaaaa`, `ddddddddd`)
)
ENGINE=INNODB
PARTITION BY RANGE(`ID`)
  PARTITIONS 3
  SUBPARTITION BY LINEAR KEY(`ID`,`aaaa,aaaaa`)
  SUBPARTITIONS 2 (
    PARTITION `p01` VALUES LESS THAN(100),
    PARTITION `p11` VALUES LESS THAN(200),
    PARTITION `p21` VALUES LESS THAN MAXVALUE
);

SELECT PARTITION_EXPRESSION FROM INFORMATIONSCHEMA.PARTITIONS WHERE TABLE_NAME='test_table';

[9 Sep 2008 20:25] Sveta Smirnova

Thank you for the report.

Verified problem with SUBPARTITION_EXPRESSION with following result:

=====mysql-6.0=====
=====bug39338=====
CREATE TABLE `test_table` (
`ID` int(11) NOT NULL,
`aaaa,aaaaa` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
`ddddddddd` int(11) NOT NULL DEFAULT '0',
`new_field0` varchar(50),
PRIMARY KEY(`ID`, `aaaa,aaaaa`, `ddddddddd`)
)
ENGINE=INNODB
PARTITION BY RANGE(`ID`)
PARTITIONS 3
SUBPARTITION BY LINEAR KEY(`ID`,`aaaa,aaaaa`)
SUBPARTITIONS 2 (
PARTITION `p01` VALUES LESS THAN(100),
PARTITION `p11` VALUES LESS THAN(200),
PARTITION `p21` VALUES LESS THAN MAXVALUE
);
SELECT PARTITION_EXPRESSION, SUBPARTITION_EXPRESSION FROM INFORMATION_SCHEMA.PARTITIONS WHERE
TABLE_NAME='test_table';
PARTITION_EXPRESSION    SUBPARTITION_EXPRESSION
`ID`    ID,aaaa,aaaaa
`ID`    ID,aaaa,aaaaa
`ID`    ID,aaaa,aaaaa
`ID`    ID,aaaa,aaaaa
`ID`    ID,aaaa,aaaaa
`ID`    ID,aaaa,aaaaa

[29 Sep 2008 14:30] Mattias Jonsson

Probably just to use append_identifier in collect_partition_expr in sql_show.cc.

[18 Feb 2009 19:06] 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/commits/66786

2756 Mattias Jonsson	2009-02-18
      Bug#39338: Fieldnames in INFORMATIONSCHEMA.PARTITIONS.PARTITION_EXPRESSION
      become unescaped
      
      Problem was only with the print out of the KEY partitioning list of
      fields, it did not include quotes, even if it was needed.
      
      Fixed by always add quotes if needed.
     @ mysql-test/r/partition.result
        Bug#39338: Fieldnames in INFORMATIONSCHEMA.PARTITIONS.PARTITION_EXPRESSION
        become unescaped
        
        Updated result
     @ mysql-test/t/partition.test
        Bug#39338: Fieldnames in INFORMATIONSCHEMA.PARTITIONS.PARTITION_EXPRESSION
        become unescaped
        
        Added test case
     @ sql/sql_show.cc
        Bug#39338: Fieldnames in INFORMATIONSCHEMA.PARTITIONS.PARTITION_EXPRESSION
        become unescaped
        
        Added quotes if necessary.

[26 May 2010 8:28] Mattias Jonsson

pushed into mysql-trunk-bugfixing and mysql-next-mr-bugfixing

[15 Jun 2010 8:12] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100615080459-smuswd9ooeywcxuc) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

[15 Jun 2010 8:27] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100615080558-cw01bzdqr1bdmmec) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (pib:16)

[7 Jul 2010 19:45] Paul DuBois

Noted in 5.5.5 changelog.

Column names displayed from the PARTITION_EXPRESSION column of the
INFORMATION_SCHEMA.PARTITIONS table did not include escape characters
as necessary.