CODEWITHSUNDEEP
mysqlsql
Keerthana Prabhakaran
Keerthana Prabhakaran

Reputation: 3787

Searching with a column that has `|` character as its value in mysql

With a as a column in table b, I'd like to understand why the search fetches a row for a=0 condition!

mysql> select * from (select "0|679501|3371371|0" as a) b where a=0;
+--------------------+
| a                  |
+--------------------+
| 0|679501|3371371|0 |
+--------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> select * from (select "079501|3371371|0" as a) b where a=0;
Empty set, 1 warning (0.04 sec)

mysql> select * from (select "None|679501|3371371|0" as a) b where a=0;
+-----------------------+
| a                     |
+-----------------------+
| None|679501|3371371|0 |
+-----------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> select * from (select "null|679501|3371371|0" as a) b where a=0;
+-----------------------+
| a                     |
+-----------------------+
| null|679501|3371371|0 |
+-----------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> select * from (select "679501|null|3371371|0" as a) b where a=0;
Empty set, 1 warning (0.01 sec)

Thanks in advance!

Upvotes: 1

Views: 67

Answers (3)

lurker
lurker

Reputation: 58244

The results have nothing to do with the fact that the delimiter you are using is |. It would be the same with any non-numeric character. Likewise, neither null nor None are special in this context. That could be any string.

In the expression, 0="0|679501|3371371|0", MySQL is doing a "string to int" on the string and comparing with 0. It's behavior is similar to how atoi in C works. Parsing stops at the first non-numeric character. If the string doesn't start with a numeric character, then it yields 0.

You can simplify examining the behavior with the following queries:

> select 0="0|1|2";
+-----------+
| 0="0|1|2" |
+-----------+
|         1 |
+-----------+
1 row in set, 1 warning (0.00 sec)

"0|1|2" converted to an integer is 0. Parsing stops at |. Comparing 0=0 gives 1.

> select 0="0x1x2";
+-----------+
| 0="0x1x2" |
+-----------+
|         1 |
+-----------+
1 row in set, 1 warning (0.00 sec)

"0x1x2" converted to an integer is 0. Parsing stops at |. Comparing 0=0 gives 1.

> select 0="1|2|0";
+-----------+
| 0="1|2|0" |
+-----------+
|         0 |
+-----------+
1 row in set, 1 warning (0.00 sec)

"1|2|0" converted to an integer is 1. Parsing stops at |. Comparing 0=1 gives 0.

> select 1="1x2x0";
+-----------+
| 1="1x2x0" |
+-----------+
|         1 |
+-----------+
1 row in set, 1 warning (0.00 sec)

"1x2x0" converted to an integer is 1. Parsing stops at |. Comparing 1=1 gives 1.

> select 0="null|1|2";
+--------------+
| 0="null|1|2" |
+--------------+
|            1 |
+--------------+
1 row in set, 1 warning (0.00 sec)

"null|1|2" converted to an integer is 0 since the string doesn't start with a numeric and parsing stops immediately. Default value is 0. Comparing 0=0 gives 1.

> select 0="foo|1|2";
+-------------+
| 0="foo|1|2" |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

"foo|1|2" converted to an integer is 0 since the string doesn't start with a numeric and parsing stops immediately. Default value is 0. Comparing 0=0 gives 1.

Upvotes: 2

revo
revo

Reputation: 48711

This is due to an implicit type conversion according to operands coercibilities. Your first query:

select * from (select "0|679501|3371371|0" as a) b where a=0;

returns a row since 0|679501|3371371|0 has a numeric character (0) in its beginning which is equal to the other side of comparison on conversion but this:

select * from (select "9|679501|3371371|0" as a) b where a=0;

returns null. MySQL automatically converts numbers to strings as necessary, and vice versa.

Upvotes: 0

mike.k
mike.k

Reputation: 3447

This is a result of how MySQL casts text/varchar to integer.

select *, cast(b.a as unsigned) from (select "None|679501|3371371|0" as a) b where a=0 gives 0 for the second column.

If you cast the integer to text, then you get 0 rows as expected: select * from (select "None|679501|3371371|0" as a) b where a='0'

Upvotes: 2

Related Questions