[MDEV-31931] FK fields cannot be used anymore in generated columns - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Duplicate
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
Component/s: Data Definition - Alter Table
Labels:
None
Environment:
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

duplicates

MDEV-31654 Support STORED generated columns with FK cascade changes

Open

is blocked by

MDEV-30182 Optimize open_tables to take O(N) time

Stalled

is caused by

MDEV-18114 Foreign Key Constraint actions don't affect Virtual Column

Closed

links to

upstream shopware issue 3273

Activity

Ascending order - Click to sort in descending order

Soner Sayakci created issue - 2023-08-16 09:17

Soner Sayakci made changes - 2023-08-16 09:18

Field	Original Value	New Value
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 ; ```	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 :) {code:sql} 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 ; {code}

Soner Sayakci made changes - 2023-08-16 09:18

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 :)

{code:sql}
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
;
{code}

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 :)

{code:sql}
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
;
{code}

Soner Sayakci made changes - 2023-08-16 09:24

Affects Version/s		11.0.3 [ 28920 ]
Affects Version/s		10.11.5 [ 29019 ]
Affects Version/s		10.10.6 [ 29017 ]
Affects Version/s		10.9.8 [ 29015 ]
Affects Version/s		10.6.15 [ 29013 ]

Daniel Black added a comment - 2023-08-18 09:22

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}

Daniel Black added a comment - 2023-08-18 09:22 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}

Daniel Black made changes - 2023-08-18 09:22

Assignee

Sergei Golubchik [ serg ]

Daniel Black made changes - 2023-08-18 09:24

Fix Version/s

10.5 [ 23123 ]

Daniel Black made changes - 2023-08-18 09:24

Status

Open [ 1 ]

Confirmed [ 10101 ]

Daniel Black made changes - 2023-08-18 09:24

Remote Link

This issue links to "upstream shopware issue 3273 (Web Link)" [ 35812 ]

Sergei Golubchik added a comment - 2023-08-18 10:08 - 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.

Sergei Golubchik added a comment - 2023-08-18 10:08 - 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.

Sergei Golubchik made changes - 2023-08-18 10:08

Fix Version/s		N/A [ 14700 ]
Fix Version/s	10.5 [ 23123 ]
Resolution		Not a Bug [ 6 ]
Status	Confirmed [ 10101 ]	Closed [ 6 ]

Soner Sayakci added a comment - 2023-08-18 12:06

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?

Soner Sayakci added a comment - 2023-08-18 12:06 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?

Sergei Golubchik added a comment - 2023-08-18 13:16

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

Sergei Golubchik added a comment - 2023-08-18 13:16 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

Daniel Black made changes - 2023-08-18 22:52

Link

This issue relates to MDEV-31942 [ MDEV-31942 ]

Sergei Golubchik made changes - 2023-08-19 05:55

Link

This issue is caused by ~~MDEV-18114~~ [ ~~MDEV-18114~~ ]

Michael Widenius made changes - 2023-11-05 15:17

Link

This issue relates to MDEV-22361 [ MDEV-22361 ]

Sergei Golubchik made changes - 2023-11-06 09:04

Link

This issue relates to MDEV-31942 [ MDEV-31942 ]

Sergei Golubchik made changes - 2023-11-06 09:05

Link

This issue is blocked by MDEV-29181 [ MDEV-29181 ]

Sergei Golubchik made changes - 2023-11-06 09:05

Link

This issue relates to MDEV-22361 [ MDEV-22361 ]

Sergei Golubchik made changes - 2023-11-06 09:05

Link

This issue duplicates MDEV-22880 [ MDEV-22880 ]

Sergei Golubchik made changes - 2023-11-06 09:05

Resolution	Not a Bug [ 6 ]
Status	Closed [ 6 ]	Stalled [ 10000 ]

Sergei Golubchik made changes - 2023-11-06 09:06

Resolution		Duplicate [ 3 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2023-11-06 09:08

Link

This issue duplicates MDEV-22880 [ MDEV-22880 ]

Sergei Golubchik made changes - 2023-11-06 09:08

Link

This issue duplicates MDEV-31654 [ MDEV-31654 ]

Nikita Malyavin made changes - 2024-08-04 23:52

Link

This issue is blocked by MDEV-30182 [ MDEV-30182 ]

Nikita Malyavin made changes - 2024-08-04 23:52

Link

This issue is blocked by MDEV-29181 [ MDEV-29181 ]

People

Assignee:: Sergei Golubchik

Reporter:: Soner Sayakci

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2023-08-16 09:17

Updated:: 2024-08-04 23:52

Resolved:: 2023-11-06 09:06

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration