Thread

1. BUG #6774: FOR IN SELECT LOOP ignores ORDER BY

   Boris Folgmann <boris@folgmann.de> — 2012-07-27T14:56:18Z

   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:        
   
   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 := 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 := 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.
   
   
   
   

2. Re: BUG #6774: FOR IN SELECT LOOP ignores ORDER BY

   hubert depesz lubaczewski <depesz@depesz.com> — 2012-07-27T16:03:24Z

   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/
   
   

3. Re: BUG #6774: FOR IN SELECT LOOP ignores ORDER BY

   Boris Folgmann <boris@folgmann.de> — 2012-07-30T09:56:35Z

   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
   
   
   

4. Re: BUG #6774: FOR IN SELECT LOOP ignores ORDER BY

   Jan Wieck <janwieck@yahoo.com> — 2012-07-30T16:21:38Z

   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
   
   

5. Re: BUG #6774: FOR IN SELECT LOOP ignores ORDER BY

   Tom Lane <tgl@sss.pgh.pa.us> — 2012-07-30T16:36:13Z

   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