-
Notifications
You must be signed in to change notification settings - Fork 211
Description
🐞 Bug Report: orderBy(...)
does not generate fully-qualified column name
Version: mybatis-dynamic-sql: 1.5.2
Problem Description
When using .orderBy(FavoriteDynamicSqlSupport.favoriteTable.createdAt.descending())
in a multi-table join, the generated SQL is:
ORDER BY created_at DESC
Even though both tables (detail_table
and favorite_table
) have a created_at
column, the generated SQL does not qualify the column with its table alias or name.
This causes SQL to sort by the wrong created_at
column or leads to ambiguity in some databases.
Example Java DSL
List<Detail> details = detailMapper.select(dsl ->
dsl.join(FavoriteDynamicSqlSupport.favoriteTable)
.on(DetailDynamicSqlSupport.detailId, equalTo(FavoriteDynamicSqlSupport.detailId))
.where(FavoriteDynamicSqlSupport.userId, isEqualTo(userId))
.and(DetailDynamicSqlSupport.someTagId, isEqualTo(request.getSomeTagId()))
.and(DetailDynamicSqlSupport.deletedState, isEqualTo(false))
.orderBy(FavoriteDynamicSqlSupport.favoriteTable.createdAt.descending())
);
Expected SQL
ORDER BY favorite_table.created_at DESC
Actual SQL
ORDER BY created_at DESC
Workaround (verbose):
.orderBy(FavoriteDynamicSqlSupport.createdAt
.as(FavoriteDynamicSqlSupport.favoriteTable.tableNameAtRuntime() + "." + FavoriteDynamicSqlSupport.createdAt.name())
.descending())
But this is not elegant or maintainable.
Suggestion
Add support for auto-prefixing table name or alias when calling:
.orderBy(FavoriteDynamicSqlSupport.favoriteTable.createdAt.descending())
Or introduce a utility method like:
.orderBy(FavoriteDynamicSqlSupport.createdAt.qualifiedWith("favorite_table").descending())
Summary
This makes .orderBy()
unsafe when columns are not unique across joined tables, and breaks expected behavior. Please consider addressing this in the next patch version.
Thanks for your great work on this library!