RE: [SQL] Oddities with NULL and GROUP BY

"Jackson, DeJuan" wrote:

The behavior is valid, if you define NULL as meaning undefined.
In other words when you define something as NULL you're saying, "I don't
know what it is. It could be equal or not."
-DEJ

-----Original Message-----
From: secret [SMTP:secret@kearneydev.com]
Sent: Friday, May 14, 1999 11:58 AM
To: PG-SQL
Subject: [SQL] Oddities with NULL and GROUP BY

Maybe there is something I don't know about how GROUP BY should
work, but if I have a table like:
a,b,c
1,1,1
1,1,2
1,1,3
1,2,1
1,3,1

And I say SELECT a,b,sum(c) FROm .. GROUP BY a,b I get
1,1,6
1,2,1
1,3,1

So whenever a or b changes we get a new summed row, well if I have rows
where a or b are null, this doesn't happen, infact I seem to get all
those rows individually... Like if:
1,1,1
1,1,3
1,NULL,10
1,NULL,20
1,2,3

I get:
1,1,4
1,NULL,10
1,NULL,20
1,2,3

Shouldn't I get 1,NULL,30? Ie shouldn't NULL be treated like any other
value? Or is there some bit of information I'm missing? I can set
everything from NULL to 0 if need be, but I'd rather not...

David Secret
MIS Director
Kearney Development Co., Inc.

IBM's DB/2 Disagrees, so does Oracle8!

Here is a cut & paste from Oracle SQL+:

SQL> select * from z;

A B
--------- ---------
1 1
1 2
5
10

SQL> select a,sum(b) from z group by a;

A SUM(B)
--------- ---------
1 3
15

SQL>

I'm going to report this as a bug now that I've verified 2 major database
vendors perform the task as I would expect them to, and PostgreSQL does it
very differently. The question is really is NULL=NULL, which I would say it
should be.

"Jackson, DeJuan" wrote:

The behavior is valid, if you define NULL as meaning undefined.
In other words when you define something as NULL you're saying, "I don't
know what it is. It could be equal or not."
-DEJ

-----Original Message-----
From: secret [SMTP:secret@kearneydev.com]
Sent: Friday, May 14, 1999 11:58 AM
To: PG-SQL
Subject: [SQL] Oddities with NULL and GROUP BY

Maybe there is something I don't know about how GROUP BY should
work, but if I have a table like:
a,b,c
1,1,1
1,1,2
1,1,3
1,2,1
1,3,1

And I say SELECT a,b,sum(c) FROm .. GROUP BY a,b I get
1,1,6
1,2,1
1,3,1

So whenever a or b changes we get a new summed row, well if I have rows
where a or b are null, this doesn't happen, infact I seem to get all
those rows individually... Like if:
1,1,1
1,1,3
1,NULL,10
1,NULL,20
1,2,3

I get:
1,1,4
1,NULL,10
1,NULL,20
1,2,3

Shouldn't I get 1,NULL,30? Ie shouldn't NULL be treated like any other
value? Or is there some bit of information I'm missing? I can set
everything from NULL to 0 if need be, but I'd rather not...

David Secret
MIS Director
Kearney Development Co., Inc.

Oh, I just observed this oddity... PostgreSQL groups just fine when there
is a table of 2 fields a int4, b int4...

SELECT a,sum(b) FROM z GROUP BY a Groups NULLs fine
SELECT a,b,sum(c) FROM z GROUP BY a,b Error in grouping NULLs in b...

secret ha scritto:

"Jackson, DeJuan" wrote:

The behavior is valid, if you define NULL as meaning undefined.
In other words when you define something as NULL you're saying, "I don't
know what it is. It could be equal or not."
-DEJ

-----Original Message-----
From: secret [SMTP:secret@kearneydev.com]
Sent: Friday, May 14, 1999 11:58 AM
To: PG-SQL
Subject: [SQL] Oddities with NULL and GROUP BY

