Re: select random order by random - Mailing list pgsql-general

Gregory Stark <stark@enterprisedb.com> writes:
> So I guess having the parser do this substitution kind of makes sense
> if you're thinking about things the way the spec does. It doesn't make
> much sense if you're thinking the way Postgres does of having
> arbitrary expressions there independent of what's in the select list.

Again: this is not "Postgres vs the spec", it is "SQL92 vs SQL99".
I draw your attention to the relevant text...

SQL92:

         <order by clause> ::=
              ORDER BY <sort specification list>

         <sort specification list> ::=
              <sort specification> [ { <comma> <sort specification> }... ]

         <sort specification> ::=
              <sort key> [ <collate clause > ] [ <ordering specification> ]

         <sort key> ::=
                <column name>
              | <unsigned integer>

         <ordering specification> ::= ASC | DESC

...

         10)If ORDER BY is specified, then each <sort specification> in the
            <order by clause> shall identify a column of T.

            Case:

            a) If a <sort specification> contains a <column name>, then T
              shall contain exactly one column with that <column name> and
              the <sort specification> identifies that column.

            b) If a <sort specification> contains an <unsigned integer>,
              then the <unsigned integer> shall be greater than 0 and not
              greater than the degree of T. The <sort specification> iden-
              tifies the column of T with the ordinal position specified by
              the <unsigned integer>.

(T is the table emitted by the SELECT.)


SQL99:

         <order by clause> ::=
              ORDER BY <sort specification list>

         <sort specification list> ::=
              <sort specification> [ { <comma> <sort specification> }... ]

         <sort specification> ::=
              <sort key> [ <collate clause> ] [ <ordering specification> ]

         <sort key> ::=
              <value expression>

         <ordering specification> ::= ASC | DESC


        18) If an <order by clause> is specified, then:

            a) Let K(i) be the <sort key> contained in the i-th <sort
              specification>.

            b) Let DT be the declared type of K(i).

            c) If DT is a user-defined type, then the comparison form of DT
              shall be FULL.

            d) K(i) shall not be a <literal>.

            e) If QE is a <query expression body> that is a <non-join query
              expression> that is a <non-join query term> that is a <non-
              join query primary> that is a <simple table> that is a <query
              specification>, then the <cursor specification> is said to be
              a simple table query.

            f) Case:

              i) If <sort specification list> contains any <sort key> K(i)
                 that contains a column reference to a column that is not a
                 column of T, then:

                 1) The <cursor specification> shall be a simple table
                   query.

                 2) Case:

                   A) If K(i) is not equivalent to a <value expression>
                      immediately contained in any <derived column> in the
                      <select list> SL of <query specification> QS contained
                      in QE, then:

                      I) T shall not be a grouped table.

                     II) QS shall not specify the <set quantifier> DISTINCT
                        or directly contain one or more <set function
                        specification>s.

                    III) Let C(j) be a column that is not a column of T and
                        whose column reference is contained in some K(i).

                     IV) Let SKL be the list of <derived column>s that are
                        <column name>s of column references to every C(j).
                        The columns C(j) are said to be extended sort key
                        columns.

                      V) Let TE be the <table expression> immediately
                        contained in QS.

                     VI) Let ST be the result of evaluating the <query
                        specification>:

                           SELECT SL, SKL FROM TE

                   B) Otherwise:

                      I) Let ST be T.

                     II) For every <derived column> DC(e) of SL that is
                        equivalent to K(i), if DC(e) has a <column name>,
                        then let CN(e) be that <column name>; otherwise:

                        1) Let CN(e) be an implementation-defined <column
                           name> that is not equal to any <column name> of
                           any column of ST.

                        2) DC(e) is effectively replaced by DE(e) AS CN(e)
                           in the <select list> of ST, where DE(e) is the
                           <derived element> of DC(e).

                    III) K(i) is effectively replaced by CN(e).

             ii) Otherwise, let ST be T.

            g) ST is said to be a sort table.

            h) K(i) is a <value expression>. The <value expression> shall
              not contain a <subquery> or a <set function specification>,
              but shall contain a <column reference>.

              i) Let X be any <column reference> directly contained in K(i).

             ii) If X does not contain an explicit <table or query name> or
                 <correlation name>, then K(i) shall be a <column name> that
                 shall be equivalent to the name of exactly one column of
                 ST.

              NOTE 287 - A previous version of ISO/IEC 9075 allows <sort
              specification> to be a <signed integer> to denote a column
              reference of a column of T. That facility no longer exists.
              See Annex E, "Incompatibilities with ISO/IEC 9075:1992 and
              ISO/IEC 9075-4:1996".


In the usual tradition of SQL99, the spec text is enormously less
readable than SQL92 was, but I *think* this says nearly the same thing
as what we do: a plain column reference in ORDER BY is first sought as
an output column name, and failing that sought as a column name of one
of the input tables.  They are more restrictive than we are but that's
OK.

For the particular issue at hand here, it seems to me that 18.f.i.2.B
dictates that a <sort key> matching an output column be treated as a
reference to the column, not as an independently evaluated expression.
Admittedly they are not talking about volatile functions per se, but
I think there's some defense here for the way our parser does it.

            regards, tom lane