Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.6, 10.2.7
-
None
Description
RECURSIVE results with NULL or empty string values in the anchor row will break the results in all other result rows (values for these columns will ALWAYS return an empty string regardless of what their values actually are)
This will work properly, the `tbl` recursive table is NOT referenced
WITH RECURSIVE tbl AS ( |
SELECT NULL AS col |
UNION |
SELECT "THIS WORKS" AS col |
)
|
SELECT * FROM tbl |
Addition of recursive `tbl` reference. The string in the second result never shows up.
WITH RECURSIVE tbl AS ( |
SELECT NULL AS col |
UNION |
SELECT "THIS NEVER SHOWS UP" AS col FROM tbl |
)
|
SELECT * FROM tbl |
Using any value other than NULL in the anchor results in only a single row being returned
WITH RECURSIVE tbl AS ( |
SELECT 0 AS col |
UNION |
SELECT "NOW ONLY 1 ROW IS RETURNED" AS col FROM tbl |
)
|
SELECT * FROM tbl |
The intended purpose was to have a custom anchor row separate from the data in the table, such as "SELECT 5 AS `id`, NULL AS `data`" and have the recursive functionality pull the data afterwards, this way the 10+ joined tables needed in my application would only need to be specified in the second half of the union, and only an otherwise empty dummy row would be specified in the anchor part of the union to simplify the query.