Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25406

JSON_TABLE: VARBINARY column in view definition causes syntax error and assertion failure

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Done
    • N/A
    • 10.6.0
    • JSON, Views
    • None

    Description

      CREATE VIEW v AS SELECT * FROM JSON_TABLE('{}', '$' COLUMNS(c VARBINARY(8) PATH '$')) AS jt;
      SHOW CREATE VIEW v;
      SHOW TABLE STATUS;
      

      bb-10.6-mdev17399-hf 160bd1691

      MariaDB [test]> SHOW CREATE VIEW v;
      ERROR 1064 (42000): 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 'CHARSET binary PATH '$')) `jt`' at line 1
      

      SHOW TABLE STATUS or queries from I_S.TABLES cause the assertion failure:

      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.
      210413 14:23:30 [ERROR] mysqld got signal 6 ;
       
      #7  0x00007f23a9b80f36 in __GI___assert_fail (assertion=0x55aee9abdc80 "m_status == DA_ERROR || m_status == DA_OK || m_status == DA_OK_BULK", file=0x55aee9abdd00 "/data/src/bb-10.6-mdev17399-hf/sql/sql_error.h", line=1022, function=0x55aee9abdd60 "const char* Diagnostics_area::message() const") at assert.c:101
      #8  0x000055aee7859ae6 in Diagnostics_area::message (this=0x62b00006ef98) at /data/src/bb-10.6-mdev17399-hf/sql/sql_error.h:1022
      #9  0x000055aee7d0155f in get_schema_tables_record (thd=0x62b000069288, tables=0x62d0001ae508, table=0x62300001dda8, res=true, db_name=0x62b00003e188, table_name=0x62b00003e278) at /data/src/bb-10.6-mdev17399-hf/sql/sql_show.cc:5698
      #10 0x000055aee7cf7401 in fill_schema_table_by_open (thd=0x62b000069288, mem_root=0x7f23a087eb80, is_show_fields_or_keys=false, table=0x62300001dda8, schema_table=0x55aeeb1cf120 <schema_tables+2048>, orig_db_name=0x62b00003e188, orig_table_name=0x62b00003e278, open_tables_state_backup=0x7f23a087ebe0, can_deadlock=false) at /data/src/bb-10.6-mdev17399-hf/sql/sql_show.cc:4645
      #11 0x000055aee7cfb0a3 in get_all_tables (thd=0x62b000069288, tables=0x62b00003a598, cond=0x0) at /data/src/bb-10.6-mdev17399-hf/sql/sql_show.cc:5253
      #12 0x000055aee7d2e7e0 in get_schema_tables_result (join=0x62b00003bae8, executed_place=PROCESSED_BY_JOIN_EXEC) at /data/src/bb-10.6-mdev17399-hf/sql/sql_show.cc:8728
      #13 0x000055aee7bfc295 in JOIN::exec_inner (this=0x62b00003bae8) at /data/src/bb-10.6-mdev17399-hf/sql/sql_select.cc:4464
      #14 0x000055aee7bfa138 in JOIN::exec (this=0x62b00003bae8) at /data/src/bb-10.6-mdev17399-hf/sql/sql_select.cc:4287
      #15 0x000055aee7bfe587 in mysql_select (thd=0x62b000069288, tables=0x62b00003a598, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2684619520, result=0x62b00003bab8, unit=0x62b00006d460, select_lex=0x62b00006dc68) at /data/src/bb-10.6-mdev17399-hf/sql/sql_select.cc:4763
      #16 0x000055aee7bcf616 in handle_select (thd=0x62b000069288, lex=0x62b00006d398, result=0x62b00003bab8, setup_tables_done_option=0) at /data/src/bb-10.6-mdev17399-hf/sql/sql_select.cc:419
      #17 0x000055aee7b391dc in execute_sqlcom_select (thd=0x62b000069288, all_tables=0x62b00003a598) at /data/src/bb-10.6-mdev17399-hf/sql/sql_parse.cc:6231
      #18 0x000055aee7b284c1 in mysql_execute_command (thd=0x62b000069288) at /data/src/bb-10.6-mdev17399-hf/sql/sql_parse.cc:3927
      #19 0x000055aee7b44455 in mysql_parse (thd=0x62b000069288, rawbuf=0x62b0000382a8 "show table status", length=17, parser_state=0x7f23a0880bb0) at /data/src/bb-10.6-mdev17399-hf/sql/sql_parse.cc:8005
      #20 0x000055aee7b1aece in dispatch_command (command=COM_QUERY, thd=0x62b000069288, packet=0x629000258289 "show table status", packet_length=17, blocking=true) at /data/src/bb-10.6-mdev17399-hf/sql/sql_parse.cc:1888
      #21 0x000055aee7b17c09 in do_command (thd=0x62b000069288, blocking=true) at /data/src/bb-10.6-mdev17399-hf/sql/sql_parse.cc:1399
      #22 0x000055aee7f5a8fc in do_handle_one_connection (connect=0x61100000b388, put_in_cache=true) at /data/src/bb-10.6-mdev17399-hf/sql/sql_connect.cc:1410
      #23 0x000055aee7f5a259 in handle_one_connection (arg=0x61100000b248) at /data/src/bb-10.6-mdev17399-hf/sql/sql_connect.cc:1312
      #24 0x000055aee8c6e081 in pfs_spawn_thread (arg=0x616000101b08) at /data/src/bb-10.6-mdev17399-hf/storage/perfschema/pfs.cc:2201
      #25 0x00007f23aa098609 in start_thread (arg=<optimized out>) at pthread_create.c:477
      #26 0x00007f23a9c6c293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
      

      frm file

      TYPE=VIEW
      query=select `jt`.`c` AS `c` from JSON_TABLE(\'{}\', \'$\' COLUMNS (`c` varbinary(8) CHARSET binary PATH \'$\')) `jt`
      md5=7464e03c4ce7dc8ed074ec7537d73305
      updatable=1
      algorithm=0
      definer_user=root
      definer_host=localhost
      suid=2
      with_check_option=0
      timestamp=2021-04-13 11:23:57
      create-version=2
      source=SELECT * FROM JSON_TABLE(\'{}\', \'$\' COLUMNS(c VARBINARY(8) PATH \'$\')) AS jt
      client_cs_name=utf8
      connection_cl_name=utf8_general_ci
      view_body_utf8=select `jt`.`c` AS `c` from JSON_TABLE(\'{}\', \'$\' COLUMNS (`c` varbinary(8) CHARSET binary PATH \'$\')) `jt`
      mariadb-version=100600
      

      So, it's the automatically added CHARSET clause that causes the problem.

      Attachments

        Issue Links

          Activity

            People

              holyfoot Alexey Botchkov
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.