the api was returning an array of 2 null inside it
"name": "Dep - ONG",
and trying to access the optionset via maintenance page was not working
looking into the db I noticed gap in the sort_order
select * from optionvalue where optionsetid = 121
I removed the gap between the sort_order
update optionvalue set sort_order = 1 where optionvalueid = 117
update optionvalue set sort_order = 2 where optionvalueid = 118
restarted dhis2 and it solved the problem.
I fear this happen again, is this a known bug ?
This happened on a 184.108.40.206
Although it’s a different case, but it happened before here where null was being added and it got fixed. However, for that issue it was possible to replicate/reproduce the issue. Do you think there’s a way to reproduce this issue?
from what I see in your comment, it has not been retro-fixed in 2.39 ?
Thanks! Yes, you are right. It has been fixed for version 40.1 as for version 2.39 it will be available in version 2.39.3 once it is released.
We noticed this bug too, and we used this function to fix it, as having non-consecutive sort orders is generally something worth fixing.
Note that some tables have sort orders that begin with 0 and some have sort orders that begin with 1, hence the need for the third parameter in the SQL function.
Please use this with caution! (As you would any other modifications to the database).
create temporary table if not exists _fixsortorder (temp integer) on commit drop;
create or replace function fixSortOrder
(tbl text, key text, start integer) returns boolean as
execute format('insert into _fixsortorder (select distinct %s from %s)', key, tbl);
for resortObject in (select temp from _fixsortorder) loop
execute format('update %s
set sort_order = -t.i
from (select row_number() over (order by sort_order) as i, %s, sort_order
from %s where %s = %s order by sort_order) t
where %s.%s = %s and t.sort_order = %s.sort_order',
tbl, key, tbl, key, resortObject.temp, tbl, key, resortObject.temp, tbl);
execute format('update %s set sort_order = -(sort_order + 1) + %s', tbl, start);
execute 'truncate _fixsortorder';
language plpgsql volatile;
select fixsortorder('categoryoptiongroupsetmembers', 'categoryoptiongroupsetid', 1);
drop function if exists fix_sort_order(tbl text, key text, start integer);