On Aug 22, 2007, at 20:36 , Ben Tilly wrote:

On 8/22/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote:

On Aug 22, 2007, at 18:45 , Ben Tilly wrote:

1. Just a minor annoyance, but why must subqueries in FROM clauses
have an alias?

It's required by the SQL standard, AIUI. I wonder what EnterpriseDB

I can well believe that the standard says that you must accept
subqueries with aliases.  But does it say you must reject subqueries
without aliases?  I strongly doubt that.

If I'm reading my draft copy of the SQL:2003 spec right (and there's a good chance that I'm not, as it's not the easiest document for me to parse), aliases *are* required.

From 5WD-02-Foundation-2003-09 (the fifth working draft)

7.5 <from clause>

<from clause> ::= FROM <table reference list>
<table reference list> ::=
    <table reference> [ { <comma> <table reference> }... ]

7.6 <table reference>

<table reference> ::=
    <table factor>
  | <joined table>

<table factor> ::= <table primary> [ <sample clause> ]

<table primary> ::=
    <table or query name> [ [ AS ] <correlation name>
    [ <left paren> <derived column list><right paren> ] ]
  | <derived table> [ AS ] <correlation name>
    [ <left paren> <derived column list><right paren> ]
  | <lateral derived table> [ AS ] <correlation name>
    [ <left paren> <derived column list><right paren> ]
  | <collection derived table> [ AS ] <correlation name>
    [ <left paren> <derived column list><right paren> ]
  | <table function derived table> [ AS ] <correlation name>
    [ <left paren> <derived column list><right paren> ]
  | <only spec> [ [ AS ] <correlation name>
    [ <left paren> <derived column list><right paren> ] ]
  | <left paren> <joined table> <right paren>

<derived table> ::= <table subquery>

<correlation name> I believe is what we commonly refer to as an alias. I think <table or query name> is a table or view name, and <derived table> is a subquery. <correlation name> is optional for <table or query name> but not for <derived table>. The fact that the standard clearly makes it optional in some cases and required in others is pretty clear that <derived table> without a <correlation name> is to be rejected, don't you think?

I have no clue what EnterpriseDB does.

In case it wasn't clear, the reason I bring it up is that EnterpriseDB, while working from a PostgreSQL base, strives for Oracle compatibility.

that case, couldn't you use whatever method works in PostgreSQL in
Oracle? I haven't checked the SQL standard, but it seems unlikely
it'd allow something like

GROUP BY , , , ;

That's not what Oracle accepts that postgres does not. What Oracle accepts is:

  GROUP BY 'foo';

Thanks for clarifying. If it wasn't obvious by now, you know I'm not familiar with Oracle :)

AIUI, Integers are only allowed because the SQL standard explicitly
allows you to refer to columns by the order they appear in the SELECT
list. Otherwise the GROUP BY items need to be column names.

Need to be?

The SQL-92 standard is clear that you must accept a list of column
names.  It is also clear that a column name must be be of the form
field or table.field.

The 2003 draft (same as above) seems to agree with the SQL92 standard:

7.9 <group by clause>

<group by clause> ::=
    GROUPBY [ <set quantifier> ] <grouping element list>
<grouping element list> ::=
    <grouping element> [ { <comma> <grouping element> }... ]
<grouping element> ::=
    <ordinary grouping set>
  | <rollup list>
  | <cube list>
  | <grouping sets specification>
  | <empty grouping set>
<ordinary grouping set> ::=
    <grouping column reference>
  | <left paren> <grouping column reference list><right paren>
<grouping column reference> ::=
    <column reference> [ <collate clause> ]
<grouping column reference list> ::=
<grouping column reference> [ { <comma><grouping column reference> }... ]
<rollup list> ::=
    ROLLUP<left paren> <ordinary grouping set list><right paren>
<ordinary grouping set list> ::=
    <ordinary grouping set> [ { <comma> <ordinary grouping set> }... ]
<cube list> ::=
    CUBE<left paren> <ordinary grouping set list><right paren>
<grouping sets specification> ::=
    GROUPINGSETS <left paren> <grouping set list><right paren>
<grouping set list> ::=
    <grouping set> [ { <comma> <grouping set> }... ]
<grouping set> ::=
    <ordinary grouping set>
  | <rollup list>
  | <cube list>
  | <grouping sets specification>
  | <empty grouping set>

<empty grouping set> ::= <left paren><right paren>

6.7 <column reference>

<column reference> ::=
    <basic identifier chain>
  | MODULE<period> <qualified identifier><period> <column name>

There'd have to be a pretty strong reason to extend this, more than just a convenience, I should think.

  In no way, shape or form does that allow having
terms like trim(foo.bar) in a group by.

But every reasonable database that I know - including postgres - allows that.

Can you give an example of something like this working in PostgreSQL? I get an error when I try to use a text value in a GROUP BY clause. (Or are you referring specifically to the CASE expression corner case?)

