All we need is an easy explanation of the problem, so here it is.
There is a table x:
select * from x; +------+------+ | a | b | +------+------+ | aaa | 999 | | bbb | 888 | | ccc | 777 | +------+------+
What do the following queries do?
select * from x order by a < b; select * from x order by a = b; select * from x order by a <> b; select * from x order by a <> b, b;
What is the name for this operator?
I have not found anything in the MySQL documentation on this topic.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
This is common comparison operators.
When comparison is performed then some boolean result is produced. It may be TRUE, FALSE or NULL.
These 3 values are related as
NULL < FALSE < TRUE
This relation is used for sorting.
select * from x order by a<b;
b is NULL then the result of ordering expression (comparison result) is NULL, and such records will be at the beginning of the output.
a less then
b then the result of ordering expression is TRUE and such records will be at the end of the output.
The records where
a is greater or equal to
b will be in the middle.
None of these queries should work at all – they are an abomination. Writing SQL where you can query whether an
INTEGER is less than, equal to, or greater than a
CHAR(n)/VARCHAR(n)/TEXT/<any_character_field> is completely meaningless!
"REAL" database server (PostgreSQL in this case) does with such horrors is shown
here (sample output from one of the queries is shown):
ERROR: operator does not exist: character < integer LINE 1: select *
from x order by a<b;
^ HINT: No operator matches the given name and argument types. You might
need to add explicit type casts.
These queries also fail (praise be!) on every other server on
dbfiddle.uk except for MariaDB which obviously has a vested interest in being bug-for-bug compatible with MySQL. This is hardly surprising as both servers have had a considerable overlap in their main contributors – Monty Widenius is CTO of MariaDB and was a founder of MySQL and MariaDB is touted as a drop-in replacement for MySQL.
Explanation of MySQL’s behaviour:
MySQL’s InnoDB engine has a mechanism for generating IMPLICIT PRIMARY KEYs, even it the dba/dev doesn’t declare one!
here, we have (a quote from MySQL’s documentation – linked to in post):
If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB
internally generates a hidden clustered index named GEN_CLUST_INDEX on
a synthetic column containing row ID values. The rows are ordered by
the ID thatInnoDB assigns to the rows in such a table. The row ID is a
6-byte field that increases monotonically as new rows are inserted.
Thus, the rows ordered by the row ID are physically in insertion
Unfortunately, you can’t access this pseudo-column. There are no accessible hidden fields – unlike say with Oracle’s server which has a plethora of such fields which one can
In InnoDB, if you don’t provide a PRIMARY KEY (and…), a hidden
BIGINT will be used as the PK. But you cannot get at it.
The documentation referred to in the previous link says it’s a 6 Byte
INTEGER and not a BIGINT (which has
8 Bytes - 64 bit – not relevant to this discussion however).
Another piece of evidence is a post
Bill Karwin. He was either a or the MySQL community manager and, as you can see from his profile, is a fairly big hitter on StackOverflow with > 400,000 points. He says:
Without a clear ORDER BY, current versions of InnoDB return rows in
the order of the index it reads from. Which index varies, but it
always reads from some index. Even reading from the "table" is really
an index—it’s the primary key index.
As in the comments above, there’s no guarantee this will remain the
same in the next version of InnoDB. You should treat it as a
coincidental behavior, it is not documented and the makers of MySQL
don’t promise not to change it.
Also in that post, he gives examples of how MySQL will behave with different
INDEXes – it’s worth reading the entire thread!
So, basically, it appears to be the case that MySQL will sort by the implicit
PK (which is in
INSERT order) if there is no other
KEY field in the
SELECT! A couple of simple tests[
2] demonstrate this in action (results from first test only, refer to the other one if interested):
CREATE TABLE a (b VARCHAR(10), c INT); INSERT INTO a VALUES ('aaa', 999), ('bbb', 888), ('ccc', 777);
CREATE TABLE x (y VARCHAR(10), z INT); INSERT INTO x VALUES ('ccc', 777), ('bbb', 888), ('aaa', 999);
SELECT * FROM a;
b c aaa 999 bbb 888 ccc 777
This could be a simple ASCII sort on the first field. However, now compare this to:
SELECT * FROM x;
y z ccc 777 bbb 888 aaa 999
But this is NOT an ASCII sort on the first field. There is no PK, therefore it uses an
(implicit) clustered index PK (*) which sorts by insertion order!
(*) this is the way MySQL does PKs – not all servers do this – I can’t, off the top of my head, think of another system that does this by default. All PKs in MySQL are clustered.
I’ve rewritten your queries (see test 1) so that you can better see what’s happening!
select a, b, a = b from x order by a = b;
a b a = b aaa 999 0 bbb 888 0 ccc 777 0
Here, the expression
a = b is ALWAYS false, therefore it sorts by hidden PK which is dependent on INSERT order!
select a, b, a <> b from x order by a <> b, b;
a b a <> b ccc 777 1 bbb 888 1 aaa 999 1
In this second case,
a != b is ALWAYS true, therefore it sorts by the second (this time
explicitly specified) field in the ORDER BY which is (
b)! No need to rely on the implicit PK!
You should be able to figure out the rest from there!
As stated by Bill Karwin referenced above, this is an undocumented feature and should NOT be relied upon and can be removed/modified at any time.
There is general agreement in the database community that if you want a given order, then you should specify it in the
ORDER BY clause and NOT rely on undocumented features that could potentially change in future releases!
p.s. welcome to the forum! And a +1 for reminding me about how crap MySQL is!
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