idea Support correlated subqueries in file geodatabase SQL expressions in ArcGIS Pro Ideas

Support correlated subqueries in file geodatabase SQL expressions

Bud — Mon, 15 Jan 2024 07:38:28 GMT

Could FGDB SQL be enhanced to support correlated subqueries?

Example: Using an SQL expression in the Select by Attributes tool, I want to select the greatest 1 per group.

NOT EXISTS (
        SELECT *
        FROM road_insp r2
        WHERE r2.asset_id = road_insp.asset_id AND r2.date_ > road_insp.date_
       )
--Demo:  https://dbfiddle.uk/gSGjLTCW

The above SQL expression would work as expected in mobile GDBs and enterprise GDBs. But in file GDBs, it would select all rows in the table, which would be incorrect.
It’s important to note that the query fails silently, producing incorrect results, rather than throwing an useful error.

Could FGDB SQL be enhanced to support correlated subqueries?

Thanks.

Re: FGDB SQL: Support self-correlated subqueries (inner join on a virtual table)

JoshuaBixby — Wed, 07 Dec 2022 22:42:24 GMT

After years, more than a decade, of Esri not really responding to requests to improve SQL support and documentation of SQL support for file geodatabases, I would not be holding one's breath. Now that mobile geodatabases are getting more support across ArcGIS product lines, I am using them more and more.

Re: FGDB SQL: Support self-correlated subqueries (inner join on a virtual table)

Bud — Thu, 08 Dec 2022 03:10:49 GMT

@JoshuaBixby @Do you find there’s functionality that’s lacking in mobile geodatabases that fgdbs supported?

Re: FGDB SQL: Support self-correlated subqueries (inner join on a virtual table)

JoshuaBixby — Thu, 08 Dec 2022 15:31:20 GMT

Regarding your question, yes, but the answer is slowly changing to, no. Not all ArcGIS products support mobile geodatabases and some only support a limited subset of functionality with mobile geodatabases. ArcGIS Desktop/ArcMap is a good example with its very limited support for mobile geodatabases. Over time older products like ArcGIS Desktop/ArcMap will be retired and most new ArcGIS products or versions are supporting more functionality with mobile geodatabases. In the end, I suspect file geodatabases will always perform better than mobile geodatabases, but mobile geodatabases offer much better SQL support, so one will have to weigh the tradeoffs when deciding.

Re: Support correlated subqueries in file geodatabase SQL expressions

Bud — Mon, 27 Feb 2023 23:31:02 GMT

BUG-000156143 - An SQL query containing the EXISTS predicate validates successfully but returns incorrect results from a file geodatabase.

ENH-000156158 - Raise exception when using unsupported SQL expressions in file geodatabases.

Esri Canada Case 03264937 - Exception not raised when unsupported FGDB SQL used (correlated subquery)

Re: Support correlated subqueries in file geodatabase SQL expressions

Bud — Thu, 11 Jan 2024 17:37:59 GMT

Here’s an example use case where a FGDB correlated subquery might have saved the day:

Selecting the most recent records based on unique values in another field