On Wed, Aug 12, 2009 at 04:14:31PM +0200, Daniel Verite wrote: > But IS NULL applied to an array is useless to test if there are null values > inside, whereas this is apparently the whole point of IS NULL applied to > rows. > I mean: > select a is null from (select array[null]) x(a); > returns false, as well as: > select a is null from (select array[1]) x(a); Yes, I know. But it seems to be a somewhat arbitrary choice to handle IS NULL for rows differently from everything else. > When applied to rows, if you consider that: > - is null applied to a row means that all columns are null > - is not null applied to a row means that all columns are not null > which is what the standard seems to dictate, then these operators make sense > and are probably useful in some situations. Yes, I understand what it's specified to do and that it's consistent with SQL spec. I just think (and Merlin seems to agree) that the spec has specified the "wrong" behavior. > Now there is the unfortunate consequence that (r is null) is not equivalent > to (not (r is not null)), yet it's not the standard's fault if "not all > values are null" is not the same as "all values are not null", that's just > set logic. Yes; but this means the user now has to be aware of exactly which type their code is using as the behavior of various things will magically change in rare circumstances. > Maybe they could have made this easier for us by naming the operators > differently, such as "is entirely null" and "is entirely not null" Yes, this would be *much* more preferable. For people aware of it this it's obviously an easy translation to make, but it's a nasty waiting for those who aren't and especially for anybody doing anything formal. I.e. when reasoning about operator semantics you suddenly have to know the type of data you're dealing with before you can say useful things about the result. There will of course be ways of avoiding the general case of an exponential increase in complexity, but it's still nasty. Anybody else think this thread is past it's bed time and should be put to rest? -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
