Bug #85436 counter-intuition behavior when dealing with alias

• Submitted: 14 Mar 2017 9:15
• Modified: 16 Mar 2017 11:48
• Reporter: 帅 Bang
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6
• OS: Linux
• CPU Architecture: Any

[14 Mar 2017 9:15] 帅 Bang

Description:
mysql> create table t1(a int, b int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(1,1),(2,2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

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

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

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, t1;
+------+------+------+------+
| a    | b    | a    | b    |
+------+------+------+------+
|    1 |    1 |    1 |    1 |
|    2 |    2 |    1 |    1 |
|    1 |    1 |    2 |    2 |
|    2 |    2 |    2 |    2 |
+------+------+------+------+
4 rows in set (0.00 sec)

mysql> select * from (select * from t1) as t;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    2 |
+------+------+
2 rows in set (0.00 sec)

Both 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 ?

thank you very much.

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

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

[16 Mar 2017 11:48] MySQL Verification Team

Hello Bang!

Thank you for the report.
Imho this is an expected behavior because list of table names and aliases for select of each nested level must be unique and each alias resolvable
with single nested query there is no problem, nested select sees one table, outer select sees one table/alias. 

You may observe this with other RDBMS.

Thanks,
Umesh