pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
prob with aggregate and group by - returns multiples
24+ messages / 9 participants
[nested] [flat]

* prob with aggregate and group by - returns multiples
@ 2000-02-28 06:22  George Dau <gedau@isa.mim.com.au>
  0 siblings, 1 reply; 24+ messages in thread

From: George Dau @ 2000-02-28 06:22 UTC (permalink / raw)
  To: pgsql-sql

I have a group by that groups some, but not all, identical rows. Here are
the details:
I can reproduce the problem using one single table, details below;

Table    = hits
+----------------------------------+----------------------------------+-----
--+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-----
--+
| userid                           | varchar                          |
12 |
| dat                              | date                             |
4 |
| tim                              | time                             |
8 |
| ipa                              | int4                             |
4 |
| ipb                              | int4                             |
4 |
| ipc                              | int4                             |
4 |
| ipd                              | int4                             |
4 |
| site                             | varchar                          |
50 |
+----------------------------------+----------------------------------+-----
--+

I want a report showing how many occurrences of "site" there are for each
distinct "site".
There is a lot of data, in there, so I'll look at a particular example of
the problem. When
I run this query:

select count(site), site
  from hits
 group by site;

The output contains lines like the following. Note that these are all
consecutive in the output.

    2|xlink.zdnet.com
    2|xlink.zdnet.com
    1|xlink.zdnet.com
    2|xlink.zdnet.com
    2|xlink.zdnet.com
    2|xlink.zdnet.com
    3|xlink.zdnet.com
    2|xlink.zdnet.com
    3|xlink.zdnet.com
    2|xlink.zdnet.com
    1|xlink.zdnet.com

Suspecting that there were differences in each xlink.zdnet.com, I counted
just them:

web=> select count (*) from hits where site='xlink.zdnet.com';
count
-----
   22
(1 row)

So, all 22 xlink.zdnet.com are selected above, but they have not grouped in
the previous
one. Any ideas why?

On a lesser note: I tried "select * into temp from hits" as per the doco,
but it barfs. Looks
like the syntax has changed. Any current "railway" diagrams for the commands
anywhere? Also,
where can I find out about other environment settings like PGDATESTYLE? I
know about that one,
but what others are there?

I'm trying to port from Oracle. Any guides? There are many differences in
the SQL.


**************************************************************
The information contained in this E-Mail is confidential
and is intended only for the use of the addressee(s).
If you receive this E-Mail in error, any use, distribution
or copying of this E-Mail is not permitted.  You are 
requested to forward unwanted E-Mail and address any problems
to the MIM Holdings Limited Help Desk. 
E-Mail: helpdesk@mim.com.au or phone: Australia 07 3833 8042.
**************************************************************



^ permalink  raw  reply  [nested|flat] 24+ messages in thread

* Re: [SQL] prob with aggregate and group by - returns multiples
@ 2000-02-28 07:50  Tom Lane <tgl@sss.pgh.pa.us>
  parent: George Dau <gedau@isa.mim.com.au>
  0 siblings, 1 reply; 24+ messages in thread

From: Tom Lane @ 2000-02-28 07:50 UTC (permalink / raw)
  To: George Dau <gedau@isa.mim.com.au>; +Cc: pgsql-sql

George Dau <gedau@isa.mim.com.au> writes:
> select count(site), site from hits group by site;

> The output contains lines like the following. Note that these are all
> consecutive in the output.

>     2|xlink.zdnet.com
>     2|xlink.zdnet.com
>     1|xlink.zdnet.com
>     2|xlink.zdnet.com
>     2|xlink.zdnet.com
>     2|xlink.zdnet.com
>     3|xlink.zdnet.com
>     2|xlink.zdnet.com
>     3|xlink.zdnet.com
>     2|xlink.zdnet.com
>     1|xlink.zdnet.com

> Suspecting that there were differences in each xlink.zdnet.com, I counted
> just them:

> web=> select count (*) from hits where site='xlink.zdnet.com';
> count
> -----
>    22
> (1 row)

> So, all 22 xlink.zdnet.com are selected above, but they have not grouped in
> the previous one. Any ideas why?

Wow, that is bizarre.  My first thought was that you had varying numbers
of trailing blanks in the "site" values, but your second example seems
to disprove that theory.

What Postgres version are you running, and on what platform?  Can you
generate a self-contained example (a script that demonstrates the error
from a standing start)?  I suspect you may be hitting a platform-
specific porting problem, but it's just speculation unless we have a
self-contained test case to try on other machines.

> On a lesser note: I tried "select * into temp from hits" as per the doco,
> but it barfs.

Postgres thinks that TEMP is a keyword, so it won't take it as a table
name unless you put quotes around it.  If we have doco examples that
use TEMP as a table name, they need to be changed --- do you recall
where you saw that, exactly?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 24+ messages in thread

