Bug report #13731

DBManager's Oracle SQL Query "load result as new layer" gives and error

Added by Mehmet Selim BILGIN almost 5 years ago. Updated over 4 years ago.

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

Revision e04d5e4d
Added by Sebastian Dietrich over 4 years ago

replaced 'LIMIT 0' by 'WHERE 0=1' (fixes #13731)

Revision 57f5fba5
Added by Jürgen Fischer over 4 years ago

Merge pull request #2478 from SebDieBln/DbManager_Fix13731

[DbManager] replaced 'LIMIT 0' by 'WHERE 0=1' (fixes #13731)

Revision 5d359a63
Added by Sebastian Dietrich over 4 years ago

replaced 'LIMIT 0' by 'WHERE 0=1' (fixes #13731)

Revision c2fb11c8
Added by Jürgen Fischer over 4 years ago

db manager: only alias subqueries on postgres (fixes #13731)

Revision 80c5cb68
Added by Jürgen Fischer over 4 years ago

db manager: only alias subqueries on postgres (fixes #13731)

Conflicts:
python/plugins/db_manager/dlg_sql_window.py

History

#1 Updated by Giovanni Manghi almost 5 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 4 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 4 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 4 years ago

  • Status changed from Open to Closed

#5 Updated by René-Luc ReLuc over 4 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 4 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 4 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 4 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 4 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 4 years ago

  • Status changed from Reopened to Closed

Also available in: Atom PDF