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

FK fields cannot be used anymore in generated columns

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Duplicate
    • 10.5.22, 10.6.15, 10.9.8, 10.10.6, 10.11.5, 11.0.3
    • N/A
    • None
    • Docker 10.5.22 official image

    Description

      Since 10.5.22, our migrations are not running anymore with the following error message:

      SQLSTATE[HY000]: General error: 1901 Function or expression 'variant_listing_config' cannot be used in the CHECK clause of `variant_listing_config`"

      When I remove the FK constraint it works again.

      Our migration steps simplified

      DROP TABLE IF EXISTS  `t1`;
       
      CREATE TABLE `t1` (
                                 `id` binary(16) NOT NULL,
                                 `configurator_group_config` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`configurator_group_config`)),
                                 `main_variant_id` binary(16) DEFAULT NULL,
                                 `display_parent` tinyint(1) DEFAULT NULL,
                                 PRIMARY KEY (`id`),
                                 CONSTRAINT `fk.t1.main_variant_id` FOREIGN KEY (`main_variant_id`) REFERENCES `t1` (`id`) ON DELETE SET NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
       
      ALTER TABLE `t1`
          ADD COLUMN `variant_listing_config` JSON
              GENERATED ALWAYS AS (
                  CASE
                      WHEN `display_parent` IS NOT NULL OR `main_variant_id` IS NOT NULL OR
                           `configurator_group_config` IS NOT NULL
                          THEN (JSON_OBJECT('displayParent', `display_parent`, 'mainVariantId', LOWER(HEX(`main_variant_id`)),
                                            'configuratorGroupConfig', JSON_EXTRACT(`configurator_group_config`, '$')))
                      END) VIRTUAL
      ;
      

      Attachments

        Issue Links

          Activity

            danblack Daniel Black added a comment -

            Thanks for the simplified test case.

            regression in 0c9794d022189be0af63cc42d2b15cefaaa5b26b

            Thread 30 "mariadbd" hit Breakpoint 2.1, check_expression (vcol=0x7f7f7c015108, name=name@entry=0x7f7f7c013848, 
                type=VCOL_GENERATED_VIRTUAL, alter_info=alter_info@entry=0x0) at /home/dan/repos/mariadb-server-10.5/sql/field.cc:10455
            10455	  Item::vcol_func_processor_result res;
            (gdb) 
            Continuing.
             
            Thread 30 "mariadbd" hit Breakpoint 4, Item_field::check_vcol_func_processor (this=0x7f7f7c013930, arg=0x7f80141756d0)
                at /home/dan/repos/mariadb-server-10.5/sql/item.cc:1547
            1547	  context= 0;
             
            (gdb) n
            1549	  if (res && res->alter_info)
            (gdb) p res
            $31 = (Item::vcol_func_processor_result *) 0x7f80141756d0
            (gdb) p *res
            $32 = {errors = 0, name = 0x0, alter_info = 0x0}
            (gdb) watch res.errors
            Watchpoint 7: res.errors
            (gdb) watch res.name
            Watchpoint 8: res.name
            (gdb) n
            1551	  else if (field)
            (gdb) c
            Continuing.
             
            Thread 30 "mariadbd" hit Watchpoint 7: res.errors
             
            Old value = 0
            New value = 1
            mark_unsupported_function (where=0x7f7f7c013920 "display_parent", store=0x7f80141756d0, result=1)
                at /home/dan/repos/mariadb-server-10.5/sql/item.cc:1528
            1528	  if (result > old_errors)
            (gdb) bt
            #0  mark_unsupported_function (where=0x7f7f7c013920 "display_parent", store=0x7f80141756d0, result=1)
                at /home/dan/repos/mariadb-server-10.5/sql/item.cc:1528
            #1  Item_field::check_vcol_func_processor (this=0x7f7f7c013930, arg=0x7f80141756d0)
                at /home/dan/repos/mariadb-server-10.5/sql/item.cc:1559
            #2  0x00000000006eba64 in Item_args::walk_args (this=0x7f7f7c013b00, processor=&virtual table offset 1128, 
                walk_subquery=<optimized out>, arg=0x7f80141756d0) at /home/dan/repos/mariadb-server-10.5/sql/item.h:2614
            #3  Item_func_or_sum::walk (this=0x7f7f7c013a70, processor=&virtual table offset 1128, walk_subquery=<optimized out>, 
                arg=0x7f80141756d0) at /home/dan/repos/mariadb-server-10.5/sql/item.h:5268
            #4  0x0000000000919012 in Item_cond::walk (this=0x7f7f7c013d80, processor=&virtual table offset 1128, walk_subquery=<optimized out>, 
                arg=0x7f80141756d0) at /home/dan/repos/mariadb-server-10.5/sql/item_cmpfunc.cc:5177
            #5  0x00000000006eba64 in Item_args::walk_args (this=0x7f7f7c015098, processor=&virtual table offset 1128, 
                walk_subquery=<optimized out>, arg=0x7f80141756d0) at /home/dan/repos/mariadb-server-10.5/sql/item.h:2614
            #6  Item_func_or_sum::walk (this=0x7f7f7c015008, processor=&virtual table offset 1128, walk_subquery=<optimized out>, 
                arg=0x7f80141756d0) at /home/dan/repos/mariadb-server-10.5/sql/item.h:5268
            #7  0x00000000008cbe82 in check_expression (vcol=0x7f7f7c015108, name=name@entry=0x7f7f7c013848, type=VCOL_GENERATED_VIRTUAL, 
                alter_info=alter_info@entry=0x0) at /home/dan/repos/mariadb-server-10.5/sql/field.cc:10465
            #8  0x00000000008cc278 in Column_definition::check (this=0x7f7f7c013810, thd=0x7f7f7c000c68)
                at /home/dan/repos/mariadb-server-10.5/sql/field.cc:10597
            #9  0x000000000088308d in MYSQLparse (thd=thd@entry=0x7f7f7c000c68) at /home/dan/repos/mariadb-server-10.5/sql/sql_yacc.yy:6123
            #10 0x0000000000702835 in parse_sql (thd=thd@entry=0x7f7f7c000c68, parser_state=parser_state@entry=0x7f80141765b0, 
                creation_ctx=creation_ctx@entry=0x0, do_pfs_digest=true) at /home/dan/repos/mariadb-server-10.5/sql/sql_parse.cc:10468
            #11 0x00000000006f6558 in mysql_parse (thd=thd@entry=0x7f7f7c000c68, 
                rawbuf=0x7f7f7c012c10 "ALTER TABLE `t1`      ADD COLUMN `variant_listing_config` JSON          GENERATED ALWAYS AS (", ' ' <repeats 14 times>, "CASE", ' ' <repeats 18 times>, "WHEN `display_parent` IS NOT NULL OR `main_variant_id` IS NOT NULL OR  "..., 
                length=<optimized out>, parser_state=parser_state@entry=0x7f80141765b0, is_com_multi=false, is_next_command=<optimized out>)
                at /home/dan/repos/mariadb-server-10.5/sql/sql_parse.cc:8070
            #12 0x00000000006f4951 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7f7f7c000c68, 
                packet=packet@entry=0x7f7f7c0080e9 "ALTER TABLE `t1`      ADD COLUMN `variant_listing_config` JSON          GENERATED ALWAYS AS (", ' ' <repeats 14 times>, "CASE", ' ' <repeats 18 times>, "WHEN `display_parent` IS NOT NULL OR `main_variant_id` IS NOT NULL OR  "..., 
                packet_length=packet_length@entry=527, is_com_multi=false, is_next_command=false)
                at /home/dan/repos/mariadb-server-10.5/sql/sql_parse.cc:1891
            #13 0x00000000006f69f2 in do_command (thd=0x7f7f7c000c68) at /home/dan/repos/mariadb-server-10.5/sql/sql_parse.cc:1375
            #14 0x00000000007e8e49 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x4275d88, put_in_cache=true)
                at /home/dan/repos/mariadb-server-10.5/sql/sql_connect.cc:1416
            #15 0x00000000007e8cbb in handle_one_connection (arg=arg@entry=0x4275d88)
                at /home/dan/repos/mariadb-server-10.5/sql/sql_connect.cc:1318
            #16 0x0000000000aec526 in pfs_spawn_thread (arg=0x3af11f8) at /home/dan/repos/mariadb-server-10.5/storage/perfschema/pfs.cc:2201
            #17 0x00007f80154ae907 in start_thread () from /lib64/libc.so.6
            #18 0x00007f8015534870 in clone3 () from /lib64/libc.so.6
            (gdb) up
            #1  Item_field::check_vcol_func_processor (this=0x7f7f7c013930, arg=0x7f80141756d0)
                at /home/dan/repos/mariadb-server-10.5/sql/item.cc:1559
            1559	  return mark_unsupported_function(field_name.str, arg, r);
            (gdb) p arg
            $33 = (void *) 0x7f80141756d0
            (gdb) p *arg
            Attempt to dereference a generic pointer.
            (gdb) p *this
            $34 = (Item_field) {<Item_ident> = {<Item_result_field> = {<Item_fixed_hybrid> = {<Item> = {<Value_source> = {<No data fields>}, <Type_all_attributes> = {<Type_std_attributes> = {<Type_numeric_attributes> = {max_length = 0, decimals = 0, unsigned_flag = false}, 
                          collation = {collation = 0x16cd220 <my_charset_bin>, derivation = DERIVATION_IMPLICIT, 
                            repertoire = MY_REPERTOIRE_UNICODE30}}, _vptr$Type_all_attributes = 0x10e9c60 <vtable for Item_field+16>}, 
                      join_tab_idx = 61, is_expensive_cache = -1 '\377', rsize = 0, str_value = {<Charset> = {
                          m_charset = 0x16cd220 <my_charset_bin>}, <Binary_string> = {<Static_binary_string> = {<Sql_alloc> = {<No data fields>}, 
                            Ptr = 0x0, str_length = 0}, Alloced_length = 0, extra_alloc = 0, alloced = false, 
                          thread_specific = false}, <No data fields>}, name = {str = 0x7f7f7c013920 "display_parent", length = 14}, 
                      orig_name = 0x0, next = 0x0, marker = 0, maybe_null = false, in_rollup = false, null_value = false, with_param = false, 
                      with_window_func = false, with_field = true, common_flags = 1 '\001'}, fixed = false}, result_field = 0x0}, 
                orig_db_name = {<Lex_cstring> = {<st_mysql_const_lex_string> = {str = 0x0, length = 0}, <No data fields>}, <No data fields>}, 
                orig_table_name = {<Lex_cstring> = {<st_mysql_const_lex_string> = {str = 0x0, length = 0}, <No data fields>}, <No data fields>}, 
                orig_field_name = {<Lex_cstring> = {<st_mysql_const_lex_string> = {str = 0x7f7f7c013920 "display_parent", 
                      length = 14}, <No data fields>}, <No data fields>}, context = 0x0, 
                db_name = {<Lex_cstring> = {<st_mysql_const_lex_string> = {str = 0x0, length = 0}, <No data fields>}, <No data fields>}, 
                table_name = {<Lex_cstring> = {<st_mysql_const_lex_string> = {str = 0x0, length = 0}, <No data fields>}, <No data fields>}, 
                field_name = {<Lex_cstring> = {<st_mysql_const_lex_string> = {str = 0x7f7f7c013920 "display_parent", 
                      length = 14}, <No data fields>}, <No data fields>}, alias_name_used = false, cached_field_index = 4294967295, 
                cached_table = 0x0, depended_from = 0x0, can_be_depended = true}, <Load_data_outvar> = {
                _vptr$Load_data_outvar = 0x10ea2c0 <vtable for Item_field+1648>}, field = 0x0, item_equal = 0x0, have_privileges = NO_ACL, 
              any_privileges = false}
            
            

            danblack Daniel Black added a comment - Thanks for the simplified test case. regression in 0c9794d022189be0af63cc42d2b15cefaaa5b26b Thread 30 "mariadbd" hit Breakpoint 2.1, check_expression (vcol=0x7f7f7c015108, name=name@entry=0x7f7f7c013848, type=VCOL_GENERATED_VIRTUAL, alter_info=alter_info@entry=0x0) at /home/dan/repos/mariadb-server-10.5/sql/field.cc:10455 10455 Item::vcol_func_processor_result res; (gdb) Continuing.   Thread 30 "mariadbd" hit Breakpoint 4, Item_field::check_vcol_func_processor (this=0x7f7f7c013930, arg=0x7f80141756d0) at /home/dan/repos/mariadb-server-10.5/sql/item.cc:1547 1547 context= 0;   (gdb) n 1549 if (res && res->alter_info) (gdb) p res $31 = (Item::vcol_func_processor_result *) 0x7f80141756d0 (gdb) p *res $32 = {errors = 0, name = 0x0, alter_info = 0x0} (gdb) watch res.errors Watchpoint 7: res.errors (gdb) watch res.name Watchpoint 8: res.name (gdb) n 1551 else if (field) (gdb) c Continuing.   Thread 30 "mariadbd" hit Watchpoint 7: res.errors   Old value = 0 New value = 1 mark_unsupported_function (where=0x7f7f7c013920 "display_parent", store=0x7f80141756d0, result=1) at /home/dan/repos/mariadb-server-10.5/sql/item.cc:1528 1528 if (result > old_errors) (gdb) bt #0 mark_unsupported_function (where=0x7f7f7c013920 "display_parent", store=0x7f80141756d0, result=1) at /home/dan/repos/mariadb-server-10.5/sql/item.cc:1528 #1 Item_field::check_vcol_func_processor (this=0x7f7f7c013930, arg=0x7f80141756d0) at /home/dan/repos/mariadb-server-10.5/sql/item.cc:1559 #2 0x00000000006eba64 in Item_args::walk_args (this=0x7f7f7c013b00, processor=&virtual table offset 1128, walk_subquery=<optimized out>, arg=0x7f80141756d0) at /home/dan/repos/mariadb-server-10.5/sql/item.h:2614 #3 Item_func_or_sum::walk (this=0x7f7f7c013a70, processor=&virtual table offset 1128, walk_subquery=<optimized out>, arg=0x7f80141756d0) at /home/dan/repos/mariadb-server-10.5/sql/item.h:5268 #4 0x0000000000919012 in Item_cond::walk (this=0x7f7f7c013d80, processor=&virtual table offset 1128, walk_subquery=<optimized out>, arg=0x7f80141756d0) at /home/dan/repos/mariadb-server-10.5/sql/item_cmpfunc.cc:5177 #5 0x00000000006eba64 in Item_args::walk_args (this=0x7f7f7c015098, processor=&virtual table offset 1128, walk_subquery=<optimized out>, arg=0x7f80141756d0) at /home/dan/repos/mariadb-server-10.5/sql/item.h:2614 #6 Item_func_or_sum::walk (this=0x7f7f7c015008, processor=&virtual table offset 1128, walk_subquery=<optimized out>, arg=0x7f80141756d0) at /home/dan/repos/mariadb-server-10.5/sql/item.h:5268 #7 0x00000000008cbe82 in check_expression (vcol=0x7f7f7c015108, name=name@entry=0x7f7f7c013848, type=VCOL_GENERATED_VIRTUAL, alter_info=alter_info@entry=0x0) at /home/dan/repos/mariadb-server-10.5/sql/field.cc:10465 #8 0x00000000008cc278 in Column_definition::check (this=0x7f7f7c013810, thd=0x7f7f7c000c68) at /home/dan/repos/mariadb-server-10.5/sql/field.cc:10597 #9 0x000000000088308d in MYSQLparse (thd=thd@entry=0x7f7f7c000c68) at /home/dan/repos/mariadb-server-10.5/sql/sql_yacc.yy:6123 #10 0x0000000000702835 in parse_sql (thd=thd@entry=0x7f7f7c000c68, parser_state=parser_state@entry=0x7f80141765b0, creation_ctx=creation_ctx@entry=0x0, do_pfs_digest=true) at /home/dan/repos/mariadb-server-10.5/sql/sql_parse.cc:10468 #11 0x00000000006f6558 in mysql_parse (thd=thd@entry=0x7f7f7c000c68, rawbuf=0x7f7f7c012c10 "ALTER TABLE `t1` ADD COLUMN `variant_listing_config` JSON GENERATED ALWAYS AS (", ' ' <repeats 14 times>, "CASE", ' ' <repeats 18 times>, "WHEN `display_parent` IS NOT NULL OR `main_variant_id` IS NOT NULL OR "..., length=<optimized out>, parser_state=parser_state@entry=0x7f80141765b0, is_com_multi=false, is_next_command=<optimized out>) at /home/dan/repos/mariadb-server-10.5/sql/sql_parse.cc:8070 #12 0x00000000006f4951 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7f7f7c000c68, packet=packet@entry=0x7f7f7c0080e9 "ALTER TABLE `t1` ADD COLUMN `variant_listing_config` JSON GENERATED ALWAYS AS (", ' ' <repeats 14 times>, "CASE", ' ' <repeats 18 times>, "WHEN `display_parent` IS NOT NULL OR `main_variant_id` IS NOT NULL OR "..., packet_length=packet_length@entry=527, is_com_multi=false, is_next_command=false) at /home/dan/repos/mariadb-server-10.5/sql/sql_parse.cc:1891 #13 0x00000000006f69f2 in do_command (thd=0x7f7f7c000c68) at /home/dan/repos/mariadb-server-10.5/sql/sql_parse.cc:1375 #14 0x00000000007e8e49 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x4275d88, put_in_cache=true) at /home/dan/repos/mariadb-server-10.5/sql/sql_connect.cc:1416 #15 0x00000000007e8cbb in handle_one_connection (arg=arg@entry=0x4275d88) at /home/dan/repos/mariadb-server-10.5/sql/sql_connect.cc:1318 #16 0x0000000000aec526 in pfs_spawn_thread (arg=0x3af11f8) at /home/dan/repos/mariadb-server-10.5/storage/perfschema/pfs.cc:2201 #17 0x00007f80154ae907 in start_thread () from /lib64/libc.so.6 #18 0x00007f8015534870 in clone3 () from /lib64/libc.so.6 (gdb) up #1 Item_field::check_vcol_func_processor (this=0x7f7f7c013930, arg=0x7f80141756d0) at /home/dan/repos/mariadb-server-10.5/sql/item.cc:1559 1559 return mark_unsupported_function(field_name.str, arg, r); (gdb) p arg $33 = (void *) 0x7f80141756d0 (gdb) p *arg Attempt to dereference a generic pointer. (gdb) p *this $34 = (Item_field) {<Item_ident> = {<Item_result_field> = {<Item_fixed_hybrid> = {<Item> = {<Value_source> = {<No data fields>}, <Type_all_attributes> = {<Type_std_attributes> = {<Type_numeric_attributes> = {max_length = 0, decimals = 0, unsigned_flag = false}, collation = {collation = 0x16cd220 <my_charset_bin>, derivation = DERIVATION_IMPLICIT, repertoire = MY_REPERTOIRE_UNICODE30}}, _vptr$Type_all_attributes = 0x10e9c60 <vtable for Item_field+16>}, join_tab_idx = 61, is_expensive_cache = -1 '\377', rsize = 0, str_value = {<Charset> = { m_charset = 0x16cd220 <my_charset_bin>}, <Binary_string> = {<Static_binary_string> = {<Sql_alloc> = {<No data fields>}, Ptr = 0x0, str_length = 0}, Alloced_length = 0, extra_alloc = 0, alloced = false, thread_specific = false}, <No data fields>}, name = {str = 0x7f7f7c013920 "display_parent", length = 14}, orig_name = 0x0, next = 0x0, marker = 0, maybe_null = false, in_rollup = false, null_value = false, with_param = false, with_window_func = false, with_field = true, common_flags = 1 '\001'}, fixed = false}, result_field = 0x0}, orig_db_name = {<Lex_cstring> = {<st_mysql_const_lex_string> = {str = 0x0, length = 0}, <No data fields>}, <No data fields>}, orig_table_name = {<Lex_cstring> = {<st_mysql_const_lex_string> = {str = 0x0, length = 0}, <No data fields>}, <No data fields>}, orig_field_name = {<Lex_cstring> = {<st_mysql_const_lex_string> = {str = 0x7f7f7c013920 "display_parent", length = 14}, <No data fields>}, <No data fields>}, context = 0x0, db_name = {<Lex_cstring> = {<st_mysql_const_lex_string> = {str = 0x0, length = 0}, <No data fields>}, <No data fields>}, table_name = {<Lex_cstring> = {<st_mysql_const_lex_string> = {str = 0x0, length = 0}, <No data fields>}, <No data fields>}, field_name = {<Lex_cstring> = {<st_mysql_const_lex_string> = {str = 0x7f7f7c013920 "display_parent", length = 14}, <No data fields>}, <No data fields>}, alias_name_used = false, cached_field_index = 4294967295, cached_table = 0x0, depended_from = 0x0, can_be_depended = true}, <Load_data_outvar> = { _vptr$Load_data_outvar = 0x10ea2c0 <vtable for Item_field+1648>}, field = 0x0, item_equal = 0x0, have_privileges = NO_ACL, any_privileges = false}
            serg Sergei Golubchik added a comment - - edited

            This is intentional. The way MariaDB works right now (and always did) SET NULL actions in child tables happen on a very low level where there is no possibility of checking a CHECK constraint. That is, if you have SET NULL actions, they can break CHECK constraints.

            This particular fix now was done within the scope of MDEV-18114.

            Currently CHECK constraints can only work correctly if FK actions do not modify the child table. RESTRICT is fine, SET NULL is not. CASCADE is fine for DELETE, as it removes the whole row, doesn't update individual fields, but CASCADE for UPDATEs will not work with CHECK constraints either.

            We're planning to lift this restriction. MDEV-29181 (or MDEV-22361) when implemented, will allow us to validate CHECK constraints, update generated columns, etc on CASCADE and SET NULL actions.

            serg Sergei Golubchik added a comment - - edited This is intentional. The way MariaDB works right now (and always did) SET NULL actions in child tables happen on a very low level where there is no possibility of checking a CHECK constraint. That is, if you have SET NULL actions, they can break CHECK constraints. This particular fix now was done within the scope of MDEV-18114 . Currently CHECK constraints can only work correctly if FK actions do not modify the child table. RESTRICT is fine, SET NULL is not. CASCADE is fine for DELETE, as it removes the whole row, doesn't update individual fields, but CASCADE for UPDATEs will not work with CHECK constraints either. We're planning to lift this restriction. MDEV-29181 (or MDEV-22361 ) when implemented, will allow us to validate CHECK constraints, update generated columns, etc on CASCADE and SET NULL actions.
            shyim Soner Sayakci added a comment -

            When a user upgrades to the newest version. Does the generated column still work? We are looking right now for a solution, how we can do this?

            shyim Soner Sayakci added a comment - When a user upgrades to the newest version. Does the generated column still work? We are looking right now for a solution, how we can do this?

            The table structure is checked when you create or alter a table. If you'll avoid ALTER TABLE, the table will work as before. It seems even to be safe to do so — generally, SET NULL can break a CHECK constraint, but in your case it seems that NULL is a valid value, so in your case SET NULL will not violate CHECK constraints

            serg Sergei Golubchik added a comment - The table structure is checked when you create or alter a table. If you'll avoid ALTER TABLE, the table will work as before. It seems even to be safe to do so — generally, SET NULL can break a CHECK constraint, but in your case it seems that NULL is a valid value, so in your case SET NULL will not violate CHECK constraints

            People

              serg Sergei Golubchik
              shyim Soner Sayakci
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.