Bug #90766 Select by SubPartition not working
Submitted: 5 May 2018 21:26 Modified: 5 Jun 2018 22:38
Reporter: James Harbal Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.4 OS:Ubuntu
Assigned to: CPU Architecture:Any

[5 May 2018 21:26] James Harbal
Description:
Trying to select data with with subpartitions fails, only able to select with name of main partition.

name of subpartition shows up in 'explain select from table;'

How to repeat:

CREATE TABLE example (
  `name` varchar(15) NOT NULL,
 `ts` datetime not null
  KEY `idx` (`name`,`ts`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (dayofyear(`ts`))
SUBPARTITION BY KEY (name)
SUBPARTITIONS 20
(PARTITION p93 VALUES LESS THAN (93) ENGINE = InnoDB,
 PARTITION p182 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

SELECT * FROM example PARTITION (p93_p93sp0);

Error: unknown partition p93_p93sp0;

Select * FROM example PARTITION (p93); -> success but not quite
[5 May 2018 21:34] James Harbal
Decided to remove first p93 part and it works,
so when showing explain partition, partitions that are listed are incorrect, or show full path instead of whats required for partition query.
[5 May 2018 22:38] MySQL Verification Team
Thank you for the bug report. The create table in the how to repeat worked for you?

Your MySQL connection id is 9
Server version: 8.0.12 Source distribution 2018-APR-29

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 8.0 > USE test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql 8.0 > CREATE TABLE example (
    ->   `name` varchar(15) NOT NULL,
    ->  `ts` datetime not null
    ->   KEY `idx` (`name`,`ts`),
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    -> PARTITION BY RANGE (dayofyear(`ts`))
    -> SUBPARTITION BY KEY (name)
    -> SUBPARTITIONS 20
    -> (PARTITION p93 VALUES LESS THAN (93) ENGINE = InnoDB,
    ->  PARTITION p182 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`idx` (`name`,`ts`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE ' at line 4

Thanks.
[6 Jun 2018 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".