MySQL Bugs: #61765: Wrong query results on subpartitions based on USIGNED BIGINT

Bug #61765	Wrong query results on subpartitions based on USIGNED BIGINT
Submitted:	6 Jul 2011 1:46	Modified:	18 Dec 2011 23:16
Reporter:	Aidar Aslyamov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S1 (Critical)
Version:	5.5.8, 5.5.11, 5.5.13	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The select query returns empty result from subpartitions based on UNSIGNED BIGINT if the variable is larger than 9223372036854775807

How to repeat:
CREATE TABLE t1 (
  `a` int(11) NOT NULL,
  `b` bigint(20) unsigned NOT NULL
) ENGINE InnoDB 
 PARTITION BY RANGE ( a)
SUBPARTITION BY  hash (b)
SUBPARTITIONS 10
(PARTITION p1 VALUES LESS THAN (1308614400),
 PARTITION p2 VALUES LESS THAN (1308700800),
 PARTITION p3 VALUES LESS THAN (1308787200),
 PARTITION p4 VALUES LESS THAN (1308873600),
 PARTITION p5 VALUES LESS THAN (1308960000)
);

insert into t1 values 
(1308614400,18446744073709551615), 
(1308700800,0xFFFFFFFFFFFFFFFE),
(1308787200,18446744073709551613), 
(1308873600,18446744073709551612), 
(1308873600, 12531568256096620965),
(1308873600, 12531568256096),
(1308873600, 9223372036854775808);

The following queries returns nothing 

select * from t1 where b = 9223372036854775808;
select * from t1 where b = 18446744073709551612;
select * from t1 where b = 18446744073709551615;

Thank you for the bug report.

C:\DBS>c:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.15-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 >use test
Database changed
mysql 5.5 >CREATE TABLE t1 (
    ->   `a` int(11) NOT NULL,
    ->   `b` bigint(20) unsigned NOT NULL
    -> ) ENGINE InnoDB
    ->  PARTITION BY RANGE ( a)
    -> SUBPARTITION BY  hash (b)
    -> SUBPARTITIONS 10
    -> (PARTITION p1 VALUES LESS THAN (1308614400),
    ->  PARTITION p2 VALUES LESS THAN (1308700800),
    ->  PARTITION p3 VALUES LESS THAN (1308787200),
    ->  PARTITION p4 VALUES LESS THAN (1308873600),
    ->  PARTITION p5 VALUES LESS THAN (1308960000)
    -> );
Query OK, 0 rows affected (2.23 sec)

mysql 5.5 >
mysql 5.5 >insert into t1 values
    -> (1308614400,18446744073709551615),
    -> (1308700800,0xFFFFFFFFFFFFFFFE),
    -> (1308787200,18446744073709551613),
    -> (1308873600,18446744073709551612),
    -> (1308873600, 12531568256096620965),
    -> (1308873600, 12531568256096),
    -> (1308873600, 9223372036854775808);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql 5.5 >select * from t1 where b = 9223372036854775808;
Empty set (0.28 sec)

mysql 5.5 >select * from t1 where b = 18446744073709551612;
Empty set (0.00 sec)

mysql 5.5 >select * from t1 where b = 18446744073709551615;
Empty set (0.00 sec)

mysql 5.5 >drop table t1;
Query OK, 0 rows affected (1.87 sec)

mysql 5.5 >CREATE TABLE t1 (
    ->   `a` int(11) NOT NULL,
    ->   `b` bigint(20) unsigned NOT NULL
    -> ) ENGINE InnoDB;
Query OK, 0 rows affected (0.11 sec)

mysql 5.5 >insert into t1 values
    -> (1308614400,18446744073709551615),
    -> (1308700800,0xFFFFFFFFFFFFFFFE),
    -> (1308787200,18446744073709551613),
    -> (1308873600,18446744073709551612),
    -> (1308873600, 12531568256096620965),
    -> (1308873600, 12531568256096),
    -> (1308873600, 9223372036854775808);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql 5.5 >select * from t1 where b = 9223372036854775808;
+------------+---------------------+
| a          | b                   |
+------------+---------------------+
| 1308873600 | 9223372036854775808 |
+------------+---------------------+
1 row in set (0.00 sec)

mysql 5.5 >select * from t1 where b = 18446744073709551612;
+------------+----------------------+
| a          | b                    |
+------------+----------------------+
| 1308873600 | 18446744073709551612 |
+------------+----------------------+
1 row in set (0.00 sec)

mysql 5.5 >select * from t1 where b = 18446744073709551615;
+------------+----------------------+
| a          | b                    |
+------------+----------------------+
| 1308614400 | 18446744073709551615 |
+------------+----------------------+
1 row in set (0.00 sec)

mysql 5.5 >

Hi MySQL Team, 

Do you have any update on this issue?

Related to bug#20257, but for subpartitions.

Documented bugfix in the 5.6.5 changelog, as follows:

      A function internal to the code for finding matching subpartitions
      represented an unsigned number as signed, with the result that matching
      subpartitions were sometimes missed in results of queries.

Closed.