List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:May 14 2008 2:52pm
Subject:Re: comparison operations in IN subquery
View as plain text  
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@stripped
Thread
comparison operations in IN subqueryxian liu14 May
  • Re: comparison operations in IN subqueryDan Nelson14 May
  • RE: comparison operations in IN subqueryJerry Schwartz14 May