Re: [HACKERS] Re: [SQL] 2 questions.

Thomas G. Lockhart wrote:
>
> > CD> I would like to...
> > CD> ... find entries that have either a null entry or a blank entry as
> > CD> a boolean result and sort by the boolean result.
> > CD> (ie: select field='' or field is null as x from table order by x;)
> > SELECT field
> > FROM table
> > WHERE field='' OR field IS NULL
> > ORDER BY field;
>
> The problem statement isn't really clear, but Colin's prototype query
> suggests that he wants all fields back, with a boolean column "true" if
> the field is zero-length or if the field is NULL.
>
> So I think his original proposal is the one he wants:
>
> SELECT (field = '' OR field IS NULL)
> FROM table
> ORDER by 1;
>
> > For hackers only. IMHO I think we need to do something for NULLs. ;)
> > What do you think about?
>
> The only open issues on NULLs afaik (I'm doing this from memory, so if
> there are other things on the ToDo don't take this as having removed
> them :) are:
>
> 1) functions taking or returning "pass by value" arguments, as is
> typical for functions returning int4 (and other 4-byte or shorter data
> types except for float4), cannot signal that the returned value should
> actually be NULL. So they must throw an error instead. That's why
> char_length() doesn't behave gracefully with nulls.
>
> 2) NULL fields don't move from front-to-back or back-to-front when
> changing the sort order from ascending to descending. I don't speak for
> the Postgres team on this, but frankly I don't see this as a big issue.
> I know that SQL92 specifies that nulls *should* switch ends of the
> returned list, but the standard also says that *which* end of the list
> they are on for, say, ascending order, is implementation-dependent. So,
> it seems like portable code really needs to handle both cases anyway...
> imho it is a place where the standard probably should have said less
> (or, depending on your preference, more) and is inadequate as-is. If you
> are getting back a mix of nulls and values, check each returned row for
> whether the value is null and you won't get in trouble...

Yes the problem with NULLS is so big that SQL92 leaves it to
implementation-dependent.

However SQL92 has some functions to help with nulls and I think we NEED
to have
these on PostgreSQL.

For example, I don't know how to compute fields containing nulls,
because the result
is always null.

AFAIK SQL92 has at least two functions that one can use to decide what
to do with nulls. (i.e.:)

o CASE WHEN ... ELSE ... END
and
o COALESCE()

other databases have similar functions, for example Oracle has the
function
NVL() to translate nulls to an arbitrary value.

I tried to write a function to emulate NVL() but it doesn't work because
nulls
have a strange behavior.

int nvl(int arg)
{
if(arg) return arg;
return 0;
}

Any ideas?

>
> - Tom -
>
> btw, Thanks again Jose' for those great SQL reference pages. Oliver and
> I have got them transcribed to sgml, and they will make a nice addition
> to the v6.4 release of the docs.

Don't mention it Tom, it was a very pleasure to me to share a bit of my
time
for this great work and I intend to co-operate again if you need help.

Jose'