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.

assignee: Dmitry Yemanov [ dimitr ]

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

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

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 2 [ 10560 ]

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

QA Status: Done successfully