incorrect result recursive query in SQL query if more than one branch bypass
----------------------------------------------------------------------------
Key: CORE-4240
URL: http://tracker.firebirdsql.org/browse/CORE-4240
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0 Alpha 1
Reporter: Simonov Denis
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.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
October Webinars: Code for Performance
Free Intel webinars can help you accelerate application performance.
Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most from
the latest Intel processors and coprocessors. See abstracts and register >
http://pubads.g.doubleclick.net/gampad/clk?id=60134791&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
