Bug #49180 Possible to define unusable partitions using RANGE COLUMNS()
Submitted: 28 Nov 2009 20:41 Modified: 11 Dec 2009 16:51
Reporter: Jon Stephens Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.5.0 OS:Linux (opensuse 11.1/x86_64)
Assigned to: Mikael Ronström CPU Architecture:Any
Tags: maxvalue, partition by range columns
Triage: Triaged: D3 (Medium)

[28 Nov 2009 20:41] Jon Stephens
Description:
It's possible using RANGE COLUMNS() partitioning to define a partition that can never be used. See 'How to Repeat' for details.

How to repeat:
It's possible to create the following table:

mysql> CREATE TABLE rc (a INT, b INT)
    -> PARTITION BY RANGE COLUMNS(a,b)
    -> (
    -> PARTITION p0 VALUES LESS THAN (10,5),
    -> PARTITION p1 VALUES LESS THAN (20,10),
    -> PARTITION p2 VALUES LESS THAN (MAXVALUE,15),
    -> PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
    -> );
Query OK, 0 rows affected (0.08 sec)

Now insert a few rows:

mysql> INSERT INTO rc VALUES (0,0), (5,5), (10,10), (15,15), (20,20), (25,25);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

Make sure they're in there:

mysql> SELECT * FROM rc;
+------+------+
| a    | b    |
+------+------+
|    0 |    0 |
|    5 |    5 |
|   10 |   10 |
|   15 |   15 |
|   20 |   20 |
|   25 |   25 |
+------+------+
6 rows in set (0.00 sec)

Which partitions did these rows go into?

mysql> SELECT PARTITION_NAME,TABLE_ROWS
    -> FROM INFORMATION_SCHEMA.PARTITIONS
    -> WHERE TABLE_NAME = 'rc';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          2 |
| p1             |          2 |
| p2             |          2 |
| p3             |          0 |
+----------------+------------+
4 rows in set (0.00 sec)

jon@grindval:~/bin/mysql-trunk/var/test> ls -l rc*MYD
-rw-rw---- 1 jon users 18 2009-11-28 21:18 rc#P#p0.MYD
-rw-rw---- 1 jon users 18 2009-11-28 21:18 rc#P#p1.MYD
-rw-rw---- 1 jon users 18 2009-11-28 21:18 rc#P#p2.MYD
-rw-rw---- 1 jon users  0 2009-11-28 21:17 rc#P#p3.MYD

Suggested fix:
Do not allow MAXVALUE to be used more than once for the *first* column in *column-list* for a table partitioned by RANGE COLUMNS(*column-list*).

A CREATE TABLE that tries to do this should fail in much the same way that this one does:

mysql> CREATE TABLE r (a INT) PARTITION BY RANGE (a)
    -> (PARTITION p0 VALUES LESS THAN (10),
    -> PARTITION p1 VALUES LESS THAN (20),
    -> PARTITION p2 VALUES LESS THAN (MAXVALUE),
    -> PARTITION p3 VALUES LESS THAN (MAXVALUE));
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition

However, we should continue to allow tables such as this one, where MAXVALUE is used more than once for a column *other* than the first one:

CREATE TABLE rc (
    a int, 
    b int
) 
PARTITION BY RANGE COLUMNS(a, b) (
    PARTITION p0 VALUES LESS THAN (5, 5),
    PARTITION p1 VALUES LESS THAN (10, 10),
    PARTITION p2 VALUES LESS THAN (15, MAXVALUE),
    PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);
[29 Nov 2009 3:28] Miguel Solorzano
Thank you for the bug report. Verified as described.
[29 Nov 2009 19:41] Jon Stephens
Looks to me like the appropriate error message should be

Error: 1481 SQLSTATE: HY000  (ER_PARTITION_MAXVALUE_ERROR)

Message: MAXVALUE can only be used in last partition definition
[1 Dec 2009 17:31] Timothy Smith
Possibly related to Bug#48162
[2 Dec 2009 7:10] Mikael Ronström
The correct behaviour should be to compare field by field
as now and when finding that both values contain MAXVALUE
one should report equality immediately independent of the
rest of fields since there is no values that can be
present in the interval between those. The fix is very simple
in compare_column_values return 0 instead of continue when
both values are MAXVALUE.
[2 Dec 2009 7:15] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/92384

2910 Mikael Ronstrom	2009-12-02
      BUG#49180, fixed MAXVALUE problem
[11 Dec 2009 6:01] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091211055901-yp18b3c7xuhl87rf) (version source revid:alik@sun.com-20091211055401-43rjwq7gjed6ds83) (merge vers: 6.0.14-alpha) (pib:13)
[11 Dec 2009 6:03] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091211055436-aagpwm5ac26waw4b) (version source revid:alik@sun.com-20091211055436-aagpwm5ac26waw4b) (merge vers: 5.5.0-beta) (pib:13)
[11 Dec 2009 6:04] Bugs System
Pushed into 5.6.0-beta (revid:alik@sun.com-20091211055628-ltr7fero363uev7r) (version source revid:alik@sun.com-20091211055453-717czhtezc74u8db) (merge vers: 5.6.0-beta) (pib:13)
[11 Dec 2009 16:51] Jon Stephens
This bug didn't appear in any release, so no changelog entry is required.

(Thanks for the quick fix, though!)

Closed without further action.