Обсуждение: Wishlist for next version: group by clause

Hi,

currently I'm writing a Web application, which should
be able to interface to any SQL database. Using perl this
seems to be straigt forward, mainly due to the DBI module
of Tim Bunce. What makes this task really difficult are
the SQL-dialects of every database. Although SQL is
standardized, there are many, subtle differences which have
to be taken into account. After stripping down my application
to an absolut basic syntax, there is still one problem left.

PostgreSQL understands the following syntax:

  select count(SUBSTR(var,1,5)),      SUBSTR(var,1,5)      from t group by 2;
  select count(SUBSTR(var,1,5)) as x, SUBSTR(var,1,5) as y from t group by y;

Unfortunately other databases - like Oracle - are not able to
handle these statements. Oracle understands only the following syntax:

  select count(SUBSTR(var,1,5)), SUBSTR(var,1,5) from t group by SUBSTR(var,1,5);

which gives an error with PostgreSQL !


I don't know if any of these variants are standard or non-standard,
but it would be very helpful, if PostgreSQL would be able to
handle all of these examples. From the functional point of view,
there is no difference. I guess, only the parser has to be adapted.


Edmund
--
Edmund Mergl          mailto:E.Mergl@bawue.de
Im Haldenhau 9        http://www.bawue.de/~mergl
70565 Stuttgart       fon: +49 711 747503
Germany

I didn't realize PG could not do

    group by [function on column]

Ouch!

I *think* all the "real" RDBMS can do this.  If Oracle and
Sybase both support it, that makes it more or less a de facto
standard :-) I'm sure we use this syntax in several places in
our apps -- certainly in our time-series analysis package.
Implication is that 6.3 is still not functional enough to
replace an existing commercial SQL server such as Oracle or
Sybase for production apps, without expensive manual proofing
and rewriting of embedded SQL statements.

Does anyone know whether this group by syntax is ANSI SQL92?

            ---------------

There must be many sites in the same boat with mine:  running
an outmoded version of one of the Big Guys' engines, unwilling
to pay the outrageous support and upgrade fees required to get
current, wanting full Linux support, yet unable to switch to
PG because of small gotchas like this one.  It's a small
gotcha if you are writing a brand new app, but it's a large
gotcha if you have to comb through thousands of embedded SQL
statements in hundreds of production apps and manually fix it
in each instance.

Is there a list of the "PG is different" gotchas like this,
and is their elimination being given a high priority? I think
"plug-n-play" replacement of existing servers with PG is a
good practical goal -- so long as the app writers have wisely
avoided vendor-specific syntax in their SQL, of course :-)
I think conversions of this sort would be good publicity
for PG, and I would be willing to write up a public report
on mine if and when PG evolves to the point where I can
do it!

What do you all think about the PR value of new PG-driven
apps vs conversion of existing production apps?

de

.............................................................................
:De Clarke, Software Engineer                     UCO/Lick Observatory, UCSC:
:Mail: de@ucolick.org | "There is no problem in computer science that cannot:
:Web: www.ucolick.org |  be solved by another level of indirection"  --J.O. :

>
>
> I didn't realize PG could not do
>
>     group by [function on column]
>
> Ouch!
>
> I *think* all the "real" RDBMS can do this.  If Oracle and
> Sybase both support it, that makes it more or less a de facto
> standard :-) I'm sure we use this syntax in several places in
> our apps -- certainly in our time-series analysis package.
> Implication is that 6.3 is still not functional enough to
> replace an existing commercial SQL server such as Oracle or
> Sybase for production apps, without expensive manual proofing
> and rewriting of embedded SQL statements.
>
> Does anyone know whether this group by syntax is ANSI SQL92?

Added to TODO.  Vadim may have a comment on this, and how hard it is to
do.  I know we allow functional indexes, but am not sure how that
relates to this problem.


--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Do you mean in a select statement?   Such as:

    SELECT func(date) as month, count(*) FROM foo GROUP BY month;

Or even:

    SELECT count(*) FROM foo GROUP BY func(date);

The first is supported.   The second would require some changes to the parser.

De Clarke wrote:

> I didn't realize PG could not do
>
>         group by [function on column]
>
> Ouch!
>

David Hartwig said:

>> Do you mean in a select statement?   Such as:
>>
>>     SELECT func(date) as month, count(*) FROM foo GROUP BY month;
>>
>> Or even:
>>
>>     SELECT count(*) FROM foo GROUP BY func(date);
>>
>> The first is supported.   The second would require some changes to the parser.

#2 was what I had in mind...

This is a pointless query, but it demonstrates a couple of
things that the sybase SQL interpreter supports:

    select avg(datepart(minute,date)) from hires_events
        group by datepart(hour,date)

1.  you can apply stat functions such as avg and sum to
    functions on columns as well as to raw columns

2.  you can group by a function on a column

I think Oracle will do this also...

de

.............................................................................
:De Clarke, Software Engineer                     UCO/Lick Observatory, UCSC:
:Mail: de@ucolick.org | "There is no problem in computer science that cannot:
:Web: www.ucolick.org |  be solved by another level of indirection"  --J.O. :