Обсуждение: Need efficient way to do comparison with NULL as an option

All,

I'm looking for an operator that will compare NULL with NULL and
evaluate as TRUE.

I have a BIGINT column which might contain NULL values.  I want to pass
a value to compare with that column in my WHERE clause.  If the value
I'm comparing is 0, I want it to match the NULL values.  Here is a
sample query that I currently use:

   SELECT *
   FROM mytable
   WHERE (col IS NULL AND NULLIF(?, 0) IS NULL) OR col = ?;

The '?' placeholders used in the query will receive the same value which
might be any one of the following: NULL, 0, 1, 2, 3, etc.

What I'd really like is an operator that will compare NULL with NULL and
evaluate as TRUE.  Does that exist?

I tried solving this myself, so I have a stored proc like this:

--------------------
CREATE OR REPLACE FUNCTION "public"."is_equal_bigint" (in_val1 bigint,
in_val2 bigint) RETURNS boolean AS
$body$
BEGIN
     -- both values are null
     IF in_val1 IS NULL AND in_val2 IS NULL THEN
        RETURN TRUE;
     END IF;

     -- values are the same
     IF in_val1 = in_val2 THEN
        RETURN TRUE;
     END IF;

     -- values are different
     RETURN FALSE;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;
--------------------

I can use this function as follows:

   SELECT *
   FROM mytable
   WHERE is_equal_bigint(col, NULLIF(?, 0)) IS TRUE;

But I worry that this will not allow my query to use any indexes on
'col' and will make all my queries slower.

Any recomendations for making my first query above more optimized and
still efficient?  Does the operator I'm looking for exist?

-- Dante

D. Dante Lorenso wrote:
> I'm looking for an operator that will compare NULL with NULL and
> evaluate as TRUE.
> I have a BIGINT column which might contain NULL values.  I want to pass
> a value to compare with that column in my WHERE clause.  If the value
> I'm comparing is 0, I want it to match the NULL values.  Here is a
> sample query that I currently use:
>   SELECT *
>   FROM mytable
>   WHERE (col IS NULL AND NULLIF(?, 0) IS NULL) OR col = ?;
> The '?' placeholders used in the query will receive the same value which
> might be any one of the following: NULL, 0, 1, 2, 3, etc.
> What I'd really like is an operator that will compare NULL with NULL and
> evaluate as TRUE.  Does that exist?

Is this the answer?

   SELECT *
   FROM mytable
   WHERE col IS NOT DISTINCT FROM NULLIF(?, 0);

-- Dante

"D. Dante Lorenso" <dante@lorenso.com> writes:
> I'm looking for an operator that will compare NULL with NULL and
> evaluate as TRUE.

regression=# select null IS NOT DISTINCT FROM 42;
 ?column?
----------
 f
(1 row)

regression=# select null IS NOT DISTINCT FROM null;
 ?column?
----------
 t
(1 row)

However, if you're expecting this to be real efficient (like, use an
index), you're out of luck ...

> If the value I'm comparing is 0, I want it to match the NULL values.

[ raised eyebrow... ]  Sir, you need to rethink your data
representation.

            regards, tom lane

Tom Lane wrote:
> "D. Dante Lorenso" <dante@lorenso.com> writes:
>> I'm looking for an operator that will compare NULL with NULL and
>> evaluate as TRUE.
>> If the value I'm comparing is 0, I want it to match the NULL values.
> [ raised eyebrow... ]  Sir, you need to rethink your data
> representation.

Tom,

Here's what I'm doing, tell me if I'm crazy:

The column I'm comparing to is 'folder_id'.  The folder_id column is a
foreign key to a folder table.  If folder_id is NULL, the row is not in
a folder.

If I want to find all items in a specific folder, I want:

   SELECT *
   FROM mytable
   WHERE folder_id = 123;

But if I want to find all the items which are not in any folder, I want:

   SELECT *
   FROM mytable
   WHERE folder_id IS NULL;

I don't have any folder_id 0, so on a URL I might do this:

   http://xyz/page.php?fid=123
   http://xyz/page.php?fid=0