Maybe there is something I don't know about how GROUP BY should
work, but if I have a table like:
a,b,c
1,1,1
1,1,2
1,1,3
1,2,1
1,3,1

And I say SELECT a,b,sum(c) FROm .. GROUP BY a,b I get
1,1,6
1,2,1
1,3,1

So whenever a or b changes we get a new summed row, well if I have rows
where a or b are null, this doesn't happen, infact I seem to get all
those rows individually... Like if:
1,1,1
1,1,3
1,NULL,10
1,NULL,20
1,2,3

I get:
1,1,4
1,NULL,10
1,NULL,20
1,2,3

Shouldn't I get 1,NULL,30? Ie shouldn't NULL be treated like any other
value? Or is there some bit of information I'm missing? I can set
everything from NULL to 0 if need be, but I'd rather not...

David Secret
MIS Director
Kearney Development Co., Inc.

IBM's DB/2 Disagrees, so does Oracle8!

Here is a cut & paste from Oracle SQL+:

SQL> select * from z;

A B
--------- ---------
1 1
1 2
5
10

SQL> select a,sum(b) from z group by a;

A SUM(B)
--------- ---------
1 3
15

SQL>

I'm going to report this as a bug now that I've verified 2 major database
vendors perform the task as I would expect them to, and PostgreSQL does it
very differently. The question is really is NULL=NULL, which I would say it
should be.

I tried it in PostgreSQL 6.5beta1 with the same result:

select * from z;
a| b
-+--
1| 1
1| 2
| 5
|10
(4 rows)

select a,sum(b) from z group by a;
a|sum
-+---
1| 3
| 15
(2 rows)

The Pratical SQL Handbook at page 171 says:
Since nulls represent "the great unknown", there is no way to know
whether one null is equal to any other null. Each unknown value
may or may not be different from another.
However, if the grouping column contains more than one null,
all of them are put into a single group.

Thus: NULL!=NULL but on GROUP BY it is considered as NULL=NULL.

Jos�

--
______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'

Jos� Soares wrote:

secret ha scritto:

"Jackson, DeJuan" wrote:

The behavior is valid, if you define NULL as meaning undefined.
In other words when you define something as NULL you're saying, "I

don't

know what it is. It could be equal or not."
-DEJ

-----Original Message-----
From: secret [SMTP:secret@kearneydev.com]
Sent: Friday, May 14, 1999 11:58 AM
To: PG-SQL
Subject: [SQL] Oddities with NULL and GROUP BY

Maybe there is something I don't know about how GROUP BY

should

work, but if I have a table like:
a,b,c
1,1,1
1,1,2
1,1,3
1,2,1
1,3,1

And I say SELECT a,b,sum(c) FROm .. GROUP BY a,b I get
1,1,6
1,2,1
1,3,1

So whenever a or b changes we get a new summed row, well if I

have rows

where a or b are null, this doesn't happen, infact I seem to get

all

those rows individually... Like if:
1,1,1
1,1,3
1,NULL,10
1,NULL,20
1,2,3

I get:
1,1,4
1,NULL,10
1,NULL,20
1,2,3

Shouldn't I get 1,NULL,30? Ie shouldn't NULL be treated like

any other

value? Or is there some bit of information I'm missing? I can

set

everything from NULL to 0 if need be, but I'd rather not...

David Secret
MIS Director
Kearney Development Co., Inc.

IBM's DB/2 Disagrees, so does Oracle8!

Here is a cut & paste from Oracle SQL+:

SQL> select * from z;

A B
--------- ---------
1 1
1 2
5
10

SQL> select a,sum(b) from z group by a;

A SUM(B)
--------- ---------
1 3
15

SQL>

I'm going to report this as a bug now that I've verified 2 major
database
vendors perform the task as I would expect them to, and PostgreSQL
does it
very differently. The question is really is NULL=NULL, which I
would say it
should be.

