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

Regression: recursive query in SQL query returns incorrect results if more than one branch bypass [CORE4240] #4564

Closed
firebird-issue-importer opened this issue Oct 5, 2013 · 6 comments

Comments

@firebird-issue-importer
Copy link

firebird-issue-importer commented Oct 5, 2013

Submitted by: @sim1984

The following query produces different results on 2.5 and 3.0

WITH RECURSIVE H
AS (SELECT 1 AS CODE_HORSE,
2 AS CODE_FATHER,
3 AS CODE_MOTHER
FROM RDB$DATABASE
UNION ALL
SELECT 2 AS CODE_HORSE,
4 AS CODE_FATHER,
5 AS CODE_MOTHER
FROM RDB$DATABASE
UNION ALL
SELECT 3 AS CODE_HORSE,
4 AS CODE_FATHER,
5 AS CODE_MOTHER
FROM RDB$DATABASE
UNION ALL
SELECT 4 AS CODE_HORSE,
NULL AS CODE_FATHER,
NULL AS CODE_MOTHER
FROM RDB$DATABASE
UNION ALL
SELECT 5 AS CODE_HORSE,
NULL AS CODE_FATHER,
NULL AS CODE_MOTHER
FROM RDB$DATABASE),
R
AS (SELECT H.CODE_HORSE AS CODE_HORSE,
H.CODE_FATHER AS CODE_FATHER,
H.CODE_MOTHER AS CODE_MOTHER,
CAST('' AS VARCHAR(10)) AS MARK,
0 AS DEPTH
FROM H
WHERE H.CODE_HORSE = 1
UNION ALL
SELECT H.CODE_HORSE AS CODE_HORSE,
H.CODE_FATHER AS CODE_FATHER,
H.CODE_MOTHER AS CODE_MOTHER,
'F' || R.MARK AS MARK,
R.DEPTH + 1 AS DEPTH
FROM R
JOIN H ON R.CODE_FATHER = H.CODE_HORSE
WHERE R.DEPTH < 5
UNION ALL
SELECT H.CODE_HORSE AS CODE_HORSE,
H.CODE_FATHER AS CODE_FATHER,
H.CODE_MOTHER AS CODE_MOTHER,
'M' || R.MARK AS MARK,
R.DEPTH + 1 AS DEPTH
FROM R
JOIN H ON R.CODE_MOTHER = H.CODE_HORSE
WHERE R.DEPTH < 5)
SELECT *
FROM R

Result FB 2.5

CODE_HORSE CODE_FATHER CODE_MOTHER MARK DEPTH
1 2 3 0
2 4 5 F 1
4 FF 2
5 MF 2
3 4 5 M 1
4 FM 2
5 MM 2

Result FB 3.0

CODE_HORSE CODE_FATHER CODE_MOTHER MARK DEPTH
1 2 3 0
2 4 5 F 1
4 FF 2

In Firebird 3 result is not correct. Optimizer for some reason does not bypass second branch of the recursive query.

Commits: 92fca58 FirebirdSQL/fbt-repository@7a5bfa5 FirebirdSQL/fbt-repository@3f06293

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Oct 5, 2013

Modified by: @sim1984

description: The following query produces different results on 2.5 and 3.0

[SRC sql]WITH RECURSIVE H
AS (SELECT 1 AS CODE_HORSE,
2 AS CODE_FATHER,
3 AS CODE_MOTHER
FROM RDB$DATABASE
UNION ALL
SELECT 2 AS CODE_HORSE,
4 AS CODE_FATHER,
5 AS CODE_MOTHER
FROM RDB$DATABASE
UNION ALL
SELECT 3 AS CODE_HORSE,
4 AS CODE_FATHER,
5 AS CODE_MOTHER
FROM RDB$DATABASE
UNION ALL
SELECT 4 AS CODE_HORSE,
NULL AS CODE_FATHER,
NULL AS CODE_MOTHER
FROM RDB$DATABASE
UNION ALL
SELECT 5 AS CODE_HORSE,
NULL AS CODE_FATHER,
NULL AS CODE_MOTHER
FROM RDB$DATABASE),
R
AS (SELECT H.CODE_HORSE AS CODE_HORSE,
H.CODE_FATHER AS CODE_FATHER,
H.CODE_MOTHER AS CODE_MOTHER,
CAST('' AS VARCHAR(10)) AS MARK,
0 AS DEPTH
FROM H
WHERE H.CODE_HORSE = 1
UNION ALL
SELECT H.CODE_HORSE AS CODE_HORSE,
H.CODE_FATHER AS CODE_FATHER,
H.CODE_MOTHER AS CODE_MOTHER,
'F' || R.MARK AS MARK,
R.DEPTH + 1 AS DEPTH
FROM R
JOIN H ON R.CODE_FATHER = H.CODE_HORSE
WHERE R.DEPTH < 5
UNION ALL
SELECT H.CODE_HORSE AS CODE_HORSE,
H.CODE_FATHER AS CODE_FATHER,
H.CODE_MOTHER AS CODE_MOTHER,
'M' || R.MARK AS MARK,
R.DEPTH + 1 AS DEPTH
FROM R
JOIN H ON R.CODE_MOTHER = H.CODE_HORSE
WHERE R.DEPTH < 5)
SELECT *
FROM R [/SRC]

