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

Better document how RANGE COLUMNS works

    XMLWordPrintable

Details

    Description

      This is dependent on MDEV-16392, since RANGE COLUMNS inherently uses the same behavior as the behavior of the less than comparison operator on ROW objects.

      The current documentation page doesn't completely explain how RANGE COLUMNS partitioning works:

      https://mariadb.com/kb/en/library/range-columns-and-list-columns-partitioning-types/

      For example, let's say that we have the following table:

      CREATE TABLE rc_part_test
      (
      	id int(11) not null auto_increment,
      	a int not null,
      	b datetime not null,
      	PRIMARY KEY (id, a, b)
      )
      PARTITION BY RANGE COLUMNS (a, b)
      (
      	PARTITION p0 VALUES LESS THAN (2,'2015-01-01'),
      	PARTITION p1 VALUES LESS THAN (2,'2016-01-01'),
      	PARTITION p2 VALUES LESS THAN (3,'2015-01-01'),
      	PARTITION p3 VALUES LESS THAN (3,'2016-01-01')
      );
       
      INSERT INTO rc_part_test (a, b) VALUES
      	(1,'2014-01-01 01:00:00'),
      	(2,'2014-02-01 02:00:00'),
      	(1,'2015-02-01 03:00:00'),
      	(2,'2015-02-01 04:00:00');
      

      Some users might expect the third row to go into either p0, p1 or p3, depending on how they think that comparison operators for ROW operators work. The correct answer is that it goes into p0:

      MariaDB [db1]> SELECT * FROM rc_part_test PARTITION (p0);
      +----+---+---------------------+
      | id | a | b                   |
      +----+---+---------------------+
      |  1 | 1 | 2014-01-01 01:00:00 |
      |  2 | 2 | 2014-02-01 02:00:00 |
      |  3 | 1 | 2015-02-01 03:00:00 |
      +----+---+---------------------+
      3 rows in set (0.00 sec)
      

      We can test out a comparison of the row against the partition's range in the following way:

      MariaDB [db1]> SELECT ROW(1, '2015-02-01 03:00:00') < ROW(2,'2015-01-01');
      +-----------------------------------------------------+
      | ROW(1, '2015-02-01 03:00:00') < ROW(2,'2015-01-01') |
      +-----------------------------------------------------+
      |                                                   1 |
      +-----------------------------------------------------+
      1 row in set (0.00 sec)
      

      This is because, as the MySQL documentation states, the less than comparison operator works the following way on ROW objects:

      For row comparisons, (a, b) < (x, y) is equivalent to:
       
      (a < x) OR ((a = x) AND (b < y))
      

      https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_less-than

      So this is equivalent to the following:

      MariaDB [db1]> SELECT (1 < 2) OR ((1 = 2) AND ('2015-02-01 03:00:00' < '2015-01-01'));                                                                                                                                                       +-----------------------------------------------------------------+
      | (1 < 2) OR ((1 = 2) AND ('2015-02-01 03:00:00' < '2015-01-01')) |
      +-----------------------------------------------------------------+
      |                                                               1 |
      +-----------------------------------------------------------------+
      1 row in set (0.00 sec)
      

      Attachments

        Issue Links

          Activity

            People

              JoeCotellese Joe Cotellese
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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