Table alias is not propagated to subquery

[wolf31o2]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (a INT, b INT);
CREATE TABLE t2 (a INT, b INT);
INSERT INTO t1 VALUES (1, 1);
INSERT INTO t2 VALUES (1, 1);
SELECT one.a FROM t1 one ORDER BY (SELECT two.b FROM t2 two WHERE two.a = one.b);

2. What did you expect to see? (Required)

+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

ERROR 1054 (42S22): Unknown column 'one.b' in 'where clause'

4. What is your TiDB version? (Required)

Release Version: v5.1.0
Edition: Community
Git Commit Hash: 8acd5c88471cb7b4d4c4a8ed73b4d53d6833f13e
Git Branch: heads/refs/tags/v5.1.0
UTC Build Time: 2021-06-24 07:10:32
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[kolbe]

@wolf31o2 you said you identified this problem due to a query generated by Hibernate, is that correct? I don't know a great deal about Hibernate, but is it possible for you to share information about the code path, methods, etc., that cause Hibernate to generate this query?

[wolf31o2]

Hibernate is a Java ORM. I could probably create some code to generate this. This is a simplified test case of a single query which is failing in TiDB, but passes in MySQL, and is automatically generated in Hibernate using the org.hibernate.dialect.MySQL57InnoDBDialect dialect on version 5.5.6.Final but the issue is readily reproducible without Hibernate involved.

[hawkingrei]

@wolf31o2 I've located the cause of the problem and have started working on a fix. When the fix is complete, issue will be updated.

[rebelice]

@wolf31o2 Thanks for your issue. I found that order by (subquery) is not legal. See details in https://dev.mysql.com/doc/internals/en/optimizer-order-by-clauses.html. Could you please tell me the meaning of this query?

[kolbe]

@rebelice how did you come to the conclusion that "order by (subquery) is not legal"? The document you linked to discusses certain order by optimizations, it certainly does not try to cover all the legal syntax of the order by clause.

Please try testing the reproduce steps in MySQL, and you will find that they work correctly there.

[wolf31o2]

Also, this syntax DOES work in TiDB:

SELECT one.a one.b FROM t1 one ORDER BY (SELECT two.b FROM t2 two WHERE two.a = one.b);

So having one.b in the outer query produces a correct query and is processed the same in TiDB as in MySQL. Both syntax work in MySQL.

[yudongusa]

@wolf31o2 I've located the cause of the problem and have started working on a fix. When the fix is complete, issue will be updated.

@hawkingrei is there any update on the fix? I'm assigning this back to you to follow up for now.

[rebelice]

Currently TiDB does not support the following statements
SELECT one.a one.b FROM t1 one ORDER BY (SELECT two.b FROM t2 two WHERE two.a = one.b);
Although it can pass the parser, it cannot get the correct answer like in MySQL. In TiDB, the following order by (sub-query) will become a constant. This is determined by our execution framework.
We will further evaluate this feature and fix this issue while supporting this feature.

[chrysan]

/assign @AilinKid