Qgis should cope with table aliases in view definitions

[pcreso]

A view with a unique primary key can't be opened by QGIS as it says the key is not unique. It is. This is on Qgis v0.11, but also I believe in v1.0.

I believe QGIS uses the following SQL to test for uniqeness in a column, which returns true in this case: select count(distinct obs_id) = count(obs_id) from v_obs_pos ; ?column?

---

t (1 row)

My version of the same test gives 0 duplicates in the underlying table & the view:

select count(*) from (select obs_id from v_obs_pos group by obs_id having count(*) > 1 limit 1) as foo; count

---

0

(1 row)

ofop=# select count(*) from (select id from obs group by id having count(*) > 1 limit 1) as foo; count

---

0

(1 row)

But QGIS gives an error msg: "Note: 'id' initially appeared suitable but does not contain unique data, so is not suitable."

This error does not appear to be genuine.

[gjm]

After some debugging with Brent (aka pcreso), we found the problem was that qgis is unable to deal with columns in a view where the view definition contained table aliases and the view column was renamed using AS. The symptoms weren't a good indication of the underlying problem.

For example, in this view:

create view view1 as select o.id AS id_1 from table1 o;

qgis won't see the id column. If the view was:

create view view1 as select table1.id AS id_1 from table1;

qgis would work fine.

This is a deficiency with how qgis parses the view definitions.

So, to turn this ticket around, I've changed it to an enhancement that qgis parse table aliases. Adding in the patch in #1535 would also provide for a way around qgis not correctly parsing other details in view definitions.

[pcav]

Apparently a duplicate of #1417

[pcav]

Dupicate of #1417