| Bug #29258 | Partitions: search fails for maximum unsigned bigint | ||
|---|---|---|---|
| Submitted: | 21 Jun 2007 0:48 | Modified: | 13 Dec 2007 12:28 |
| Reporter: | Peter Gulutzan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S3 (Non-critical) |
| Version: | 5.1.20-beta-debug | OS: | Linux (SUSE 10 64-bit) |
| Assigned to: | Ramil Kalimullin | CPU Architecture: | Any |
| Tags: | insert, maxvalue, partitioning | ||
[21 Jun 2007 1:32]
MySQL Verification Team
Thank you for the bug report. Verified as described on FC 6 32-bit.
[27 Sep 2007 21:26]
Sergey Petrunya
Doesn't repeat on 5.1.23-beta-debug:
mysql> SELECT * FROM t1 WHERE s1 =18446744073709551614;
+----------------------+
| s1 |
+----------------------+
| 18446744073709551614 |
+----------------------+
1 row in set (0.01 sec)
mysql> SELECT * FROM t1 WHERE s1 =18446744073709551614;
+----------------------+
| s1 |
+----------------------+
| 18446744073709551614 |
+----------------------+
1 row in set (0.01 sec)
mysql> explain partitions SELECT * FROM t1 WHERE s1 =18446744073709551614\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: p3
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: Using where
1 row in set (0.01 sec)
[27 Sep 2007 22:15]
Sergey Petrunya
The bug also doesn't repeat on 5.1.20 compiled from bk. I've compiled with compile-pentium-debug-max on Linux x86. Tip changeset: ChangeSet@1.2512, 2007-06-26 13:15:43+02:00, tsmith@maint1.mysql.com +26 -0 Bug #29245: Bad Merge Caused Error Codes Conflict between 5.0/5.1 Fix some error messages so that all error codes are equivalent in 5.0 and 5.1 TAG: mysql-5.1.20 TAG: mysql-5.1.20-beta
[27 Sep 2007 22:45]
Sergey Petrunya
Also cannot repeat on 5.1.20-beta release build, compiled with BUILD/compile-pentium-max (tip cset is as indicated in previous comment)
[27 Sep 2007 22:45]
Sergey Petrunya
Setting back to Open. Miguel could you please provide exact instructions on how to repeat?
[27 Sep 2007 23:03]
MySQL Verification Team
Still happens on FC 6 32-bit.
Sergey just applied the original how to repeat:
[miguel@skybr 5.1]$ bin/mysql -uroot db8
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.23-beta-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE t1 (s1 BIGINT UNSIGNED)
-> ENGINE=MyISAM
-> PARTITION BY RANGE (s1)
-> (PARTITION p3 VALUES LESS THAN MAXVALUE);
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> INSERT INTO t1 VALUES (18446744073709551614),(18446744073709551615);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT * FROM t1 WHERE s1 =18446744073709551614;
+----------------------+
| s1 |
+----------------------+
| 18446744073709551614 |
+----------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM t1 WHERE s1 =18446744073709551615;
Empty set (0.00 sec)
[18 Oct 2007 21:42]
Jon Stephens
My results using 5.1.23-bk built 2007-10-13. OS is SuSE 10.2/32-bit.
mysql> CREATE TABLE t4 (s1 BIGINT UNSIGNED)
-> PARTITION BY RANGE (s1)
-> (PARTITION p3 VALUES LESS THAN MAXVALUE);
Query OK, 0 rows affected (0.09 sec)
flundra:/usr/local/mysql/var/test # ls t4* -l
-rw-rw---- 1 mysql users 8556 2007-10-18 23:29 t4.frm
-rw-rw---- 1 mysql users 24 2007-10-18 23:29 t4.par
-rw-rw---- 1 mysql users 0 2007-10-18 23:29 t4#P#p3.MYD
-rw-rw---- 1 mysql users 1024 2007-10-18 23:29 t4#P#p3.MYI
mysql> INSERT INTO t1 VALUES (18446744073709551615);
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> show warnings;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1264 | Out of range value for column 'param' at row 1 |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from t4;
Empty set (0.00 sec)
flundra:/usr/local/mysql/var/test # ls t4* -l
-rw-rw---- 1 mysql users 8556 2007-10-18 23:29 t4.frm
-rw-rw---- 1 mysql users 24 2007-10-18 23:29 t4.par
-rw-rw---- 1 mysql users 0 2007-10-18 23:29 t4#P#p3.MYD
-rw-rw---- 1 mysql users 1024 2007-10-18 23:29 t4#P#p3.MYI
Shouldn't the INSERT be failing with a "no matching partition" error?
[21 Nov 2007 6:06]
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/38186 ChangeSet@1.2627, 2007-11-21 10:05:41+04:00, ramil@mysql.com +3 -0 Fix for bug #29258: Partitions: search fails for maximum unsigned bigint Problem: looking for a matching partition we miss the fact that the maximum allowed value is in the PARTITION p LESS THAN MAXVALUE. Fix: consider the maximum value as a supermum.
[26 Nov 2007 6:28]
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/38467 ChangeSet@1.2627, 2007-11-26 10:28:25+04:00, ramil@mysql.com +4 -0 Fix for bug #29258: Partitions: search fails for maximum unsigned bigint Problems: 1. looking for a matching partition we miss the fact that the maximum allowed value is in the PARTITION p LESS THAN MAXVALUE. 2. one can insert maximum value if numeric maximum value is the last range. (should only work if LESS THAN MAXVALUE). 3. one cannot have both numeric maximum value and MAXVALUE string as ranges (the same value, but different meanings). Fix: consider the maximum value as a supremum.
[26 Nov 2007 8:00]
Mattias Jonsson
OK to push
[30 Nov 2007 7:46]
Ramil Kalimullin
Queued to 5.1-engines, 6.0-engines.
[12 Dec 2007 23:00]
Bugs System
Pushed into 6.0.5-alpha
[12 Dec 2007 23:02]
Bugs System
Pushed into 5.1.23-rc
[13 Dec 2007 12:28]
Jon Stephens
Documented bugfix in the 5.1.23 and 6.0.5 changelogs as follows:
It was not possible to insert the greatest possible value for a
given data type into a partitioned table. For example, consider
a table defined as shown here:
CREATE TABLE t (c BIGINT UNSIGNED)
PARTITION BY RANGE(c) (
PARTITION p0 VALUES LESS THAN MAXVALUE
);
The largest possible value for a BIGINT
UNSIGNED column is 18446744073709551615, but the
statement INSERT INTO t VALUES
(18446744073709551615); would fail, even though the
same statement succeeded were t not a
partitioned table.
In other words, MAXVALUE was treated as being
equal to the greatest possible value, rather than as a least
upper bound.
In addition, I've updated the description of MAXVALUE in the 5.1/6.0 Partitioning chapter, noting that it actually refers to a least upper bound and not a greatest possible value.
Description: I create a partitioned table with an unsigned bigint column. I insert the maximum possible unsigned bigint value. I search for the maximum possible unsigned bigint value. I get nothing. How to repeat: mysql> CREATE TABLE t1 (s1 BIGINT UNSIGNED) -> ENGINE=MyISAM -> PARTITION BY RANGE (s1) -> (PARTITION p3 VALUES LESS THAN MAXVALUE); Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO t1 VALUES (18446744073709551614),(18446744073709551615); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM t1 WHERE s1 =18446744073709551614; +----------------------+ | s1 | +----------------------+ | 18446744073709551614 | +----------------------+ 1 row in set (0.02 sec) mysql> SELECT * FROM t1 WHERE s1 =18446744073709551615; Empty set (0.01 sec)