public inbox for [email protected]  

help / color / mirror / Atom feed

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

I'll note that the clause is arbitrary in the sense that I don't generate
it and cannot edit it but it's basically a bunch of boolean comparisons
chained i.e. `col_a >= 1 and col_b <=5 and col_c ...` so I can in general
add an index on say col_a and it does get used.

On Wed, Dec 18, 2024 at 9:47 PM Adrian Garcia Badaracco <[email protected]>
wrote:

> 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: <CAE8z92FZa5dmnm3k2OQOVsazbuUrdSh8HFrgkU3XM79cxv66tw@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