I tried it in PostgreSQL 6.5beta1 with the same result:

select * from z;
a| b
-+--
1| 1
1| 2
| 5
|10
(4 rows)

select a,sum(b) from z group by a;
a|sum
-+---
1| 3
| 15
(2 rows)

The Pratical SQL Handbook at page 171 says:
Since nulls represent "the great unknown", there is no way to know
whether one null is equal to any other null. Each unknown value
may or may not be different from another.
However, if the grouping column contains more than one null,
all of them are put into a single group.

Thus: NULL!=NULL but on GROUP BY it is considered as NULL=NULL.

Jos�

--
______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'

Wonderful, that's as I expected. However please try this in 6.5
Beta1,
CREATE TABLE z(a int4,b int4, c int4);
INSERT INTO z VALUES (1,1,1);
INSERT INTO z VALUES (1,1,2);
INSERT INTO z(a,c) VALUES (2,1);
INSERT INTO z(a,c) VALUES (2,2);

SELECT a,b,sum(c) FROM z GROUP BY a,b

GROUPing in PostgreSQL w/NULLs works just fine when there is only 1
column, however when one throws 2 in, the 2nd one having NULLs it starts
failing. Your example demonstrates the right answer for 1 group by
column, try it with 2 and I expect 6.5beta1 will fail as 6.4.2 does.

As to NULL=NULL or NULL!=NULL, evadentally my estimation of why the
problem is occuring was wrong. :) But from the SQL handbook we
definately have a bug here.

David Secret
MIS Director
Kearney Development Co., Inc.

At 18:28 +0300 on 17/05/1999, Jos� Soares wrote:

The Pratical SQL Handbook at page 171 says:
Since nulls represent "the great unknown", there is no way to know
whether one null is equal to any other null. Each unknown value
may or may not be different from another.
However, if the grouping column contains more than one null,
all of them are put into a single group.

Thus: NULL!=NULL but on GROUP BY it is considered as NULL=NULL.

This is something I have complained about time and again. It is time
something is changed about it, otherwise Postgres will NEVER be a
standard-compliant RDBMS.

The SQL92 text says:

A null value is an implementation-dependent special value that
is distinct from all non-null values of the associated data type.
There is effectively only one null value and that value is a member
of every SQL data type. There is no <literal> for a null value,
although the keyword NULL is used in some places to indicate that a
null value is desired.

Thus, by rights, NULL=NULL should be true, because there is only one null
value.

About the <group by clause>, the text says:

1) The result of the <group by clause> is a partitioning of T into
a set of groups. The set is the minimum number of groups such
that, for each grouping column of each group of more than one
row, no two values of that grouping column are distinct.

And the treatment of nulls is implied from the definition of distinctness:

h) distinct: Two values are said to be not distinct if either:
both are the null value, or they compare equal according to
Subclause 8.2, "<comparison predicate>". Otherwise they are
distinct. Two rows (or partial rows) are distinct if at least
one of their pairs of respective values is distinct. Otherwise
they are not distinct. The result of evaluating whether or not
two values or two rows are distinct is never unknown.

About uniqueness, it says:

A unique constraint is satisfied if and only if no two rows in
a table have the same non-null values in the unique columns. In
addition, if the unique constraint was defined with PRIMARY KEY,
then it requires that none of the values in the specified column or
columns be the null value.

