Why does my LIMITed SELECT fetch the last record of the table, not the first?

Question

I read that to fetch the first table record, you may use LIMIT 1. I tried to apply that knowledge to my JOIN statement and got a diametrically opposite result. It joined the last record of the joined table

SELECT * FROM users;

user_id	username	[more columns]
1	mickey_m	[...]
[...]	[...]	[...]
6	daisy_d	[...]

SELECT * FROM roles;

role_id	role
1	USER
2	ADMIN

INSERT IGNORE INTO user_role (user_id, username, role_id, role)
SELECT users.id, users.username, roles.id, roles.role
FROM users
JOIN roles ON roles.role = 'ADMIN' LIMIT 1;

SELECT * FROM user_role;

user_id	username	role_id	role
6	daisy_d	2	ADMIN

Why did it happen? It seems unnatural for LIMIT to limit the result set to the last record as opposed to the first one

Even this

INSERT IGNORE INTO user_role (user_id, username, role_id, role)
SELECT first_row.id, first_row.username, roles.id, roles.role
FROM (SELECT id, username FROM users LIMIT 1) AS first_row
JOIN roles ON roles.role = 'ADMIN';

SELECT * FROM user_role;

produces the same

In both cases, I expected to get this instead

user_id	username	role_id	role
1	mickey_m	2	ADMIN

Does SELECT select from the bottom up in some cases? It seems so because plain SELECTs like this one

SELECT * FROM users LIMIT 1;

do indeed retrieve the first row. However, if you specify columns

SELECT id, username FROM users LIMIT 1;

you observe the same issue (the last row gets retrieved instead)

Sage (a chat bot) says when it comes to LIMIT, no order is guaranteed so I should use ORDER BY (it does help, by the way). Is it right? Does LIMIT 1 limit the result set to a random row that satisfies the criteria? Then why is it always the last one? The likelihood of that is infinitesimally small

The behavior you are observing is likely due to the use of the LIMIT clause. When you use LIMIT without an explicit ORDER BY clause, the database will return an arbitrary set of rows that satisfy the query conditions, but the order of the rows is not guaranteed. In other words, the database may return the first row, the last row, or any other row that satisfies the query conditions.

–Sage

Answer 1

The order of the results of a SELECT query are never guaranteed when there's no ORDER BY clause specified. This is true in pretty much all modern relational database systems (as per the SQL standard).

You can find more information specific to MySQL in this DBA.StackExchange.com answer:

In the SQL world, order is not an inherent property of a set of data. Thus, you get no guarantees from your RDBMS that your data will come back in a certain order -- or even in a consistent order -- unless you query your data with an ORDER BY clause.

• MySQL sorts the records however it wants without any guarantee of consistency.
• If you intend to rely on this order for anything, you must specify your desired order using ORDER BY. To do anything else is to set yourself up for unwelcome surprises.

---

To your question "«Then why is it always the last one?»":

You can almost attribute this to luck on repeated runs (the proper term is nondeterminsim - not quite the same as random). As mentioned above "MySQL sorts the records however it wants". What this really means is that the algorithm of the database engine returns the rows based on the results of a multitude of operations that it goes through under the hood. For now, it appears to be in a repeatable order, but because of the complexity of the database engine and that promised lack of guarantee without an ORDER BY clause, it will not always be repeatable and can change for a multitude of reasons, unexpectedly.

So you should not rely on the ordering you see today to be the same one you'll see in the future - without an ORDER BY clause.