Where clause magic in MySQL

Question

I've found something in MySQL which astonished me a lot. I created a table and inserted records into it as below,

CREATE TABLE `test` (
   `ID` int,
   `NAME` varchar(100)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO test VALUES (1,'a'),(2,'b'),(3,'c');

Now, I run two SELECT queries against this as below,

select * from test where id;

select * from test where name;

I noticed these queries do not throw syntax errors but the "first" select query returns result as select * from test; while the "second" one doesn't return any. This means if the where clause contains an "integer" column I get a result set, while I don't get any if where clause contains a "varchar" column.

Can anyone tell me whether this is a bug or a feature in MySQL? Also explain me why this happens.

Thanks in advance!

What do you want? Do you mean `SELECT id FROM test` ? – Raptor Jan 02 '15 at 07:40 — Raptor, Jan 02 '15 at 07:40

juergen d · Accepted Answer · 2015-01-02T07:48:59.430

10

In MySQL 0 is false and not 0 is true. So

where id for instance is like where 1.

So your ids are all true and return the record.

But name can't be automatically converted to a not 0 number and is false.

BTW if the name column value would start with a number, then it would be converted to that number and be true (for instance 1tom would return in 1).

edited Jan 02 '15 at 07:48

answered Jan 02 '15 at 07:40

juergen d

201,996
37
293
362

Hi,why does this not happen for varchar columns? – Yashwanth Aluru Jan 02 '15 at 07:42
Because a string that does not start with a number will be converted to the number `0` to make the boolean comparison – juergen d Jan 02 '15 at 07:44

score 1 · Answer 2 · answered Jan 02 '15 at 07:48

name columns are converted to 0 because they are not numerical format. It could be fetched if name is '2' with warnings.

mysql> CREATE TABLE test (id INT, name VARCHAR(20));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test VALUES (0, 'a'), (1, 'b'), (2, '2');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test WHERE name;
+------+------+
| id   | name |
+------+------+
|    2 | 2    |
+------+------+
1 row in set, 2 warnings (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'a' |
| Warning | 1292 | Truncated incorrect INTEGER value: 'b' |
+---------+------+----------------------------------------+
2 rows in set (0.00 sec)

Where clause magic in MySQL

2 Answers2