MySQL ORDER BY returns things in (apparently) random order !?

Question

The following query:

SELECT DISTINCT ClassName FROM SiteTree ORDER BY ClassName

is returning things in no apparent order!

I get the same result whether I quote column/table names, or use DISTINCT or not, or add ASC or DESC.

I assumed the indexes might be broken, or something like this, so tried dropping and recreating. Also tried REPAIR TABLE and CHECK TABLE.

The table collation is set to latin1_swedish_ci. All the textual columns are set to use UTF-8 and collation is set to utf8_general_ci

What could be causing this?

Edit:

Sample data at pastie.

Results are direct from SQL query executed in MySQL client (tried 2 different client apps).

Answer 1

It is possible, or indeed likely, that the table is sorted before DISTINCTing. Try using an aggregate function or GROUP BY

Answer 2

If you are looking at the results on a web page you may be just seeing a cached resultset from an earlier query.

Answer 3

There is no reason this should work, by see if getting rid of the mix of camel casing makes a difference:

  SELECT DISTINCT LOWER(LTRIM(ClassName)) AS classname 
  FROM SiteTree ORDER BY classname

I liked Cruachan's idea so much, I updated my out-there idea to cover it. Remove leading spaces.

Answer 4

How was your data loaded? I've seen a few occasions where loading from some external source has placed a whitespace or other similar character in the first position of a string - with the result that the returned result set is actually sorted, but not as one would expect.

It can be incredibly difficult to detect this sort of thing and if I get anomalous results of the type you're seeing on of the first things I tend to do is select the field in question concatenating '>' and '<'.

Answer 5

Eureka!

While it's true that using a function would return the correct order, e.g:

  SELECT DISTINCT ClassName FROM SiteTree ORDER BY REPLACE(ClassName,'','')

It turns out that I was looking at an ENUM column (I'd forgotten, thought it was plain text), and so MySQL was sorting according to the order of the items in the ENUM.

Thanks for the helpful suggestions however.

Here's a reasonable solution, given the situation.

SELECT DISTINCT ClassName FROM SiteTree ORDER BY CAST(ClassName AS CHAR)