Re: ACS Web Server & PostgreSQL

Peter Eisentraut <peter_e@gmx.net> writes:
> leif@danmos.dk writes:
>> This statement must have worked fine in 7.0.2, but now a get an error:
>> Error: Ns_PgExec: result status: 7 message: ERROR:  Attribute 'group_name' not found
>> After several tries, I ended up just modifying the order by clause to:
>> order by upper

> This works because the expression 'upper(group_name)' happens to be
> labelled 'upper' due to the lack of an AS clause, but this assumption is
> not portable.  It would be better to either use an AS clause an order by
> that alias, or simply use the column number, as in 'ORDER BY 6'.

It only worked by sheer luck in 7.0.* and earlier.  As Peter says, the
statement violates the SQL standard --- you can only ORDER BY an output
column name or number according to SQL.  We support the extension of
ordering by an expression for simple SELECTs, but not for UNIONs.

7.0 didn't realize the difficulties involved, and tried to interpret
such an expression the same as it would do for just the first component
SELECT.  (Thought experiment: how would you interpret this command if
there is no "group_name" attribute in the second SELECT?  What if it
exists, but refers to something completely different than in the first?)
This example happened to end up being converted to 'ORDER BY 6', which
will work, but there are lots of similar cases where 7.0 produces totally
bogus ordering.  7.1 only accepts cases it can actually do correctly.

7.1's error message does need to be improved, and I have that on the
to-do list, but it's not done yet ...

            regards, tom lane