test=# select record_id as foo, count(observation_id) as bar from observation group by trim(observation.record_id::text); ERROR: column "observation.record_id" must appear in the GROUP BY clause or be used in an aggregate function test=# select record_id as foo, count(observation_id) as bar from observation group by trim(observation.record_id); ERROR: column "observation.record_id" must appear in the GROUP BY clause or be used in an aggregate function

Postgres explicitly disallows a constant character expression.  But it
allows the constant case expression that I gave.  It would be nice for
me to not have to remember that very obscure and convoluted case.

I agree, and would move that it should be disallowed if there isn't a reason for it to be maintained, for exactly the reason you give: there shouldn't be such convoluted, and obscure corner case.

test=# select record_id as foo, count(observation_id) as bar from observation group by record_id, true;
ERROR:  non-integer constant in GROUP BY
test=# select record_id as foo, count(observation_id) as bar from observation group by record_id, case when true then true end;

What I'm trying to say is that it would be convenient for me to be
able to write:

  select bar as "baz"
    , count(*) as "some count"
  from foo
  group by bar as "baz"

That's not allowed right now because as is not allowed in a group by statement.

Ah, I see. Thanks for clarifying.

Which is to assume that
a query without a group by clause, but with an aggregate function in
the select, should have an implicit group by clause where you group by
all non-aggregate functions in the select.

For example

  SELECT foo, count(*)
  FROM bar

would be processed as:

  SELECT foo, count(*)
  FROM bar
  GROUP BY foo

It's been discussed before. I don't believe it's been rejected out of
hand (though you can check the archives), just that no one's gotten
around to it. (Don't know what the SQL-spec says on this point.)

I don't know what the SQL spec says, but I know (having talked to
other developers) that many people would find it very nice.

Since I had the spec open, I tried to look at this as well, though I must admit I found it very tough going.

I think this is the key section:

10.9 <aggregate function>


Syntax Rules


4) The argument source of an <aggregate function> is
a) If AF is immediately contained in a <set function specification>, then a table or group of a grouped table as specified in Subclause 7.10, “<having clause>”, and Subclause 7.12, “<query specification>”.

b) Otherwise, the collection of rows in the current row's window frame defined by the window structure descriptor identified by the <window function> that simply contains AF, as defined in Subclause 7.11, “<window clause>”.

Now the <query specification> stuff is pretty dense, and the <window clause> is currently impenetrable for me, so I just looked at the <having clause> and <query specification> sections referenced above.

7.10 <having clause>


Syntax Rules

1) Let HC be the <having clause>. Let TE be the <table expression> that immediately contains HC. If TE does not immediately contain a <group by clause>, then “GROUPBY ()” is implicit. Let T be the descriptor of the table defined by the <group by clause>GBC immediately contained in TE and let R be the result of GBC.

So it looks like if there's no explicit GROUP BY, we're to assume GROUP BY (), at least in the case of HAVING.

7.12 <query specification>


Syntax Rules


14) If <table expression> does not immediately contain a <group by clause> and <table expression> is simply contained in a <query expression> that is the aggregation query of some <set function specification>, then GROUP BY () is implicit.

Again, assume GROUP BY () if no explicit GROUP BY with a <set function specification>, which I understand to be some form of aggregation.

15) If T is a grouped table, then let G be the set of grouping columns of T. In each <value expression> contained in <select list> , each column reference that references a column of T shall reference some column C that is functionally dependent on G or shall be contained in an aggregated argument of a <set function specification> whose aggregation query is QS.

And a definition of grouped table is found:

4.14.2 Types of tables


A grouped table is a set of groups derived during the evaluation of a <group by clause>. A group G is a collection of rows in which, for every grouping column GC, if the value of GC in some row is not distinct from GV, then the value of GC in every row is GV; moreover, if R1 is a row in group G1 of grouped table GT and R2 is a row in GT such that for every grouping column GC the value of GC in R1 is not distinct from the value of GC in R2, then R2 is in G1. Every row in GT is in exactly one group. A group may be considered as a table. Set functions operate on groups.

So we're already talking about an explicit GROUP BY clause, and it doesn't seem to leave much wiggle room grouping columns that aren't explicitly listed. I'll readily admit I very easily could be reading this wrong or have missed a section of the spec that discusses this in more detail. (And that goes for any of my interpretations of the spec!)

One place you and I differ is that I think you'd be in favor of many more extensions in cases where the SQL spec doesn't explicitly say "Thou shalt not do X". I'm a bit more conservative here: in my opinion, if the SQL speaks to a particular issue (for example, a WHERE clause), then you should pretty much keep to the letter of what it says. Extensions are more for areas where the standard is silent. For example, full text search will be added to PostgreSQL in 8.3. AFAIK, the SQL spec doesn't talk about full text search at all, so this is an area where extensions are a natural fit. Others, such as PostgreSQL's DISTINCT ON, is a much more difficult issue, and I'd suspect if it were proposed as a new feature now it would be rejected. However, it's been a part of PostgreSQL for a long time, so for historical reasons it remains. Likewise, I doubt "text" as a synonym for "varchar" would be accepted.

Anyway, enough excursions into SpecLand for me. I'd welcome anyone else taking a crack at it.

Michael Glaesemann
grzm seespotcode net

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to