Thread: handling of COUNT(record) vs IS NULL
Hi, I've just noticed that the handling of COUNT(record) and (record IS NULL) aren't consistent with my understanding of them. If I run the following query: SELECT NULL IS NULL, COUNT( NULL ), (NULL,NULL) IS NULL, COUNT((NULL,NULL)); The IS NULL checks both return TRUE as I'd expect them to, but the second count doesn't return 0. The Comparison Operator docs[1] describe the behaviour of IS NULL changing, with respect to records, in version 8.2. Is count still exhibiting the old behaviour? Sam [1] http://www.postgresql.org/docs/8.2/static/functions-comparison.html
Sam Mason <sam@samason.me.uk> writes: > I've just noticed that the handling of COUNT(record) and (record IS > NULL) aren't consistent with my understanding of them. If I run the > following query: > SELECT > NULL IS NULL, COUNT( NULL ), > (NULL,NULL) IS NULL, COUNT((NULL,NULL)); > The IS NULL checks both return TRUE as I'd expect them to, but the > second count doesn't return 0. THe fourth of those isn't really valid SQL. According to SQL99, IS NULL takes a <row value expression> as argument, so it's valid to do (NULL,NULL) IS NULL, but COUNT takes a <value expression>. I don't see anything in the spec suggesting that we are supposed to drill down into a rowtype value to see whether all its fields are null, in any context other than the IS [NOT] NULL predicate. regards, tom lane
"Tom Lane" <tgl@sss.pgh.pa.us> writes: > Sam Mason <sam@samason.me.uk> writes: >> I've just noticed that the handling of COUNT(record) and (record IS >> NULL) aren't consistent with my understanding of them. If I run the >> following query: > >> SELECT >> NULL IS NULL, COUNT( NULL ), >> (NULL,NULL) IS NULL, COUNT((NULL,NULL)); > >> The IS NULL checks both return TRUE as I'd expect them to, but the >> second count doesn't return 0. > > THe fourth of those isn't really valid SQL. According to SQL99, > IS NULL takes a <row value expression> as argument, so it's valid > to do (NULL,NULL) IS NULL, but COUNT takes a <value expression>. > > I don't see anything in the spec suggesting that we are supposed > to drill down into a rowtype value to see whether all its fields > are null, in any context other than the IS [NOT] NULL predicate. Well it's not just in the predicate, we handle it for other strict operators and functions: postgres=# select (ROW(null,null)=row(1,2)) IS NULL; ?column? ---------- t (1 row) It does seem a bit inconsistent: postgres=# select count(ROW(null,null)=row(1,2)); count ------- 0 (1 row) postgres=# select count(ROW(null,null)); count ------- 1 (1 row) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!
On Mon, Jan 28, 2008 at 04:38:01PM -0500, Tom Lane wrote: > Sam Mason <sam@samason.me.uk> writes: > > I've just noticed that the handling of COUNT(record) and (record IS > > NULL) aren't consistent with my understanding of them. If I run the > > following query: > > > SELECT > > NULL IS NULL, COUNT( NULL ), > > (NULL,NULL) IS NULL, COUNT((NULL,NULL)); > > > The IS NULL checks both return TRUE as I'd expect them to, but the > > second count doesn't return 0. > > THe fourth of those isn't really valid SQL. According to SQL99, > IS NULL takes a <row value expression> as argument, so it's valid > to do (NULL,NULL) IS NULL, but COUNT takes a <value expression>. But isn't COUNT(*), logically from a syntactic point of view, working with records? Or is it really supposed to be a special hack. My intuition of the original intent of COUNT(*) is that all tables have at least one non-null column, so it's safe to rewrite COUNT(*) into COUNT(1). In general this doesn't seem correct to me though. > I don't see anything in the spec suggesting that we are supposed > to drill down into a rowtype value to see whether all its fields > are null, in any context other than the IS [NOT] NULL predicate. I believe that somewhere in the spec COUNT is defined to return the count of non-null rows. If so, then the behaviour of COUNT isn't consistent with IS NULL and if the spec only defines the behaviour for non-record values then you can't look to it for guidance. Wouldn't it be better to either ban counts of records or make it follow the same semantics as the IS NULL predicate. Sam