[Commits] d6782fcded7: MDEV-24519: Server crashes in Charset::set_charset upon SELECT
by varun 13 Jan '21
by varun 13 Jan '21
13 Jan '21
revision-id: d6782fcded7cfe5f561359124153d5deaba28ec9 (mariadb-10.2.31-677-gd6782fcded7)
parent(s): ab271ee7e22ce1250ec36b09123bfb98bc3f8107
author: Varun Gupta
committer: Varun Gupta
timestamp: 2021-01-13 18:36:31 +0530
message:
MDEV-24519: Server crashes in Charset::set_charset upon SELECT
The query causing the issue here has implicit grouping for we
have to produce one row with special values for the aggregates (depending on
each aggregate function), and NULL values for all non-aggregate fields.
The subselect item where implicit grouping was being done, null_value
for the subselect item was not being set for the case when the implcit grouping prodcues
NULL values for the items in the select list for the subquery. This which was leading to the crash.
The fix would be to set the null_value correctly in the val_* functions for
Item_singlerow_subselect.
---
mysql-test/r/subselect4.result | 27 +++++++++++++++++++++++++++
mysql-test/t/subselect4.test | 24 ++++++++++++++++++++++++
sql/item_subselect.cc | 35 ++++++++++++++++++++++++++++++-----
3 files changed, 81 insertions(+), 5 deletions(-)
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index c0df4f626b1..a9b89577462 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -2686,4 +2686,31 @@ SELECT * FROM t2;
f
bar
DROP TABLE t1, t2;
+#
+# MDEV-24519: Server crashes in Charset::set_charset upon SELECT
+#
+CREATE TABLE t1 (a VARBINARY(8));
+INSERT INTO t1 VALUES ('foo'),('bar');
+CREATE TABLE t2 (b VARBINARY(8));
+EXPLAIN
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2);
+a
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b VARBINARY(8));
+EXPLAIN
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2);
+a
+DROP TABLE t1,t2;
# End of 10.2 tests
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index 6eada9b27d9..7c6f294c25e 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -2201,4 +2201,28 @@ SELECT * FROM t2;
DROP TABLE t1, t2;
+--echo #
+--echo # MDEV-24519: Server crashes in Charset::set_charset upon SELECT
+--echo #
+
+CREATE TABLE t1 (a VARBINARY(8));
+INSERT INTO t1 VALUES ('foo'),('bar');
+CREATE TABLE t2 (b VARBINARY(8));
+
+EXPLAIN
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2);
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2);
+
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b VARBINARY(8));
+
+EXPLAIN
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2);
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2);
+
+DROP TABLE t1,t2;
+
--echo # End of 10.2 tests
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 802bfca64b7..0e51465e31b 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1325,7 +1325,11 @@ longlong Item_singlerow_subselect::val_int()
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
- return value->val_int();
+ {
+ longlong val= value->val_int();
+ null_value= value->null_value;
+ return val;
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
@@ -1334,6 +1338,7 @@ longlong Item_singlerow_subselect::val_int()
else
{
reset();
+ DBUG_ASSERT(null_value);
return 0;
}
}
@@ -1342,7 +1347,11 @@ String *Item_singlerow_subselect::val_str(String *str)
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
- return value->val_str(str);
+ {
+ String *res= value->val_str(str);
+ null_value= value->null_value;
+ return res;
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
@@ -1351,6 +1360,7 @@ String *Item_singlerow_subselect::val_str(String *str)
else
{
reset();
+ DBUG_ASSERT(null_value);
return 0;
}
}
@@ -1360,7 +1370,11 @@ my_decimal *Item_singlerow_subselect::val_decimal(my_decimal *decimal_value)
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
- return value->val_decimal(decimal_value);
+ {
+ my_decimal *val= value->val_decimal(decimal_value);
+ null_value= value->null_value;
+ return val;
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
@@ -1369,6 +1383,7 @@ my_decimal *Item_singlerow_subselect::val_decimal(my_decimal *decimal_value)
else
{
reset();
+ DBUG_ASSERT(null_value);
return 0;
}
}
@@ -1378,7 +1393,11 @@ bool Item_singlerow_subselect::val_bool()
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
- return value->val_bool();
+ {
+ bool val= value->val_bool();
+ null_value= value->null_value;
+ return val;
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
@@ -1387,6 +1406,7 @@ bool Item_singlerow_subselect::val_bool()
else
{
reset();
+ DBUG_ASSERT(null_value);
return 0;
}
}
@@ -1396,7 +1416,11 @@ bool Item_singlerow_subselect::get_date(MYSQL_TIME *ltime,ulonglong fuzzydate)
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
- return value->get_date(ltime, fuzzydate);
+ {
+ bool val= value->get_date(ltime, fuzzydate);
+ null_value= value->null_value;
+ return val;
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
@@ -1405,6 +1429,7 @@ bool Item_singlerow_subselect::get_date(MYSQL_TIME *ltime,ulonglong fuzzydate)
else
{
reset();
+ DBUG_ASSERT(null_value);
return 1;
}
}
1
0
[Commits] ff927d06e89: MDEV-24519: Server crashes in Charset::set_charset upon SELECT
by varun 13 Jan '21
by varun 13 Jan '21
13 Jan '21
revision-id: ff927d06e89d50f1d9e8e5bbf1798f700dfdd4e6 (mariadb-10.2.31-677-gff927d06e89)
parent(s): ab271ee7e22ce1250ec36b09123bfb98bc3f8107
author: Varun Gupta
committer: Varun Gupta
timestamp: 2021-01-13 17:06:01 +0530
message:
MDEV-24519: Server crashes in Charset::set_charset upon SELECT
The query causing the issue here has implicit grouping for we
have to produce one row with special values for the aggregates (depending on
each aggregate function), and NULL values for all non-aggregate fields.
The subselect item where implicit grouping was being done, null_value
for the subselect item was being reset incorrectly for the case when the implcit grouping produces
NULL values for the items in the select list for the subquery.
A fix could be that if all the columns of the subquery are nullable then we make sure
that the null_value for the subselect is set to TRUE.
---
mysql-test/r/subselect4.result | 27 +++++++++++++++++++++++++++
mysql-test/t/subselect4.test | 24 ++++++++++++++++++++++++
sql/item_subselect.cc | 22 +++++++++++++++++++++-
sql/item_subselect.h | 1 +
4 files changed, 73 insertions(+), 1 deletion(-)
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index c0df4f626b1..a9b89577462 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -2686,4 +2686,31 @@ SELECT * FROM t2;
f
bar
DROP TABLE t1, t2;
+#
+# MDEV-24519: Server crashes in Charset::set_charset upon SELECT
+#
+CREATE TABLE t1 (a VARBINARY(8));
+INSERT INTO t1 VALUES ('foo'),('bar');
+CREATE TABLE t2 (b VARBINARY(8));
+EXPLAIN
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2);
+a
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b VARBINARY(8));
+EXPLAIN
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2);
+a
+DROP TABLE t1,t2;
# End of 10.2 tests
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index 6eada9b27d9..7c6f294c25e 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -2201,4 +2201,28 @@ SELECT * FROM t2;
DROP TABLE t1, t2;
+--echo #
+--echo # MDEV-24519: Server crashes in Charset::set_charset upon SELECT
+--echo #
+
+CREATE TABLE t1 (a VARBINARY(8));
+INSERT INTO t1 VALUES ('foo'),('bar');
+CREATE TABLE t2 (b VARBINARY(8));
+
+EXPLAIN
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2);
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2);
+
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b VARBINARY(8));
+
+EXPLAIN
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2);
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2);
+
+DROP TABLE t1,t2;
+
--echo # End of 10.2 tests
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 802bfca64b7..7d3fcc8d55f 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1296,9 +1296,29 @@ bool Item_singlerow_subselect::null_inside()
return 0;
}
+
+/*
+ @brief
+ Checks whether all the columns of a subselect are NULL
+
+ @retval
+ TRUE all columns NULL
+ FALSE otherwise
+*/
+bool Item_singlerow_subselect::all_nulls()
+{
+ for (uint i= 0; i < max_columns ; i++)
+ {
+ if (!row[i]->null_value)
+ return false;
+ }
+ return true;
+}
+
+
void Item_singlerow_subselect::bring_value()
{
- if (!exec() && assigned())
+ if (!exec() && assigned() && !all_nulls())
null_value= 0;
else
reset();
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 2292c22480f..f999e1a5015 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -317,6 +317,7 @@ class Item_singlerow_subselect :public Item_subselect
Item** addr(uint i) { return (Item**)row + i; }
bool check_cols(uint c);
bool null_inside();
+ bool all_nulls();
void bring_value();
/**
1
0
[Commits] e554cd1f31b: MDEV-24519: Server crashes in Charset::set_charset upon SELECT
by varun 08 Jan '21
by varun 08 Jan '21
08 Jan '21
revision-id: e554cd1f31b8fecc0ec9ab7845a82a852e739b99 (mariadb-10.2.31-657-ge554cd1f31b)
parent(s): cd1e5d65c6d3aab7f77860803d2d0f29bf307b4a
author: Varun Gupta
committer: Varun Gupta
timestamp: 2021-01-08 13:01:38 +0530
message:
MDEV-24519: Server crashes in Charset::set_charset upon SELECT
The query causing the issue here has implicit grouping for we
have to produce one row with special values for the aggregates (depending on
each aggregate function), and NULL values for all non-aggregate fields.
The subselect item where implicit grouping was being done, null_value
for the subselect item was not being set for the case when the implcit grouping prodcues
NULL values for the items in the select list for the subquery. This which was leading to the crash.
The fix would be to set the null_value correctly in the val_* functions for
Item_singlerow_subselect.
---
mysql-test/r/subselect4.result | 27 +++++++++++++++++++++++++++
mysql-test/t/subselect4.test | 24 ++++++++++++++++++++++++
sql/item_subselect.cc | 27 +++++++++++++++++++++++++++
3 files changed, 78 insertions(+)
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index c0df4f626b1..a9b89577462 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -2686,4 +2686,31 @@ SELECT * FROM t2;
f
bar
DROP TABLE t1, t2;
+#
+# MDEV-24519: Server crashes in Charset::set_charset upon SELECT
+#
+CREATE TABLE t1 (a VARBINARY(8));
+INSERT INTO t1 VALUES ('foo'),('bar');
+CREATE TABLE t2 (b VARBINARY(8));
+EXPLAIN
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2);
+a
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b VARBINARY(8));
+EXPLAIN
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2);
+a
+DROP TABLE t1,t2;
# End of 10.2 tests
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index 6eada9b27d9..7c6f294c25e 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -2201,4 +2201,28 @@ SELECT * FROM t2;
DROP TABLE t1, t2;
+--echo #
+--echo # MDEV-24519: Server crashes in Charset::set_charset upon SELECT
+--echo #
+
+CREATE TABLE t1 (a VARBINARY(8));
+INSERT INTO t1 VALUES ('foo'),('bar');
+CREATE TABLE t2 (b VARBINARY(8));
+
+EXPLAIN
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2);
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2);
+
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b VARBINARY(8));
+
+EXPLAIN
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2);
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2);
+
+DROP TABLE t1,t2;
+
--echo # End of 10.2 tests
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 802bfca64b7..f63bc944d6c 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1308,7 +1308,11 @@ double Item_singlerow_subselect::val_real()
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
+ {
+ if ((null_value= value->null_value))
+ return 0;
return value->val_real();
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
@@ -1325,7 +1329,11 @@ longlong Item_singlerow_subselect::val_int()
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
+ {
+ if ((null_value= value->null_value))
+ return 0;
return value->val_int();
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
@@ -1342,7 +1350,14 @@ String *Item_singlerow_subselect::val_str(String *str)
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
+ {
+ if (value->null_value)
+ {
+ if ((null_value= value->null_value))
+ return 0;
+ }
return value->val_str(str);
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
@@ -1360,7 +1375,11 @@ my_decimal *Item_singlerow_subselect::val_decimal(my_decimal *decimal_value)
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
+ {
+ if ((null_value= value->null_value))
+ return 0;
return value->val_decimal(decimal_value);
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
@@ -1378,7 +1397,11 @@ bool Item_singlerow_subselect::val_bool()
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
+ {
+ if ((null_value= value->null_value))
+ return 0;
return value->val_bool();
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
@@ -1396,7 +1419,11 @@ bool Item_singlerow_subselect::get_date(MYSQL_TIME *ltime,ulonglong fuzzydate)
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
+ {
+ if ((null_value= value->null_value))
+ return 1;
return value->get_date(ltime, fuzzydate);
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
1
0
[Commits] e554cd1f31b: MDEV-24519: Server crashes in Charset::set_charset upon SELECT
by varun 08 Jan '21
by varun 08 Jan '21
08 Jan '21
revision-id: e554cd1f31b8fecc0ec9ab7845a82a852e739b99 (mariadb-10.2.31-657-ge554cd1f31b)
parent(s): cd1e5d65c6d3aab7f77860803d2d0f29bf307b4a
author: Varun Gupta
committer: Varun Gupta
timestamp: 2021-01-08 13:01:38 +0530
message:
MDEV-24519: Server crashes in Charset::set_charset upon SELECT
The query causing the issue here has implicit grouping for we
have to produce one row with special values for the aggregates (depending on
each aggregate function), and NULL values for all non-aggregate fields.
The subselect item where implicit grouping was being done, null_value
for the subselect item was not being set for the case when the implcit grouping prodcues
NULL values for the items in the select list for the subquery. This which was leading to the crash.
The fix would be to set the null_value correctly in the val_* functions for
Item_singlerow_subselect.
---
mysql-test/r/subselect4.result | 27 +++++++++++++++++++++++++++
mysql-test/t/subselect4.test | 24 ++++++++++++++++++++++++
sql/item_subselect.cc | 27 +++++++++++++++++++++++++++
3 files changed, 78 insertions(+)
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index c0df4f626b1..a9b89577462 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -2686,4 +2686,31 @@ SELECT * FROM t2;
f
bar
DROP TABLE t1, t2;
+#
+# MDEV-24519: Server crashes in Charset::set_charset upon SELECT
+#
+CREATE TABLE t1 (a VARBINARY(8));
+INSERT INTO t1 VALUES ('foo'),('bar');
+CREATE TABLE t2 (b VARBINARY(8));
+EXPLAIN
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2);
+a
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b VARBINARY(8));
+EXPLAIN
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2);
+a
+DROP TABLE t1,t2;
# End of 10.2 tests
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index 6eada9b27d9..7c6f294c25e 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -2201,4 +2201,28 @@ SELECT * FROM t2;
DROP TABLE t1, t2;
+--echo #
+--echo # MDEV-24519: Server crashes in Charset::set_charset upon SELECT
+--echo #
+
+CREATE TABLE t1 (a VARBINARY(8));
+INSERT INTO t1 VALUES ('foo'),('bar');
+CREATE TABLE t2 (b VARBINARY(8));
+
+EXPLAIN
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2);
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2);
+
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b VARBINARY(8));
+
+EXPLAIN
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2);
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2);
+
+DROP TABLE t1,t2;
+
--echo # End of 10.2 tests
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 802bfca64b7..f63bc944d6c 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1308,7 +1308,11 @@ double Item_singlerow_subselect::val_real()
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
+ {
+ if ((null_value= value->null_value))
+ return 0;
return value->val_real();
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
@@ -1325,7 +1329,11 @@ longlong Item_singlerow_subselect::val_int()
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
+ {
+ if ((null_value= value->null_value))
+ return 0;
return value->val_int();
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
@@ -1342,7 +1350,14 @@ String *Item_singlerow_subselect::val_str(String *str)
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
+ {
+ if (value->null_value)
+ {
+ if ((null_value= value->null_value))
+ return 0;
+ }
return value->val_str(str);
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
@@ -1360,7 +1375,11 @@ my_decimal *Item_singlerow_subselect::val_decimal(my_decimal *decimal_value)
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
+ {
+ if ((null_value= value->null_value))
+ return 0;
return value->val_decimal(decimal_value);
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
@@ -1378,7 +1397,11 @@ bool Item_singlerow_subselect::val_bool()
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
+ {
+ if ((null_value= value->null_value))
+ return 0;
return value->val_bool();
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
@@ -1396,7 +1419,11 @@ bool Item_singlerow_subselect::get_date(MYSQL_TIME *ltime,ulonglong fuzzydate)
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
+ {
+ if ((null_value= value->null_value))
+ return 1;
return value->get_date(ltime, fuzzydate);
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
1
0
[Commits] e554cd1f31b: MDEV-24519: Server crashes in Charset::set_charset upon SELECT
by varun 08 Jan '21
by varun 08 Jan '21
08 Jan '21
revision-id: e554cd1f31b8fecc0ec9ab7845a82a852e739b99 (mariadb-10.2.31-657-ge554cd1f31b)
parent(s): cd1e5d65c6d3aab7f77860803d2d0f29bf307b4a
author: Varun Gupta
committer: Varun Gupta
timestamp: 2021-01-08 13:01:38 +0530
message:
MDEV-24519: Server crashes in Charset::set_charset upon SELECT
The query causing the issue here has implicit grouping for we
have to produce one row with special values for the aggregates (depending on
each aggregate function), and NULL values for all non-aggregate fields.
The subselect item where implicit grouping was being done, null_value
for the subselect item was not being set for the case when the implcit grouping prodcues
NULL values for the items in the select list for the subquery. This which was leading to the crash.
The fix would be to set the null_value correctly in the val_* functions for
Item_singlerow_subselect.
---
mysql-test/r/subselect4.result | 27 +++++++++++++++++++++++++++
mysql-test/t/subselect4.test | 24 ++++++++++++++++++++++++
sql/item_subselect.cc | 27 +++++++++++++++++++++++++++
3 files changed, 78 insertions(+)
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index c0df4f626b1..a9b89577462 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -2686,4 +2686,31 @@ SELECT * FROM t2;
f
bar
DROP TABLE t1, t2;
+#
+# MDEV-24519: Server crashes in Charset::set_charset upon SELECT
+#
+CREATE TABLE t1 (a VARBINARY(8));
+INSERT INTO t1 VALUES ('foo'),('bar');
+CREATE TABLE t2 (b VARBINARY(8));
+EXPLAIN
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2);
+a
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b VARBINARY(8));
+EXPLAIN
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2);
+a
+DROP TABLE t1,t2;
# End of 10.2 tests
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index 6eada9b27d9..7c6f294c25e 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -2201,4 +2201,28 @@ SELECT * FROM t2;
DROP TABLE t1, t2;
+--echo #
+--echo # MDEV-24519: Server crashes in Charset::set_charset upon SELECT
+--echo #
+
+CREATE TABLE t1 (a VARBINARY(8));
+INSERT INTO t1 VALUES ('foo'),('bar');
+CREATE TABLE t2 (b VARBINARY(8));
+
+EXPLAIN
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2);
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2);
+
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b VARBINARY(8));
+
+EXPLAIN
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2);
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2);
+
+DROP TABLE t1,t2;
+
--echo # End of 10.2 tests
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 802bfca64b7..f63bc944d6c 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1308,7 +1308,11 @@ double Item_singlerow_subselect::val_real()
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
+ {
+ if ((null_value= value->null_value))
+ return 0;
return value->val_real();
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
@@ -1325,7 +1329,11 @@ longlong Item_singlerow_subselect::val_int()
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
+ {
+ if ((null_value= value->null_value))
+ return 0;
return value->val_int();
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
@@ -1342,7 +1350,14 @@ String *Item_singlerow_subselect::val_str(String *str)
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
+ {
+ if (value->null_value)
+ {
+ if ((null_value= value->null_value))
+ return 0;
+ }
return value->val_str(str);
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
@@ -1360,7 +1375,11 @@ my_decimal *Item_singlerow_subselect::val_decimal(my_decimal *decimal_value)
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
+ {
+ if ((null_value= value->null_value))
+ return 0;
return value->val_decimal(decimal_value);
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
@@ -1378,7 +1397,11 @@ bool Item_singlerow_subselect::val_bool()
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
+ {
+ if ((null_value= value->null_value))
+ return 0;
return value->val_bool();
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
@@ -1396,7 +1419,11 @@ bool Item_singlerow_subselect::get_date(MYSQL_TIME *ltime,ulonglong fuzzydate)
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
+ {
+ if ((null_value= value->null_value))
+ return 1;
return value->get_date(ltime, fuzzydate);
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
1
0
[Commits] e554cd1f31b: MDEV-24519: Server crashes in Charset::set_charset upon SELECT
by varun 08 Jan '21
by varun 08 Jan '21
08 Jan '21
revision-id: e554cd1f31b8fecc0ec9ab7845a82a852e739b99 (mariadb-10.2.31-657-ge554cd1f31b)
parent(s): cd1e5d65c6d3aab7f77860803d2d0f29bf307b4a
author: Varun Gupta
committer: Varun Gupta
timestamp: 2021-01-08 13:01:38 +0530
message:
MDEV-24519: Server crashes in Charset::set_charset upon SELECT
The query causing the issue here has implicit grouping for we
have to produce one row with special values for the aggregates (depending on
each aggregate function), and NULL values for all non-aggregate fields.
The subselect item where implicit grouping was being done, null_value
for the subselect item was not being set for the case when the implcit grouping prodcues
NULL values for the items in the select list for the subquery. This which was leading to the crash.
The fix would be to set the null_value correctly in the val_* functions for
Item_singlerow_subselect.
---
mysql-test/r/subselect4.result | 27 +++++++++++++++++++++++++++
mysql-test/t/subselect4.test | 24 ++++++++++++++++++++++++
sql/item_subselect.cc | 27 +++++++++++++++++++++++++++
3 files changed, 78 insertions(+)
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index c0df4f626b1..a9b89577462 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -2686,4 +2686,31 @@ SELECT * FROM t2;
f
bar
DROP TABLE t1, t2;
+#
+# MDEV-24519: Server crashes in Charset::set_charset upon SELECT
+#
+CREATE TABLE t1 (a VARBINARY(8));
+INSERT INTO t1 VALUES ('foo'),('bar');
+CREATE TABLE t2 (b VARBINARY(8));
+EXPLAIN
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2);
+a
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b VARBINARY(8));
+EXPLAIN
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2);
+a
+DROP TABLE t1,t2;
# End of 10.2 tests
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index 6eada9b27d9..7c6f294c25e 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -2201,4 +2201,28 @@ SELECT * FROM t2;
DROP TABLE t1, t2;
+--echo #
+--echo # MDEV-24519: Server crashes in Charset::set_charset upon SELECT
+--echo #
+
+CREATE TABLE t1 (a VARBINARY(8));
+INSERT INTO t1 VALUES ('foo'),('bar');
+CREATE TABLE t2 (b VARBINARY(8));
+
+EXPLAIN
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2);
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2);
+
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b VARBINARY(8));
+
+EXPLAIN
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2);
+SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2);
+
+DROP TABLE t1,t2;
+
--echo # End of 10.2 tests
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 802bfca64b7..f63bc944d6c 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1308,7 +1308,11 @@ double Item_singlerow_subselect::val_real()
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
+ {
+ if ((null_value= value->null_value))
+ return 0;
return value->val_real();
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
@@ -1325,7 +1329,11 @@ longlong Item_singlerow_subselect::val_int()
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
+ {
+ if ((null_value= value->null_value))
+ return 0;
return value->val_int();
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
@@ -1342,7 +1350,14 @@ String *Item_singlerow_subselect::val_str(String *str)
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
+ {
+ if (value->null_value)
+ {
+ if ((null_value= value->null_value))
+ return 0;
+ }
return value->val_str(str);
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
@@ -1360,7 +1375,11 @@ my_decimal *Item_singlerow_subselect::val_decimal(my_decimal *decimal_value)
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
+ {
+ if ((null_value= value->null_value))
+ return 0;
return value->val_decimal(decimal_value);
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
@@ -1378,7 +1397,11 @@ bool Item_singlerow_subselect::val_bool()
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
+ {
+ if ((null_value= value->null_value))
+ return 0;
return value->val_bool();
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
@@ -1396,7 +1419,11 @@ bool Item_singlerow_subselect::get_date(MYSQL_TIME *ltime,ulonglong fuzzydate)
{
DBUG_ASSERT(fixed == 1);
if (forced_const)
+ {
+ if ((null_value= value->null_value))
+ return 1;
return value->get_date(ltime, fuzzydate);
+ }
if (!exec() && !value->null_value)
{
null_value= FALSE;
1
0
[Commits] 608b0ee52ef: MDEV-23033: All slaves crash once in ~24 hours and loop restart with signal 11
by sujatha 04 Jan '21
by sujatha 04 Jan '21
04 Jan '21
revision-id: 608b0ee52ef3e854ce14a407e64e936adbbeba23 (mariadb-10.2.31-648-g608b0ee52ef)
parent(s): 25db9ffa8bdab8a2f2af3c7f154343dd6c6d238f
author: Sujatha
committer: Sujatha
timestamp: 2021-01-04 15:06:12 +0530
message:
MDEV-23033: All slaves crash once in ~24 hours and loop restart with signal 11
Problem:
=======
Upon deleting or updating a row in a parent table (with primary key), if
the child table has virtual column and an associated key with ON UPDATE
CASCADE/ON DELETE CASCADE, it will result in slave crash.
Analysis:
========
Tables which are related through foreign key require prelocking similar to
triggers. i.e If a table has triggers/foreign keys we should add all tables
and routines used by them to the prelocking set. This prelocking happens
during 'open_and_lock_tables' call. Each table being opened is checked for
foreign key references. If foreign key reference exists then the child
table is opened and it is linked to the table_list. Upon any modification
to parent table its corresponding child tables are retried from table_list
and they are updated accordingly. This prelocking work fine on master.
On slave prelocking works for following cases.
- Statement/mixed based replication
- In row based replication when trigger execution is enabled through
'slave_run_triggers_for_rbr=YES/LOGGING/ENFORCE'
Otherwise it results in an assert/crash, as the parent table will not find
the corresponding child table and it will be NULL. Dereferencing NULL
pointer leads to slave server exit.
Fix:
===
Introduce a new 'slave_fk_event_map' flag similar to 'trg_event_map'. This
flag will ensure that when foreign key is enabled in row based replication
all the parent and child tables are prelocked, so that parent is able to
locate the child table.
Note: This issue is specific to slave, hence only slave needs to be
upgraded.
---
mysql-test/suite/rpl/r/rpl_row_vcol_crash.result | 380 ++++++++++++++++++++
mysql-test/suite/rpl/t/rpl_row_vcol_crash.test | 425 +++++++++++++++++++++++
sql/log_event.cc | 42 +--
sql/sql_base.cc | 118 ++++---
sql/table.h | 6 +-
5 files changed, 906 insertions(+), 65 deletions(-)
diff --git a/mysql-test/suite/rpl/r/rpl_row_vcol_crash.result b/mysql-test/suite/rpl/r/rpl_row_vcol_crash.result
new file mode 100644
index 00000000000..f76d8935fa8
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_row_vcol_crash.result
@@ -0,0 +1,380 @@
+include/master-slave.inc
+[connection master]
+#
+# Test case 1: KEY on a virtual column with ON DELETE CASCADE
+#
+CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1),(2),(3);
+CREATE TABLE t2 (id INT NOT NULL PRIMARY KEY,
+t1_id INT NOT NULL,
+v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id),
+CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (90,1,NULL);
+INSERT INTO t2 VALUES (91,2,default);
+DELETE FROM t1 WHERE id=1;
+connection slave;
+#
+# Verify data consistency on slave
+#
+include/diff_tables.inc [master:test.t1, slave:test.t1]
+include/diff_tables.inc [master:test.t2, slave:test.t2]
+connection master;
+DROP TABLE t2,t1;
+connection slave;
+#
+# Test case 2: Verify "ON DELETE CASCADE" for parent->child->child scenario
+# Parent table: users
+# Child tables: matchmaking_groups, matchmaking_group_users
+# Parent table: matchmaking_groups
+# Child tables: matchmaking_group_users, matchmaking_group_maps
+#
+# Deleting a row from parent table should be reflected in
+# child tables.
+# matchmaking_groups->matchmaking_group_users->matchmaking_group_maps
+# users->matchmaking_group_users->matchmaking_group_maps
+#
+connection master;
+CREATE TABLE users (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
+name VARCHAR(32) NOT NULL DEFAULT ''
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+CREATE TABLE matchmaking_groups (
+id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
+host_user_id INT UNSIGNED NOT NULL UNIQUE,
+v_col INT AS (host_user_id+1) VIRTUAL, KEY (v_col),
+CONSTRAINT FOREIGN KEY (host_user_id) REFERENCES users (id)
+ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+CREATE TABLE matchmaking_group_users (
+matchmaking_group_id BIGINT UNSIGNED NOT NULL,
+user_id INT UNSIGNED NOT NULL,
+v_col1 int as (user_id+1) virtual, KEY (v_col1),
+PRIMARY KEY (matchmaking_group_id,user_id),
+UNIQUE KEY user_id (user_id),
+CONSTRAINT FOREIGN KEY (matchmaking_group_id)
+REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE,
+CONSTRAINT FOREIGN KEY (user_id)
+REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+CREATE TABLE matchmaking_group_maps (
+matchmaking_group_id BIGINT UNSIGNED NOT NULL,
+map_id TINYINT UNSIGNED NOT NULL,
+v_col2 INT AS (map_id+1) VIRTUAL, KEY (v_col2),
+PRIMARY KEY (matchmaking_group_id,map_id),
+CONSTRAINT FOREIGN KEY (matchmaking_group_id)
+REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+connection slave;
+connection master;
+INSERT INTO users VALUES (NULL,'foo'),(NULL,'bar');
+INSERT INTO matchmaking_groups VALUES (10,1,default),(11,2,default);
+INSERT INTO matchmaking_group_users VALUES (10,1,default),(11,2,default);
+INSERT INTO matchmaking_group_maps VALUES (10,55,default),(11,66,default);
+DELETE FROM matchmaking_groups WHERE id = 10;
+connection slave;
+#
+# No rows should be returned as ON DELETE CASCASE should have removed
+# corresponding rows from child tables. There should not any mismatch
+# of 'id' field between parent->child.
+#
+SELECT * FROM matchmaking_group_users WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
+matchmaking_group_id user_id v_col1
+SELECT * FROM matchmaking_group_maps WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
+matchmaking_group_id map_id v_col2
+#
+# Rows with id=11 should be present
+#
+SELECT * FROM matchmaking_group_users;
+matchmaking_group_id user_id v_col1
+11 2 3
+SELECT * FROM matchmaking_group_maps;
+matchmaking_group_id map_id v_col2
+11 66 67
+connection master;
+DELETE FROM users WHERE id = 2;
+connection slave;
+#
+# No rows should be present in both the child tables
+#
+SELECT * FROM matchmaking_group_users;
+matchmaking_group_id user_id v_col1
+SELECT * FROM matchmaking_group_maps;
+matchmaking_group_id map_id v_col2
+connection master;
+DROP TABLE matchmaking_group_maps, matchmaking_group_users, matchmaking_groups, users;
+connection slave;
+#
+# Test case 3: KEY on a virtual column with ON UPDATE CASCADE
+#
+connection master;
+CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1, 80);
+CREATE TABLE t2 (a INT KEY, b INT,
+v_col int as (b+1) virtual, KEY (v_col),
+CONSTRAINT b FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE CASCADE
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (51, 1, default);
+connection slave;
+connection master;
+UPDATE t1 SET a = 50 WHERE a = 1;
+#
+# Master: Verify that ON UPDATE CASCADE works fine
+# old_row: (51, 1, 2) ON UPDATE New_row: (51, 50, 51)
+#
+SELECT * FROM t2 WHERE b=50;
+a b v_col
+51 50 51
+connection slave;
+#
+# Slave: Verify that ON UPDATE CASCADE works fine
+# old_row: (51, 1, 2) ON UPDATE New_row: (51, 50, 51)
+#
+SELECT * FROM t2 WHERE b=50;
+a b v_col
+51 50 51
+connection master;
+DROP TABLE t2, t1;
+connection slave;
+#
+# Test case 4: Define triggers on master, their results should be
+# replicated as part of row events and they should be
+# applied on slave with the default
+# slave_run_triggers_for_rbr=NO
+#
+connection master;
+CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE t2 (count INT NOT NULL) ENGINE=InnoDB;
+CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (1);
+INSERT INTO t1 VALUES (2),(3);
+connection slave;
+SHOW GLOBAL VARIABLES LIKE 'slave_run_triggers_for_rbr';
+Variable_name Value
+slave_run_triggers_for_rbr NO
+#
+# As two rows are inserted in table 't1', two rows should get inserted
+# into table 't2' as part of trigger.
+#
+include/assert.inc [Table t2 should have two rows.]
+connection master;
+DROP TABLE t1,t2;
+connection slave;
+#
+# Test case 5: Define triggers + Foreign Keys on master, their results
+# should be replicated as part of row events and master
+# and slave should be in sync.
+#
+connection master;
+CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE t2 (t1_id INT NOT NULL,
+v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id),
+CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
+CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t3 VALUES (1);
+INSERT INTO t1 VALUES (2),(3);
+INSERT INTO t2 VALUES (2, default), (3, default);
+connection slave;
+#
+# As two rows are inserted in table 't1', two rows should get inserted
+# into table 't3' as part of trigger.
+#
+include/assert.inc [Table t3 should have two rows.]
+#
+# Verify ON DELETE CASCASE correctness
+#
+connection master;
+DELETE FROM t1 WHERE id=2;
+connection slave;
+connection master;
+include/diff_tables.inc [master:test.t1, slave:test.t1]
+include/diff_tables.inc [master:test.t2, slave:test.t2]
+include/diff_tables.inc [master:test.t3, slave:test.t3]
+DROP TABLE t3,t2,t1;
+connection slave;
+#
+# Test case 6: Triggers are present only on slave and
+# 'slave_run_triggers_for_rbr=NO'
+#
+connection slave;
+SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr;
+SET GLOBAL slave_run_triggers_for_rbr= NO;;
+SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%';
+Variable_name Value
+slave_run_triggers_for_rbr NO
+connection master;
+CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE t2 (t1_id INT NOT NULL,
+v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col),
+KEY (t1_id), CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
+connection slave;
+CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1);
+connection master;
+INSERT INTO t1 VALUES (2),(3);
+INSERT INTO t2 VALUES (2, default), (3, default);
+connection slave;
+#
+# Count must be 0
+#
+include/assert.inc [Table t3 should have zero rows.]
+connection master;
+DELETE FROM t1 WHERE id=2;
+connection slave;
+SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr;
+#
+# Verify t1, t2 are consistent on slave.
+#
+include/diff_tables.inc [master:test.t1, slave:test.t1]
+include/diff_tables.inc [master:test.t2, slave:test.t2]
+connection master;
+DROP TABLE t3,t2,t1;
+connection slave;
+#
+# Test case 7: Triggers are present only on slave and
+# 'slave_run_triggers_for_rbr=YES'
+#
+connection slave;
+SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr;
+SET GLOBAL slave_run_triggers_for_rbr= YES;;
+SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%';
+Variable_name Value
+slave_run_triggers_for_rbr YES
+connection master;
+CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE t2 (t1_id INT NOT NULL,
+v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col),
+KEY (t1_id), CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
+connection slave;
+CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1);
+connection master;
+INSERT INTO t1 VALUES (2),(3);
+INSERT INTO t2 VALUES (2, default), (3, default);
+connection slave;
+#
+# Count must be 2
+#
+include/assert.inc [Table t3 should have two rows.]
+connection master;
+DELETE FROM t1 WHERE id=2;
+connection slave;
+SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr;
+#
+# Verify t1, t2 are consistent on slave.
+#
+include/diff_tables.inc [master:test.t1, slave:test.t1]
+include/diff_tables.inc [master:test.t2, slave:test.t2]
+connection master;
+DROP TABLE t3,t2,t1;
+connection slave;
+#
+# Test case 8: Triggers and Foreign Keys are present only on slave and
+# 'slave_run_triggers_for_rbr=NO'
+#
+connection slave;
+SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr;
+SET GLOBAL slave_run_triggers_for_rbr= NO;;
+SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%';
+Variable_name Value
+slave_run_triggers_for_rbr NO
+connection master;
+CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+SET sql_log_bin=0;
+CREATE TABLE t2 (t1_id INT NOT NULL,v_col INT AS (t1_id+1) VIRTUAL) ENGINE=INNODB;
+SET sql_log_bin=1;
+CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
+connection slave;
+CREATE TABLE t2 (t1_id INT NOT NULL,
+v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id),
+CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1);
+connection master;
+INSERT INTO t1 VALUES (2),(3);
+INSERT INTO t2 VALUES (2, default), (3, default);
+connection slave;
+#
+# Count must be 0
+#
+include/assert.inc [Table t3 should have zero rows.]
+connection master;
+DELETE FROM t1 WHERE id=2;
+# t1: Should have one row
+SELECT * FROM t1;
+id
+3
+# t2: Should have two rows
+SELECT * FROM t2;
+t1_id v_col
+2 3
+3 4
+connection slave;
+# t1: Should have one row
+SELECT * FROM t1;
+id
+3
+# t2: Should have one row on slave due to ON DELETE CASCASE
+SELECT * FROM t2;
+t1_id v_col
+3 4
+SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr;
+connection master;
+DROP TABLE t3,t2,t1;
+connection slave;
+#
+# Test case 9: Triggers are Foreign Keys are present only on slave and
+# 'slave_run_triggers_for_rbr=YES'
+#
+connection slave;
+SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr;
+SET GLOBAL slave_run_triggers_for_rbr= YES;;
+SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%';
+Variable_name Value
+slave_run_triggers_for_rbr YES
+connection master;
+CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+SET sql_log_bin=0;
+CREATE TABLE t2 (t1_id INT NOT NULL,v_col INT AS (t1_id+1) VIRTUAL) ENGINE=INNODB;
+SET sql_log_bin=1;
+CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
+connection slave;
+CREATE TABLE t2 (t1_id INT NOT NULL,
+v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id),
+CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1);
+connection master;
+INSERT INTO t1 VALUES (2),(3);
+INSERT INTO t2 VALUES (2, default), (3, default);
+connection slave;
+#
+# Count must be 2
+#
+include/assert.inc [Table t3 should have two rows.]
+connection master;
+DELETE FROM t1 WHERE id=2;
+# t1: Should have one row
+SELECT * FROM t1;
+id
+3
+# t2: Should have two rows
+SELECT * FROM t2;
+t1_id v_col
+2 3
+3 4
+connection slave;
+# t1: Should have one row
+SELECT * FROM t1;
+id
+3
+# t2: Should have one row on slave due to ON DELETE CASCASE
+SELECT * FROM t2;
+t1_id v_col
+3 4
+SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr;
+connection master;
+DROP TABLE t3,t2,t1;
+connection slave;
+include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_row_vcol_crash.test b/mysql-test/suite/rpl/t/rpl_row_vcol_crash.test
new file mode 100644
index 00000000000..84ee14977f3
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_row_vcol_crash.test
@@ -0,0 +1,425 @@
+# ==== Purpose ====
+#
+# Test verifies that, slave doesn't report any assert on UPDATE or DELETE of
+# row which tries to update the virtual columns with associated KEYs.
+#
+# Test scenarios are listed below.
+# 1) KEY on a virtual column with ON DELETE CASCADE
+# 2) Verify "ON DELETE CASCADE" for parent->child->child scenario
+# 3) KEY on a virtual column with ON UPDATE CASCADE
+# 4) Define triggers on master, their results should be replicated
+# as part of row events and they should be applied on slave with
+# the default slave_run_triggers_for_rbr=NO
+# 5) Define triggers + Foreign Keys on master, their results should be
+# replicated as part of row events and master and slave should be in sync.
+# 6) Triggers are present only on slave and 'slave_run_triggers_for_rbr=NO'
+# 7) Triggers are present only on slave and 'slave_run_triggers_for_rbr=YES'
+# 8) Triggers and Foreign Keys are present only on slave and
+# 'slave_run_triggers_for_rbr=NO'
+# 9) Triggers are Foreign Keys are present only on slave and
+# 'slave_run_triggers_for_rbr=YES'
+#
+# ==== References ====
+#
+# MDEV-23033: All slaves crash once in ~24 hours and loop restart with signal 11
+#
+
+--source include/have_binlog_format_row.inc
+--source include/have_innodb.inc
+--source include/master-slave.inc
+
+
+--echo #
+--echo # Test case 1: KEY on a virtual column with ON DELETE CASCADE
+--echo #
+CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1),(2),(3);
+
+CREATE TABLE t2 (id INT NOT NULL PRIMARY KEY,
+ t1_id INT NOT NULL,
+ v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id),
+ CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+
+INSERT INTO t2 VALUES (90,1,NULL);
+INSERT INTO t2 VALUES (91,2,default);
+
+# Following query results in an assert on slave
+DELETE FROM t1 WHERE id=1;
+--sync_slave_with_master
+
+--echo #
+--echo # Verify data consistency on slave
+--echo #
+--let $diff_tables= master:test.t1, slave:test.t1
+--source include/diff_tables.inc
+--let $diff_tables= master:test.t2, slave:test.t2
+--source include/diff_tables.inc
+
+--connection master
+DROP TABLE t2,t1;
+--sync_slave_with_master
+
+--echo #
+--echo # Test case 2: Verify "ON DELETE CASCADE" for parent->child->child scenario
+--echo # Parent table: users
+--echo # Child tables: matchmaking_groups, matchmaking_group_users
+--echo # Parent table: matchmaking_groups
+--echo # Child tables: matchmaking_group_users, matchmaking_group_maps
+--echo #
+--echo # Deleting a row from parent table should be reflected in
+--echo # child tables.
+--echo # matchmaking_groups->matchmaking_group_users->matchmaking_group_maps
+--echo # users->matchmaking_group_users->matchmaking_group_maps
+--echo #
+
+--connection master
+CREATE TABLE users (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
+ name VARCHAR(32) NOT NULL DEFAULT ''
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE matchmaking_groups (
+ id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
+ host_user_id INT UNSIGNED NOT NULL UNIQUE,
+ v_col INT AS (host_user_id+1) VIRTUAL, KEY (v_col),
+ CONSTRAINT FOREIGN KEY (host_user_id) REFERENCES users (id)
+ ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE matchmaking_group_users (
+ matchmaking_group_id BIGINT UNSIGNED NOT NULL,
+ user_id INT UNSIGNED NOT NULL,
+ v_col1 int as (user_id+1) virtual, KEY (v_col1),
+ PRIMARY KEY (matchmaking_group_id,user_id),
+ UNIQUE KEY user_id (user_id),
+ CONSTRAINT FOREIGN KEY (matchmaking_group_id)
+ REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT FOREIGN KEY (user_id)
+ REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE matchmaking_group_maps (
+ matchmaking_group_id BIGINT UNSIGNED NOT NULL,
+ map_id TINYINT UNSIGNED NOT NULL,
+ v_col2 INT AS (map_id+1) VIRTUAL, KEY (v_col2),
+ PRIMARY KEY (matchmaking_group_id,map_id),
+ CONSTRAINT FOREIGN KEY (matchmaking_group_id)
+ REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--sync_slave_with_master
+
+--connection master
+INSERT INTO users VALUES (NULL,'foo'),(NULL,'bar');
+INSERT INTO matchmaking_groups VALUES (10,1,default),(11,2,default);
+INSERT INTO matchmaking_group_users VALUES (10,1,default),(11,2,default);
+INSERT INTO matchmaking_group_maps VALUES (10,55,default),(11,66,default);
+
+DELETE FROM matchmaking_groups WHERE id = 10;
+--sync_slave_with_master
+
+--echo #
+--echo # No rows should be returned as ON DELETE CASCASE should have removed
+--echo # corresponding rows from child tables. There should not any mismatch
+--echo # of 'id' field between parent->child.
+--echo #
+SELECT * FROM matchmaking_group_users WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
+SELECT * FROM matchmaking_group_maps WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
+
+--echo #
+--echo # Rows with id=11 should be present
+--echo #
+SELECT * FROM matchmaking_group_users;
+SELECT * FROM matchmaking_group_maps;
+
+--connection master
+DELETE FROM users WHERE id = 2;
+--sync_slave_with_master
+
+--echo #
+--echo # No rows should be present in both the child tables
+--echo #
+SELECT * FROM matchmaking_group_users;
+SELECT * FROM matchmaking_group_maps;
+
+--connection master
+DROP TABLE matchmaking_group_maps, matchmaking_group_users, matchmaking_groups, users;
+--sync_slave_with_master
+
+--echo #
+--echo # Test case 3: KEY on a virtual column with ON UPDATE CASCADE
+--echo #
+
+--connection master
+CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1, 80);
+
+CREATE TABLE t2 (a INT KEY, b INT,
+ v_col int as (b+1) virtual, KEY (v_col),
+ CONSTRAINT b FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE CASCADE
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (51, 1, default);
+--sync_slave_with_master
+
+--connection master
+UPDATE t1 SET a = 50 WHERE a = 1;
+
+--echo #
+--echo # Master: Verify that ON UPDATE CASCADE works fine
+--echo # old_row: (51, 1, 2) ON UPDATE New_row: (51, 50, 51)
+--echo #
+SELECT * FROM t2 WHERE b=50;
+--sync_slave_with_master
+
+--echo #
+--echo # Slave: Verify that ON UPDATE CASCADE works fine
+--echo # old_row: (51, 1, 2) ON UPDATE New_row: (51, 50, 51)
+--echo #
+SELECT * FROM t2 WHERE b=50;
+
+--connection master
+DROP TABLE t2, t1;
+--sync_slave_with_master
+
+--echo #
+--echo # Test case 4: Define triggers on master, their results should be
+--echo # replicated as part of row events and they should be
+--echo # applied on slave with the default
+--echo # slave_run_triggers_for_rbr=NO
+--echo #
+
+# In row-based replication, the binary log contains row changes. It will have
+# both the changes made by the statement itself, and the changes made by the
+# triggers that were invoked by the statement. Slave server(s) do not need to
+# run triggers for row changes they are applying. Hence verify that this
+# property remains the same and data should be available as if trigger was
+# executed. Please note by default slave_run_triggers_for_rbr=NO.
+
+--connection master
+CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE t2 (count INT NOT NULL) ENGINE=InnoDB;
+CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (1);
+INSERT INTO t1 VALUES (2),(3);
+--sync_slave_with_master
+
+SHOW GLOBAL VARIABLES LIKE 'slave_run_triggers_for_rbr';
+--echo #
+--echo # As two rows are inserted in table 't1', two rows should get inserted
+--echo # into table 't2' as part of trigger.
+--echo #
+--let $assert_cond= COUNT(*) = 2 FROM t2
+--let $assert_text= Table t2 should have two rows.
+--source include/assert.inc
+
+--connection master
+DROP TABLE t1,t2;
+--sync_slave_with_master
+
+--echo #
+--echo # Test case 5: Define triggers + Foreign Keys on master, their results
+--echo # should be replicated as part of row events and master
+--echo # and slave should be in sync.
+--echo #
+--connection master
+CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE t2 (t1_id INT NOT NULL,
+ v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id),
+ CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
+CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t3 VALUES (1);
+
+INSERT INTO t1 VALUES (2),(3);
+INSERT INTO t2 VALUES (2, default), (3, default);
+--sync_slave_with_master
+
+--echo #
+--echo # As two rows are inserted in table 't1', two rows should get inserted
+--echo # into table 't3' as part of trigger.
+--echo #
+--let $assert_cond= COUNT(*) = 2 FROM t3
+--let $assert_text= Table t3 should have two rows.
+--source include/assert.inc
+
+--echo #
+--echo # Verify ON DELETE CASCASE correctness
+--echo #
+--connection master
+DELETE FROM t1 WHERE id=2;
+--sync_slave_with_master
+
+--connection master
+--let $diff_tables= master:test.t1, slave:test.t1
+--source include/diff_tables.inc
+--let $diff_tables= master:test.t2, slave:test.t2
+--source include/diff_tables.inc
+--let $diff_tables= master:test.t3, slave:test.t3
+--source include/diff_tables.inc
+
+DROP TABLE t3,t2,t1;
+--sync_slave_with_master
+
+#
+# Test case: Triggers only on slave
+#
+--write_file $MYSQLTEST_VARDIR/tmp/trig_on_slave.inc PROCEDURE
+ if ($slave_run_triggers_for_rbr == '') {
+ --die !!!ERROR IN TEST: you must set $slave_run_triggers_for_rbr
+ }
+
+--connection slave
+SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr;
+--eval SET GLOBAL slave_run_triggers_for_rbr= $slave_run_triggers_for_rbr;
+SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%';
+
+--connection master
+CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE t2 (t1_id INT NOT NULL,
+ v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col),
+ KEY (t1_id), CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
+--sync_slave_with_master
+
+CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1);
+
+--connection master
+INSERT INTO t1 VALUES (2),(3);
+INSERT INTO t2 VALUES (2, default), (3, default);
+--sync_slave_with_master
+
+if ($slave_run_triggers_for_rbr == 'NO') {
+--echo #
+--echo # Count must be 0
+--echo #
+--let $assert_cond= COUNT(*) = 0 FROM t3
+--let $assert_text= Table t3 should have zero rows.
+--source include/assert.inc
+}
+if ($slave_run_triggers_for_rbr == 'YES') {
+--echo #
+--echo # Count must be 2
+--echo #
+--let $assert_cond= COUNT(*) = 2 FROM t3
+--let $assert_text= Table t3 should have two rows.
+--source include/assert.inc
+}
+
+--connection master
+DELETE FROM t1 WHERE id=2;
+--sync_slave_with_master
+SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr;
+
+--echo #
+--echo # Verify t1, t2 are consistent on slave.
+--echo #
+--let $diff_tables= master:test.t1, slave:test.t1
+--source include/diff_tables.inc
+--let $diff_tables= master:test.t2, slave:test.t2
+--source include/diff_tables.inc
+
+--connection master
+DROP TABLE t3,t2,t1;
+--sync_slave_with_master
+#END OF
+PROCEDURE
+
+--echo #
+--echo # Test case 6: Triggers are present only on slave and
+--echo # 'slave_run_triggers_for_rbr=NO'
+--echo #
+--let $slave_run_triggers_for_rbr=NO
+--source $MYSQLTEST_VARDIR/tmp/trig_on_slave.inc
+
+--echo #
+--echo # Test case 7: Triggers are present only on slave and
+--echo # 'slave_run_triggers_for_rbr=YES'
+--echo #
+--let $slave_run_triggers_for_rbr=YES
+--source $MYSQLTEST_VARDIR/tmp/trig_on_slave.inc
+--remove_file $MYSQLTEST_VARDIR/tmp/trig_on_slave.inc
+
+#
+# Test case: Trigger and Foreign Key are present only on slave
+#
+--write_file $MYSQLTEST_VARDIR/tmp/trig_fk_on_slave.inc PROCEDURE
+ if ($slave_run_triggers_for_rbr == '') {
+ --die !!!ERROR IN TEST: you must set $slave_run_triggers_for_rbr
+ }
+
+--connection slave
+SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr;
+--eval SET GLOBAL slave_run_triggers_for_rbr= $slave_run_triggers_for_rbr;
+SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%';
+
+--connection master
+CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+SET sql_log_bin=0;
+CREATE TABLE t2 (t1_id INT NOT NULL,v_col INT AS (t1_id+1) VIRTUAL) ENGINE=INNODB;
+SET sql_log_bin=1;
+CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
+--sync_slave_with_master
+
+# Have foreign key and trigger on slave.
+CREATE TABLE t2 (t1_id INT NOT NULL,
+ v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id),
+ CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1);
+
+--connection master
+INSERT INTO t1 VALUES (2),(3);
+INSERT INTO t2 VALUES (2, default), (3, default);
+--sync_slave_with_master
+
+if ($slave_run_triggers_for_rbr == 'NO') {
+--echo #
+--echo # Count must be 0
+--echo #
+--let $assert_cond= COUNT(*) = 0 FROM t3
+--let $assert_text= Table t3 should have zero rows.
+--source include/assert.inc
+}
+if ($slave_run_triggers_for_rbr == 'YES') {
+--echo #
+--echo # Count must be 2
+--echo #
+--let $assert_cond= COUNT(*) = 2 FROM t3
+--let $assert_text= Table t3 should have two rows.
+--source include/assert.inc
+}
+
+--connection master
+DELETE FROM t1 WHERE id=2;
+--echo # t1: Should have one row
+SELECT * FROM t1;
+--echo # t2: Should have two rows
+SELECT * FROM t2;
+--sync_slave_with_master
+--echo # t1: Should have one row
+SELECT * FROM t1;
+--echo # t2: Should have one row on slave due to ON DELETE CASCASE
+SELECT * FROM t2;
+SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr;
+
+--connection master
+DROP TABLE t3,t2,t1;
+--sync_slave_with_master
+#END OF
+PROCEDURE
+
+--echo #
+--echo # Test case 8: Triggers and Foreign Keys are present only on slave and
+--echo # 'slave_run_triggers_for_rbr=NO'
+--echo #
+--let $slave_run_triggers_for_rbr=NO
+--source $MYSQLTEST_VARDIR/tmp/trig_fk_on_slave.inc
+
+--echo #
+--echo # Test case 9: Triggers are Foreign Keys are present only on slave and
+--echo # 'slave_run_triggers_for_rbr=YES'
+--echo #
+--let $slave_run_triggers_for_rbr=YES
+--source $MYSQLTEST_VARDIR/tmp/trig_fk_on_slave.inc
+--remove_file $MYSQLTEST_VARDIR/tmp/trig_fk_on_slave.inc
+
+--source include/rpl_end.inc
diff --git a/sql/log_event.cc b/sql/log_event.cc
index c649e1f64fa..c32f31db1f6 100644
--- a/sql/log_event.cc
+++ b/sql/log_event.cc
@@ -10718,7 +10718,7 @@ int Rows_log_event::do_add_row_data(uchar *row_data, size_t length)
There was the same problem with MERGE MYISAM tables and so here we try to
go the same way.
*/
-static void restore_empty_query_table_list(LEX *lex)
+inline void restore_empty_query_table_list(LEX *lex)
{
if (lex->first_not_own_table())
(*lex->first_not_own_table()->prev_global)= NULL;
@@ -10733,6 +10733,8 @@ int Rows_log_event::do_apply_event(rpl_group_info *rgi)
TABLE* table;
DBUG_ENTER("Rows_log_event::do_apply_event(Relay_log_info*)");
int error= 0;
+ LEX *lex= thd->lex;
+ uint8 new_trg_event_map= get_trg_event_map();
/*
If m_table_id == ~0ULL, then we have a dummy event that does not
contain any data. In that case, we just remove all tables in the
@@ -10823,27 +10825,29 @@ int Rows_log_event::do_apply_event(rpl_group_info *rgi)
DBUG_ASSERT(!debug_sync_set_action(thd, STRING_WITH_LEN(action)));
};);
- if (slave_run_triggers_for_rbr)
- {
- LEX *lex= thd->lex;
- uint8 new_trg_event_map= get_trg_event_map();
-
- /*
- Trigger's procedures work with global table list. So we have to add
- rgi->tables_to_lock content there to get trigger's in the list.
+ /*
+ Trigger's procedures work with global table list. So we have to add
+ rgi->tables_to_lock content there to get trigger's in the list.
- Then restore_empty_query_table_list() restore the list as it was
- */
- DBUG_ASSERT(lex->query_tables == NULL);
- if ((lex->query_tables= rgi->tables_to_lock))
- rgi->tables_to_lock->prev_global= &lex->query_tables;
+ Then restore_empty_query_table_list() restore the list as it was
+ */
+ DBUG_ASSERT(lex->query_tables == NULL);
+ if ((lex->query_tables= rgi->tables_to_lock))
+ rgi->tables_to_lock->prev_global= &lex->query_tables;
- for (TABLE_LIST *tables= rgi->tables_to_lock; tables;
- tables= tables->next_global)
+ for (TABLE_LIST *tables= rgi->tables_to_lock; tables;
+ tables= tables->next_global)
+ {
+ if (slave_run_triggers_for_rbr)
{
tables->trg_event_map= new_trg_event_map;
lex->query_tables_last= &tables->next_global;
}
+ else if (!WSREP_ON)
+ {
+ tables->slave_fk_event_map= new_trg_event_map;
+ lex->query_tables_last= &tables->next_global;
+ }
}
if (open_and_lock_tables(thd, rgi->tables_to_lock, FALSE, 0))
{
@@ -11193,8 +11197,7 @@ int Rows_log_event::do_apply_event(rpl_group_info *rgi)
}
/* remove trigger's tables */
- if (slave_run_triggers_for_rbr)
- restore_empty_query_table_list(thd->lex);
+ restore_empty_query_table_list(thd->lex);
#if defined(WITH_WSREP) && defined(HAVE_QUERY_CACHE)
if (WSREP(thd) && thd->wsrep_exec_mode == REPL_RECV)
@@ -11212,8 +11215,7 @@ int Rows_log_event::do_apply_event(rpl_group_info *rgi)
DBUG_RETURN(error);
err:
- if (slave_run_triggers_for_rbr)
- restore_empty_query_table_list(thd->lex);
+ restore_empty_query_table_list(thd->lex);
rgi->slave_close_thread_tables(thd);
DBUG_RETURN(error);
}
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 8e57ea437b6..b8d18abb50c 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -4341,6 +4341,70 @@ bool table_already_fk_prelocked(TABLE_LIST *tl, LEX_STRING *db,
return false;
}
+/**
+ Extend the table_list to include foreign tables for prelocking.
+
+ @param[in] thd Thread context.
+ @param[in] prelocking_ctx Prelocking context of the statement.
+ @param[in] table_list Table list element for table.
+ @param[in] sp Routine body.
+ @param[out] need_prelocking Set to TRUE if method detects that prelocking
+ required, not changed otherwise.
+
+ @retval FALSE Success.
+ @retval TRUE Failure (OOM).
+*/
+inline bool
+prepare_fk_prelocking_list(THD *thd, Query_tables_list *prelocking_ctx,
+ TABLE_LIST *table_list, bool *need_prelocking,
+ uint8 op)
+{
+ List <FOREIGN_KEY_INFO> fk_list;
+ List_iterator<FOREIGN_KEY_INFO> fk_list_it(fk_list);
+ FOREIGN_KEY_INFO *fk;
+ Query_arena *arena, backup;
+
+ arena= thd->activate_stmt_arena_if_needed(&backup);
+
+ table_list->table->file->get_parent_foreign_key_list(thd, &fk_list);
+ if (thd->is_error())
+ {
+ if (arena)
+ thd->restore_active_arena(arena, &backup);
+ return TRUE;
+ }
+
+ *need_prelocking= TRUE;
+
+ while ((fk= fk_list_it++))
+ {
+ // FK_OPTION_RESTRICT and FK_OPTION_NO_ACTION only need read access
+ static bool can_write[]= { true, false, true, true, false, true };
+ thr_lock_type lock_type;
+
+ if ((op & (1 << TRG_EVENT_DELETE) && can_write[fk->delete_method])
+ || (op & (1 << TRG_EVENT_UPDATE) && can_write[fk->update_method]))
+ lock_type= TL_WRITE_ALLOW_WRITE;
+ else
+ lock_type= TL_READ;
+
+ if (table_already_fk_prelocked(prelocking_ctx->query_tables,
+ fk->foreign_db, fk->foreign_table,
+ lock_type))
+ continue;
+
+ TABLE_LIST *tl= (TABLE_LIST *) thd->alloc(sizeof(TABLE_LIST));
+ tl->init_one_table_for_prelocking(fk->foreign_db->str, fk->foreign_db->length,
+ fk->foreign_table->str, fk->foreign_table->length,
+ NULL, lock_type, false, table_list->belong_to_view,
+ op, &prelocking_ctx->query_tables_last);
+ }
+ if (arena)
+ thd->restore_active_arena(arena, &backup);
+
+ return FALSE;
+}
+
/**
Defines how prelocking algorithm for DML statements should handle table list
@@ -4381,55 +4445,21 @@ handle_table(THD *thd, Query_tables_list *prelocking_ctx,
add_tables_and_routines_for_triggers(thd, prelocking_ctx, table_list))
return TRUE;
}
-
if (table_list->table->file->referenced_by_foreign_key())
{
- List <FOREIGN_KEY_INFO> fk_list;
- List_iterator<FOREIGN_KEY_INFO> fk_list_it(fk_list);
- FOREIGN_KEY_INFO *fk;
- Query_arena *arena, backup;
-
- arena= thd->activate_stmt_arena_if_needed(&backup);
-
- table_list->table->file->get_parent_foreign_key_list(thd, &fk_list);
- if (thd->is_error())
- {
- if (arena)
- thd->restore_active_arena(arena, &backup);
- return TRUE;
- }
-
- *need_prelocking= TRUE;
-
- while ((fk= fk_list_it++))
- {
- // FK_OPTION_RESTRICT and FK_OPTION_NO_ACTION only need read access
- static bool can_write[]= { true, false, true, true, false, true };
- uint8 op= table_list->trg_event_map;
- thr_lock_type lock_type;
-
- if ((op & (1 << TRG_EVENT_DELETE) && can_write[fk->delete_method])
- || (op & (1 << TRG_EVENT_UPDATE) && can_write[fk->update_method]))
- lock_type= TL_WRITE_ALLOW_WRITE;
- else
- lock_type= TL_READ;
-
- if (table_already_fk_prelocked(prelocking_ctx->query_tables,
- fk->foreign_db, fk->foreign_table,
- lock_type))
- continue;
-
- TABLE_LIST *tl= (TABLE_LIST *) thd->alloc(sizeof(TABLE_LIST));
- tl->init_one_table_for_prelocking(fk->foreign_db->str, fk->foreign_db->length,
- fk->foreign_table->str, fk->foreign_table->length,
- NULL, lock_type, false, table_list->belong_to_view,
- op, &prelocking_ctx->query_tables_last);
- }
- if (arena)
- thd->restore_active_arena(arena, &backup);
+ return (prepare_fk_prelocking_list(thd, prelocking_ctx, table_list,
+ need_prelocking,
+ table_list->trg_event_map));
}
}
+ else if (table_list->slave_fk_event_map &&
+ table_list->table->file->referenced_by_foreign_key())
+ {
+ return (prepare_fk_prelocking_list(thd, prelocking_ctx,
+ table_list, need_prelocking,
+ table_list->slave_fk_event_map));
+ }
return FALSE;
}
diff --git a/sql/table.h b/sql/table.h
index 9a864f7ce9f..57706655d9b 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -2277,8 +2277,12 @@ struct TABLE_LIST
Indicates what triggers we need to pre-load for this TABLE_LIST
when opening an associated TABLE. This is filled after
the parsed tree is created.
+
+ slave_fk_event_map is filled on the slave side with bitmaps value
+ representing row-based event operation to help find and prelock
+ possible FK constrain-related child tables.
*/
- uint8 trg_event_map;
+ uint8 trg_event_map, slave_fk_event_map;
/* TRUE <=> this table is a const one and was optimized away. */
bool optimized_away;
2
2
[Commits] 3f48bc08b92: MDEV-23033: All slaves crash once in ~24 hours and loop restart with signal 11
by sujatha 31 Dec '20
by sujatha 31 Dec '20
31 Dec '20
revision-id: 3f48bc08b92b2734c2af7408c246b96338675205 (mariadb-10.2.31-646-g3f48bc08b92)
parent(s): 78292047a4747ccd9210ba36a185a1dbe825de89
author: Sujatha
committer: Sujatha
timestamp: 2020-12-31 15:43:53 +0530
message:
MDEV-23033: All slaves crash once in ~24 hours and loop restart with signal 11
Problem:
=======
Upon deleting or updating a row in a parent table (with primary key), if
the child table has virtual column and an associated key with ON UPDATE
CASCADE/ON DELETE CASCADE, it will result in slave crash.
Analysis:
========
Tables which are related through foreign key require prelocking similar to
triggers. i.e If a table has triggers/foreign keys we should add all tables
and routines used by them to the prelocking set. This prelocking happens
during 'open_and_lock_tables' call. Each table being opened is checked for
foreign key references. If foreign key reference exists then the child
table is opened and it is linked to the table_list. Upon any modification
to parent table its corresponding child tables are retried from table_list
and they are updated accordingly. This prelocking work fine on master.
On slave prelocking works for following cases.
- Statement/mixed based replication
- In row based replication when trigger execution is enabled through
'slave_run_triggers_for_rbr=YES/LOGGING/ENFORCE'
Otherwise it results in an assert/crash, as the parent table will not find
the corresponding child table and it will be NULL. Dereferencing NULL
pointer leads to slave server exit.
Fix:
===
Introduce a new 'slave_fk_event_map' flag similar to 'trg_event_map'. This
flag will ensure that when foreign key is enabled in row based replication
all the parent and child tables are prelocked, so that parent is able to
locate the child table.
---
mysql-test/suite/rpl/r/rpl_row_vcol_crash.result | 381 +++++++++++++++++++++
mysql-test/suite/rpl/t/rpl_row_vcol_crash.test | 410 +++++++++++++++++++++++
sql/log_event.cc | 44 ++-
sql/sql_base.cc | 6 +-
sql/table.h | 6 +-
5 files changed, 821 insertions(+), 26 deletions(-)
diff --git a/mysql-test/suite/rpl/r/rpl_row_vcol_crash.result b/mysql-test/suite/rpl/r/rpl_row_vcol_crash.result
new file mode 100644
index 00000000000..e2a5d9b65e5
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_row_vcol_crash.result
@@ -0,0 +1,381 @@
+include/master-slave.inc
+[connection master]
+#
+# Test case: KEY on a virtual column with ON DELETE CASCADE
+#
+CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1),(2),(3);
+CREATE TABLE t2 (id INT NOT NULL PRIMARY KEY,
+t1_id INT NOT NULL,
+v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id),
+CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (90,1,NULL);
+INSERT INTO t2 VALUES (91,2,default);
+DELETE FROM t1 WHERE id=1;
+connection slave;
+#
+# Verify data consistency on slave
+#
+include/diff_tables.inc [master:test.t1, slave:test.t1]
+include/diff_tables.inc [master:test.t2, slave:test.t2]
+connection master;
+DROP TABLE t2,t1;
+connection slave;
+#
+# Test case: Verify cascading on delete with multiple levels of child
+# tables works fine.
+# Parent table: users
+# Child tables: matchmaking_groups, matchmaking_group_users
+# Parent table: matchmaking_groups
+# Child tables: matchmaking_group_users, matchmaking_group_maps
+#
+# Verify ON DELETE CASCASE works fine when a row is deleted
+# from parent table users
+# matchmaking_groups->matchmaking_group_users->matchmaking_group_maps
+# users->matchmaking_group_users->matchmaking_group_maps
+#
+connection master;
+CREATE TABLE users (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
+name VARCHAR(32) NOT NULL DEFAULT ''
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+CREATE TABLE matchmaking_groups (
+id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
+host_user_id INT UNSIGNED NOT NULL UNIQUE,
+v_col INT AS (host_user_id+1) VIRTUAL, KEY (v_col),
+CONSTRAINT FOREIGN KEY (host_user_id) REFERENCES users (id)
+ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+CREATE TABLE matchmaking_group_users (
+matchmaking_group_id BIGINT UNSIGNED NOT NULL,
+user_id INT UNSIGNED NOT NULL,
+v_col1 int as (user_id+1) virtual, KEY (v_col1),
+PRIMARY KEY (matchmaking_group_id,user_id),
+UNIQUE KEY user_id (user_id),
+CONSTRAINT FOREIGN KEY (matchmaking_group_id)
+REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE,
+CONSTRAINT FOREIGN KEY (user_id)
+REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+CREATE TABLE matchmaking_group_maps (
+matchmaking_group_id BIGINT UNSIGNED NOT NULL,
+map_id TINYINT UNSIGNED NOT NULL,
+v_col2 INT AS (map_id+1) VIRTUAL, KEY (v_col2),
+PRIMARY KEY (matchmaking_group_id,map_id),
+CONSTRAINT FOREIGN KEY (matchmaking_group_id)
+REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+connection slave;
+connection master;
+INSERT INTO users VALUES (NULL,'foo'),(NULL,'bar');
+INSERT INTO matchmaking_groups VALUES (10,1,default),(11,2,default);
+INSERT INTO matchmaking_group_users VALUES (10,1,default),(11,2,default);
+INSERT INTO matchmaking_group_maps VALUES (10,55,default),(11,66,default);
+DELETE FROM matchmaking_groups WHERE id = 10;
+connection slave;
+#
+# No rows should be returned as ON DELETE CASCASE should have removed
+# corresponding rows from child tables. There should not any mismatch
+# of 'id' field between parent->child.
+#
+SELECT * FROM matchmaking_group_users WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
+matchmaking_group_id user_id v_col1
+SELECT * FROM matchmaking_group_maps WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
+matchmaking_group_id map_id v_col2
+#
+# Rows with id=11 should be present
+#
+SELECT * FROM matchmaking_group_users;
+matchmaking_group_id user_id v_col1
+11 2 3
+SELECT * FROM matchmaking_group_maps;
+matchmaking_group_id map_id v_col2
+11 66 67
+connection master;
+DELETE FROM users WHERE id = 2;
+connection slave;
+#
+# No rows should be present in both the child tables
+#
+SELECT * FROM matchmaking_group_users;
+matchmaking_group_id user_id v_col1
+SELECT * FROM matchmaking_group_maps;
+matchmaking_group_id map_id v_col2
+connection master;
+DROP TABLE matchmaking_group_maps, matchmaking_group_users, matchmaking_groups, users;
+connection slave;
+#
+# Test case: ON UPDATE CASCADE scenario
+#
+connection master;
+CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1, 80);
+CREATE TABLE t2 (a INT KEY, b INT,
+v_col int as (b+1) virtual, KEY (v_col),
+CONSTRAINT b FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE CASCADE
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (51, 1, default);
+connection slave;
+connection master;
+UPDATE t1 SET a = 50 WHERE a = 1;
+#
+# Master: Verify that ON UPDATE CASCADE works fine
+# old_row: (51, 1, 2) ON UPDATE New_row: (51, 50, 51)
+#
+SELECT * FROM t2 WHERE b=50;
+a b v_col
+51 50 51
+connection slave;
+#
+# Slave: Verify that ON UPDATE CASCADE works fine
+# old_row: (51, 1, 2) ON UPDATE New_row: (51, 50, 51)
+#
+SELECT * FROM t2 WHERE b=50;
+a b v_col
+51 50 51
+connection master;
+DROP TABLE t2, t1;
+connection slave;
+#
+# Test case: When triggers are defined on master they should be
+# replicated as part of row events and they should be
+# applied on slave with the default
+# slave_run_tiggers_for_rbr=NO
+#
+connection master;
+CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE t2 (count INT NOT NULL) ENGINE=InnoDB;
+CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (1);
+INSERT INTO t1 VALUES (2),(3);
+connection slave;
+SHOW GLOBAL VARIABLES LIKE 'slave_run_triggers_for_rbr';
+Variable_name Value
+slave_run_triggers_for_rbr NO
+#
+# As two rows are inserted in table 't1', two rows should get inserted
+# into table 't2' as part of trigger.
+#
+include/assert.inc [Table t2 should have two rows.]
+connection master;
+DROP TABLE t1,t2;
+connection slave;
+#
+# Test case: On master create triggers and tables with foreign key
+# relation. Upon replication to slave verify their results
+# are fine. Master and Slave should be in sync.
+#
+connection master;
+CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE t2 (t1_id INT NOT NULL,
+v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id),
+CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
+CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t3 VALUES (1);
+INSERT INTO t1 VALUES (2),(3);
+INSERT INTO t2 VALUES (2, default), (3, default);
+connection slave;
+#
+# As two rows are inserted in table 't1', two rows should get inserted
+# into table 't3' as part of trigger.
+#
+include/assert.inc [Table t3 should have two rows.]
+#
+# Verify ON DELETE CASCASE correctness
+#
+connection master;
+DELETE FROM t1 WHERE id=2;
+connection slave;
+connection master;
+include/diff_tables.inc [master:test.t1, slave:test.t1]
+include/diff_tables.inc [master:test.t2, slave:test.t2]
+include/diff_tables.inc [master:test.t3, slave:test.t3]
+DROP TABLE t3,t2,t1;
+connection slave;
+#
+# Test case: Triggers are present only on slave and
+# 'slave_run_triggers_for_rbr=NO'
+#
+connection slave;
+SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr;
+SET GLOBAL slave_run_triggers_for_rbr= NO;;
+SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%';
+Variable_name Value
+slave_run_triggers_for_rbr NO
+connection master;
+CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE t2 (t1_id INT NOT NULL,
+v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col),
+KEY (t1_id), CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
+connection slave;
+CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1);
+connection master;
+INSERT INTO t1 VALUES (2),(3);
+INSERT INTO t2 VALUES (2, default), (3, default);
+connection slave;
+#
+# Count must be 0
+#
+include/assert.inc [Table t3 should have zero rows.]
+connection master;
+DELETE FROM t1 WHERE id=2;
+connection slave;
+SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr;
+#
+# Verify t1,t2 are consistent on slave.
+#
+include/diff_tables.inc [master:test.t1, slave:test.t1]
+include/diff_tables.inc [master:test.t2, slave:test.t2]
+connection master;
+DROP TABLE t3,t2,t1;
+connection slave;
+#
+# Test case: Triggers are present only on slave and
+# 'slave_run_triggers_for_rbr=YES'
+#
+connection slave;
+SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr;
+SET GLOBAL slave_run_triggers_for_rbr= YES;;
+SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%';
+Variable_name Value
+slave_run_triggers_for_rbr YES
+connection master;
+CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE t2 (t1_id INT NOT NULL,
+v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col),
+KEY (t1_id), CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
+connection slave;
+CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1);
+connection master;
+INSERT INTO t1 VALUES (2),(3);
+INSERT INTO t2 VALUES (2, default), (3, default);
+connection slave;
+#
+# Count must be 2
+#
+include/assert.inc [Table t3 should have two rows.]
+connection master;
+DELETE FROM t1 WHERE id=2;
+connection slave;
+SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr;
+#
+# Verify t1,t2 are consistent on slave.
+#
+include/diff_tables.inc [master:test.t1, slave:test.t1]
+include/diff_tables.inc [master:test.t2, slave:test.t2]
+connection master;
+DROP TABLE t3,t2,t1;
+connection slave;
+#
+# Test case: Triggers and Foreign Keys are present only on slave and
+# 'slave_run_triggers_for_rbr=NO'
+#
+connection slave;
+SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr;
+SET GLOBAL slave_run_triggers_for_rbr= NO;;
+SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%';
+Variable_name Value
+slave_run_triggers_for_rbr NO
+connection master;
+CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+SET sql_log_bin=0;
+CREATE TABLE t2 (t1_id INT NOT NULL,v_col INT AS (t1_id+1) VIRTUAL) ENGINE=INNODB;
+SET sql_log_bin=1;
+CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
+connection slave;
+CREATE TABLE t2 (t1_id INT NOT NULL,
+v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id),
+CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1);
+connection master;
+INSERT INTO t1 VALUES (2),(3);
+INSERT INTO t2 VALUES (2, default), (3, default);
+connection slave;
+#
+# Count must be 0
+#
+include/assert.inc [Table t3 should have zero rows.]
+connection master;
+DELETE FROM t1 WHERE id=2;
+# t1: Should have one row
+SELECT * FROM t1;
+id
+3
+# t2: Should have two rows
+SELECT * FROM t2;
+t1_id v_col
+2 3
+3 4
+connection slave;
+# t1: Should have one row
+SELECT * FROM t1;
+id
+3
+# t2: Should have one row on slave due to ON DELETE CASCASE
+SELECT * FROM t2;
+t1_id v_col
+3 4
+SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr;
+connection master;
+DROP TABLE t3,t2,t1;
+connection slave;
+#
+# Test case: Triggers are Foreign Keys are present only on slave and
+# 'slave_run_triggers_for_rbr=YES'
+#
+connection slave;
+SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr;
+SET GLOBAL slave_run_triggers_for_rbr= YES;;
+SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%';
+Variable_name Value
+slave_run_triggers_for_rbr YES
+connection master;
+CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+SET sql_log_bin=0;
+CREATE TABLE t2 (t1_id INT NOT NULL,v_col INT AS (t1_id+1) VIRTUAL) ENGINE=INNODB;
+SET sql_log_bin=1;
+CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
+connection slave;
+CREATE TABLE t2 (t1_id INT NOT NULL,
+v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id),
+CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1);
+connection master;
+INSERT INTO t1 VALUES (2),(3);
+INSERT INTO t2 VALUES (2, default), (3, default);
+connection slave;
+#
+# Count must be 2
+#
+include/assert.inc [Table t3 should have two rows.]
+connection master;
+DELETE FROM t1 WHERE id=2;
+# t1: Should have one row
+SELECT * FROM t1;
+id
+3
+# t2: Should have two rows
+SELECT * FROM t2;
+t1_id v_col
+2 3
+3 4
+connection slave;
+# t1: Should have one row
+SELECT * FROM t1;
+id
+3
+# t2: Should have one row on slave due to ON DELETE CASCASE
+SELECT * FROM t2;
+t1_id v_col
+3 4
+SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr;
+connection master;
+DROP TABLE t3,t2,t1;
+connection slave;
+include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_row_vcol_crash.test b/mysql-test/suite/rpl/t/rpl_row_vcol_crash.test
new file mode 100644
index 00000000000..2ed7c219aeb
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_row_vcol_crash.test
@@ -0,0 +1,410 @@
+# ==== Purpose ====
+#
+# Test verifies that, slave doesn't report any assert on UPDATE or DELETE of
+# row which tries to update the virtual columns with associated KEYs.
+#
+# ==== References ====
+#
+# MDEV-23033: All slaves crash once in ~24 hours and loop restart with signal 11
+#
+
+--source include/have_binlog_format_row.inc
+--source include/have_innodb.inc
+--source include/master-slave.inc
+
+
+--echo #
+--echo # Test case: KEY on a virtual column with ON DELETE CASCADE
+--echo #
+CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1),(2),(3);
+
+CREATE TABLE t2 (id INT NOT NULL PRIMARY KEY,
+ t1_id INT NOT NULL,
+ v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id),
+ CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+
+INSERT INTO t2 VALUES (90,1,NULL);
+INSERT INTO t2 VALUES (91,2,default);
+
+# Following query results in an assert on slave
+DELETE FROM t1 WHERE id=1;
+--sync_slave_with_master
+
+--echo #
+--echo # Verify data consistency on slave
+--echo #
+--let $diff_tables= master:test.t1, slave:test.t1
+--source include/diff_tables.inc
+--let $diff_tables= master:test.t2, slave:test.t2
+--source include/diff_tables.inc
+
+--connection master
+DROP TABLE t2,t1;
+--sync_slave_with_master
+
+--echo #
+--echo # Test case: Verify cascading on delete with multiple levels of child
+--echo # tables works fine.
+--echo # Parent table: users
+--echo # Child tables: matchmaking_groups, matchmaking_group_users
+--echo # Parent table: matchmaking_groups
+--echo # Child tables: matchmaking_group_users, matchmaking_group_maps
+--echo #
+--echo # Verify ON DELETE CASCASE works fine when a row is deleted
+--echo # from parent table users
+--echo # matchmaking_groups->matchmaking_group_users->matchmaking_group_maps
+--echo # users->matchmaking_group_users->matchmaking_group_maps
+--echo #
+
+--connection master
+CREATE TABLE users (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
+ name VARCHAR(32) NOT NULL DEFAULT ''
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE matchmaking_groups (
+ id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
+ host_user_id INT UNSIGNED NOT NULL UNIQUE,
+ v_col INT AS (host_user_id+1) VIRTUAL, KEY (v_col),
+ CONSTRAINT FOREIGN KEY (host_user_id) REFERENCES users (id)
+ ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE matchmaking_group_users (
+ matchmaking_group_id BIGINT UNSIGNED NOT NULL,
+ user_id INT UNSIGNED NOT NULL,
+ v_col1 int as (user_id+1) virtual, KEY (v_col1),
+ PRIMARY KEY (matchmaking_group_id,user_id),
+ UNIQUE KEY user_id (user_id),
+ CONSTRAINT FOREIGN KEY (matchmaking_group_id)
+ REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT FOREIGN KEY (user_id)
+ REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE matchmaking_group_maps (
+ matchmaking_group_id BIGINT UNSIGNED NOT NULL,
+ map_id TINYINT UNSIGNED NOT NULL,
+ v_col2 INT AS (map_id+1) VIRTUAL, KEY (v_col2),
+ PRIMARY KEY (matchmaking_group_id,map_id),
+ CONSTRAINT FOREIGN KEY (matchmaking_group_id)
+ REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--sync_slave_with_master
+
+--connection master
+INSERT INTO users VALUES (NULL,'foo'),(NULL,'bar');
+INSERT INTO matchmaking_groups VALUES (10,1,default),(11,2,default);
+INSERT INTO matchmaking_group_users VALUES (10,1,default),(11,2,default);
+INSERT INTO matchmaking_group_maps VALUES (10,55,default),(11,66,default);
+
+DELETE FROM matchmaking_groups WHERE id = 10;
+--sync_slave_with_master
+
+--echo #
+--echo # No rows should be returned as ON DELETE CASCASE should have removed
+--echo # corresponding rows from child tables. There should not any mismatch
+--echo # of 'id' field between parent->child.
+--echo #
+SELECT * FROM matchmaking_group_users WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
+SELECT * FROM matchmaking_group_maps WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
+
+--echo #
+--echo # Rows with id=11 should be present
+--echo #
+SELECT * FROM matchmaking_group_users;
+SELECT * FROM matchmaking_group_maps;
+
+--connection master
+DELETE FROM users WHERE id = 2;
+--sync_slave_with_master
+
+--echo #
+--echo # No rows should be present in both the child tables
+--echo #
+SELECT * FROM matchmaking_group_users;
+SELECT * FROM matchmaking_group_maps;
+
+--connection master
+DROP TABLE matchmaking_group_maps, matchmaking_group_users, matchmaking_groups, users;
+--sync_slave_with_master
+
+--echo #
+--echo # Test case: ON UPDATE CASCADE scenario
+--echo #
+
+--connection master
+CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1, 80);
+
+CREATE TABLE t2 (a INT KEY, b INT,
+ v_col int as (b+1) virtual, KEY (v_col),
+ CONSTRAINT b FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE CASCADE
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (51, 1, default);
+--sync_slave_with_master
+
+--connection master
+UPDATE t1 SET a = 50 WHERE a = 1;
+
+--echo #
+--echo # Master: Verify that ON UPDATE CASCADE works fine
+--echo # old_row: (51, 1, 2) ON UPDATE New_row: (51, 50, 51)
+--echo #
+SELECT * FROM t2 WHERE b=50;
+--sync_slave_with_master
+
+--echo #
+--echo # Slave: Verify that ON UPDATE CASCADE works fine
+--echo # old_row: (51, 1, 2) ON UPDATE New_row: (51, 50, 51)
+--echo #
+SELECT * FROM t2 WHERE b=50;
+
+--connection master
+DROP TABLE t2, t1;
+--sync_slave_with_master
+
+--echo #
+--echo # Test case: When triggers are defined on master they should be
+--echo # replicated as part of row events and they should be
+--echo # applied on slave with the default
+--echo # slave_run_tiggers_for_rbr=NO
+--echo #
+
+# In row-based replication, the binary log contains row changes. It will have
+# both the changes made by the statement itself, and the changes made by the
+# triggers that were invoked by the statement. Slave server(s) do not need to
+# run triggers for row changes they are applying. Hence verify that this
+# property remains the same and data should be available as if trigger was
+# executed. Please note by default slave_run_tiggers_for_rbr=NO.
+
+--connection master
+CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE t2 (count INT NOT NULL) ENGINE=InnoDB;
+CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (1);
+INSERT INTO t1 VALUES (2),(3);
+--sync_slave_with_master
+
+SHOW GLOBAL VARIABLES LIKE 'slave_run_triggers_for_rbr';
+--echo #
+--echo # As two rows are inserted in table 't1', two rows should get inserted
+--echo # into table 't2' as part of trigger.
+--echo #
+--let $assert_cond= COUNT(*) = 2 FROM t2
+--let $assert_text= Table t2 should have two rows.
+--source include/assert.inc
+
+--connection master
+DROP TABLE t1,t2;
+--sync_slave_with_master
+
+--echo #
+--echo # Test case: On master create triggers and tables with foreign key
+--echo # relation. Upon replication to slave verify their results
+--echo # are fine. Master and Slave should be in sync.
+--echo #
+--connection master
+CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE t2 (t1_id INT NOT NULL,
+ v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id),
+ CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
+CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t3 VALUES (1);
+
+INSERT INTO t1 VALUES (2),(3);
+INSERT INTO t2 VALUES (2, default), (3, default);
+--sync_slave_with_master
+
+--echo #
+--echo # As two rows are inserted in table 't1', two rows should get inserted
+--echo # into table 't3' as part of trigger.
+--echo #
+--let $assert_cond= COUNT(*) = 2 FROM t3
+--let $assert_text= Table t3 should have two rows.
+--source include/assert.inc
+
+--echo #
+--echo # Verify ON DELETE CASCASE correctness
+--echo #
+--connection master
+DELETE FROM t1 WHERE id=2;
+--sync_slave_with_master
+
+--connection master
+--let $diff_tables= master:test.t1, slave:test.t1
+--source include/diff_tables.inc
+--let $diff_tables= master:test.t2, slave:test.t2
+--source include/diff_tables.inc
+--let $diff_tables= master:test.t3, slave:test.t3
+--source include/diff_tables.inc
+
+DROP TABLE t3,t2,t1;
+--sync_slave_with_master
+
+#
+# Test case: Triggers only on slave
+#
+--write_file $MYSQLTEST_VARDIR/tmp/trig_on_slave.inc PROCEDURE
+ if ($slave_run_triggers_for_rbr == '') {
+ --die !!!ERROR IN TEST: you must set $slave_run_triggers_for_rbr
+ }
+
+--connection slave
+SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr;
+--eval SET GLOBAL slave_run_triggers_for_rbr= $slave_run_triggers_for_rbr;
+SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%';
+
+--connection master
+CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE t2 (t1_id INT NOT NULL,
+ v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col),
+ KEY (t1_id), CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
+--sync_slave_with_master
+
+CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1);
+
+--connection master
+INSERT INTO t1 VALUES (2),(3);
+INSERT INTO t2 VALUES (2, default), (3, default);
+--sync_slave_with_master
+
+if ($slave_run_triggers_for_rbr == 'NO') {
+--echo #
+--echo # Count must be 0
+--echo #
+--let $assert_cond= COUNT(*) = 0 FROM t3
+--let $assert_text= Table t3 should have zero rows.
+--source include/assert.inc
+}
+if ($slave_run_triggers_for_rbr == 'YES') {
+--echo #
+--echo # Count must be 2
+--echo #
+--let $assert_cond= COUNT(*) = 2 FROM t3
+--let $assert_text= Table t3 should have two rows.
+--source include/assert.inc
+}
+
+--connection master
+DELETE FROM t1 WHERE id=2;
+--sync_slave_with_master
+SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr;
+
+--echo #
+--echo # Verify t1,t2 are consistent on slave.
+--echo #
+--let $diff_tables= master:test.t1, slave:test.t1
+--source include/diff_tables.inc
+--let $diff_tables= master:test.t2, slave:test.t2
+--source include/diff_tables.inc
+
+--connection master
+DROP TABLE t3,t2,t1;
+--sync_slave_with_master
+#END OF
+PROCEDURE
+
+--echo #
+--echo # Test case: Triggers are present only on slave and
+--echo # 'slave_run_triggers_for_rbr=NO'
+--echo #
+--let $slave_run_triggers_for_rbr=NO
+--source $MYSQLTEST_VARDIR/tmp/trig_on_slave.inc
+
+--echo #
+--echo # Test case: Triggers are present only on slave and
+--echo # 'slave_run_triggers_for_rbr=YES'
+--echo #
+--let $slave_run_triggers_for_rbr=YES
+--source $MYSQLTEST_VARDIR/tmp/trig_on_slave.inc
+--remove_file $MYSQLTEST_VARDIR/tmp/trig_on_slave.inc
+
+#
+# Test case: Trigger and Foreign Key are present only on slave
+#
+--write_file $MYSQLTEST_VARDIR/tmp/trig_fk_on_slave.inc PROCEDURE
+ if ($slave_run_triggers_for_rbr == '') {
+ --die !!!ERROR IN TEST: you must set $slave_run_triggers_for_rbr
+ }
+
+--connection slave
+SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr;
+--eval SET GLOBAL slave_run_triggers_for_rbr= $slave_run_triggers_for_rbr;
+SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%';
+
+--connection master
+CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+SET sql_log_bin=0;
+CREATE TABLE t2 (t1_id INT NOT NULL,v_col INT AS (t1_id+1) VIRTUAL) ENGINE=INNODB;
+SET sql_log_bin=1;
+CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB;
+--sync_slave_with_master
+
+# Have foreign key and trigger on slave.
+CREATE TABLE t2 (t1_id INT NOT NULL,
+ v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id),
+ CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1);
+
+--connection master
+INSERT INTO t1 VALUES (2),(3);
+INSERT INTO t2 VALUES (2, default), (3, default);
+--sync_slave_with_master
+
+if ($slave_run_triggers_for_rbr == 'NO') {
+--echo #
+--echo # Count must be 0
+--echo #
+--let $assert_cond= COUNT(*) = 0 FROM t3
+--let $assert_text= Table t3 should have zero rows.
+--source include/assert.inc
+}
+if ($slave_run_triggers_for_rbr == 'YES') {
+--echo #
+--echo # Count must be 2
+--echo #
+--let $assert_cond= COUNT(*) = 2 FROM t3
+--let $assert_text= Table t3 should have two rows.
+--source include/assert.inc
+}
+
+--connection master
+DELETE FROM t1 WHERE id=2;
+--echo # t1: Should have one row
+SELECT * FROM t1;
+--echo # t2: Should have two rows
+SELECT * FROM t2;
+--sync_slave_with_master
+--echo # t1: Should have one row
+SELECT * FROM t1;
+--echo # t2: Should have one row on slave due to ON DELETE CASCASE
+SELECT * FROM t2;
+SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr;
+
+--connection master
+DROP TABLE t3,t2,t1;
+--sync_slave_with_master
+#END OF
+PROCEDURE
+
+--echo #
+--echo # Test case: Triggers and Foreign Keys are present only on slave and
+--echo # 'slave_run_triggers_for_rbr=NO'
+--echo #
+--let $slave_run_triggers_for_rbr=NO
+--source $MYSQLTEST_VARDIR/tmp/trig_fk_on_slave.inc
+
+--echo #
+--echo # Test case: Triggers are Foreign Keys are present only on slave and
+--echo # 'slave_run_triggers_for_rbr=YES'
+--echo #
+--let $slave_run_triggers_for_rbr=YES
+--source $MYSQLTEST_VARDIR/tmp/trig_fk_on_slave.inc
+--remove_file $MYSQLTEST_VARDIR/tmp/trig_fk_on_slave.inc
+
+--source include/rpl_end.inc
diff --git a/sql/log_event.cc b/sql/log_event.cc
index c649e1f64fa..10aa0afbad8 100644
--- a/sql/log_event.cc
+++ b/sql/log_event.cc
@@ -10718,7 +10718,7 @@ int Rows_log_event::do_add_row_data(uchar *row_data, size_t length)
There was the same problem with MERGE MYISAM tables and so here we try to
go the same way.
*/
-static void restore_empty_query_table_list(LEX *lex)
+inline void restore_empty_query_table_list(LEX *lex)
{
if (lex->first_not_own_table())
(*lex->first_not_own_table()->prev_global)= NULL;
@@ -10733,6 +10733,8 @@ int Rows_log_event::do_apply_event(rpl_group_info *rgi)
TABLE* table;
DBUG_ENTER("Rows_log_event::do_apply_event(Relay_log_info*)");
int error= 0;
+ LEX *lex= thd->lex;
+ uint8 new_trg_event_map= get_trg_event_map();
/*
If m_table_id == ~0ULL, then we have a dummy event that does not
contain any data. In that case, we just remove all tables in the
@@ -10823,27 +10825,25 @@ int Rows_log_event::do_apply_event(rpl_group_info *rgi)
DBUG_ASSERT(!debug_sync_set_action(thd, STRING_WITH_LEN(action)));
};);
- if (slave_run_triggers_for_rbr)
- {
- LEX *lex= thd->lex;
- uint8 new_trg_event_map= get_trg_event_map();
-
- /*
- Trigger's procedures work with global table list. So we have to add
- rgi->tables_to_lock content there to get trigger's in the list.
+ /*
+ Trigger's procedures work with global table list. So we have to add
+ rgi->tables_to_lock content there to get trigger's in the list.
- Then restore_empty_query_table_list() restore the list as it was
- */
- DBUG_ASSERT(lex->query_tables == NULL);
- if ((lex->query_tables= rgi->tables_to_lock))
- rgi->tables_to_lock->prev_global= &lex->query_tables;
+ Then restore_empty_query_table_list() restore the list as it was
+ */
+ DBUG_ASSERT(lex->query_tables == NULL);
+ if ((lex->query_tables= rgi->tables_to_lock))
+ rgi->tables_to_lock->prev_global= &lex->query_tables;
- for (TABLE_LIST *tables= rgi->tables_to_lock; tables;
- tables= tables->next_global)
- {
+ for (TABLE_LIST *tables= rgi->tables_to_lock; tables;
+ tables= tables->next_global)
+ {
+ if (slave_run_triggers_for_rbr)
tables->trg_event_map= new_trg_event_map;
- lex->query_tables_last= &tables->next_global;
- }
+ else
+ tables->slave_fk_event_map= new_trg_event_map;
+
+ lex->query_tables_last= &tables->next_global;
}
if (open_and_lock_tables(thd, rgi->tables_to_lock, FALSE, 0))
{
@@ -11193,8 +11193,7 @@ int Rows_log_event::do_apply_event(rpl_group_info *rgi)
}
/* remove trigger's tables */
- if (slave_run_triggers_for_rbr)
- restore_empty_query_table_list(thd->lex);
+ restore_empty_query_table_list(thd->lex);
#if defined(WITH_WSREP) && defined(HAVE_QUERY_CACHE)
if (WSREP(thd) && thd->wsrep_exec_mode == REPL_RECV)
@@ -11212,8 +11211,7 @@ int Rows_log_event::do_apply_event(rpl_group_info *rgi)
DBUG_RETURN(error);
err:
- if (slave_run_triggers_for_rbr)
- restore_empty_query_table_list(thd->lex);
+ restore_empty_query_table_list(thd->lex);
rgi->slave_close_thread_tables(thd);
DBUG_RETURN(error);
}
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 8e57ea437b6..a7cddbca16a 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -4371,7 +4371,8 @@ handle_table(THD *thd, Query_tables_list *prelocking_ctx,
/* We rely on a caller to check that table is going to be changed. */
DBUG_ASSERT(table_list->lock_type >= TL_WRITE_ALLOW_WRITE);
- if (table_list->trg_event_map)
+ if (table_list->trg_event_map ||
+ (thd->rgi_slave && thd->is_current_stmt_binlog_format_row()))
{
if (table_list->table->triggers)
{
@@ -4405,7 +4406,8 @@ handle_table(THD *thd, Query_tables_list *prelocking_ctx,
{
// FK_OPTION_RESTRICT and FK_OPTION_NO_ACTION only need read access
static bool can_write[]= { true, false, true, true, false, true };
- uint8 op= table_list->trg_event_map;
+ uint8 op= ((table_list->trg_event_map) ? table_list->trg_event_map :
+ table_list->slave_fk_event_map);
thr_lock_type lock_type;
if ((op & (1 << TRG_EVENT_DELETE) && can_write[fk->delete_method])
diff --git a/sql/table.h b/sql/table.h
index 9a864f7ce9f..57706655d9b 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -2277,8 +2277,12 @@ struct TABLE_LIST
Indicates what triggers we need to pre-load for this TABLE_LIST
when opening an associated TABLE. This is filled after
the parsed tree is created.
+
+ slave_fk_event_map is filled on the slave side with bitmaps value
+ representing row-based event operation to help find and prelock
+ possible FK constrain-related child tables.
*/
- uint8 trg_event_map;
+ uint8 trg_event_map, slave_fk_event_map;
/* TRUE <=> this table is a const one and was optimized away. */
bool optimized_away;
1
0
[Commits] 2c32b5d26bf: MDEV-19620: Changing join_buffer_size causes different results
by varun 31 Dec '20
by varun 31 Dec '20
31 Dec '20
revision-id: 2c32b5d26bf0a863316ac377eaf27ed41f0aba4b (mariadb-10.3.21-309-g2c32b5d26bf)
parent(s): 043bd85a574a88856ab9c6d497e682ed06fe45e9
author: Varun Gupta
committer: Varun Gupta
timestamp: 2020-12-30 20:17:01 +0530
message:
MDEV-19620: Changing join_buffer_size causes different results
The scenario here is that query refinement phase decides to use a hash join.
When the join buffers are allocated in the JOIN::init_join_caches, for a table
the size exceeds the value for join_buffer_space_limit (which is the limit of the
space available for all join buffers). When this happens then we disallow join
buffering for the table, this is done in the revise_cache_usage and set_join_cache_denial.
In this issue the hash key is created on an index for which ref access is possible, so
when we disallow hash join then instead of switching to REF access we switch to a table
scan. This is a problem because the equijoin conditions for which a lookup can be made
are not attached to the table(or are not evaluated for the table). This leads to incorrect
results.
The fix here would be to switch to using a lookup because it was picked by the join planner
to be more efficient than the table scan.
---
mysql-test/main/join_cache.result | 138 ++++++++++++++++++++++++++++++++++++++
mysql-test/main/join_cache.test | 105 +++++++++++++++++++++++++++++
sql/sql_select.cc | 81 ++++++++++++++++++----
sql/sql_select.h | 6 ++
4 files changed, 316 insertions(+), 14 deletions(-)
diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result
index 3d1d91df997..e58503f422f 100644
--- a/mysql-test/main/join_cache.result
+++ b/mysql-test/main/join_cache.result
@@ -6128,4 +6128,142 @@ EXPLAIN
}
}
drop table t1,t2,t3;
+#
+# MDEV-19620: Changing join_buffer_size causes different results
+#
+SET @save_join_cache_level= @@join_cache_level;
+SET @save_join_buffer_size= @@join_buffer_size;
+SET @save_join_buffer_space_limit= @@join_buffer_space_limit;
+SET join_cache_level = 3;
+CREATE TABLE t1 (i2 VARCHAR(500), c2 VARCHAR(500)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('1','v'),('7','s');
+CREATE TABLE t2 (pk3 INT PRIMARY KEY, i3 VARCHAR(300), c3 VARCHAR(500)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,'7','g'),(2,'4','p'),(3,'1','q');
+INSERT INTO t2 VALUES (4,'7','g'),(5,'4','p'),(6,'1','q');
+INSERT INTO t2 VALUES (16,'7','g'),(17,'4','p'),(28,'1','q');
+#
+# Hash join + table Scan on t2
+#
+set join_buffer_size=1024;
+set join_buffer_space_limit=2048;
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 ALL NULL NULL NULL NULL 9 Using where
+SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2);
+i2 c2 pk3 i3 c3
+1 v NULL NULL NULL
+7 s NULL NULL NULL
+set join_buffer_space_limit=262144;
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 hash_ALL NULL #hash#$hj 503 test.t1.c2 9 Using where; Using join buffer (flat, BNLH join)
+SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2);
+i2 c2 pk3 i3 c3
+1 v NULL NULL NULL
+7 s NULL NULL NULL
+#
+# HASH join + ref access on t2
+#
+ALTER TABLE t2 ADD KEY k1(c3);
+set join_buffer_size=1024;
+set join_buffer_space_limit=2048;
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 ref k1 k1 503 test.t1.c2 2 Using where
+SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2);
+i2 c2 pk3 i3 c3
+1 v NULL NULL NULL
+7 s NULL NULL NULL
+set join_buffer_space_limit=262144;
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 hash_ALL k1 #hash#k1 503 test.t1.c2 9 Using where; Using join buffer (flat, BNLH join)
+SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2);
+i2 c2 pk3 i3 c3
+1 v NULL NULL NULL
+7 s NULL NULL NULL
+#
+# Hash join + index scan on t2
+#
+ALTER TABLE t2 DROP KEY k1;
+ALTER TABLE t2 ADD KEY k1(i3,c3);
+set join_buffer_size=1024;
+set join_buffer_space_limit=2048;
+EXPLAIN SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 index NULL k1 806 NULL 9 Using where; Using index
+SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2);
+c3 i3 i2 c2
+NULL NULL 1 v
+NULL NULL 7 s
+set join_buffer_space_limit=262144;
+EXPLAIN SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 hash_index NULL #hash#$hj:k1 503:806 test.t1.c2 9 Using where; Using index; Using join buffer (flat, BNLH join)
+SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2);
+c3 i3 i2 c2
+NULL NULL 1 v
+NULL NULL 7 s
+DROP TABLE t1,t2;
+#
+# Hash join + range scan on t2
+#
+CREATE TABLE t1 (i2 VARCHAR(500), c2 VARCHAR(500));
+INSERT INTO t1 VALUES ('1','v'),('7','s');
+CREATE TABLE t2 (pk3 INT PRIMARY KEY, i3 VARCHAR(300), c3 VARCHAR(500), INDEX(i3,c3));
+INSERT INTO t2 VALUES (1,'7','g'),(2,'4','s'),(3,'1','q');
+set join_buffer_size=1024;
+set join_buffer_space_limit=2048;
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3 = c2) WHERE i3 >= '4';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t2 range i3 i3 303 NULL 2 Using index condition; Using where
+SELECT * FROM t1 LEFT JOIN t2 ON (c3 = c2) WHERE i3 >= '4';
+i2 c2 pk3 i3 c3
+7 s 2 4 s
+set join_buffer_space_limit=262144;
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3 = c2) WHERE i3 >= '4';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t2 hash_range i3 #hash#$hj:i3 503:303 test.t1.c2 2 Using where; Using join buffer (flat, BNLH join)
+SELECT * FROM t1 LEFT JOIN t2 ON (c3 = c2) WHERE i3 >= '4';
+i2 c2 pk3 i3 c3
+7 s 2 4 s
+DROP TABLE t1,t2;
+#
+# Hash join + eq ref access on t2
+#
+CREATE TABLE t1 (i2 VARCHAR(500), c2 VARCHAR(500));
+INSERT INTO t1 VALUES ('1','v'),('7','s');
+CREATE TABLE t2 (pk3 INT, i3 VARCHAR(300), c3 VARCHAR(500) PRIMARY KEY);
+INSERT INTO t2 VALUES (1,'7','g'),(2,'4','s'),(3,'1','q');
+set join_buffer_size=1024;
+set join_buffer_space_limit=2048;
+EXPLAIN SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 502 test.t1.c2 1 Using where
+SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2);
+c3 i3 i2 c2
+NULL NULL 1 v
+s 4 7 s
+set join_buffer_space_limit=262144;
+EXPLAIN SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 hash_ALL PRIMARY #hash#PRIMARY 502 test.t1.c2 3 Using where; Using join buffer (flat, BNLH join)
+SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2);
+c3 i3 i2 c2
+NULL NULL 1 v
+s 4 7 s
+DROP TABLE t1,t2;
+SET @save_join_cache_level= @@join_cache_level;
+SET @save_join_buffer_size= @@join_buffer_size;
+SET @save_join_buffer_space_limit= @@join_buffer_space_limit;
set @@optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test
index 91339c2cb21..6670c62516b 100644
--- a/mysql-test/main/join_cache.test
+++ b/mysql-test/main/join_cache.test
@@ -4054,5 +4054,110 @@ where
drop table t1,t2,t3;
+--echo #
+--echo # MDEV-19620: Changing join_buffer_size causes different results
+--echo #
+
+SET @save_join_cache_level= @@join_cache_level;
+SET @save_join_buffer_size= @@join_buffer_size;
+SET @save_join_buffer_space_limit= @@join_buffer_space_limit;
+SET join_cache_level = 3;
+
+CREATE TABLE t1 (i2 VARCHAR(500), c2 VARCHAR(500)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('1','v'),('7','s');
+CREATE TABLE t2 (pk3 INT PRIMARY KEY, i3 VARCHAR(300), c3 VARCHAR(500)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,'7','g'),(2,'4','p'),(3,'1','q');
+INSERT INTO t2 VALUES (4,'7','g'),(5,'4','p'),(6,'1','q');
+INSERT INTO t2 VALUES (16,'7','g'),(17,'4','p'),(28,'1','q');
+
+--echo #
+--echo # Hash join + table Scan on t2
+--echo #
+
+set join_buffer_size=1024;
+set join_buffer_space_limit=2048;
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2);
+SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2);
+
+set join_buffer_space_limit=262144;
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2);
+SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2);
+
+--echo #
+--echo # HASH join + ref access on t2
+--echo #
+
+ALTER TABLE t2 ADD KEY k1(c3);
+set join_buffer_size=1024;
+set join_buffer_space_limit=2048;
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2);
+SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2);
+
+set join_buffer_space_limit=262144;
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2);
+SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2);
+
+--echo #
+--echo # Hash join + index scan on t2
+--echo #
+ALTER TABLE t2 DROP KEY k1;
+ALTER TABLE t2 ADD KEY k1(i3,c3);
+
+set join_buffer_size=1024;
+set join_buffer_space_limit=2048;
+
+EXPLAIN SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2);
+SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2);
+
+set join_buffer_space_limit=262144;
+EXPLAIN SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2);
+SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2);
+DROP TABLE t1,t2;
+
+--echo #
+--echo # Hash join + range scan on t2
+--echo #
+
+CREATE TABLE t1 (i2 VARCHAR(500), c2 VARCHAR(500));
+INSERT INTO t1 VALUES ('1','v'),('7','s');
+CREATE TABLE t2 (pk3 INT PRIMARY KEY, i3 VARCHAR(300), c3 VARCHAR(500), INDEX(i3,c3));
+INSERT INTO t2 VALUES (1,'7','g'),(2,'4','s'),(3,'1','q');
+
+set join_buffer_size=1024;
+set join_buffer_space_limit=2048;
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3 = c2) WHERE i3 >= '4';
+SELECT * FROM t1 LEFT JOIN t2 ON (c3 = c2) WHERE i3 >= '4';
+
+set join_buffer_space_limit=262144;
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3 = c2) WHERE i3 >= '4';
+SELECT * FROM t1 LEFT JOIN t2 ON (c3 = c2) WHERE i3 >= '4';
+
+DROP TABLE t1,t2;
+
+--echo #
+--echo # Hash join + eq ref access on t2
+--echo #
+
+CREATE TABLE t1 (i2 VARCHAR(500), c2 VARCHAR(500));
+INSERT INTO t1 VALUES ('1','v'),('7','s');
+CREATE TABLE t2 (pk3 INT, i3 VARCHAR(300), c3 VARCHAR(500) PRIMARY KEY);
+INSERT INTO t2 VALUES (1,'7','g'),(2,'4','s'),(3,'1','q');
+
+set join_buffer_size=1024;
+set join_buffer_space_limit=2048;
+
+EXPLAIN SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2);
+SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2);
+
+set join_buffer_space_limit=262144;
+EXPLAIN SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2);
+SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2);
+DROP TABLE t1,t2;
+
+SET @save_join_cache_level= @@join_cache_level;
+SET @save_join_buffer_size= @@join_buffer_size;
+SET @save_join_buffer_space_limit= @@join_buffer_space_limit;
+
# The following command must be the last one in the file
set @@optimizer_switch=@save_optimizer_switch;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 203422f0b43..0c56500b4d0 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -111,6 +111,7 @@ static bool best_extension_by_limited_search(JOIN *join,
uint prune_level,
uint use_cond_selectivity);
static uint determine_search_depth(JOIN* join);
+static void pick_table_access_method(JOIN_TAB *tab);
C_MODE_START
static int join_tab_cmp(const void *dummy, const void* ptr1, const void* ptr2);
static int join_tab_cmp_straight(const void *dummy, const void* ptr1, const void* ptr2);
@@ -10081,6 +10082,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j,
j->ref.disable_cache= FALSE;
j->ref.null_ref_part= NO_REF_PART;
j->ref.const_ref_part_map= 0;
+ j->ref.not_null_keyparts= 0;
keyuse=org_keyuse;
store_key **ref_key= j->ref.key_copy;
@@ -10173,23 +10175,12 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j,
}
} /* not ftkey */
*ref_key=0; // end_marker
+ j->ref.not_null_keyparts= not_null_keyparts;
if (j->type == JT_FT)
DBUG_RETURN(0);
- ulong key_flags= j->table->actual_key_flags(keyinfo);
if (j->type == JT_CONST)
j->table->const_table= 1;
- else if (!((keyparts == keyinfo->user_defined_key_parts &&
- (
- (key_flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME ||
- /* Unique key and all keyparts are NULL rejecting */
- ((key_flags & HA_NOSAME) && keyparts == not_null_keyparts)
- )) ||
- /* true only for extended keys */
- (keyparts > keyinfo->user_defined_key_parts &&
- MY_TEST(key_flags & HA_EXT_NOSAME) &&
- keyparts == keyinfo->ext_key_parts)
- ) ||
- null_ref_key)
+ else if (!j->is_eq_ref_access()|| null_ref_key)
{
/* Must read with repeat */
j->type= null_ref_key ? JT_REF_OR_NULL : JT_REF;
@@ -11582,11 +11573,33 @@ void set_join_cache_denial(JOIN_TAB *join_tab)
don't do join buffering for the first table in sjm nest.
*/
join_tab[-1].next_select= sub_select;
- if (join_tab->type == JT_REF && join_tab->is_ref_for_hash_join())
+ if ((join_tab->type == JT_REF || join_tab->type == JT_HASH) &&
+ join_tab->is_ref_for_hash_join())
{
join_tab->type= JT_ALL;
join_tab->ref.key_parts= 0;
}
+
+ if (join_tab->type == JT_HASH && !join_tab->is_ref_for_hash_join())
+ {
+ /*
+ Reverting to ref-access that was picked before the choice to use a
+ hash join was made
+ */
+ join_tab->type= join_tab->is_eq_ref_access() ? JT_EQ_REF : JT_REF;
+ pick_table_access_method(join_tab);
+ }
+
+ if (join_tab->type == JT_HASH_NEXT)
+ {
+ /*
+ Reverting to index scan that was picked before the choice to use a
+ hash join was made
+ */
+ join_tab->type = JT_NEXT;
+ join_tab->ref.key_parts= 0;
+ }
+
join_tab->join->return_tab= join_tab;
}
}
@@ -27954,6 +27967,46 @@ void JOIN_TAB::partial_cleanup()
}
+/*
+ @brief
+ Check if the access method for the table is EQ_REF access or not
+
+ @retval
+ TRUE EQ_REF access
+ FALSE Otherwise
+*/
+bool JOIN_TAB::is_eq_ref_access()
+{
+
+ KEY *keyinfo;
+ if (!is_hash_join_key_no(ref.key))
+ keyinfo= table->key_info + ref.key;
+ else
+ keyinfo= hj_key;
+
+ // For a hash key, we will not be having any EQ_REF access
+ if (hj_key)
+ return false;
+
+ uint keyparts= ref.key_parts;
+ ulong key_flags= table->actual_key_flags(keyinfo);
+ if ( (keyparts == keyinfo->user_defined_key_parts &&
+ (
+ (key_flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME ||
+ /* Unique key and all keyparts are NULL rejecting */
+ ((key_flags & HA_NOSAME) && keyparts == ref.not_null_keyparts)
+ )
+ ) ||
+ /* true only for extended keys */
+ (keyparts > keyinfo->user_defined_key_parts &&
+ MY_TEST(key_flags & HA_EXT_NOSAME) &&
+ keyparts == keyinfo->ext_key_parts)
+ )
+ return true;
+ return false;
+}
+
+
/**
@} (end of group Query_Optimizer)
*/
diff --git a/sql/sql_select.h b/sql/sql_select.h
index d207363a9ba..d4cdc9ad920 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -178,6 +178,11 @@ typedef struct st_table_ref
*/
bool disable_cache;
+ /*
+ The number of NOT NULL keyparts
+ */
+ uint not_null_keyparts;
+
bool tmp_table_index_lookup_init(THD *thd, KEY *tmp_key, Item_iterator &it,
bool value, uint skip= 0);
bool is_access_triggered();
@@ -611,6 +616,7 @@ typedef struct st_join_table {
bool hash_join_is_possible();
int make_scan_filter();
bool is_ref_for_hash_join() { return is_hash_join_key_no(ref.key); }
+ bool is_eq_ref_access();
KEY *get_keyinfo_by_key_no(uint key)
{
return (is_hash_join_key_no(key) ? hj_key : table->key_info+key);
1
0
[Commits] 78f72d4c798: MDEV-22700: Assertion `subq_pred->engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE' failed in setup_jtbm_semi_joins
by varun 31 Dec '20
by varun 31 Dec '20
31 Dec '20
revision-id: 78f72d4c7984230288f42ba1cb397125f8862866 (mariadb-10.2.31-642-g78f72d4c798)
parent(s): 8d8370e31d48e0bc6139c18770746f9959c21598
author: Varun Gupta
committer: Varun Gupta
timestamp: 2020-12-31 12:21:08 +0530
message:
MDEV-22700: Assertion `subq_pred->engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE' failed in setup_jtbm_semi_joins
For IN/ALL/ANY/SOME subqueries with window functions, IN to EXISTS strategy cannot be used
because with IN->EXISTS transformation the window function would be pushed inside the
HAVING clause which is not correct. So disabling the queries where IN->EXISTS transformation
is used with window functions.
---
mysql-test/r/win.result | 26 ++++++++++++++++++++++++--
mysql-test/t/win.test | 20 ++++++++++++++++++++
sql/item_subselect.cc | 10 ++++++++++
sql/opt_subselect.cc | 10 +++++++++-
4 files changed, 63 insertions(+), 3 deletions(-)
diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result
index 3023a86eaad..9bce517a108 100644
--- a/mysql-test/r/win.result
+++ b/mysql-test/r/win.result
@@ -3153,8 +3153,7 @@ DROP TABLE t1;
CREATE TABLE t1 (c CHAR(8)) ENGINE=MyISAM;
INSERT IGNORE INTO t1 VALUES ('foo');
SELECT ('bar',1) IN ( SELECT c, ROW_NUMBER() OVER (PARTITION BY c) FROM t1);
-('bar',1) IN ( SELECT c, ROW_NUMBER() OVER (PARTITION BY c) FROM t1)
-0
+ERROR 42000: This version of MariaDB doesn't yet support 'the combination of this ALL/ANY/SOME/IN subquery with this comparison operator and with contained window functions'
DROP TABLE t1;
#
# MDEV-13351: Server crashes in st_select_lex::set_explain_type upon UNION with window function
@@ -3866,5 +3865,28 @@ NULL
DROP VIEW v1;
DROP TABLE t1,t2;
#
+# MDEV-22700: Assertion `subq_pred->engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE'
+# failed in setup_jtbm_semi_joins
+#
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 VALUES (1,1), (2,2);
+SELECT * FROM t1 WHERE t1.a IN (SELECT COUNT(t2.a) over () from t1 t2);
+a b
+2 2
+SELECT * FROM t1 WHERE t1.a > ALL (SELECT COUNT(t2.a) over () from t1 t2);
+a b
+SELECT * FROM t1 WHERE t1.a > ANY (SELECT COUNT(t2.a) over () from t1 t2);
+a b
+SELECT * FROM t1 WHERE EXISTS (SELECT row_number() OVER () FROM t1 A WHERE t1.a= A.a);
+a b
+1 1
+2 2
+SELECT * FROM t1 WHERE a IN (SELECT 1 FROM t1 t2 ORDER BY sum(t2.b) over ());
+ERROR 42000: This version of MariaDB doesn't yet support 'the combination of this ALL/ANY/SOME/IN subquery with this comparison operator and with contained window functions'
+select a from t1 where a in (select row_number() over (order by a) from t1 A
+union select row_number() over (order by a) from t1 B) ;
+ERROR 42000: This version of MariaDB doesn't yet support 'the combination of this ALL/ANY/SOME/IN subquery with this comparison operator and with contained window functions'
+DROP TABLE t1;
+#
# End of 10.2 tests
#
diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test
index c7e3dac598b..869ecd756f5 100644
--- a/mysql-test/t/win.test
+++ b/mysql-test/t/win.test
@@ -1941,6 +1941,7 @@ DROP TABLE t1;
--echo #
CREATE TABLE t1 (c CHAR(8)) ENGINE=MyISAM;
INSERT IGNORE INTO t1 VALUES ('foo');
+--error ER_NOT_SUPPORTED_YET
SELECT ('bar',1) IN ( SELECT c, ROW_NUMBER() OVER (PARTITION BY c) FROM t1);
DROP TABLE t1;
@@ -2522,6 +2523,25 @@ SELECT NTH_VALUE(i1, i1) OVER (PARTITION BY i1) FROM v1;
DROP VIEW v1;
DROP TABLE t1,t2;
+--echo #
+--echo # MDEV-22700: Assertion `subq_pred->engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE'
+--echo # failed in setup_jtbm_semi_joins
+--echo #
+
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 VALUES (1,1), (2,2);
+SELECT * FROM t1 WHERE t1.a IN (SELECT COUNT(t2.a) over () from t1 t2);
+SELECT * FROM t1 WHERE t1.a > ALL (SELECT COUNT(t2.a) over () from t1 t2);
+SELECT * FROM t1 WHERE t1.a > ANY (SELECT COUNT(t2.a) over () from t1 t2);
+SELECT * FROM t1 WHERE EXISTS (SELECT row_number() OVER () FROM t1 A WHERE t1.a= A.a);
+--error ER_NOT_SUPPORTED_YET
+SELECT * FROM t1 WHERE a IN (SELECT 1 FROM t1 t2 ORDER BY sum(t2.b) over ());
+--error ER_NOT_SUPPORTED_YET
+select a from t1 where a in (select row_number() over (order by a) from t1 A
+ union select row_number() over (order by a) from t1 B) ;
+
+DROP TABLE t1;
+
--echo #
--echo # End of 10.2 tests
--echo #
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 802bfca64b7..018c4246619 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -3211,6 +3211,16 @@ Item_in_subselect::select_in_like_transformer(JOIN *join)
{
if (sl->join)
{
+ for (ORDER* order= sl->join->order; order; order= order->next)
+ {
+ if (order->item[0]->with_window_func)
+ {
+ my_error(ER_NOT_SUPPORTED_YET, MYF(0),
+ "the combination of this ALL/ANY/SOME/IN subquery with this"
+ " comparison operator and with contained window functions");
+ DBUG_RETURN(TRUE);
+ }
+ }
sl->join->order= 0;
sl->join->skip_sort_order= 1;
}
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index fec4e8b2828..54a65353756 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -5574,7 +5574,8 @@ bool setup_jtbm_semi_joins(JOIN *join, List<TABLE_LIST> *join_list,
subq_pred->jtbm_record_count=rows;
JOIN *subq_join= subq_pred->unit->first_select()->join;
- if (!subq_join->tables_list || !subq_join->table_count)
+ if ((!subq_join->tables_list || !subq_join->table_count) &&
+ !subq_join->select_lex->have_window_funcs())
{
/*
A special case; subquery's join is degenerate, and it either produces
@@ -5967,6 +5968,13 @@ bool JOIN::choose_subquery_plan(table_map join_tables)
}
else if (in_subs->test_strategy(SUBS_IN_TO_EXISTS))
{
+ if (select_lex->have_window_funcs())
+ {
+ my_error(ER_NOT_SUPPORTED_YET, MYF(0),
+ "the combination of this ALL/ANY/SOME/IN subquery with this"
+ " comparison operator and with contained window functions");
+ return true;
+ }
if (reopt_result == REOPT_NONE && in_to_exists_where &&
const_tables != table_count)
{
1
0