I have a premium support case open for this issue, but wanted to see if anyone else in the developer community has experienced duplicate features being returned when querying feature layers. I have only been able to replicate this issue with services published to ArcGIS Enterprise from an Oracle database, not able to reproduce with hosted feature layers or services published from file geodatabases or SQL Server. Also this issue is not reproducible on version prior to version 2.3.
This issue is when a layer this is coming from Oracle is queried using layer.query(), the result is returning duplicate features, with duplicate OBJECTIDs. It will return the correct number of records, but after dropping the duplicates, there ends up being less features than their should be, so it is also not returning every record. I have confirmed that the duplicates are there if you return the results as a feature set or as a dataframe.
Instead of returning all records in one query, I decided to try using the API with return_all_records set to False and page through the requests result_record_count and result_offset to get the data in batches of the max record count the layer. Doing so does not result in duplicate records and also there are no missing records. This indicated that there is not an issue with the dataset itself, but with the Python API.
A major issue with their being duplicate OBJECTIDs, is that when you convert a data frame to a feature set using df.spatial.to_featureset() and there are duplicate OBJECTIDs, the OBJECTIDs for the resulting feature set are reset to start with 1 (might be another bug). This has caused some records to be updated incorrectly, or causing the update to fail since an invalid OBJECTID is sent.
For now I have asked staff to page through the requests, rather than get them in a single query. However, this does require more lines of code. Also if they do just use a single query to drop duplicates before converting to a feature set, so the OBJECTID is maintained.
The below sample is querying for all records and just returning the OBJECTID with no geometry. The value_counts of the OBJECTID field of the dataframe shows that there are duplicate OBJECTIDs.
fs = layer.query(out_fields=['OBJECTID'], return_geometry=False)
fs.sdf.OBJECTID.value_counts()
#OBJECTID
#528193 2
#528306 2
#528313 2
#528312 2
#528311 2
# ..
#525523 1
#525522 1
#525521 1
#525520 1
#528097 1
#Name: count, Length: 2198, dtype: Int64
