SEO

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


*^_^*

---------------------------------
ÑÅ»¢ÓÊÏ䣬ÄúµÄÖÕÉúÓÊÏ䣡

In the last episode (May 14), xian liu said:
> 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.

http://dev.mysql.com/doc/refman/5.0/...onversion.html

"When an operator is used with operands of different types, type
conversion occurs to make the operands compatible. Some conversions
occur implicitly. For example, MySQL automatically converts numbers
to strings as necessary, and vice versa."

When the string "2,3,4" gets converted to a number, the first
non-numeric character finishes the conversion so you get the number 2.

--
Dan Nelson
dnelson@allantgroup.com

>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!!!
>
>
>*^_^*
>
>---------------------------------
> ÑÅ»¢ÓÊÏ䣬ÄúµÄÖÕÉúÓÊÏ䣡

In the last episode (May 15), raid fifa said:
> Jerry Schwartz <jschwartz@the-infoshop.com> ????:
> > 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/...functions.html

--
Dan Nelson
dnelson@allantgroup.com