要不是好奇去试一下，我就信你了。

我在本地无法重现你的问题！

MariaDB 10
MySQL Connector 8.0.28
Java 17

Table:
create table tableX(id int, name varchar(200));
insert into `tableX` values(1, 'test1');
insert into `tableX` values(2, 'test2');
insert into `tableX` values(3, 'test3');

Code:
Connection connection = DriverManager.getConnection("jdbc:mysql://test:test@localhost/test");
PreparedStatement preparedStatement =
connection.prepareStatement("select b from( select n.id as a, n.name as b from tableX n order by a, b ) t;");
ResultSet resultSet = preparedStatement.executeQuery();

while(resultSet.next()) {
System.out.println(resultSet.getString("b"));
}

Output：
test1
test2
test3