* RE: [SQL] prob with aggregate and group by - returns multiples
@ 2000-02-28 21:58  George Dau <gedau@isa.mim.com.au>
  0 siblings, 2 replies; 24+ messages in thread

From: George Dau @ 2000-02-28 21:58 UTC (permalink / raw)
  To: pgsql-sql

The selects below are self-contained. The one that is failing is, in full,
#!/bin/bash
#
psql web <<EOS

select count(site), site
  from hits
 group by site;

\q;
EOS

The first part of the output is:
count|site
-----+-------------------------------
    8|1.digital.cnet.com
   18|1000.stars.ru
    1|10e2.linkexchange.ru
    2|128.11.10.58
    1|192.148.121.27
    1|195.115.59.156
    7|195.172.106.37
    1|195.86.97.21

and then, finally,
    2|xbase.plugsys.com
    3|xbase.plugsys.com
    2|xbase.plugsys.com
    1|xbase.plugsys.com
    2|xlink.zdnet.com
    2|xlink.zdnet.com
    1|xlink.zdnet.com
    2|xlink.zdnet.com
    2|xlink.zdnet.com
    2|xlink.zdnet.com
    3|xlink.zdnet.com
    2|xlink.zdnet.com
    3|xlink.zdnet.com
    2|xlink.zdnet.com
    1|xlink.zdnet.com
    1|y0.extreme-dm.com
    1|y0.extreme-dm.com
    2|y1.extreme-dm.com
    1|yahoo.com.au
    1|yellowpages.com.au
    2|z0.extreme-dm.com
    1|z0.extreme-dm.com
    2|z1.extreme-dm.com
    2|z1.extreme-dm.com
    1|z1.extreme-dm.com
(5510 rows)

I have postgresql version 6.2 running on RedHat Linux kernel 2.2.5  The box
has 128Meg RAM
and lotsa disk. Upgrading to 6.5 will be either expensive (download) or very
slow (buy CD
from Canada). I hope there is another solution.

The TEMP table references are in HTML doco in query.html where it describes
"select into".

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, 28 February 2000 17:50
To: George Dau
Cc: 'pgsql-sql@postgresql.org'
Subject: Re: [SQL] prob with aggregate and group by - returns multiples 


George Dau <gedau@isa.mim.com.au> writes:
> select count(site), site from hits group by site;

> The output contains lines like the following. Note that these are all
> consecutive in the output.

>     2|xlink.zdnet.com
>     2|xlink.zdnet.com
>     1|xlink.zdnet.com
>     2|xlink.zdnet.com
>     2|xlink.zdnet.com
>     2|xlink.zdnet.com
>     3|xlink.zdnet.com
>     2|xlink.zdnet.com
>     3|xlink.zdnet.com
>     2|xlink.zdnet.com
>     1|xlink.zdnet.com

> Suspecting that there were differences in each xlink.zdnet.com, I counted
> just them:

> web=> select count (*) from hits where site='xlink.zdnet.com';
> count
> -----
>    22
> (1 row)

> So, all 22 xlink.zdnet.com are selected above, but they have not grouped
in
> the previous one. Any ideas why?

Wow, that is bizarre.  My first thought was that you had varying numbers
of trailing blanks in the "site" values, but your second example seems
to disprove that theory.

What Postgres version are you running, and on what platform?  Can you
generate a self-contained example (a script that demonstrates the error
from a standing start)?  I suspect you may be hitting a platform-
specific porting problem, but it's just speculation unless we have a
self-contained test case to try on other machines.

> On a lesser note: I tried "select * into temp from hits" as per the doco,
> but it barfs.

Postgres thinks that TEMP is a keyword, so it won't take it as a table
name unless you put quotes around it.  If we have doco examples that
use TEMP as a table name, they need to be changed --- do you recall
where you saw that, exactly?

			regards, tom lane


**************************************************************
The information contained in this E-Mail is confidential
and is intended only for the use of the addressee(s).
If you receive this E-Mail in error, any use, distribution
or copying of this E-Mail is not permitted.  You are 
requested to forward unwanted E-Mail and address any problems
to the MIM Holdings Limited Help Desk. 
E-Mail: helpdesk@mim.com.au or phone: Australia 07 3833 8042.
**************************************************************



^ permalink  raw  reply  [nested|flat] 24+ messages in thread

* Re: [SQL] prob with aggregate and group by - returns multiples
@ 2000-02-28 22:53  Tom Lane <tgl@sss.pgh.pa.us>
  parent: George Dau <gedau@isa.mim.com.au>
  1 sibling, 0 replies; 24+ messages in thread

From: Tom Lane @ 2000-02-28 22:53 UTC (permalink / raw)
  To: George Dau <gedau@isa.mim.com.au>; +Cc: pgsql-sql

