Clarify the docs: Correlated subqueries not supported in FGDB SQL expressions

Bud — Tue, 16 Jan 2024 08:47:04 GMT

Add a line to the SQL Expression docs that clearly states:

Correlated subqueries are not supported in file geodatabase SQL expressions

Details:

A database performs a correlated subquery when a nested subquery references a column from a table in a parent statement one or more levels above the subquery.

Since correlated subqueries aren’t supported, SQL expressions on file geodatabases can’t do things like get the greatest 1 per group, and other similar requirements:

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 file geodatabase SQL expression 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 error. Therefore, correlated subqueries should be avoided in file geodatabase SQL expressions to avoid producing misleading results.

Re: Clarify the docs: Correlated subqueries not supported in FGDB SQL expressions

Bud — Mon, 27 Feb 2023 23:31:23 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)

idea Clarify the docs: Correlated subqueries not supported in FGDB SQL expressions in Data Management Ideas

Clarify the docs: Correlated subqueries not supported in FGDB SQL expressions

Re: Clarify the docs: Correlated subqueries not supported in FGDB SQL expressions