Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
11.4, 11.8, 12.3, 13.1, 12.3.3
-
Linux/amd64
Description
For rows containing [40] and [50,60], a constant '$[last]' path makes JSON_CONTAINS_PATH return 0 and JSON_EXTRACT return NULL. The equivalent column-supplied path correctly returns 1 and extracts 40 or 60. The same mismatch occurs in a direct query and both executions of a prepared statement on MariaDB 12.3.3 (Linux/amd64).
Run the following SQL:
CREATE DATABASE json_paths; |
USE json_paths; |
CREATE TABLE documents (pk INT PRIMARY KEY,j VARCHAR(100),p VARCHAR(30),expected_contains INT,expected_extract VARCHAR(10)); |
INSERT INTO documents VALUES |
(1,'[10,20,30]','$[last]',1,'30'),(2,'[40]','$[last]',1,'40'), |
(3,'[50,60]','$[last]',1,'60'),(4,'[]','$[last]',0,NULL); |
SELECT 'paths' AS phase,pk, |
JSON_CONTAINS_PATH(j,'one','$[last]') AS const_contains, |
JSON_CONTAINS_PATH(j,'one',p) AS dynamic_contains,expected_contains, |
JSON_EXTRACT(j,'$[last]') AS const_extract, |
JSON_EXTRACT(j,p) AS dynamic_extract,expected_extract |
FROM documents FORCE INDEX(PRIMARY) ORDER BY pk; |
PREPARE read_last_element FROM 'SELECT ''paths'' AS phase,pk, |
JSON_CONTAINS_PATH(j,''one'',''$[last]'') AS const_contains, |
JSON_CONTAINS_PATH(j,''one'',p) AS dynamic_contains,expected_contains, |
JSON_EXTRACT(j,''$[last]'') AS const_extract, |
JSON_EXTRACT(j,p) AS dynamic_extract,expected_extract
|
FROM documents FORCE INDEX(PRIMARY) ORDER BY pk'; |
EXECUTE read_last_element; |
EXECUTE read_last_element; |
DEALLOCATE PREPARE read_last_element; |
DROP DATABASE json_paths; |
SELECT 'cleanup' AS phase,COUNT(*) AS remaining FROM information_schema.SCHEMATA WHERE SCHEMA_NAME='json_paths'; |
The direct query and both prepared executions produce these result rows:
phase pk const_contains dynamic_contains expected_contains const_extract dynamic_extract expected_extract
|
paths 1 1 1 1 30 30 30
|
paths 2 0 1 1 NULL 40 40
|
paths 3 0 1 1 NULL 60 60
|
paths 4 0 0 0 NULL NULL NULL
|
For each document, '$[last]' denotes its final array element, so a constant path and the equivalent column-supplied path must agree. Rows 2 and 3 should have const_contains=1 and const_extract=40 or 60, respectively, during both direct and prepared execution.
The source evidence suggests that the parsed constant path is cached while document-dependent array-length metadata is not refreshed for each row. This is a source-based hypothesis; the reproduced result mismatch does not establish the internal cause.
This occurs when a constant $[last] JSON path is evaluated across rows containing arrays of different lengths, including repeated prepared execution.
Attachments
Issue Links
- relates to
-
-
- In Review
-