Result FB 2.5

[FIXED]CODE_HORSE CODE_FATHER CODE_MOTHER MARK DEPTH
1 2 3 0
2 4 5 F 1
4 FF 2
5 MF 2
3 4 5 M 1
4 FM 2
5 MM 2 [/FIXED]

Result FB 3.0

[FIXED]CODE_HORSE CODE_FATHER CODE_MOTHER MARK DEPTH
1 2 3 0
2 4 5 F 1
4 FF 2 [/FIXED]

In Firebird 3 result is not correct. Optimizer for some reason does not bypass second branch of the recursive query.

=>

The following query produces different results on 2.5 and 3.0

WITH RECURSIVE H
AS (SELECT 1 AS CODE_HORSE,
2 AS CODE_FATHER,
3 AS CODE_MOTHER
FROM RDB$DATABASE
UNION ALL
SELECT 2 AS CODE_HORSE,
4 AS CODE_FATHER,
5 AS CODE_MOTHER
FROM RDB$DATABASE
UNION ALL
SELECT 3 AS CODE_HORSE,
4 AS CODE_FATHER,
5 AS CODE_MOTHER
FROM RDB$DATABASE
UNION ALL
SELECT 4 AS CODE_HORSE,
NULL AS CODE_FATHER,
NULL AS CODE_MOTHER
FROM RDB$DATABASE
UNION ALL
SELECT 5 AS CODE_HORSE,
NULL AS CODE_FATHER,
NULL AS CODE_MOTHER
FROM RDB$DATABASE),
R
AS (SELECT H.CODE_HORSE AS CODE_HORSE,
H.CODE_FATHER AS CODE_FATHER,
H.CODE_MOTHER AS CODE_MOTHER,
CAST('' AS VARCHAR(10)) AS MARK,
0 AS DEPTH
FROM H
WHERE H.CODE_HORSE = 1
UNION ALL
SELECT H.CODE_HORSE AS CODE_HORSE,
H.CODE_FATHER AS CODE_FATHER,
H.CODE_MOTHER AS CODE_MOTHER,
'F' || R.MARK AS MARK,
R.DEPTH + 1 AS DEPTH
FROM R
JOIN H ON R.CODE_FATHER = H.CODE_HORSE
WHERE R.DEPTH < 5
UNION ALL
SELECT H.CODE_HORSE AS CODE_HORSE,
H.CODE_FATHER AS CODE_FATHER,
H.CODE_MOTHER AS CODE_MOTHER,
'M' || R.MARK AS MARK,
R.DEPTH + 1 AS DEPTH
FROM R
JOIN H ON R.CODE_MOTHER = H.CODE_HORSE
WHERE R.DEPTH < 5)
SELECT *
FROM R

Result FB 2.5

CODE_HORSE CODE_FATHER CODE_MOTHER MARK DEPTH
1 2 3 0
2 4 5 F 1
4 FF 2
5 MF 2
3 4 5 M 1
4 FM 2
5 MM 2

Result FB 3.0

CODE_HORSE CODE_FATHER CODE_MOTHER MARK DEPTH
1 2 3 0
2 4 5 F 1
4 FF 2

In Firebird 3 result is not correct. Optimizer for some reason does not bypass second branch of the recursive query.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Oct 6, 2013

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Oct 6, 2013

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Oct 7, 2013

Modified by: Sean Leyne (seanleyne)

summary: incorrect result recursive query in SQL query if more than one branch bypass => Regression: recursive query in SQL query returns incorrect results if more than one branch bypass

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 12, 2013

Modified by: @dyemanov

status: In Progress [ 3 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 2 [ 10560 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 30, 2015

Modified by: @pavel-zotov

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

QA Status: Done successfully

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