Bug #65316 SELECT MAX with integer as string comparison returns the wrong result

• Submitted: 15 May 2012 7:02
• Modified: 15 May 2012 7:52
• Reporter: Maarten Kossen
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.5.22
• OS: Linux (5.5.22-0ubuntu1)
• CPU Architecture: Any

[15 May 2012 7:02] Maarten Kossen

Description:
When running a SELECT MAX query on a VARCHAR column and using an integer as a string for comparison, the wrong result gets returned.

For example: if you have a VARCHAR column that contains numbers (and maybe text). The highest number in the column is 100. The numbers 10 and 99 are also in the column, but on different rows each. You want to SELECT MAX to get the highest value from the column. But ONLY when the value is greater than or equal to 100.

When you >= with 100 between quotes, the server returns 99. When you >= with 100 without quotes, the server returns 100.

How to repeat:
Create a table with the following data:

--
-- Table structure for table `bug`
--

CREATE TABLE IF NOT EXISTS `bug` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `bug`
--

INSERT INTO `bug` (`id`, `content`) VALUES
(1, '10'),
(2, '100'),
(3, '99');

Now run:

mysql> select max(content) from bug where content >= '100';

And see:

+--------------+
| max(content) |
+--------------+
| 99           |
+--------------+

Then run:

mysql> select max(content) from bug where content >= 100;

And see:

+--------------+
| max(content) |
+--------------+
| 100          |
+--------------+

[15 May 2012 7:52] Valeriy Kravchuk

Why do you think this is a bug? When you compare values of VARCHAR column with a string, '100', then values are compared with strings, and '99' is the "greatest" of '10', '100' and '99', just because it starts with '9'. When you compare with numeric literal, 100, then values are compared as numbers, strings are converted to numbers before comparison and, surely, '100' is the greatest. 

All this is documented, see http://dev.mysql.com/doc/refman/5.5/en/type-conversion.html for details.