Opened 14 years ago

Closed 14 years ago

#544 closed defect (invalid)

alias construct fails in SELECT

Reported by: darkblueb Owned by: pramsey
Priority: medium Milestone: PostGIS 1.5.2
Component: postgis Version: 1.5.X
Keywords: Cc:

Description

using two queries, with identical constructs other than an alias in the SELECT list, the one with the alias gives inconsistent results.. maybe the identifier 'area' is being used for something else?

—- this one works without error dbb_grid_hack=# select b.ogc_fid, st_area( a.wkb_geometry) as area, st_area(a.wkb_geometry)/(150*150) as area_perc, st_area(b.wkb_geometry) as area_b from constrained_sum2 a, ct_sac_0 b where (a.id150=b.id150) order by area desc;


— this one gives wrong results sometimes dbb_grid_hack=# select b.ogc_fid, st_area( a.wkb_geometry) as area, area/(150*150) as area_perc, st_area(b.wkb_geometry) as area_b from constrained_sum2 a, ct_sac_0 b where (a.id150=b.id150) order by area desc;

==================

ogc_fid | area | area_perc | area_b


21061 | 22500 | 1.11713319443597 | 22500 36756 | 22500 | 1 | 22500 98425 | 22500 | 1 | 22500 83585 | 22500 | 1 | 22500 98683 | 22500 | 2.21060802547667 | 22500

101783 | 22500 | 1.21702020789252 | 22500

83590 | 22500 | 1 | 22500 92120 | 22500 | 1 | 22500

5870 | 22500 | 5 | 22500

110706 | 22500 | 2.88152405025164 | 22500

19157 | 22500 | 1.83199074812995 | 22500 17504 | 22500 | 1 | 22500

105455 | 22500 | 2.985409233284 | 22500

28576 | 22500 | 1.10312756123013 | 22500 41484 | 22500 | 1 | 22500 86113 | 22500 | 1 | 22500 80264 | 22500 | 1 | 22500 97363 | 22500 | 3.07222351900736 | 22500

112246 | 22500 | 3 | 22500 114085 | 22500 | 2.3938812451058 | 22500 113627 | 22500 | 3.97366612998115 | 22500

71307 | 22500 | 1.34081633254157 | 22500 98943 | 22500 | 2 | 22500

Change History (1)

comment:1 by mcayland, 14 years ago

Resolution: invalid
Status: newclosed

This suggests to me that "area" is already defined somewhere else in theq query - perhaps it is already a column within your constrained_sum2 or ct_sac_0 tables?

I know that per the SQL spec, you are not allowed to reference an existing column alias within a select, e.g.

SELECT st_area(col) AS foo, foo / 10 AS bar

is illegal. You need to repeat it again, e.g.

SELECT st_area(col) AS foo, st_area(col) / 10 AS bar

HTH,

Mark.

Note: See TracTickets for help on using tickets.