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

Re: [INTERFACES] JDBC next() method

From: Herouth Maoz <herouth(at)oumail(dot)openu(dot)ac(dot)il>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-interfaces(at)postgresql(dot)org
Subject: Re: [INTERFACES] JDBC next() method
Date: 1999-04-25 17:27:55
Message-ID: l03130302b348fb9e81c6@[147.233.159.109] (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-interfaces
At 19:40 +0300 on 25/04/1999, Tom Lane wrote:


> Well, this is certainly adequate precedent for the behavior of these
> particular aggregates --- although I'd have to say that the standard-
> writers blew it for SUM; SUM of an empty set ought to return 0 not
> null.  (It looks like Postgres follows the spec, however.)

I don't agree. Suppose you want to sum of all your banking transactions in
January. There is a distinction between getting a 0, meaning you had a
balanced budget in January, and getting a null, meaning you made no
transactions in January.

> Now that I think about it, the arguments on the hackers list were not
> about the plain SELECT case but about the GROUP BY case.  For example,
> if you do
>
> 	SELECT productname, AVG(saleprice) FROM sales GROUP BY productname;
>
> then you get a row in the output for each different productname, and
> a separate instance of AVG is run over the prices for each group.
> (Unless there are NULLs in the saleprice column, none of the AVG
> instances could ever return a null result.)
>
> BUT: what happens if the sales table is empty?  There are no
> productnames, therefore no groups, therefore no rows ought to appear
> in the output (IMHO).  However, what Postgres actually does right now
> is to emit one all-nulls row (but only if an aggregate function was
> used; if you say "SELECT productname FROM sales GROUP BY productname"
> then you get no rows).  That is the behavior that we've gone 'round and
> 'round on without any resolution; it seems obviously inconsistent to me,
> but others think it's OK because it parallels what happens in the non-
> GROUP BY case.
>
> Is there anything in the SQL92 spec addressing this point?

Ooh, definitely... Here are the general rules regarding a query expression
(i.e. a general select statement). A grouped table is defined somewhere
else in the document as the result of a group by or having clause. You
really should read the definitions (I can make my draft available on the
web for a while. I don't remember where I downloaded it).

<<< Begin quotation >>>

 General Rules

 1) Case:

    a) If T is not a grouped table, then

      Case:

      i) If the <select list> contains a <set function specifica-
	 tion> that contains a reference to a column of T or di-
	 rectly contains a <set function specification> that does
	 not contain an outer reference, then T is the argument or
	 argument source of each such <set function specification>
	 and the result of the <query specification> is a table con-
	 sisting of 1 row. The i-th value of the row is the value
	 specified by the i-th <value expression>.

     ii) If the <select list> does not include a <set function spec-
	 ification> that contains a reference to T, then each <value
	 expression> is applied to each row of T yielding a table of
	 M rows, where M is the cardinality of T. The i-th column of
	 the table contains the values derived by the evaluation of
	 the i-th <value expression>.

	 Case:

	 1) If the <set quantifier> DISTINCT is not specified, then
	   the table is the result of the <query specification>.

	 2) If the <set quantifier> DISTINCT is specified, then the
	   result of the <query specification> is the table derived
	   from that table by the elimination of any redundant
	   duplicate rows.

    b) If T is a grouped table, then

      Case:

      i) If T has 0 groups, then the result of the <query specifica-
	 tion> is an empty table.

     ii) If T has one or more groups, then each <value expression>
	 is applied to each group of T yielding a table of M rows,
	 where M is the number of groups in T. The i-th column of
	 the table contains the values derived by the evaluation of
	 the i-th <value expression>. When a <value expression> is
	 applied to a given group of T, that group is the argument
	 or argument source of each <set function specification> in
	 the <value expression>.

	 Case:

	 1) If the <set quantifier> DISTINCT is not specified, then
	   the table is the result of the <query specification>.

	 2) If the <set quantifier> DISTINCT is specified, then the
	   result of the <query specification> is the table derived
	   from T by the elimination of any redundant duplicate
	   rows.


<<< End quotation >>>

Which implies exactly what you say. Perhaps you should forward this to the
Hackers list to re-start the argument - I am not a subscriber there.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma



In response to

pgsql-interfaces by date

Next:From: Mariusz CzuładaDate: 1999-04-26 07:01:17
Subject: CASE tools? (slightly off-topic)
Previous:From: Tom LaneDate: 1999-04-25 16:40:44
Subject: Re: [INTERFACES] JDBC next() method

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