Bug #9600 row() comparions works differently for IN and =
Submitted: 4 Apr 2005 9:29 Modified: 24 Mar 2006 12:44
Reporter: Michael Widenius Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.1 OS:
Assigned to: Ramil Kalimullin CPU Architecture:Any

[4 Apr 2005 9:29] Michael Widenius
Description:
ROW() comparision using = and IN gives different results

How to repeat:
select row('a',0,3) IN (row(0,0,3)) -> 0
select row('a',0,3) = row(0,0,3) -> 1

A related item is that:

select row('a',0,3) IN (row(3,2,3), row(4.0,'0','3'))

Doesn't produce a warning when 'a' is compared as number
[19 Nov 2005 3:41] Roberto Spadim
server 5.0.15 linux max from mysql site works fine in both cases
returning 1
[24 Mar 2006 12:44] Ramil Kalimullin
Could not repeat using the latest 5.1

mysql> select row('a',0,3) = row(0,0,3);
+---------------------------+
| row('a',0,3) = row(0,0,3) |
+---------------------------+
| 1                         |
+---------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
+---------+------+---------------------------------------+
1 row in set (0.01 sec)

mysql> select row('a',0,3) IN (row(0,0,3));
+------------------------------+
| row('a',0,3) IN (row(0,0,3)) |
+------------------------------+
| 1                            |
+------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

So the issue is fixed somehow.