Postgres Pro
Downloads
Home   >   mailing lists

Thread: select null + 0 question

select null + 0 question

From
Jean-Christian Imbeault
Date:
Why is it that "select null + 1" gives null but "select sum(a) from
table" where there are null entries returns an integer?

Shouldn't the sum() and "+" operators behave the same?

TAL=# select null + 0;
  ?column?
----------

(1 row)

TAL=# select * from a;
  a
---


  1
(3 rows)

TAL=# select sum(a) from a;
  sum
-----
    1
(1 row)


Thanks,

Jean-Christian Imbeault

Re: select null + 0 question

From
"listrec"
Date:
The

select null + 0

is not the same as the

select sum(a) from a

statement.

Something equivalent would be

select sum(a) where a in (select null as a union select 1 as a)

In other words: As far as I understand it, sum() sums up all non null
values. In statement you have only one value, which happens to be null which
in return adds up to null. In your other statement you have one non null
value and sum returns the sum of this one value which is 1.


Detlef


-----Ursprungliche Nachricht-----
Von: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]Im Auftrag von Jean-Christian
Imbeault
Gesendet: Montag, 14. Juli 2003 07:42
An: pgsql-general@postgresql.org
Betreff: [GENERAL] select null + 0 question


Why is it that "select null + 1" gives null but "select sum(a) from
table" where there are null entries returns an integer?

Shouldn't the sum() and "+" operators behave the same?

TAL=# select null + 0;
  ?column?
----------

(1 row)

TAL=# select * from a;
  a
---


  1
(3 rows)

TAL=# select sum(a) from a;
  sum
-----
    1
(1 row)


Thanks,

Jean-Christian Imbeault


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Re: select null + 0 question

From
Joe Conway
Date:
Jean-Christian Imbeault wrote:
> Shouldn't the sum() and "+" operators behave the same?

No, see SQL99, Section 6.16, General Rules 1.b:

"Otherwise, let TX be the single-column table that is the result of
applying the <value expression> to each row of T and eliminating null
values. If one or more null values are eliminated, then a completion
condition is raised: warning — null value eliminated in set function."

I've never seen a database that emits the required warning, though.

Joe

Re: select null + 0 question

From
Mike Mascari
Date:
Jean-Christian Imbeault wrote:

> Why is it that "select null + 1" gives null but "select sum(a) from
> table" where there are null entries returns an integer?
>
> Shouldn't the sum() and "+" operators behave the same?

---

SQL92 (6.5 <set function specification>):

1) Case:

 a) If COUNT(*) is specified, then the result is the cardinality of T.

 b) Otherwise, let TX be the single-column table that is the result of
applying the <value expression> to each row of T and eliminating null
values. If one or more null values are eliminated, then a completion
condition is raised: warning-null value eliminated in set function.

---

So PostgreSQL is compliant with SQL92. Reading the above should
concern you regarding COUNT() as well:

CREATE TABLE foo (value integer);

INSERT INTO foo VALUES (NULL);
INSERT INTO foo VALUES (3);

Compare:

SELECT COUNT(*) FROM foo;
vs.
SELECT COUNT(value) FROM foo;

SQL has its problems. Of course, you could avoid this entirely by not
using NULLs :-)

Mike Mascari
mascarm@mascari.com

Re: select null + 0 question

From
Date:
> Jean-Christian Imbeault wrote:
>
>> Why is it that "select null + 1" gives null but "select sum(a) from
>> table" where there are null entries returns an integer?
>>
>> Shouldn't the sum() and "+" operators behave the same?
> SQL92 (6.5 <set function specification>):
> 1) Case:
>  a) If COUNT(*) is specified, then the result is the cardinality of T.
>  b) Otherwise, ...
> So  ...
> CREATE TABLE foo (value integer);
> INSERT INTO foo VALUES (NULL);
> INSERT INTO foo VALUES (3);
> Compare:
> SELECT COUNT(*) FROM foo;
> vs.
> SELECT COUNT(value) FROM foo;

Interesting. Thanks for pointing that out. I hadn't thought about the
fact that, e.g.,

SELECT count(*), count(last_name), count(middle_name) FROM person

would produce

430, 430, 186

~Berend Tober

Re: select null + 0 question

From
Greg Stark
Date:
Joe Conway <mail@joeconway.com> writes:

> Jean-Christian Imbeault wrote:
> > Shouldn't the sum() and "+" operators behave the same?
>
> No, see SQL99, Section 6.16, General Rules 1.b:
>
> "Otherwise, let TX be the single-column table that is the result of applying
> the <value expression> to each row of T and eliminating null values. If one or
> more null values are eliminated, then a completion condition is raised: warning
> — null value eliminated in set function."
>
> I've never seen a database that emits the required warning, though.

Oracle 8i began doing this. How it's handled is up to your driver, I know
DBD::Oracle changed several times and at least for a while treated it as an
error. I'm not sure what the current state on that is.

--
greg