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:
?