One should note, however, that when the actual comparison operator "=" is
used, the standard says that if one of the operands is null, the result of
the comparison is unknown. One should make a distinction between making
comparisons within group by, uniqueness, and other database-logic
operations, and between making the actual comparison (though in my opinion,
this should not be so. Comparing a null value to something should be always
false unless the other something is also null. But that's my opinion and
not the standard's).

Herouth

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

Here the result:

SELECT a,b,sum(c) FROM z GROUP BY a,b;
a|b|sum
-+-+---
1|1| 3
2| | 3
(2 rows)

secret ha scritto:

Jos� Soares wrote:

secret ha scritto:

"Jackson, DeJuan" wrote:

The behavior is valid, if you define NULL as meaning undefined.
In other words when you define something as NULL you're saying, "I

don't

know what it is. It could be equal or not."
-DEJ

-----Original Message-----
From: secret [SMTP:secret@kearneydev.com]
Sent: Friday, May 14, 1999 11:58 AM
To: PG-SQL
Subject: [SQL] Oddities with NULL and GROUP BY

Maybe there is something I don't know about how GROUP BY

should

work, but if I have a table like:
a,b,c
1,1,1
1,1,2
1,1,3
1,2,1
1,3,1

And I say SELECT a,b,sum(c) FROm .. GROUP BY a,b I get
1,1,6
1,2,1
1,3,1

So whenever a or b changes we get a new summed row, well if I

have rows

where a or b are null, this doesn't happen, infact I seem to get

all

those rows individually... Like if:
1,1,1
1,1,3
1,NULL,10
1,NULL,20
1,2,3

I get:
1,1,4
1,NULL,10
1,NULL,20
1,2,3

Shouldn't I get 1,NULL,30? Ie shouldn't NULL be treated like

any other

value? Or is there some bit of information I'm missing? I can

set

everything from NULL to 0 if need be, but I'd rather not...

David Secret
MIS Director
Kearney Development Co., Inc.

IBM's DB/2 Disagrees, so does Oracle8!

Here is a cut & paste from Oracle SQL+:

SQL> select * from z;

A B
--------- ---------
1 1
1 2
5
10

SQL> select a,sum(b) from z group by a;

A SUM(B)
--------- ---------
1 3
15

SQL>

I'm going to report this as a bug now that I've verified 2 major
database
vendors perform the task as I would expect them to, and PostgreSQL
does it
very differently. The question is really is NULL=NULL, which I
would say it
should be.

I tried it in PostgreSQL 6.5beta1 with the same result:

select * from z;
a| b
-+--
1| 1
1| 2
| 5
|10
(4 rows)

select a,sum(b) from z group by a;
a|sum
-+---
1| 3
| 15
(2 rows)

The Pratical SQL Handbook at page 171 says:
Since nulls represent "the great unknown", there is no way to know
whether one null is equal to any other null. Each unknown value
may or may not be different from another.
However, if the grouping column contains more than one null,
all of them are put into a single group.

Thus: NULL!=NULL but on GROUP BY it is considered as NULL=NULL.

Jos�

--
______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'

Wonderful, that's as I expected. However please try this in 6.5
Beta1,
CREATE TABLE z(a int4,b int4, c int4);
INSERT INTO z VALUES (1,1,1);
INSERT INTO z VALUES (1,1,2);
INSERT INTO z(a,c) VALUES (2,1);
INSERT INTO z(a,c) VALUES (2,2);

SELECT a,b,sum(c) FROM z GROUP BY a,b

GROUPing in PostgreSQL w/NULLs works just fine when there is only 1
column, however when one throws 2 in, the 2nd one having NULLs it starts
failing. Your example demonstrates the right answer for 1 group by
column, try it with 2 and I expect 6.5beta1 will fail as 6.4.2 does.

As to NULL=NULL or NULL!=NULL, evadentally my estimation of why the
problem is occuring was wrong. :) But from the SQL handbook we
definately have a bug here.

David Secret
MIS Director
Kearney Development Co., Inc.

______________________________________________________________

PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'

Herouth Maoz <herouth@oumail.openu.ac.il> writes:

Thus, by rights, NULL=NULL should be true, because there is only one null
value.

You are jumping to a conclusion not supported by the text you have
quoted.

It does appear that GROUP BY and DISTINCT should treat all nulls as
falling into the same class, because of

h) distinct: Two values are said to be not distinct if either:
both are the null value, or they compare equal according to
Subclause 8.2, "<comparison predicate>".

