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

Creating table with certain generated column crashes server

Details

    Description

      Consider the following table definition:

      CREATE TABLE crash_test_1 (
      DATA_VALUE CHAR(10) NULL,
      HAS_DATA BIT NOT NULL,
      TEST_COLUMN CHAR(10) AS (CASE WHEN HAS_DATA = 1 THEN DATA_VALUE ELSE NULL END) STORED
      );

      On creating or selecting from this table, I receive the following warning:

      Function or expression 'case when "HAS_DATA" = 1 then "DATA VALUE" else NULL end' cannot be used in the GENERATED ALWAYS AS clause of `TEST_COLUMN`
      Warning: Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH

      So, as per the documentation, I tried using RPAD:

      CREATE TABLE crash_test_2 (
      DATA_VALUE CHAR(10) NULL,
      HAS_DATA BIT NOT NULL,
      TEST_COLUMN CHAR(10) AS (RPAD(CASE WHEN HAS_DATA = 1 THEN DATA_VALUE ELSE NULL END, 10)) STORED
      );

      Here is the real problem. Attempting to create crash_test_2 causes the server to crash. Some info from the error log:

      [ERROR] mysqld got exception 0xc0000005 ;

      mysqld.exe!Item_func_rpad::value_depends_on_sql_mode()[item_strfunc.cc:3262]
      mysqld.exe!Field::check_vcol_sql_mode_dependency()[field.cc:1411]
      mysqld.exe!parse_vcol_defs()[table.cc:1185]
      mysqld.exe!open_table_from_share()[table.cc:3846]
      mysqld.exe!ha_create_table()[handler.cc:5237]
      mysqld.exe!create_table_impl()[sql_table.cc:5094]
      mysqld.exe!mysql_create_table_no_lock()[sql_table.cc:5182]
      mysqld.exe!mysql_create_table()[sql_table.cc:5274]
      mysqld.exe!Sql_cmd_create_table_like::execute()[sql_table.cc:11503]
      mysqld.exe!mysql_execute_command()[sql_parse.cc:6153]
      mysqld.exe!mysql_parse()[sql_parse.cc:7942]
      mysqld.exe!dispatch_command()[sql_parse.cc:1842]
      mysqld.exe!do_command()[sql_parse.cc:1357]
      mysqld.exe!threadpool_process_request()[threadpool_common.cc:374]
      mysqld.exe!tp_callback()[threadpool_common.cc:192]
      ntdll.dll!TpAllocPool()
      ntdll.dll!TpReleaseCleanupGroupMembers()
      KERNEL32.DLL!BaseThreadInitThunk()
      ntdll.dll!RtlUserThreadStart()

      Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on

      Attachments

        Activity

          alice Alice Sherepa added a comment - - edited

          Thank you for the report! I repeated as described on 10.3-10.5:

          10.3 23e090626a9138f44905a5

          Version: '10.3.29-MariaDB-debug-log'  
          /10.3/sql/item_strfunc.cc:3235: virtual Sql_mode_dependency Item_func_rpad::value_depends_on_sql_mode() const: Assertion `arg_count == 3' failed.
          210429  7:20:17 [ERROR] mysqld got signal 6 ;
           
          Server version: 10.3.29-MariaDB-debug-log
           
          /lib/x86_64-linux-gnu/libc.so.6(+0x30102)[0x7f6c03c5e102]
          sql/item_strfunc.cc:3236(Item_func_rpad::value_depends_on_sql_mode() const)[0x55f25ae24c1e]
          sql/field.cc:1401(Field::check_vcol_sql_mode_dependency(THD*, vcol_init_mode) const)[0x55f25ab72cac]
          sql/table.cc:1101(parse_vcol_defs(THD*, st_mem_root*, TABLE*, bool*, vcol_init_mode))[0x55f25a77fa51]
          sql/table.cc:3430(open_table_from_share(THD*, TABLE_SHARE*, st_mysql_const_lex_string const*, unsigned int, unsigned int, unsigned int, TABLE*, bool, List<String>*))[0x55f25a795579]
          sql/handler.cc:5200(ha_create_table(THD*, char const*, char const*, char const*, HA_CREATE_INFO*, st_mysql_const_unsigned_lex_string*))[0x55f25ac573d5]
          sql/unireg.cc:515(rea_create_table(THD*, st_mysql_const_unsigned_lex_string*, char const*, char const*, char const*, HA_CREATE_INFO*, handler*, bool))[0x55f25a7e6328]
          sql/sql_table.cc:5098(create_table_impl(THD*, st_mysql_const_lex_string const*, st_mysql_const_lex_string const*, st_mysql_const_lex_string const*, st_mysql_const_lex_string const*, char const*, DDL_options_st, HA_CREATE_INFO*, Alter_info*, int, bool*, st_key**, unsigned int*, st_mysql_const_unsigned_lex_string*))[0x55f25a6da93d]
          sql/sql_table.cc:5217(mysql_create_table_no_lock(THD*, st_mysql_const_lex_string const*, st_mysql_const_lex_string const*, Table_specification_st*, Alter_info*, bool*, int, TABLE_LIST*))[0x55f25a6db6f0]
          sql/sql_table.cc:5312(mysql_create_table(THD*, TABLE_LIST*, Table_specification_st*, Alter_info*))[0x55f25a6dc228]
          sql/sql_table.cc:11369(Sql_cmd_create_table_like::execute(THD*))[0x55f25a709abe]
          sql/sql_parse.cc:6078(mysql_execute_command(THD*))[0x55f25a495fdf]
          sql/sql_parse.cc:7873(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55f25a4a2659]
          sql/sql_parse.cc:1855(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55f25a478d17]
          sql/sql_parse.cc:1398(do_command(THD*))[0x55f25a475452]
          sql/sql_connect.cc:1403(do_handle_one_connection(CONNECT*))[0x55f25a8696a7]
          sql/sql_connect.cc:1309(handle_one_connection)[0x55f25a868f5f]
          perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55f25bfe6275]
          nptl/pthread_create.c:487(start_thread)[0x7f6c03df6fa3]
          x86_64/clone.S:97(clone)[0x7f6c03d274cf]
           
          Query (0x62b000000410): CREATE TABLE crash_test_2 (
          DATA_VALUE CHAR(10) NULL,
          HAS_DATA BIT NOT NULL,
          TEST_COLUMN CHAR(10) AS (RPAD(CASE WHEN HAS_DATA = 1 THEN DATA_VALUE ELSE NULL END, 10)) stored 
          )
          

          CREATE TABLE t1 (i int, b int AS (RPAD(123,1)) stored);
          

          alice Alice Sherepa added a comment - - edited Thank you for the report! I repeated as described on 10.3-10.5: 10.3 23e090626a9138f44905a5 Version: '10.3.29-MariaDB-debug-log' /10.3/sql/item_strfunc.cc:3235: virtual Sql_mode_dependency Item_func_rpad::value_depends_on_sql_mode() const: Assertion `arg_count == 3' failed. 210429 7:20:17 [ERROR] mysqld got signal 6 ;   Server version: 10.3.29-MariaDB-debug-log   /lib/x86_64-linux-gnu/libc.so.6(+0x30102)[0x7f6c03c5e102] sql/item_strfunc.cc:3236(Item_func_rpad::value_depends_on_sql_mode() const)[0x55f25ae24c1e] sql/field.cc:1401(Field::check_vcol_sql_mode_dependency(THD*, vcol_init_mode) const)[0x55f25ab72cac] sql/table.cc:1101(parse_vcol_defs(THD*, st_mem_root*, TABLE*, bool*, vcol_init_mode))[0x55f25a77fa51] sql/table.cc:3430(open_table_from_share(THD*, TABLE_SHARE*, st_mysql_const_lex_string const*, unsigned int, unsigned int, unsigned int, TABLE*, bool, List<String>*))[0x55f25a795579] sql/handler.cc:5200(ha_create_table(THD*, char const*, char const*, char const*, HA_CREATE_INFO*, st_mysql_const_unsigned_lex_string*))[0x55f25ac573d5] sql/unireg.cc:515(rea_create_table(THD*, st_mysql_const_unsigned_lex_string*, char const*, char const*, char const*, HA_CREATE_INFO*, handler*, bool))[0x55f25a7e6328] sql/sql_table.cc:5098(create_table_impl(THD*, st_mysql_const_lex_string const*, st_mysql_const_lex_string const*, st_mysql_const_lex_string const*, st_mysql_const_lex_string const*, char const*, DDL_options_st, HA_CREATE_INFO*, Alter_info*, int, bool*, st_key**, unsigned int*, st_mysql_const_unsigned_lex_string*))[0x55f25a6da93d] sql/sql_table.cc:5217(mysql_create_table_no_lock(THD*, st_mysql_const_lex_string const*, st_mysql_const_lex_string const*, Table_specification_st*, Alter_info*, bool*, int, TABLE_LIST*))[0x55f25a6db6f0] sql/sql_table.cc:5312(mysql_create_table(THD*, TABLE_LIST*, Table_specification_st*, Alter_info*))[0x55f25a6dc228] sql/sql_table.cc:11369(Sql_cmd_create_table_like::execute(THD*))[0x55f25a709abe] sql/sql_parse.cc:6078(mysql_execute_command(THD*))[0x55f25a495fdf] sql/sql_parse.cc:7873(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55f25a4a2659] sql/sql_parse.cc:1855(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55f25a478d17] sql/sql_parse.cc:1398(do_command(THD*))[0x55f25a475452] sql/sql_connect.cc:1403(do_handle_one_connection(CONNECT*))[0x55f25a8696a7] sql/sql_connect.cc:1309(handle_one_connection)[0x55f25a868f5f] perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55f25bfe6275] nptl/pthread_create.c:487(start_thread)[0x7f6c03df6fa3] x86_64/clone.S:97(clone)[0x7f6c03d274cf]   Query (0x62b000000410): CREATE TABLE crash_test_2 ( DATA_VALUE CHAR(10) NULL, HAS_DATA BIT NOT NULL, TEST_COLUMN CHAR(10) AS (RPAD(CASE WHEN HAS_DATA = 1 THEN DATA_VALUE ELSE NULL END, 10)) stored ) CREATE TABLE t1 (i int , b int AS (RPAD(123,1)) stored);

          Hello novog and thanks for a submission!

          I will soon submit the fix for a review. Until then, you can work around the problem by passing ' ' (a space) as a third argument – the bug was that we did not handle a possibility of two argument input in one place (sql mode dependancies computation)

          nikitamalyavin Nikita Malyavin added a comment - Hello novog and thanks for a submission! I will soon submit the fix for a review. Until then, you can work around the problem by passing ' ' (a space) as a third argument – the bug was that we did not handle a possibility of two argument input in one place (sql mode dependancies computation)

          Patch for review is: b2965aa554

          nikitamalyavin Nikita Malyavin added a comment - Patch for review is: b2965aa554

          People

            nikitamalyavin Nikita Malyavin
            novog Christopher Granahan
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.