Description:
Usually the syntax
SELECT * FROM `tablename` WHERE `user_id`='1'
works, where `user_id` is of an integer type. However, in our table with a field `user_id` of type mediumint(9), MySQL finds a record when the single quotes are omitted, but fails to typecast the value '1' as an integer like it should. Here are the results:
mysql> SELECT query_id FROM compound_queries WHERE user_id='1' ORDER BY queryname ;
Empty set (0.00 sec)
mysql> SELECT query_id FROM compound_queries WHERE user_id=1 ORDER BY queryname ;
+----------+
| query_id |
+----------+
| 656 |
+----------+
1 row in set (0.00 sec)
Here is the table explained:
mysql> explain compound_queries;
+--------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+------------+----------------+
| query_id | int(11) | | PRI | NULL | auto_increment |
| user_id | mediumint(9) | | | 0 | |
| queryname | varchar(20) | YES | MUL | NULL | |
| total_count | int(11) | | | 0 | |
| query_list | blob | YES | | NULL | |
| datecreated | date | | | 0000-00-00 | |
| datelastused | date | | | 0000-00-00 | |
| datemodified | date | | | 0000-00-00 | |
+--------------+--------------+------+-----+------------+----------------+
8 rows in set (0.00 sec)
How to repeat:
Create a table using:
CREATE TABLE `compound_queries` (
`query_id` int(11) NOT NULL auto_increment,
`user_id` mediumint(9) NOT NULL default '0',
`queryname` varchar(20) default NULL,
`total_count` int(11) NOT NULL default '0',
`query_list` blob,
`datecreated` date NOT NULL default '0000-00-00',
`datelastused` date NOT NULL default '0000-00-00',
`datemodified` date NOT NULL default '0000-00-00',
PRIMARY KEY (`query_id`),
KEY `queryname` (`queryname`)
) TYPE=MyISAM ;
and insert some data; be sure to insert integer values into field `user_id`. Then run:
mysql> SELECT query_id FROM compound_queries WHERE user_id='1' ORDER BY queryname ;
mysql> SELECT query_id FROM compound_queries WHERE user_id=1 ORDER BY queryname ;
Suggested fix:
All the variations of the integer field type should behave the same way with regard to query syntax and typecasting.