Kindly note, however, that the standards authors felt it necessary to
describe those two cases as separate cases. If nulls compare as equal,
there would be no need to write more than "Two values are not distinct
if they compare equal".

One should note, however, that when the actual comparison operator "=" is
used, the standard says that if one of the operands is null, the result of
the comparison is unknown.

Precisely. A fortiori, if both operands are null, the result of the
comparison is still unknown.

We do seem to have a bug in GROUP BY/DISTINCT if nulls are producing
more than one output tuple in those operations. But that has nothing
to do with what the comparison operator produces.

regards, tom lane

The Pratical SQL Handbook at page 171 says:
Since nulls represent "the great unknown", there is no way to know
whether one null is equal to any other null. Each unknown value
may or may not be different from another.

Although I've noticed some questionable statements quoted from this
book, this looks good...

Thus: NULL!=NULL but on GROUP BY it is considered as NULL=NULL.

This is something I have complained about time and again. It is time
something is changed about it, otherwise Postgres will NEVER be a
standard-compliant RDBMS.

Postgres conforms to SQL92 in this regard. Date and Darwen, "A Guide
to the SQL Standard", 3rd ed., are explicit about this near the top of
page 249:

Duplicates are relevant to the ... GROUP BY ... operations ...
... GROUP BY groups rows together on the basis of duplicate values in
the set of grouping columns (and those sets of grouping column values
can be regarded as "rows" for present purposes). The point is,
however, the definition of duplicate rows requires some refinement in
the presence of nulls. Let "left" and "right" be as defined
(previously). Then "left" and "right" are defined to be "duplicates"
of one another if and only if, for all "i" in the range 1 to "n",
either "left_i" = "right_i" is TRUE, or "left_i" and "right_i" are
both null.

There is a single exception to Postgres' SQL92 conformance wrt NULLs
afaik, involving DISTINCT column constraints which I discuss below.

However, if the grouping column contains more than one null,
all of them are put into a single group.
Thus: NULL!=NULL but on GROUP BY it is considered as NULL=NULL.

The SQL92 text says:
A null value is an implementation-dependent special value that
is distinct from all non-null values of the associated data type.
There is effectively only one null value and that value is a member
of every SQL data type. There is no <literal> for a null value,
although the keyword NULL is used in some places to indicate that a
null value is desired.
Thus, by rights, NULL=NULL should be true, because there is only one null
value.

No! An explicit "unknown" = "unknown" in a constraint clause should
always evaluate to FALSE (we'll get to GROUP BY later). SQL92 and all
of my reference books are clear about this. Date and Darwen have a
good discussion of the shortcomings of NULL in SQL92, pointing out
that with NULL handling one would really like a distinct UNKNOWN added
to the possible boolean values TRUE and FALSE so that SQL would have
true three-value logic.

About the <group by clause>, the text says:
1) The result of the <group by clause> is a partitioning of T into
a set of groups. The set is the minimum number of groups such
that, for each grouping column of each group of more than one
row, no two values of that grouping column are distinct.

Interesting. Note that SQL92 asks that any column with the DISTINCT
constraint contain *only one* NULL value in the entire column. Date
and Darwen point out that this is inconsistant with the fundamental
notion of "unknown" and renders DISTINCT constraints without NOT NULL
to be effectively useless. They recommend against having any DISTINCT
column without having an additional NOT NULL constraint. We've had
this discussion wrt Postgres, and concluded that we would diverge from
the standard by allowing multiple NULL fields in DISTINCT columns, to
make DISTINCT a useful feature with NULLs. It probably didn't hurt
that Postgres already behaved this way :)

afaik this last point is the *only* place where Postgres intentionally
diverges from SQL92, and it was done (or rather retained from existing
behavior) to make a useless feature useful.

