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

Commita5652d3

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 parentcd70dd6 commita5652d3

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
@@ -325,8 +325,14 @@ _bt_preprocess_keys(IndexScanDesc scan)
325325

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

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

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

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

1282+
SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500;
1283+
count
1284+
-------
1285+
499
1286+
(1 row)
1287+
1288+
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500;
1289+
count
1290+
-------
1291+
0
1292+
(1 row)
1293+
12821294
DROP INDEX onek_nulltest;
12831295
CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc,unique1);
12841296
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
@@ -1305,6 +1317,18 @@ SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NUL
13051317
1
13061318
(1 row)
13071319

1320+
SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500;
1321+
count
1322+
-------
1323+
499
1324+
(1 row)
1325+
1326+
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500;
1327+
count
1328+
-------
1329+
0
1330+
(1 row)
1331+
13081332
DROP INDEX onek_nulltest;
13091333
CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc nulls last,unique1);
13101334
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
@@ -1331,6 +1355,18 @@ SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NUL
13311355
1
13321356
(1 row)
13331357

1358+
SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500;
1359+
count
1360+
-------
1361+
499
1362+
(1 row)
1363+
1364+
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500;
1365+
count
1366+
-------
1367+
0
1368+
(1 row)
1369+
13341370
DROP INDEX onek_nulltest;
13351371
CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 nulls first,unique1);
13361372
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
@@ -1357,6 +1393,18 @@ SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NUL
13571393
1
13581394
(1 row)
13591395

1396+
SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500;
1397+
count
1398+
-------
1399+
499
1400+
(1 row)
1401+
1402+
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500;
1403+
count
1404+
-------
1405+
0
1406+
(1 row)
1407+
13601408
RESET enable_seqscan;
13611409
RESET enable_indexscan;
13621410
RESET enable_bitmapscan;

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

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -451,6 +451,8 @@ SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
451451
SELECTcount(*)FROM onek_with_nullWHERE unique1 ISNULLAND unique2 ISNULL;
452452
SELECTcount(*)FROM onek_with_nullWHERE unique1IS NOT NULL;
453453
SELECTcount(*)FROM onek_with_nullWHERE unique1 ISNULLAND unique2IS NOT NULL;
454+
SELECTcount(*)FROM onek_with_nullWHERE unique1IS NOT NULLAND unique1>500;
455+
SELECTcount(*)FROM onek_with_nullWHERE unique1 ISNULLAND unique1>500;
454456

455457
DROPINDEX onek_nulltest;
456458

@@ -460,6 +462,8 @@ SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
460462
SELECTcount(*)FROM onek_with_nullWHERE unique1 ISNULLAND unique2 ISNULL;
461463
SELECTcount(*)FROM onek_with_nullWHERE unique1IS NOT NULL;
462464
SELECTcount(*)FROM onek_with_nullWHERE unique1 ISNULLAND unique2IS NOT NULL;
465+
SELECTcount(*)FROM onek_with_nullWHERE unique1IS NOT NULLAND unique1>500;
466+
SELECTcount(*)FROM onek_with_nullWHERE unique1 ISNULLAND unique1>500;
463467

464468
DROPINDEX onek_nulltest;
465469

@@ -469,6 +473,8 @@ SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
469473
SELECTcount(*)FROM onek_with_nullWHERE unique1 ISNULLAND unique2 ISNULL;
470474
SELECTcount(*)FROM onek_with_nullWHERE unique1IS NOT NULL;
471475
SELECTcount(*)FROM onek_with_nullWHERE unique1 ISNULLAND unique2IS NOT NULL;
476+
SELECTcount(*)FROM onek_with_nullWHERE unique1IS NOT NULLAND unique1>500;
477+
SELECTcount(*)FROM onek_with_nullWHERE unique1 ISNULLAND unique1>500;
472478

473479
DROPINDEX onek_nulltest;
474480

@@ -478,6 +484,8 @@ SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
478484
SELECTcount(*)FROM onek_with_nullWHERE unique1 ISNULLAND unique2 ISNULL;
479485
SELECTcount(*)FROM onek_with_nullWHERE unique1IS NOT NULL;
480486
SELECTcount(*)FROM onek_with_nullWHERE unique1 ISNULLAND unique2IS NOT NULL;
487+
SELECTcount(*)FROM onek_with_nullWHERE unique1IS NOT NULLAND unique1>500;
488+
SELECTcount(*)FROM onek_with_nullWHERE unique1 ISNULLAND unique1>500;
481489

482490
RESET enable_seqscan;
483491
RESET enable_indexscan;

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp