Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13297

RECURSIVE and NULL returning empty string fields in results

    XMLWordPrintable

Details

    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.

      Attachments

        Activity

          People

            greenman Ian Gilfillan
            darkain Vincent Milum Jr
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.