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-issue-importer opened this issue Oct 10, 2008 · 24 comments
Closed

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

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

Comments

@firebird-issue-importer

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 10, 2008

Modified by: Oleg Matveyev (o_matveev)

Attachment: LONG_PLAN.sql [ 11070 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 10, 2008

Modified by: Oleg Matveyev (o_matveev)

Attachment: SHORT_PLAN.sql [ 11071 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 10, 2008

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 10, 2008

Commented by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 10, 2008

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 10, 2008

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 10, 2008

Modified by: Sean Leyne (seanleyne)

Link: This issue is related to CORE2116 [ CORE2116 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 10, 2008

Commented by: Sean Leyne (seanleyne)

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 10, 2008

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 11, 2008

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 11, 2008

Modified by: Oleg Matveyev (o_matveev)

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 11, 2008

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 13, 2008

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 13, 2008

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 13, 2008

Modified by: @dyemanov

Fix Version: 2.5 Beta 1 [ 10251 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 20, 2008

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 20, 2008

Commented by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 20, 2008

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 1, 2008

Commented by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 23, 2011

Modified by: @pcisar

Link: This issue is related to QA369 [ QA369 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 27, 2011

Commented by: @pcisar

QA test added.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 27, 2011

Modified by: @pcisar

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 19, 2016

Modified by: @pavel-zotov

QA Status: No test

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 19, 2016

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