Search code examples
sqlspringjooq

Select Distinct Not working in Spring with DSL Context

I have the ff. code.

 @Override
    public Optional<TransactionJournalRecord> findByReferenceNumber(final String referenceNumber) {
        return this.dsl
            .select(TRANSACTION_JOURNAL.fields())
            .distinctOn(TRANSACTION_JOURNAL.CUSTOMER_NUMBER)
            .from(TRANSACTION_JOURNAL)
            .where(TRANSACTION_JOURNAL.REFERENCE_NUMBER.eq(referenceNumber))
            .fetchOptionalInto(TransactionJournalRecord.class);
    }

All I want it to do is to query a specific reference number but only getting the first distinct ref. no. as I want other duplicate transactions with the same ref no and customer number to be processed later on.

But I kept getting this error of

org.springframework.jdbc.BadSqlGrammarException: Access database using jOOQ; bad SQL grammar [select distinct on (`transaction_journal`.`customer_number`) `transaction_journal`.`id`, `transaction_journal`.`reference_number`, `transaction_journal`.`future_dated_transaction_id`, `transaction_journal`.`send_money_type_id`, `transaction_journal`.`source_account_number`, `transaction_journal`.`source_account_type`, `transaction_journal`.`customer_number`, `transaction_journal`.`request_id`, `transaction_journal`.`destination_account_number`, `transaction_journal`.`destination_account_type`, `transaction_journal`.`destination_validation`, `transaction_journal`.`transfer_schedule_type`, `transaction_journal`.`currency_id`, `transaction_journal`.`amount`, `transaction_journal`.`service_fee`, `transaction_journal`.`transaction_date`, `transaction_journal`.`posting_date`, `transaction_journal`.`status`, `transaction_journal`.`remarks`, `transaction_journal`.`created_date`, `transaction_journal`.`updated_date`, `transaction_journal`.`source_account_name`, `transaction_journal`.`username`, `transaction_journal`.`reason`, `transaction_journal`.`card_number`, `transaction_journal`.`status_remarks`, `transaction_journal`.`creditor_bank_code`, `transaction_journal`.`creditor_details`, `transaction_journal`.`mobile_number`, `transaction_journal`.`address`, `transaction_journal`.`channel_id`, `transaction_journal`.`system`, `transaction_journal`.`purpose_of_transaction`, `transaction_journal`.`esb_posted_date`, `transaction_journal`.`currency_id_destination`, `transaction_journal`.`gl_pa_status`, `transaction_journal`.`gl_sf_status`, `transaction_journal`.`gl_status_remarks`, `transaction_journal`.`email_address`, `transaction_journal`.`exchange_rate`, `transaction_journal`.`contact_type`, `transaction_journal`.`contact_value`, `transaction_journal`.`is_validated` from `transaction_journal` where `transaction_journal`.`reference_number` = ?]; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on (`transaction_journal`.`customer_number`) `transaction_journal`.`id`, `transa' at line 1
    at org.jooq_3.11.12.MYSQL_8_0.debug(Unknown Source)
Solution

  • Using DISTINCT ON

    You're not using DISTINCT, you're using DISTINCT ON, which is a PostgreSQL vendor specific SQL feature. In newer versions of jOOQ, DISTINCT ON is being emulated using window functions for other dialects, so you might want to upgrade.

    You'll still need an ORDER BY clause for DISTINCT ON to work. It's a bit of an esoteric PostgreSQL invention, adding to the confusion of the logical order of operations in SQL.

    Using LIMIT

    While what you want to do is possible with DISTINCT ON, it seems overly complicated. Here's a much simpler way to solve your problem, producing an arbitrary record, or optionally, if you uncomment the ORDER BY clause, the first/last record given some ordering:

    SELECT *
FROM transaction_journal
WHERE transaction_journal.reference_number = :referenceNumber
-- ORDER BY something
LIMIT 1

    With jOOQ:

    @Override
public Optional<TransactionJournalRecord> findByReferenceNumber(
    final String referenceNumber
) {
    return this.dsl
        .selectFrom(TRANSACTION_JOURNAL)
        .where(TRANSACTION_JOURNAL.REFERENCE_NUMBER.eq(referenceNumber))
        // .orderBy(something)
        .limit(1)
        .fetchOptional();
}

    Using GROUP BY

    Note that in MySQL, if strict mode is turned off, then the GROUP BY approach you've mentioned in your comments will also produce an arbitrary value for all non-GROUP BY columns, which is not correct standard SQL.

    Unlike as with DISTINCT ON or LIMIT, you have no control over which value is being produced. As a matter of fact, you can't even be sure if two non-GROUP BY values belong to the same record. It is never a good idea to depend on this outdated, MySQL-specific behaviour.

    Using DISTINCT

    There is no way to solve this with DISTINCT only. If you don't have a unique constraint on your search criteria, then you will always get duplicates, which will throw an exception when using fetchOptional(), in jOOQ.