Dear all,
To my greatest astonishment, I run into this case where a "-8" signed SMALLINT came back as "2^32 - 8" if mysql_server_prepare is turned on.
check the SELECT yCoord AS 'Y' output in the three examples below. The last one is where the problem shows up.
I hope this can help you.
Best regards,
Thiery
I did compile Perl myself and all the CPAN modules.
DBD::mysql -- 4.022
DBI -- 1.622
$ perl -v
This is perl 5, version 14, subversion 2 (v5.14.2) built for x86_64-linux-thread-multi
$ perl -V:config_args
config_args='-des -Dprefix=/opt/perl -Acflags=-fPID -DPIC -m64 -Aldflags=-L/usr/lib64 -L/lib64 -lresolv -lnsl -ldl -lm -lcrypt -lutil -lc -Duse64bitint -Duseshrplib -Dusemultiplicity -Duselargefiles -Dusethreads -
Duseithreads -Dmyhostname=localhost -Dperladmin=root@localhost';
Show quoted textmysql> SELECT c.xCoord AS 'X', c.yCoord AS 'Y', c.dieId, a.headNum, a.siteNum, a.numInfo, a.hardBin, a.softBin, b.docType, UNIX_TIMESTAMP(b.startT) AS startT,
-> IF(d.keyId IS NULL,0, d.keyId) AS 'keyId',
-> IF(d.keyData IS NULL,'null', d.keyData) AS 'CPSN',
-> IF(e.keyStatus IS NULL,'', e.keyStatus) AS 'State',
-> IF(a.keyId IS NULL,'None', IF(a.partFlagB3 = 0,'Pass',IF(a.realPRR = true,'Fail','Fake'))) AS 'Status'
-> FROM (DieInfo AS a, STDFInfo AS b, Die AS c, LotWaf AS l)
-> LEFT JOIN MACKey AS d
-> ON a.keyId = d.keyId
-> LEFT JOIN MACKeyHist AS e
-> ON (a.keyId = e.keyId AND e.dieInfoId = a.dieInfoId)
-> WHERE a.STDFId = b.STDFId
-> AND b.lwId = l.lwId
-> AND a.dieId = c.dieId
-> AND l.lotId = 'ABCDEFG'
-> AND l.subLotId = '21';
+-----+-----+-------+---------+---------+---------+---------+---------+
| X | Y | dieId | headNum | siteNum | numInfo | hardBin | softBin |
+-----+-----+-------+---------+---------+---------+---------+---------+
| 69 | -9 | 1 | 1 | 27 | 1 | 6 | 138 |
| 69 | -8 | 2 | 1 | 28 | 1 | 1 | 1 |
| 68 | -8 | 3 | 1 | 29 | 1 | 1 | 1 |
| 67 | -8 | 4 | 1 | 30 | 1 | 1 | 1 |
| 73 | -10 | 5 | 1 | 20 | 1 | 1 | 1 |
| 72 | -10 | 6 | 1 | 21 | 1 | 1 | 1 |
| 70 | -9 | 7 | 1 | 24 | 1 | 1 | 1 |
| 71 | -9 | 8 | 1 | 25 | 1 | 1 | 1 |
| 72 | -9 | 9 | 1 | 26 | 1 | 1 | 1 |
| 73 | -9 | 10 | 1 | 27 | 1 | 1 | 1 |
| 73 | -8 | 11 | 1 | 28 | 1 | 1 | 1 |
| 72 | -8 | 12 | 1 | 29 | 1 | 1 | 1 |
...
With mysql_server_prepare disabled, my application's trace reports:
[26611]: Found a row with: [69,-9] as <STDF> s<27> h<1> n<1> <1340349441> <> <None>
[26611]: Found a row with: [69,-8] as <STDF> s<28> h<1> n<1> <1340349441> <ASSIGNED> <Pass>
[26611]: Found a row with: [68,-8] as <STDF> s<29> h<1> n<1> <1340349441> <ASSIGNED> <Pass>
[26611]: Found a row with: [67,-8] as <STDF> s<30> h<1> n<1> <1340349441> <ASSIGNED> <Pass>
[26611]: Found a row with: [73,-10] as <STDF> s<20> h<1> n<1> <1340349441> <ASSIGNED> <Pass>
[26611]: Found a row with: [72,-10] as <STDF> s<21> h<1> n<1> <1340349441> <ASSIGNED> <Pass>
[26611]: Found a row with: [70,-9] as <STDF> s<24> h<1> n<1> <1340349441> <ASSIGNED> <Pass>
[26611]: Found a row with: [71,-9] as <STDF> s<25> h<1> n<1> <1340349441> <ASSIGNED> <Pass>
...
With mysql_server_prepare ENABLED, my application's trace reports "4294967287" instead of "-8":
[26477]: Found a row with: [69,4294967287] as <STDF> s<27> h<1> n<1> <1340349441> <> <None>
[26477]: Found a row with: [69,4294967288] as <STDF> s<28> h<1> n<1> <1340349441> <ASSIGNED> <Pass>
[26477]: Found a row with: [68,4294967288] as <STDF> s<29> h<1> n<1> <1340349441> <ASSIGNED> <Pass>
[26477]: Found a row with: [67,4294967288] as <STDF> s<30> h<1> n<1> <1340349441> <ASSIGNED> <Pass>
[26477]: Found a row with: [73,4294967286] as <STDF> s<20> h<1> n<1> <1340349441> <ASSIGNED> <Pass>
[26477]: Found a row with: [72,4294967286] as <STDF> s<21> h<1> n<1> <1340349441> <ASSIGNED> <Pass>
[26477]: Found a row with: [70,4294967287] as <STDF> s<24> h<1> n<1> <1340349441> <ASSIGNED> <Pass>
[26477]: Found a row with: [71,4294967287] as <STDF> s<25> h<1> n<1> <1340349441> <ASSIGNED> <Pass>
[26477]: Found a row with: [72,4294967287] as <STDF> s<26> h<1> n<1> <1340349441> <ASSIGNED> <Pass>
[26477]: Found a row with: [73,4294967287] as <STDF> s<27> h<1> n<1> <1340349441> <ASSIGNED> <Pass>
[26477]: Found a row with: [73,4294967288] as <STDF> s<28> h<1> n<1> <1340349441> <ASSIGNED> <Pass>
...
CREATE TABLE `Die` (
`dieId` INT unsigned NOT NULL AUTO_INCREMENT,
`dieTypeId` SMALLINT unsigned NOT NULL,
`xCoord` SMALLINT signed NOT NULL,
`yCoord` SMALLINT signed NOT NULL,
...
my $stm = qq {
SELECT c.xCoord AS 'X', c.yCoord AS 'Y', c.dieId, a.headNum, a.siteNum, a.numInfo, a.hardBin, a.softBin, b.docType, UNIX_TIMESTAMP(b.startT) AS startT,
IF(d.keyId IS NULL,0, d.keyId) AS 'keyId',
IF(d.keyData IS NULL,'null', d.keyData) AS 'CPSN',
IF(e.keyStatus IS NULL,'', e.keyStatus) AS 'State',
IF(a.keyId IS NULL,'None', IF(a.partFlagB3 = 0,'Pass',IF(a.realPRR = true,'Fail','Fake'))) AS 'Status'
FROM (DieInfo AS a, STDFInfo AS b, Die AS c, LotWaf AS l)
LEFT JOIN MACKey AS d
ON a.keyId = d.keyId
LEFT JOIN MACKeyHist AS e
ON (a.keyId = e.keyId AND e.dieInfoId = a.dieInfoId)
WHERE a.STDFId = b.STDFId
AND b.lwId = l.lwId
AND a.dieId = c.dieId
AND l.lotId = $inp->{lotId}
AND l.subLotId = $inp->{subLotId}
};
&tracing (8, " Step 1: Running this query: <$stm>");
my $sth = $dbmyh->prepare($stm);
$DBI::err && ( &tracerr (1, " Error processing request: $DBI::err ($DBI::errstr)."), return (0));
$sth->execute();
$DBI::err && ( &tracerr (1, " Error processing request: $DBI::err ($DBI::errstr)."), return (0));
($DBI::rows == 0) && ( &tracing (2, " Error it is not possible to have ($DBI::rows) of information returned for <$STIFInfo->{waferId}>."), return (0));
while (my $ref = $sth->fetchrow_hashref()) {
$DBI::err && ( &tracerr (1, " Error processing request: $DBI::err ($DBI::errstr)."), return (0));
&tracing (8, " Found a row with: [$ref->{'X'},$ref->{'Y'}] as <$ref->{'docType'}> s<$ref->{'siteNum'}> h<$ref->{'headNum'}> n<$ref->{'numInfo'}> <$ref->{'startT'}> <$ref->{'State'}> <$ref->{'Status'}>");
RT for rt.cpan.org