George Dau <gedau@isa.mim.com.au> writes:
> I have postgresql version 6.2 running on RedHat Linux kernel 2.2.5

6.2 ???

Sorry, fella, but that's roughly Late Neolithic Era in Postgres time.
I have no doubt that what you are running into is a long-dead bug.
You won't find much enthusiasm among the developers for remembering
exactly what it was.  I suggest an upgrade.

> Upgrading to 6.5 will be either expensive (download) or very slow (buy
> CD from Canada). I hope there is another solution.

According to our mirror-sites list, you can find Postgres down under
at ftp://mirror.aarnet.edu.au/pub/postgresql or
http://postgresql.mirror.aarnet.edu.au.  I'm not having any luck
contacting that site from here right now, but perhaps it will work
for you.  If not, there are other mirrors that are at least on your
side of the globe; see http://www.postgresql.org/sites.html.

> The TEMP table references are in HTML doco in query.html where it describes
> "select into".

Ah, I see at least one such still there in the current version.  Will
get fixed.  Thanks for the pointer.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 24+ messages in thread

* Re: [SQL] prob with aggregate and group by - returns multiples
@ 2000-02-28 23:19  Peter Eisentraut <peter_e@gmx.net>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 24+ messages in thread

From: Peter Eisentraut @ 2000-02-28 23:19 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: George Dau <gedau@isa.mim.com.au>; pgsql-sql; PostgreSQL Development <pgsql-hackers@postgresql.org>

Tom Lane writes:

> > On a lesser note: I tried "select * into temp from hits" as per the doco,
> > but it barfs.
> 
> Postgres thinks that TEMP is a keyword, so it won't take it as a table
> name unless you put quotes around it.

This is really an unfortunate case where someone should have read the SQL
standard before putting in a feature. The SQL keyword is TEMPORARY, and
TEMP is really a popular name for a dummy table.

I tried making TEMP a ColId but it croaks on this syntactic contruct:

SELECT xxx INTO [TEMP] [TABLE] tablename

which is incidentally not SQL either. If someone is interested in allowing
'temp' as an identifier, there doesn't seem to be a good way without
requiring the TABLE keyword above. Would that be worth it for 7.0 maybe?

Of course the documentation should be changed to TEMPORARY as well in
various places.

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden




^ permalink  raw  reply  [nested|flat] 24+ messages in thread

* Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiples
@ 2000-02-29 05:44  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Thomas Lockhart <lockhart@alumni.caltech.edu>
  1 sibling, 1 reply; 24+ messages in thread

From: Tom Lane @ 2000-02-29 05:44 UTC (permalink / raw)
  To: Thomas Lockhart <lockhart@alumni.caltech.edu>; +Cc: pgsql-sql; PostgreSQL Development <pgsql-hackers@postgresql.org>

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> This is really an unfortunate case where someone should have read the SQL
>> standard before putting in a feature. The SQL keyword is TEMPORARY, and
>> TEMP is really a popular name for a dummy table.

> So why not yank TEMP and require TEMPORARY?

Probably we ought to stop to ask why TEMP is in there to begin with;
perhaps for compatibility with some other RDBMS?

If not, I'd vote for pulling it out.  That's a heck of a poor word to
reserve.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 24+ messages in thread

* Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiples
@ 2000-02-29 05:48  Thomas Lockhart <lockhart@alumni.caltech.edu>
  parent: Peter Eisentraut <peter_e@gmx.net>
  0 siblings, 2 replies; 24+ messages in thread

From: Thomas Lockhart @ 2000-02-29 05:48 UTC (permalink / raw)
  To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; George Dau <gedau@isa.mim.com.au>; pgsql-sql; PostgreSQL Development <pgsql-hackers@postgresql.org>

> > Postgres thinks that TEMP is a keyword, so it won't take it as a table
> > name unless you put quotes around it.
> This is really an unfortunate case where someone should have read the SQL
> standard before putting in a feature. The SQL keyword is TEMPORARY, and
> TEMP is really a popular name for a dummy table.

So why not yank TEMP and require TEMPORARY? Saving an extra 5
characters of typing is not a good enough reason to keep it imho, and
if the SQL92 standard requires a particular form why bother extending
it?

A major release is a good time to adjust syntax to promote
compliance...

                      - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California



^ permalink  raw  reply  [nested|flat] 24+ messages in thread

* Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiplesh
@ 2000-02-29 05:59  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 24+ messages in thread

From: Bruce Momjian @ 2000-02-29 05:59 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Thomas Lockhart <lockhart@alumni.caltech.edu>; pgsql-sql; PostgreSQL Development <pgsql-hackers@postgreSQL.org>

> Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> >> This is really an unfortunate case where someone should have read the SQL
> >> standard before putting in a feature. The SQL keyword is TEMPORARY, and
> >> TEMP is really a popular name for a dummy table.
> 
> > So why not yank TEMP and require TEMPORARY?
> 
> Probably we ought to stop to ask why TEMP is in there to begin with;
> perhaps for compatibility with some other RDBMS?

It was me.  Informix uses it.

> 
> If not, I'd vote for pulling it out.  That's a heck of a poor word to
> reserve.

I am afraid of lots of user complaints, even if we had not already used
TEMP.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 24+ messages in thread

* Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiplesh
@ 2000-02-29 06:48  Thomas Lockhart <lockhart@alumni.caltech.edu>
  parent: Bruce Momjian <pgman@candle.pha.pa.us>
  1 sibling, 2 replies; 24+ messages in thread

From: Thomas Lockhart @ 2000-02-29 06:48 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql; PostgreSQL Development <pgsql-hackers@postgreSQL.org>

> > If not, I'd vote for pulling it out.  That's a heck of a poor word to
> > reserve.
> I am afraid of lots of user complaints, even if we had not already used
> TEMP.

OK, but we've already got "user complaints" about TEMP being a
reserved word, so that part seems to balance out. There is apparently
no basis in published standards for TEMP being a reserved word. And
btw it is not currently documented as a reserved word in
syntax.sgml...

                     - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California



^ permalink  raw  reply  [nested|flat] 24+ messages in thread

* Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiples
@ 2000-02-29 10:54  Peter Eisentraut <e99re41@DoCS.UU.SE>
  parent: Thomas Lockhart <lockhart@alumni.caltech.edu>
  1 sibling, 1 reply; 24+ messages in thread

From: Peter Eisentraut @ 2000-02-29 10:54 UTC (permalink / raw)
  To: Thomas Lockhart <lockhart@alumni.caltech.edu>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; George Dau <gedau@isa.mim.com.au>; pgsql-sql; PostgreSQL Development <pgsql-hackers@postgresql.org>

On Tue, 29 Feb 2000, Thomas Lockhart wrote:

> > > Postgres thinks that TEMP is a keyword, so it won't take it as a table
> > > name unless you put quotes around it.
> > This is really an unfortunate case where someone should have read the SQL
> > standard before putting in a feature. The SQL keyword is TEMPORARY, and
> > TEMP is really a popular name for a dummy table.
> 
> So why not yank TEMP and require TEMPORARY? Saving an extra 5
> characters of typing is not a good enough reason to keep it imho, and
> if the SQL92 standard requires a particular form why bother extending
> it?
> 
> A major release is a good time to adjust syntax to promote
> compliance...

I've been (lightly) bashed in the past for proposing such things (see
END/ABORT) but I'm with you. I think that TEMP may be far too wide-spread
by now, though.

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden




^ permalink  raw  reply  [nested|flat] 24+ messages in thread

* RE: [SQL] prob with aggregate and group by - returns multiples
@ 2000-02-29 11:59  Peter Eisentraut <e99re41@DoCS.UU.SE>
  parent: George Dau <gedau@isa.mim.com.au>
  1 sibling, 0 replies; 24+ messages in thread

From: Peter Eisentraut @ 2000-02-29 11:59 UTC (permalink / raw)
  To: George Dau <gedau@isa.mim.com.au>; +Cc: pgsql-sql

On Tue, 29 Feb 2000, George Dau wrote:

> I have postgresql version 6.2 running on RedHat Linux kernel 2.2.5 The
> box has 128Meg RAM and lotsa disk. Upgrading to 6.5 will be either
> expensive (download) or very slow (buy CD from Canada). I hope there
> is another solution.

Wow, those still exist? The source is about 7MB and I'm sure it's mirrored
in Australia. For the few dollars you might spend there you will move
*light years* ahead in terms of speed, features, and reliability. I'm not
even sure anyone remembers 6.2 in order to help you. :(


-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden




^ permalink  raw  reply  [nested|flat] 24+ messages in thread

* Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returnsmultiples
@ 2000-02-29 14:35  Thomas Lockhart <lockhart@alumni.caltech.edu>
  parent: Peter Eisentraut <e99re41@DoCS.UU.SE>
  0 siblings, 0 replies; 24+ messages in thread

From: Thomas Lockhart @ 2000-02-29 14:35 UTC (permalink / raw)
  To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; George Dau <gedau@isa.mim.com.au>; pgsql-sql; PostgreSQL Development <pgsql-hackers@postgresql.org>

> > A major release is a good time to adjust syntax to promote
> > compliance...
> I've been (lightly) bashed in the past for proposing such things (see
> END/ABORT) but I'm with you. I think that TEMP may be far too wide-spread
> by now, though.

Well, imho the TEMP issue is not identical to END/ABORT. For TEMP, we
are unnecessarily restricting the space of possible identifiers,
eliminating a common and obvious name. The fix is trivial, and the
affected parties are *only* those who use temporary tables and who
chose to *not* use SQL92 syntax, which was always available.

                    - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California



^ permalink  raw  reply  [nested|flat] 24+ messages in thread

* Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns  multiplesh
@ 2000-02-29 14:35  Don Baccus <dhogaza@pacifier.com>
  parent: Bruce Momjian <pgman@candle.pha.pa.us>
  1 sibling, 2 replies; 24+ messages in thread

From: Don Baccus @ 2000-02-29 14:35 UTC (permalink / raw)
  To: Thomas Lockhart <lockhart@alumni.caltech.edu>; Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql; PostgreSQL Development <pgsql-hackers@postgreSQL.org>

At 06:48 AM 2/29/00 +0000, Thomas Lockhart wrote:
>> > If not, I'd vote for pulling it out.  That's a heck of a poor word to
>> > reserve.
>> I am afraid of lots of user complaints, even if we had not already used
>> TEMP.
>
>OK, but we've already got "user complaints" about TEMP being a
>reserved word, so that part seems to balance out. There is apparently
>no basis in published standards for TEMP being a reserved word. And
>btw it is not currently documented as a reserved word in
>syntax.sgml...

I vote for the SQL92 TEMPORARY.  Let's not add a keyword that is non-standard
just because one or another commercial database makes use of it, unless
there's some real functionality to be gained that's not covered by the
standard.

TEMP is covered in SQL92 by TEMPORARY.

As an example of when adopting a construct from another commercial database
makes sense to me, SEQUENCE and SERIAL are both convenient means of generating
unique keys that have no equivalent in the standard.



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



^ permalink  raw  reply  [nested|flat] 24+ messages in thread

* Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiplesh
@ 2000-02-29 14:57  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Thomas Lockhart <lockhart@alumni.caltech.edu>
  1 sibling, 0 replies; 24+ messages in thread

From: Tom Lane @ 2000-02-29 14:57 UTC (permalink / raw)
  To: Thomas Lockhart <lockhart@alumni.caltech.edu>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-sql; PostgreSQL Development <pgsql-hackers@postgreSQL.org>

>>>> If not, I'd vote for pulling it out.  That's a heck of a poor word to
>>>> reserve.
>> I am afraid of lots of user complaints, even if we had not already used
>> TEMP.

> OK, but we've already got "user complaints" about TEMP being a
> reserved word, so that part seems to balance out. There is apparently
> no basis in published standards for TEMP being a reserved word. And
> btw it is not currently documented as a reserved word in
> syntax.sgml...

The real problem is not that we accept TEMP as a synonym for TEMPORARY;
it is that we treat TEMP as a reserved word.  What are the chances that
we could make it a member of the ColId list?  I am thinking that
"... INTO TEMP temp" is *not* ambiguous given one token lookahead...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 24+ messages in thread

* Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiplesh
@ 2000-02-29 16:09  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Don Baccus <dhogaza@pacifier.com>
  1 sibling, 2 replies; 24+ messages in thread

From: Tom Lane @ 2000-02-29 16:09 UTC (permalink / raw)
  To: Don Baccus <dhogaza@pacifier.com>; +Cc: Thomas Lockhart <lockhart@alumni.caltech.edu>; Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-sql; PostgreSQL Development <pgsql-hackers@postgreSQL.org>

Don Baccus <dhogaza@pacifier.com> writes:
> I vote for the SQL92 TEMPORARY.  Let's not add a keyword that is non-standard
> just because one or another commercial database makes use of it,

You're missing the point: we are not talking about *adding* a keyword,
we're talking about *removing* one that we've already supported for
a year or so.  That changes matters considerably, IMHO.

I have in fact been able to make a conflict-free grammar in which TEMP
is accepted but not reserved.  It requires a certain amount of
redundancy in the productions (see below), but I think this is a
worthwhile tradeoff for not breaking existing user code.

Shall I commit this?

			regards, tom lane


/*
 * Redundancy here is needed to avoid shift/reduce conflicts,
 * since TEMP is not a reserved word.  See also OptTemp.
 *
 * The result is a cons cell (not a true list!) containing
 * a boolean and a table name.
 */
