From: Dan Nelson (dnelsonallantgroup.com)
Date: Wed May 14 2008 - 09:52:19 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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
        dnelsonallantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]