Provide a default secondary sort column in grails 3.3.9

I have an issue where when using createCriteria().list(params) to page through a set of 12 records by 10 records/page, the 2 records shown on page 2 had appeared on page 1. A default 'sort' was specified in the domain mapping, but the specified column contains duplicates, so I'm assuming the order of the subset with duplicates in the sort column is nondeterministic.

This also uses sortable column headers, as does grails when you do generate-all. Does this mean anytime the selected sort column contains duplicates the order can be nondeterministic? Is there a way (other than brute force) to pass a default secondary sort column (maybe ID) to be used in addition to the selected column to guarantee order?

This is on SQL Server and these are the generated queries:

page 1:

select TOP(?) 
    this_.procedure_occurrence_id as procedur1_22_1_, 
    this_.provenance_id as provenan2_22_1_, 
    this_.procedure_date as procedur3_22_1_, 
    this_.procedure_type_concept_id as procedur4_22_1_, 
    this_.procedure_source_value as procedur5_22_1_, 
    this_.associated_provider_id as associat6_22_1_, 
    this_.relevant_condition_concept_id as relevant7_22_1_, 
    this_.visit_occurrence_id as visit_oc8_22_1_, 
    this_.person_id as person_i9_22_1_, 
    this_.procedure_concept_id as procedu10_22_1_, 
    person_ali1_.person_id as person_i1_20_0_, 
    person_ali1_.person_source_value as person_s2_20_0_, 
    person_ali1_.provenance_id as provenan3_20_0_, 
    person_ali1_.location_id as location4_20_0_, 
    person_ali1_.gender_concept_id as gender_c5_20_0_, 
    person_ali1_.care_site_id as care_sit6_20_0_, 
    person_ali1_.year_of_birth as year_of_7_20_0_, 
    person_ali1_.provider_id as provider8_20_0_, 
    person_ali1_.race_concept_id as race_con9_20_0_, 
    person_ali1_.month_of_birth as month_o10_20_0_, 
    person_ali1_.ethnicity_concept_id as ethnici11_20_0_, 
    person_ali1_.ethnicity_source_value as ethnici12_20_0_, 
    person_ali1_.race_source_value as race_so13_20_0_, 
    person_ali1_.gender_source_value as gender_14_20_0_, 
    person_ali1_.day_of_birth as day_of_15_20_0_ 
from procedure_occurrence this_ 
inner join person person_ali1_ on this_.person_id=person_ali1_.person_id 
where (person_ali1_.person_id=?) 
and this_.provenance_id=? 
and this_.procedure_concept_id in (?, ?, ?, ?) 
order by this_.procedure_date desc

page 2:

WITH query AS (
    SELECT inner_query.*, ROW_NUMBER() 
    OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__ 
    FROM ( 
        select TOP(?) this_.procedure_occurrence_id as procedur1_22_1_, 
            this_.provenance_id as provenan2_22_1_, 
            this_.procedure_date as procedur3_22_1_, 
            this_.procedure_type_concept_id as procedur4_22_1_, 
            this_.procedure_source_value as procedur5_22_1_, 
            this_.associated_provider_id as associat6_22_1_, 
            this_.relevant_condition_concept_id as relevant7_22_1_, 
            this_.visit_occurrence_id as visit_oc8_22_1_, 
            this_.person_id as person_i9_22_1_, 
            this_.procedure_concept_id as procedu10_22_1_, 
            person_ali1_.person_id as person_i1_20_0_, 
            person_ali1_.person_source_value as person_s2_20_0_, 
            person_ali1_.provenance_id as provenan3_20_0_, 
            person_ali1_.location_id as location4_20_0_, 
            person_ali1_.gender_concept_id as gender_c5_20_0_, 
            person_ali1_.care_site_id as care_sit6_20_0_, 
            person_ali1_.year_of_birth as year_of_7_20_0_, 
            person_ali1_.provider_id as provider8_20_0_, 
            person_ali1_.race_concept_id as race_con9_20_0_, 
            person_ali1_.month_of_birth as month_o10_20_0_, 
            person_ali1_.ethnicity_concept_id as ethnici11_20_0_, 
            person_ali1_.ethnicity_source_value as ethnici12_20_0_, 
            person_ali1_.race_source_value as race_so13_20_0_, 
            person_ali1_.gender_source_value as gender_14_20_0_, 
            person_ali1_.day_of_birth as day_of_15_20_0_ 
        from procedure_occurrence this_ 
        inner join person person_ali1_ on this_.person_id=person_ali1_.person_id 
        where (person_ali1_.person_id=?) 
        and this_.provenance_id=? 
        and this_.procedure_concept_id in (?, ?, ?, ?) 
        order by this_.procedure_date desc ) inner_query ) 
    SELECT 
        procedur1_22_1_, 
        provenan2_22_1_, 
        procedur3_22_1_, 
        procedur4_22_1_, 
        procedur5_22_1_, 
        associat6_22_1_, 
        relevant7_22_1_, 
        visit_oc8_22_1_, 
        person_i9_22_1_, 
        procedu10_22_1_, 
        person_i1_20_0_, 
        person_s2_20_0_, 
        provenan3_20_0_, 
        location4_20_0_, 
        gender_c5_20_0_, 
        care_sit6_20_0_, 
        year_of_7_20_0_, 
        provider8_20_0_, 
        race_con9_20_0_, 
        month_o10_20_0_, 
        ethnici11_20_0_, 
        ethnici12_20_0_, 
        race_so13_20_0_, 
        gender_14_20_0_, 
        day_of_15_20_0_ 
    FROM query 
    WHERE __hibernate_row_nr__ >= ? 
    AND __hibernate_row_nr__ < ?

Solution

I think it's possible to define more than one sort cloumn in domain class:

    static mapping = { 
          sort(["property1" : "asc",
                "property2" : "desc"] ) 
    }