CODEWITHSUNDEEP
mysqlwhere-clause
Yashwanth Aluru
Yashwanth Aluru

Reputation: 1193

Where clause magic in MySQL

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!

Upvotes: 2

Views: 110

Answers (2)

juergen d
juergen d

Reputation: 204884

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).

Upvotes: 10

Jason Heo
Jason Heo

Reputation: 10246

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)

Upvotes: 1

Related Questions