One should note, however, that when the actual comparison operator "=" is
used, the standard says that if one of the operands is null, the result of
the comparison is unknown. One should make a distinction between making
comparisons within group by, uniqueness, and other database-logic
operations, and between making the actual comparison (though in my opinion,
this should not be so. Comparing a null value to something should be always
false unless the other something is also null. But that's my opinion and
not the standard's).

One can't take a portion of SQL92 statements wrt NULLs and apply it to
all uses of NULL, because SQL92 is not internally consistant in this
regard.

In most GROUP BY situations, a corresponding WHERE col IS NOT NULL is
probably a good idea.

Regards.

- Thomas

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

Thomas Lockhart wrote:

The Pratical SQL Handbook at page 171 says:
Since nulls represent "the great unknown", there is no way to know
whether one null is equal to any other null. Each unknown value
may or may not be different from another.

Although I've noticed some questionable statements quoted from this
book, this looks good...

Thus: NULL!=NULL but on GROUP BY it is considered as NULL=NULL.

This is something I have complained about time and again. It is time
something is changed about it, otherwise Postgres will NEVER be a
standard-compliant RDBMS.

Postgres conforms to SQL92 in this regard. Date and Darwen, "A Guide
to the SQL Standard", 3rd ed., are explicit about this near the top of
page 249:

Duplicates are relevant to the ... GROUP BY ... operations ...
... GROUP BY groups rows together on the basis of duplicate values in
the set of grouping columns (and those sets of grouping column values
can be regarded as "rows" for present purposes). The point is,
however, the definition of duplicate rows requires some refinement in
the presence of nulls. Let "left" and "right" be as defined
(previously). Then "left" and "right" are defined to be "duplicates"
of one another if and only if, for all "i" in the range 1 to "n",
either "left_i" = "right_i" is TRUE, or "left_i" and "right_i" are
both null.

There is a single exception to Postgres' SQL92 conformance wrt NULLs
afaik, involving DISTINCT column constraints which I discuss below.

However, if the grouping column contains more than one null,
all of them are put into a single group.
Thus: NULL!=NULL but on GROUP BY it is considered as NULL=NULL.

The SQL92 text says:
A null value is an implementation-dependent special value that
is distinct from all non-null values of the associated data type.
There is effectively only one null value and that value is a member
of every SQL data type. There is no <literal> for a null value,
although the keyword NULL is used in some places to indicate that a
null value is desired.
Thus, by rights, NULL=NULL should be true, because there is only one null
value.

No! An explicit "unknown" = "unknown" in a constraint clause should
always evaluate to FALSE (we'll get to GROUP BY later). SQL92 and all
of my reference books are clear about this. Date and Darwen have a
good discussion of the shortcomings of NULL in SQL92, pointing out
that with NULL handling one would really like a distinct UNKNOWN added
to the possible boolean values TRUE and FALSE so that SQL would have
true three-value logic.

About the <group by clause>, the text says:
1) The result of the <group by clause> is a partitioning of T into
a set of groups. The set is the minimum number of groups such
that, for each grouping column of each group of more than one
row, no two values of that grouping column are distinct.

Interesting. Note that SQL92 asks that any column with the DISTINCT
constraint contain *only one* NULL value in the entire column. Date
and Darwen point out that this is inconsistant with the fundamental
notion of "unknown" and renders DISTINCT constraints without NOT NULL
to be effectively useless. They recommend against having any DISTINCT
column without having an additional NOT NULL constraint. We've had
this discussion wrt Postgres, and concluded that we would diverge from
the standard by allowing multiple NULL fields in DISTINCT columns, to
make DISTINCT a useful feature with NULLs. It probably didn't hurt
that Postgres already behaved this way :)

afaik this last point is the *only* place where Postgres intentionally
diverges from SQL92, and it was done (or rather retained from existing
behavior) to make a useless feature useful.

