MySQL Bugs: #74496: View creation: error in checking duplicate column names

Bug #74496	View creation: error in checking duplicate column names
Submitted:	22 Oct 2014 8:08	Modified:	22 Oct 2014 17:25
Reporter:	Luca Scomparin	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.6.21	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	view union duplicate column names

Description:
The view creation procedure report a wrong error "Duplicate column name" when an UNION SQL has duplicate column names in the second or third select but the final SQL generating the view will NOT have duplicate column names.

How to repeat:
To repeat, simply execute this command:

CREATE OR REPLACE VIEW `test` AS
SELECT 1 AS a, 2 AS b
UNION ALL
SELECT 1 AS a, 1 AS a;

Worked in 5.6.19:

mysql> CREATE OR REPLACE VIEW `test` AS
    -> SELECT 1 AS a, 2 AS b
    -> UNION ALL
    -> SELECT 1 AS a, 1 AS a;
Query OK, 0 rows affected (0.09 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.19-log |
+------------+
1 row in set (0.03 sec)

Regression bug maybe?

C:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.97-Win X64 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.0 > use test
Database changed
mysql 5.0 > CREATE OR REPLACE VIEW `test` AS
    -> SELECT 1 AS a, 2 AS b
    -> UNION ALL
    -> SELECT 1 AS a, 1 AS a;
ERROR 1347 (HY000): 'test.test' is not VIEW
mysql 5.0 > exit
Bye

C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.41-debug Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 > use test
Database changed
mysql 5.5 > CREATE OR REPLACE VIEW `test` AS
    -> SELECT 1 AS a, 2 AS b
    -> UNION ALL
    -> SELECT 1 AS a, 1 AS a;
ERROR 1060 (42S21): Duplicate column name 'a'

C:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 --prompt="mysql 5.7 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.6-m16-debug Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > use test
Database changed
mysql 5.7 > CREATE OR REPLACE VIEW `test` AS
    -> SELECT 1 AS a, 2 AS b
    -> UNION ALL
    -> SELECT 1 AS a, 1 AS a;
ERROR 1060 (42S21): Duplicate column name 'a'

Server version: 5.6.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE OR REPLACE VIEW `test` AS
    -> SELECT 1 AS a, 2 AS b
    -> UNION ALL
    -> SELECT 1 AS a, 1 AS a;
ERROR 1060 (42S21): Duplicate column name 'a'
mysql> exit
Bye

c:\tmp\mysql-5.6.20-winx64>cd ..

c:\tmp>cd mysql-5.6.19-winx64

c:\tmp\mysql-5.6.19-winx64>bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE OR REPLACE VIEW `test` AS
    -> SELECT 1 AS a, 2 AS b
    -> UNION ALL
    -> SELECT 1 AS a, 1 AS a;
Query OK, 0 rows affected (0.12 sec)

Thank you very much for your bug report.

MySQL has to follow SQL standards. In this particular case, all versions have been followed it strictly, except in the version 5.6, ending with release 5.6.19. However, there was one internal bug dealing with some errors in UNIONs, and fixing of that bug corrected several anomalies, one of which is the behavior that you describe. So, to conclude, MySQL follows SQL-99 standard as of 5.6.20 onwards.

So, why is this standard behavior and why is behavior in 5.6.19 non-standard.

UNION is only one of the set operators, the other being INTERSECT, EXCEPT etc. These operators act on entities only. So, in broader sense its definition is like this:

(Entity_1) UNION [options] (Entity_2)

and in narrower definition it goes like this:

(Table_1) UNION [options] (Table_2)

So, simply, in your case, your second node in the UNION does not satisfy one of the basic conditions that it could be an entity. More precisely, a condition that all attributes have unique names is not satisfied. In more narrower sense, all columns in the table must have unique names.

Standard allows that tables are defined on the spot, which is your case. If you do not put names to your constants, then the server will take care of that detail.

I wonder why standard allows this SELECT to work without problems then, and get "a" and "b" as column names:

mysql> SELECT 1 AS a, 2 AS b
    -> UNION ALL
    -> SELECT 1 AS a, 1 AS a;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
| 1 | 1 |
+---+---+
2 rows in set (0.03 sec)

On a side remark this 

CREATE OR REPLACE VIEW `test` AS
SELECT 1 AS a, 2 AS b
UNION ALL
SELECT 1 AS a, 1 AS a;

.. works in both MariaDB 10.0.14 and 10.1.1

-- Peter
-- not a MySQL/Oracle persn

Thanks guys - you pushed Sinisa to go after me - which cost me a bit of digging in SQL2011 ;-)
So: Valeryi wonders why
SELECT 1 AS a, 2 AS b UNION ALL SELECT 1 AS a, 1 AS a;
is accepted and produces "a" and "b" as column names.
I cannot paste tens of lines of different cross-linked pages of SQL2011 to prove this, but in essence: yes it's a legal query, and in the UNION's result the column name must be "a" for the first column, and implementation-defined for the 2nd column (MySQL chooses "b").
Btw even "SELECT 1 AS a, 1 AS a" is ok.
But, when this gets fed into a DDL statement like CREATE TABLE or CREATE VIEW more rules kick in.
I'm focusing on CREATE VIEW

<continuation of previous post, apparently I hit a magic "post this" key on my keyboard>
So, in "create view", you optionally specify a list of column names:
CREATE OR REPLACE VIEW `test` AS
SELECT 1 AS a, 2 AS b
UNION ALL
SELECT 1 AS a, 1 AS a;
has an empty such list; it's possible to write a non-empty list (foo,bar):
CREATE OR REPLACE VIEW `test` (foo, bar) AS
SELECT 1 AS a, 2 AS b
UNION ALL
SELECT 1 AS a, 1 AS a;
The standard says that if, in the query which forms the "input" of the view, here it means the UNION, a column name is implementation-dependent (and remember it's the case here, for the second column of UNION's result) then the (foo,bar) list is mandatory (it would also be the case if the UNION's columns had duplicated names).
In Peter's query, no (foo,bar) list, so: not legal.
So this is not a bug (phew).

However there is a real bug: adding a (foo,bar) list does not make MySQL accept the query - it should. I will file this in a minute.

The issue with (vv,ww) is now filed as bug#74539