|
Thanks for the report!
Reproducible on 10.5
create table t1 (i int,id int);
|
insert into t1 values (7,7),(7,8);
|
|
create view v1 as
|
( select json_arrayagg(json_object( "type", "permperiod", "id", id )) as json_data
|
from t1
|
group by i);
|
|
show create view v1;
|
|
select * from v1 ;
|
|
select json_arrayagg(json_object( "type", "permperiod", "id", id )) as json_data
|
from t1
|
group by i ;
|
|
drop view v1;
|
drop table t1;
|
MariaDB [test]> show create view v1;
|
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
|
| View | Create View | character_set_client | collation_connection |
|
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
|
| v1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select group_concat(json_object('type','permPeriod','id',`t1`.`id`) separator ',') AS `JSON_DATA` from `t1` group by `t1`.`i`) | utf8 | utf8_general_ci |
|
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
|
1 row in set (0.001 sec)
|
|
MariaDB [test]> select * from v1 ;
|
+-----------------------------------------------------------------+
|
| JSON_DATA |
|
+-----------------------------------------------------------------+
|
| {"type": "permPeriod", "id": 7},{"type": "permPeriod", "id": 8} |
|
+-----------------------------------------------------------------+
|
1 row in set (0.002 sec)
|
|
MariaDB [test]> SELECT json_arrayagg(json_object( "type", "permPeriod", "id", ID )) AS JSON_DATA
|
-> FROM t1
|
-> GROUP BY i ;
|
+-------------------------------------------------------------------+
|
| JSON_DATA |
|
+-------------------------------------------------------------------+
|
| [{"type": "permPeriod", "id": 7},{"type": "permPeriod", "id": 8}] |
|
+-------------------------------------------------------------------+
|
1 row in set (0.004 sec)
|
|
|
I might be mistaken, but after upgrading my MariaDB to 10.5.8, this issue is back again?
I am not using it in a view, but just a regular select:
SELECT
trid,
JSON_ARRAYAGG(
JSON_OBJECT(
'teid', teid,
'tename', tename,
'trid', trid,
'trname', trname,
'medium', temedium,
'contacttype', te.tecontacttype,
'stoptype', te.testop,
'contactid', te.tecontactid,
'templateid', tt.ttid,
'templatename', tt.ttname,
'templatesubject', tt.ttsubject,
'templatetext', tt.tttext
)
) as trevents
FROM trackingrules tr
RIGHT JOIN trackingevents te ON te.tetrid = tr.trid AND te.teactive = 1 AND te.temedium > 0
LEFT JOIN trackingtemplates tt ON tt.ttid = te.tetemplateid
WHERE tr.trisactive = 1
GROUP BY trid
|