One should note, however, that when the actual comparison operator "=" is
used, the standard says that if one of the operands is null, the result of
the comparison is unknown. One should make a distinction between making
comparisons within group by, uniqueness, and other database-logic
operations, and between making the actual comparison (though in my opinion,
this should not be so. Comparing a null value to something should be always
false unless the other something is also null. But that's my opinion and
not the standard's).

One can't take a portion of SQL92 statements wrt NULLs and apply it to
all uses of NULL, because SQL92 is not internally consistant in this
regard.

In most GROUP BY situations, a corresponding WHERE col IS NOT NULL is
probably a good idea.

Regards.

- Thomas

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

Sigh. PostgreSQL seems pretty inconsitant in this... GROUP BY with 1 column
produces NULLs grouped, with 2 colums it usually seems not to(although I somehow
came up with an example where it did, grr... but lets ignore this since it's
supposed to "not work" that way.)... Oracle8, DB/2, and Sybase all group NULLs
together, for compatibility sake wouldn't it be reasonable for PostgreSQL to do
the same? Else porting applications could fail miserably when one hits this
inconsistency.

--David

At 18:16 +0300 on 19/05/1999, Thomas Lockhart wrote:

Interesting. Note that SQL92 asks that any column with the DISTINCT
constraint contain *only one* NULL value in the entire column. Date
and Darwen point out that this is inconsistant with the fundamental
notion of "unknown" and renders DISTINCT constraints without NOT NULL
to be effectively useless. They recommend against having any DISTINCT
column without having an additional NOT NULL constraint. We've had
this discussion wrt Postgres, and concluded that we would diverge from
the standard by allowing multiple NULL fields in DISTINCT columns, to
make DISTINCT a useful feature with NULLs. It probably didn't hurt
that Postgres already behaved this way :)

afaik this last point is the *only* place where Postgres intentionally
diverges from SQL92, and it was done (or rather retained from existing
behavior) to make a useless feature useful.

You are probably referring to UNIQUE, not DISTINCT, which is not a
constraint but a query qualifier.

As for uniqueness, as I already quoted, it says:

A unique constraint is satisfied if and only if no two rows in
a table have the same non-null values in the unique columns. In
addition, if the unique constraint was defined with PRIMARY KEY,
then it requires that none of the values in the specified column or
columns be the null value.

Which means that what Postgres does is quite the correct thing. You see?
"No two rows in a table have the same non-null values in the unique
columns". They *can* have the same *null* values!. The constraints only
talks about the non-null ones!

So I think Date and Darwen misinterpreted the rule, and you got this part
right in PostgreSQL. However, there *is* a bug in the GROUP BY behaviour,
at least over one column, and it should be checked if it doesn't work
according to the old convention of comparing nulls internally as they are
compared with the "=" operator.

Herouth

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

At 18:28 +0300 on 19/05/1999, secret wrote:

Sigh. PostgreSQL seems pretty inconsitant in this... GROUP BY with 1
column
produces NULLs grouped, with 2 colums it usually seems not to(although I
somehow
came up with an example where it did, grr... but lets ignore this since it's
supposed to "not work" that way.)... Oracle8, DB/2, and Sybase all group
NULLs
together, for compatibility sake wouldn't it be reasonable for PostgreSQL
to do
the same? Else porting applications could fail miserably when one hits this
inconsistency.

Please, please, the standard is clear about each of these things
separately. It absolutely says that nulls should be grouped together, and
it absolutely says that the comparison operator should not. It's true that
these things are not consistent, but for each operation, the standard is
quite clear on how it should be done.

In my opinion, there should be null comparison for internal operations, and
null comparison for the comparison operator. For this purpose, what
Postgres does now - return a NULL boolean if one of its operands is null -
is consistent with the standard. For GROUP BY and ORDER BY, they should be
compared equal, and for UNIQUE, they should not be compared.

UNIQUE has explicit mention of nulls in the standard.
ORDER BY has explicit mention of nulls in the standard.
GROUP BY has implicit mention of nulls, by using the term "distinct" which
is defined earlier and includes and explicit mention of nulls.
"=" has explicit mention of nulls in the standard.