If folder_id is 0, I do the NULL comparison.

   SELECT *
   FROM mytable
   WHERE folder_id IS NOT DISTINCT FROM NULLIF(?, 0);

That seems to do what I want.  Is it bad design?  Something I'm missing
about indexing a NULL or something like that?

-- Dante

Tom Lane wrote:
> "D. Dante Lorenso" <dante@lorenso.com> writes:
>> I'm looking for an operator that will compare NULL with NULL and
>> evaluate as TRUE.
> regression=# select null IS NOT DISTINCT FROM 42;
>  ?column?
> ----------
>  f
> (1 row)
> regression=# select null IS NOT DISTINCT FROM null;
>  ?column?
> ----------
>  t
> (1 row)
> However, if you're expecting this to be real efficient (like, use an
> index), you're out of luck ...
>> If the value I'm comparing is 0, I want it to match the NULL values.
> [ raised eyebrow... ]  Sir, you need to rethink your data
> representation.

Tom,

I don't understand why my index is not being used (other than you said so):

----------
   SELECT COUNT(*)
   FROM audio
   WHERE (folder_id = ? AND ? IS NOT NULL)
   OR (folder_id IS NULL AND ? IS NULL);

uses index when ? = 100 (as expected)
does NOT use index when ? = NULL (as expected)

----------
   SELECT COUNT(*)
   FROM audio
   WHERE folder_id IS NOT DISTINCT FROM ?;

does NOT use index when ? = NULL (as expected)
does NOT use index when ? = 100 (NOT expected!) <-------------!!!

----------

So, although 'IS NOT DISTINCT FROM' is a lot more readable than my other
form, it's apparently not efficient.  How can I get the efficiency and
still have the clarity?

-- Dante

D. Dante Lorenso wrote:


> But if I want to find all the items which are not in any folder, I want:
>
>   SELECT *
>   FROM mytable
>   WHERE folder_id IS NULL;
>
> I don't have any folder_id 0, so on a URL I might do this:
>
>   http://xyz/page.php?fid=123
>   http://xyz/page.php?fid=0

Why not just have fid undef? thus you can just say:

>
> If folder_id is 0, I do the NULL comparison.
>
>   SELECT *
>   FROM mytable
>   WHERE folder_id IS NOT DISTINCT FROM NULLIF(?, 0);

SELECT *
    FROM mytable
    WHERE folder IS NULL;

Or have your PHP say...

if ($fid == 0) {
   $fid == "IS NULL"
}

(my php is extremely rusty but I think you get the idea)

And then build out your where clause.

>
> That seems to do what I want.  Is it bad design?  Something I'm missing
> about indexing a NULL or something like that?

Sincerely,

Joshua D. Drake



>
> -- Dante
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>

"D. Dante Lorenso" <dante@lorenso.com> writes:
> Here's what I'm doing, tell me if I'm crazy:

> The column I'm comparing to is 'folder_id'.  The folder_id column is a
> foreign key to a folder table.  If folder_id is NULL, the row is not in
> a folder.

Yup, you're crazy.  The best interpretation of NULL according to the SQL
spec is that you don't know which folder the row is in.

If you are willing to reserve ID 0 as not being any real folder, then
folder_id = 0 would be a reasonable way to represent "it's not in a
folder".  This is positive knowledge, entirely distinct from "I don't
know if it's in a folder, much less which one".

Now there is a small problem with that, which is that if you want to
have folder_id be a foreign key to a table of folders then it doesn't
work so well.  But do not let yourself be tempted to use NULL as a
solution to that.  What I'd suggest after a few seconds' thought is that
you create an explicit "unclassified" folder and put every "not in a
folder" row into the "unclassified" folder.

            regards, tom lane

D. Dante Lorenso wrote:
>> But if I want to find all the items which are not in any folder, I want:
>>
>>   SELECT *
>>   FROM mytable
>>   WHERE folder_id IS NULL;
>>
>> I don't have any folder_id 0, so on a URL I might do this:
>>
>>   http://xyz/page.php?fid=123
>>   http://xyz/page.php?fid=0

SELECT * FROM mytable WHERE COALESCE( folder_id, 0 ) = 0;

--
Lew