Error after migrating from Mysql 5.7 to MariaDb 10.6.5-CodePudding

I am using PHP-MySQLi-Database-Class to construct the queries, it is working fine on MySQL but after switching to a MariaDB database a single query in specific is throwing this error: (sorry for the long text)

*Uncaught Exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'JSON) FROM attributes AS _sub_attr WHERE JSON_EXTRACT(sub_prop_attr.attrib_te...' at line 1 query: SELECT ,p.id AS id, DATEDIFF(CURDATE(),DATE(p.created)) AS days, COALESCE(bed_room,bath_room,parking,construction_size,lot_size,floors,building_age) AS chs , (SELECT JSON_OBJECTAGG(attribute_name, (SELECT CAST(CONCAT('[',GROUP_CONCAT(JSON_QUOTE(_sub_attr.attribute_name)),']') AS JSON) FROM attributes AS _sub_attr WHERE JSON_EXTRACT(sub_prop_attr.attrib_terms, JSON_UNQUOTE(JSON_SEARCH(sub_prop_attr.attrib_terms, 'one', _sub_attr.id))) IS NOT NULL)) FROM prop_attributes AS sub_prop_attr LEFT JOIN attributes AS sub_attr ON sub_prop_attr.attribute_id = sub_attr.id WHERE prop_id = p.id) AS JSON_attributes FROM properties p LEFT JOIN (SELECT id, state_name, CONCAT("en ",state_name) AS state_alias FROM states) s on p.state=s.id LEFT JOIN (SELECT id, city_na in C:\wamp64...\classes\MysqliDb.php on line 1819

...And this is the query:

SELECT *,p.id AS id, DATEDIFF(CURDATE(),DATE(p.created)) AS days, 
    COALESCE(bed_room,bath_room,parking,construction_size,lot_size,floors,building_age) AS chs ,
    (SELECT JSON_OBJECTAGG(attribute_name, 
        (SELECT CAST(CONCAT(\'[\',GROUP_CONCAT(JSON_QUOTE(_sub_attr.attribute_name)),\']\') AS JSON) 
        FROM attributes AS _sub_attr 
        WHERE JSON_EXTRACT(sub_prop_attr.attrib_terms, JSON_UNQUOTE(JSON_SEARCH(sub_prop_attr.attrib_terms, \'one\', _sub_attr.id))) IS NOT NULL)) 
    FROM prop_attributes AS sub_prop_attr LEFT JOIN attr';

This is the MySQL query:

$Srelated_attributes = ", (SELECT JSON_OBJECTAGG(attribute_name, (SELECT CAST(CONCAT('[',GROUP_CONCAT(JSON_QUOTE(_sub_attr.attribute_name)),']') AS JSON) FROM attributes AS _sub_attr WHERE JSON_EXTRACT(sub_prop_attr.attrib_terms, JSON_UNQUOTE(JSON_SEARCH(sub_prop_attr.attrib_terms, 'one', _sub_attr.id))) IS NOT NULL)) FROM prop_attributes AS sub_prop_attr LEFT JOIN attributes AS sub_attr ON sub_prop_attr.attribute_id = sub_attr.id WHERE prop_id = p.id) AS JSON_attributes";

The last is part of a long query and if I take out that part it would work... I think it may be related to quotes or maybe JSON functions but I haven't find any nice solution to make that same code work.

Do any of you have had a related experience? Hope I was clear with my problem description.

I expect I can use my query. Also I've tried erasing the part of the query which I quoted in the issue description and the error disappears so I the problem may be arround that part of the query.

CodePudding user response：

Because JSON isn't a type in the SQL standard, MariaDB hasn't implemented the CAST(... AS JSON) as its not a type.

If you remove those words and the CAST operator will behave in the same way.