OptTempTableName:  TEMPORARY opt_table relation_name
                { $$ = lcons(makeInteger(TRUE), (List *) $3); }
            | TEMP opt_table relation_name
                { $$ = lcons(makeInteger(TRUE), (List *) $3); }
            | LOCAL TEMPORARY opt_table relation_name
                { $$ = lcons(makeInteger(TRUE), (List *) $4); }
            | LOCAL TEMP opt_table relation_name
                { $$ = lcons(makeInteger(TRUE), (List *) $4); }
            | GLOBAL TEMPORARY opt_table relation_name
                {
                    elog(ERROR, "GLOBAL TEMPORARY TABLE is not currently supported");
                    $$ = lcons(makeInteger(TRUE), (List *) $4);
                }
            | GLOBAL TEMP opt_table relation_name
                {
                    elog(ERROR, "GLOBAL TEMPORARY TABLE is not currently supported");
                    $$ = lcons(makeInteger(TRUE), (List *) $4);
                }
            | TABLE relation_name
                { $$ = lcons(makeInteger(FALSE), (List *) $2); }
            | relation_name
                { $$ = lcons(makeInteger(FALSE), (List *) $1); }
        ;



^ permalink  raw  reply  [nested|flat] 24+ messages in thread

* Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns  multiplesh
@ 2000-02-29 16:38  Peter Eisentraut <e99re41@DoCS.UU.SE>
  parent: Don Baccus <dhogaza@pacifier.com>
  1 sibling, 0 replies; 24+ messages in thread

From: Peter Eisentraut @ 2000-02-29 16:38 UTC (permalink / raw)
  To: Don Baccus <dhogaza@pacifier.com>; +Cc: Thomas Lockhart <lockhart@alumni.caltech.edu>; Bruce Momjian <pgman@candle.pha.pa.us>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql; PostgreSQL Development <pgsql-hackers@postgreSQL.org>

On Tue, 29 Feb 2000, Don Baccus wrote:

> I vote for the SQL92 TEMPORARY.  Let's not add a keyword that is non-standard
> just because one or another commercial database makes use of it, unless
> there's some real functionality to be gained that's not covered by the
> standard.

The difference is that TEMP is already a keyword since 6.5 and we're
considering removing it.

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden




^ permalink  raw  reply  [nested|flat] 24+ messages in thread

* Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiplesh
@ 2000-02-29 17:19  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Thomas Lockhart <lockhart@alumni.caltech.edu>
  1 sibling, 1 reply; 24+ messages in thread

From: Bruce Momjian @ 2000-02-29 17:19 UTC (permalink / raw)
  To: Thomas Lockhart <lockhart@alumni.caltech.edu>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql; PostgreSQL Development <pgsql-hackers@postgreSQL.org>

> > > If not, I'd vote for pulling it out.  That's a heck of a poor word to
> > > reserve.
> > I am afraid of lots of user complaints, even if we had not already used
> > TEMP.
> 
> OK, but we've already got "user complaints" about TEMP being a
> reserved word, so that part seems to balance out. There is apparently
> no basis in published standards for TEMP being a reserved word. And
> btw it is not currently documented as a reserved word in
> syntax.sgml...

OK, I certainly didn't look at the standard to when I implemented TEMP
tables.  In fact, I was surprised it worked considering it is just a
hack on the cache code.

Let's forget I made a mistake, and consider how many people are going to
think they should use TEMP and how many TEMPORARY.  I personally would
guess TEMP and never TEMPORARY.  I wonder if others would too.

So are we willing to field questions from people trying to use TEMP
tables and trying TEMP and not TEMPORARY.  I realize the restriction on
a field called TEMP, but we don't get those very often.  How many people
are going to guess TEMP and not TEMPORARY?

Of course, as a Unix guy, I may have guessed TMP too.  :-)

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 24+ messages in thread

* Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiplesh
@ 2000-02-29 17:21  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 24+ messages in thread

From: Bruce Momjian @ 2000-02-29 17:21 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Don Baccus <dhogaza@pacifier.com>; Thomas Lockhart <lockhart@alumni.caltech.edu>; pgsql-sql; PostgreSQL Development <pgsql-hackers@postgreSQL.org>

Looks good to me.

