description: SQL> set plan only;

Test-1a:

SQL> select count(*) from rdb$database where rdb$db_key = 1;

PLAN (RDB$DATABASE INDEX ()) // Ok

Test-1b:

SQL> select count(*) from rdb$database where rdb$db_key in (1,2);

PLAN (RDB$DATABASE NATURAL) // Bad

SQL> quit;

Also, consider the following cases (they seems not related with showed above but I don`t want to create separate ticket for these RDB$DB_KEY problems):

-- ::: NB::: make reconnect now to ensure that set planonly = OFF

Test-2a:

SQL> out /dev/null;
SQL> select count(*) from rdb$database where rdb$db_key is null;

Trace:

1 records fetched
0 ms, 6 fetch(es)

Table Natural
********************************************
RDB$DATABASE 1
(despite that result is 0, FB *does* scans - we can see here fetches > 0).

Test-2b:

SQL> select count(*) from rdb$database where rdb$db_key = cast(null as char(8) character set octets);

Trace:

select count(*) from rdb$database where rdb$db_key = cast(null as char(8) character set octets)
1 records fetched
0 ms

Where statistics with RDB$DATABASE ?
(the same result will be for this: select count(*) from rdb$database where rdb$db_key = cast('ABC' as char(8) character set octets); )

Test 2-c:

SQL> select count(*) from rdb$database where rdb$db_key = cast('1234' as char(8) character set octets);

Statement 6835:
-------------------------------------------------------------------------------
select count(*) from rdb$database where rdb$db_key = cast('1234' as char(8) character set octets)
1 records fetched
0 ms, 302 fetch(es)

Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
RDB$PAGES 148

Again no RDB$DATABASE in statistics but why RDB$PAGES appears here ?

PS. Reproduced on both FB 2.5 and 3.0.

=>

SQL> set plan only;

Test-1a:

SQL> select count(*) from rdb$database where rdb$db_key = 1;

PLAN (RDB$DATABASE INDEX ()) // Ok

Test-1b:

SQL> select count(*) from rdb$database where rdb$db_key in (1,2);

PLAN (RDB$DATABASE NATURAL) // Bad

SQL> quit;

assignee: Dmitry Yemanov [ dimitr ]

status: Open [ 1 ] => Open [ 1 ]

Test Details: See also CORE4038, CORE4530

status: Open [ 1 ] => Open [ 1 ]

Test Details: See also CORE4038, CORE4530 => Deferred untill ticket will be fixed.
See also CORE4038, CORE4530

QA Status: Deferred

Version: 4.0 Alpha 1 [ 10731 ]

Version: 3.0.2 [ 10785 ]

Version: 2.5.7 [ 10770 ]

Version: 3.0.1 [ 10730 ]

Version: 2.5.6 [ 10721 ]

Version: 3.0.0 [ 10740 ]

Version: 4.0 Initial [ 10621 ]

Version: 2.5.5 [ 10670 ]

Version: 2.5.4 [ 10585 ]

Version: 2.5.3 Update 1 [ 10650 ]

Version: 2.5.3 [ 10461 ]

Version: 2.5.2 Update 1 [ 10521 ]

Version: 2.5.2 [ 10450 ]

Version: 2.5.1 [ 10333 ]

Version: 2.5.0 [ 10221 ]

summary: Adding 2nd condition in WHERE which containing RDB$DBKEY leads to NATURAL plan => OR/IN predicates for RDB$DBKEY lead to NATURAL plan

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 Beta 1 [ 10750 ]

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Deferred => Done with caveats

Test Details: Deferred untill ticket will be fixed.
See also CORE4038, CORE4530

=>

Test includes only part of examples. Two cases remain uncleared.
Sent letter to dimitr, 25.11.2017 22:42. Waiting for reply.

See also CORE4038, CORE4530

Fix Version: 3.0.3 [ 10810 ]

status: Resolved [ 5 ] => Resolved [ 5 ]

Test Details: Test includes only part of examples. Two cases remain uncleared.
Sent letter to dimitr, 25.11.2017 22:42. Waiting for reply.

See also CORE4038, CORE4530

=>

Following query will not compile:
select 1 from rdb$relations a join rdb$relations b using ( rdb$db_key );
Statement failed, SQLSTATE = 42000 / -Token unknown / -rdb$db_key

See also CORE4038, CORE4530

status: Resolved [ 5 ] => Closed [ 6 ]