[MDEV-23029] JSON_OBJECTAGG returns NULL when used together with GROUP BY Created: 2020-06-26  Updated: 2020-07-18  Resolved: 2020-06-29

Status: Closed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.5.4
Fix Version/s: 10.5.5

Type: Bug Priority: Major
Reporter: Markus Winand Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 0
Labels: JSON_OBJECTAGG
Environment:

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.

 Comments   
Comment by Varun Gupta (Inactive) [ 2020-06-29 ]

On debug builds the crash happens here:

(lldb) bt
 
* thread #2, stop reason = signal SIGABRT
 
  * frame #0: 0x00007fff730707fa libsystem_kernel.dylib`__pthread_kill + 10
 
    frame #1: 0x00007fff7312dbc1 libsystem_pthread.dylib`pthread_kill + 432
 
    frame #2: 0x00007fff72ff7a1c libsystem_c.dylib`abort + 120
 
    frame #3: 0x00007fff72ff6cd6 libsystem_c.dylib`__assert_rtn + 314
 
    frame #4: 0x0000000101798640 mysqld`Item_func_json_objectagg::reset_field(this=0x000062b000062c20) at item_jsonfunc.h:600:24
 
    frame #5: 0x000000010112e487 mysqld`init_tmptable_sum_functions(func_ptr=0x000062b000064810) at sql_select.cc:25592:11
 
    frame #6: 0x00000001010c71af mysqld`end_update(join=0x000062b000064030, join_tab=0x000062b000065818, end_of_records=false) at sql_select.cc:22128:3
 
    frame #7: 0x00000001011037fe mysqld`AGGR_OP::put_record(this=0x000062b000066310, end_of_records=false) at sql_select.cc:28796:30
 
    frame #8: 0x00000001010d0f57 mysqld`AGGR_OP::put_record(this=0x000062b000066310) at sql_select.h:1048:48
 
    frame #9: 0x000000010103cb89 mysqld`sub_select_postjoin_aggr(join=0x000062b000064030, join_tab=0x000062b000065818, end_of_records=false) at sql_select.cc:20316:13
 
    frame #10: 0x00000001010d3122 mysqld`evaluate_join_record(join=0x000062b000064030, join_tab=0x000062b000065470, error=0) at sql_select.cc:20823:11
 
    frame #11: 0x00000001010d01b2 mysqld`sub_select(join=0x000062b000064030, join_tab=0x000062b000065470, end_of_records=false) at sql_select.cc:20600:9
 
    frame #12: 0x0000000101056314 mysqld`do_select(join=0x000062b000064030, procedure=0x0000000000000000) at sql_select.cc:20134:14
 
    frame #13: 0x0000000101051e05 mysqld`JOIN::exec_inner(this=0x000062b000064030) at sql_select.cc:4444:50
 
    frame #14: 0x000000010104dc25 mysqld`JOIN::exec(this=0x000062b000064030) at sql_select.cc:4225:3
 
    frame #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:9
 
    frame #16: 0x0000000100fb756f mysqld`handle_select(thd=0x000062b00005b288, lex=0x000062b00005f208, result=0x000062b000064000, setup_tables_done_option=0) at sql_select.cc:417:10

The 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.

Comment by Varun Gupta (Inactive) [ 2020-06-29 ]

Patch
http://lists.askmonty.org/pipermail/commits/2020-June/014276.html

Comment by Sergei Petrunia [ 2020-06-29 ]

Ok to push.

Comment by Varun Gupta (Inactive) [ 2020-06-29 ]

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;

Comment by Roel Van de Paar [ 2020-07-14 ] 

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.

Comment by Varun Gupta (Inactive) [ 2020-07-14 ]

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)

Comment by Roel Van de Paar [ 2020-07-14 ]

Thank you varun!

Comment by Zmegolaz [ 2020-07-18 ]

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

Generated at Thu Feb 08 09:19:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.