Bug report #10694
Oracle Provider: Cannot open subqueries
|Affected QGIS version:||2.2.0||Regression?:||No|
|Operating System:||Easy fix?:||No|
|Pull Request or Patch supplied:||No||Resolution:|
|Crashes QGIS or corrupts data:||No||Copied to github as #:||19090|
with Database Manager plugin, QGis is able to open spatial queries between two or more tables on a PostGIS server. The table definition (in the project file) is then a query of the following form (instead of a table name):
(SELECT b.* FROM a INNER JOIN b ON ST_Intersects(a.geom, b.geom)). It is a way to handle dynamic queries in a project.
It seems that even if there is no Oracle part in Database Manager plugin, Oracle Provider is able to deal with such dynamic queries (cf: https://github.com/qgis/QGIS/blob/master/src/providers/oracle/qgsoracleprovider.cpp#L812). But even if there is something in the code, I am not able to open a table which is a valid SQL query.
Here is an example of table definition under my Qgis project:
<datasource>dbname='oracle' port=1521 user='user' password='password' key='GID' estimatedmetadata=true srid=27562 type=MULTIPOLYGON table="(SELECT b.* FROM a, b WHERE SDO_RELATE(a.geom, b.geom, 'mask=touch') = 'TRUE')" (GEOM) sql=</datasource>
When I try to open the project file, Oracle provider launch the following query:
SELECT * FROM (SELECT b.* FROM a, b WHERE SDO_RELATE(a.geom, b.geom, 'mask=touch') = 'TRUE') AS "subQuery_0" WHERE 1=0
This query seems to have a problem with the AS statement which should identifies an alternative name for a column, not a table or a subquery. Oracle server returns an ORA-00933 error code.
The following query works:
SELECT * FROM (SELECT b.* FROM a, b WHERE SDO_RELATE(a.geom, b.geom, 'mask=touch') = 'TRUE') WHERE 1=0
You just need to delete the AS statement.
Or perhaps the WITH clause should be used:
WITH "subQuery_0" AS (SELECT b.* FROM a, b WHERE SDO_RELATE(a.geom, b.geom, 'mask=touch') = 'TRUE')
SELECT * FROM "subQuery_0" WHERE 1=0
Even if there seems to be no other way to build those query from manually modifying QGis project file, I still believe this is a bug because Oracle provider seems to have the necessary code to handle thos subqueries.
Thanks to try to fix this...