MySQL Bugs: #40950: Falcon indexes broken for unsigned bigint

Bug #40950	Falcon indexes broken for unsigned bigint
Submitted:	22 Nov 2008 9:13	Modified:	15 May 2009 17:01
Reporter:	Lars-Erik BjÃ¸rk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S1 (Critical)
Version:	6.0-falcon-team	OS:	Any
Assigned to:	Lars-Erik BjÃ¸rk	CPU Architecture:	Any
Tags:	F_ENCODING

Description:
This bug is a fork of bug#40607 which reported a problem for indexes for both unsigned decimal, and unsigned bingint. These problems, however, do not seem to be related. I have set the severity to S1, as this is the severity set on the original bug.

The following description is copied from the report for bug#40607:

CREATE TABLE `table10` (
        `bigint_unsigned_key` bigint unsigned,
        key (`bigint_unsigned_key` )
);

INSERT IGNORE INTO table10 VALUES ('9');

UPDATE `table10` SET `bigint_unsigned_key` = 10 WHERE `bigint_unsigned_key` <
1.65025801745927e+19;
SELECT * FROM table10;

The update fails to match any rows in Falcon, and matches correctly in Innodb.

How to repeat:
See description

I have debugged this issue a little further. When Falcon extracts the value part of the key provided by the server, the binary representation of the key is

(StorageDatabase::getSegmentValue)

(gdb) x/tg ptr
0x36dd021:	1110010100000100111100010011110000000000000000011111100000000000

This is big-endian for 1.65025801745928e+19 (as given by the query)

Falcon treats the case for HA_KEYTYPE_ULONGLONG and HA_KEYTYPE_LONGLONG the same, and the value is interpreted as an int64, which causes it to be negative, because the sign bit is set.

case HA_KEYTYPE_ULONGLONG:
case HA_KEYTYPE_LONGLONG:
	{
	int64 temp = (int64) <------ Here
		((uint64)(((uint32) ((UCHAR) ptr[0])) +
			(((uint32) ((UCHAR) ptr[1])) << 8) +
			(((uint32) ((UCHAR) ptr[2])) << 16) +
			(((uint32) ((UCHAR) ptr[3])) << 24)) +
		(((uint64)(((uint32) ((UCHAR) ptr[4])) +
			(((uint32) ((UCHAR) ptr[5])) << 8) +
			(((uint32) ((UCHAR) ptr[6])) << 16) +
			(((uint32) ((UCHAR) ptr[7])) << 24)))
		<< 32));
	value->setValue(temp);
	}

The value that is set is therefore -1944163899116750848 which, of course, is totally wrong.

It seems like there is no support for an unsigned 64bit key (but I haven't dug deep yet)

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/70096

3069 lars-erik.bjork@sun.com	2009-03-23
      This is a patch for bug#40950 Falcon indexes broken for unsigned bigint
      
      As explained in the bug report, the problem is that Falcon does not find the value in the index if the sign bit is set. This is because the value is intepreted as negative.
      
      The way I have fixed this is by encoding unsigned bigint (MYSQL_TYPE_LONGLONG) using BigInt instead of int64. I have also changed StorageDatabase::getSegmentValue accordingly to create working index values.
      added:
        mysql-test/suite/falcon/r/falcon_bug_40950.result
        mysql-test/suite/falcon/t/falcon_bug_40950.test
      modified:
        storage/falcon/BigInt.cpp
        storage/falcon/BigInt.h
        storage/falcon/StorageDatabase.cpp
        storage/falcon/Value.cpp
        storage/falcon/ha_falcon.cpp

OK to push.  Jim Starkey has reviewed the patch.

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/70196

3073 lars-erik.bjork@sun.com	2009-03-24
      This is a new patch for bug#40950 Falcon indexes broken for unsigned bigint
            
      As explained in the bug report, the problem is that Falcon does not find the value in the index if the sign bit is set. This is because the value is intepreted as negative.
            
      The way I have fixed this is by encoding unsigned bigint (MYSQL_TYPE_LONGLONG), with the MSB set, using BigInt instead of int64. I have also changed StorageDatabase::getSegmentValue accordingly to create working index values.
      added:
        mysql-test/suite/falcon/r/falcon_bug_40950.result
        mysql-test/suite/falcon/t/falcon_bug_40950.test
      modified:
        storage/falcon/BigInt.cpp
        storage/falcon/BigInt.h
        storage/falcon/StorageDatabase.cpp
        storage/falcon/Value.cpp
        storage/falcon/ha_falcon.cpp

See also Duplicate bug#42425 :Issues with unsigned bigint range select with Falcon

Patch was approved by Jim in addition to Kevin, I have pushed it to the team tree

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/70519

3079 lars-erik.bjork@sun.com	2009-03-26
      Follow-up patch to the patch for bug#40950
      
      The constant 0x8000000000000000 broke compilation on 32-bit platforms. Have changed this to
      
      LL(0x8000000000000000) in ha_falcon.cpp and 0x8000000000000000ULL in StorageDatabase.cpp
      modified:
        storage/falcon/StorageDatabase.cpp
        storage/falcon/ha_falcon.cpp

Pushed into 6.0.11-alpha (revid:hky@sun.com-20090402144811-yc5kp8g0rjnhz7vy) (version source revid:vvaintroub@mysql.com-20090328013055-of8vpzkxhot40zy0) (merge vers: 6.0.11-alpha) (pib:6)

A note has been added to the 6.0.11 changelog: 

Performing SELECT operations on Falcon tables using the maximum BIG INT value would fail to return matching rows.