MySQL bug related with natural number order fix

It seems that this is a known bug but I only noticed it yesterday.

Imagine you have a field named price and it’s type is VARCHAR (it doesn’t matter why it’s not INT or FLOAT) and you want to use ORDER BY clause so you can list table rows ordered by price value.

It won’t work, since the prices will be listed alphabetically, you may read here how to fix this issue.

If you have a table labeled products, a field labeled price (typed as VARCHAR) and the following values: 1, 2, 3, 7, 8, 53, 47, 32, 99 and 327.

And you perform a query similar to the one below:

SELECT price FROM products ORDER BY price;

The result will be:

1, 2, 3, 32, 327,47, 53, 7, 8, 99

As you can see the product prices are ordered alphabetically to fix this you should add + 0 to the query (see example below):

SELECT price FROM products ORDER BY price + 0;

The result will be:

1, 2, 3, 7, 8, 32, 47, 53, 99, 327

Problem solved, now your rows will be sorted by price correctly.

Facebook Twitter Linkedin Plusone