Details
Major Description
Expected Behavior: Special characters should not cause NULL values when using JSON_ARRAYAGG.
Actual Behavior: Aggregated values return NULL when there are special characters in article_code, especially with GROUP BY.
Test Case: Special Characters in article_code with JSON_ARRAYAGG and GROUP BY
*1. Setup: *Create Tables and Sample Data
-- Create a sample 'resources' table |
CREATE TABLE resources (
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
article_code VARCHAR(255) NOT NULL |
);
|
|
|
-- Create a sample 'collaborations' table with metadata |
CREATE TABLE collaborations (
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
metadata JSON
|
);
|
|
|
-- Insert sample data with special characters in 'article_code' |
INSERT INTO resources (article_code) VALUES
|
('NormalCode'), |
('Special-ÄÖÜ'), |
('Läk-Und'); |
|
|
-- Insert sample collaboration data with recurring metadata
|
INSERT INTO collaborations (metadata) VALUES
|
('{"recurring": "30"}'), |
('{"recurring": "365"}'), |
('{"recurring": "30"}'); |
2. Test the Query without GROUP BY
First, test the query without GROUP BY to show that JSON_ARRAYAGG works as expected.
SELECT
|
COALESCE(JSON_UNQUOTE(JSON_EXTRACT(collaborations.metadata, '$.recurring')), '365') AS recurring, |
JSON_ARRAYAGG(DISTINCT resources.article_code) AS exercises
|
FROM
|
resources
|
JOIN
|
collaborations ON collaborations.id = resources.id;
|
Expected Output:
recurring | exercises
|
-------------------------------
|
30 | ["NormalCode","Special-ÄÖÜ","Läk-Und"] |
365 | ["Läk-Und"] |
3. Test the Query with GROUP BY
Next, run the query with GROUP BY to show the issue with special characters.
SELECT
|
COALESCE(JSON_UNQUOTE(JSON_EXTRACT(collaborations.metadata, '$.recurring')), '365') AS recurring, |
JSON_ARRAYAGG(DISTINCT resources.article_code) AS exercises
|
FROM
|
resources
|
JOIN
|
collaborations ON collaborations.id = resources.id
|
GROUP BY
|
recurring;
|
Expected Behavior:
The special characters should not cause any issues, and the article codes should be aggregated correctly.
Actual Output:
The query may return NULL for the exercises field or fail when there are special characters (like Ä, Ö, Ü, or -) in article_code.
4. Possible Workaround
Using TO_BASE64 to handle special characters and then decoding them:
SELECT
|
JSON_ARRAYAGG(DISTINCT FROM_BASE64(TO_BASE64(resources.article_code))) AS exercises
|
FROM
|
resources;
|
|