Recommand · October 22, 2021 0

LIMIT changing sort order on query

Two requests with differing LIMIT number are yielding different sorting results.

SELECT "acgti"."id" AS "id", "acgti"."ac_node_id" AS "ac_node_id", "acgti"."ac_msn_number" AS "ac_msn_number", "acgti"."ac_production_site" AS "ac_production_site_short_name", "acgti"."ac_program_id" AS "ac_program_id", "acgti"."ac_fal_rank" AS "ac_fal_rank", "acgti"."ac_status" AS "ac_status", "acgti"."logical_station_production_site" AS "logical_station_production_site", "acgti"."logical_station_short_name" AS "logical_station_short_name", "acgti"."rgti_node_id" AS "rgti_node_id", "acgti"."rgti_release_number" AS "rgti_release", "acgti"."rgti_reference" AS "rgti_reference", "acgti"."rgti_title" AS "rgti_title", "acgti"."rgti_title_second_lang" AS "rgti_title_second_lang", "acgti"."status" AS "status", "acgti"."created_on" AS "created_on", "acgti"."updated_on" AS "updated_on", "acgti"."attestation_type" AS "attestation_type", "created_by"."fullname" AS "created_by", CONCAT("acgti"."ac_customer_version_name", '/', "acgti"."ac_customer_version_rank") AS "ac_version_and_rank" FROM "ac_gti" "acgti" LEFT JOIN "ext_usr_user" "created_by" ON "created_by"."username"="acgti"."created_by" LEFT JOIN "ext_usr_user" "updated_by" ON "updated_by"."username"="acgti"."updated_by" WHERE "acgti"."ac_production_site" IN ('A350 TJN', 'A350 TLS') ORDER BY "acgti"."rgti_reference" ASC NULLS FIRST LIMIT 10

Yields :

enter image description here

SELECT "acgti"."id" AS "id", "acgti"."ac_node_id" AS "ac_node_id", "acgti"."ac_msn_number" AS "ac_msn_number", "acgti"."ac_production_site" AS "ac_production_site_short_name", "acgti"."ac_program_id" AS "ac_program_id", "acgti"."ac_fal_rank" AS "ac_fal_rank", "acgti"."ac_status" AS "ac_status", "acgti"."logical_station_production_site" AS "logical_station_production_site", "acgti"."logical_station_short_name" AS "logical_station_short_name", "acgti"."rgti_node_id" AS "rgti_node_id", "acgti"."rgti_release_number" AS "rgti_release", "acgti"."rgti_reference" AS "rgti_reference", "acgti"."rgti_title" AS "rgti_title", "acgti"."rgti_title_second_lang" AS "rgti_title_second_lang", "acgti"."status" AS "status", "acgti"."created_on" AS "created_on", "acgti"."updated_on" AS "updated_on", "acgti"."attestation_type" AS "attestation_type", "created_by"."fullname" AS "created_by", CONCAT("acgti"."ac_customer_version_name", '/', "acgti"."ac_customer_version_rank") AS "ac_version_and_rank" FROM "ac_gti" "acgti" LEFT JOIN "ext_usr_user" "created_by" ON "created_by"."username"="acgti"."created_by" LEFT JOIN "ext_usr_user" "updated_by" ON "updated_by"."username"="acgti"."updated_by" WHERE "acgti"."ac_production_site" IN ('A350 TJN', 'A350 TLS') ORDER BY "acgti"."rgti_reference" ASC NULLS FIRST LIMIT 20

Yields different order :

enter image description here

We couldn’t figure why, what is going on here?