"unable to get a stable set of rows in the source table" Is Reported

Symptom

MERGE INTO is used to update a destination table, or to insert a row of the source table to the destination table based on certain matching conditions. If multiple rows meet the conditions, GaussDB(DWS) may perform either the following operations:

1. Report the error "unable to get a stable set of rows in the source table".
2. A random row is inserted, which may not be the row you want.

Possible Causes

This error is reported if the MERGE INTO operation is performed to update or insert data to the destination table, and multiple rows meet the matching conditions.

Solution

Check the value of behavior_compat_options. If behavior_compat_options is set to the default value, an error will be reported when multiple rows are matched. If behavior_compat_options is set to merge_update_multi, no error will be reported, and one of the matched rows will be randomly inserted.

If the result of the MERGE INTO operation is not as expected, modify this parameter as needed, check whether multiple rows are matched, and modify the service logic.