And although they are not consistent (some are equal, some are not equal,
and some are unknown), they are covered in no uncertain terms.

Herouth

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

Looks like this is fixed in 6.5 too.

a|b|sum
-+-+---
1|1| 3
2| | 3
(2 rows)

[Charset iso-8859-1 unsupported, filtering to ASCII...]

Jos_ Soares wrote:

secret ha scritto:

"Jackson, DeJuan" wrote:

The behavior is valid, if you define NULL as meaning undefined.
In other words when you define something as NULL you're saying, "I

don't

know what it is. It could be equal or not."
-DEJ

-----Original Message-----
From: secret [SMTP:secret@kearneydev.com]
Sent: Friday, May 14, 1999 11:58 AM
To: PG-SQL
Subject: [SQL] Oddities with NULL and GROUP BY

Maybe there is something I don't know about how GROUP BY

should

work, but if I have a table like:
a,b,c
1,1,1
1,1,2
1,1,3
1,2,1
1,3,1

And I say SELECT a,b,sum(c) FROm .. GROUP BY a,b I get
1,1,6
1,2,1
1,3,1

So whenever a or b changes we get a new summed row, well if I

have rows

where a or b are null, this doesn't happen, infact I seem to get

all

those rows individually... Like if:
1,1,1
1,1,3
1,NULL,10
1,NULL,20
1,2,3

I get:
1,1,4
1,NULL,10
1,NULL,20
1,2,3

Shouldn't I get 1,NULL,30? Ie shouldn't NULL be treated like

any other

value? Or is there some bit of information I'm missing? I can

set

everything from NULL to 0 if need be, but I'd rather not...

David Secret
MIS Director
Kearney Development Co., Inc.

IBM's DB/2 Disagrees, so does Oracle8!

Here is a cut & paste from Oracle SQL+:

SQL> select * from z;

A B
--------- ---------
1 1
1 2
5
10

SQL> select a,sum(b) from z group by a;

A SUM(B)
--------- ---------
1 3
15

SQL>

I'm going to report this as a bug now that I've verified 2 major
database
vendors perform the task as I would expect them to, and PostgreSQL
does it
very differently. The question is really is NULL=NULL, which I
would say it
should be.

I tried it in PostgreSQL 6.5beta1 with the same result:

select * from z;
a| b
-+--
1| 1
1| 2
| 5
|10
(4 rows)

select a,sum(b) from z group by a;
a|sum
-+---
1| 3
| 15
(2 rows)

The Pratical SQL Handbook at page 171 says:
Since nulls represent "the great unknown", there is no way to know
whether one null is equal to any other null. Each unknown value
may or may not be different from another.
However, if the grouping column contains more than one null,
all of them are put into a single group.

Thus: NULL!=NULL but on GROUP BY it is considered as NULL=NULL.

Jos_

--
______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'

Wonderful, that's as I expected. However please try this in 6.5
Beta1,
CREATE TABLE z(a int4,b int4, c int4);
INSERT INTO z VALUES (1,1,1);
INSERT INTO z VALUES (1,1,2);
INSERT INTO z(a,c) VALUES (2,1);
INSERT INTO z(a,c) VALUES (2,2);

SELECT a,b,sum(c) FROM z GROUP BY a,b

GROUPing in PostgreSQL w/NULLs works just fine when there is only 1
column, however when one throws 2 in, the 2nd one having NULLs it starts
failing. Your example demonstrates the right answer for 1 group by
column, try it with 2 and I expect 6.5beta1 will fail as 6.4.2 does.

As to NULL=NULL or NULL!=NULL, evadentally my estimation of why the
problem is occuring was wrong. :) But from the SQL handbook we
definately have a bug here.

David Secret
MIS Director
Kearney Development Co., Inc.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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