Mysql Question posted in
The official MySQL documentation can be found here.

Unexpected SELECT Query result occur in MySQL version 8.0.19

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?

Tags:

3

Answers


  1. 0

    Error you got is due to missing expression alias:

    No : SELECT A FROM (SELECT MAX(A)      FROM table_a) AS TMP;
Yes: SELECT A FROM (SELECT MAX(A) AS A FROM table_a) AS TMP;

    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:

    SQL> select * from table_a;

         A
----------
       100
       200

    Your query:

    SQL> SELECT *
  2    FROM table_a
  3   WHERE a = (SELECT a
  4                FROM (SELECT MAX (a) FROM table_a) tmp);

         A
----------
       100
       200

    It behaves just as if you wrongly aliased tables:

                            this is wrong
SQL> SELECT *             |
  2    FROM table_a a     v
  3   WHERE a.a = (SELECT a.a
  4                FROM (SELECT MAX (b.a) FROM table_a b) tmp);

         A
----------
       100
       200

    If you properly aliased it, you’d really get an error because there’s no column/alias named A in subquery named TMP:

    SELECT *
  2    FROM table_a a
  3   WHERE a.a = (SELECT tmp.a
  4                  FROM (
  5                          SELECT MAX (b.a)
  6                            FROM table_a b
  7                       ) tmp);
 WHERE a.a = (SELECT tmp.a
                     *
ERROR at line 3:
ORA-00904: "TMP"."A": invalid identifier

    What you really should have done is

    SQL> SELECT *
  2    FROM table_a a
  3   WHERE a.a = (SELECT tmp.a
  4                  FROM (
  5                          SELECT MAX (b.a) a
  6                            FROM table_a b
  7                       ) tmp);

         A
----------
       200

SQL>
    Login or Signup to reply.
  2. 0

    When you do not specify the alias for output column expression then its code is used as a column name:

    -- create table 
CREATE TABLE table_a SELECT 1 A;  
-- Select max value and look for its name
SELECT MAX(A) FROM table_a;
-- Error - unknown column
SELECT A FROM (SELECT MAX(A) FROM table_a) AS TMP;
-- Success, expression as column name is used
SELECT `MAX(A)` FROM (SELECT MAX(A) FROM table_a) AS TMP;
-- Success, an alias is used
SELECT A FROM (SELECT MAX(A) AS A FROM table_a) AS TMP;
    Login or Signup to reply.
  3. 0 
    SELECT * FROM table_a
WHERE A = (SELECT A FROM (SELECT MAX(A) FROM table_a) AS TMP);
>> Retrieves all rows from table A;

    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 :

    create table table_a (
  A int,
  B int
);

insert into table_a values
(10, 10),
(20, 10);

    This query :

    SELECT * FROM table_a
WHERE A = (SELECT A FROM (SELECT MAX(A) FROM table_a) AS TMP);

    Results :

    A   B
10  10
20  10

    And this :

    SELECT * FROM table_a
WHERE A = (SELECT B FROM (SELECT MAX(A) FROM table_a) AS TMP);

    Results :

    A   B
10  10

    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 :

    SELECT * 
FROM table_a
WHERE A = (SELECT TMP.A FROM (SELECT MAX(A) FROM table_a) AS TMP);

    It fails :

    Query Error: Error: ER_BAD_FIELD_ERROR: Unknown column ‘TMP.A’ in
    ‘field list’

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search