Bug report #13731
DBManager's Oracle SQL Query "load result as new layer" gives and error
| Status: | Closed | ||
|---|---|---|---|
| Priority: | High | ||
| Assignee: | - | ||
| Category: | DB Manager | ||
| Affected QGIS version: | 2.12.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 #: | 21758 |
Description
In QGIS 2.12 when using DBManager plugin with Oracle tables this bug occurs. I ran a simple SQL query like SELECT * FROM ilceler it returned the results succesfully. And than i clicked Load as new layer button to see results in QGIS canvas it gave error. The error says the reason is: ORA-00933: SQL command not properly ended. Also it gives the SQL query: SELECT * FROM (select * from ilceler
) AS "_subQuery__0" LIMIT 0. I only entered SELECT * FROM ilceler. DBManager changed the original query so it throws error.
Associated revisions
replaced 'LIMIT 0' by 'WHERE 0=1' (fixes #13731)
replaced 'LIMIT 0' by 'WHERE 0=1' (fixes #13731)
db manager: only alias subqueries on postgres (fixes #13731)
db manager: only alias subqueries on postgres (fixes #13731)
Conflicts:
python/plugins/db_manager/dlg_sql_window.py
History
#1
Updated by Giovanni Manghi over 8 years ago
- Subject changed from 2.12 DBManager's Oracle SQL Query result as new layer error ! to DBManager's Oracle SQL Query "load result as new layer" gives and error
- Target version deleted (
Version 2.12) - OS version deleted (
2.12)
#2
Updated by René-Luc ReLuc over 8 years ago
- Priority changed from Normal to High
It's a known issue.
In the TODO list of the oracle's db_manager plugin, we can find :
- SQL Window - Can't retrieve column: dlg_sql_window.py launch an invalid request for Oracle (no LIMIT operator in Oracle). Should patch dlg_sql_window.py to use a connector method to grab columns.
https://github.com/qgis/QGIS/blob/master/python/plugins/db_manager/db_plugins/oracle/TODO.md
#3
Updated by Sebastian Dietrich over 8 years ago
Please see PR 2478 for a simple solution (replacing LIMIT 0 with WHERE 0=1) of this issue, that is actually in the won't fix section of the mentioned TODO.
#4
Updated by Jürgen Fischer over 8 years ago
- Status changed from Open to Closed
Fixed in changeset 57f5fba5823e318809aa409a77ae636ea12874f5.
#5
Updated by René-Luc ReLuc over 8 years ago
I'll backport 5d359a6 to release-2_12 if a 2.12.1 will be released.
#6
Updated by René-Luc ReLuc over 8 years ago
- Status changed from Closed to Reopened
57f5fba does not fixed it.
It seems that the 'AS' is not supported in Oracle. Some one can confirm it ?
#7
Updated by René-Luc ReLuc over 8 years ago
Oracle does not support AS for aliases table name. It seems that in PostgreSQL and SQLite, it's not mandatory.
I proposed to remove it. Some objection ?
#8
Updated by Jukka Rahkonen over 8 years ago
Works for me from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production:
SELECT attribute AS test FROM test_table;
This diagram shows also AS in quite many places http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm.
Could you show example SQL where AS is not supported?
#9
Updated by René-Luc ReLuc over 8 years ago
It seems to not worked for table name aliases.
This does not work:
SELECT * FROM (select * from ilceler) AS "_subQuery__0".
This works:
SELECT * FROM (select * from ilceler) "_subQuery__0".
In the documentation, the "AS" seems to be not used for table aliases (search for t_alias in http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm).
#10
Updated by Jürgen Fischer over 8 years ago
- Status changed from Reopened to Closed
Fixed in changeset c2fb11c8d5fba163068d7ea87aa11062035dd167.