Bug report #12892

Can't load table from SQL editor in DBManager with 'WHERE' argument

Added by Nicolas Rochard almost 9 years ago. Updated about 8 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:DB Manager
Affected QGIS version:2.8.2 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:not reproducable
Crashes QGIS or corrupts data:No Copied to github as #:20982

Description

When I want to load a table from SQL editor from DB Manager nothing happened.
In case where my primary key won't be unique, I make a row number but same problem.
When I execute, everything is well display, everything is fine.
Despite indicate primary key and geometric field, it doesn't work when I press load. Nothing, no error indicated, no loading time.

Tested on QGIS 2.8.2 under Windows 7 64 bits (on 2 computer)
And my openSUSE 13.2 64bits with same version.

SQL preview with 2 tables join

04.png - SQL preview with 2 tables join (179 KB) Nicolas Rochard, 2015-06-05 08:05 AM

table_btx_2011.png - attribute layer (278 KB) Nicolas Rochard, 2015-06-08 01:56 AM

tu_iris_commune.png - geometric layer (267 KB) Nicolas Rochard, 2015-06-08 01:56 AM

dbManager.PNG (40.7 KB) Saber Razmjooei, 2015-06-08 03:29 AM

39.png (65.3 KB) Nicolas Rochard, 2015-06-08 05:34 AM

11.png (196 KB) Nicolas Rochard, 2015-06-08 05:34 AM

53.png (203 KB) Nicolas Rochard, 2015-06-08 05:34 AM

History

#1 Updated by Saber Razmjooei almost 9 years ago

  • Operating System deleted (Linux openSUSE)
  • Category set to DB Manager
  • Status changed from Open to Feedback
  • OS version deleted (13.2 64 bits)

is idu your primary key? You can check under table properties > constraints?

#2 Updated by Nicolas Rochard almost 9 years ago

Saber Razmjooei wrote:

is idu your primary key? You can check under table properties > constraints?

I see that plateform and version was delete, what should I indicate in plateforme and plateform version ?
About your question, as it didn't work with primary key "oid" define as constraints, I try to generate a serial field rowid ... but it didn't work.
I've done that many times with 2.4 and it was working without any problem.

#3 Updated by Giovanni Manghi almost 9 years ago

I see that plateform and version was delete, what should I indicate in platform and platform version ?

we usually use this fields to define if an issue is specific of a certain platform/version.

#4 Updated by Saber Razmjooei almost 9 years ago

If it doesn't work in Windows and Linux it is likely to be:
- A problem independant of your platform
- (very likely) to be a problem with your database

I can't reproduce your problem on either platforms with my pg database. Could you provide a screenshot of info tab from your database.

#5 Updated by Giovanni Manghi almost 9 years ago

Does this happen only if the query contains a WHEN conditional?

#6 Updated by Nicolas Rochard almost 9 years ago

Giovanni Manghi wrote:

Does this happen only if the query contains a WHEN conditional?

Yes only when conditional.

@Saber
I provide requested element as soon as I go back to work on monday morning.

#7 Updated by Nicolas Rochard almost 9 years ago

Here are screenshot from my table on pgadmin.
Primary key are define, see nothing strange.
Reinstall 2.4 version, it works :S

Saber Razmjooei wrote:

If it doesn't work in Windows and Linux it is likely to be:
- A problem independant of your platform
- (very likely) to be a problem with your database

I can't reproduce your problem on either platforms with my pg database. Could you provide a screenshot of info tab from your database.

attribute layer
geometric layer

#8 Updated by Saber Razmjooei almost 9 years ago

Could you have the screenshot from DB Manager in QGIS like the ones I have attached.

#9 Updated by Giovanni Manghi almost 9 years ago

Reinstall 2.4 version, it works :S

does this means that the same query with the same tables works on qgis 2.4?

Could you please post the simplest version of the query that does not work for you?

#10 Updated by Nicolas Rochard almost 9 years ago

Yes it able to display in QGIS
In attachment, query as simple as possible and both table display in dbmanager.
Result is clearly displayed as working but no way to load it in QGIS (that detect correctly geometry column)

Giovanni Manghi wrote:

does this means that the same query with the same tables works on qgis 2.4?

Could you please post the simplest version of the query that does not work for you?



#11 Updated by Jürgen Fischer almost 9 years ago

Did anyone spot a WHEN yet? BTW I find the screenshot not very helpful.

Why not the @CREATE TABLE@s for all tables involved, the query that used to work in 2.4 and the message log output of 2.8 (if any) in plain text (quoted with <pre>...</pre>).

There should be a unique column in the query - like the in the first query (ie. row_number() OVER ()). Not sure why it doesn't work. Is there any message log output after trying to load that as a layer?

#12 Updated by Nicolas Rochard almost 9 years ago

Sorry, my fault, I was talking about a WHERE clause and not WHEN.
I send most simply query for example. But if my unique column won't be unique, QGIS display me an error. By the way, I've tested with a row number and same problem.
I'me gonna to reinstall QGIS 2.4 and done what you've asked.

Jürgen Fischer wrote:

Did anyone spot a WHEN yet? BTW I find the screenshot not very helpful.

Why not the @CREATE TABLE@s for all tables involved, the query that used to work in 2.4 and the message log output of 2.8 (if any) in plain text (quoted with <pre>...</pre>).

There should be a unique column in the query - like the in the first query (ie. row_number() OVER ()). Not sure why it doesn't work. Is there any message log output after trying to load that as a layer?

#13 Updated by Giovanni Manghi almost 9 years ago

  • Subject changed from Can't load table from SQL editor in DBManager with 'WHEN' argument to Can't load table from SQL editor in DBManager with 'WHERE' argument

I'me gonna to reinstall QGIS 2.4 and done what you've asked.

just post here the query (not as screenshot) and the sql to create the necessary involved tables.

#14 Updated by Nicolas Rochard almost 9 years ago

Create Geometry Table : http://paste.opensuse.org/93583864
Create Attribute Table : http://paste.opensuse.org/69714441
SQL Query : http://paste.opensuse.org/58723192
or with row number : http://paste.opensuse.org/82958548

Do you want a dump from my data ?

Giovanni Manghi wrote:

I'me gonna to reinstall QGIS 2.4 and done what you've asked.

just post here the query (not as screenshot) and the sql to create the necessary involved tables.

#15 Updated by Giovanni Manghi over 8 years ago

Nicolas Rochard wrote:

Create Geometry Table : http://paste.opensuse.org/93583864
Create Attribute Table : http://paste.opensuse.org/69714441
SQL Query : http://paste.opensuse.org/58723192
or with row number : http://paste.opensuse.org/82958548

Do you want a dump from my data ?

the above code is not available anymore.

Please attach a dump and paste here the query.

#16 Updated by Giovanni Manghi about 8 years ago

  • Status changed from Feedback to Closed
  • Resolution set to not reproducable

closing for lack of feedback, please reopen of necessary.

Also available in: Atom PDF