[#MDEV-29627] Boolean logic is failing using JSON_EXTRACT function with multiple RECURSIVE CTE's

[MDEV-29627] Boolean logic is failing using JSON_EXTRACT function with multiple RECURSIVE CTE's Created: 2022-09-24 Updated: 2024-02-02
Status:	Confirmed
Project:	MariaDB Server
Component/s:	Stored routines
Affects Version/s:	10.3, 10.4, 10.6.10, 10.5, 10.6, 10.7, 10.8, 10.9
Fix Version/s:	10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3

Type:

Bug

Priority:

Major

Reporter:

Rich Theobald

Assignee:

Sergei Petrunia

Resolution:

Unresolved

Votes:

Labels:

regression, wrong_result

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

Comments

Comment by Rich Theobald [ 2022-09-26 ]

I neglected to check later versions that the 10.4.24 version that I am using.

This is also failing in 10.4.25 or higher

Comment by Alice Sherepa [ 2022-09-26 ]

Thank you for the report!
I repeated as described on 10.3-10.10, regression somewhere in a merge revision #9614fde1aac6ffa4745804342ff70a96b2418e30 (maybe after ~~MDEV-27212~~, but I didn't check)

CREATE  FUNCTION bug_demo() RETURNS BOOL RETURN

(WITH cte AS (SELECT a = '"bar"' AS A1 FROM

    ( SELECT JSON_EXTRACT('[{"foo": "bar"}]', '$[0].foo') as a FROM (values(0))dt) dt2)

SELECT A1 FROM cte);

SELECT bug_demo();

SELECT bug_demo();

SELECT bug_demo();

Comment by Rich Theobald [ 2024-02-01 ]

As it has been well over a year since this was reported and fix versions have been steadily removed, can I assume this is an especially problematic issue and will not be address any time soon?

Generated at Thu Feb 08 10:10:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.

[MDEV-29627] Boolean logic is failing using JSON_EXTRACT function with multiple RECURSIVE CTE's Created: 2022-09-24 Updated: 2024-02-02