public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Garcia Badaracco <[email protected]>
To: [email protected]
Subject: Wrapping a where clause to preserve rows with nulls
Date: Wed, 18 Dec 2024 21:47:15 -0600
Message-ID: <CAE8z92GN0-5J=4q6RSsPGk_OP-Y_EHVNgQimTWwcq+BE+HGqRQ@mail.gmail.com> (raw)

I have a query where I have to run a where clause generated by another
system (i.e., I can't modify that where clause. The where clause may return
`null`,  but I actually want to keep rows that return `null` (and rows that
return `true` but not rows that return `false`).

I thought it would be as simple as wrapping in `(...) is not false` but
that seems to prevent index usage.

For example, let's say that given the table:

CREATE TABLE test_index (value INTEGER);
CREATE INDEX idx_value ON test_index(value);

And the predicate `value = 5000`, if I run the original query that excludes
rows where the predicate is null it uses the index:

SELECT *
FROM test_index
WHERE value = 5000;

But as soon as I tack on an `IS NOT FALSE` the index is not used:

SELECT *
FROM test_index
WHERE (value = 5000) IS NOT FALSE;

This was surprising to me. I was hoping this might be able to use the index.

Is there any way to include the rows where the predicate evaluates to null
while still using an index?

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Wrapping a where clause to preserve rows with nulls
  In-Reply-To: <CAE8z92GN0-5J=4q6RSsPGk_OP-Y_EHVNgQimTWwcq+BE+HGqRQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox