Help Center> Data Warehouse Service> Troubleshooting> Database Use> Execution Results of the string_agg Function Are Inconsistent
View PDF

Execution Results of the string_agg Function Are Inconsistent

Symptom

The execution results of an SQL statement are inconsistent.

Possible Causes

The string_agg function is used in the SQL statement. The statement logic is shown in the following figure.

Run the following SQL statement:

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 results 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.

Handling Procedure

Add order by to string_agg to ensure that the values in the ename column are concatenated in the specified order.

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;
Parent topic: Database Use