[MDEV-31931] FK fields cannot be used anymore in generated columns Created: 2023-08-16  Updated: 2023-11-06  Resolved: 2023-11-06

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.5.22, 10.6.15, 10.9.8, 10.10.6, 10.11.5, 11.0.3
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Soner Sayakci Assignee: Sergei Golubchik
Resolution: Duplicate Votes: 0
Labels: None
Environment:

Docker 10.5.22 official image

Issue Links:
Blocks
is blocked by MDEV-29181 Potential corruption on Foreign key u... In Review
Duplicate
duplicates MDEV-31654 Support STORED generated columns with... Open
Problem/Incident
is caused by MDEV-18114 Foreign Key Constraint actions don't ... Closed

 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
 
;



 Comments   
Comment by Daniel Black [ 2023-08-18 ]

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}

Comment by Sergei Golubchik [ 2023-08-18 ]

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.

Comment by Soner Sayakci [ 2023-08-18 ]

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?

Comment by Sergei Golubchik [ 2023-08-18 ]

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

Generated at Thu Feb 08 10:27:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.