Bug #3472 converting int to char produces different values based on OS
Submitted: 14 Apr 2004 12:16 Modified: 4 May 2004 14:32
Reporter: Barbara Deaton Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.0-alpha-standard OS:HP/UX (HP 11 64-bit)
Assigned to: CPU Architecture:Any

[14 Apr 2004 12:16] Barbara Deaton
Description:
We have created a table with an INT column and inserted the char value of 
'21474836471' when you then select from the table you will get the value -9.  If you do the same thing on windows you will get the value 2147483647 (the max int value).  We are wondering why there is a difference, and what should the correct value be?

Not sure what info is needed from the mysqlbug script so here it is:

>Release:       mysql-4.1.0-alpha-standard (Official MySQL-standard binary)
>Environment:
        <machine, os, target, libraries (multiple lines)>
System: HP-UX garage B.11.11 U 9000/800 162494636 unlimited-user license

Some paths:  /usr/local/bin/perl /usr/ccs/bin/make /opt/softbench/bin/gcc /usr/ccs/bin/cc
GCC: /usr/local/bin/gcc -v 
gcc: SB error: cannot find exe /usr/local/bin/gcc
Compilation info: CC='cc'  CFLAGS='+DD64'  CXX='aCC'  CXXFLAGS='+DD64'  LDFLAGS=''  ASFLAGS=''
LIBC: 
lrwxr-xr-x   1 root       root             8 Dec 27  2002 /lib/libc.0 -> ./libc.1
-r-xr-xr-x   1 bin        bin        1867776 Dec  7  2001 /lib/libc.1
-r-xr-xr-x   1 bin        bin        1814528 Sep 15  2003 /lib/libc.2
-r--r--r--   1 bin        bin        2539556 Sep 15  2003 /lib/libc.a
lrwxr-xr-x   1 root       root            15 Dec 27  2002 /lib/libc.sl -> /usr/lib/libc.2
lrwxr-xr-x   1 root       root             8 Dec 27  2002 /usr/lib/libc.0 -> ./libc.1
-r-xr-xr-x   1 bin        bin        1867776 Dec  7  2001 /usr/lib/libc.1
-r-xr-xr-x   1 bin        bin        1814528 Sep 15  2003 /usr/lib/libc.2
-r--r--r--   1 bin        bin        2539556 Sep 15  2003 /usr/lib/libc.a
lrwxr-xr-x   1 root       root            15 Dec 27  2002 /usr/lib/libc.sl -> /usr/lib/libc.2
Configure command: ./configure '--prefix=/usr/local/mysql' '--with-comment=Official MySQL-standard binary' '--with-extra-charsets=complex' '--with-server-suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile' '--disable-shared' '--with-inn
odb' 'CC=cc' 'CFLAGS=+DD64' 'CXXFLAGS=+DD64' 'CXX=aCC'

How to repeat:
If you use the MySQL command line tool and run
the following commands you will see -9 inserted, however if you do the same
thing on windows you will see the value is converted to 21474836471:

**** Reproduced on garage server version: 4.1.0-alpha-standard***********

mysql> create table defect (`c1` INT);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into defect (`c1`) values ('21474836471');
Query OK, 1 row affected (0.02 sec)

mysql> select * from defect;
+------+
| c1   |
+------+
|   -9 |
+------+
1 row in set (0.00 sec)

**** Reproduced on windows server version:  4.1.0-alpha-max-debug******
mysql> create table defect (`c1` INT);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into defect values ('21474836471');
Query OK, 1 row affected (0.05 sec)

mysql> select * from defect;
+------------+
| c1         |
+------------+
| 2147483647 |
+------------+
1 row in set (0.19 sec)

Suggested fix:
?
[4 May 2004 14:32] Michael Widenius
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

I couldn't repeat this in my Linux machine, so I assume this is now fixed. (Fix will be in 4.1.2)

I did add a test case for this to our test suite, so if the bug still exists we will catch it in our build process.

Regards,
Monty