Re: comparison operations in IN subquery

[Dan Nelson <dnelson@xxxxxxxxxxxxxxx>]

In the last episode (May 14), xian liu said:
> mysql> select * from temp1;
> +------+
> | id   |
> +------+
> |    1 | 
> |    2 | 
> |    3 | 
> |    4 | 
> +------+
> 4 rows in set (0.01 sec)
> 
> mysql> select * from temp2;
> +-------+
> | tid   |
> +-------+
> | 2,3,4 | 
> +-------+
> 1 row in set (0.00 sec)
> 
> mysql> select * from temp1 where id in (select tid from temp2);
> +------+
> | id   |
> +------+
> |    2 | 
> +------+
> 1 row in set (0.00 sec)
>    
>   The problem:
>   Why there is result for the last SELECT statement???
>
>   How does mysql compare id with tid ?? they are different type and
>   have different format value.

http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html

 "When an operator is used with operands of different types, type
  conversion occurs to make the operands compatible. Some conversions
  occur implicitly. For example, MySQL automatically converts numbers
  to strings as necessary, and vice versa."

When the string "2,3,4" gets converted to a number, the first
non-numeric character finishes the conversion so you get the number 2.

-- 
	Dan Nelson
	dnelson@xxxxxxxxxxxxxxx

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=royale@xxxxxxxxxx