[MDEV-31654] Support STORED generated columns with FK cascade changes Created: 2023-07-10  Updated: 2023-11-06

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Fix Version/s: None

Type: Task Priority: Major
Reporter: Nikita Malyavin Assignee: Nikita Malyavin
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Blocks
is blocked by MDEV-29181 Potential corruption on Foreign key u... In Review
Duplicate
is duplicated by MDEV-31931 FK fields cannot be used anymore in g... Closed
Relates
relates to MDEV-18114 Foreign Key Constraint actions don't ... Closed

 Description   

MDEV-18114 found a lack of STORED generated column support in foreign keys these columns were never updated on the CASCADE/SET NULL changes.

I tried to make the support with some simple patch, but no luck, too many things should be changed. Yet I think that such features are vital for our marketal competitivity. One MDEV-29181 is done, this one is expected not to be a big deal.



 Comments   
Comment by Nikita Malyavin [ 2023-07-10 ]

Here is some patch to start from:

Index: storage/innobase/dict/dict0mem.cc
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/storage/innobase/dict/dict0mem.cc b/storage/innobase/dict/dict0mem.cc
--- a/storage/innobase/dict/dict0mem.cc	(revision 6e6e950c8abf40041b146b538c4825856c89e64b)
+++ b/storage/innobase/dict/dict0mem.cc	(revision f65ce4bf748e952d4c0fe95c0425840c913a31c1)
@@ -134,7 +134,7 @@
 dict_table_t *dict_table_t::create(const span<const char> &name,
                                    fil_space_t *space,
                                    ulint n_cols, ulint n_v_cols, ulint flags,
-                                   ulint flags2)
+                                   ulint flags2, ulint n_cs_cols)
 {
   ut_ad(!space || space->purpose == FIL_TYPE_TABLESPACE ||
         space->purpose == FIL_TYPE_TEMPORARY ||
@@ -167,7 +167,8 @@
     dict_index_t::MAX_N_FIELDS;
   table->n_v_cols= static_cast<unsigned>(n_v_cols) &
     dict_index_t::MAX_N_FIELDS;
-  table->n_cols= static_cast<unsigned>(table->n_t_cols - table->n_v_cols) &
+  table->n_cols= static_cast<unsigned>(table->n_t_cols - table->n_v_cols
+                                       + n_cs_cols) &
     dict_index_t::MAX_N_FIELDS;
   table->cols= static_cast<dict_col_t*>
     (mem_heap_alloc(heap, table->n_cols * sizeof *table->cols));
Index: storage/innobase/handler/ha_innodb.cc
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
--- a/storage/innobase/handler/ha_innodb.cc	(revision 6e6e950c8abf40041b146b538c4825856c89e64b)
+++ b/storage/innobase/handler/ha_innodb.cc	(revision f65ce4bf748e952d4c0fe95c0425840c913a31c1)
@@ -5874,7 +5874,8 @@
 	size_t n_fields = omits_virtual_cols(*table_share)
 		? table_share->stored_fields : table_share->fields;
 	size_t n_cols = dict_table_get_n_user_cols(ib_table)
-		+ dict_table_get_n_v_cols(ib_table)
+		+ dict_table_get_n_v_cols(ib_table) - table_share->virtual_fields
+                +
 		- !!DICT_TF2_FLAG_IS_SET(ib_table, DICT_TF2_FTS_HAS_DOC_ID);
 
 	if (UNIV_UNLIKELY(n_cols != n_fields)) {
@@ -8061,7 +8062,7 @@
 
 	for (uint i = 0; i < table->s->fields; i++) {
 		field = table->field[i];
-		const bool is_virtual = !field->stored_in_db();
+		const bool is_virtual = field->vcol_info;
 		if (is_virtual && skip_virtual) {
 			num_v++;
 			continue;
@@ -10592,7 +10593,7 @@
 
 	if (!omit_virtual) {
 		for (ulint i = 0; i < n_cols; i++) {
-			num_v += !m_form->field[i]->stored_in_db();
+			num_v += !!m_form->field[i]->vcol_info;
 		}
 	}
 
@@ -10612,12 +10613,15 @@
 		+ (m_flags2 & DICT_TF2_FTS && !has_doc_id_col);
 
 	table = dict_table_t::create({m_table_name,table_name_len}, nullptr,
-				     actual_n_cols, num_v, m_flags, m_flags2);
+				     actual_n_cols, num_v, m_flags, m_flags2,
+                                     m_form->s->virtual_fields
+                                     - (m_form->s->fields
+                                        - m_form->s->stored_fields));
 
 	/* Set the hidden doc_id column. */
 	if (m_flags2 & DICT_TF2_FTS) {
 		table->fts->doc_col = has_doc_id_col
-				      ? doc_id_col : n_cols - num_v;
+				      ? doc_id_col : m_form->s->stored_fields;
 	}
 
 	if (DICT_TF_HAS_DATA_DIR(m_flags)) {
@@ -10725,9 +10729,9 @@
 			goto err_col;
 		}
 
-		ulint is_virtual = !field->stored_in_db() ? DATA_VIRTUAL : 0;
+		bool is_stored = field->stored_in_db();
 
-		if (!is_virtual) {
+		if (is_stored) {
 			dict_mem_table_add_col(table, heap,
 				field->field_name.str, col_type,
 				dtype_form_prtype(
@@ -10737,7 +10741,8 @@
 					| vers_row,
 					charset_no),
 				col_len);
-		} else if (!omit_virtual) {
+		}
+		if (field->vcol_info && !omit_virtual) {
 			dict_mem_table_add_v_col(table, heap,
 				field->field_name.str, col_type,
 				dtype_form_prtype(
@@ -10745,19 +10750,19 @@
 					| nulls_allowed | unsigned_type
 					| binary_type | long_true_varchar
 					| vers_row
-					| is_virtual,
+					| DATA_VIRTUAL,
 					charset_no),
 				col_len, i, 0);
 		}
 
 		if (innobase_is_s_fld(field)) {
-			ut_ad(!is_virtual);
+			ut_ad(is_stored);
 			/* Added stored column in m_s_cols list. */
 			dict_mem_table_add_s_col(
 				table, 0);
 		}
 
-		if (is_virtual && omit_virtual) {
+		if (!is_stored && omit_virtual) {
 			continue;
 		}
 
@@ -10774,7 +10779,7 @@
 
 			const Field* field = m_form->field[i];
 
-			if (field->stored_in_db()) {
+			if (!field->vcol_info) {
 				continue;
 			}
 
Index: storage/innobase/include/dict0mem.h
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/storage/innobase/include/dict0mem.h b/storage/innobase/include/dict0mem.h
--- a/storage/innobase/include/dict0mem.h	(revision 6e6e950c8abf40041b146b538c4825856c89e64b)
+++ b/storage/innobase/include/dict0mem.h	(revision f65ce4bf748e952d4c0fe95c0425840c913a31c1)
@@ -2429,7 +2429,7 @@
   @return newly allocated table object */
   static dict_table_t *create(const span<const char> &name, fil_space_t *space,
                               ulint n_cols, ulint n_v_cols, ulint flags,
-                              ulint flags2);
+                              ulint flags2, ulint n_cs_cols=0);
 
   /** Check whether the table has any spatial indexes */
   bool has_spatial_index() const

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