[MDEV-8348] Add catchall to all table partitioning for list partitions Created: 2015-06-22  Updated: 2020-04-06  Resolved: 2016-09-07

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Fix Version/s: 10.2.2

Type: Task Priority: Minor
Reporter: Norbert van Nobelen Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: Compatibility

Attachments: Zip Archive MDEV-8348.zip    
Issue Links:
PartOf
is part of MDEV-10137 Providing compatibility to other data... Open
Relates
relates to MDEV-10760 Faster adding PARTITIONs in the table... Open
relates to MDEV-10763 Wrong result - server does not return... Closed
relates to MDEV-10765 Wrong result - query does not retriev... Closed
relates to MDEV-11681 PARTITION BY LIST COLUMNS with defaul... Closed
relates to MDEV-12395 DROP PARTITION does not work as expec... Closed
relates to MDEV-22163 Adding OVERFLOW partition Open
Epic Link: Oracle Compatibility
Sprint: 10.2.2-1, 10.2.2-2

 Description   

Not all table partitioning has the ability to add a so called "catch all". The catch all is present in range partitioning:
partition last values less than(MAXVALUE)

The proposal is to add similar statements to list partitioning: Proposed syntax:
partition last values not in other

Benefits: No more insert exceptions, giving the DBA more choices in applying this partitioning with less effort.

Oracle uses the following syntax for this:

CREATE TABLE h2 (c1 NUMBER,c2 NUMBER)
PARTITION BY LIST(c1) (
PARTITION p0 VALUES (1, 4, 7),
PARTITION p1 VALUES (2, 5, 8),
PARTITION p3 VALUES(DEFAULT)

We should probably use the DEFAULT syntax for this.

 Comments   
Comment by Oleksandr Byelkin [ 2016-08-24 ]

DEFAULT looks ok.
There can not be DEFAULT partition for HASH and KEY partition types (If they can I can find why).

Comment by Oleksandr Byelkin [ 2016-08-24 ]

VALUES LESS THAN (MAXVALUE) is effectively DEFAULT partition for RANGE partition so probably it has sens to make syntax sugar here.

Comment by Oleksandr Byelkin [ 2016-08-24 ]

Actual LIST partitioning syntax in MariaDB is

PARTITION BY LIST (partitioning_expression)
 
(
 
	PARTITION partition_name VALUES IN (value_list),
 
	[ PARTITION partition_name VALUES IN (value_list), ... ]
 
)

IMHO following syntax is what correspond to it:

PARTITION BY LIST (partitioning_expression)
 
(
 
	PARTITION partition_name VALUES IN (value_list),
 
	[ PARTITION partition_name VALUES IN (value_list), ... ]
 
        [ PARTITION partition_name DEFAULT ]
 
)

"PARTITION partition_name VALUES IN (DEFAULT)" looks strange IMHO.

Comment by Oleksandr Byelkin [ 2016-08-24 ]

For beginning prune_partitions() should always mark default partition as used.

After discussing it with Sergei Petrunia we decided that with current approach it will be done automatically.

Comment by Oleksandr Byelkin [ 2016-08-25 ]

As far as several columns expression possible, we have to support
PARTITION p1 VALUES IN ((default, 2))
hopefully it will not conflict with default values of correspondent column in users understanding...

Comment by Oleksandr Byelkin [ 2016-08-26 ]

Taking into account complications with deciding where to put record (2,3) when we have partitions (DEFAULT,3) and (2, DEFAULT) it will be better to have only one partition which catch all missed records. So syntax will be following:

PARTITION BY LIST (partitioning_expression)
 
(
 
	PARTITION partition_name VALUES IN (value_list),
 
	[ PARTITION partition_name VALUES IN (value_list), ... ]
 
        [ PARTITION partition_name DEFAULT ]
 
)


PARTITION BY LIST COLUMNS(column_list)
 
(
 
	PARTITION partition_name VALUES IN ((value_list),(value_list),...),
 
	[ PARTITION partition_name VALUES IN ((value_list),(value_list), ...) ]
 
        [ PARTITION partition_name DEFAULT ]
 
)

Comment by Oleksandr Byelkin [ 2016-09-02 ]

revision-id: 442a3d4766f9115bfd496dd4fafd38f7aeb0e273 (mariadb-10.2.1-52-g442a3d4)
parent(s): 1eb58ff3b8569d7dad1f5c180a5e55683e53d205
committer: Oleksandr Byelkin
timestamp: 2016-09-02 15:09:12 +0200
message:

MDEV-8348: Add catchall to all table partitioning for list partitions

DEFAULT partition support added to LIST and LIST COLUMN partitioning.
Partitions Prunning added for DEFAULT partititon.

—

Comment by Oleksandr Byelkin [ 2016-09-06 ]

revision-id: 06310902e5754fc2ead73d903267bb9fa85b85b2 (mariadb-10.2.1-52-g0631090)
parent(s): 1eb58ff3b8569d7dad1f5c180a5e55683e53d205
committer: Oleksandr Byelkin
timestamp: 2016-09-06 22:52:39 +0200
message:

MDEV-8348: Add catchall to all table partitioning for list partitions

DEFAULT partition support added to LIST and LIST COLUMN partitioning.
Partitions Prunning added for DEFAULT partititon.

—

Comment by Krishnadas [ 2016-10-04 ]

DBS test cases for MDEV-8348 MDEV-8348.zip

Generated at Thu Feb 08 07:26:29 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.