Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita298a1e

Browse files
committed
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 thefact that there might be non-NULL "b" values associated with later valuesof "a". This happens because _bt_mark_scankey_required() marks thesubsidiary scankey for "b" as required, which is just wrong: it's fora column after the one with the first inequality key (namely "a"), andthus can't be considered a required match.This bit of brain fade dates back to the very beginnings of our supportfor indexed ROW() comparisons, in 2006. Kind of astonishing that no onecame 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, evidentlybecause the fix for bug #6278 (i.e., stopping at nulls in either scandirection) is required to make it fail. I'm sure I could devise a casethat fails in 9.1 as well, perhaps with something involving making a cursorback up; but it doesn't seem worth the trouble.
1 parentbe060cb commita298a1e

File tree

3 files changed

+46
-23
lines changed

3 files changed

+46
-23
lines changed

‎src/backend/access/nbtree/nbtutils.c

Lines changed: 8 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -1285,12 +1285,9 @@ _bt_fix_scankey_strategy(ScanKey skey, int16 *indoption)
12851285
*
12861286
* Depending on the operator type, the key may be required for both scan
12871287
* directions or just one. Also, if the key is a row comparison header,
1288-
* we have to mark the appropriate subsidiary ScanKeys as required. In
1289-
* such cases, the first subsidiary key is required, but subsequent ones
1290-
* are required only as long as they correspond to successive index columns
1291-
* and match the leading column as to sort direction.
1292-
* Otherwise the row comparison ordering is different from the index ordering
1293-
* and so we can't stop the scan on the basis of those lower-order columns.
1288+
* we have to mark its first subsidiary ScanKey as required. (Subsequent
1289+
* subsidiary ScanKeys are normally for lower-order columns, and thus
1290+
* cannot be required, since they're after the first non-equality scankey.)
12941291
*
12951292
* Note: when we set required-key flag bits in a subsidiary scankey, we are
12961293
* scribbling on a data structure belonging to the index AM's caller, not on
@@ -1328,24 +1325,12 @@ _bt_mark_scankey_required(ScanKey skey)
13281325
if (skey->sk_flags&SK_ROW_HEADER)
13291326
{
13301327
ScanKeysubkey= (ScanKey)DatumGetPointer(skey->sk_argument);
1331-
AttrNumberattno=skey->sk_attno;
13321328

1333-
/* First subkey should be same as the header says */
1334-
Assert(subkey->sk_attno==attno);
1335-
1336-
for (;;)
1337-
{
1338-
Assert(subkey->sk_flags&SK_ROW_MEMBER);
1339-
if (subkey->sk_attno!=attno)
1340-
break;/* non-adjacent key, so not required */
1341-
if (subkey->sk_strategy!=skey->sk_strategy)
1342-
break;/* wrong direction, so not required */
1343-
subkey->sk_flags |=addflags;
1344-
if (subkey->sk_flags&SK_ROW_END)
1345-
break;
1346-
subkey++;
1347-
attno++;
1348-
}
1329+
/* First subkey should be same column/operator as the header */
1330+
Assert(subkey->sk_flags&SK_ROW_MEMBER);
1331+
Assert(subkey->sk_attno==skey->sk_attno);
1332+
Assert(subkey->sk_strategy==skey->sk_strategy);
1333+
subkey->sk_flags |=addflags;
13491334
}
13501335
}
13511336

‎src/test/regress/expected/rowtypes.out

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -278,6 +278,29 @@ order by thousand, tenthous;
278278
999 | 9999
279279
(25 rows)
280280

281+
-- Test case for bug #14010: indexed row comparisons fail with nulls
282+
create temp table test_table (a text, b text);
283+
insert into test_table values ('a', 'b');
284+
insert into test_table select 'a', null from generate_series(1,1000);
285+
insert into test_table values ('b', 'a');
286+
create index on test_table (a,b);
287+
set enable_sort = off;
288+
explain (costs off)
289+
select a,b from test_table where (a,b) > ('a','a') order by a,b;
290+
QUERY PLAN
291+
--------------------------------------------------------
292+
Index Only Scan using test_table_a_b_idx on test_table
293+
Index Cond: (ROW(a, b) > ROW('a'::text, 'a'::text))
294+
(2 rows)
295+
296+
select a,b from test_table where (a,b) > ('a','a') order by a,b;
297+
a | b
298+
---+---
299+
a | b
300+
b | a
301+
(2 rows)
302+
303+
reset enable_sort;
281304
-- Check row comparisons with IN
282305
select * from int8_tbl i8 where i8 in (row(123,456)); -- fail, type mismatch
283306
ERROR: cannot compare dissimilar column types bigint and integer at record column 1

‎src/test/regress/sql/rowtypes.sql

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -116,6 +116,21 @@ select thousand, tenthous from tenk1
116116
where (thousand, tenthous)>= (997,5000)
117117
order by thousand, tenthous;
118118

119+
-- Test case for bug #14010: indexed row comparisons fail with nulls
120+
create temp table test_table (atext, btext);
121+
insert into test_tablevalues ('a','b');
122+
insert into test_tableselect'a',nullfrom generate_series(1,1000);
123+
insert into test_tablevalues ('b','a');
124+
createindexon test_table (a,b);
125+
set enable_sort= off;
126+
127+
explain (costs off)
128+
select a,bfrom test_tablewhere (a,b)> ('a','a')order by a,b;
129+
130+
select a,bfrom test_tablewhere (a,b)> ('a','a')order by a,b;
131+
132+
reset enable_sort;
133+
119134
-- Check row comparisons with IN
120135
select*from int8_tbl i8where i8in (row(123,456));-- fail, type mismatch
121136

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp