Quand EXISTS ne fonctionne pas : File Geodatabases et sous-requêtes corrélées

JoshuaBixby

<\/HEAD>

Dans \/blogs\/tilting\/2016\/09\/27\/performance-at-a-price-file-geodatabases-and-sql-support?sr=search&searchId=c3a45d0d-24f6-481b-bcf7-cfc7493b25fc&searchIndex=0<\/A>, je souligne le compromis du support SQL qu'Esri a fait lors du développement de la file geodatabase (FGDB) comme remplacement de la personal geodatabase (PGDB).  Dans ce billet, plusieurs liens sont fournis pour ceux qui souhaitent en savoir plus sur le support SQL dans les file geodatabases.  Bien qu'il y ait beaucoup de chevauchement dans le contenu entre les différentes sources\/liens, il y a aussi des déclarations importantes qui n'existent que dans un endroit ou un autre, et le savoir peut être important lors du dépannage d'erreurs ou pour comprendre des résultats erronés provenant de données stockées dans des file geodatabases.<\/P>

<\/P>

Un domaine où les utilisateurs peuvent se mettre en difficulté avec les file geodatabases et SQL est la condition ou opérateur EXISTS.  En regardant la référence SQL (FileGDB_SQL.htm) dans l'API File Geodatabase (

@Esri Downloads <\/A>) ou la référence SQL pour les expressions de requête utilisées dans ArcGIS<\/A>:<\/P>

[NOT] EXISTS<\/P><\/TD>

Renvoie TRUE si la sous-requête renvoie au moins un enregistrement ; sinon, elle renvoie FALSE. Par exemple, cette expression renvoie TRUE si le champ OBJECTID contient une valeur de 50 :<\/P> EXISTS (SELECT * FROM parcels WHERE "OBJECTID" = 50)<\/PRE>EXISTS est pris en charge uniquement dans les file, personal et ArcSDE geodatabases.<\/TD><\/TR><\/TBODY><\/TABLE><\/BLOCKQUOTE><\/P>Voyant que SQL EXISTS est pris en charge dans la file geodatabase, comment quelqu'un peut-il se mettre en difficulté en l'utilisant ?  Malheureusement, c'est plus facile que vous ne le pensez, et la réponse réside dans les sous-requêtes corrélées.<\/P><\/P>Les sous-requêtes corrélées sont assez courantes lorsqu'on travaille avec EXISTS, tellement courantes en fait que la documentation Microsoft

EXISTS (Transact-SQL)<\/A> et la documentation Oracle Condition EXISTS <\/A> utilisent toutes deux une sous-requête corrélée dans amp au moins amp un exemple de code.  Au plus simple, une sous-requête corrélée est une sous-requête qui se rapporte à une ou plusieurs tables dans la requête externe.  La théorie et l'empirisme des sous-requêtes corrélées vont bien au-delà de ce billet de blog, mais je mentionnerai que les sous-requêtes corrélées ont certaines propriétés similaires à JOIN, ou du moins en apparence.<\/P>

<\/P>

L'exemple suivant est tiré de 

Select Max value arcpy<\/A>, la même question GeoNet qui m'a amené à rechercher ce problème il y a plusieurs mois.  Commençons par deux tables basiques (TableA et TableB), toutes deux ayant un champ texte et un champ entier, et l'une contenant un sous-ensemble d'enregistrements de l'autre table.<\/P>

<\/P>

<\/P>

>><\/SPAN>><\/SPAN> fgdb =<\/SPAN> # chemin vers file geodatabase\/<\/<\/<\/<\/<\/<\/<\/<\/<\/<\/<\/<\/<\/<\/<\/<\/<\/<\/<\/<\/<\/<\/<\/<\/<\/<\/<\/<\/<\/<\/>  class="punctuation token">)<\/SPAN>:<\/SPAN>
.<\/SPAN>.<\/SPAN>.<\/SPAN>         table =<\/SPAN> arcpy.<\/SPAN>CreateTable_management(<\/SPAN>gdb, <\/SPAN>table_names[<\/SPAN>i-1<\/SPAN>]<\/SPAN>)<\/SPAN>
.<\/SPAN>.<\/SPAN>.<\/SPAN>         arcpy.<\/SPAN>AddField_management(<\/SPAN>table, <\/SPAN>"id"<\/SPAN>, <\/SPAN>"TEXT"<\/SPAN>)<\/SPAN>
.<\/SPAN>.<\/SPAN>.<\/SPAN>         arcpy.<\/SPAN>AddField_management(<\/Span>table, <\/Span>"version"<\/Span>, <\/Span>"LONG"<\/Span>)<\/Span>   
.<\/Span>.<\/Span>.<\/Span>         avec arcpy.<\/Span>da.<\/Span>InsertCursor(<\/Span>table, <\/Span>("id") , "version") ) comme cur:

...            pour value dans values[: :i]: 

...& nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; cur.insertRow(value)

...& nbsp; & nbsp; & nbsp; & nbsp;

...& nbsp; & nbsp; & nbsp; & nbsp; qry = (

...& nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp;"EXISTS (SELECT 1"

...& nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp;< spanclass= " stringtoken ">"&amp ; FROM TableB"</ span >
.</ span >< spanclass= " punctuationtoken ">.</ span >< spanclass= " punctuationtoken ">.</ span >& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& nb sp ;& n b s p;< spanclass= " stringtoken ">" WHERE TableB.id = TableA.id"</ span >
.</ span >< spanclass= " punctuationtoken ">.</ span >< spanclass= " punctuationtoken ">.</ span >& n b s p ;& n b s p ;& n b s p ;& n b s p ;& n b s p ;& n b s p ;& n b s p ;& n b s p ;& n b s p;< spanclass= " stringtoken ">" AND TableB.version = TableA.version)"</ span > )
.</ span >< spanclass= " punctuationtoken ">.</ span >< spanclass= " punctuationtoken ">.</ span >& n b s p ;& n b s p ;
.</ span >< spanclass= " punctuationtoken ">.</ span >< spanclass= " punctuationtoken ">.</ span >& n b s p ;& n b s p ; table_view = arcpy.MakeTableView_management(table, < spanclass =" stringtoken ">"table_view"</ span >, qry)
.... print arcpy.GetCount_management(table_view)
... arcpy.Delete_management(table_view)
0
3
3
3
>>> ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍>>>>>>>>>>>>>>>>>>>></ SP > < SP an > < EM OJ I _ 21 > > < / SP > < SP a n > < EM OJ I _ 22 > > < / SP > < SP a n > < EM OJ I _ 23 > > < / SP > < SP a n > < EM OJ I _ 24 > > < / SP > < SP a n > < EM OJ I _ 25 > > < / SP > < SP a n > < EM OJ I _ 26 > > < / SP > < SP a n > < EM OJ I _ 27 > > < / SP > < SP a n > < EM OJ I _ 28 > > < / SP > < SP a n > < EM OJ I _ 29 > > < / SP > < SP a n > < EM OJ I _ 30 > > < / SP > < SP a n > < EM OJ I _ 31 > > < / SP > < SP a n > < EM OJ I _ 32 > > < / SP > < SP a n > < EM OJ I _ 33 >> </ Span ></ CODE ></ PRE ></ P ></ P ></ P >
Le code d'exemple ci-dessus crée les deux tables de base dans une file geodatabase, personal geodatabase, enterprise geodatabase (j'ai utilisé SQL Server) et un

GeoPackage. Le code crée ensuite une vue de table pour chacune des geodatabases en utilisant EXISTS avec une simple sous-requête corrélée pour sélectionner les enregistrements de TableA qui ont un id et une version correspondants dans TableB. La vue de table correcte est montrée dans la capture d'écran ci-dessus. On peut voir d'après les résultats du code qu'aucun enregistrement ne provient de la file geodatabase.

Alors pourquoi la file geodatabase ne renvoie-t-elle aucun enregistrement ? Les sous-requêtes ne sont-elles pas prises en charge ? Étant donné que la documentation pour EXISTS mentionne les sous-requêtes, il semble qu'elles doivent être prises en charge, au moins dans une certaine mesure. Examinons de plus près la sectionSous-requêtes de laRéférence SQL pour les expressions de requête utilisées dans ArcGIS:

Sous-requêtes

Note : <\/H5>Les couvertures, shapefiles et autres sources de données basées sur des fichiers non géodatabase ne prennent pas en charge les sous-requêtes. Les sous-requêtes effectuées sur des classes d'entités et tables ArcSDE versionnées ne retourneront pas les entités stockées dans les tables delta. Les géodatabases fichier offrent un support limité pour les sous-requêtes expliqué dans cette section, tandis que les géodatabases personnelles et ArcSDE offrent un support complet. Pour des informations sur l'ensemble complet des capacités de sous-requête des géodatabases personnelles et ArcSDE, référez-vous à la documentation de votre DBMS.<\/P><\/DIV><\/P><\/P><\/P><\/P>Une sous-requête est une requête imbriquée dans une autre requête. Elle peut être utilisée pour appliquer des fonctions prédicatives ou agrégées ou pour comparer des données avec des valeurs stockées dans une autre table....<\/P><\/BLOCKQUOTE><\/P>Le support des sous-requêtes dans les géodatabases fichier est limité aux éléments suivants :<\/P>Prédicat IN. Par exemple :<\/LI><\/UL>"COUNTRY_NAME" NOT IN (SELECT "COUNTRY_NAME" FROM indep_countries)<\/PRE>Sous-requêtes scalaires avec opérateurs de comparaison. Une sous-requête scalaire retourne une seule valeur. Par exemple :<\/LI><\/UL>"GDP2006" > (SELECT MAX("GDP2005") FROM countries)<\/PRE>Pour les géodatabases fichier, les fonctions d'ensemble AVG, COUNT, MIN, MAX et SUM ne peuvent être utilisées que dans des sous-requêtes scalaires.<\/SPAN><\/P>Prédicat EXISTS. Par exemple :<\/LI><\/UL>EXISTS (SELECT * FROM indep_countries WHERE "COUNTRY_NAME" = 'Mexico')<\/PRE><\/BLOCKQUOTE><\/P>La documentation indique un « support limité » pour les sous-requêtes, mais elle indique également que le prédicat EXISTS est pris en charge.  Si les sous-requêtes ne posent pas problème, peut-être que ce sont les sous-requêtes corrélées qui posent problème.  Après tout, aucun des exemples EXISTS dans toute la documentation n'utilise une sous-requête corrélée.  Malheureusement, il ne reste que des suppositions car les sous-requêtes corrélées ne sont mentionnées explicitement dans aucune documentation de géodatabase fichier que je puisse trouver.<\/P><\/P>À partir de cet exemple simple, il est clair que la géodatabase fichier donne des résultats incorrects lorsqu'on utilise EXISTS avec des sous-requêtes corrélées.  Ces résultats incorrects sont-ils un bug ou une limitation du support SQL dans la géodatabase fichier ?  Même si l'on essaie d'argumenter pour cette dernière hypothèse, il y a toujours le problème que l'utilisateur obtienne des résultats incorrects au lieu d'un message d'erreur, ce qui devrait être le cas si les sous-requêtes corrélées ne sont pas prises en charge.<\/P><\/BODY><\/HTML>

LanceShipman

File Geodatabase does not support correlated subqueries,