| 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: | |
| 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 | ||
[29 Nov 2009 3:28]
MySQL Verification Team
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.
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) );