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

Error "block size exceeds implementation restriction" while inner joining large datasets with a long key using the HASH JOIN plan [CORE5598] #5864

Closed
firebird-issue-importer opened this issue Aug 24, 2017 · 7 comments

Comments

@firebird-issue-importer

Submitted by: @cincuranet

I have a query that on one particular database throws:

2017-08-24T16:29:11.5140 (2704:0000000001620040) ERROR AT
JStatement::openCursor
I:\DOWNLOADS\BI2.FDB (ATT_16, SYSDBA:NONE, NONE,
TCPv6:::1/51084)
C:\Users\Jiri\Documents\devel\bin\Firebird-3.0.2.32703-0_x64\isql.exe:7116
335544381 : Implementation limit exceeded
335544388 : block size exceeds implementation restriction

On other databases (same structure, same plan produced) on the same
server (different data of course) it works fine. Backup & restore didn't
help. I can provide the database, but given it's a customer data it
needs to be handled as private data.

The minimum query I can replicate it looks like this:
with DAYS
as (select distinct(DR.F_DA_DATE)
from T_DAILY_RAW DR
)
select count(*)
from T_EMAILS_WEBDB_RAW EWR
inner join DAYS DR on DR.F_DA_DATE = EWR.F_EWR_SENT
inner join T_EMAIL_TYPE_DIM EMTD on EMTD.F_EMT_VALUE = EWR.F_EWR_TYPE;

And the plan is "PLAN HASH (EMTD NATURAL, JOIN (SORT (DR DR NATURAL), EWR INDEX(IDX_EWR_SENT_A_TEMP)))".

Commits: 4638dc1 92ce56c

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 24, 2017

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 24, 2017

Commented by: @dyemanov

The keys cache for hash join algorithm is limited by 1GB and seems to be reached in this case. Longish UTF-8 VARCHARs as a join key and many records (about 1 million?) together cause this issue.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 24, 2017

Commented by: @cincuranet

Yes. The resulting count is over 700k.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 27, 2017

Modified by: @dyemanov

summary: block size exceeds implementation restriction at JStatement::openCursor => Error "block size exceeds implementation restriction" while inner joining large datasets with a long key using the HASH JOIN plan

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 8, 2018

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 3.0.3 [ 10810 ]

Fix Version: 4.0 Beta 1 [ 10750 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 2, 2018

Modified by: @pavel-zotov

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

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 2, 2018

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
Projects
None yet
Development

No branches or pull requests

2 participants