> Don Baccus <dhogaza@pacifier.com> writes:
> > I vote for the SQL92 TEMPORARY.  Let's not add a keyword that is non-standard
> > just because one or another commercial database makes use of it,
> 
> You're missing the point: we are not talking about *adding* a keyword,
> we're talking about *removing* one that we've already supported for
> a year or so.  That changes matters considerably, IMHO.
> 
> I have in fact been able to make a conflict-free grammar in which TEMP
> is accepted but not reserved.  It requires a certain amount of
> redundancy in the productions (see below), but I think this is a
> worthwhile tradeoff for not breaking existing user code.
> 
> Shall I commit this?
> 
> 			regards, tom lane
> 
> 
> /*
>  * Redundancy here is needed to avoid shift/reduce conflicts,
>  * since TEMP is not a reserved word.  See also OptTemp.
>  *
>  * The result is a cons cell (not a true list!) containing
>  * a boolean and a table name.
>  */
> OptTempTableName:  TEMPORARY opt_table relation_name
>                 { $$ = lcons(makeInteger(TRUE), (List *) $3); }
>             | TEMP opt_table relation_name
>                 { $$ = lcons(makeInteger(TRUE), (List *) $3); }
>             | LOCAL TEMPORARY opt_table relation_name
>                 { $$ = lcons(makeInteger(TRUE), (List *) $4); }
>             | LOCAL TEMP opt_table relation_name
>                 { $$ = lcons(makeInteger(TRUE), (List *) $4); }
>             | GLOBAL TEMPORARY opt_table relation_name
>                 {
>                     elog(ERROR, "GLOBAL TEMPORARY TABLE is not currently supported");
>                     $$ = lcons(makeInteger(TRUE), (List *) $4);
>                 }
>             | GLOBAL TEMP opt_table relation_name
>                 {
>                     elog(ERROR, "GLOBAL TEMPORARY TABLE is not currently supported");
>                     $$ = lcons(makeInteger(TRUE), (List *) $4);
>                 }
>             | TABLE relation_name
>                 { $$ = lcons(makeInteger(FALSE), (List *) $2); }
>             | relation_name
>                 { $$ = lcons(makeInteger(FALSE), (List *) $1); }
>         ;
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 24+ messages in thread

* Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiplesh
@ 2000-03-01 03:26  Christopher Sawtell <csawtell@xtra.co.nz>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 24+ messages in thread

From: Christopher Sawtell @ 2000-03-01 03:26 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Don Baccus <dhogaza@pacifier.com>; +Cc: Thomas Lockhart <lockhart@alumni.caltech.edu>; Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-sql; PostgreSQL Development <pgsql-hackers@postgreSQL.org>

On Wed, 01 Mar 2000, Tom Lane wrote:
> Don Baccus <dhogaza@pacifier.com> writes:
> > I vote for the SQL92 TEMPORARY.  Let's not add a keyword that is non-standard
> > just because one or another commercial database makes use of it,
> 
> You're missing the point: we are not talking about *adding* a keyword,
> we're talking about *removing* one that we've already supported for
> a year or so.  That changes matters considerably, IMHO.
> 
> I have in fact been able to make a conflict-free grammar in which TEMP
> is accepted but not reserved.  It requires a certain amount of
> redundancy in the productions (see below), but I think this is a
> worthwhile tradeoff for not breaking existing user code.
> 
> Shall I commit this?

  Is there not also the possibility of  making this a configure-time option?

./configure --temporary-table=TEMPORARY   # or TEMP as you wish

Default to TEMPORARY in accord with SQL 92.

--
Sincerely etc.,

 NAME       Christopher Sawtell - Support Engineer - iOpen Technologies Ltd.
 CELL PHONE 021 257 4451
 ICQ UIN    45863470
 EMAIL      chris @ iopen . co . nz,  csawtell @ xtra . co . nz
 CNOTES     ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz

 ---->>> Please refrain from using HTML attachments in e-mails to me. <<<----




^ permalink  raw  reply  [nested|flat] 24+ messages in thread

* Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiplesh
@ 2000-03-01 06:14  Thomas Lockhart <lockhart@alumni.caltech.edu>
  parent: Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 2 replies; 24+ messages in thread

From: Thomas Lockhart @ 2000-03-01 06:14 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql; PostgreSQL Development <pgsql-hackers@postgreSQL.org>

> So are we willing to field questions from people trying to use TEMP
> tables and trying TEMP and not TEMPORARY.  I realize the restriction on
> a field called TEMP, but we don't get those very often.  How many people
> are going to guess TEMP and not TEMPORARY?

Well, that's one reason to stick to a published standard. No guessing
required since one could look it up ;)

                  - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California



^ permalink  raw  reply  [nested|flat] 24+ messages in thread

* Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiplesh
@ 2000-03-01 06:18  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Thomas Lockhart <lockhart@alumni.caltech.edu>
  1 sibling, 1 reply; 24+ messages in thread

From: Bruce Momjian @ 2000-03-01 06:18 UTC (permalink / raw)
  To: Thomas Lockhart <lockhart@alumni.caltech.edu>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql; PostgreSQL Development <pgsql-hackers@postgreSQL.org>

> > So are we willing to field questions from people trying to use TEMP
> > tables and trying TEMP and not TEMPORARY.  I realize the restriction on
> > a field called TEMP, but we don't get those very often.  How many people
> > are going to guess TEMP and not TEMPORARY?
> 
> Well, that's one reason to stick to a published standard. No guessing
> required since one could look it up ;)

If you survive reading the standard.  :-)

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 24+ messages in thread

* Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiplesh
@ 2000-03-01 06:29  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Thomas Lockhart <lockhart@alumni.caltech.edu>
  1 sibling, 1 reply; 24+ messages in thread

