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

Query plan is missing for the long query [CORE2115] #2548

Closed
firebird-automations opened this issue Oct 10, 2008 · 24 comments
Closed

Query plan is missing for the long query [CORE2115] #2548

firebird-automations opened this issue Oct 10, 2008 · 24 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Oleg Matveyev (o_matveev)

Is related to CORE2116
Is related to QA369

Attachments:
LONG_PLAN.sql
SHORT_PLAN.sql

Why the query plan is missed when long_plan.sql is executed, but is shown when short_plan.sql is executed.
It seems like buffer overflow (2000 byte), am I right?

--------------- short_plan.sql ----------------------------------------------
SET NAMES NONE;

CREATE DATABASE 'oleg:c:\test.ib'
USER 'SYSDBA' PASSWORD 'masterkey' PAGE_SIZE 16384 DEFAULT CHARACTER SET NONE;

CREATE TABLE TESTTABLE (
TESTFIELD INTEGER
);

CREATE INDEX TESTTABLE_INDEX_LONG_NAME ON TESTTABLE (TESTFIELD);

COMMIT;

SET PLAN;

SELECT COUNT(*) FROM TESTTABLE WHERE TESTFIELD
IN(0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
);

COMMIT;

DROP DATABASE;
EXIT;
--------------- end of short_plan.sql ----------------------------------------------

--------------- long_plan.sql ----------------------------------------------
SET NAMES NONE;

CREATE DATABASE 'oleg:c:\test.ib'
USER 'SYSDBA' PASSWORD 'masterkey' PAGE_SIZE 16384 DEFAULT CHARACTER SET NONE;

CREATE TABLE TESTTABLE (
TESTFIELD INTEGER
);

CREATE INDEX TESTTABLE_INDEX_LONG_NAME ON TESTTABLE (TESTFIELD);

COMMIT;

SET PLAN;

SELECT COUNT(*) FROM TESTTABLE WHERE TESTFIELD
IN(0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
);

COMMIT;

DROP DATABASE;
EXIT;
--------------- end of long_plan.sql ----------------------------------------------

Commits: e6dc2d8

@firebird-automations
Copy link
Collaborator Author

Modified by: Oleg Matveyev (o_matveev)

