In the last episode (May 15), raid fifa said:
> Jerry Schwartz <[EMAIL PROTECTED]> ????:
> > 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
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
