List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:May 15 2008 5:31am
Subject:Re: ????: RE: comparison operations in IN subquery
View as plain text  
In the last episode (May 15), raid fifa said:
> Jerry Schwartz <jschwartz@stripped> ????:
> > 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.
>
> thank you!
>    
>   But if MySQL handles this case, the results of this query is not
>   what I want to get. So, is there some way to avoid it?

If you want to compare both fields as string, you will need to cast
your integer field:

 select * from temp1 where CAST(id AS CHAR) in (select tid from temp2);

That will convert "id" to a character string, which will let mysql use
a string-string comparison against "tid" instead of trying to convert
both to numbers.

http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

-- 
	Dan Nelson
	dnelson@stripped
Thread
Re: ????: RE: comparison operations in IN subqueryDan Nelson15 May