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:
None 
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
Triage: D2 (Serious)

[21 Jun 2007 0:48] Peter Gulutzan
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)
[21 Jun 2007 1:32] Miguel Solorzano
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] Miguel Solorzano
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.