Select By Attributes on joined data — For rows that are 1:M, all rows in the join table get selected, despite selection criteria

[Bud]

Title edited. Originally "1:M Join — Select By Attributes handling nulls incorrectly."

ArcGIS Pro 3.2.2; File Geodatabase:

I've joined TABLE_A to TABLE_B via common ASSET_ID text fields using Add Join. The join is one-to-many: 1 row in TABLE_A to 4 rows in TABLE_B.

I want to select from TABLE_B.ROAD_NAME (text) where ROAD_NAME is null. However, Select By Attributes doesn't seem to work correctly. It selects all four rows, regardless if ROAD_NAME is null or not.

Video:

Notes:

1. I have the same issue in ArcGIS Pro 2.6.8 in a file geodatabase and in an Oracle 18c 10.7.1 enterprise geodatabase.
2. I don't have this problem with definition queries in ArcGIS Pro 3.2.2, only with Select By Attributes. There is a separate issue in 2.6.8 with definition queries: Esri Case #03387420 - Definition query on join returns incorrect rows.

I don't understand how that issue has been around since ArcGIS Pro 2.6.8 (or earlier) and is also in 3.2.2. How could such a serious issue not have been fixed as of 3.2.2?

[JillScholz_esri]

Hi @Bud,

Unfortunately, this is a known limitation. 

As you suspected above, ObjectIDs are stored when we perform a selection. Situations where we end up with duplicate ObjectIDs can be a bit misleading.  We haven’t duplicated the number of features in a layer, only their records within the table view.  In this instance, both records from the right table are joined to the same feature (id=2) from the layer. Since there are multiple records joined to the same feature (with id=2), the selection of the one ObjectID results in all of those rows being highlighted in the attribute table window.

Thanks for taking the time to post this to our Community forum and also for providing several alternatives for others to potentially work around this issue.

[Bud]

For my notes:

The bug report was recently changed from “In Review” to “Known Limit”.

Known Limit: After review by the development team, it has been determined that this issue is related to a known limitation with the software that lies outside of Esri's control.

So, that means the issue cannot be fixed.