Bug #28922 strange result in func_in
Submitted: 6 Jun 2007 11:06 Modified: 15 Jun 2007 8:34
Reporter: Matthias Leich Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version: OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[6 Jun 2007 11:06] Matthias Leich
Description:
# There is a strange result set within func_in
SELECT HEX(a) FROM t2 WHERE a IN
(0xBB3C3E98175D33C8,
0x7fffffffffffffff,
0x8000000000000000,
0x8000000000000400,
0x8000000000000401,
42);
HEX(a)
BB3C3E98175D33C8
7FFFFFFFFFFFFEFF <--------------
7FFFFFFFFFFFFFEF <--------------
7FFFFFFFFFFFFFFE <--------------
7FFFFFFFFFFFFFFF
8000000000000000
8000000000000001 <--------------
8000000000000002 <--------------
8000000000000300 <--------------
8000000000000400
8000000000000401

Some experiments with simplified data:
--------------------------------------
CREATE TABLE t2 (a BIGINT UNSIGNED);
insert into t2 values (0x8000000000000000),
(0x8000000000000001),
(0x8000000000000002),
(0x8000000000000300),
(0x8000000000000400),
(0x8000000000000401),
(0x8000000000004001),
(0x8000000000040001),
(0x8000000000400001),
(0x8000000004000001),
(0x8000000040000001),
(0x8000000400000001),
(0x8000004000000001),
(0x8000040000000001);
SELECT HEX(a) FROM t2 WHERE a IN
(0x8000000000000000,
0x8000000000000400,
0x8000000000000401,
42);      <= This non hex value seems to play
HEX(a)       a big role.
8000000000000000
8000000000000001 <------------
8000000000000002 <------------
8000000000000300 <------------
8000000000000400
8000000000000401
# Some similar statements for comparison
SELECT HEX(a) FROM t2 WHERE a IN
(0x8000000000000000,
0x8000000000000400,
0x8000000000000401,
0x000000000000002A); <= hex value insted of INTEGER 42
HEX(a)
8000000000000000   # The result set is now ok.
8000000000000400
8000000000000401  
SELECT HEX(a) FROM t2 WHERE a IN
( CAST(0x8000000000000000 AS UNSIGNED),
CAST(0x8000000000000400 AS UNSIGNED),
CAST(0x8000000000000401 AS UNSIGNED),
42);
HEX(a)
8000000000000000   # The result set is now ok.
8000000000000400
8000000000000401
SELECT HEX(a) FROM t2 WHERE HEX(a) IN
('8000000000000000',
'8000000000000400',
'8000000000000401',
'000000000000002A');
HEX(a)
8000000000000000    # The result set is now ok.
8000000000000400
8000000000000401

My environment:
- mysql-5.1-rpl last ChangeSet@1.2543, 2007-06-05
- Linux openSUSE 10.2 (X86-64), Core2Duo (x86 64 Bit)

How to repeat:
Please use my attached files with the simplified test
  cp m1_func_in.test t
  echo "Dummy" > r/m1_func_in.result
  ./mysql-test-run m1_func_in
and inspect r/m1_func_in.reject.
[6 Jun 2007 11:07] Matthias Leich
test script

Attachment: ml_func_in.test (application/octet-stream, text), 1.04 KiB.

[6 Jun 2007 11:08] Matthias Leich
My suspicious results

Attachment: ml_func_in.reject (application/octet-stream, text), 1.11 KiB.

[9 Jun 2007 6:53] Ramil Kalimullin
it's related to bug #28748: "Select" returning one value too few.
[14 Jun 2007 5:57] Ramil Kalimullin
The bug is "fixed" during merge of fix for bug #28748: "Select" returning one value too few" to 5.1