List:General Discussion« Previous MessageNext Message »
From:xian liu Date:May 14 2008 8:56am
Subject: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.
   
  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