Description:
$ cat sign.test
# Test signed/unsigned conversion
# we use InnoDB to repro the bug, but other engine would generate the same bug.
-- source include/have_innodb.inc
--disable_warnings
drop table if exists t2;
--enable_warnings
# create t2, Note that Fp is signed
CREATE TABLE `t2` (
`Fp` bigint(20) NOT NULL default '0',
`Text` varchar(255) character set latin1 collate latin1_bin NOT NULL default '',
PRIMARY KEY (`Fp`),
KEY `Text` (`Text`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# put some values
insert into t2 values
(-1552084730623784428, 100),
(-2474873173060715355, 101),
(-7134262809993999386, 1001);
# this one works with mysqltest and mysql
select * from t2 where Fp=-2474873173060715355;
# get the unsigned value of Fp
select cast(-2474873173060715355 as unsigned);
# this line generates nothing from mysqltest
# but it generates (-2474873173060715355, 101) if run thru mysql client.
select * from t2 where Fp=15971870900648836261;
# cleanup
drop table t2;
========================================================
running the same thing thru mysql, there is a match for
select * from t2 where Fp=15971870900648836261;
while running it as mysqltest, there is no match for that query
How to repeat:
put above script under mysql-test/t/sign.test
then run
mysql-test$ ./mysql-test-run sign
look at sign.log file
==================================
mysql-test$ cat r/sign.log
drop table if exists t2;
CREATE TABLE `t2` (
`Fp` bigint(20) NOT NULL default '0',
`Text` varchar(255) character set latin1 collate latin1_bin NOT NULL default '',
PRIMARY KEY (`Fp`),
KEY `Text` (`Text`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into t2 values
(-1552084730623784428, 100),
(-2474873173060715355, 101),
(-7134262809993999386, 1001);
select * from t2 where Fp=-2474873173060715355;
Fp Text
-2474873173060715355 101
select cast(-2474873173060715355 as unsigned);
cast(-2474873173060715355 as unsigned)
15971870900648836261
select * from t2 where Fp=15971870900648836261;
Fp Text
drop table t2;
======================================
run as mysql client
======================================
mysql-test$ mysql -uroot -E test < t/sign.test
*************************** 1. row ***************************
Fp: -2474873173060715355
Text: 101
*************************** 1. row ***************************
cast(-2474873173060715355 as unsigned): 15971870900648836261
*************************** 1. row ***************************
Fp: -2474873173060715355
Text: 101
=================================