From: Tom Lane @ 2000-03-01 06:29 UTC (permalink / raw)
  To: Thomas Lockhart <lockhart@alumni.caltech.edu>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-sql; PostgreSQL Development <pgsql-hackers@postgreSQL.org>

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> So are we willing to field questions from people trying to use TEMP
>> tables and trying TEMP and not TEMPORARY.  I realize the restriction on
>> a field called TEMP, but we don't get those very often.  How many people
>> are going to guess TEMP and not TEMPORARY?

> Well, that's one reason to stick to a published standard. No guessing
> required since one could look it up ;)

As of an hour ago, neither one is a reserved word ;-)

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 24+ messages in thread

* Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiplesh
@ 2000-03-01 06:46  Thomas Lockhart <lockhart@alumni.caltech.edu>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 24+ messages in thread

From: Thomas Lockhart @ 2000-03-01 06:46 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-sql; PostgreSQL Development <pgsql-hackers@postgreSQL.org>

> As of an hour ago, neither one is a reserved word ;-)

Darn, I was just warming up to a good argument :))

                  - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California



^ permalink  raw  reply  [nested|flat] 24+ messages in thread

* Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiplesh
@ 2000-03-01 09:48  Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
  parent: Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 0 replies; 24+ messages in thread

From: Moray McConnachie @ 2000-03-01 09:48 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: pgsql-sql

----- Original Message -----
From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Thomas Lockhart <lockhart@alumni.caltech.edu>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; 'pgsql-sql@postgresql.org'
<pgsql-sql@postgreSQL.org>; PostgreSQL Development
<pgsql-hackers@postgreSQL.org>
Sent: Wednesday, March 01, 2000 6:18 AM
Subject: Re: [HACKERS] Re: [SQL] prob with aggregate and group by -
returns multiplesh


> > > So are we willing to field questions from people trying to use
TEMP
> > > tables and trying TEMP and not TEMPORARY.  I realize the
restriction on
> > > a field called TEMP, but we don't get those very often.  How
many people
> > > are going to guess TEMP and not TEMPORARY?
> >
> > Well, that's one reason to stick to a published standard. No
guessing
> > required since one could look it up ;)
>
> If you survive reading the standard.  :-)

If you can even *find* the standard :->

(is there an on-line source - all links I've ever seen 404ed...?)




^ permalink  raw  reply  [nested|flat] 24+ messages in thread


end of thread, other threads:[~2000-03-01 09:48 UTC | newest]

Thread overview: 24+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-02-28 06:22 prob with aggregate and group by - returns multiples George Dau <gedau@isa.mim.com.au>
2000-02-28 07:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-02-28 23:19   ` Peter Eisentraut <peter_e@gmx.net>
2000-02-29 05:48     ` Thomas Lockhart <lockhart@alumni.caltech.edu>
2000-02-29 05:44       ` Tom Lane <tgl@sss.pgh.pa.us>
2000-02-29 05:59         ` Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiplesh Bruce Momjian <pgman@candle.pha.pa.us>
2000-02-29 06:48           ` Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiplesh Thomas Lockhart <lockhart@alumni.caltech.edu>
2000-02-29 14:57             ` Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiplesh Tom Lane <tgl@sss.pgh.pa.us>
2000-02-29 17:19             ` Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiplesh Bruce Momjian <pgman@candle.pha.pa.us>
2000-03-01 06:14               ` Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiplesh Thomas Lockhart <lockhart@alumni.caltech.edu>
2000-03-01 06:18                 ` Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiplesh Bruce Momjian <pgman@candle.pha.pa.us>
2000-03-01 09:48                   ` Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiplesh Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
2000-03-01 06:29                 ` Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiplesh Tom Lane <tgl@sss.pgh.pa.us>
2000-03-01 06:46                   ` Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiplesh Thomas Lockhart <lockhart@alumni.caltech.edu>
2000-02-29 14:35           ` Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns  multiplesh Don Baccus <dhogaza@pacifier.com>
2000-02-29 16:09             ` Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiplesh Tom Lane <tgl@sss.pgh.pa.us>
2000-02-29 17:21               ` Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiplesh Bruce Momjian <pgman@candle.pha.pa.us>
2000-03-01 03:26               ` Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiplesh Christopher Sawtell <csawtell@xtra.co.nz>
2000-02-29 16:38             ` Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns  multiplesh Peter Eisentraut <e99re41@DoCS.UU.SE>
2000-02-29 10:54       ` Peter Eisentraut <e99re41@DoCS.UU.SE>
2000-02-29 14:35         ` Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returnsmultiples Thomas Lockhart <lockhart@alumni.caltech.edu>
2000-02-28 21:58 RE: [SQL] prob with aggregate and group by - returns multiples George Dau <gedau@isa.mim.com.au>
2000-02-28 22:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-02-29 11:59 ` Peter Eisentraut <e99re41@DoCS.UU.SE>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox