Thread: BUG #6774: FOR IN SELECT LOOP ignores ORDER BY

The following bug has been logged on the website:

Bug reference:      6774
Logged by:          Boris Folgmann
Email address:      boris@folgmann.de
PostgreSQL version: 8.4.12
Operating system:   CentOS 6.3
Description:=20=20=20=20=20=20=20=20

This is an really interesting one!
I've trimmed down the problem so you can simply reproduce it by copy &
paste:

CREATE OR REPLACE FUNCTION ignores_order_by()
RETURNS TABLE(datname VARCHAR) AS $$
DECLARE
        r       RECORD;
BEGIN
        FOR r IN SELECT * FROM pg_database WHERE datallowconn ORDER BY
datname
        LOOP
            datname :=3D r.datname;
            RETURN NEXT;
        END LOOP;
END;
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION respects_order_by()
RETURNS TABLE(dn VARCHAR) AS $$
DECLARE
        r       RECORD;
BEGIN
        FOR r IN SELECT * FROM pg_database WHERE datallowconn ORDER BY
datname
        LOOP
            dn :=3D r.datname;
            RETURN NEXT;
        END LOOP;
END;
$$ LANGUAGE 'plpgsql';

SELECT * from ignores_order_by();
SELECT * from respects_order_by();

Now compare the different output!
The only difference of the two functions is that the first one uses a
variable with the same name of a column.
This might be a feature and not a bug, but browsing through the
documentation I could not find any documented restrictions on variable names
in this context.

On Fri, Jul 27, 2012 at 02:56:18PM +0000, boris@folgmann.de wrote:
> This is an really interesting one!
> I've trimmed down the problem so you can simply reproduce it by copy &
> paste:
> The only difference of the two functions is that the first one uses a
> variable with the same name of a column.
> This might be a feature and not a bug, but browsing through the
> documentation I could not find any documented restrictions on variable names
> in this context.

and the variable name is the problem.

generally - order by datname is understood as "order by *variable
datname*". - which is null.

change the select to:
select d.* from pg_database d order by d.datname
and now there is no more problem - because you're no longer using
ambiguous identifier.

Also, check this:
http://www.depesz.com/2009/12/16/waiting-for-8-5-plpgsql-variable-resolution/

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

Hi,

hubert depesz lubaczewski schrieb/wrote:
> generally - order by datname is understood as "order by *variable
> datname*". - which is null.

It's clear that it's a shadowing problem. But it's not a "FOR IN EXECUTE"
where a variable makes sense. I mean why is a "ORDER BY variable" valid in
"FOR IN"? In SQL you can't use ORDER BY NULL. If the order by clause is not
correct the whole statement should raise an error.

Tnx,
       Boris

On 7/30/2012 5:56 AM, Boris Folgmann wrote:
> Hi,
>
> hubert depesz lubaczewski schrieb/wrote:
>> generally - order by datname is understood as "order by *variable
>> datname*". - which is null.
>
> It's clear that it's a shadowing problem. But it's not a "FOR IN EXECUTE"
> where a variable makes sense. I mean why is a "ORDER BY variable" valid in
> "FOR IN"? In SQL you can't use ORDER BY NULL. If the order by clause is not
> correct the whole statement should raise an error.

Note that PL/pgSQL replaces all local variables inside a query with
$-parameters for the prepared SPI plan. The parser rejects ordering by
non-integer constants, but it does not reject ordering by $-parameters
or constant expressions. (maybe it should). You can for example

     SELECT * FROM something ORDER BY 'foo'||'bar';


Jan

--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin

Jan Wieck <JanWieck@Yahoo.com> writes:
> Note that PL/pgSQL replaces all local variables inside a query with
> $-parameters for the prepared SPI plan. The parser rejects ordering by
> non-integer constants, but it does not reject ordering by $-parameters
> or constant expressions. (maybe it should).

The only real reason it complains about simple constants is that
otherwise people might think "ORDER BY 1" means something different
than what it does mean according to SQL92.  Otherwise, if you want
to sort by a constant, who are we to stop you?

            regards, tom lane