I encountered a very weird issue recently with PostgreSQL (in metabase environment, as I don't have access to database directly)
In table_A, I use string_agg() to combine a series of string into one string in certain order, e.g. "A > B > C" or "3.5 > 4 > 5.6", and the result is correct and consistent in table_A.
However, when I use left join table_A in table_B, the sequence in the string is altered, e.g. "A > C > B", and even worse, the change of sequence appears randomly, i.e. sometimes "A > C > B", sometimes "B > A > C", and never seem to be correct.
I am not sure if it is metabase issue or my script issue. Your knowledge will be much appreciated !
In order to make sure it is not complicated by script in Table_B and too many columns / values from Table_A, I simplified the script in Table_B, and only import/join one column and one record, the problem is still the same
e.g.
select B.id, A.string from table_B B left join table_A A on A.id = B.id where B.id = '123'
Result: 123 | 'B >C >A' // but expect: 123 | 'A >B >C'
The order of a result set depends on the query execution plan that the database chooses. If you use an aggregate function like string_agg(), rows are aggregated in the order they happen to be produced by the query plan. If you need a certain fixed order, you have to be explicit about it:
string_agg(col, ',' ORDER BY col)