[QGIS-trac] Re: [Quantum GIS] #1718: Incorrect unique key interpretation on PostGIS view - view doesn't load as expected

[Quantum GIS]

#1718: Incorrect unique key interpretation on PostGIS view - view doesn't load as
expected
-------------------------------------+--------------------------------------
        Reporter:  mwtoews           |         Owner:  nobody       
            Type:  bug               |        Status:  new          
        Priority:  minor: annoyance  |     Milestone:  Version 1.2.0
       Component:  Data Provider     |       Version:  HEAD         
      Resolution:                    |      Keywords:               
Platform_version:                    |      Platform:  All          
        Must_fix:  No                |   Status_info:  0            
-------------------------------------+--------------------------------------
Changes (by pcav):

  * priority:  major: does not work as expected => minor: annoyance
  * platform:  Windows => All
  * milestone:  Version 1.0.3 => Version 1.2.0

Comment:

 It seems that the problem lies in using both table aliases AND column
 aliases; if you use only one of the two, the problem disappears.
 {{{
 CREATE OR REPLACE VIEW spot_analysis AS
  SELECT ana.sid, spt.geometry, spt.id AS spot_id, smp.sid AS sample_sid,
 ana.parameter, ana.result
    FROM spot spt
    JOIN sample smp ON spt.id::text = smp.spot_id::text
    JOIN analysis ana ON smp.sid = ana.sample_sid;
 }}}

 How do other clients behave in such a case?

 Aha, more info: the problem seems to depend on aliases in primary keys.
 With OIDs as primary key the problem seems to disappear.
 Probably #1417 is a duplicate of this.

 Thanks Emilia Venturato for tracking it down.

-- 
Ticket URL: <https://trac.osgeo.org/qgis/ticket/1718#comment:5>
Quantum GIS <http://qgis.org>
Quantum GIS is an Open Source GIS viewer/editor supporting OGR, PostGIS, and GRASS formats