Bug #85492 counter-intuition behavior when dealing with alias
Submitted: 17 Mar 2017 2:26 Modified: 20 Mar 2017 9:06
Reporter: 帅 Bang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6, 5.6.35, 5.7.17, 5.5.54 OS:Linux
Assigned to: CPU Architecture:Any
Tags: DML

[17 Mar 2017 2:26] 帅 Bang
Description:
Sorry to bother you again. 

At first sight this post is identical with Bug#85436 (https://bugs.mysql.com/bug.php?id=85436), while, it is not , maybe.

MySQL > create table t(a int, b int);
Query OK, 0 rows affected (0.05 sec)
MySQL > create table t1(a int, b int);
Query OK, 0 rows affected (0.04 sec)
MySQL > insert into t1 values(1,2);
Query OK, 1 row affected (0.04 sec)
MySQL > insert into t values(1,2);
Query OK, 1 row affected (0.04 sec)

OK, now, let us perform these statements one by one:

MySQL > select * from t, t;
ERROR 1066 (42000): Not unique table/alias: 't'
MySQL > select * from t1 as t, t;
ERROR 1066 (42000): Not unique table/alias: 't'
MySQL > select * from (select * from t1) t, (select * from t1) t;
ERROR 1066 (42000): Not unique table/alias: 't'

All failed. Let us move on please:

MySQL > select * from (select * from t1) as t, t;
+------+------+------+------+
| a    | b    | a    | b    |
+------+------+------+------+
|    1 |    2 |    1 |    2 |
+------+------+------+------+
1 row in set (0.03 sec)

It succeed.

After referring the doc of mysql5.6, I found nothing which can do me a favor to tell what is really going on here.

Would you please help me figure out what the rule and mechanism are when mysql resolves alias in a query ?

Is this a bug of mysql?

thank you very much.

How to repeat:
create table t(a int, b int);
create table t1(a int, b int);
insert into t1 values(1,2);
insert into t values(1,2);

select * from t, t;
select * from t1 as t, t;
select * from (select * from t1) t, (select * from t1) t;

select * from (select * from t1) as t, t;
[20 Mar 2017 9:06] MySQL Verification Team
Thank you for the report and test case.
Imho, last query should have been failed but it succeeded.