I expected an error to occur with the following query, but instead, the query is executed in an unintended direction.
Copy code
SELECT MAX(A) FROM table_a;
>> 1
SELECT A FROM (SELECT MAX(A) FROM table_a) AS TMP;
>> ERROR
SELECT * FROM table_a
WHERE A = (SELECT A FROM (SELECT MAX(A) FROM table_a) AS TMP);
>> Retrieves all rows from table A;
I thought there would be an error in the third query due to the WHERE clause, but no error occurs, and it retrieves all rows from the table. Could you please explain how the third query is functioning?
3
Answers
Error you got is due to missing expression alias:
Query #3 you posted is suspicious to you, as you expected it to raise an error. Why didn’t it? It would, if you properly aliased columns (i.e. preceded column names with table names (or table aliases)). The way it is, column names (A) are ambiguous, you can’t really tell which table’s (or subquerie’s) "A" you actually meant and database took what it found appropriate.
(Example is based on my Oracle database (hence Oracle’s error codes and messages), but disregard that – it is just the same in MySQL (you use)).
Sample data:
Your query:
It behaves just as if you wrongly aliased tables:
If you properly aliased it, you’d really get an error because there’s no column/alias named
A
in subquery namedTMP
:What you really should have done is
When you do not specify the alias for output column expression then its code is used as a column name:
We can see that the subquery error not propagated to outer query !
As A doesn’t exist in the TMP table, it will use the column from the table_a !
If we take this simple data :
This query :
Results :
And this :
Results :
We can see that the Column A/B from table TMP have been replaced by Column A/B from
table_a
.As mentioned by @jarlh in comments you should qualify all your columns when you are dealing with multiple tables/subqueries.
This can be done using full table name or aliases to ensure which table you are using per column.
If I add the alias to the column A :
It fails :
Demo here