Skip site navigation (1) Skip section navigation (2)

Re: [SQL] MAX() of 0 records.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Paul McGarry <paulm(at)opentec(dot)com(dot)au>, pgsql-bugs(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Re: [SQL] MAX() of 0 records.
Date: 2000-07-09 18:35:40
Message-ID: 29285.963167740@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackerspgsql-sql
I wrote:
> Comments anyone?  What exactly *should* be the behavior of an UPDATE
> that uses an aggregate function and a join to another table?  Over what
> set of tuples should the aggregate be evaluated?

Further note on this: SQL99 specifies:

         <update statement: searched> ::=
              UPDATE <target table>
                SET <set clause list>
                [ WHERE <search condition> ]

         ...

         5) A <value expression> in a <set clause> shall not directly
            contain a <set function specification>.

so the construct is definitely not SQL-compliant.  Maybe we should just
forbid it.  However, if you are joining against another table (which
itself is not an SQL feature) then it seems like there is some potential
use in it.  What do people think of my implicit-GROUP-BY-ctid idea?
That would basically say that the aggregate is computed over all the
tuples that join to a single target tuple.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2000-07-09 19:40:26
Subject: es_BaseId/cs_base_id seem to be dead code?
Previous:From: Tom LaneDate: 2000-07-09 17:57:40
Subject: Re: Bug list?

pgsql-bugs by date

Next:From: Philip WarnerDate: 2000-07-10 00:24:30
Subject: Re: [HACKERS] Re: [SQL] MAX() of 0 records.
Previous:From: Tom LaneDate: 2000-07-09 17:44:26
Subject: Re: Periodic freezing of backend processes

pgsql-sql by date

Next:From: Philip WarnerDate: 2000-07-10 00:24:30
Subject: Re: [HACKERS] Re: [SQL] MAX() of 0 records.
Previous:From: Jan WieckDate: 2000-07-08 11:29:17
Subject: Re: Re: [SQL] Re: [GENERAL] lztext and compression ratios....

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group