Generated ORDER BY for table-qualified GROUP BY or DISTINCT columns keeps the logic table name in rewritten sharding SQL

Bug Report

Which version of ShardingSphere did you use?

master @ 101c3d56ccd

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

Both

Expected behavior

SELECT t_account.status, COUNT(*) FROM t_account GROUP BY t_account.status routed to several shards is rewritten to ... FROM t_account_0 GROUP BY t_account_0.status ORDER BY t_account_0.status ASC .

Actual behavior

The generated ORDER BY keeps the logic table owner: ... FROM t_account_0 GROUP BY t_account_0.status ORDER BY t_account.status ASC , and MySQL fails with Unknown column 't_account.status' in 'order clause'. SELECT DISTINCT t_account.account_id FROM t_account gets the same ORDER BY t_account.account_id ASC suffix.

Reason analyze (If you can)

ShardingOrderByTokenGenerator.generateSQLToken() (features/sharding/core) uses ColumnOrderByItemSegment.getText(), and OrderByToken only implements Attachable, so the same logic-owner text is attached to every route unit. The sibling ProjectionsToken implements RouteUnitAware and rewrites the owner per route unit. Same root cause as #18122, closed without reproduction.

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

1. Sharding table t_account with actual data nodes db.t_account_${0..1} (test/it/rewriter scenario sharding).
2. Execute the GROUP BY SQL above without a sharding condition.

Example codes for reproduce this issue (such as a github link).

N/A