Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8348

Add catchall to all table partitioning for list partitions

Details

    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.

      Attachments

        Issue Links

          is part of

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-10137 Providing compatibility to other databases

          • Major - Major loss of function.
          • Open
          relates to

          Task - A task that needs to be done. MDEV-10760 Faster adding PARTITIONs in the table with DEFAULT partition (ALTER TABLE ADD PARTITION)

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-10763 Wrong result - server does not return NULL values from default list partition after ALTER table

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-10765 Wrong result - query does not retrieve values from default partition on a table partitioned by list columns

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11681 PARTITION BY LIST COLUMNS with default partition: Assertion `part_info->num_list_values' failed in get_part_iter_for_interval_cols_via_map

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-12395 DROP PARTITION does not work as expected when table has DEFAULT LIST partition

          • Major - Major loss of function.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-22163 Adding OVERFLOW partition

          • Major - Major loss of function.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            Norbert van Nobelen Norbert van Nobelen created issue -
            monty Michael Widenius made changes -
            Field Original Value New Value
            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.

            		 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.
            monty Michael Widenius made changes -
            monty Michael Widenius made changes -
            Assignee Oleksandr Byelkin [ sanja ]
            alvinr Alvin Richards (Inactive) made changes -
            Labels Compatibility
            ratzpo Rasmus Johansson (Inactive) made changes -
            Sprint Compatibility-1 [ 88 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Fix Version/s 10.2 [ 14601 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Sprint Compatibility-1 [ 88 ] 10.2.2-1 [ 89 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Rank Ranked higher
            sanja Oleksandr Byelkin made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            sanja Oleksandr Byelkin added a comment - - edited

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

            sanja Oleksandr Byelkin added a comment - - edited DEFAULT looks ok. There can not be DEFAULT partition for HASH and KEY partition types (If they can I can find why).
            sanja Oleksandr Byelkin added a comment - - edited

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

            sanja Oleksandr Byelkin added a comment - - edited VALUES LESS THAN (MAXVALUE) is effectively DEFAULT partition for RANGE partition so probably it has sens to make syntax sugar here.
            sanja Oleksandr Byelkin added a comment -

            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.

            sanja Oleksandr Byelkin added a comment - 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.
            sanja Oleksandr Byelkin added a comment - - edited

            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.

            sanja Oleksandr Byelkin added a comment - - edited 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.
            sanja Oleksandr Byelkin added a comment -

            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...

            sanja Oleksandr Byelkin added a comment - 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...
            sanja Oleksandr Byelkin added a comment - - edited

            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 ]
            		 
            )

            sanja Oleksandr Byelkin added a comment - - edited 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 ] )
            ratzpo Rasmus Johansson (Inactive) made changes -
            Sprint 10.2.2-1 [ 89 ] 10.2.2-1, 10.2.2-2 [ 89, 92 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Rank Ranked lower
            sanja Oleksandr Byelkin added a comment -

            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.

            —

            sanja Oleksandr Byelkin added a comment - 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. —
            sanja Oleksandr Byelkin made changes -
            Assignee Oleksandr Byelkin [ sanja ] Michael Widenius [ monty ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            sanja Oleksandr Byelkin added a comment -

            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.

            —

            sanja Oleksandr Byelkin added a comment - 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. —
            sanja Oleksandr Byelkin made changes -
            sanja Oleksandr Byelkin made changes -
            Assignee Michael Widenius [ monty ] Oleksandr Byelkin [ sanja ]
            sanja Oleksandr Byelkin made changes -
            Status In Review [ 10002 ] Stalled [ 10000 ]
            sanja Oleksandr Byelkin made changes -
            Fix Version/s 10.2.3 [ 22115 ]
            Fix Version/s 10.2 [ 14601 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            sanja Oleksandr Byelkin made changes -
            Fix Version/s 10.2.2 [ 22013 ]
            Fix Version/s 10.2.3 [ 22115 ]
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            alvinr Alvin Richards (Inactive) made changes -
            Labels Compatibility Approved Compatibility
            alvinr Alvin Richards (Inactive) made changes -
            Labels Approved Compatibility Compatibility
            alvinr Alvin Richards (Inactive) made changes -
            Labels Compatibility Approved Compatibility
            alvinr Alvin Richards (Inactive) made changes -
            Epic Link MDEV-10872 [ 58182 ]
            Krishnadas Krishnadas made changes -
            Attachment MDEV-8348.zip [ 42679 ]
            Krishnadas Krishnadas added a comment -

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

            Krishnadas Krishnadas added a comment - DBS test cases for MDEV-8348 MDEV-8348.zip
            alvinr Alvin Richards (Inactive) made changes -
            Labels Approved Compatibility Approved Compatibility NRE-307517
            alvinr Alvin Richards (Inactive) made changes -
            NRE Projects NRE-307517
            alvinr Alvin Richards (Inactive) made changes -
            Labels Approved Compatibility NRE-307517 Approved Compatibility
            alvinr Alvin Richards (Inactive) made changes -
            NRE Approved Yes [ 10304 ]
            alvinr Alvin Richards (Inactive) made changes -
            Labels Approved Compatibility Compatibility
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            monty Michael Widenius made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 70100 ] MariaDB v4 [ 132638 ]

            People

              sanja Oleksandr Byelkin
              Norbert van Nobelen Norbert van Nobelen
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.