Query unable to use expression index that coalesces nulls

[marcrasi]

Describe the problem

I made a table with a nullable boolean field, and I created an expression index on coalesce(field, false). When I set disallow_full_table_scans and query with a WHERE coalesce(field, false) condition, it tells me that the query contains a full table scan.

Weirdly, WHERE not coalesce(field, false) runs successfully and uses the expression index.

To Reproduce

Here are some commands that repro the problem from scratch:

root@localhost:26257/defaultdb> create database repro_bug;
root@localhost:26257/defaultdb> use repro_bug;
root@localhost:26257/repro_bug> create table testing (id string, field boolean, primary key (id));
root@localhost:26257/repro_bug> create index on testing (coalesce(field, false));
root@localhost:26257/repro_bug> set disallow_full_table_scans=on;
root@localhost:26257/repro_bug> set large_full_scan_rows=0;

Now this query fails:

root@localhost:26257/repro_bug> select * from testing where coalesce(field, false);
ERROR: query `select * from testing where coalesce(field, false)` contains a full table/index scan which is explicitly disallowed
SQLSTATE: P0003
HINT: try overriding the `disallow_full_table_scans` or increasing the `large_full_scan_rows` cluster/session settings

And this query succeeds:

root@localhost:26257/repro_bug> select * from testing where not coalesce(field, false);
  id | field
-----+--------
(0 rows)

Here are the explains for the two queries:

root@localhost:26257/repro_bug> explain select * from testing where coalesce(field, false);
                                        info
------------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • filter
  │ estimated row count: 0
  │ filter: COALESCE(field, false)
  │
  └── • scan
        estimated row count: 1 (100% of the table; stats collected 10 seconds ago)
        table: testing@testing_pkey
        spans: FULL SCAN
(11 rows)

root@localhost:26257/repro_bug> explain select * from testing where not coalesce(field, false);
                                       info
-----------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • index join
  │ estimated row count: 0
  │ table: testing@testing_pkey
  │
  └── • scan
        estimated row count: 1 (99% of the table; stats collected 15 seconds ago)
        table: testing@testing_expr_idx
        spans: [/false - /false]
(11 rows)

Expected behavior

The select * from testing where coalesce(field, false) query should succeed and use the index.

Environment:

I'm running cockroach start-single-node on my macbook, and connecting to it with cockroach sql.

% cockroach --version
cockroach version details:
Build Tag:        v23.1.2
Build Time:       2023/05/25 16:20:05
Distribution:     CCL
Platform:         darwin arm64 (aarch64-apple-darwin21.2)
Go Version:       go1.19.4
C Compiler:       Clang 10.0.0
Build Commit ID:  810d4f27a7f02b9cc2750cab654ed1c62ac3e75a
Build Type:       release

Jira issue: CRDB-33326

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-queries (found keywords: vectorized)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[mgartner]

Thanks for the report @marcrasi! It's definitely a bit confusing why the not coalesce(field, false) query uses the expression index, but not the coalesce(field, false) query.

One workaround it to index field and alter your query to include or exclude NULL values as needed:

-- This is the same as `... WHERE coalesce(field, false)` and will use an index on `field`.
SELECT * FROM testing WHERE field;

-- This is the same as `... WHERE NOT coalesce(field, false)` and will use an index on `field`.
SELECT * FROM testing WHERE NOT field OR field IS NULL;