Execution Results of the string_agg Function Are Inconsistent

1
2
3
4

select count(*) from
(select deptno, string_agg(ename, ',') from employee group by deptno) t1 ,
(select deptno, string_agg(ename, ',') from employee group by deptno) t2
where t1.string_agg = t2.string_agg;

When this statement is executed repeatedly, the result sets are inconsistent (t1 or t2).

The string_agg function is used to concatenate data in a group into one row. However, if you use string_agg(ename, ','), the order of concatenated results needs to be specified.

If the order is not specified, the output of the SQL statement above can be any one of the following:

30 | ALLEN,MARTIN

30 |MARTIN,ALLEN

Therefore, the result of subquery t1 may be different from that of subquery t2 when the value of deptno is 30.

1
2
3
4

select count(*) from
(select deptno, string_agg(ename, ',' order by ename desc) from employee group by deptno) t1 ,
(select deptno, string_agg(ename, ',' order by ename desc) from employee group by deptno) t2
where t1.string_agg = t2.string_agg;