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

Sat, 13 Jun 1998 20:07:42 +0200
Edmund Mergl <E.Mergl@bawue.de>
Wishlist for next version: group by clause

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




Tue, 16 Jun 1998 11:33:39 -0700 (PDT)
De Clarke <de@ucolick.org>
Re: [HACKERS] Wishlist for next version: group by clause 
> 
> 
> 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)

Tue, 16 Jun 1998 15:09:41 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Wishlist for next version: group by clause
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!
>




Tue, 16 Jun 1998 16:27:35 -0400
David Hartwig <daveh@insightdist.com>
Re: [HACKERS] Wishlist for next version: group by clause

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




Tue, 16 Jun 1998 14:25:18 -0700 (PDT)
De Clarke <de@ucolick.org>
group by :  syntactic example (sybase)