Fix incorrect handling of NULL index entries in indexed ROW() comparisons.

An index search using a row comparison such as ROW(a, b) > ROW('x', 'y')
would stop upon reaching a NULL entry in the "b" column, ignoring the
fact that there might be non-NULL "b" values associated with later values
of "a".  This happens because _bt_mark_scankey_required() marks the
subsidiary scankey for "b" as required, which is just wrong: it's for
a column after the one with the first inequality key (namely "a"), and
thus can't be considered a required match.

This bit of brain fade dates back to the very beginnings of our support
for indexed ROW() comparisons, in 2006.  Kind of astonishing that no one
came across it before Glen Takahashi, in bug #14010.

Back-patch to all supported versions.

Note: the given test case doesn't actually fail in unpatched 9.1, evidently
because the fix for bug #6278 (i.e., stopping at nulls in either scan
direction) is required to make it fail.  I'm sure I could devise a case
that fails in 9.1 as well, perhaps with something involving making a cursor
back up; but it doesn't seem worth the trouble.

2 files changed, 38 insertions, 0 deletions

diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out
index 5e3fa707515..96da4be048c 100644
--- a/src/test/regress/expected/rowtypes.out
+++ b/src/test/regress/expected/rowtypes.out
@@ -268,6 +268,29 @@ order by thousand, tenthous;
       999 |     9999
 (25 rows)
 
+-- Test case for bug #14010: indexed row comparisons fail with nulls
+create temp table test_table (a text, b text);
+insert into test_table values ('a', 'b');
+insert into test_table select 'a', null from generate_series(1,1000);
+insert into test_table values ('b', 'a');
+create index on test_table (a,b);
+set enable_sort = off;
+explain (costs off)
+select a,b from test_table where (a,b) > ('a','a') order by a,b;
+                      QUERY PLAN                       
+-------------------------------------------------------
+ Index Scan using test_table_a_b_idx on test_table
+   Index Cond: (ROW(a, b) > ROW('a'::text, 'a'::text))
+(2 rows)
+
+select a,b from test_table where (a,b) > ('a','a') order by a,b;
+ a | b 
+---+---
+ a | b
+ b | a
+(2 rows)
+
+reset enable_sort;
 -- Check row comparisons with IN
 select * from int8_tbl i8 where i8 in (row(123,456));  -- fail, type mismatch
 ERROR:  cannot compare dissimilar column types bigint and integer at record column 1
diff --git a/src/test/regress/sql/rowtypes.sql b/src/test/regress/sql/rowtypes.sql
index a3652c7ce3d..69851ec2ff1 100644
--- a/src/test/regress/sql/rowtypes.sql
+++ b/src/test/regress/sql/rowtypes.sql
@@ -111,6 +111,21 @@ select thousand, tenthous from tenk1
 where (thousand, tenthous) >= (997, 5000)
 order by thousand, tenthous;
 
+-- Test case for bug #14010: indexed row comparisons fail with nulls
+create temp table test_table (a text, b text);
+insert into test_table values ('a', 'b');
+insert into test_table select 'a', null from generate_series(1,1000);
+insert into test_table values ('b', 'a');
+create index on test_table (a,b);
+set enable_sort = off;
+
+explain (costs off)
+select a,b from test_table where (a,b) > ('a','a') order by a,b;
+
+select a,b from test_table where (a,b) > ('a','a') order by a,b;
+
+reset enable_sort;
+
 -- Check row comparisons with IN
 select * from int8_tbl i8 where i8 in (row(123,456));  -- fail, type mismatch