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

Boolean logic is failing using JSON_EXTRACT function with multiple RECURSIVE CTE's

    XMLWordPrintable

    Details

      Description

      Was looking to upgrade to 10.6 but ran into a very specific bug that is blocking us:

      CREATE OR REPLACE FUNCTION bug_demo() 
      RETURNS BOOL CONTAINS SQL DETERMINISTIC 
      RETURN (WITH RECURSIVE cte1 AS (SELECT 0 AS num), 
      cte2 AS (SELECT JSON_EXTRACT('[{"foo": "bar"}]', '$[0].foo') AS value FROM cte1), 
      cte3 AS (SELECT value = '"bar"' AS baz FROM cte2) SELECT baz FROM cte3); 
      SELECT bug_demo(), bug_demo(), bug_demo();
       
      +------------+------------+------------+
      | bug_demo() | bug_demo() | bug_demo() |
      +------------+------------+------------+
      |          1 |          0 |          0 |
      +------------+------------+------------+
      
      

      The first returned value (1) is correct but then returns an incorrect value (0) on any further calls. Re-creating the function repeats the pattern.

      Works correctly in 10.4

        Attachments

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            Rich Rich Theobald
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

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