alias construct fails in SELECT

[darkblueb]

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

[mcayland]

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.