Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.5.4
-
Built from source.
Description
I might be missing somthing totally trivial, but the following example doesn't look correct to me:
(psergey: fixed formatting)
CREATE TABLE t ( |
e INTEGER, |
a VARCHAR(255), |
v VARCHAR(255) |
);
|
|
|
INSERT INTO t VALUES (0, 'a1', '1') |
, (0, 'a2', '2') |
, (1, 'b1', '3'); |
|
|
SELECT e, JSON_OBJECTAGG(a, v) |
FROM t |
GROUP BY e; |
This is what I get:
MariaDB 5.5.5-10.5.4-MariaDB> SELECT e, JSON_OBJECTAGG(a, v)
|
-> FROM t
|
-> GROUP BY e;
|
+------+----------------------+
|
| e | JSON_OBJECTAGG(a, v) |
|
+------+----------------------+
|
| 0 | NULL |
|
| 1 | NULL |
|
+------+----------------------+
|
2 rows in set (0.00 sec)
|
The second column is NULL, although it should contain the actual data (like MySQL does).
Removing the GROUP BY yields a correct result.
Attachments
Activity
Checked against mysql and then even don't use an index on the temp table for group by items and then update the correspoding rows,
Here is the comment from mysql explaining it too
/** |
This will force tmp table to NOT use index + update for group
|
operation as it'll cause [de]serialization for each json aggregated
|
value and is very ineffective (times worse).
|
Server should use filesort, or tmp table + filesort to resolve GROUP BY
|
with JSON aggregate functions.
|
*/
|
bool with_json_agg;
|
USE test;
|
CREATE TABLE t (a INT PRIMARY KEY) ENGINE=InnoDB;
|
INSERT INTO t VALUES (2);
|
SELECT a % 2 AS i, JSON_OBJECTAGG(a,a) FROM t GROUP BY i;
|
Leads to:
|
10.5.5 e1013725ce0f3f947e728491eef75d9985e8db2f |
mysqld: /test/10.5_dbg/sql/item_jsonfunc.h:599: virtual void Item_func_json_objectagg::reset_field(): Assertion `0' failed.
|
|
10.5.5 e1013725ce0f3f947e728491eef75d9985e8db2f |
Core was generated by `/test/MD250620-mariadb-10.5.5-linux-x86_64-dbg/bin/mysqld --no-defaults --core-'.
|
Program terminated with signal SIGABRT, Aborted.
|
#0 __pthread_kill (threadid=<optimized out>, signo=signo@entry=6)
|
at ../sysdeps/unix/sysv/linux/pthread_kill.c:57
|
[Current thread is 1 (Thread 0x1463a8c3d700 (LWP 588632))]
|
(gdb) bt
|
#0 __pthread_kill (threadid=<optimized out>, signo=signo@entry=6) at ../sysdeps/unix/sysv/linux/pthread_kill.c:57
|
#1 0x000055fc96ed27d0 in my_write_core (sig=sig@entry=6) at /test/10.5_dbg/mysys/stacktrace.c:518
|
#2 0x000055fc9668b47a in handle_fatal_signal (sig=6) at /test/10.5_dbg/sql/signal_handler.cc:330
|
#3 <signal handler called>
|
#4 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:51
|
#5 0x00001463a6ed38b1 in __GI_abort () at abort.c:79
|
#6 0x00001463a6ec342a in __assert_fail_base (fmt=0x1463a704aa38 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=assertion@entry=0x55fc9720c66f "0", file=file@entry=0x55fc9722e3b0 "/test/10.5_dbg/sql/item_jsonfunc.h", line=line@entry=599, function=function@entry=0x55fc9722d940 <Item_func_json_objectagg::reset_field()::__PRETTY_FUNCTION__> "virtual void Item_func_json_objectagg::reset_field()") at assert.c:92
|
#7 0x00001463a6ec34a2 in __GI___assert_fail (assertion=assertion@entry=0x55fc9720c66f "0", file=file@entry=0x55fc9722e3b0 "/test/10.5_dbg/sql/item_jsonfunc.h", line=line@entry=599, function=function@entry=0x55fc9722d940 <Item_func_json_objectagg::reset_field()::__PRETTY_FUNCTION__> "virtual void Item_func_json_objectagg::reset_field()") at assert.c:101
|
#8 0x000055fc968902f1 in Item_func_json_objectagg::reset_field (this=<optimized out>) at /test/10.5_dbg/sql/item_jsonfunc.h:599
|
#9 0x000055fc96418a2d in init_tmptable_sum_functions (func_ptr=0x146385076698) at /test/10.5_dbg/sql/sql_select.cc:25592
|
#10 0x000055fc96438976 in end_update (join=0x146385075e90, join_tab=0x146385077838, end_of_records=false) at /test/10.5_dbg/sql/sql_select.cc:22128
|
#11 0x000055fc9643dc43 in AGGR_OP::put_record (this=this@entry=0x146385078298, end_of_records=end_of_records@entry=false) at /test/10.5_dbg/sql/sql_select.cc:28797
|
#12 0x000055fc9643e2f2 in AGGR_OP::put_record (this=0x146385078298) at /test/10.5_dbg/sql/sql_select.h:1048
|
#13 sub_select_postjoin_aggr (join=0x146385075e90, join_tab=0x146385077838, end_of_records=<optimized out>) at /test/10.5_dbg/sql/sql_select.cc:20316
|
#14 0x000055fc9641eea5 in evaluate_join_record (join=join@entry=0x146385075e90, join_tab=join_tab@entry=0x146385077490, error=error@entry=0) at /test/10.5_dbg/sql/sql_select.cc:20823
|
#15 0x000055fc964261cf in sub_select (join=0x146385075e90, join_tab=0x146385077490, end_of_records=<optimized out>) at /test/10.5_dbg/sql/sql_select.cc:20600
|
#16 0x000055fc9645f150 in do_select (procedure=0x0, join=0x146385075e90) at /test/10.5_dbg/sql/sql_select.cc:20134
|
#17 JOIN::exec_inner (this=this@entry=0x146385075e90) at /test/10.5_dbg/sql/sql_select.cc:4444
|
#18 0x000055fc9645f76b in JOIN::exec (this=this@entry=0x146385075e90) at /test/10.5_dbg/sql/sql_select.cc:4225
|
#19 0x000055fc9645da80 in mysql_select (thd=thd@entry=0x146385015088, tables=<optimized out>, fields=@0x1463850742c8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x1463850748a8, last = 0x146385074c98, elements = 2}, <No data fields>}, conds=0x0, og_num=1, order=<optimized out>, group=0x146385075528, having=0x0, proc_param=0x0, select_options=2147748608, result=0x146385075e68, unit=0x1463850190a0, select_lex=0x146385074178) at /test/10.5_dbg/sql/sql_select.cc:4649
|
#20 0x000055fc9645ddaf in handle_select (thd=thd@entry=0x146385015088, lex=lex@entry=0x146385018fd8, result=result@entry=0x146385075e68, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/10.5_dbg/sql/sql_select.cc:417
|
#21 0x000055fc963e76c9 in execute_sqlcom_select (thd=thd@entry=0x146385015088, all_tables=0x146385074cf8) at /test/10.5_dbg/sql/sql_parse.cc:6211
|
#22 0x000055fc963e07c2 in mysql_execute_command (thd=thd@entry=0x146385015088) at /test/10.5_dbg/sql/sql_parse.cc:3939
|
#23 0x000055fc963ed638 in mysql_parse (thd=thd@entry=0x146385015088, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x1463a8c3c350, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /test/10.5_dbg/sql/sql_parse.cc:7995
|
#24 0x000055fc963da110 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x146385015088, packet=packet@entry=0x146385067089 "", packet_length=packet_length@entry=56, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /test/10.5_dbg/sql/sql_parse.cc:1874
|
#25 0x000055fc963d88ea in do_command (thd=0x146385015088) at /test/10.5_dbg/sql/sql_parse.cc:1355
|
#26 0x000055fc9653415f in do_handle_one_connection (connect=<optimized out>, connect@entry=0x146387cd2808, put_in_cache=put_in_cache@entry=true) at /test/10.5_dbg/sql/sql_connect.cc:1411
|
#27 0x000055fc9653487b in handle_one_connection (arg=arg@entry=0x146387cd2808) at /test/10.5_dbg/sql/sql_connect.cc:1313
|
#28 0x000055fc9699711c in pfs_spawn_thread (arg=0x1463a5846508) at /test/10.5_dbg/storage/perfschema/pfs.cc:2201
|
#29 0x00001463a7bb66db in start_thread (arg=0x1463a8c3d700) at pthread_create.c:463
|
#30 0x00001463a6fb4a3f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
|
Bug confirmed present in:
MariaDB: 10.5.5 (dbg)
Bug confirmed not present in:
MariaDB: 10.1.46 (dbg), 10.1.46 (opt), 10.2.33 (dbg), 10.2.33 (opt), 10.3.24 (dbg), 10.3.24 (opt), 10.4.14 (dbg), 10.4.14 (opt), 10.5.5 (opt)
MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.47 (dbg), 5.6.47 (opt), 5.7.29 (dbg), 5.7.29 (opt), 8.0.19 (dbg), 8.0.19 (opt)
Not tested if it is fixed by the above patch yet.
The above test case is fixed by the patch, here is the demonstration
MariaDB [test]> CREATE TABLE t (a INT PRIMARY KEY) ENGINE=InnoDB;
|
Query OK, 0 rows affected (0.009 sec)
|
|
|
MariaDB [test]> INSERT INTO t VALUES (2);
|
Query OK, 1 row affected (0.016 sec)
|
|
|
MariaDB [test]> SELECT a % 2 AS i, JSON_OBJECTAGG(a,a) FROM t GROUP BY i;
|
+------+---------------------+
|
| i | JSON_OBJECTAGG(a,a) |
|
+------+---------------------+
|
| 0 | {"2":2} |
|
+------+---------------------+
|
1 row in set (0.003 sec)
|
|
A workaround is to have GROUP_CONCAT() in the same query, doesn't really matter for which columns.
See: https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=c3b651fbd5ee266d8432cf7f70a96ed9
On debug builds the crash happens here:
(lldb) bt* thread #2, stop reason = signal SIGABRT* frame #0: 0x00007fff730707fa libsystem_kernel.dylib`__pthread_kill + 10frame #1: 0x00007fff7312dbc1 libsystem_pthread.dylib`pthread_kill + 432frame #2: 0x00007fff72ff7a1c libsystem_c.dylib`abort + 120frame #3: 0x00007fff72ff6cd6 libsystem_c.dylib`__assert_rtn + 314frame #4: 0x0000000101798640 mysqld`Item_func_json_objectagg::reset_field(this=0x000062b000062c20) at item_jsonfunc.h:600:24frame #5: 0x000000010112e487 mysqld`init_tmptable_sum_functions(func_ptr=0x000062b000064810) at sql_select.cc:25592:11frame #6: 0x00000001010c71af mysqld`end_update(join=0x000062b000064030, join_tab=0x000062b000065818, end_of_records=false) at sql_select.cc:22128:3frame #7: 0x00000001011037fe mysqld`AGGR_OP::put_record(this=0x000062b000066310, end_of_records=false) at sql_select.cc:28796:30frame #8: 0x00000001010d0f57 mysqld`AGGR_OP::put_record(this=0x000062b000066310) at sql_select.h:1048:48frame #9: 0x000000010103cb89 mysqld`sub_select_postjoin_aggr(join=0x000062b000064030, join_tab=0x000062b000065818, end_of_records=false) at sql_select.cc:20316:13frame #10: 0x00000001010d3122 mysqld`evaluate_join_record(join=0x000062b000064030, join_tab=0x000062b000065470, error=0) at sql_select.cc:20823:11frame #11: 0x00000001010d01b2 mysqld`sub_select(join=0x000062b000064030, join_tab=0x000062b000065470, end_of_records=false) at sql_select.cc:20600:9frame #12: 0x0000000101056314 mysqld`do_select(join=0x000062b000064030, procedure=0x0000000000000000) at sql_select.cc:20134:14frame #13: 0x0000000101051e05 mysqld`JOIN::exec_inner(this=0x000062b000064030) at sql_select.cc:4444:50frame #14: 0x000000010104dc25 mysqld`JOIN::exec(this=0x000062b000064030) at sql_select.cc:4225:3frame #15: 0x0000000100fb8e87 mysqld`mysql_select(thd=0x000062b00005b288, tables=0x000062b000062e20, fields=0x000062b0000624d0, conds=0x0000000000000000, og_num=1, order=0x0000000000000000, group=0x000062b000063678, having=0x0000000000000000, proc_param=0x0000000000000000, select_options=2147748608, result=0x000062b000064000, unit=0x000062b00005f2d0, select_lex=0x000062b000062380) at sql_select.cc:4649:9frame #16: 0x0000000100fb756f mysqld`handle_select(thd=0x000062b00005b288, lex=0x000062b00005f208, result=0x000062b000064000, setup_tables_done_option=0) at sql_select.cc:417:10The reset_field and update_field is not implemented in Item_func_json_objectagg. The same is for Item_func_json_arrayagg, so we should make sure that quick_grouping is not done, that is we don't create an index over grouping fields and update the result in the temp table, instead we should first sort the result in accordance to the GROUP BY fields and then perform the grouping and write the result to the temp table.