Unnecessary COALESCE function used in ORDER BY clause harms performance

Problem

It's observed such SQL statement is generated when I run Broadleaf demo site (the issue is here because the cause stems from Broadleaf framework):

SELECT  (omit projections)
FROM      blc_product productimp0_ 
LEFT JOIN blc_product_bundle productimp0_1_ 
ON        productimp0_.product_id = productimp0_1_.product_id 
WHERE     productimp0_.archived = 0 
OR        productimp0_.archived IS NULL 
ORDER BY  coalesce(productimp0_.product_id, 9223372036854775807) ASC 
LIMIT     50

COALESCE is to support nulls-last sorting policy. It's unnecessary here because the column product_id is the primary key so definitely non-null. Of course it won't produce wrong result, but is a performance vulnerability - DBMS is prevented from leveraging index and resort to full table sort. I have run EXPLAIN on the statement in MySQL 5.7.25 (with 10k rows populated in blc_product table). The output shows

+----+-------------+----------------+------------+--------+---------------+---------+---------+----------------------------------------+------+----------+-----------------------------+
| id | select_type | table          | partitions | type   | possible_keys | key     | key_len | ref                                    | rows | filtered | Extra                       |
+----+-------------+----------------+------------+--------+---------------+---------+---------+----------------------------------------+------+----------+-----------------------------+
|  1 | SIMPLE      | productimp0_   | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                                   |    1 |   100.00 | Using where; Using filesort |
|  1 | SIMPLE      | productimp0_1_ | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | broadleaf_test.productimp0_.PRODUCT_ID |    1 |   100.00 | NULL                        |
+----+-------------+----------------+------------+--------+---------------+---------+---------+----------------------------------------+------+----------+-----------------------------+

While without COALESCE the output becomes:

+----+-------------+----------------+------------+--------+---------------+---------+---------+----------------------------------------+------+----------+-------------+
| id | select_type | table          | partitions | type   | possible_keys | key     | key_len | ref                                    | rows | filtered | Extra       |
+----+-------------+----------------+------------+--------+---------------+---------+---------+----------------------------------------+------+----------+-------------+
|  1 | SIMPLE      | productimp0_   | NULL       | index  | NULL          | PRIMARY | 8       | NULL                                   |    1 |   100.00 | Using where |
|  1 | SIMPLE      | productimp0_1_ | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | broadleaf_test.productimp0_.PRODUCT_ID |    1 |   100.00 | NULL        |
+----+-------------+----------------+------------+--------+---------------+---------+---------+----------------------------------------+------+----------+-------------+

And a rough micro-benchmark shows the former version may spend ~10x time longer than the latter one.

Cause

https://github.com/BroadleafCommerce/BroadleafCommerce/blob/38b317fb10bdc5f39556e732e8fe368dd4fd8918/admin/broadleaf-open-admin-platform/src/main/java/org/broadleafcommerce/openadmin/server/service/persistence/module/criteria/CriteriaTranslatorImpl.java#L304-L317

Notice line 309. javax.persistence.criteria.CriteriaBuilder#coalesce is called conditionally on filterMapping.getNullsLast(), and will direct Hibernate to generate SQL with COALESCE.

I encountered this problem when call the API POST /admin/category/{id}/allProductXrefs/add. It goes from org.broadleafcommerce.openadmin.web.controller.entity.AdminBasicEntityController#addCollectionItem to CriteriaTranslatorImpl::addSorting. Throughout the path, filterMapping.nullsLast remains its default value - true. Thus line 309 is eventually reached and the problem occurs.

I suppose there are other paths would trigger this problem, since CriteriaTranslatorImpl is used in several places.

Possible Solution

Inspect the metadata to check if a column is nullable or not, and avoid unnecessary coalesce call accordingly.

Start in admin/broadleaf-open-admin-platform/src/main/java/org/broadleafcommerce/openadmin/server/service/persistence/module/criteria/CriteriaTranslatorImpl.java around lines 304-317, especially addSorting and the conditional CriteriaBuilder#coalesce call. Trace the path from AdminBasicEntityController#addCollectionItem and inspect how filterMapping.nullsLast and column nullability are represented. Done means nullable columns retain nulls-last behavior while non-null columns avoid unnecessary COALESCE and the generated SQL can use the primary-key index.