Exception: java.sql.SQLIntegrityConstraintViolationException: Column 'created_time' in order clause is ambiguous

[ghost]

code:

        SelectStatementProvider ssp = select(category.id,
                category.name,
                item.id.as("itemId"),
                item.itemName,
                itemImg.url.as("imgUrl"))
                .from(category, "a")
                .leftJoin(item, "b")
                .on(category.id, equalTo(item.catId))
                .leftJoin(itemImg, "c")
                .on(item.id, equalTo(itemImg.itemId))
                .where(category.id, isEqualTo(id))
                .orderBy(item.createdTime)
                .build()
                .render(RenderingStrategies.MYBATIS3);

generated sql:

select
    a.id, a.name, b.id as itemId, b.item_name, c.url as imgUrl
from
    category a
left join
    item b
on
    a.id = b.cat_id
left join
    item_img c
on
    b.id = c.item_id
where
    a.id = ?
order by
    created_time

The tables "item" and "item_img" have the same column "created_time", and the "order by" clause doesn't add table alias as the column's prefix, so it crashed.

[jeffgbutler]

This is by design. There are too many corner cases with "order by" clauses so we take a very simple approach. Please read the documentation about it here: https://mybatis.org/mybatis-dynamic-sql/docs/select.html

There are a couple of solutions:

1. You can add one of the columns to the select list and give it an alias
2. You can use the "sortColumn" function to write the table alias and column into the generated SQL.

[ghost]

Got it, thank you!

[ghost]

Is it possible to add an overloaded method for sortColumn? Just like this:

    public static <T> SortSpecification sortColumn(SqlColumn<T> column, String tableAlias) {
        return SqlBuilder.sortColumn(tableAlias + "." + column.name());
    }

Then we can use it easier.

[jeffgbutler]

Good idea - I'll commit something like this shortly.

[ghost]

Very good!