Join to table with definition query — removing join breaks the layer

[Bud]

ArcGIS Pro 3.0.3; mobile geodatabase:

I have a FC and a standalone table:

I apply a definition query to the table -- to pare-down the rows so that only the latest inspection per asset is shown:

date_ = (select max(subq.date_) from roadinsptable subq where roadinsptable.asset_id = subq.asset_id)

I create a join from the FC to the table:

The join works as expected. And the definition query from the table is honoured.

Later, I change my mind: I've determined I don't need the join, so I remove it:

Problem:

Removing the join causes an error:

Failed to open table. 
Error:
Attribute column not found [no such column: date_]
[Roads][STATE_ID = 0]

I hit OK. The attribute table of the FC automatically closes.

I try to re-open the attribute table of the FC:

But I get the same error I saw previously. And the attribute table doesn't open.

Trying again produces the same error.

The only way to successfully open the attribute table again is to remove the FC from the map and re-add it, which isn’t ideal.

Is that a known issue?

I only have the problem when the join table has a definition query. If there's no definition query, then I can remove the join without getting the error.

[AlfredBaldenweck]

I have also run into this

[eclecticlearner]

ArcGIS Pro 3.1
I've run into a VERY similar issue (the same error message). My scenario is this:
Add a sqlite table to a map
Add a featureclass to the same map
Add a definition query on the sqlite table to limit the number of records
Join the featureclass (id field integer) to the sqlite table (id field integer)

The join works just fine, but when opening the attribute table of the featureclass (which is now joined to the sqlite table) Same error

"failed to open table.
Error:
Attribute column not found [no such column: 'name-of-the-first-column-used-in-the-sqlite-table-definition-query']

[Bud]

@JonathanNeal might be interested in this.

Edit:

I tested in 3.1.2 just now and am still seeing the issue in that version.

I submitted an Esri Canada case this afternoon: 
03410020 - ArcGIS Pro 3.1.2: Join to table with definition query — removing join breaks the layer

[eclecticlearner]

Thanks for replicating this issue @Bud .  I am also using ArcGIS Pro 3.1.2.

I believe you mentioned @JonathanNeal because of this  post.

"Two likely scenarios come to mind in 3.1- and in ArcMap that you can mark as a YES:
1. Subqueries do not work if done before the join on datasources with "."   "

The response by him strikes me as what could be the issue.  If ArcGIS Pro adds (as default behavior) sqlite/gpkg tables as a "query layer" then would a definition query on that query layer be treated as a subquery?  For reference I just drag and drop the tables into the map, sometimes I it asks for me to identify the uniqueID column but otherwise I've treated them no different than any other table when adding/removing etc.

All of my sqlite tables show up in catalog pane as "main.mytablename".  For the life of me I have not figured out how to implement this in such a way that ArcPro drops the "main." It would be great if I could solve that part of the equation and see if it solves the definition query part of the equation.

Anyhow, thanks for the reply and hopefully this post gets some traction.  I have a tool I'm ready to implement but an error like this has me debating on rewriting the entire tool to output in some other way (I need outputs to be able to be read by more than just ESRI applications).

[Bud]

Interesting.

Even if Esri doesn't plan to fix this issue: Subqueries do not work if done before the join on datasources with "." , at the very least, it shouldn't permanently break the layer (the join can't be removed). That should be fixed. And there should be helpful error messages to tell us what the problem is.

As I mentioned in a different post, the measure of software quality is how good the error messages are.

[Bud]

From Esri Support in case #03410020 (open):

I have followed the workflow you provided and was able to reproduce the issue you encountered. I'd like to share a few observations with you:

• Apart from removing the FC from the map and then re-adding it, I found that I could open the attribute table again by removing the definition query in the FC that inherited from the definition query in the table
  • This is an alternative approach to successfully opening the attribute table again
• I attempted to apply a definition query in the default mode instead of the SQL mode and encountered no errors
  • Upon further research on the "Attribute column not found" error, it seems that a possible cause is the QueryDef object is not handling wild cards (*) for its SubFields property if there are fully qualified fields in any of the tables (for example AREA).
  • You can explore potential workarounds for this issue at https://support.esri.com/en-us/knowledge-base/error-attribute-column-not-found-000003383 

[Bud]

Esri Support reported the following bug for me:

BUG-000160924 : SQL query becomes case sensitive when a Feature Layer is joined to a table stored in a Mobile Geodatabase. After joining a feature layer and a table (both stored in a Mobile Geodatabase), SQL query becomes case sensitive and the previous SQL query expression fails to execute.

@JoshuaBixby might find this interesting.