From: xian liu
Date: May 14 2008 8:56am
Subject: comparison operations in IN subquery
List-Archive: http://lists.mysql.com/mysql/212791
Message-Id: <163523.77383.qm@web15103.mail.cnb.yahoo.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-222800119-1210755365=:77383"
Content-Transfer-Encoding: 8bit

--0-222800119-1210755365=:77383
Content-Type: text/plain; charset=gb2312
Content-Transfer-Encoding: 8bit

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!!!


*^_^*
       
---------------------------------
 ÑÅ»¢ÓÊÏ䣬ÄúµÄÖÕÉúÓÊÏ䣡
--0-222800119-1210755365=:77383--