D.R. D.R. - 1 year ago 74
MySQL Question

Sub select fails with "Duplicate column name 'NULL'"

I've extracted the following "complete minimal example" from my actual query:

SELECT * FROM ( SELECT 1, null, null ) AS tmp LIMIT 1

Expected result:

| 1 | NULL | NULL |

Actual result:

1060 - Duplicate column name 'NULL'

What am I doing wrong? Do I really have to convert my query into the following very cumbersome variation:

SELECT tmp.a, tmp.b, tmp.c FROM ( SELECT 1 a, null b, null c ) AS tmp LIMIT 1

If yes, why so? Normally, MySQL doesn't try to hurt me :o

Answer Source

Why is MySQL throwing the error? Becase column names must be unique within a table.

In the example query, tmp is a derived table, consisting of three columns.

If you assign an alias to an expression in the SELECT list, MySQL will use the alias as the column name. Without an alias, MySQL will derive a column name to assign.

Your query is essentially shorthand for:

  SELECT tmp.*
    FROM ( SELECT 1     AS `1` 
                , null  AS `NULL`
                , null  AS `NULL`
         ) tmp
   LIMIT 1

As a workaround, just assign a unique alias to either the 2nd or 3rd columns.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download