Non-Literal Numeric Select

I came across an odd situation a recently which highlighted some interesting points surrounding the choice of field types, their uses and a workaround.

My example table structure is as follows;

Field Type NULL Key Default Extra
id int(11) NO PRI auto_increment
col1 varchar(255) YES
col2 int(11) YES

And my data;

id col1 col2
1 123 123
2 159 159
3 456 456
4 789 789
5 963 963
6 987 987
7 951 951
8 741 741
9 258 258
10 369 369
11 1598 1598
12 9512 9512
13 7412 7412
14 3698 3698
15 8523 8523
16 7896 7896
17 6541 6541
18 4563 4563
19 9632 9632
20 4563 4563

From my example data you can see two columns, col1 as varchar and col2 as int.

Now running the same query on both columns produces different results

select * from sample order by col1
id col1 col2
1 123 123
2 159 159
11 1598 1598
9 258 258
10 369 369
14 3698 3698
3 456 456
20 4563 4563
18 4563 4563
17 6541 6541
8 741 741
13 7412 7412
4 789 789
16 7896 7896
15 8523 8523
7 951 951
12 9512 9512
5 963 963
19 9632 9632
6 987 987

And from the results above col1 has returned 123, 159, 1598 and 258.  Hardly the order we’d expect.  So when running the same query on col2;

select * from sample order by col2
id col1 col2
1 123 123
2 159 159
9 258 258
10 369 369
3 456 456
8 741 741
4 789 789
7 951 951
5 963 963
6 987 987
11 1598 1598
14 3698 3698
18 4563 4563
20 4563 4563
17 6541 6541
13 7412 7412
16 7896 7896
15 8523 8523
12 9512 9512
19 9632 9632

This is because of the different field types and their associated comparisons.  If you wanted to list the data by numeric value then int is the correct choice.

The same behaviour can be seen when using the greater than operator;

select * from sample where col1 > '7412'
id col1 col2
4 789 789
5 963 963
6 987 987
7 951 951
12 9512 9512
15 8523 8523
16 7896 7896
19 9632 9632

As you can see from the data returned above, col1 I include 789, 963 and 987 which are all above 7412.  So when we run the same query on col2;

select * from sample where col2 > '7412'
id col1 col2
12 9512 9512
15 8523 8523
16 7896 7896
19 9632 9632

Again col2 gets things right because varchar is not ideal for the number comparison.

BUT WAIT… when using the value as a non-literal value i.e. removing the quotes we get a different result;

select * from sample where col1 > 7412
id col1 col2
12 9512 9512
15 8523 8523
16 7896 7896
19 9632 9632

This gives us the correct result.

The obvious option here is to change the column type to int but this may not always be possible.  I’ve searched around and have no official answer to why the non-literal value works like this, possibly due to the comparison being different by using a numeric instead of text maybe but this may be useful to someone.

No Comments

Leave a Reply