[#MDEV-16393] Better document how RANGE COLUMNS works

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)

[MDEV-16393] Better document how RANGE COLUMNS works Created: 2018-06-04 Updated: 2023-12-15
Status:	Open
Project:	MariaDB Server
Component/s:	Documentation - Support, Partitioning
Fix Version/s:	N/A

[MDEV-16393] Better document how RANGE COLUMNS works Created: 2018-06-04 Updated: 2023-12-15