Plan returned for query with recursive CTE return wrong count of parenthesis [CORE3614] #3967
Comments
|
Commented by: @livius2 i test this also in latest snapshot of 2.5.1 series (Firebird-2.5.1.26375) and for my real query it looks like this as you can see here are 9 opening parenthesis and 15 closing parenthesis.. |
|
Commented by: @livius2 I suppose that it can be closed as fixed in FB3 PLAN JOIN (R_TREE TT INDEX (IXA_TEST_TREE__ID_HEADER), R_TREE TT INDEX (IXA_TEST_TREE__ID_HEADER), TT2 INDEX (PK_TEST_TREE__ID)) but there is some minor problem - that generated plan cannot be included as explicit plan in query |
|
Commented by: @dyemanov Printed plans really cannot be used inside non-trivial queries, their syntax and semantics is just too limited for that purpose. |
Modified by: @dyemanovstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 3.0.0 [ 10740 ] assignee: Dmitry Yemanov [ dimitr ] |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Closed [ 6 ] |
Submitted by: @livius2
####################### metadata ###############################
CREATE TABLE TEST_TREE
(
ID INTEGER NOT NULL,
ID_HEADER INTEGER,
CONSTRAINT PK_TEST_TREE__ID PRIMARY KEY (ID)
);
CREATE INDEX IXA_TEST_TREE__ID_HEADER ON TEST_TREE (ID_HEADER);
####################### test data ###############################
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('1', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('2', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('3', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('4', '1');
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('5', '4');
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('6', '2');
####################### test query ###############################
WITH RECURSIVE
R_TREE AS
(
SELECT http://TT.ID AS A, CAST(http://TT.ID AS VARCHAR(100)) AS ASUM
FROM TEST_TREE TT
WHERE TT.ID_HEADER IS NULL
UNION ALL
SELECT http://TT.ID AS A, RT.ASUM || '_' || http://TT.ID
FROM TEST_TREE TT JOIN R_TREE RT ON RT.A = TT.ID_HEADER
)
SELECT
*
FROM
R_TREE RT2 INNER JOIN TEST_TREE TT2 ON TT2.ID=RT2.A
####################### plan returned b y engine ###############################
PLAN JOIN ((R_TREE TT INDEX (IXA_TEST_TREE__ID_HEADER))
PLAN (R_TREE TT INDEX (IXA_TEST_TREE__ID_HEADER)), TT2 INDEX (PK_TEST_TREE__ID))
as you can see count of opening parenthesis are not equal to count of closing parenthesis
The text was updated successfully, but these errors were encountered: