Re: ERROR: found unexpected null value in index

Manuel Rigger <rigger.manuel@gmail.com> writes:
> CREATE TABLE t0(c0 TEXT);
> INSERT INTO t0(c0) VALUES('b'), ('a');
> ANALYZE t0;
> INSERT INTO t0(c0) VALUES (NULL);
> UPDATE t0 SET c0 = 'a';
> CREATE INDEX i0 ON t0(c0);
> SELECT * FROM t0 WHERE 'baaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' > t0.c0;
> -- unexpected: ERROR: found unexpected null value in index "i0"

Nifty.  As before, the way to make this reproducible is to do it with
another open transaction holding a snapshot, so that the NULL entry
has to be reflected in the index.  (I'm not sure why the HOT-update
exception doesn't apply here, but apparently it doesn't.)  That is,
it's sufficient to set up one session with

begin transaction isolation level serializable;
select * from some_table;

and then run the above script in another session.

The error is coming from the planner's get_actual_variable_range:

                    /* Shouldn't have got a null, but be careful */
                    if (isnull[0])
                        elog(ERROR, "found unexpected null value in index \"%s\"",
                             RelationGetRelationName(indexRel));

and I think it's entirely within its rights to complain, because it
set up the scan key to reject nulls.  In short, somebody seems to
have broken btrees' processing of SK_ISNULL | SK_SEARCHNOTNULL scankeys,
and they broke it in v11, because prior versions don't show this failure.

            regards, tom lane