List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:May 14 2008 3:04pm
Subject:RE: comparison operations in IN subquery
View as plain text  
>Hi guys,
>
>  look at the following test case:
>
>  mysql> create table temp1( id int)ENGINE=innodb;
>Query OK, 0 rows affected (0.18 sec)
>
>mysql> create table temp2( tid varchar(10))ENGINE=innodb;
>Query OK, 0 rows affected (0.07 sec)
>
>mysql> insert into temp1 values(1);
>Query OK, 1 row affected (0.07 sec)
>
>mysql> insert into temp1 values(2);
>Query OK, 1 row affected (0.05 sec)
>
>mysql> insert into temp1 values(3);
>Query OK, 1 row affected (0.04 sec)
>
>mysql> insert into temp1 values(4);
>Query OK, 1 row affected (0.04 sec)
>
>mysql> insert into temp2 values('2,3,4');
>Query OK, 1 row affected (0.05 sec)
>
>mysql> commit;
>Query OK, 0 rows affected (0.00 sec)
>
>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.
[JS] MySQL converts the string to a number in this case. It stops at the
first character that cannot be part of a number.
>
>  Thanks a lot!!!
>
>
>*^_^*
>
>---------------------------------



Thread
comparison operations in IN subqueryxian liu14 May
  • Re: comparison operations in IN subqueryDan Nelson14 May
  • RE: comparison operations in IN subqueryJerry Schwartz14 May