[MDEV-25230] JSON_TABLE: CREATE VIEW with 2nd level NESTED PATH ends up with invalid frm, Assertion `m_status == DA_ERROR || m_status == DA_OK || m_status == DA_OK_BULK' failed Created: 2021-03-23  Updated: 2021-04-21  Resolved: 2021-03-24

Status: Closed
Project: MariaDB Server
Component/s: JSON, Views
Affects Version/s: N/A
Fix Version/s: 10.6.0

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Alexey Botchkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-17399 Add support for JSON_TABLE Closed

 Description    

CREATE VIEW v AS SELECT * FROM JSON_TABLE('{}', '$' COLUMNS(NESTED PATH '$' COLUMNS(NESTED PATH '$.*' COLUMNS(o FOR ORDINALITY)))) AS jt;
 
SELECT * FROM v;

bb-10.6-mdev17399-hf 49603a0191

 
'SELECT * FROM v' failed: 1064: 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 ')) `jt`' at line 1

frm

 
TYPE=VIEW
 
query=select `jt`.`o` AS `o` from JSON_TABLE(\'{}\', \'$\' COLUMNS ()) `jt`
 
md5=f253a6b293f2eaad473af1906b3c1f1a
 
updatable=1
 
algorithm=0
 
definer_user=root
 
definer_host=localhost
 
suid=2
 
with_check_option=0
 
timestamp=2021-03-23 15:15:37
 
create-version=2
 
source=SELECT * FROM JSON_TABLE(\'{}\', \'$\' COLUMNS(NESTED PATH \'$\' COLUMNS(NESTED PATH \'$.*\' COLUMNS(o FOR ORDINALITY)))) AS jt
 
client_cs_name=latin1
 
connection_cl_name=latin1_swedish_ci
 
view_body_utf8=select `jt`.`o` AS `o` from JSON_TABLE(\'{}\', \'$\' COLUMNS ()) `jt`
 
mariadb-version=100600



 Comments   
Comment by Elena Stepanova [ 2021-03-23 ]

As customary in such cases, an immediate consequence is the assertion failure:

CREATE VIEW v AS SELECT * FROM JSON_TABLE('{}', '$' COLUMNS(NESTED PATH '$' COLUMNS(NESTED PATH '$.*' COLUMNS(o FOR ORDINALITY)))) AS jt;
 
SELECT * FROM INFORMATION_SCHEMA.TABLES;


mariadbd: /data/src/bb-10.6-mdev17399-hf/sql/sql_error.h:1022: const char* Diagnostics_area::message() const: Assertion `m_status == DA_ERROR || m_status == DA_OK || m_status == DA_OK_BULK' failed.
 
 
 
#7  0x00007f940b1f7f36 in __GI___assert_fail (assertion=0x55a0810431c0 "m_status == DA_ERROR || m_status == DA_OK || m_status == DA_OK_BULK", file=0x55a081043240 "/data/src/bb-10.6-mdev17399-hf/sql/sql_error.h", line=1022, function=0x55a0810432a0 "const char* Diagnostics_area::message() const") at assert.c:101
 
#8  0x000055a07edfa94a in Diagnostics_area::message (this=0x62b00006ef98) at /data/src/bb-10.6-mdev17399-hf/sql/sql_error.h:1022
 
#9  0x000055a07f2a118c in get_schema_tables_record (thd=0x62b000069288, tables=0x62d0001a4508, table=0x62300001dda8, res=true, db_name=0x629000254610, table_name=0x629000254650) at /data/src/bb-10.6-mdev17399-hf/sql/sql_show.cc:5705
 
#10 0x000055a07f29702e in fill_schema_table_by_open (thd=0x62b000069288, mem_root=0x7f9402037780, is_show_fields_or_keys=false, table=0x62300001dda8, schema_table=0x55a08274d6e0 <schema_tables+2048>, orig_db_name=0x629000254610, orig_table_name=0x629000254650, open_tables_state_backup=0x7f94020377e0, can_deadlock=false) at /data/src/bb-10.6-mdev17399-hf/sql/sql_show.cc:4652
 
#11 0x000055a07f29acd0 in get_all_tables (thd=0x62b000069288, tables=0x62b0000389d0, cond=0x0) at /data/src/bb-10.6-mdev17399-hf/sql/sql_show.cc:5260
 
#12 0x000055a07f2ce265 in get_schema_tables_result (join=0x62b00003a590, executed_place=PROCESSED_BY_JOIN_EXEC) at /data/src/bb-10.6-mdev17399-hf/sql/sql_show.cc:8731
 
#13 0x000055a07f19c1eb in JOIN::exec_inner (this=0x62b00003a590) at /data/src/bb-10.6-mdev17399-hf/sql/sql_select.cc:4448
 
#14 0x000055a07f19a08e in JOIN::exec (this=0x62b00003a590) at /data/src/bb-10.6-mdev17399-hf/sql/sql_select.cc:4271
 
#15 0x000055a07f19e4dd in mysql_select (thd=0x62b000069288, tables=0x62b0000389d0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2684619520, result=0x62b00003a560, unit=0x62b00006d460, select_lex=0x62b000038360) at /data/src/bb-10.6-mdev17399-hf/sql/sql_select.cc:4747
 
#16 0x000055a07f16f86a in handle_select (thd=0x62b000069288, lex=0x62b00006d398, result=0x62b00003a560, setup_tables_done_option=0) at /data/src/bb-10.6-mdev17399-hf/sql/sql_select.cc:417
 
#17 0x000055a07f0d9a0a in execute_sqlcom_select (thd=0x62b000069288, all_tables=0x62b0000389d0) at /data/src/bb-10.6-mdev17399-hf/sql/sql_parse.cc:6230
 
#18 0x000055a07f0c8cec in mysql_execute_command (thd=0x62b000069288) at /data/src/bb-10.6-mdev17399-hf/sql/sql_parse.cc:3926
 
#19 0x000055a07f0e4cb5 in mysql_parse (thd=0x62b000069288, rawbuf=0x62b0000382a8 "SELECT * FROM INFORMATION_SCHEMA.TABLES", length=39, parser_state=0x7f94020397b0) at /data/src/bb-10.6-mdev17399-hf/sql/sql_parse.cc:8000
 
#20 0x000055a07f0bb6f0 in dispatch_command (command=COM_QUERY, thd=0x62b000069288, packet=0x62900024e289 "SELECT * FROM INFORMATION_SCHEMA.TABLES", packet_length=39, blocking=true) at /data/src/bb-10.6-mdev17399-hf/sql/sql_parse.cc:1886
 
#21 0x000055a07f0b842b in do_command (thd=0x62b000069288, blocking=true) at /data/src/bb-10.6-mdev17399-hf/sql/sql_parse.cc:1397
 
#22 0x000055a07f4f9ecd in do_handle_one_connection (connect=0x61100000ad48, put_in_cache=true) at /data/src/bb-10.6-mdev17399-hf/sql/sql_connect.cc:1410
 
#23 0x000055a07f4f982a in handle_one_connection (arg=0x61100000ac08) at /data/src/bb-10.6-mdev17399-hf/sql/sql_connect.cc:1312
 
#24 0x000055a08020417f in pfs_spawn_thread (arg=0x616000102a08) at /data/src/bb-10.6-mdev17399-hf/storage/perfschema/pfs.cc:2201
 
#25 0x00007f940b70f609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#26 0x00007f940b2e3293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

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