Adding 2nd condition in WHERE which containing RDB$DBKEY leads to NATURAL plan
------------------------------------------------------------------------------
Key: CORE-4492
URL: http://tracker.firebirdsql.org/browse/CORE-4492
Project: Firebird Core
Issue Type: Bug
Reporter: Pavel Zotov
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.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
