MySQL Bugs: #30069: bigint -9223372036854775808 stopped working on x86

Bug #30069	bigint -9223372036854775808 stopped working on x86_64
Submitted:	26 Jul 2007 14:27	Modified:	4 Dec 2007 21:29
Reporter:	Michal Marek	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Linux
Assigned to:	Kent Boortz	CPU Architecture:	Any
Tags:	Contribution

Description:
Hi,

on openSUSE Factory / x86_64 (gcc version 4.2.1 20070705 (prerelease)
(SUSE Linux)), storing -9223372036854775808 into a bigint field returns
garbage in SELECT.

This is also detected by several tests (eg t/round.test). Both 5.0.41
and 5.0.45 show this bug. But it works fine on i386 and on older
distributions (eg.  openSUSE 10.2 with gcc version 4.1.2 20061115
(prerelease) (SUSE Linux)). I don't know what makes the difference, gcc
is just a guess :) It also doesn't seem to be related to a storage
engine, I can reproduce it with both MyISAM and InnoDB. However, 'select
(-9223372036854775808)' works.

BTW, stracing the client shows

21013 write(3, "-\0\0\0\3INSERT INTO t1 values (-9223372036854775808)", 49) = 49
...
21013 write(3, "\21\0\0\0\3SELECT * FROM t1", 21) = 21
21013 read(3, "\1\0\0\1\1*\0\0\2\3def\4test\2t1\2t1\6sint64\6sint64\f?\0\24\0\0\0\10\1\20\0\0\0\5\0\0\3\376\0\0\2\0\25\0\0\4\24-\'..--).0-*(+,))+(0(\5\0\0\5\376\0\0\2\0", 16384) = 94

so the corruption happens on the server side. I'll try to debug this
further.

How to repeat:

mysql> CREATE TABLE t1 (sint64 bigint not null);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t1 values (-9223372036854775808);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+----------------------+
| sint64               |
+----------------------+
| -'..--).0-*(+,))+(0( | 
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 5.0.45    | 
+-----------+
1 row in set (0.00 sec)

mysql>

Thank you for the bug report. I was not able to repeat with current source tree:

miguel@luar:~/dbs/5.0> cat /etc/issue

Welcome to openSUSE 10.2 (X86-64) - Kernel \r (\l).

miguel@luar:~/dbs/5.0> gcc --version
gcc (GCC) 4.1.2 20061115 (prerelease) (SUSE Linux)
Copyright (C) 2006 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

miguel@luar:~/dbs/5.0> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.48-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.0.48-debug        |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | suse-linux-gnu      |
+-------------------------+---------------------+
5 rows in set (0.00 sec)

mysql> CREATE TABLE t1 (sint64 bigint not null);
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO t1 values (-9223372036854775808);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+----------------------+
| sint64               |
+----------------------+
| -9223372036854775808 |
+----------------------+
1 row in set (0.00 sec)

mysql>

Yes, it works for me on 10.2-x86_64 as well. Only an x86_64 build on a recent openSUSE Factory (10.3 development version) shows the problem. But it might be tough to even install openSUSE Factory, so let's leave it as "can't repeat" for now and I'll try to debug it my system.

Some info: compiling strings/* with -O0 instead of -O2 make the problem go away.

strings/ctype-simple.c makes the difference.

I tracked it down to the my_longlong10_to_str_8bit() function, which breaks with -O2 with gcc 4.2.1 if passed a LONG_MIN. This is an explanation I got from a GCC developer (Richard Guenther):

On Wed, 29 Aug 2007, Michal Marek wrote:
> The problem is that when calling the my_longlong10_to_str_8bit()
> function with LONG_MIN, the second if condition evaluates to false when
> compiled with -O2:
> 
> my_longlong10_to_str_8bit(-9223372036854775807)
> a
> my_longlong10_to_str_8bit(-9223372036854775808)
> a
> b
> $ gcc -O2 -Wall test.c && ./a.out
> my_longlong10_to_str_8bit(-9223372036854775807)
> a
> my_longlong10_to_str_8bit(-9223372036854775808)
> a
> $
> 
> 
> I suspect that either the 'val = -val' assignment or the cast to
> unsigned long is undefined due to the overflow and therefore the
> compiler is free to do anything, but a more insightful explanation would
> help...

Well...

#include <stdio.h>
#include <limits.h>

void my_longlong10_to_str_8bit(long val)
{
    printf("my_longlong10_to_str_8bit(%ld)\n", val);
    if (val < 0)
    {
      puts("a");
      val = -val;

...this is undefined for val == LONG_MIN, so the compiler assumes
the resulting val is positive...

    }
  
...which then makes this test always evaluate to false.
    if ((unsigned long) val > (unsigned long) LONG_MAX)
          puts("b");
}

int main()
{
        my_longlong10_to_str_8bit(-LONG_MAX);
        my_longlong10_to_str_8bit(LONG_MIN);
        
        return 0;
}

a "fix" is to use

	
     val = -(unsigned long)val;

instead.

Richard.

And indeed, changing the 'val = -val' line in my_longlong10_to_str_8bit()
to 'val = -(ulonglong)val' fixes the problem. Please consider this fix.

Patch to fix BUG#30069: bigint -9223372036854775808 stopped working on x86_64

Attachment: mybug30069.patch (text/x-patch), 335 bytes.

Setting this bug back to verified and tagging it accordingly. Can someone please apply this patch (I extracted it from the openSUSE RPM)? Thanks!

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

ChangeSet@1.2685, 2007-09-28 22:35:48+02:00, kent@mysql.com +1 -0
  ctype-simple.c:
    Avoid undefined value when negating (bug#30069)

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

ChangeSet@1.2686, 2007-11-12 12:52:03+01:00, kent@mysql.com +2 -0
  bigint.test, bigint.result:
    Test case for Bug#30069

Pushed into 4.1.24

Pushed into 5.0.54

Pushed into 5.1.23-rc

Pushed into 6.0.4-alpha

Noted in 4.1.24, 5.0.54, 5.1.23, 6.0.4 changelogs.

On some 64-bit systems, inserting the largest negative value into a
BIGINT column resulted in incorrect data.