MySQL Bugs: #71039: non-standard usage of table alias allowed in SELECT but not in CREATE VIEW

Bug #71039	non-standard usage of table alias allowed in SELECT but not in CREATE VIEW
Submitted:	29 Nov 2013 14:20
Reporter:	Guilhem Bichot	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.7, 5.7.14	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In SQL2011 an alias is "standalone", has no "database". Thus:
select mysqltest_db1.foo.gid
  from mysqltest_db2.t1 as foo;
is illegal.

But in MySQL it is:

create database mysqltest_db1;
create table mysqltest_db1.t1(gid int);
insert into mysqltest_db1.t1 values(333);

create database mysqltest_db2;
create table mysqltest_db2.t1(gid int);
insert into mysqltest_db2.t1 values(0);

let $query=
select mysqltest_db1.foo.gid as xx,mysqltest_db2.foo.gid as yy
  from mysqltest_db2.t1 as foo, mysqltest_db1.t1 as foo;
eval $query;

The SELECT above is accepted, and returns:
+xx	yy
+333	0

But when the same SELECT in in a view:

eval create view v1 as $query;
show create view v1;
drop view v1;
drop database mysqltest_db1;
drop database mysqltest_db2;

then CREATE VIEW is accepted but SHOW fails:
1052: Column 'foo.gid' in field list is ambiguous

How to repeat:
see description

Suggested fix:
deprecate non-standard behaviour of allowing db.table_alias

-- 5.7.14

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.14: bin/mysql -uroot -S /tmp/mysql_ushastry.sock --prompt='5.7.14>'
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.14-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2016, 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.

5.7.14>create database test;
Query OK, 1 row affected (0.00 sec)

5.7.14>use test
Database changed
5.7.14>create database mysqltest_db1;
Query OK, 1 row affected (0.00 sec)

5.7.14>create table mysqltest_db1.t1(gid int);
Query OK, 0 rows affected (0.01 sec)

5.7.14>insert into mysqltest_db1.t1 values(333);
Query OK, 1 row affected (0.00 sec)

5.7.14>
5.7.14>create database mysqltest_db2;
Query OK, 1 row affected (0.00 sec)

5.7.14>create table mysqltest_db2.t1(gid int);
Query OK, 0 rows affected (0.00 sec)

5.7.14>insert into mysqltest_db2.t1 values(0);
Query OK, 1 row affected (0.00 sec)

5.7.14>select mysqltest_db1.foo.gid as xx,mysqltest_db2.foo.gid as yy
    ->   from mysqltest_db2.t1 as foo, mysqltest_db1.t1 as foo;
+------+------+
| xx   | yy   |
+------+------+
|  333 |    0 |
+------+------+
1 row in set (0.00 sec)

5.7.14>
5.7.14>create view v1 as select mysqltest_db1.foo.gid as xx,mysqltest_db2.foo.gid as yy
    ->   from mysqltest_db2.t1 as foo, mysqltest_db1.t1 as foo;
Query OK, 0 rows affected (0.00 sec)

5.7.14>show create view v1;
ERROR 1052 (23000): Column 'foo.gid' in field list is ambiguous
5.7.14>