Bug #5712 Column type mediumint(9) fails to find record when value sent in quotes
Submitted: 23 Sep 2004 8:55 Modified: 24 Sep 2004 11:55
Reporter: Jeremy and Rob Sher and Lloyd, respectively Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.18max OS:Linux (RedHat 9)
Assigned to: CPU Architecture:Any

[23 Sep 2004 8:55] Jeremy and Rob Sher and Lloyd, respectively
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.
[24 Sep 2004 11:55] MySQL Verification Team
Hi,

Thank you for the report, but I wasn´t able to repeat it on the latest BK 4.0 tree.