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

Commitbbfcc71

Browse files
committed
Restore correct btree preprocessing of "indexedcol IS NULL" conditions.
Such a condition is unsatisfiable in combination with any other type ofbtree-indexable condition (since we assume btree operators are alwaysstrict). 8.3 and 8.4 had an explicit test for this, which I removed incommit29c4ad9, mistakenly thinking thatthe case would be subsumed by the more general handling of IS (NOT) NULLadded in that patch. Put it back, and improve the comments about it, andadd a regression test case.Per bug #6079 from Renat Nasyrov, and analysis by Dean Rasheed.
1 parentcbfd82a commitbbfcc71

File tree

3 files changed

+71
-2
lines changed

3 files changed

+71
-2
lines changed

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

Lines changed: 15 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -323,8 +323,14 @@ _bt_preprocess_keys(IndexScanDesc scan)
323323

324324
/*
325325
* If = has been specified, all other keys can be eliminated as
326-
* redundant. In case of key > 2 && key == 1 we can set qual_ok
327-
* to false and abandon further processing.
326+
* redundant. If we have a case like key = 1 AND key > 2, we can
327+
* set qual_ok to false and abandon further processing.
328+
*
329+
* We also have to deal with the case of "key IS NULL", which is
330+
* unsatisfiable in combination with any other index condition.
331+
* By the time we get here, that's been classified as an equality
332+
* check, and we've rejected any combination of it with a regular
333+
* equality condition; but not with other types of conditions.
328334
*/
329335
if (xform[BTEqualStrategyNumber-1])
330336
{
@@ -337,6 +343,13 @@ _bt_preprocess_keys(IndexScanDesc scan)
337343
if (!chk||j== (BTEqualStrategyNumber-1))
338344
continue;
339345

346+
if (eq->sk_flags&SK_SEARCHNULL)
347+
{
348+
/* IS NULL is contradictory to anything else */
349+
so->qual_ok= false;
350+
return;
351+
}
352+
340353
if (_bt_compare_scankey_args(scan,chk,eq,chk,
341354
&test_result))
342355
{

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

Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -942,6 +942,18 @@ SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NUL
942942
1
943943
(1 row)
944944

945+
SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500;
946+
count
947+
-------
948+
499
949+
(1 row)
950+
951+
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500;
952+
count
953+
-------
954+
0
955+
(1 row)
956+
945957
DROP INDEX onek_nulltest;
946958
CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc,unique1);
947959
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
@@ -968,6 +980,18 @@ SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NUL
968980
1
969981
(1 row)
970982

983+
SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500;
984+
count
985+
-------
986+
499
987+
(1 row)
988+
989+
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500;
990+
count
991+
-------
992+
0
993+
(1 row)
994+
971995
DROP INDEX onek_nulltest;
972996
CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc nulls last,unique1);
973997
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
@@ -994,6 +1018,18 @@ SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NUL
9941018
1
9951019
(1 row)
9961020

1021+
SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500;
1022+
count
1023+
-------
1024+
499
1025+
(1 row)
1026+
1027+
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500;
1028+
count
1029+
-------
1030+
0
1031+
(1 row)
1032+
9971033
DROP INDEX onek_nulltest;
9981034
CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 nulls first,unique1);
9991035
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
@@ -1020,6 +1056,18 @@ SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NUL
10201056
1
10211057
(1 row)
10221058

1059+
SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500;
1060+
count
1061+
-------
1062+
499
1063+
(1 row)
1064+
1065+
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500;
1066+
count
1067+
-------
1068+
0
1069+
(1 row)
1070+
10231071
RESET enable_seqscan;
10241072
RESET enable_indexscan;
10251073
RESET enable_bitmapscan;

‎src/test/regress/sql/create_index.sql

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -393,6 +393,8 @@ SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
393393
SELECTcount(*)FROM onek_with_nullWHERE unique1 ISNULLAND unique2 ISNULL;
394394
SELECTcount(*)FROM onek_with_nullWHERE unique1IS NOT NULL;
395395
SELECTcount(*)FROM onek_with_nullWHERE unique1 ISNULLAND unique2IS NOT NULL;
396+
SELECTcount(*)FROM onek_with_nullWHERE unique1IS NOT NULLAND unique1>500;
397+
SELECTcount(*)FROM onek_with_nullWHERE unique1 ISNULLAND unique1>500;
396398

397399
DROPINDEX onek_nulltest;
398400

@@ -402,6 +404,8 @@ SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
402404
SELECTcount(*)FROM onek_with_nullWHERE unique1 ISNULLAND unique2 ISNULL;
403405
SELECTcount(*)FROM onek_with_nullWHERE unique1IS NOT NULL;
404406
SELECTcount(*)FROM onek_with_nullWHERE unique1 ISNULLAND unique2IS NOT NULL;
407+
SELECTcount(*)FROM onek_with_nullWHERE unique1IS NOT NULLAND unique1>500;
408+
SELECTcount(*)FROM onek_with_nullWHERE unique1 ISNULLAND unique1>500;
405409

406410
DROPINDEX onek_nulltest;
407411

@@ -411,6 +415,8 @@ SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
411415
SELECTcount(*)FROM onek_with_nullWHERE unique1 ISNULLAND unique2 ISNULL;
412416
SELECTcount(*)FROM onek_with_nullWHERE unique1IS NOT NULL;
413417
SELECTcount(*)FROM onek_with_nullWHERE unique1 ISNULLAND unique2IS NOT NULL;
418+
SELECTcount(*)FROM onek_with_nullWHERE unique1IS NOT NULLAND unique1>500;
419+
SELECTcount(*)FROM onek_with_nullWHERE unique1 ISNULLAND unique1>500;
414420

415421
DROPINDEX onek_nulltest;
416422

@@ -420,6 +426,8 @@ SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
420426
SELECTcount(*)FROM onek_with_nullWHERE unique1 ISNULLAND unique2 ISNULL;
421427
SELECTcount(*)FROM onek_with_nullWHERE unique1IS NOT NULL;
422428
SELECTcount(*)FROM onek_with_nullWHERE unique1 ISNULLAND unique2IS NOT NULL;
429+
SELECTcount(*)FROM onek_with_nullWHERE unique1IS NOT NULLAND unique1>500;
430+
SELECTcount(*)FROM onek_with_nullWHERE unique1 ISNULLAND unique1>500;
423431

424432
RESET enable_seqscan;
425433
RESET enable_indexscan;

0 commit comments

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