Bug #9600 row() comparions works differently for IN and =

• Submitted: 4 Apr 2005 9:29
• Modified: 24 Mar 2006 12:44
• Reporter: Michael Widenius
• Status: Can't repeat
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.1
• 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.