MariaDB Query EXISTS and BETWEEN

Question

I want to Filter about Events where the Distance is between a Range

I use 10.6.15-MariaDB

DB Structure is like:

event_id, event_title, event_distances
1, event1, [{"order":0,"value":21}]
2, event2, [{"order":0,"value":8}]
3, event3, [{"order":0,"value":10}]
4, event4, [{"order":0,"value":5},{"order":1,"value":10},{"order":2,"value":21}]

I try with Query:

SELECT events.event_title, event_distances 
FROM events 
WHERE 
EXISTS ( 
    SELECT 1 
    FROM JSON_TABLE(event_distances, '$[*]' COLUMNS (distance_value FLOAT PATH '$.value')) AS distances 
    WHERE distances.distance_value BETWEEN 9 AND 22
) 
ORDER BY events.event_id ASC;

but I get strange returns, with value between 0 and 22 I get all Events (which is correct, but for example with value between 20 and 22 no rows returns.

Maybe someone can help me to find the problem on my Query?

Answer 1

Unfortunately its a current bug MDEV-30623 that has not been fixed yet.

Answer 2

I've worked around the Bug for now by using a JOIN

SELECT events.* FROM events JOIN JSON_TABLE(event_distances, '$[*]' COLUMNS (distance_value FLOAT PATH '$.value')) AS distances ON distances.distance_value BETWEEN $minDistance AND $maxDistance

For now i can't see any Problems with this...