Attachment: LONG_PLAN.sql [ 11070 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: Oleg Matveyev (o_matveev)

Attachment: SHORT_PLAN.sql [ 11071 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: Oleg Matveyev (o_matveev)

Version: 2.0.4 [ 10211 ]

Version: 2.1.0 [ 10041 ]

description: Why the query plan is missed when long_plan.sql is executed, but is shown when short_plan.sql is executed

--------------- short_plan.sql ----------------------------------------------
SET NAMES NONE;

CREATE DATABASE 'oleg:c:\test.ib'
USER 'SYSDBA' PASSWORD 'masterkey' PAGE_SIZE 16384 DEFAULT CHARACTER SET NONE;

CREATE TABLE TESTTABLE (
TESTFIELD INTEGER
);

CREATE INDEX TESTTABLE_INDEX_LONG_NAME ON TESTTABLE (TESTFIELD);

COMMIT;

SET PLAN;

SELECT COUNT(*) FROM TESTTABLE WHERE TESTFIELD
IN(0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
);

COMMIT;

DROP DATABASE;
EXIT;
--------------- end of short_plan.sql ----------------------------------------------

--------------- long_plan.sql ----------------------------------------------
SET NAMES NONE;

CREATE DATABASE 'oleg:c:\test.ib'
USER 'SYSDBA' PASSWORD 'masterkey' PAGE_SIZE 16384 DEFAULT CHARACTER SET NONE;

CREATE TABLE TESTTABLE (
TESTFIELD INTEGER
);

CREATE INDEX TESTTABLE_INDEX_LONG_NAME ON TESTTABLE (TESTFIELD);

COMMIT;

SET PLAN;

SELECT COUNT(*) FROM TESTTABLE WHERE TESTFIELD
IN(0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
);

COMMIT;

DROP DATABASE;
EXIT;
--------------- end of long_plan.sql ----------------------------------------------

=>

Why the query plan is missed when long_plan.sql is executed, but is shown when short_plan.sql is executed.
It seems like buffer overflow (2000 byte), am I right?

--------------- short_plan.sql ----------------------------------------------
SET NAMES NONE;

CREATE DATABASE 'oleg:c:\test.ib'
USER 'SYSDBA' PASSWORD 'masterkey' PAGE_SIZE 16384 DEFAULT CHARACTER SET NONE;

CREATE TABLE TESTTABLE (
TESTFIELD INTEGER
);

CREATE INDEX TESTTABLE_INDEX_LONG_NAME ON TESTTABLE (TESTFIELD);

COMMIT;

SET PLAN;

SELECT COUNT(*) FROM TESTTABLE WHERE TESTFIELD
IN(0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
);

COMMIT;

DROP DATABASE;
EXIT;
--------------- end of short_plan.sql ----------------------------------------------

--------------- long_plan.sql ----------------------------------------------
SET NAMES NONE;

CREATE DATABASE 'oleg:c:\test.ib'
USER 'SYSDBA' PASSWORD 'masterkey' PAGE_SIZE 16384 DEFAULT CHARACTER SET NONE;

CREATE TABLE TESTTABLE (
TESTFIELD INTEGER
);

CREATE INDEX TESTTABLE_INDEX_LONG_NAME ON TESTTABLE (TESTFIELD);

COMMIT;

SET PLAN;

SELECT COUNT(*) FROM TESTTABLE WHERE TESTFIELD
IN(0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
);

COMMIT;

DROP DATABASE;
EXIT;
--------------- end of long_plan.sql ----------------------------------------------

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

I suppose the plan becomes longer than its limit of 32K, so the API call cannot return it properly.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Oleg Matveyev (o_matveev)

Plan for the short_plan.sql is

PLAN (TESTTABLE INDEX (TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME))

ONLY 1239 Bytes!
Why plan for the long_plan.sql more that 32K? Queryes different less that twice.

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

Link: This issue is related to CORE2116 [ CORE2116 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

The Feature request that I have created would certain address this issue.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Oleg, it seems your test case has revealed yet another hidden limitation. The binary plan information (returned by the optimizer to the upper layers that actually construct the textual plan representation) is also limited, but its limit is much smaller -- only 2K. I will try to address this particular issue, to make binary plans limited by 32K (as expected).

@firebird-automations
Copy link
Collaborator Author

Commented by: Oleg Matveyev (o_matveev)

Dima, thanks. I talked with Vlad, he point to function get_plan_info

...
if (*explain == gds__info_truncated)
{
explain_ptr = (SCHAR*) isc_alloc (BUFFER_XLARGE);

...
#⁠define BUFFER_XLARGE 2048

Is it?

@firebird-automations
Copy link
Collaborator Author

Modified by: Oleg Matveyev (o_matveev)

summary: query plan is missed for the long query => Query plan is missed for the long query

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

As far as I see, there are two places with the same limitation. One in /jrd/inf.cpp and another one in /dsql/dsql.cpp. I will fix both tomorrow.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 2.1.1 [ 10223 ]

Version: 2.0.3 [ 10200 ]

Version: 2.0.2 [ 10130 ]

Version: 2.0.1 [ 10090 ]

Version: 2.0.0 [ 10091 ]

summary: Query plan is missed for the long query => Query plan is missing for the long query

Component: API / Client Library [ 10040 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 2.5 Beta 1 [ 10251 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Oleg Matveyev (o_matveev)

Ok, this worked:

SELECT COUNT(*) FROM TESTTABLE WHERE TESTFIELD
IN(0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
);

(plan size is 17439 bytes)

But server was crashed by query:
SELECT COUNT(*) FROM TESTTABLE WHERE TESTFIELD
IN(0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
);

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

My build crashes due to a stack overflow for both these queries. I'm investigating the reason right now...

@firebird-automations
Copy link
Collaborator Author

Commented by: Oleg Matveyev (o_matveev)

My build created Vlad... (2.5.0.21199) runing as superclassic.
my DB was created in the FB2.0.4, ODS 11.1

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

All the tests should work fine now (build 21264 and above).

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA369 [ QA369 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pcisar

QA test added.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test => Done successfully

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