[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
[Commits] d5a82208f61: MDEV-22700: Assertion `subq_pred->engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE' failed in setup_jtbm_semi_joins
by varun 30 Dec '20
by varun 30 Dec '20
30 Dec '20
revision-id: d5a82208f61f2f476c2667c77c9cbb74d137374b (mariadb-10.2.31-642-gd5a82208f61)
parent(s): 8d8370e31d48e0bc6139c18770746f9959c21598
author: Varun Gupta
committer: Varun Gupta
timestamp: 2020-12-24 19:19:43 +0530
message:
MDEV-22700: Assertion `subq_pred->engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE' failed in setup_jtbm_semi_joins
Currently window functions do not work with IN/ALL/ANY/SOME subqueries, so throw an error
when such a query is encountered
---
mysql-test/r/win.result | 28 ++++++++++++++++++++++++----
mysql-test/t/win.test | 24 ++++++++++++++++++++++++
sql/item_subselect.cc | 3 +++
sql/opt_subselect.cc | 8 ++++++++
4 files changed, 59 insertions(+), 4 deletions(-)
diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result
index 3023a86eaad..1b4b7435351 100644
--- a/mysql-test/r/win.result
+++ b/mysql-test/r/win.result
@@ -2391,8 +2391,7 @@ COUNT(*) OVER (PARTITION BY c)
2
2
SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 );
-i
-1
+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, t2;
#
# MDEV-9976: window function without PARTITION BY and ORDER BY
@@ -3153,8 +3152,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 +3864,27 @@ 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);
+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 * FROM t1 WHERE t1.a > ALL (SELECT COUNT(t2.a) over () from t1 t2);
+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 * FROM t1 WHERE t1.a > ANY (SELECT COUNT(t2.a) over () from t1 t2);
+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 * 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..32eed85224b 100644
--- a/mysql-test/t/win.test
+++ b/mysql-test/t/win.test
@@ -1440,6 +1440,7 @@ INSERT INTO t2 VALUES ('foo'),('bar'),('foo');
SELECT COUNT(*) OVER (PARTITION BY c) FROM t2;
+--error ER_NOT_SUPPORTED_YET
SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 );
DROP TABLE t1, t2;
@@ -1941,6 +1942,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 +2524,28 @@ 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);
+--error ER_NOT_SUPPORTED_YET
+SELECT * FROM t1 WHERE t1.a IN (SELECT COUNT(t2.a) over () from t1 t2);
+--error ER_NOT_SUPPORTED_YET
+SELECT * FROM t1 WHERE t1.a > ALL (SELECT COUNT(t2.a) over () from t1 t2);
+--error ER_NOT_SUPPORTED_YET
+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..5b7b24d9e21 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1951,6 +1951,9 @@ bool Item_allany_subselect::transform_into_max_min(JOIN *join)
*/
DBUG_ASSERT(!substitution);
+ if (select_lex->have_window_funcs())
+ DBUG_RETURN(false);
+
/*
Check if optimization with aggregate min/max possible
1 There is no aggregate in the subquery
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index fec4e8b2828..3fdba7f4520 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -5940,6 +5940,14 @@ bool JOIN::choose_subquery_plan(table_map join_tables)
in_subs->set_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 (in_subs->test_strategy(SUBS_MATERIALIZATION))
{
/* Restore the original query plan used for materialization. */
1
0
[Commits] 9dba597: MDEV-23886 Reusing CTE inside a function fails with table doesn't exist
by IgorBabaev 29 Dec '20
by IgorBabaev 29 Dec '20
29 Dec '20
revision-id: 9dba597580096b2e8141f2dd201e394b597e5eab (mariadb-10.4.11-507-g9dba597)
parent(s): 478b83032b170b2ae030fa77fe4bed60a7910472
author: Igor Babaev
committer: Igor Babaev
timestamp: 2020-12-29 13:57:01 -0800
message:
MDEV-23886 Reusing CTE inside a function fails with table doesn't exist
Intermediate commit (not to be pushed).
---
mysql-test/main/brackets.result | 2 +-
mysql-test/main/cte_nonrecursive.result | 156 +++++++++++++
mysql-test/main/cte_nonrecursive.test | 163 ++++++++++++++
mysql-test/main/cte_recursive.result | 4 +-
sql/item_subselect.cc | 1 -
sql/sp_head.cc | 3 +-
sql/sql_base.cc | 33 +--
sql/sql_class.cc | 56 +++++
sql/sql_class.h | 8 +-
sql/sql_cte.cc | 382 +++++++++++++++++++++-----------
sql/sql_cte.h | 87 +++++++-
sql/sql_derived.cc | 2 +
sql/sql_lex.cc | 23 +-
sql/sql_lex.h | 25 ++-
sql/sql_parse.cc | 15 +-
sql/sql_prepare.cc | 3 -
sql/sql_view.cc | 9 -
sql/sql_yacc.yy | 27 ++-
sql/sql_yacc_ora.yy | 26 ++-
sql/table.cc | 1 +
sql/table.h | 37 ++++
21 files changed, 842 insertions(+), 221 deletions(-)
diff --git a/mysql-test/main/brackets.result b/mysql-test/main/brackets.result
index 99e84f9..1da5168 100644
--- a/mysql-test/main/brackets.result
+++ b/mysql-test/main/brackets.result
@@ -4508,7 +4508,7 @@ s as (select * from t where a > 3)
select a from t where a=1 union select a from s where a=7 order by a desc;
show create view v1;
View Create View character_set_client collation_connection
-v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3), s as (select `t`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3) `t` where `t`.`a` > 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3), s as (select `t`.`a` AS `a` from `t` where `t`.`a` > 3)select `t`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3) `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci
select * from v1;
a
1
diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result
index ee2320c..b8e885e 100644
--- a/mysql-test/main/cte_nonrecursive.result
+++ b/mysql-test/main/cte_nonrecursive.result
@@ -1846,4 +1846,160 @@ set sql_mode="oracle";
with data as (select 1 as id)
select id into @myid from data;
set sql_mode= @save_sql_mode;
+#
+# MDEV-23886: Stored Function returning the result of a query
+# that uses CTE over a table twice
+#
+create table t1 (c1 int);
+insert into t1 values (1),(2),(6);
+create function f1() returns int return
+( with cte1 as (select c1 from t1)
+select sum(c1) from
+(select * from cte1 union all select * from cte1) dt
+);
+select f1();
+f1()
+18
+create function f2() returns int return
+( with cte1 as (select c1 from t1)
+select sum(s.c1) from cte1 as s, cte1 as t where s.c1=t.c1
+);
+select f2();
+f2()
+9
+create function f3() returns int return
+( with cte1 as (select c1 from t1)
+select
+case
+when exists(select 1 from cte1 where c1 between 1 and 2) then 1
+when exists(select 1 from cte1 where c1 between 5 and 6) then 2
+else 0
+end
+);
+select f3();
+f3()
+1
+drop function f1;
+drop function f2;
+drop function f3;
+create table t2 (a int, b int);
+insert into t2
+with cte1 as (select c1 from t1)
+select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 > 5;
+select * from t2;
+a b
+6 6
+create procedure p1()
+begin
+insert into t2
+with cte1 as (select c1 from t1)
+select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2;
+end |
+call p1();
+select * from t2;
+a b
+6 6
+2 2
+drop procedure p1;
+# checking CTE resolution for queries with hanging CTEs
+with
+cte1(a) as (select * from t1 where c1 <= 2),
+cte2(b) as (select * from cte1 where a >= 2),
+cte3 as (select * from cte1,cte2 where cte1.a < cte2.b)
+select * from cte3;
+a b
+1 2
+select * from t2;
+a b
+6 6
+2 2
+with
+cte1(a) as (select * from t1 where c1 <= 2),
+cte2(b) as (select * from cte1 where a >= 2),
+cte3 as (select * from cte1,cte2 where cte1.a < cte2.b)
+select * from t2;
+a b
+6 6
+2 2
+with
+cte1(a) as (select * from t1 where c1 <= 2),
+cte2(b) as (select * from cte1 where c1 >= 2),
+cte3 as (select * from cte1,cte2 where cte1.a < cte2.b)
+select * from t2;
+ERROR 42S22: Unknown column 'c1' in 'where clause'
+with
+cte1(a) as (select * from t1 where c1 <= 2),
+cte2(b) as (select * from cte1 where a >= 2),
+cte3 as (select * from cte1,cte2 where cte1.a < cte2.c1)
+select * from t2;
+ERROR 42S22: Unknown column 'cte2.c1' in 'where clause'
+with
+cte1 as (select * from t1 where c1 <= 2),
+cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
+select * from cte2;
+a b
+1 1
+2 2
+with
+cte1 as (select * from t1 where c1 <= 2),
+cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
+select * from t2;
+a b
+6 6
+2 2
+with
+cte1 as (select * from t1 where c1 <= 2),
+cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=c1)
+select * from t2;
+ERROR 23000: Column 'c1' in where clause is ambiguous
+with cte3 as
+( with cte2(a,b) as
+( with cte1 as (select * from t1 where c1 <= 2)
+select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
+select r1.a,r2.b from cte2 as r1, cte2 as r2)
+select * from cte3;
+a b
+1 1
+2 1
+1 2
+2 2
+with cte3 as
+( with cte2(a,b) as
+( with cte1 as (select * from t1 where c1 <= 2)
+select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
+select r1.a,r2.b from cte2 as r1, cte2 as r2)
+select * from t2;
+a b
+6 6
+2 2
+with cte3 as
+( with cte2(a,b) as
+( with cte1 as (select * from t1 where c1 <= 2)
+select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
+select r1.c1,r2.c1 from cte2 as r1, cte2 as r2)
+select * from t2;
+ERROR 42S22: Unknown column 'r1.c1' in 'field list'
+create procedure p1()
+begin
+insert into t2
+with cte1 as (select c1 from t1)
+select * from t1 as s, t1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2;
+end |
+call p1();
+select * from t2;
+a b
+6 6
+2 2
+2 2
+drop procedure p1;
+create procedure p1()
+begin
+insert into t2
+with cte1 as (select a from t1)
+select * from t1 as s, t1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2;
+end |
+call p1();
+ERROR 42S22: Unknown column 'a' in 'field list'
+drop procedure p1;
+drop table t1,t2;
# End of 10.4 tests
diff --git a/mysql-test/main/cte_nonrecursive.test b/mysql-test/main/cte_nonrecursive.test
index 2242dce..2dd4857 100644
--- a/mysql-test/main/cte_nonrecursive.test
+++ b/mysql-test/main/cte_nonrecursive.test
@@ -1347,4 +1347,167 @@ with data as (select 1 as id)
select id into @myid from data;
set sql_mode= @save_sql_mode;
+--echo #
+--echo # MDEV-23886: Stored Function returning the result of a query
+--echo # that uses CTE over a table twice
+--echo #
+
+create table t1 (c1 int);
+insert into t1 values (1),(2),(6);
+
+create function f1() returns int return
+( with cte1 as (select c1 from t1)
+ select sum(c1) from
+ (select * from cte1 union all select * from cte1) dt
+);
+select f1();
+
+create function f2() returns int return
+( with cte1 as (select c1 from t1)
+ select sum(s.c1) from cte1 as s, cte1 as t where s.c1=t.c1
+);
+select f2();
+
+create function f3() returns int return
+( with cte1 as (select c1 from t1)
+ select
+ case
+ when exists(select 1 from cte1 where c1 between 1 and 2) then 1
+ when exists(select 1 from cte1 where c1 between 5 and 6) then 2
+ else 0
+ end
+);
+select f3();
+
+drop function f1;
+drop function f2;
+drop function f3;
+
+create table t2 (a int, b int);
+
+insert into t2
+with cte1 as (select c1 from t1)
+select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 > 5;
+
+select * from t2;
+
+delimiter |;
+
+create procedure p1()
+begin
+insert into t2
+with cte1 as (select c1 from t1)
+select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2;
+end |
+
+delimiter ;|
+
+call p1();
+select * from t2;
+
+drop procedure p1;
+
+--echo # checking CTE resolution for queries with hanging CTEs
+
+with
+cte1(a) as (select * from t1 where c1 <= 2),
+cte2(b) as (select * from cte1 where a >= 2),
+cte3 as (select * from cte1,cte2 where cte1.a < cte2.b)
+select * from cte3;
+
+select * from t2;
+
+with
+cte1(a) as (select * from t1 where c1 <= 2),
+cte2(b) as (select * from cte1 where a >= 2),
+cte3 as (select * from cte1,cte2 where cte1.a < cte2.b)
+select * from t2;
+
+--error ER_BAD_FIELD_ERROR
+with
+cte1(a) as (select * from t1 where c1 <= 2),
+cte2(b) as (select * from cte1 where c1 >= 2),
+cte3 as (select * from cte1,cte2 where cte1.a < cte2.b)
+select * from t2;
+
+--error ER_BAD_FIELD_ERROR
+with
+cte1(a) as (select * from t1 where c1 <= 2),
+cte2(b) as (select * from cte1 where a >= 2),
+cte3 as (select * from cte1,cte2 where cte1.a < cte2.c1)
+select * from t2;
+
+with
+cte1 as (select * from t1 where c1 <= 2),
+cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
+select * from cte2;
+
+with
+cte1 as (select * from t1 where c1 <= 2),
+cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
+select * from t2;
+
+--error ER_NON_UNIQ_ERROR
+with
+cte1 as (select * from t1 where c1 <= 2),
+cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=c1)
+select * from t2;
+
+with cte3 as
+( with cte2(a,b) as
+ ( with cte1 as (select * from t1 where c1 <= 2)
+ select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
+ select r1.a,r2.b from cte2 as r1, cte2 as r2)
+select * from cte3;
+
+with cte3 as
+( with cte2(a,b) as
+ ( with cte1 as (select * from t1 where c1 <= 2)
+ select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
+ select r1.a,r2.b from cte2 as r1, cte2 as r2)
+select * from t2;
+
+--error ER_BAD_FIELD_ERROR
+with cte3 as
+( with cte2(a,b) as
+ ( with cte1 as (select * from t1 where c1 <= 2)
+ select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
+ select r1.c1,r2.c1 from cte2 as r1, cte2 as r2)
+select * from t2;
+
+delimiter |;
+
+create procedure p1()
+begin
+insert into t2
+with cte1 as (select c1 from t1)
+select * from t1 as s, t1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2;
+end |
+
+delimiter ;|
+
+call p1();
+select * from t2;
+
+drop procedure p1;
+
+delimiter |;
+
+create procedure p1()
+begin
+insert into t2
+with cte1 as (select a from t1)
+select * from t1 as s, t1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2;
+end |
+
+delimiter ;|
+
+--error ER_BAD_FIELD_ERROR
+call p1();
+
+drop procedure p1;
+
+drop table t1,t2;
+
--echo # End of 10.4 tests
+
diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result
index 370da62..6f30de3 100644
--- a/mysql-test/main/cte_recursive.result
+++ b/mysql-test/main/cte_recursive.result
@@ -1301,7 +1301,7 @@ select ancestors.name, ancestors.dob from ancestors;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived4> ALL NULL NULL NULL NULL 24
4 DERIVED folks ALL NULL NULL NULL NULL 12 Using where
-6 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12
+6 UNION <derived3> ALL NULL NULL NULL NULL 12
5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24
NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL
3 DERIVED folks ALL NULL NULL NULL NULL 12 Using where
@@ -4029,7 +4029,7 @@ id select_type table type possible_keys key key_len ref rows Extra
3 RECURSIVE UNION t1 ALL NULL NULL NULL NULL 4 Using where
3 RECURSIVE UNION <derived2> ref key0 key0 9 test.t1.c 2
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
-4 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 4
+4 UNION <derived2> ALL NULL NULL NULL NULL 4
with recursive r_cte as
( select * from t1 as s
union
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index e205847..f6768d0 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1723,7 +1723,6 @@ double Item_in_subselect::val_real()
As far as Item_in_subselect called only from Item_in_optimizer this
method should not be used
*/
- DBUG_ASSERT(0);
DBUG_ASSERT(fixed == 1);
if (forced_const)
return value;
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index aa4f809..3ea4938 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -3411,8 +3411,7 @@ sp_lex_keeper::reset_lex_and_exec_core(THD *thd, uint *nextp,
Json_writer_object trace_command(thd);
Json_writer_array trace_command_steps(thd, "steps");
if (open_tables)
- res= check_dependencies_in_with_clauses(m_lex->with_clauses_list) ||
- instr->exec_open_and_lock_tables(thd, m_lex->query_tables);
+ res= instr->exec_open_and_lock_tables(thd, m_lex->query_tables);
if (likely(!res))
{
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 8bb6011..c3aeb7d 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -3695,7 +3695,11 @@ open_and_process_table(THD *thd, TABLE_LIST *tables, uint *counter, uint flags,
if (tables->derived)
{
if (!tables->view)
+ {
+ if (!tables->is_derived())
+ tables->set_derived();
goto end;
+ }
/*
We restore view's name and database wiped out by derived tables
processing and fall back to standard open process in order to
@@ -3705,35 +3709,6 @@ open_and_process_table(THD *thd, TABLE_LIST *tables, uint *counter, uint flags,
tables->db= tables->view_db;
tables->table_name= tables->view_name;
}
- else if (tables->select_lex)
- {
- /*
- Check whether 'tables' refers to a table defined in a with clause.
- If so set the reference to the definition in tables->with.
- */
- if (!tables->with)
- tables->with= tables->select_lex->find_table_def_in_with_clauses(tables);
- /*
- If 'tables' is defined in a with clause set the pointer to the
- specification from its definition in tables->derived.
- */
- if (tables->with)
- {
- if (tables->is_recursive_with_table() &&
- !tables->is_with_table_recursive_reference())
- {
- tables->with->rec_outer_references++;
- With_element *with_elem= tables->with;
- while ((with_elem= with_elem->get_next_mutually_recursive()) !=
- tables->with)
- with_elem->rec_outer_references++;
- }
- if (tables->set_as_with_table(thd, tables->with))
- DBUG_RETURN(1);
- else
- goto end;
- }
- }
if (!tables->derived && is_infoschema_db(&tables->db))
{
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index ceb8dc1..9d817aa 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -2804,6 +2804,62 @@ void THD::close_active_vio()
#endif
+/*
+ @brief MySQL parser used for recursive invocations
+
+ @param old_lex The LEX structure in the state when this parser
+ is called recursively
+ @param lex The LEX structure used to parse a new SQL fragment
+ @param str The SQL fragment to parse
+ @param str_len The length of the SQL fragment to parse
+ @param stmt_prepare_mode true <=> when parsing a prepare statement
+
+ @details
+ This function is to be used when parsing of an SQL fragment is
+ needed within one of the grammar rules.
+
+ @notes
+ Currently the function is used only when the specification of a CTE
+ is parsed for the not first and not recursive references of the CTE.
+
+ @retval false On a succesful parsing of the fragment
+ @retval true Otherwise
+*/
+
+bool THD::sql_parser(LEX *old_lex, LEX *lex,
+ char *str, uint str_len, bool stmt_prepare_mode)
+{
+ extern int MYSQLparse(THD * thd);
+ extern int ORAparse(THD * thd);
+
+ bool parse_status= false;
+ Parser_state parser_state;
+ Parser_state *old_parser_state= m_parser_state;
+
+ if (parser_state.init(this, str, str_len))
+ return true;
+
+ m_parser_state= &parser_state;
+ parser_state.m_lip.stmt_prepare_mode= stmt_prepare_mode;
+ parser_state.m_lip.multi_statements= false;
+ parser_state.m_lip.m_digest= NULL;
+
+ lex->param_list= old_lex->param_list;
+ lex->sphead= old_lex->sphead;
+ lex->spname= old_lex->spname;
+ lex->spcont= old_lex->spcont;
+ lex->sp_chistics= old_lex->sp_chistics;
+
+ parse_status= (variables.sql_mode & MODE_ORACLE) ?
+ ORAparse(this) : MYSQLparse(this) != 0;
+
+ m_parser_state= old_parser_state;
+
+ return parse_status;
+}
+
+
+
struct Item_change_record: public ilink
{
Item **place;
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 936437f..ecab13e 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -4241,14 +4241,11 @@ class THD: public THD_count, /* this must be first */
to resolve all CTE names as we don't need this message to be thrown
for any CTE references.
*/
- if (!lex->with_clauses_list)
+ if (!lex->with_cte_resolution)
{
my_message(ER_NO_DB_ERROR, ER(ER_NO_DB_ERROR), MYF(0));
return TRUE;
}
- /* This will allow to throw an error later for non-CTE references */
- to->str= NULL;
- to->length= 0;
return FALSE;
}
@@ -5043,6 +5040,9 @@ class THD: public THD_count, /* this must be first */
Item *sp_prepare_func_item(Item **it_addr, uint cols= 1);
bool sp_eval_expr(Field *result_field, Item **expr_item_ptr);
+ bool sql_parser(LEX *old_lex, LEX *lex,
+ char *str, uint str_len, bool stmt_prepare_mode);
+
};
/** A short cut for thd->get_stmt_da()->set_ok_status(). */
diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc
index a9f5443..c17c5a0 100644
--- a/sql/sql_cte.cc
+++ b/sql/sql_cte.cc
@@ -1,3 +1,4 @@
+
/*
Copyright (c) 2016, 2017 MariaDB
@@ -83,7 +84,7 @@ void st_select_lex_unit::set_with_clause(With_clause *with_cl)
true on failure
*/
-bool check_dependencies_in_with_clauses(With_clause *with_clauses_list)
+bool LEX::check_dependencies_in_with_clauses()
{
for (With_clause *with_clause= with_clauses_list;
with_clause;
@@ -99,6 +100,165 @@ bool check_dependencies_in_with_clauses(With_clause *with_clauses_list)
}
+bool
+LEX::resolve_references_to_cte_in_hanging_cte()
+{
+ for (With_clause *with_clause= with_clauses_list;
+ with_clause; with_clause= with_clause->next_with_clause)
+ {
+ for (With_element *with_elem= with_clause->with_list.first;
+ with_elem; with_elem= with_elem->next)
+ {
+ if (!with_elem->is_referenced())
+ {
+ TABLE_LIST *first_tbl=
+ with_elem->spec->first_select()->table_list.first;
+ TABLE_LIST **with_elem_end_pos= with_elem->head->tables_pos.end_pos;
+ if (first_tbl && resolve_references_to_cte(first_tbl, with_elem_end_pos))
+ return true;
+ }
+ }
+ }
+ return false;
+}
+
+
+/**
+ @brief
+ Resolve table references to CTE from a sunchain of table references
+
+ @param tables Points to the beginning of the subchain
+ @param tables_last Points to the address with the subchain barrier
+
+ @details
+ The method resolves tables references to CTE from the chain of
+ table refereces specified by the parameters 'tables' and 'tables_last'.
+ It resolves the references against the CTE definition occured in a query
+ or the specification of a CTE whose parsing tree is represented by
+ this LEX structure. The method is always called right after the process
+ of parsing the query or of the specification of a CTE has been finished,
+ thus the chain of table references used in the parsed fragment has been
+ already built. It is assumed that parameters of the methos specify a
+ a subchain of this chain.
+ If a table reference can be potentially a table reference to a CTE and it
+ has not been resolved yet then the method tries to find the definition
+ of the CTE against which the reference can be resolved. If it succeeds
+ it sets the field TABLE_LIST::with to point to the found definition.
+ It also sets the field TABLE_LIST::derived to point to the specification
+ of the found CTE and sets TABLE::db.str to empty_c_string. This will
+ allow to handle this table reference like a reference to a derived handle.
+ If another table reference has been already resolved against this CTE
+ and this CTE is not recursive then a clone of the CTE specification is
+ constructed using the function With_element::clone_parsed_spec() and
+ TABLE_LIST::derived is set to point to this clone rather than to the
+ original specification.
+ If the method does not find a matched CTE definition in the parsed fragment
+ then in the case when the flag this->only_cte_resolution is set to true
+ it just moves to the resolution of the next table reference from the
+ specified subchain while in the case when this->only_cte_resolution is set
+ to false the method additionally sets an mdl request for this table
+ reference.
+
+ @notes
+ The flag this->only_cte_resolution is set to true in the cases when
+ the failure to resolve a table reference as a CTE reference within
+ the fragment assiciated with this LEX structure does not imply that
+ this table reference cannot be resolved as such at all.
+
+ @retval false On success: no errors reported, no memory allocations failed
+ @retval true Otherwise
+
+*/
+
+bool LEX::resolve_references_to_cte(TABLE_LIST *tables,
+ TABLE_LIST **tables_last)
+{
+ With_element *with_elem= 0;
+
+ for (TABLE_LIST *tbl= tables; tbl != *tables_last; tbl= tbl->next_global)
+ {
+ if (tbl->derived)
+ continue;
+ if (!tbl->db.str && !tbl->with)
+ tbl->with= tbl->select_lex->find_table_def_in_with_clauses(tbl);
+ if (!tbl->with) // no CTE matches table reference tbl
+ {
+ if (only_cte_resolution)
+ continue;
+ if (!tbl->db.str) // no database specified in table reference tbl
+ {
+ if (!thd->db.str) // no default database is set
+ {
+ my_message(ER_NO_DB_ERROR, ER(ER_NO_DB_ERROR), MYF(0));
+ return true;
+ }
+ if (copy_db_to(&tbl->db))
+ return true;
+ if (!(tbl->table_options & TL_OPTION_ALIAS))
+ tbl->mdl_request.init(MDL_key::TABLE, tbl->db.str,
+ tbl->table_name.str,
+ tbl->mdl_type, MDL_TRANSACTION);
+ tbl->mdl_request.set_type((tbl->lock_type >= TL_WRITE_ALLOW_WRITE) ?
+ MDL_SHARED_WRITE : MDL_SHARED_READ);
+ }
+ continue;
+ }
+ with_elem= tbl->with;
+ if (tbl->is_recursive_with_table() &&
+ !tbl->is_with_table_recursive_reference())
+ {
+ tbl->with->rec_outer_references++;
+ while ((with_elem= with_elem->get_next_mutually_recursive()) !=
+ tbl->with)
+ with_elem->rec_outer_references++;
+ }
+ if (!with_elem->is_used_in_query || with_elem->is_recursive)
+ {
+ tbl->derived= with_elem->spec;
+ if (tbl->derived != tbl->select_lex->master_unit() &&
+ !with_elem->is_recursive &&
+ !tbl->is_with_table_recursive_reference())
+ {
+ tbl->derived->move_as_slave(tbl->select_lex);
+ }
+ with_elem->is_used_in_query= true;
+ }
+ else
+ {
+ if (!(tbl->derived= tbl->with->clone_parsed_spec(thd->lex, tbl)))
+ return true;
+ }
+ tbl->db.str= empty_c_string;
+ tbl->db.length= 0;
+ tbl->schema_table= 0;
+ if (tbl->derived)
+ {
+ tbl->derived->first_select()->set_linkage(DERIVED_TABLE_TYPE);
+ tbl->select_lex->add_statistics(tbl->derived);
+ }
+ if (tbl->with->is_recursive && tbl->is_with_table_recursive_reference())
+ continue;
+ with_elem->inc_references();
+ }
+ return false;
+}
+
+
+bool
+LEX::check_cte_dependencies_and_resolve_references()
+{
+ if (check_dependencies_in_with_clauses())
+ return true;
+ if (!with_cte_resolution)
+ return false;
+ if (resolve_references_to_cte(query_tables, query_tables_last))
+ return true;
+ if (resolve_references_to_cte_in_hanging_cte())
+ return true;
+ return false;
+}
+
+
/**
@brief
Check dependencies between tables defined in this with clause
@@ -137,10 +297,11 @@ bool With_clause::check_dependencies()
elem != with_elem;
elem= elem->next)
{
- if (lex_string_cmp(system_charset_info, with_elem->query_name,
- elem->query_name) == 0)
+ if (lex_string_cmp(system_charset_info, with_elem->get_name(),
+ elem->get_name()) == 0)
{
- my_error(ER_DUP_QUERY_NAME, MYF(0), with_elem->query_name->str);
+ my_error(ER_DUP_QUERY_NAME, MYF(0),
+ with_elem->get_name_str());
return true;
}
}
@@ -247,13 +408,12 @@ With_element *With_clause::find_table_def(TABLE_LIST *table,
with_elem != barrier;
with_elem= with_elem->next)
{
- if (my_strcasecmp(system_charset_info, with_elem->query_name->str,
- table->table_name.str) == 0 &&
+ if (my_strcasecmp(system_charset_info, with_elem->get_name_str(),
+ table->table_name.str) == 0 &&
!table->is_fqtn)
{
table->set_derived();
- table->db.str= empty_c_string;
- table->db.length= 0;
+ with_elem->referenced= true;
return with_elem;
}
}
@@ -610,7 +770,7 @@ bool With_clause::check_anchors()
if (elem == with_elem)
{
my_error(ER_RECURSIVE_WITHOUT_ANCHORS, MYF(0),
- with_elem->query_name->str);
+ with_elem->get_name_str());
return true;
}
}
@@ -643,7 +803,7 @@ bool With_clause::check_anchors()
if (elem->work_dep_map & elem->get_elem_map())
{
my_error(ER_UNACCEPTABLE_MUTUAL_RECURSION, MYF(0),
- with_elem->query_name->str);
+ with_elem->get_name_str());
return true;
}
}
@@ -797,9 +957,10 @@ bool With_element::set_unparsed_spec(THD *thd,
@brief
Create a clone of the specification for the given with table
- @param thd The context of the statement containing this with element
+ @param old_lex The LEX structure created for the query or CTE specification
+ where this With_element is defined
@param with_table The reference to the table defined in this element for which
- the clone is created.
+ the clone is created.
@details
The method creates a clone of the specification used in this element.
@@ -807,12 +968,13 @@ bool With_element::set_unparsed_spec(THD *thd,
this element.
The clone is created when the string with the specification saved in
unparsed_spec is fed into the parser as an input string. The parsing
- this string a unit object representing the specification is build.
+ this string a unit object representing the specification is built.
A chain of all table references occurred in the specification is also
formed.
The method includes the new unit and its sub-unit into hierarchy of
the units of the main query. I also insert the constructed chain of the
table references into the chain of all table references of the main query.
+ The method resolves all references to CTE in the clone.
@note
Clones is created only for not first references to tables defined in
@@ -828,113 +990,127 @@ bool With_element::set_unparsed_spec(THD *thd,
NULL - otherwise
*/
-st_select_lex_unit *With_element::clone_parsed_spec(THD *thd,
+st_select_lex_unit *With_element::clone_parsed_spec(LEX *old_lex,
TABLE_LIST *with_table)
{
+ THD *thd= old_lex->thd;
LEX *lex;
- st_select_lex_unit *res= NULL;
- Query_arena backup;
- Query_arena *arena= thd->activate_stmt_arena_if_needed(&backup);
+ st_select_lex_unit *res= NULL;
if (!(lex= (LEX*) new(thd->mem_root) st_lex_local))
- {
- if (arena)
- thd->restore_active_arena(arena, &backup);
return res;
- }
- LEX *old_lex= thd->lex;
thd->lex= lex;
bool parse_status= false;
- Parser_state parser_state;
- TABLE_LIST *spec_tables;
- TABLE_LIST *spec_tables_tail;
st_select_lex *with_select;
char save_end= unparsed_spec.str[unparsed_spec.length];
((char*) &unparsed_spec.str[unparsed_spec.length])[0]= '\0';
- if (parser_state.init(thd, (char*) unparsed_spec.str, (unsigned int)unparsed_spec.length))
- goto err;
- parser_state.m_lip.stmt_prepare_mode= stmt_prepare_mode;
- parser_state.m_lip.multi_statements= false;
- parser_state.m_lip.m_digest= NULL;
lex_start(thd);
lex->clone_spec_offset= unparsed_spec_offset;
- lex->param_list= old_lex->param_list;
- lex->sphead= old_lex->sphead;
- lex->spname= old_lex->spname;
- lex->spcont= old_lex->spcont;
- lex->sp_chistics= old_lex->sp_chistics;
-
- lex->stmt_lex= old_lex;
- parse_status= parse_sql(thd, &parser_state, 0);
+ lex->with_cte_resolution= true;
+
+ /*
+ The specification of a CTE is to be parsed as a regular query.
+ At the very end of the parsing query the function
+ check_cte_dependencies_and_resolve_references() will be called.
+ It will check the dependencies between CTEs that are defined
+ within the query and will resolve CTE references in this query.
+ If a table reference is not resolved as a CTE reference within
+ this query it still can be resolved as a reference to a CTE defined
+ in the same clause as the CTE whose specification is to be parsed
+ or defined in an embedding CTE definition.
+
+ Example:
+ with
+ cte1 as ( ... ),
+ cte2 as ([WITH ...] select ... from cte1 ...)
+ select ... from cte2 as r, ..., cte2 as s ...
+
+ Here the specification of cte2 has be cloned for table reference
+ with alias s1. The specification contains a reference to cte1
+ that is defined outside this specification. If the reference to
+ cte1 cannot be resolved within the specification of cte2 it's
+ not necessarily has to be a reference to a non-CTE table. That's
+ why the flag lex->only_cte_resolution has to be set to true
+ before parsing of the specification of cte2 invoked by this
+ function starts. Otherwise an mdl_lock would be requested for s
+ and this would not be correct
+ */
+
+ lex->only_cte_resolution= true;
+
+ lex->stmt_lex= old_lex->stmt_lex ? old_lex->stmt_lex : old_lex;
+
+ parse_status= thd->sql_parser(old_lex, lex,
+ (char*) unparsed_spec.str,
+ (unsigned int)unparsed_spec.length,
+ stmt_prepare_mode);
+
((char*) &unparsed_spec.str[unparsed_spec.length])[0]= save_end;
with_select= lex->first_select_lex();
if (parse_status)
goto err;
- if (check_dependencies_in_with_clauses(lex->with_clauses_list))
- goto err;
-
- spec_tables= lex->query_tables;
- spec_tables_tail= 0;
- for (TABLE_LIST *tbl= spec_tables;
- tbl;
- tbl= tbl->next_global)
- {
- if (!tbl->derived && !tbl->schema_table &&
- thd->open_temporary_table(tbl))
- goto err;
- spec_tables_tail= tbl;
- }
- if (spec_tables)
+ /*
+ The global chain of TABLE_LIST objects created for the specification that
+ just has been parsed is added to such chain that contains the reference
+ to the CTE whose specification is parsed right after the TABLE_LIST object
+ created for the reference.
+ */
+ if (lex->query_tables)
{
- if (with_table->next_global)
+ head->tables_pos.set_start_pos(&with_table->next_global);
+ head->tables_pos.set_end_pos(lex->query_tables_last);
+ TABLE_LIST *next_tbl= with_table->next_global;
+ if (next_tbl)
{
- spec_tables_tail->next_global= with_table->next_global;
- with_table->next_global->prev_global= &spec_tables_tail->next_global;
+ *(lex->query_tables->prev_global= next_tbl->prev_global)=
+ lex->query_tables;
+ *(next_tbl->prev_global= lex->query_tables_last)= next_tbl;
}
else
{
- old_lex->query_tables_last= &spec_tables_tail->next_global;
+ *(lex->query_tables->prev_global= old_lex->query_tables_last)=
+ lex->query_tables;
+ old_lex->query_tables_last= lex->query_tables_last;
}
- spec_tables->prev_global= &with_table->next_global;
- with_table->next_global= spec_tables;
}
res= &lex->unit;
+ /*
+ The unit of the specification that just has been parsed is included
+ as a slave of the select that contained in its from list the table
+ reference for which the unit has been created.
+ */
lex->unit.include_down(with_table->select_lex);
- lex->unit.set_slave(with_select);
+ lex->unit.set_slave(with_select);
+ lex->unit.cloned_from= spec;
old_lex->all_selects_list=
(st_select_lex*) (lex->all_selects_list->
insert_chain_before(
(st_select_lex_node **) &(old_lex->all_selects_list),
with_select));
- if (check_dependencies_in_with_clauses(lex->with_clauses_list))
- res= NULL;
+
/*
- Resolve references to CTE from the spec_tables list that has not
- been resolved yet.
+ Now all references to the CTE defined outside of the cloned specification
+ has to be resolved. Additionally if old_lex->only_cte_resolution == false
+ for the table references that has not been resolved requests for mdl_locks
+ has to be set.
*/
- for (TABLE_LIST *tbl= spec_tables;
- tbl;
- tbl= tbl->next_global)
+ lex->only_cte_resolution= old_lex->only_cte_resolution;
+ if (lex->resolve_references_to_cte(lex->query_tables,
+ lex->query_tables_last))
{
- if (!tbl->with)
- tbl->with= with_select->find_table_def_in_with_clauses(tbl);
- if (tbl == spec_tables_tail)
- break;
- }
- if (check_table_access(thd, SELECT_ACL, spec_tables, FALSE, UINT_MAX, FALSE))
+ res= NULL;
goto err;
+ }
lex->sphead= NULL; // in order not to delete lex->sphead
lex_end(lex);
err:
- if (arena)
- thd->restore_active_arena(arena, &backup);
thd->lex= old_lex;
return res;
}
@@ -1104,58 +1280,6 @@ With_element *st_select_lex::find_table_def_in_with_clauses(TABLE_LIST *table)
}
-/**
- @brief
- Set the specifying unit in this reference to a with table
-
- @details
- The method assumes that the given element with_elem defines the table T
- this table reference refers to.
- If this is the first reference to T the method just sets its specification
- in the field 'derived' as the unit that yields T. Otherwise the method
- first creates a clone specification and sets rather this clone in this field.
-
- @retval
- false on success
- true on failure
-*/
-
-bool TABLE_LIST::set_as_with_table(THD *thd, With_element *with_elem)
-{
- if (table)
- {
- /*
- This table was prematurely identified as a temporary table.
- We correct it here, but it's not a nice solution in the case
- when the temporary table with this name is not used anywhere
- else in the query.
- */
- thd->mark_tmp_table_as_free_for_reuse(table);
- table= 0;
- }
- with= with_elem;
- schema_table= NULL;
- if (!with_elem->is_referenced() || with_elem->is_recursive)
- {
- derived= with_elem->spec;
- if (derived != select_lex->master_unit() &&
- !with_elem->is_recursive &&
- !is_with_table_recursive_reference())
- {
- derived->move_as_slave(select_lex);
- }
- }
- else
- {
- if(!(derived= with_elem->clone_parsed_spec(thd, this)))
- return true;
- }
- derived->first_select()->set_linkage(DERIVED_TABLE_TYPE);
- select_lex->add_statistics(derived);
- with_elem->inc_references();
- return false;
-}
-
bool TABLE_LIST::is_recursive_with_table()
{
@@ -1256,7 +1380,7 @@ bool st_select_lex::check_unrestricted_recursive(bool only_standard_compliant)
if (only_standard_compliant && with_elem->is_unrestricted())
{
my_error(ER_NOT_STANDARD_COMPLIANT_RECURSIVE,
- MYF(0), with_elem->query_name->str);
+ MYF(0), with_elem->get_name_str());
return true;
}
@@ -1456,7 +1580,7 @@ void With_clause::print(String *str, enum_query_type query_type)
void With_element::print(String *str, enum_query_type query_type)
{
- str->append(query_name);
+ str->append(get_name());
if (column_list.elements)
{
List_iterator_fast<LEX_CSTRING> li(column_list);
diff --git a/sql/sql_cte.h b/sql/sql_cte.h
index 80d5664..f30c287 100644
--- a/sql/sql_cte.h
+++ b/sql/sql_cte.h
@@ -23,6 +23,38 @@
class select_unit;
struct st_unit_ctxt_elem;
+/**
+ @class With_element_head
+ @brief Head of the definition of a CTE table
+
+ It contains the name of the CTE and it contains the position of the subchain
+ of table references used in the definition in the global chain of table
+ references used in the query where this definition is encountered.
+*/
+
+class With_element_head : public Sql_alloc
+{
+ /* The name of the defined CTE */
+ LEX_CSTRING *query_name;
+
+public:
+ /*
+ The structure describing the subchain of the table references used in
+ the specification of the defined CTE in the global chain of table
+ references used in the query. The structure is fully defined only
+ after the CTE definition has been parsed.
+ */
+ TABLE_CHAIN tables_pos;
+
+ With_element_head(LEX_CSTRING *name)
+ : query_name(name)
+ {
+ tables_pos.set_start_pos(0);
+ tables_pos.set_end_pos(0);
+ }
+ friend class With_element;
+};
+
/**
@class With_element
@@ -85,9 +117,22 @@ class With_element : public Sql_alloc
subqueries and specifications of other with elements).
*/
uint references;
+
+ /*
+ true <=> this With_element is referred in the query in which the
+ element is defined
+ */
+ bool referenced;
+
+ /*
+ true <=> this With_element is needed for the execution of the query
+ in which the element is defined
+ */
+ bool is_used_in_query;
+
/*
Unparsed specification of the query that specifies this element.
- It used to build clones of the specification if they are needed.
+ It's used to build clones of the specification if they are needed.
*/
LEX_CSTRING unparsed_spec;
/* Offset of the specification in the input string */
@@ -101,10 +146,12 @@ class With_element : public Sql_alloc
public:
/*
- The name of the table introduced by this with elememt. The name
- can be used in FROM lists of the queries in the scope of the element.
+ Contains the name of the defined With element and the position of
+ the subchain of the tables references used by its definition in the
+ global chain of TABLE_LIST objects created for the whole query.
*/
- LEX_CSTRING *query_name;
+ With_element_head *head;
+
/*
Optional list of column names to name the columns of the table introduced
by this with element. It is used in the case when the names are not
@@ -162,18 +209,27 @@ class With_element : public Sql_alloc
/* List of derived tables containing recursive references to this CTE */
SQL_I_List<TABLE_LIST> derived_with_rec_ref;
- With_element(LEX_CSTRING *name,
+ With_element(With_element_head *h,
List <LEX_CSTRING> list,
st_select_lex_unit *unit)
: next(NULL), base_dep_map(0), derived_dep_map(0),
sq_dep_map(0), work_dep_map(0), mutually_recursive(0),
top_level_dep_map(0), sq_rec_ref(NULL),
next_mutually_recursive(NULL), references(0),
- query_name(name), column_list(list), spec(unit),
+ referenced(false), is_used_in_query(false),
+ head(h), column_list(list), spec(unit),
is_recursive(false), rec_outer_references(0), with_anchor(false),
level(0), rec_result(NULL)
{ unit->with_element= this; }
+ LEX_CSTRING *get_name() { return head->query_name; }
+ const char *get_name_str() { return get_name()->str; }
+
+ void set_tables_start_pos(TABLE_LIST **pos)
+ { head->tables_pos.set_start_pos(pos); }
+ void set_tables_end_pos(TABLE_LIST **pos)
+ { head->tables_pos.set_end_pos(pos); }
+
bool check_dependencies_in_spec();
void check_dependencies_in_select(st_select_lex *sl, st_unit_ctxt_elem *ctxt,
@@ -200,9 +256,9 @@ class With_element : public Sql_alloc
bool set_unparsed_spec(THD *thd, const char *spec_start, const char *spec_end,
my_ptrdiff_t spec_offset);
- st_select_lex_unit *clone_parsed_spec(THD *thd, TABLE_LIST *with_table);
+ st_select_lex_unit *clone_parsed_spec(LEX *old_lex, TABLE_LIST *with_table);
- bool is_referenced() { return references != 0; }
+ bool is_referenced() { return referenced; }
void inc_references() { references++; }
@@ -260,6 +316,12 @@ class With_element : public Sql_alloc
void prepare_for_next_iteration();
friend class With_clause;
+
+ friend
+ bool LEX::resolve_references_to_cte(TABLE_LIST *tables,
+ TABLE_LIST **tables_last);
+ friend
+ bool LEX::resolve_references_to_cte_in_hanging_cte();
};
const uint max_number_of_elements_in_with_clause= sizeof(table_map)*8;
@@ -291,6 +353,7 @@ class With_clause : public Sql_alloc
in the current statement
*/
With_clause *next_with_clause;
+
/* Set to true if dependencies between with elements have been checked */
bool dependencies_are_checked;
/*
@@ -338,7 +401,7 @@ class With_clause : public Sql_alloc
void attach_to(st_select_lex *select_lex);
With_clause *pop() { return embedding_with_clause; }
-
+
bool check_dependencies();
bool check_anchors();
@@ -358,8 +421,10 @@ class With_clause : public Sql_alloc
friend class With_element;
friend
- bool
- check_dependencies_in_with_clauses(With_clause *with_clauses_list);
+ bool LEX::check_dependencies_in_with_clauses();
+
+ friend
+ bool LEX::resolve_references_to_cte_in_hanging_cte();
};
inline
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index dc079ba..89b1900 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -917,6 +917,7 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived)
#ifndef NO_EMBEDDED_ACCESS_CHECKS
if (derived->is_view())
table->grant= derived->grant;
+#if 1
else
{
DBUG_ASSERT(derived->is_derived());
@@ -925,6 +926,7 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived)
derived->grant.privilege= SELECT_ACL;
}
#endif
+#endif
/* Add new temporary table to list of open derived tables */
if (!derived->is_with_table_recursive_reference())
{
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 10cd20e..c674495 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -721,6 +721,8 @@ void LEX::start(THD *thd_arg)
explain_json= false;
context_analysis_only= 0;
derived_tables= 0;
+ with_cte_resolution= false;
+ only_cte_resolution= false;
safe_to_cache_query= 1;
parsing_options.reset();
empty_field_list_on_rset= 0;
@@ -2388,6 +2390,7 @@ void st_select_lex_unit::init_query()
is_view= false;
with_clause= 0;
with_element= 0;
+ cloned_from= 0;
columns_are_renamed= false;
intersect_mark= NULL;
with_wrapped_tvc= false;
@@ -4831,14 +4834,14 @@ void st_select_lex::set_explain_type(bool on_the_fly)
/*
pos_in_table_list=NULL for e.g. post-join aggregation JOIN_TABs.
*/
- if (!tab->table);
- else if (const TABLE_LIST *pos= tab->table->pos_in_table_list)
+ if (!(tab->table && tab->table->pos_in_table_list))
+ continue;
+ TABLE_LIST *tbl= tab->table->pos_in_table_list;
+ if (tbl->with && tbl->with->is_recursive &&
+ tbl->is_with_table_recursive_reference())
{
- if (pos->with && pos->with->is_recursive)
- {
- uses_cte= true;
- break;
- }
+ uses_cte= true;
+ break;
}
}
if (uses_cte)
@@ -8266,6 +8269,8 @@ bool LEX::check_main_unit_semantics()
if (unit.set_nest_level(0) ||
unit.check_parameters(first_select_lex()))
return TRUE;
+ if (check_cte_dependencies_and_resolve_references())
+ return TRUE;
return FALSE;
}
@@ -8948,8 +8953,8 @@ void st_select_lex::add_statistics(SELECT_LEX_UNIT *unit)
bool LEX::main_select_push(bool service)
{
DBUG_ENTER("LEX::main_select_push");
- current_select_number= 1;
- builtin_select.select_number= 1;
+ current_select_number= ++thd->lex->stmt_lex->current_select_number;
+ builtin_select.select_number= current_select_number;
builtin_select.is_service_select= service;
if (push_select(&builtin_select))
DBUG_RETURN(TRUE);
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 66c44f2..f2fd67c 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -917,6 +917,8 @@ class st_select_lex_unit: public st_select_lex_node {
With_clause *with_clause;
/* With element where this unit is used as the specification (if any) */
With_element *with_element;
+ /* The unit used as a CTE specification from which this unit is cloned */
+ st_select_lex_unit *cloned_from;
/* thread handler */
THD *thd;
/*
@@ -1492,7 +1494,9 @@ class st_select_lex: public st_select_lex_node
}
With_element *get_with_element()
{
- return master_unit()->with_element;
+ return master_unit()->cloned_from ?
+ master_unit()->cloned_from->with_element :
+ master_unit()->with_element;
}
With_element *find_table_def_in_with_clauses(TABLE_LIST *table);
bool check_unrestricted_recursive(bool only_standard_compliant);
@@ -3314,6 +3318,20 @@ struct LEX: public Query_tables_list
*/
uint8 derived_tables;
uint8 context_analysis_only;
+ /*
+ true <=> The parsed fragment requires resolution of references to CTE
+ at the end of parsing. This name resolution process involves searching
+ for possible dependencies between CTE defined in the parsed fragment and
+ detecting possible recursive references.
+ The flag is set to true if the fragment contains CTE definitions.
+ */
+ bool with_cte_resolution;
+ /*
+ true <=> only resolution of references to CTE are required in the parsed
+ fragment, no checking of dependecies between CTE is required.
+ This flag is used only when parsing clones of CTE specifications.
+ */
+ bool only_cte_resolution;
bool local_file;
bool check_exists;
bool autocommit;
@@ -4547,6 +4565,11 @@ struct LEX: public Query_tables_list
select_stack[0]->is_service_select);
}
+ bool check_dependencies_in_with_clauses();
+ bool resolve_references_to_cte_in_hanging_cte();
+ bool check_cte_dependencies_and_resolve_references();
+ bool resolve_references_to_cte(TABLE_LIST *tables,
+ TABLE_LIST **tables_last);
};
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index e03b981..b60cc86 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -3397,9 +3397,6 @@ mysql_execute_command(THD *thd)
thd->get_stmt_da()->opt_clear_warning_info(thd->query_id);
}
- if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list))
- DBUG_RETURN(1);
-
#ifdef HAVE_REPLICATION
if (unlikely(thd->slave_thread))
{
@@ -8139,7 +8136,7 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd,
ptr->is_fqtn= TRUE;
ptr->db= table->db;
}
- else if (lex->copy_db_to(&ptr->db))
+ else if (!lex->with_cte_resolution && lex->copy_db_to(&ptr->db))
DBUG_RETURN(0);
else
ptr->is_fqtn= FALSE;
@@ -8156,7 +8153,9 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd,
}
ptr->table_name= table->table;
- ptr->lock_type= lock_type;
+ ptr->lock_type= lock_type;
+ ptr->mdl_type= mdl_type;
+ ptr->table_options= table_options;
ptr->updating= MY_TEST(table_options & TL_OPTION_UPDATING);
/* TODO: remove TL_OPTION_FORCE_INDEX as it looks like it's not used */
ptr->force_index= MY_TEST(table_options & TL_OPTION_FORCE_INDEX);
@@ -8838,8 +8837,10 @@ void st_select_lex::set_lock_for_tables(thr_lock_type lock_type, bool for_update
{
tables->lock_type= lock_type;
tables->updating= for_update;
- tables->mdl_request.set_type((lock_type >= TL_WRITE_ALLOW_WRITE) ?
- MDL_SHARED_WRITE : MDL_SHARED_READ);
+
+ if (tables->db.str && tables->db.str[0])
+ tables->mdl_request.set_type((lock_type >= TL_WRITE_ALLOW_WRITE) ?
+ MDL_SHARED_WRITE : MDL_SHARED_READ);
}
DBUG_VOID_RETURN;
}
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index ee50402..64e138f 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -2376,9 +2376,6 @@ static bool check_prepared_statement(Prepared_statement *stmt)
if (tables)
thd->get_stmt_da()->opt_clear_warning_info(thd->query_id);
- if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list))
- goto error;
-
if (sql_command_flags[sql_command] & CF_HA_CLOSE)
mysql_ha_rm_tables(thd, tables);
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index 126db90..1e3c4ce 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -431,12 +431,6 @@ bool mysql_create_view(THD *thd, TABLE_LIST *views,
lex->link_first_table_back(view, link_to_local);
view->open_type= OT_BASE_ONLY;
- if (check_dependencies_in_with_clauses(lex->with_clauses_list))
- {
- res= TRUE;
- goto err;
- }
-
WSREP_TO_ISOLATION_BEGIN(WSREP_MYSQL_DB, NULL, NULL);
/*
@@ -1413,9 +1407,6 @@ bool mysql_make_view(THD *thd, TABLE_SHARE *share, TABLE_LIST *table,
TABLE_LIST *tbl;
Security_context *security_ctx= 0;
- if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list))
- goto err;
-
/*
Check rights to run commands which show underlying tables.
In the optimizer trace we would not like to show trace for
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 2548dc2..f3e1cf2 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -770,6 +770,7 @@ Virtual_column_info *add_virtual_expression(THD *thd, Item *expr)
class sp_head *sphead;
class sp_name *spname;
class sp_variable *spvar;
+ class With_element_head *with_element_head;
class With_clause *with_clause;
class Virtual_column_info *virtual_column;
@@ -2188,7 +2189,7 @@ END_OF_INPUT
%type <with_clause> with_clause
-%type <lex_str_ptr> query_name
+%type <with_element_head> with_element_head
%type <lex_str_list> opt_with_column_list
@@ -3336,7 +3337,11 @@ call:
if (unlikely(Lex->call_statement_start(thd, $2)))
MYSQL_YYABORT;
}
- opt_sp_cparam_list {}
+ opt_sp_cparam_list
+ {
+ if (Lex->check_cte_dependencies_and_resolve_references())
+ MYSQL_YYABORT;
+ }
;
/* CALL parameters */
@@ -4185,6 +4190,8 @@ sp_proc_stmt_return:
LEX *lex= Lex;
sp_head *sp= lex->sphead;
Lex->pop_select(); //main select
+ if (Lex->check_cte_dependencies_and_resolve_references())
+ MYSQL_YYABORT;
if (unlikely(sp->m_handler->add_instr_freturn(thd, sp, lex->spcont,
$3, lex)) ||
unlikely(sp->restore_lex(thd)))
@@ -13331,6 +13338,8 @@ do:
{
Lex->insert_list= $3;
Lex->pop_select(); //main select
+ if (Lex->check_cte_dependencies_and_resolve_references())
+ MYSQL_YYABORT;
}
;
@@ -15497,6 +15506,7 @@ with_clause:
if (unlikely(with_clause == NULL))
MYSQL_YYABORT;
lex->derived_tables|= DERIVED_WITH;
+ lex->with_cte_resolution= true;
lex->curr_with_clause= with_clause;
with_clause->add_to_list(Lex->with_clauses_list_last_next);
if (lex->current_select &&
@@ -15524,7 +15534,7 @@ with_list:
with_list_element:
- query_name
+ with_element_head
opt_with_column_list
{
$2= new List<LEX_CSTRING> (Lex->with_column_list);
@@ -15544,6 +15554,7 @@ with_list_element:
if (elem->set_unparsed_spec(thd, spec_start, $7.pos(),
spec_start - query_start))
MYSQL_YYABORT;
+ elem->set_tables_end_pos(lex->query_tables_last);
}
;
@@ -15570,16 +15581,18 @@ with_column_list:
;
-query_name:
+with_element_head:
ident
{
- $$= (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING));
- if (unlikely($$ == NULL))
+ LEX_CSTRING *name=
+ (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING));
+ $$= new (thd->mem_root) With_element_head(name);
+ if (unlikely(name == NULL || $$ == NULL))
MYSQL_YYABORT;
+ $$->tables_pos.set_start_pos(Lex->query_tables_last);
}
;
-
/**********************************************************************
** Creating different items.
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index 30727a8..f2e112c 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -248,6 +248,7 @@ void ORAerror(THD *thd, const char *s)
class sp_head *sphead;
class sp_name *spname;
class sp_variable *spvar;
+ class With_element_head *with_element_head;
class With_clause *with_clause;
class Virtual_column_info *virtual_column;
@@ -1689,7 +1690,7 @@ END_OF_INPUT
%type <with_clause> with_clause
-%type <lex_str_ptr> query_name
+%type <with_element_head> with_element_head
%type <lex_str_list> opt_with_column_list
@@ -3138,7 +3139,11 @@ call:
if (unlikely(Lex->call_statement_start(thd, $2)))
MYSQL_YYABORT;
}
- opt_sp_cparam_list {}
+ opt_sp_cparam_list
+ {
+ if (Lex->check_cte_dependencies_and_resolve_references())
+ MYSQL_YYABORT;
+ }
;
/* CALL parameters */
@@ -4092,6 +4097,8 @@ sp_proc_stmt_return:
LEX *lex= Lex;
sp_head *sp= lex->sphead;
Lex->pop_select(); //main select
+ if (Lex->check_cte_dependencies_and_resolve_references())
+ MYSQL_YYABORT;
if (unlikely(sp->m_handler->add_instr_freturn(thd, sp, lex->spcont,
$3, lex)) ||
unlikely(sp->restore_lex(thd)))
@@ -13436,6 +13443,8 @@ do:
{
Lex->insert_list= $3;
Lex->pop_select(); //main select
+ if (Lex->check_cte_dependencies_and_resolve_references())
+ MYSQL_YYABORT;
}
;
@@ -15620,6 +15629,7 @@ with_clause:
if (unlikely(with_clause == NULL))
MYSQL_YYABORT;
lex->derived_tables|= DERIVED_WITH;
+ lex->with_cte_resolution= true;
lex->curr_with_clause= with_clause;
with_clause->add_to_list(Lex->with_clauses_list_last_next);
if (lex->current_select &&
@@ -15647,7 +15657,7 @@ with_list:
with_list_element:
- query_name
+ with_element_head
opt_with_column_list
{
$2= new List<LEX_CSTRING> (Lex->with_column_list);
@@ -15667,6 +15677,7 @@ with_list_element:
if (elem->set_unparsed_spec(thd, spec_start, $7.pos(),
spec_start - query_start))
MYSQL_YYABORT;
+ elem->set_tables_end_pos(lex->query_tables_last);
}
;
@@ -15693,12 +15704,15 @@ with_column_list:
;
-query_name:
+with_element_head:
ident
{
- $$= (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING));
- if (unlikely($$ == NULL))
+ LEX_CSTRING *name=
+ (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING));
+ $$= new (thd->mem_root) With_element_head(name);
+ if (unlikely(name == NULL || $$ == NULL))
MYSQL_YYABORT;
+ $$->tables_pos.set_start_pos(Lex->query_tables_last);
}
;
diff --git a/sql/table.cc b/sql/table.cc
index 084b441..67b2b14 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -9144,6 +9144,7 @@ void TABLE_LIST::set_lock_type(THD *thd, enum thr_lock_type lock)
}
}
+
bool TABLE_LIST::is_with_table()
{
return derived && derived->with_element;
diff --git a/sql/table.h b/sql/table.h
index 6073e35..248c239 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -2139,6 +2139,29 @@ struct vers_select_conds_t
struct LEX;
class Index_hint;
+
+/*
+ @struct TABLE_CHAIN
+ @brief Subchain of global chain of table references
+
+ The structure contains a pointer to the address of the next_global
+ pointer to the first TABLE_LIST objectof the subchain and the address
+ of the next_global pointer to the element right after the last
+ TABLE_LIST object of the subchain. For an empty subchain both pointers
+ have the same value.
+*/
+
+struct TABLE_CHAIN
+{
+ TABLE_CHAIN() {}
+
+ TABLE_LIST **start_pos;
+ TABLE_LIST ** end_pos;
+
+ void set_start_pos(TABLE_LIST **pos) { start_pos= pos; }
+ void set_end_pos(TABLE_LIST **pos) { end_pos= pos; }
+};
+
struct TABLE_LIST
{
TABLE_LIST() {} /* Remove gcc warning */
@@ -2473,6 +2496,20 @@ struct TABLE_LIST
/* call back function for asking handler about caching in query cache */
qc_engine_callback callback_func;
thr_lock_type lock_type;
+
+ /*
+ Two fields below are set during parsing this table reference in the cases
+ when the table reference can be potentially a reference to a CTE table.
+ In this cases the fact that the reference is a reference to a CTE or not
+ will be ascertained at the very end of parsing of the query when referencies
+ to CTE are resolved. For references to CTE and to derived tables no mdl
+ requests are needed while for other table references they are. If a request
+ is possibly postponed the info that allows to issue this request must be
+ saved in 'mdl_type' and 'table_options'.
+ */
+ enum_mdl_type mdl_type;
+ ulong table_options;
+
uint outer_join; /* Which join type */
uint shared; /* Used in multi-upd */
bool updatable; /* VIEW/TABLE can be updated now */
1
0
[Commits] cdc305c8dd8: MDEV-19620: Changing join_buffer_size causes different results
by varun 29 Dec '20
by varun 29 Dec '20
29 Dec '20
revision-id: cdc305c8dd89a726e09e5fe70ff890d06609cbfb (mariadb-10.3.21-309-gcdc305c8dd8)
parent(s): 043bd85a574a88856ab9c6d497e682ed06fe45e9
author: Varun Gupta
committer: Varun Gupta
timestamp: 2020-12-28 14:12:14 +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 | 69 +++++++++++++++----
sql/sql_select.h | 6 ++
4 files changed, 304 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..8b7deb98dc6 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,25 @@ 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())
+ {
+ 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)
+ {
+ join_tab->type = JT_NEXT;
+ DBUG_ASSERT(join_tab->ref.key_parts == 0);
+ }
+
join_tab->join->return_tab= join_tab;
}
}
@@ -27954,6 +27959,42 @@ 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;
+
+ 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);
3
2