Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

OR/IN predicates for RDB$DBKEY lead to NATURAL plan [CORE4492] #1566

Closed
firebird-automations opened this issue Jul 13, 2014 · 13 comments
Closed

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @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;

Commits: 134d923 57f2da4

====== Test Details ======

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @mrotteveel

Please: use one problem per ticket, do not put multiple problems into one even if they are related to the same feature.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Wiped out the second issue, especially given that it wasn't a bug.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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;

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

Test Details: See also CORE4038, CORE4530

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

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

QA Status: Deferred

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Fixing it for v4 at the moment, to be backported after successful QA pass.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 4.0 Beta 1 [ 10750 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0.3 [ 10810 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment