HI Stefan, a virtual table can be built around any resultset returning statement (e.g., a stored procedure), and the JDBCDataStore machinery makes no attempt to understand the SQL being provided, it just uses it as a subquery in the from clause.
Query hints, at least in the databases that I'm familiar with, must be provided in very specific positions, which requires an understanding of the SQL involved. If you check the SQLServerDialect handleSelectHints, it makes assumptions on how the query is built to find the exact point where a hint can be added (it's pretty much added mid-query). The assumption is fair because we control how a select against a table is built, the same cannot be said for virtual tables, where anything goes in the user provided query (a table might not be involved at all). Is SAP HANA so freeform that one can put query hints pretty much anywhere, regardless of what is found in the user provided query? Even if it was, you'd have to modify the API to allow other database (SQLServer) to tell apart actual table vs virtual table. Cheers Andrea On Tue, Dec 13, 2022 at 9:36 PM Uhrig, Stefan via GeoTools-Devel < geotools-devel@lists.sourceforge.net> wrote: > Hi all, > > > > Recently, a group of GeoServer users on HANA requested the possibility to > append hints to SQL queries. Luckily, there was already > SQLDialect#handleSelectHints(). So, I could just override the method in > HanaDialect and append the hints to the query, which I did in [GEOT-7230] ( > https://github.com/geotools/geotools/pull/4064). > > > > Now the group of users who requested the feature noticed that it works for > common GeoServer layers, but not for GeoServer views, which are based on > GeoTools’ VirtualTable. I investigated and found that JDBCDataStore > deliberately blocks the addition of search hints ( > https://github.com/geotools/geotools/blob/29394a11ce3128a625f5251abeb9b0b09cc0105b/modules/library/jdbc/src/main/java/org/geotools/jdbc/JDBCDataStore.java#L3505 > ). > > > > I found that the SQLDialect#handleSelectHints() has been introduced with > “[GEOT-4707] Add a flag to force spatial index usage in sql server” ( > https://github.com/geotools/geotools/pull/371/files). Adding search hints > to VirtualTable queries was blocked from the start. > > > > Does someone know why search hints are not added to VirtualTable queries? > > > > In the HANA use case, I could not identify any reason yet why the addition > of search hints should be blocked. I might oversee something though. If > there is no urgent reason to block search hints in VirtualTable queries, > what would be a good way to support search hints on VirtualTable queries in > a SQLDialect? Would the introduction of an additional function like > appendSearchHintsToVirtualTableQueries() be acceptable? > > > > Thank you and best regards, > > Stefan > > > > > _______________________________________________ > GeoTools-Devel mailing list > GeoTools-Devel@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/geotools-devel > -- Regards, Andrea Aime == GeoServer Professional Services from the experts! Visit http://bit.ly/gs-services-us for more information. == Ing. Andrea Aime @geowolf Technical Lead GeoSolutions Group phone: +39 0584 962313 fax: +39 0584 1660272 mob: +39 339 8844549 https://www.geosolutionsgroup.com/ http://twitter.com/geosolutions_it ------------------------------------------------------- Con riferimento alla normativa sul trattamento dei dati personali (Reg. UE 2016/679 - Regolamento generale sulla protezione dei dati “GDPR”), si precisa che ogni circostanza inerente alla presente email (il suo contenuto, gli eventuali allegati, etc.) è un dato la cui conoscenza è riservata al/i solo/i destinatario/i indicati dallo scrivente. Se il messaggio Le è giunto per errore, è tenuta/o a cancellarlo, ogni altra operazione è illecita. Le sarei comunque grato se potesse darmene notizia. This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. We remind that - as provided by European Regulation 2016/679 “GDPR” - copying, dissemination or use of this e-mail or the information herein by anyone other than the intended recipient is prohibited. If you have received this email by mistake, please notify us immediately by telephone or e-mail
_______________________________________________ GeoTools-Devel mailing list GeoTools-Devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geotools-devel
