MySQL Bugs: #55900: Pruning for PARTITION BY LIST COLUMNS should take advantage of 2nd+ column

Bug #55900	Pruning for PARTITION BY LIST COLUMNS should take advantage of 2nd+ column
Submitted:	11 Aug 2010 4:16	Modified:	24 Aug 2010 8:42
Reporter:	Janek Schumann	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S4 (Feature request)
Version:	5.5.5-m3	OS:	Windows (Windows 7)
Assigned to:		CPU Architecture:	Any
Tags:	columns, list, multiple columns, partition, pruning

Description:
When defining partitions by using LIST COLUMNS the 2nd column (and later for that matter) should be used for partition pruning if used in a WHERE clause alone. Currently, all partitions will be used (no pruning at all) if the first column used to partition isn't filtered on as well.

The following example shows that:
- Filter on 1st column: only 2 out of 4 partitions are used (CORRECT)
- Filter on both columns: only 1 partition is used (CORRECT)
- Filter on 2nd column: all 4 partitions are used (WRONG, should be only 2)

How to repeat:
CREATE TABLE test (
 type CHAR(16),
 language char(2),
 value int
)
PARTITION BY LIST COLUMNS(type, language)
(
    PARTITION Type1_en VALUES IN (('Type1', 'en')),
    PARTITION Type1_de VALUES IN (('Type1', 'de')),
    PARTITION Type2_en VALUES IN (('Type2', 'en')),
    PARTITION Type2_de VALUES IN (('Type2', 'de'))
);

INSERT INTO Test VALUES 
( 'Type1', 'en', 1), 
( 'Type1', 'de', 2), 
( 'Type2', 'en', 3),
( 'Type2', 'de', 4)
;

EXPLAIN PARTITIONS SELECT * FROM test WHERE type = 'Type1';
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: Type1_en,Type1_de
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where

EXPLAIN PARTITIONS SELECT * FROM test WHERE type = 'Type1' and language = 'en';
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: Type1_en
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where

EXPLAIN PARTITIONS SELECT * FROM test WHERE language = 'en';
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: Type1_en,Type1_de,Type2_en,Type2_de
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where

Thank you for the feature request. Indeed, it would be nice to have pruning in the last case below:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.6-m3-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> drop table test;
ERROR 1051 (42S02): Unknown table 'test'
mysql> CREATE TABLE test (
    ->  type CHAR(16),
    ->  language char(2),
    ->  value int
    -> )
    -> PARTITION BY LIST COLUMNS(type, language)
    -> (
    ->     PARTITION Type1_en VALUES IN (('Type1', 'en')),
    ->     PARTITION Type1_de VALUES IN (('Type1', 'de')),
    ->     PARTITION Type2_en VALUES IN (('Type2', 'en')),
    ->     PARTITION Type2_de VALUES IN (('Type2', 'de'))
    -> );
Query OK, 0 rows affected (0.18 sec)

mysql> 
mysql> INSERT INTO Test VALUES 
    -> ( 'Type1', 'en', 1), 
    -> ( 'Type1', 'de', 2), 
    -> ( 'Type2', 'en', 3),
    -> ( 'Type2', 'de', 4)
    -> ;
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> EXPLAIN PARTITIONS SELECT * FROM test WHERE type = 'Type1' and language = 'en'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: Type1_en
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)

mysql> EXPLAIN PARTITIONS SELECT * FROM test WHERE language = 'en'\G*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: Type1_en,Type1_de,Type2_en,Type2_de
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)

This is WL#4065.