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

[26 Jul 2007 14:27] Michal Marek
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>
[26 Jul 2007 18:10] MySQL Verification Team
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>
[27 Jul 2007 9:07] Michal Marek
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.
[1 Aug 2007 9:25] Michal Marek
Some info: compiling strings/* with -O0 instead of -O2 make the problem go away.
[1 Aug 2007 10:15] Michal Marek
strings/ctype-simple.c makes the difference.
[30 Aug 2007 8:39] Michal Marek
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.
[28 Sep 2007 18:01] Lenz Grimmer
Patch to fix BUG#30069: bigint -9223372036854775808 stopped working on x86_64

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

[28 Sep 2007 18:02] Lenz Grimmer
Setting this bug back to verified and tagging it accordingly. Can someone please apply this patch (I extracted it from the openSUSE RPM)? Thanks!
[28 Sep 2007 20:36] 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/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)
[12 Nov 2007 11:51] 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/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
[21 Nov 2007 18:52] Bugs System
Pushed into 4.1.24
[21 Nov 2007 18:53] Bugs System
Pushed into 5.0.54
[21 Nov 2007 18:53] Bugs System
Pushed into 5.1.23-rc
[21 Nov 2007 18:54] Bugs System
Pushed into 6.0.4-alpha
[4 Dec 2007 21:29] Paul DuBois
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.