Re: [Commits] b97452a6204: MDEV-19491 update query stopped working after mariadb upgrade 10.2.23 -> 10.2.24
by Sergey Vojtovich 30 May '19
by Sergey Vojtovich 30 May '19
30 May '19
Hi Sergei,
On Fri, May 24, 2019 at 06:06:43PM +0200, Sergei Golubchik wrote:
> revision-id: b97452a6204 (mariadb-5.5.64-11-gb97452a6204)
> parent(s): 7fceef405ae
> author: Sergei Golubchik <serg(a)mariadb.com>
> committer: Sergei Golubchik <serg(a)mariadb.com>
> timestamp: 2019-05-24 18:00:34 +0200
> message:
>
> MDEV-19491 update query stopped working after mariadb upgrade 10.2.23 -> 10.2.24
...skip...
> diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test
> index 14c5574f61c..a3b64ffde8c 100644
> --- a/mysql-test/t/multi_update.test
> +++ b/mysql-test/t/multi_update.test
> @@ -1081,6 +1081,18 @@ select * from t2;
> drop table t1,t2, t3;
> drop user foo;
>
> +#
> +# MDEV-19521 Update Table Fails with Trigger and Stored Function
> +#
> +create table t1 (a int, b varchar(50), c varchar(50));
> +insert t1 (a,b) values (1,'1'), (2,'2'), (3,'3');
> +create function f1() returns varchar(50) return 'result';
> +create trigger tr before update on t1 for each row set new.c = (select f1());
> +create table t2 select a, b from t1;
> +update t1 join t2 using (a) set t1.b = t2.b;
> +drop table t1, t2;
> +drop function f1;
I don't completely understand why this test was failing. It doesn't request
backoff action, right? Or is this patch fixing more than just backoff retry?
> diff --git a/mysql-test/t/multi_update_debug.test b/mysql-test/t/multi_update_debug.test
> new file mode 100644
> index 00000000000..ccd7791f029
> --- /dev/null
> +++ b/mysql-test/t/multi_update_debug.test
> @@ -0,0 +1,30 @@
> +#
> +# test MDL backoff-and-retry during multi-update
> +#
> +source include/have_debug_sync.inc;
> +create table t1 (a int, b int);
> +create table t2 (c int, d int);
> +insert t1 values (1,2),(3,4);
> +insert t2 values (5,6),(7,8);
> +create table t0 (x int);
> +insert t0 values (11), (22);
> +create trigger tr1 before update on t1 for each row insert t0 values (new.b);
> +
> +set debug_sync='open_tables_after_open_and_process_table WAIT_FOR cont';
> +send update t1 join t2 on (a=c+4) set b=d;
> +
> +connect con1, localhost, root;
You should wait here until update reaches
open_tables_after_open_and_process_table.
Also you should be able to do something like
set debug_sync='mdl_acquire_lock_wait SIGNAL cont';
So that you don't need con2 at all.
...skip...
> diff --git a/sql/sql_base.h b/sql/sql_base.h
> index ea92e880db7..439052a28f5 100644
> --- a/sql/sql_base.h
> +++ b/sql/sql_base.h
> @@ -426,6 +426,7 @@ class Prelocking_strategy
> public:
> virtual ~Prelocking_strategy() { }
>
> + virtual void reset(THD *thd) { };
> virtual bool handle_routine(THD *thd, Query_tables_list *prelocking_ctx,
> Sroutine_hash_entry *rt, sp_head *sp,
> bool *need_prelocking) = 0;
> @@ -433,6 +434,7 @@ class Prelocking_strategy
> TABLE_LIST *table_list, bool *need_prelocking) = 0;
> virtual bool handle_view(THD *thd, Query_tables_list *prelocking_ctx,
> TABLE_LIST *table_list, bool *need_prelocking)= 0;
> + virtual bool handle_end(THD *thd) { return 0; };
> };
May be just end()? It looks inconsistent with reset().
Also end() sounds multi-update specific. Isn't it "next nesting level dive"?
May be level_end()? Or next_level()?
I wonder if handle_table() alone can handle this? Or do we need to open all
tables before we can tell which ones are go ing to be updated?
...skip...
> +int mysql_multi_update_prepare(THD *thd)
> +{
> + LEX *lex= thd->lex;
> + TABLE_LIST *table_list= lex->query_tables;
> + TABLE_LIST *tl;
> + Multiupdate_prelocking_strategy prelocking_strategy;
> + uint table_count= lex->table_count;
Bad spacing.
> + bool original_multiupdate= (thd->lex->sql_command == SQLCOM_UPDATE_MULTI);
Do you really need this variable?
> + DBUG_ENTER("mysql_multi_update_prepare");
> +
> + /*
> + Open tables and create derived ones, but do not lock and fill them yet.
> +
> + During prepare phase acquire only S metadata locks instead of SW locks to
> + keep prepare of multi-UPDATE compatible with concurrent LOCK TABLES WRITE
> + and global read lock.
> + */
> + if (original_multiupdate)
> + {
> + if (open_tables(thd, &table_list, &table_count,
> + thd->stmt_arena->is_stmt_prepare() ? MYSQL_OPEN_FORCE_SHARED_MDL : 0,
> + &prelocking_strategy))
> + DBUG_RETURN(TRUE);
Wrong indentation.
Do we ever enter this branch with is_stmt_prepare()?
> + }
> + else
> + {
> + /* following need for prepared statements, to run next time multi-update */
> + thd->lex->sql_command= SQLCOM_UPDATE_MULTI;
> + prelocking_strategy.reset(thd);
> + if (prelocking_strategy.handle_end(thd))
> DBUG_RETURN(TRUE);
> }
>
In 10.2 we got MDL_SHARED_READ_ONLY, which is acquired by LOCK TABLES ... READ
for InnoDB. I guess it is going to be incompatible with multi update tables
opened for reading. I don't see this approach making fix for this issue
impossible, but still worth to double check.
Looks good otherwise.
Regards,
Sergey
2
3
revision-id: 636b0ae9600a48ba4ff5b5a9f645b38c9a289fad (mariadb-10.1.39-51-g636b0ae9600)
parent(s): 78c1be8b6b427e1331bbf0e5d5a24d57dc1477e3
author: Sujatha
committer: Sujatha
timestamp: 2019-05-30 17:46:40 +0530
message:
Enable Slave
---
mysql-test/suite/rpl/r/bug.result | 15 +++++++++++++++
mysql-test/suite/rpl/r/rpl_set_statement.result | 6 +++---
mysql-test/suite/rpl/t/bug.test | 22 ++++++++++++++++++++++
sql/sql_parse.cc | 2 +-
4 files changed, 41 insertions(+), 4 deletions(-)
diff --git a/mysql-test/suite/rpl/r/bug.result b/mysql-test/suite/rpl/r/bug.result
new file mode 100644
index 00000000000..b89dcf7327e
--- /dev/null
+++ b/mysql-test/suite/rpl/r/bug.result
@@ -0,0 +1,15 @@
+include/master-slave.inc
+[connection master]
+SET STATEMENT storage_engine=Aria FOR CREATE TABLE t (i INT);
+SHOW CREATE TABLE t;
+Table Create Table
+t CREATE TABLE `t` (
+ `i` int(11) DEFAULT NULL
+) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
+SHOW CREATE TABLE t;
+Table Create Table
+t CREATE TABLE `t` (
+ `i` int(11) DEFAULT NULL
+) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
+DROP TABLE t;
+include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/r/rpl_set_statement.result b/mysql-test/suite/rpl/r/rpl_set_statement.result
index c9df74b0d3b..d2ed7fe3522 100644
--- a/mysql-test/suite/rpl/r/rpl_set_statement.result
+++ b/mysql-test/suite/rpl/r/rpl_set_statement.result
@@ -28,7 +28,7 @@ SELECT @@max_join_size;
[connection slave]
SELECT * FROM t1;
a
-18446744073709551615
+2
SELECT @@max_join_size;
@@max_join_size
18446744073709551615
@@ -104,7 +104,7 @@ SELECT @@sql_mode;
[connection slave]
SELECT * FROM t2;
a
-
+NO_DIR_IN_CREATE
SELECT @@sql_mode;
@@sql_mode
@@ -131,7 +131,7 @@ SELECT @@max_heap_table_size;
[connection slave]
SELECT * FROM t1;
a
-1048576
+16384
SELECT @@max_heap_table_size;
@@max_heap_table_size
1048576
diff --git a/mysql-test/suite/rpl/t/bug.test b/mysql-test/suite/rpl/t/bug.test
new file mode 100644
index 00000000000..440d39e84c4
--- /dev/null
+++ b/mysql-test/suite/rpl/t/bug.test
@@ -0,0 +1,22 @@
+--source include/master-slave.inc
+
+
+
+SET STATEMENT storage_engine=Aria FOR CREATE TABLE t (i INT);
+
+SHOW CREATE TABLE t;
+
+
+
+--sync_slave_with_master
+
+SHOW CREATE TABLE t;
+
+
+
+--connection master
+
+DROP TABLE t;
+
+--source include/rpl_end.inc
+
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 9cb65e82321..e9cd869d24c 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -2733,7 +2733,7 @@ mysql_execute_command(THD *thd)
thd->get_binlog_format(&orig_binlog_format,
&orig_current_stmt_binlog_format);
- if (!lex->stmt_var_list.is_empty() && !thd->slave_thread)
+ if (!lex->stmt_var_list.is_empty())
{
Query_arena backup;
DBUG_PRINT("info", ("SET STATEMENT %d vars", lex->stmt_var_list.elements));
1
0
revision-id: 78c1be8b6b427e1331bbf0e5d5a24d57dc1477e3 (mariadb-10.1.39-50-g78c1be8b6b4)
parent(s): a47464d1c12d773364e78f50090b08484fe76129
author: Sujatha
committer: Sujatha
timestamp: 2019-05-30 12:11:57 +0530
message:
MDEV-18913: typo in error log
Problem:
========
Following typo in error log:
2019-03-13 15:58:10 0 [Note] Reading of all Master_info entries succeded
Should be 'succeeded'
Fix:
===
Fixed the typo with the right word 'succeeded'.
---
sql/rpl_mi.cc | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/sql/rpl_mi.cc b/sql/rpl_mi.cc
index 58131ff771c..70e60b1d4ad 100644
--- a/sql/rpl_mi.cc
+++ b/sql/rpl_mi.cc
@@ -1233,7 +1233,7 @@ bool Master_info_index::init_all_master_info()
if (!err_num) // No Error on read Master_info
{
if (global_system_variables.log_warnings > 1)
- sql_print_information("Reading of all Master_info entries succeded");
+ sql_print_information("Reading of all Master_info entries succeeded");
DBUG_RETURN(0);
}
if (succ_num) // Have some Error and some Success
1
0
[Commits] a47464d1c12: MDEV-11094: Blackhole table updates on slave fail when row annotation is enabled
by sujatha 29 May '19
by sujatha 29 May '19
29 May '19
revision-id: a47464d1c12d773364e78f50090b08484fe76129 (mariadb-10.1.39-49-ga47464d1c12)
parent(s): b347396181018cedc946450cb49891f1a0aa4575
author: Sujatha
committer: Sujatha
timestamp: 2019-05-29 17:35:29 +0530
message:
MDEV-11094: Blackhole table updates on slave fail when row annotation is enabled
Post push fix.
Simplified the earlier fixes.
---
storage/blackhole/ha_blackhole.cc | 40 ++++++++++++++++++++-------------------
1 file changed, 21 insertions(+), 19 deletions(-)
diff --git a/storage/blackhole/ha_blackhole.cc b/storage/blackhole/ha_blackhole.cc
index 43bcdc541a1..69182676c1e 100644
--- a/storage/blackhole/ha_blackhole.cc
+++ b/storage/blackhole/ha_blackhole.cc
@@ -25,15 +25,23 @@
#include "ha_blackhole.h"
#include "sql_class.h" // THD, SYSTEM_THREAD_SLAVE_SQL
+/**
+ Checks if the param 'thd' is pointing to slave applier thread and row based
+ replication is in use.
+
+ A row event will have its thd->query() == NULL except in cases where
+ replicate_annotate_row_events is enabled. In the later case the thd->query()
+ will be pointing to the query, received through replicated annotate event
+ from master.
+
+ @param thd pointer to a THD instance
+
+ @return TRUE if thread is slave applier and row based replication is in use
+*/
static bool is_row_based_replication(THD *thd)
{
- /*
- A row event which has its thd->query() == NULL or a row event which has
- replicate_annotate_row_events enabled. In the later case the thd->query()
- will be pointing to the query, received through replicated annotate event
- from master.
- */
- return ((thd->query() == NULL) || thd->variables.binlog_annotate_row_events);
+ return thd->system_thread == SYSTEM_THREAD_SLAVE_SQL &&
+ (thd->query() == NULL || thd->variables.binlog_annotate_row_events);
}
/* Static declarations for handlerton */
@@ -119,8 +127,7 @@ int ha_blackhole::update_row(const uchar *old_data, uchar *new_data)
{
DBUG_ENTER("ha_blackhole::update_row");
THD *thd= ha_thd();
- if (thd->system_thread == SYSTEM_THREAD_SLAVE_SQL &&
- is_row_based_replication(thd))
+ if (is_row_based_replication(thd))
DBUG_RETURN(0);
DBUG_RETURN(HA_ERR_WRONG_COMMAND);
}
@@ -129,8 +136,7 @@ int ha_blackhole::delete_row(const uchar *buf)
{
DBUG_ENTER("ha_blackhole::delete_row");
THD *thd= ha_thd();
- if (thd->system_thread == SYSTEM_THREAD_SLAVE_SQL &&
- is_row_based_replication(thd))
+ if (is_row_based_replication(thd))
DBUG_RETURN(0);
DBUG_RETURN(HA_ERR_WRONG_COMMAND);
}
@@ -147,8 +153,7 @@ int ha_blackhole::rnd_next(uchar *buf)
int rc;
DBUG_ENTER("ha_blackhole::rnd_next");
THD *thd= ha_thd();
- if (thd->system_thread == SYSTEM_THREAD_SLAVE_SQL &&
- is_row_based_replication(thd))
+ if (is_row_based_replication(thd))
rc= 0;
else
rc= HA_ERR_END_OF_FILE;
@@ -233,8 +238,7 @@ int ha_blackhole::index_read_map(uchar * buf, const uchar * key,
int rc;
DBUG_ENTER("ha_blackhole::index_read");
THD *thd= ha_thd();
- if (thd->system_thread == SYSTEM_THREAD_SLAVE_SQL &&
- is_row_based_replication(thd))
+ if (is_row_based_replication(thd))
rc= 0;
else
rc= HA_ERR_END_OF_FILE;
@@ -249,8 +253,7 @@ int ha_blackhole::index_read_idx_map(uchar * buf, uint idx, const uchar * key,
int rc;
DBUG_ENTER("ha_blackhole::index_read_idx");
THD *thd= ha_thd();
- if (thd->system_thread == SYSTEM_THREAD_SLAVE_SQL &&
- is_row_based_replication(thd))
+ if (is_row_based_replication(thd))
rc= 0;
else
rc= HA_ERR_END_OF_FILE;
@@ -264,8 +267,7 @@ int ha_blackhole::index_read_last_map(uchar * buf, const uchar * key,
int rc;
DBUG_ENTER("ha_blackhole::index_read_last");
THD *thd= ha_thd();
- if (thd->system_thread == SYSTEM_THREAD_SLAVE_SQL &&
- is_row_based_replication(thd))
+ if (is_row_based_replication(thd))
rc= 0;
else
rc= HA_ERR_END_OF_FILE;
1
0
[Commits] b3473961810: MDEV-11094: Blackhole table updates on slave fail when row annotation is enabled
by sujatha 29 May '19
by sujatha 29 May '19
29 May '19
revision-id: b347396181018cedc946450cb49891f1a0aa4575 (mariadb-10.1.39-48-gb3473961810)
parent(s): 8358c6f03edb941be488f009c2bd0eb9df47e8c5
author: Sujatha
committer: Sujatha
timestamp: 2019-05-29 15:18:52 +0530
message:
MDEV-11094: Blackhole table updates on slave fail when row annotation is enabled
Problem:
=======
rpl_blackhole.test fails when executed with following options
mysqld=--binlog_annotate_row_events=1, mysqld=--replicate_annotate_row_events=1
Test output:
------------
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
rpl.rpl_blackhole_bug 'mix' [ pass ] 791
rpl.rpl_blackhole_bug 'row' [ fail ]
Replicate_Wild_Ignore_Table
Last_Errno 1032
Last_Error Could not execute Update_rows_v1 event on table test.t1; Can't find
record in 't1', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's
master log master-bin.000001, end_log_pos 1510
Analysis:
=========
Enabling "replicate_annotate_row_events" on slave, Tells the slave to write
annotate rows events received from the master to its own binary log. The
received annotate events are applied after the Gtid event as shown below.
thd->query() will be set to the actual query received from the master, through
annotate event. Annotate_rows event should not be deleted after the event is
applied as the thd->query will be used to generate new Annotate_rows event
during applying the subsequent Rows events. After the last Rows event has been
applied, the saved Annotate_rows event (if any) will be deleted.
In balckhole engine all the DML operations are noops as they donot store any
data. They simply return success without doing any operation. But the existing
strictly expects thd->query() to be 'NULL' to identify that row based
replication is in use. This assumption will fail when row annotations are
enabled as the query is not 'NULL'. Hence various row based operations like
'update', 'delete', 'index lookup' will fail when row annotations are enabled.
Fix:
===
Extend the row based replication check to include row annotations as well.
i.e Either the thd->query() is NULL or thd->query() points to query and row
annotations are in use.
---
mysql-test/extra/rpl_tests/rpl_blackhole.test | 2 +-
.../extra/rpl_tests/rpl_blackhole_basic.test | 97 +++++
.../suite/rpl/r/rpl_blackhole_row_annotate.result | 434 +++++++++++++++++++++
mysql-test/suite/rpl/t/rpl_blackhole.test | 77 +---
.../rpl/t/rpl_blackhole_row_annotate-master.opt | 1 +
.../rpl/t/rpl_blackhole_row_annotate-slave.opt | 1 +
.../suite/rpl/t/rpl_blackhole_row_annotate.test | 49 +++
storage/blackhole/ha_blackhole.cc | 28 +-
8 files changed, 606 insertions(+), 83 deletions(-)
diff --git a/mysql-test/extra/rpl_tests/rpl_blackhole.test b/mysql-test/extra/rpl_tests/rpl_blackhole.test
index 1a0eeb3cf15..569a24e5252 100644
--- a/mysql-test/extra/rpl_tests/rpl_blackhole.test
+++ b/mysql-test/extra/rpl_tests/rpl_blackhole.test
@@ -11,7 +11,7 @@
# executing statement. If difference is >0, then something was
# written to the binary log on the slave.
-connection slave;
+# On Connection Slave
let $before = query_get_value("SHOW MASTER STATUS", Position, 1);
--echo [on master]
diff --git a/mysql-test/extra/rpl_tests/rpl_blackhole_basic.test b/mysql-test/extra/rpl_tests/rpl_blackhole_basic.test
new file mode 100644
index 00000000000..f3fdc915080
--- /dev/null
+++ b/mysql-test/extra/rpl_tests/rpl_blackhole_basic.test
@@ -0,0 +1,97 @@
+# PURPOSE. Test that blackhole works with replication in all three
+# modes: STATEMENT, MIXED, and ROW.
+#
+# METHOD. We start by creating a table on the master and then change
+# the engine to use blackhole on the slave.
+#
+# After insert/update/delete of one or more rows, the test the
+# proceeds to check that replication is running after replicating an
+# change, that the blackhole engine does not contain anything (which
+# is just a check that the correct engine is used), and that something
+# is written to the binary log.
+#
+# Whe check INSERT, UPDATE, and DELETE statement for tables with no
+# key (forcing a range search on the slave), primary keys (using a
+# primary key lookup), and index/key with multiple matches (forcing an
+# index search).
+
+# We start with no primary key
+CREATE TABLE t1 (a INT, b INT, c INT);
+CREATE TABLE t2 (a INT, b INT, c INT);
+
+sync_slave_with_master;
+ALTER TABLE t1 ENGINE=BLACKHOLE;
+
+connection master;
+INSERT INTO t2 VALUES (1,9,1), (2,9,2), (3,9,3), (4,9,4);
+sync_slave_with_master;
+
+# Test insert, no primary key
+let $statement = INSERT INTO t1 VALUES (1,1,1),(2,1,2),(3,1,3),(4,1,4);
+source extra/rpl_tests/rpl_blackhole.test;
+
+# Test update, no primary key
+let $statement = UPDATE t1 SET c = 2*c WHERE a % 2 = 0 AND b = 1;
+source extra/rpl_tests/rpl_blackhole.test;
+
+# Test delete, no primary key
+let $statement = DELETE FROM t1 WHERE a % 2 = 0 AND b = 1;
+source extra/rpl_tests/rpl_blackhole.test;
+
+# Test INSERT-SELECT into Blackhole, no primary key
+let $statement = INSERT INTO t1 SELECT * FROM t2;
+source extra/rpl_tests/rpl_blackhole.test;
+
+#
+# The MASTER has MyISAM as the engine for both tables. The SLAVE has Blackhole
+# on t1 (transactional engine) and MyISAM on t2 (non-transactional engine).
+#
+# In MIXED mode, the command "INSERT INTO t2 SELECT * FROM t1" is logged as
+# statement on the master. On the slave, it is tagged as unsafe because the
+# statement mixes both transactional and non-transactional engines and as such
+# its changes are logged as rows. However, due to the nature of the blackhole
+# engine, no rows are returned and thus any chain replication would make the
+# next master on the chain diverge.
+#
+# Fo this reason, we have disabled the statement.
+#
+# Test INSERT-SELECT from Blackhole, no primary key
+# let $statement = INSERT INTO t2 SELECT * FROM t1;
+# source extra/rpl_tests/rpl_blackhole.test;
+#
+
+connection master;
+ALTER TABLE t1 ADD PRIMARY KEY pk_t1 (a,b);
+sync_slave_with_master;
+
+# Test insert, primary key
+let $statement = INSERT INTO t1 VALUES (1,2,1),(2,2,2),(3,2,3),(4,2,4);
+source extra/rpl_tests/rpl_blackhole.test;
+
+# Test update, primary key
+let $statement = UPDATE t1 SET c = 2*c WHERE a % 2 = 0 AND b = 2;
+source extra/rpl_tests/rpl_blackhole.test;
+
+# Test delete, primary key
+let $statement = DELETE FROM t1 WHERE a % 2 = 0 AND b = 2;
+source extra/rpl_tests/rpl_blackhole.test;
+
+connection master;
+ALTER TABLE t1 DROP PRIMARY KEY, ADD KEY key_t1 (a);
+sync_slave_with_master;
+
+# Test insert, key
+let $statement = INSERT INTO t1 VALUES (1,3,1),(2,3,2),(3,3,3),(4,3,4);
+source extra/rpl_tests/rpl_blackhole.test;
+
+# Test update, key
+let $statement = UPDATE t1 SET c = 2*c WHERE a % 2 = 0 AND b = 3;
+source extra/rpl_tests/rpl_blackhole.test;
+
+# Test delete, key
+let $statement = DELETE FROM t1 WHERE a % 2 = 0 AND b = 3;
+source extra/rpl_tests/rpl_blackhole.test;
+
+connection master;
+DROP TABLE t1,t2;
+sync_slave_with_master;
diff --git a/mysql-test/suite/rpl/r/rpl_blackhole_row_annotate.result b/mysql-test/suite/rpl/r/rpl_blackhole_row_annotate.result
new file mode 100644
index 00000000000..20ca37322dc
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_blackhole_row_annotate.result
@@ -0,0 +1,434 @@
+include/master-slave.inc
+[connection master]
+SET timestamp=1000000000;
+RESET MASTER;
+SET timestamp=1000000000;
+RESET MASTER;
+CREATE TABLE t1 (a INT, b INT, c INT);
+CREATE TABLE t2 (a INT, b INT, c INT);
+ALTER TABLE t1 ENGINE=BLACKHOLE;
+INSERT INTO t2 VALUES (1,9,1), (2,9,2), (3,9,3), (4,9,4);
+[on master]
+INSERT INTO t1 VALUES (1,1,1),(2,1,2),(3,1,3),(4,1,4);
+[on slave]
+# Expect 0
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+0
+>>> Something was written to binary log <<<
+[on master]
+UPDATE t1 SET c = 2*c WHERE a % 2 = 0 AND b = 1;
+[on slave]
+# Expect 0
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+0
+>>> Something was written to binary log <<<
+[on master]
+DELETE FROM t1 WHERE a % 2 = 0 AND b = 1;
+[on slave]
+# Expect 0
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+0
+>>> Something was written to binary log <<<
+[on master]
+INSERT INTO t1 SELECT * FROM t2;
+[on slave]
+# Expect 0
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+0
+>>> Something was written to binary log <<<
+ALTER TABLE t1 ADD PRIMARY KEY pk_t1 (a,b);
+[on master]
+INSERT INTO t1 VALUES (1,2,1),(2,2,2),(3,2,3),(4,2,4);
+[on slave]
+# Expect 0
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+0
+>>> Something was written to binary log <<<
+[on master]
+UPDATE t1 SET c = 2*c WHERE a % 2 = 0 AND b = 2;
+[on slave]
+# Expect 0
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+0
+>>> Something was written to binary log <<<
+[on master]
+DELETE FROM t1 WHERE a % 2 = 0 AND b = 2;
+[on slave]
+# Expect 0
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+0
+>>> Something was written to binary log <<<
+ALTER TABLE t1 DROP PRIMARY KEY, ADD KEY key_t1 (a);
+[on master]
+INSERT INTO t1 VALUES (1,3,1),(2,3,2),(3,3,3),(4,3,4);
+[on slave]
+# Expect 0
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+0
+>>> Something was written to binary log <<<
+[on master]
+UPDATE t1 SET c = 2*c WHERE a % 2 = 0 AND b = 3;
+[on slave]
+# Expect 0
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+0
+>>> Something was written to binary log <<<
+[on master]
+DELETE FROM t1 WHERE a % 2 = 0 AND b = 3;
+[on slave]
+# Expect 0
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+0
+>>> Something was written to binary log <<<
+DROP TABLE t1,t2;
+FLUSH LOGS;
+show binlog events in 'slave-bin.000001' from <start_pos>;
+Log_name Pos Event_type Server_id End_log_pos Info
+slave-bin.000001 # Gtid_list 2 # []
+slave-bin.000001 # Binlog_checkpoint 2 # slave-bin.000001
+slave-bin.000001 # Gtid 1 # GTID 0-1-1
+slave-bin.000001 # Query 1 # use `test`; CREATE TABLE t1 (a INT, b INT, c INT)
+slave-bin.000001 # Gtid 1 # GTID 0-1-2
+slave-bin.000001 # Query 1 # use `test`; CREATE TABLE t2 (a INT, b INT, c INT)
+slave-bin.000001 # Gtid 2 # GTID 0-2-3
+slave-bin.000001 # Query 2 # use `test`; ALTER TABLE t1 ENGINE=BLACKHOLE
+slave-bin.000001 # Gtid 1 # BEGIN GTID 0-1-3
+slave-bin.000001 # Annotate_rows 1 # INSERT INTO t2 VALUES (1,9,1), (2,9,2), (3,9,3), (4,9,4)
+slave-bin.000001 # Table_map 1 # table_id: # (test.t2)
+slave-bin.000001 # Write_rows_v1 1 # table_id: # flags: STMT_END_F
+slave-bin.000001 # Query 1 # COMMIT
+slave-bin.000001 # Gtid 1 # BEGIN GTID 0-1-4
+slave-bin.000001 # Annotate_rows 1 # INSERT INTO t1 VALUES (1,1,1),(2,1,2),(3,1,3),(4,1,4)
+slave-bin.000001 # Table_map 1 # table_id: # (test.t1)
+slave-bin.000001 # Write_rows_v1 1 # table_id: # flags: STMT_END_F
+slave-bin.000001 # Query 1 # COMMIT
+slave-bin.000001 # Gtid 1 # BEGIN GTID 0-1-5
+slave-bin.000001 # Annotate_rows 1 # UPDATE t1 SET c = 2*c WHERE a % 2 = 0 AND b = 1
+slave-bin.000001 # Table_map 1 # table_id: # (test.t1)
+slave-bin.000001 # Update_rows_v1 1 # table_id: # flags: STMT_END_F
+slave-bin.000001 # Query 1 # COMMIT
+slave-bin.000001 # Gtid 1 # BEGIN GTID 0-1-6
+slave-bin.000001 # Annotate_rows 1 # DELETE FROM t1 WHERE a % 2 = 0 AND b = 1
+slave-bin.000001 # Table_map 1 # table_id: # (test.t1)
+slave-bin.000001 # Delete_rows_v1 1 # table_id: # flags: STMT_END_F
+slave-bin.000001 # Query 1 # COMMIT
+slave-bin.000001 # Gtid 1 # BEGIN GTID 0-1-7
+slave-bin.000001 # Annotate_rows 1 # INSERT INTO t1 SELECT * FROM t2
+slave-bin.000001 # Table_map 1 # table_id: # (test.t1)
+slave-bin.000001 # Write_rows_v1 1 # table_id: # flags: STMT_END_F
+slave-bin.000001 # Query 1 # COMMIT
+slave-bin.000001 # Gtid 1 # GTID 0-1-8
+slave-bin.000001 # Query 1 # use `test`; ALTER TABLE t1 ADD PRIMARY KEY pk_t1 (a,b)
+slave-bin.000001 # Gtid 1 # BEGIN GTID 0-1-9
+slave-bin.000001 # Annotate_rows 1 # INSERT INTO t1 VALUES (1,2,1),(2,2,2),(3,2,3),(4,2,4)
+slave-bin.000001 # Table_map 1 # table_id: # (test.t1)
+slave-bin.000001 # Write_rows_v1 1 # table_id: # flags: STMT_END_F
+slave-bin.000001 # Query 1 # COMMIT
+slave-bin.000001 # Gtid 1 # BEGIN GTID 0-1-10
+slave-bin.000001 # Annotate_rows 1 # UPDATE t1 SET c = 2*c WHERE a % 2 = 0 AND b = 2
+slave-bin.000001 # Table_map 1 # table_id: # (test.t1)
+slave-bin.000001 # Update_rows_v1 1 # table_id: # flags: STMT_END_F
+slave-bin.000001 # Query 1 # COMMIT
+slave-bin.000001 # Gtid 1 # BEGIN GTID 0-1-11
+slave-bin.000001 # Annotate_rows 1 # DELETE FROM t1 WHERE a % 2 = 0 AND b = 2
+slave-bin.000001 # Table_map 1 # table_id: # (test.t1)
+slave-bin.000001 # Delete_rows_v1 1 # table_id: # flags: STMT_END_F
+slave-bin.000001 # Query 1 # COMMIT
+slave-bin.000001 # Gtid 1 # GTID 0-1-12
+slave-bin.000001 # Query 1 # use `test`; ALTER TABLE t1 DROP PRIMARY KEY, ADD KEY key_t1 (a)
+slave-bin.000001 # Gtid 1 # BEGIN GTID 0-1-13
+slave-bin.000001 # Annotate_rows 1 # INSERT INTO t1 VALUES (1,3,1),(2,3,2),(3,3,3),(4,3,4)
+slave-bin.000001 # Table_map 1 # table_id: # (test.t1)
+slave-bin.000001 # Write_rows_v1 1 # table_id: # flags: STMT_END_F
+slave-bin.000001 # Query 1 # COMMIT
+slave-bin.000001 # Gtid 1 # BEGIN GTID 0-1-14
+slave-bin.000001 # Annotate_rows 1 # UPDATE t1 SET c = 2*c WHERE a % 2 = 0 AND b = 3
+slave-bin.000001 # Table_map 1 # table_id: # (test.t1)
+slave-bin.000001 # Update_rows_v1 1 # table_id: # flags: STMT_END_F
+slave-bin.000001 # Query 1 # COMMIT
+slave-bin.000001 # Gtid 1 # BEGIN GTID 0-1-15
+slave-bin.000001 # Annotate_rows 1 # DELETE FROM t1 WHERE a % 2 = 0 AND b = 3
+slave-bin.000001 # Table_map 1 # table_id: # (test.t1)
+slave-bin.000001 # Delete_rows_v1 1 # table_id: # flags: STMT_END_F
+slave-bin.000001 # Query 1 # COMMIT
+slave-bin.000001 # Gtid 1 # GTID 0-1-16
+slave-bin.000001 # Query 1 # use `test`; DROP TABLE IF EXISTS `t1`,`t2` /* generated by server */
+slave-bin.000001 # Rotate 2 # slave-bin.000002;pos=4
+/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
+/*!40019 SET @@session.max_insert_delayed_threads=0*/;
+/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
+DELIMITER /*!*/;
+# at #
+#010909 4:46:40 server id # end_log_pos # Start: binlog v 4, server v #.##.## created 010909 4:46:40 at startup
+ROLLBACK/*!*/;
+# at #
+#010909 4:46:40 server id # end_log_pos # Gtid list []
+# at #
+#010909 4:46:40 server id # end_log_pos # Binlog checkpoint slave-bin.000001
+# at #
+#010909 4:46:40 server id # end_log_pos # GTID 0-1-1 ddl
+/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;
+/*!100001 SET @@session.gtid_domain_id=0*//*!*/;
+/*!100001 SET @@session.server_id=1*//*!*/;
+/*!100001 SET @@session.gtid_seq_no=1*//*!*/;
+# at #
+#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0
+use `test`/*!*/;
+SET TIMESTAMP=1000000000/*!*/;
+SET @@session.pseudo_thread_id=#/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.sql_mode=1342177280/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
+/*!\C latin1 *//*!*/;
+SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
+CREATE TABLE t1 (a INT, b INT, c INT)
+/*!*/;
+# at #
+#010909 4:46:40 server id # end_log_pos # GTID 0-1-2 ddl
+/*!100001 SET @@session.gtid_seq_no=2*//*!*/;
+# at #
+#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0
+SET TIMESTAMP=1000000000/*!*/;
+CREATE TABLE t2 (a INT, b INT, c INT)
+/*!*/;
+# at #
+#010909 4:46:40 server id # end_log_pos # GTID 0-2-3 ddl
+/*!100001 SET @@session.server_id=2*//*!*/;
+/*!100001 SET @@session.gtid_seq_no=3*//*!*/;
+# at #
+#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0
+SET TIMESTAMP=1000000000/*!*/;
+ALTER TABLE t1 ENGINE=BLACKHOLE
+/*!*/;
+# at #
+#010909 4:46:40 server id # end_log_pos # GTID 0-1-3 trans
+/*!100001 SET @@session.server_id=1*//*!*/;
+/*!100001 SET @@session.gtid_seq_no=3*//*!*/;
+BEGIN
+/*!*/;
+# at #
+# at #
+#010909 4:46:40 server id # end_log_pos # Annotate_rows:
+#Q> INSERT INTO t2 VALUES (1,9,1), (2,9,2), (3,9,3), (4,9,4)
+#010909 4:46:40 server id # end_log_pos # Table_map: `test`.`t2` mapped to number #
+# at #
+#010909 4:46:40 server id # end_log_pos # Write_rows: table id # flags: STMT_END_F
+# at #
+#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0
+SET TIMESTAMP=1000000000/*!*/;
+COMMIT
+/*!*/;
+# at #
+#010909 4:46:40 server id # end_log_pos # GTID 0-1-4 trans
+/*!100001 SET @@session.gtid_seq_no=4*//*!*/;
+BEGIN
+/*!*/;
+# at #
+# at #
+#010909 4:46:40 server id # end_log_pos # Annotate_rows:
+#Q> INSERT INTO t1 VALUES (1,1,1),(2,1,2),(3,1,3),(4,1,4)
+#010909 4:46:40 server id # end_log_pos # Table_map: `test`.`t1` mapped to number #
+# at #
+#010909 4:46:40 server id # end_log_pos # Write_rows: table id # flags: STMT_END_F
+# at #
+#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0
+SET TIMESTAMP=1000000000/*!*/;
+COMMIT
+/*!*/;
+# at #
+#010909 4:46:40 server id # end_log_pos # GTID 0-1-5 trans
+/*!100001 SET @@session.gtid_seq_no=5*//*!*/;
+BEGIN
+/*!*/;
+# at #
+# at #
+#010909 4:46:40 server id # end_log_pos # Annotate_rows:
+#Q> UPDATE t1 SET c = 2*c WHERE a % 2 = 0 AND b = 1
+#010909 4:46:40 server id # end_log_pos # Table_map: `test`.`t1` mapped to number #
+# at #
+#010909 4:46:40 server id # end_log_pos # Update_rows: table id # flags: STMT_END_F
+# at #
+#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0
+SET TIMESTAMP=1000000000/*!*/;
+COMMIT
+/*!*/;
+# at #
+#010909 4:46:40 server id # end_log_pos # GTID 0-1-6 trans
+/*!100001 SET @@session.gtid_seq_no=6*//*!*/;
+BEGIN
+/*!*/;
+# at #
+# at #
+#010909 4:46:40 server id # end_log_pos # Annotate_rows:
+#Q> DELETE FROM t1 WHERE a % 2 = 0 AND b = 1
+#010909 4:46:40 server id # end_log_pos # Table_map: `test`.`t1` mapped to number #
+# at #
+#010909 4:46:40 server id # end_log_pos # Delete_rows: table id # flags: STMT_END_F
+# at #
+#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0
+SET TIMESTAMP=1000000000/*!*/;
+COMMIT
+/*!*/;
+# at #
+#010909 4:46:40 server id # end_log_pos # GTID 0-1-7 trans
+/*!100001 SET @@session.gtid_seq_no=7*//*!*/;
+BEGIN
+/*!*/;
+# at #
+# at #
+#010909 4:46:40 server id # end_log_pos # Annotate_rows:
+#Q> INSERT INTO t1 SELECT * FROM t2
+#010909 4:46:40 server id # end_log_pos # Table_map: `test`.`t1` mapped to number #
+# at #
+#010909 4:46:40 server id # end_log_pos # Write_rows: table id # flags: STMT_END_F
+# at #
+#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0
+SET TIMESTAMP=1000000000/*!*/;
+COMMIT
+/*!*/;
+# at #
+#010909 4:46:40 server id # end_log_pos # GTID 0-1-8 ddl
+/*!100001 SET @@session.gtid_seq_no=8*//*!*/;
+# at #
+#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0
+SET TIMESTAMP=1000000000/*!*/;
+ALTER TABLE t1 ADD PRIMARY KEY pk_t1 (a,b)
+/*!*/;
+# at #
+#010909 4:46:40 server id # end_log_pos # GTID 0-1-9 trans
+/*!100001 SET @@session.gtid_seq_no=9*//*!*/;
+BEGIN
+/*!*/;
+# at #
+# at #
+#010909 4:46:40 server id # end_log_pos # Annotate_rows:
+#Q> INSERT INTO t1 VALUES (1,2,1),(2,2,2),(3,2,3),(4,2,4)
+#010909 4:46:40 server id # end_log_pos # Table_map: `test`.`t1` mapped to number #
+# at #
+#010909 4:46:40 server id # end_log_pos # Write_rows: table id # flags: STMT_END_F
+# at #
+#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0
+SET TIMESTAMP=1000000000/*!*/;
+COMMIT
+/*!*/;
+# at #
+#010909 4:46:40 server id # end_log_pos # GTID 0-1-10 trans
+/*!100001 SET @@session.gtid_seq_no=10*//*!*/;
+BEGIN
+/*!*/;
+# at #
+# at #
+#010909 4:46:40 server id # end_log_pos # Annotate_rows:
+#Q> UPDATE t1 SET c = 2*c WHERE a % 2 = 0 AND b = 2
+#010909 4:46:40 server id # end_log_pos # Table_map: `test`.`t1` mapped to number #
+# at #
+#010909 4:46:40 server id # end_log_pos # Update_rows: table id # flags: STMT_END_F
+# at #
+#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0
+SET TIMESTAMP=1000000000/*!*/;
+COMMIT
+/*!*/;
+# at #
+#010909 4:46:40 server id # end_log_pos # GTID 0-1-11 trans
+/*!100001 SET @@session.gtid_seq_no=11*//*!*/;
+BEGIN
+/*!*/;
+# at #
+# at #
+#010909 4:46:40 server id # end_log_pos # Annotate_rows:
+#Q> DELETE FROM t1 WHERE a % 2 = 0 AND b = 2
+#010909 4:46:40 server id # end_log_pos # Table_map: `test`.`t1` mapped to number #
+# at #
+#010909 4:46:40 server id # end_log_pos # Delete_rows: table id # flags: STMT_END_F
+# at #
+#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0
+SET TIMESTAMP=1000000000/*!*/;
+COMMIT
+/*!*/;
+# at #
+#010909 4:46:40 server id # end_log_pos # GTID 0-1-12 ddl
+/*!100001 SET @@session.gtid_seq_no=12*//*!*/;
+# at #
+#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0
+SET TIMESTAMP=1000000000/*!*/;
+ALTER TABLE t1 DROP PRIMARY KEY, ADD KEY key_t1 (a)
+/*!*/;
+# at #
+#010909 4:46:40 server id # end_log_pos # GTID 0-1-13 trans
+/*!100001 SET @@session.gtid_seq_no=13*//*!*/;
+BEGIN
+/*!*/;
+# at #
+# at #
+#010909 4:46:40 server id # end_log_pos # Annotate_rows:
+#Q> INSERT INTO t1 VALUES (1,3,1),(2,3,2),(3,3,3),(4,3,4)
+#010909 4:46:40 server id # end_log_pos # Table_map: `test`.`t1` mapped to number #
+# at #
+#010909 4:46:40 server id # end_log_pos # Write_rows: table id # flags: STMT_END_F
+# at #
+#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0
+SET TIMESTAMP=1000000000/*!*/;
+COMMIT
+/*!*/;
+# at #
+#010909 4:46:40 server id # end_log_pos # GTID 0-1-14 trans
+/*!100001 SET @@session.gtid_seq_no=14*//*!*/;
+BEGIN
+/*!*/;
+# at #
+# at #
+#010909 4:46:40 server id # end_log_pos # Annotate_rows:
+#Q> UPDATE t1 SET c = 2*c WHERE a % 2 = 0 AND b = 3
+#010909 4:46:40 server id # end_log_pos # Table_map: `test`.`t1` mapped to number #
+# at #
+#010909 4:46:40 server id # end_log_pos # Update_rows: table id # flags: STMT_END_F
+# at #
+#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0
+SET TIMESTAMP=1000000000/*!*/;
+COMMIT
+/*!*/;
+# at #
+#010909 4:46:40 server id # end_log_pos # GTID 0-1-15 trans
+/*!100001 SET @@session.gtid_seq_no=15*//*!*/;
+BEGIN
+/*!*/;
+# at #
+# at #
+#010909 4:46:40 server id # end_log_pos # Annotate_rows:
+#Q> DELETE FROM t1 WHERE a % 2 = 0 AND b = 3
+#010909 4:46:40 server id # end_log_pos # Table_map: `test`.`t1` mapped to number #
+# at #
+#010909 4:46:40 server id # end_log_pos # Delete_rows: table id # flags: STMT_END_F
+# at #
+#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0
+SET TIMESTAMP=1000000000/*!*/;
+COMMIT
+/*!*/;
+# at #
+#010909 4:46:40 server id # end_log_pos # GTID 0-1-16 ddl
+/*!100001 SET @@session.gtid_seq_no=16*//*!*/;
+# at #
+#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0
+SET TIMESTAMP=1000000000/*!*/;
+DROP TABLE IF EXISTS `t1`,`t2` /* generated by server */
+/*!*/;
+# at #
+#010909 4:46:40 server id # end_log_pos # Rotate to slave-bin.000002 pos: 4
+DELIMITER ;
+# End of log file
+ROLLBACK /* added by mysqlbinlog */;
+/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
+/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
+include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_blackhole.test b/mysql-test/suite/rpl/t/rpl_blackhole.test
index 76b2e2421c9..9128382d12b 100644
--- a/mysql-test/suite/rpl/t/rpl_blackhole.test
+++ b/mysql-test/suite/rpl/t/rpl_blackhole.test
@@ -20,81 +20,6 @@ source include/master-slave.inc;
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
-# We start with no primary key
-CREATE TABLE t1 (a INT, b INT, c INT);
-CREATE TABLE t2 (a INT, b INT, c INT);
+source extra/rpl_tests/rpl_blackhole_basic.test;
-sync_slave_with_master;
-ALTER TABLE t1 ENGINE=BLACKHOLE;
-
-connection master;
-INSERT INTO t2 VALUES (1,9,1), (2,9,2), (3,9,3), (4,9,4);
-sync_slave_with_master;
-
-# Test insert, no primary key
-let $statement = INSERT INTO t1 VALUES (1,1,1),(2,1,2),(3,1,3),(4,1,4);
-source extra/rpl_tests/rpl_blackhole.test;
-
-# Test update, no primary key
-let $statement = UPDATE t1 SET c = 2*c WHERE a % 2 = 0 AND b = 1;
-source extra/rpl_tests/rpl_blackhole.test;
-
-# Test delete, no primary key
-let $statement = DELETE FROM t1 WHERE a % 2 = 0 AND b = 1;
-source extra/rpl_tests/rpl_blackhole.test;
-
-# Test INSERT-SELECT into Blackhole, no primary key
-let $statement = INSERT INTO t1 SELECT * FROM t2;
-source extra/rpl_tests/rpl_blackhole.test;
-
-#
-# The MASTER has MyISAM as the engine for both tables. The SLAVE has Blackhole
-# on t1 (transactional engine) and MyISAM on t2 (non-transactional engine).
-#
-# In MIXED mode, the command "INSERT INTO t2 SELECT * FROM t1" is logged as
-# statement on the master. On the slave, it is tagged as unsafe because the
-# statement mixes both transactional and non-transactional engines and as such
-# its changes are logged as rows. However, due to the nature of the blackhole
-# engine, no rows are returned and thus any chain replication would make the
-# next master on the chain diverge.
-#
-# Fo this reason, we have disabled the statement.
-#
-# Test INSERT-SELECT from Blackhole, no primary key
-# let $statement = INSERT INTO t2 SELECT * FROM t1;
-# source extra/rpl_tests/rpl_blackhole.test;
-#
-
-connection master;
-ALTER TABLE t1 ADD PRIMARY KEY pk_t1 (a,b);
-
-# Test insert, primary key
-let $statement = INSERT INTO t1 VALUES (1,2,1),(2,2,2),(3,2,3),(4,2,4);
-source extra/rpl_tests/rpl_blackhole.test;
-
-# Test update, primary key
-let $statement = UPDATE t1 SET c = 2*c WHERE a % 2 = 0 AND b = 2;
-source extra/rpl_tests/rpl_blackhole.test;
-
-# Test delete, primary key
-let $statement = DELETE FROM t1 WHERE a % 2 = 0 AND b = 2;
-source extra/rpl_tests/rpl_blackhole.test;
-
-connection master;
-ALTER TABLE t1 DROP PRIMARY KEY, ADD KEY key_t1 (a);
-
-# Test insert, key
-let $statement = INSERT INTO t1 VALUES (1,3,1),(2,3,2),(3,3,3),(4,3,4);
-source extra/rpl_tests/rpl_blackhole.test;
-
-# Test update, key
-let $statement = UPDATE t1 SET c = 2*c WHERE a % 2 = 0 AND b = 3;
-source extra/rpl_tests/rpl_blackhole.test;
-
-# Test delete, key
-let $statement = DELETE FROM t1 WHERE a % 2 = 0 AND b = 3;
-source extra/rpl_tests/rpl_blackhole.test;
-
-connection master;
-DROP TABLE t1,t2;
--source include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_blackhole_row_annotate-master.opt b/mysql-test/suite/rpl/t/rpl_blackhole_row_annotate-master.opt
new file mode 100644
index 00000000000..91302791099
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_blackhole_row_annotate-master.opt
@@ -0,0 +1 @@
+--binlog_annotate_row_events --timezone=GMT-3
diff --git a/mysql-test/suite/rpl/t/rpl_blackhole_row_annotate-slave.opt b/mysql-test/suite/rpl/t/rpl_blackhole_row_annotate-slave.opt
new file mode 100644
index 00000000000..7ac6a84faa7
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_blackhole_row_annotate-slave.opt
@@ -0,0 +1 @@
+--binlog_annotate_row_events --replicate_annotate_row_events
diff --git a/mysql-test/suite/rpl/t/rpl_blackhole_row_annotate.test b/mysql-test/suite/rpl/t/rpl_blackhole_row_annotate.test
new file mode 100644
index 00000000000..77384d91475
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_blackhole_row_annotate.test
@@ -0,0 +1,49 @@
+# ==== Purpose ====
+#
+# Test verifies that when "replicate_annotate_row_events" are enabled on slave
+# the DML operations on blackhole engine will be successful. It also ensures
+# that Annotate events are logged into slave's binary log.
+#
+# ==== Implementation ====
+#
+# Steps:
+# 0 - Enable "replicate_annotate_row_events" on slave and do DML operations
+# on master.
+# 1 - Slave server will successfully apply the DML operations and it is in
+# sync with master.
+# 2 - Verify that the "show binlog events" prints all annotate events.
+# 3 - Stream the slave's binary log using "mysqlbinlog" tool and verify
+# that the Annotate events are being displayed.
+#
+# ==== References ====
+#
+# MDEV-11094: Blackhole table updates on slave fail when row annotation is
+# enabled
+
+source include/have_blackhole.inc;
+source include/have_binlog_format_row.inc;
+source include/binlog_start_pos.inc;
+source include/master-slave.inc;
+
+SET timestamp=1000000000;
+RESET MASTER;
+connection slave;
+SET timestamp=1000000000;
+RESET MASTER;
+
+connection master;
+source extra/rpl_tests/rpl_blackhole_basic.test;
+
+# Verify on slave.
+connection slave;
+FLUSH LOGS;
+--replace_column 2 # 5 #
+--replace_result $binlog_start_pos <start_pos>
+--replace_regex /table_id: [0-9]+/table_id: #/ /\/\* xid=.* \*\//\/* xid= *\//
+--eval show binlog events in 'slave-bin.000001' from $binlog_start_pos
+
+let $MYSQLD_DATADIR= `select @@datadir`;
+--replace_regex /server id [0-9]*/server id #/ /server v [^ ]*/server v #.##.##/ /exec_time=[0-9]*/exec_time=#/ /thread_id=[0-9]*/thread_id=#/ /table id [0-9]*/table id #/ /mapped to number [0-9]*/mapped to number #/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/
+--exec $MYSQL_BINLOG --base64-output=decode-rows $MYSQLD_DATADIR/slave-bin.000001
+
+source include/rpl_end.inc;
diff --git a/storage/blackhole/ha_blackhole.cc b/storage/blackhole/ha_blackhole.cc
index 01aaa9ea15f..43bcdc541a1 100644
--- a/storage/blackhole/ha_blackhole.cc
+++ b/storage/blackhole/ha_blackhole.cc
@@ -25,6 +25,16 @@
#include "ha_blackhole.h"
#include "sql_class.h" // THD, SYSTEM_THREAD_SLAVE_SQL
+static bool is_row_based_replication(THD *thd)
+{
+ /*
+ A row event which has its thd->query() == NULL or a row event which has
+ replicate_annotate_row_events enabled. In the later case the thd->query()
+ will be pointing to the query, received through replicated annotate event
+ from master.
+ */
+ return ((thd->query() == NULL) || thd->variables.binlog_annotate_row_events);
+}
/* Static declarations for handlerton */
static handler *blackhole_create_handler(handlerton *hton,
@@ -109,7 +119,8 @@ int ha_blackhole::update_row(const uchar *old_data, uchar *new_data)
{
DBUG_ENTER("ha_blackhole::update_row");
THD *thd= ha_thd();
- if (thd->system_thread == SYSTEM_THREAD_SLAVE_SQL && thd->query() == NULL)
+ if (thd->system_thread == SYSTEM_THREAD_SLAVE_SQL &&
+ is_row_based_replication(thd))
DBUG_RETURN(0);
DBUG_RETURN(HA_ERR_WRONG_COMMAND);
}
@@ -118,7 +129,8 @@ int ha_blackhole::delete_row(const uchar *buf)
{
DBUG_ENTER("ha_blackhole::delete_row");
THD *thd= ha_thd();
- if (thd->system_thread == SYSTEM_THREAD_SLAVE_SQL && thd->query() == NULL)
+ if (thd->system_thread == SYSTEM_THREAD_SLAVE_SQL &&
+ is_row_based_replication(thd))
DBUG_RETURN(0);
DBUG_RETURN(HA_ERR_WRONG_COMMAND);
}
@@ -135,7 +147,8 @@ int ha_blackhole::rnd_next(uchar *buf)
int rc;
DBUG_ENTER("ha_blackhole::rnd_next");
THD *thd= ha_thd();
- if (thd->system_thread == SYSTEM_THREAD_SLAVE_SQL && thd->query() == NULL)
+ if (thd->system_thread == SYSTEM_THREAD_SLAVE_SQL &&
+ is_row_based_replication(thd))
rc= 0;
else
rc= HA_ERR_END_OF_FILE;
@@ -220,7 +233,8 @@ int ha_blackhole::index_read_map(uchar * buf, const uchar * key,
int rc;
DBUG_ENTER("ha_blackhole::index_read");
THD *thd= ha_thd();
- if (thd->system_thread == SYSTEM_THREAD_SLAVE_SQL && thd->query() == NULL)
+ if (thd->system_thread == SYSTEM_THREAD_SLAVE_SQL &&
+ is_row_based_replication(thd))
rc= 0;
else
rc= HA_ERR_END_OF_FILE;
@@ -235,7 +249,8 @@ int ha_blackhole::index_read_idx_map(uchar * buf, uint idx, const uchar * key,
int rc;
DBUG_ENTER("ha_blackhole::index_read_idx");
THD *thd= ha_thd();
- if (thd->system_thread == SYSTEM_THREAD_SLAVE_SQL && thd->query() == NULL)
+ if (thd->system_thread == SYSTEM_THREAD_SLAVE_SQL &&
+ is_row_based_replication(thd))
rc= 0;
else
rc= HA_ERR_END_OF_FILE;
@@ -249,7 +264,8 @@ int ha_blackhole::index_read_last_map(uchar * buf, const uchar * key,
int rc;
DBUG_ENTER("ha_blackhole::index_read_last");
THD *thd= ha_thd();
- if (thd->system_thread == SYSTEM_THREAD_SLAVE_SQL && thd->query() == NULL)
+ if (thd->system_thread == SYSTEM_THREAD_SLAVE_SQL &&
+ is_row_based_replication(thd))
rc= 0;
else
rc= HA_ERR_END_OF_FILE;
2
1
[Commits] 0fa3079ccc9: Mdev-17588 replicate-do filters cause errors when creating filtered-out tables on master with syntax unsupported on slave
by sachin.setiyaï¼ mariadb.com 29 May '19
by sachin.setiyaï¼ mariadb.com 29 May '19
29 May '19
revision-id: 0fa3079ccc93288d4ec93f713bc162d69cc477d0 (mariadb-10.1.38-114-g0fa3079ccc9)
parent(s): 6c5e4c9bc0d9ac30f7ec7ee334630bacb58687ba
author: Sachin
committer: Sachin
timestamp: 2019-05-27 21:44:48 +0530
message:
Mdev-17588 replicate-do filters cause errors when creating filtered-out tables on master with syntax unsupported on slave
Prototype -4
Use a ME_DEFERRED_ERROR/ WARN_LEVEL_DEFERRED_WARN for my_error/push_warning
when error can be deferred. It will store the error/warning in m_warn_list.
And thd->is_error() will return false. To actiave the error/warning we need
to call these functions change_deferred_to_normal_warn/error
---
include/my_sys.h | 5 +++
mysql-test/suite/rpl/r/rpl_mdev_17588.result | 27 +++++++++++++
mysql-test/suite/rpl/t/rpl_mdev_17588-slave.opt | 1 +
mysql-test/suite/rpl/t/rpl_mdev_17588.test | 36 +++++++++++++++++
sql/mysqld.cc | 10 +++++
sql/sql_class.cc | 2 +
sql/sql_class.h | 4 ++
sql/sql_error.cc | 51 +++++++++++++++++++++++++
sql/sql_error.h | 33 +++++++++++++++-
sql/sql_parse.cc | 18 +++++++++
sql/sql_yacc.yy | 14 +++++--
11 files changed, 196 insertions(+), 5 deletions(-)
diff --git a/include/my_sys.h b/include/my_sys.h
index c30580a8c06..f6dcc278225 100644
--- a/include/my_sys.h
+++ b/include/my_sys.h
@@ -112,6 +112,11 @@ typedef struct my_aio_result {
#define ME_JUST_INFO 1024 /**< not error but just info */
#define ME_JUST_WARNING 2048 /**< not error but just warning */
#define ME_FATALERROR 4096 /* Fatal statement error */
+/* Throw error at later stage */
+#define ME_DEFERRED_JUST_INFO 8192 /* Not USED*/
+#define ME_DEFERRED_JUST_WARNING 16384
+#define ME_DEFERRED_ERROR 32768
+
/* Bits in last argument to fn_format */
#define MY_REPLACE_DIR 1 /* replace dir in name with 'dir' */
diff --git a/mysql-test/suite/rpl/r/rpl_mdev_17588.result b/mysql-test/suite/rpl/r/rpl_mdev_17588.result
new file mode 100644
index 00000000000..e1b017950c2
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_mdev_17588.result
@@ -0,0 +1,27 @@
+include/master-slave.inc
+[connection master]
+set sql_log_bin= 0;
+install soname 'ha_tokudb';
+set sql_log_bin= 1;
+set server_id=23;
+create table t1 (a int) engine=TokuDB;
+create table t2 (a int);
+create table t3 (a int) engine=TokuDB;
+include/wait_for_slave_sql_error.inc [errno=1286]
+show create table t1;
+ERROR 42S02: Table 'test.t1' doesn't exist
+show create table t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+show create table t3;
+ERROR 42S02: Table 'test.t3' doesn't exist
+SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
+START SLAVE;
+drop table t1, t2, t3;
+set sql_log_bin= 0;
+uninstall soname 'ha_tokudb';
+set sql_log_bin= 1;
+CALL mtr.add_suppression('Slave: Unknown storage engine .* Error_code: 1286');
+include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_mdev_17588-slave.opt b/mysql-test/suite/rpl/t/rpl_mdev_17588-slave.opt
new file mode 100644
index 00000000000..19497afd22a
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_mdev_17588-slave.opt
@@ -0,0 +1 @@
+--replicate-do-table=test.t2 --replicate-do-table=test.t3 --sql-mode='NO_ENGINE_SUBSTITUTION'
diff --git a/mysql-test/suite/rpl/t/rpl_mdev_17588.test b/mysql-test/suite/rpl/t/rpl_mdev_17588.test
new file mode 100644
index 00000000000..b073b791d7a
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_mdev_17588.test
@@ -0,0 +1,36 @@
+--source include/master-slave.inc
+
+
+--connection slave
+# For debugging
+#create table xyz(a int );
+
+--connection master
+set sql_log_bin= 0;
+install soname 'ha_tokudb';
+set sql_log_bin= 1;
+set server_id=23;
+
+create table t1 (a int) engine=TokuDB;
+create table t2 (a int);
+create table t3 (a int) engine=TokuDB;
+
+--connection slave
+let $slave_sql_errno= 1286;
+source include/wait_for_slave_sql_error.inc;
+--sleep 10
+--error ER_NO_SUCH_TABLE
+show create table t1;
+show create table t2;
+--error ER_NO_SUCH_TABLE
+show create table t3;
+SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
+START SLAVE;
+--connection master
+drop table t1, t2, t3;
+set sql_log_bin= 0;
+uninstall soname 'ha_tokudb';
+set sql_log_bin= 1;
+--sync_slave_with_master
+CALL mtr.add_suppression('Slave: Unknown storage engine .* Error_code: 1286');
+--source include/rpl_end.inc
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 8d464ed75e6..d68b3c16e95 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -3524,6 +3524,16 @@ void my_message_sql(uint error, const char *str, myf MyFlags)
level= Sql_condition::WARN_LEVEL_WARN;
func= sql_print_warning;
}
+ else if (MyFlags & ME_DEFERRED_JUST_WARNING)
+ {
+ level= Sql_condition::WARN_LEVEL_DEFERRED_WARN;
+ func= sql_print_warning;
+ }
+ else if (MyFlags & ME_DEFERRED_ERROR)
+ {
+ level= Sql_condition::WARN_LEVEL_DEFERRED_ERROR;
+ func= sql_print_error;
+ }
else
{
level= Sql_condition::WARN_LEVEL_ERROR;
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 639c7c1784a..de637ccaf8c 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -1167,9 +1167,11 @@ Sql_condition* THD::raise_condition(uint sql_errno,
{
case Sql_condition::WARN_LEVEL_NOTE:
case Sql_condition::WARN_LEVEL_WARN:
+ case Sql_condition::WARN_LEVEL_DEFERRED_WARN:
got_warning= 1;
break;
case Sql_condition::WARN_LEVEL_ERROR:
+ case Sql_condition::WARN_LEVEL_DEFERRED_ERROR:
break;
default:
DBUG_ASSERT(FALSE);
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 6640e02147a..c51800cb6d7 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -3405,6 +3405,10 @@ class THD :public Statement,
Diagnostics_area *get_stmt_da()
{ return m_stmt_da; }
+ /// Get Deferred error status
+ bool get_deferred_error()
+ { return m_stmt_da->get_deferred_error();}
+
/// Returns Diagnostics-area for the current statement.
const Diagnostics_area *get_stmt_da() const
{ return m_stmt_da; }
diff --git a/sql/sql_error.cc b/sql/sql_error.cc
index b72d642efbc..b0b0349e968 100644
--- a/sql/sql_error.cc
+++ b/sql/sql_error.cc
@@ -312,6 +312,7 @@ Sql_condition::get_message_octet_length() const
return m_message_text.length();
}
+
void
Sql_condition::set_sqlstate(const char* sqlstate)
{
@@ -319,6 +320,15 @@ Sql_condition::set_sqlstate(const char* sqlstate)
m_returned_sqlstate[SQLSTATE_LENGTH]= '\0';
}
+void
+Sql_condition::deferred_to_normal()
+{
+ if (m_level == Sql_condition::WARN_LEVEL_DEFERRED_ERROR)
+ m_level= Sql_condition::WARN_LEVEL_ERROR;
+ if (m_level == Sql_condition::WARN_LEVEL_DEFERRED_WARN)
+ m_level= Sql_condition::WARN_LEVEL_WARN;
+}
+
Diagnostics_area::Diagnostics_area(bool initialize)
: m_main_wi(0, false, initialize)
{
@@ -657,6 +667,47 @@ void Warning_info::remove_marked_sql_conditions()
m_marked_sql_conditions.empty();
}
+const Sql_condition* Warning_info::get_first_deferred_error()
+{
+ Diagnostics_area::Sql_condition_iterator it(m_warn_list);
+ const Sql_condition *err;
+
+ while ((err= it++))
+ {
+ if (err->m_level == Sql_condition::WARN_LEVEL_DEFERRED_ERROR)
+ return err;
+ }
+ return NULL;
+}
+void Warning_info::change_deferred_to_normal_warning()
+{
+ Diagnostics_area::Sql_condition_iterator it(m_warn_list);
+ const Sql_condition *err;
+
+ while ((err= it++))
+ ((Sql_condition *)err)->deferred_to_normal();
+ m_warn_count[Sql_condition::WARN_LEVEL_WARN]+=
+ m_warn_count[Sql_condition::WARN_LEVEL_DEFERRED_WARN];
+ m_warn_count[Sql_condition::WARN_LEVEL_ERROR]+=
+ m_warn_count[Sql_condition::WARN_LEVEL_DEFERRED_ERROR];
+ m_warn_count[Sql_condition::WARN_LEVEL_DEFERRED_WARN]= 0;
+ m_warn_count[Sql_condition::WARN_LEVEL_DEFERRED_ERROR]= 0;
+
+}
+void Warning_info::remove_deferred_error_and_warning()
+{
+ Diagnostics_area::Sql_condition_iterator it(m_warn_list);
+ const Sql_condition *err;
+
+ while ((err= it++))
+ {
+ if (err->m_level > Sql_condition::WARN_LEVEL_DEFERRED_ERROR)
+ m_warn_list.remove((Sql_condition *)err);
+ }
+ m_warn_count[Sql_condition::WARN_LEVEL_DEFERRED_WARN]= 0;
+ m_warn_count[Sql_condition::WARN_LEVEL_DEFERRED_ERROR]= 0;
+
+}
bool Warning_info::is_marked_for_removal(const Sql_condition *cond) const
{
diff --git a/sql/sql_error.h b/sql/sql_error.h
index 0134f938c75..8613eb642bf 100644
--- a/sql/sql_error.h
+++ b/sql/sql_error.h
@@ -45,7 +45,8 @@ class Sql_condition : public Sql_alloc
of the sql_print_message_handlers array.
*/
enum enum_warning_level
- { WARN_LEVEL_NOTE, WARN_LEVEL_WARN, WARN_LEVEL_ERROR, WARN_LEVEL_END};
+ { WARN_LEVEL_NOTE, WARN_LEVEL_WARN, WARN_LEVEL_ERROR,
+ WARN_LEVEL_DEFERRED_WARN, WARN_LEVEL_DEFERRED_ERROR, WARN_LEVEL_END};
/**
Convert a bitmask consisting of MYSQL_TIME_{NOTE|WARN}_XXX bits
@@ -170,6 +171,8 @@ class Sql_condition : public Sql_alloc
/** Set the SUBCLASS_ORIGIN of this condition. */
void set_subclass_origin();
+ void deferred_to_normal();
+
/**
Clear this SQL condition.
*/
@@ -307,6 +310,13 @@ class Warning_info
/* Allocate memory for structures */
void init();
void free_memory();
+ bool get_deferred_error()
+ {
+ if (m_warn_count[Sql_condition::WARN_LEVEL_DEFERRED_WARN] ||
+ m_warn_count[Sql_condition::WARN_LEVEL_DEFERRED_ERROR])
+ return true;
+ return false;
+ }
private:
Warning_info(const Warning_info &rhs); /* Not implemented */
@@ -387,6 +397,9 @@ class Warning_info
This is done to simulate stacked DAs for HANDLER statements.
*/
void remove_marked_sql_conditions();
+ const Sql_condition* get_first_deferred_error();
+ void change_deferred_to_normal_warning();
+ void remove_deferred_error_and_warning();
/**
Check if the given SQL-condition is marked for removal in this Warning_info
@@ -833,6 +846,24 @@ class Diagnostics_area
void remove_marked_sql_conditions()
{ get_warning_info()->remove_marked_sql_conditions(); }
+ void change_deferred_to_normal_error()
+ {
+ const Sql_condition *cond= get_warning_info()->get_first_deferred_error();
+ if (!cond)
+ return;
+ this->set_error_status(cond->get_sql_errno(), cond->get_message_text(),
+ cond->get_sqlstate(), cond);
+ }
+
+ void change_deferred_to_normal_warning()
+ {get_warning_info()->change_deferred_to_normal_warning();}
+
+ void remove_deferred_error_and_warning()
+ { get_warning_info()->remove_deferred_error_and_warning();}
+
+ bool get_deferred_error()
+ { return get_warning_info()->get_deferred_error();}
+
const Sql_condition *get_error_condition() const
{ return get_warning_info()->get_error_condition(); }
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 6649c60f827..0949883d1c7 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -2653,9 +2653,27 @@ mysql_execute_command(THD *thd)
lex->tmp_table() && lex->if_exists()) &&
all_tables_not_ok(thd, all_tables))
{
+ if (thd->get_deferred_error())
+ {
+ /* Clear the WARN_LEVEL_DEFERRED_WARN and WARN_LEVEL_DEFERRED_ERROR */
+ thd->get_stmt_da()->remove_deferred_error_and_warning();
+ }
/* we warn the slave SQL thread */
my_message(ER_SLAVE_IGNORED_TABLE, ER_THD(thd, ER_SLAVE_IGNORED_TABLE),
MYF(0));
+
+ DBUG_RETURN(0);
+ }
+ if (thd->get_deferred_error())
+ {
+ thd->is_slave_error= true;
+ /*
+ 1 Set the thd err status
+ 2 convert WARN_LEVEL_DEFERRED_WARN WARN_LEVEL_DEFERRED_ERROR to
+ WARN_LEVEL_WARN and WARN_LEVEL_DEFERRED_ERROR
+ */
+ thd->get_stmt_da()->change_deferred_to_normal_error();
+ thd->get_stmt_da()->change_deferred_to_normal_warning();
DBUG_RETURN(0);
}
/*
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 5111f0690ab..444d2c06075 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -107,6 +107,7 @@ int yylex(void *yylval, void *yythd);
#else
#define YYDEBUG 0
#endif
+#define DEFERRED_OR_NORMAL(A) A ? Sql_condition::WARN_LEVEL_WARN:Sql_condition::WARN_LEVEL_DEFERRED_WARN
/**
@brief Push an error message into MySQL error stack with line
@@ -2476,10 +2477,10 @@ create:
LEX *lex= thd->lex;
lex->current_select= &lex->select_lex;
if ((lex->create_info.used_fields & HA_CREATE_USED_ENGINE) &&
- !lex->create_info.db_type)
+ !lex->create_info.db_type && !thd->slave_thread)
{
lex->create_info.use_default_db_type(thd);
- push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
+ push_warning_printf(thd, DEFERRED_OR_NORMAL(thd->slave_thread),
ER_WARN_USING_OTHER_HANDLER,
ER_THD(thd, ER_WARN_USING_OTHER_HANDLER),
hton_name(lex->create_info.db_type)->str,
@@ -5791,9 +5792,14 @@ storage_engines:
else
{
if (thd->variables.sql_mode & MODE_NO_ENGINE_SUBSTITUTION)
- my_yyabort_error((ER_UNKNOWN_STORAGE_ENGINE, MYF(0), $1.str));
+ {
+ if (!thd->slave_thread)
+ my_yyabort_error((ER_UNKNOWN_STORAGE_ENGINE, MYF(0), $1.str));
+ else
+ my_error(ER_UNKNOWN_STORAGE_ENGINE, MYF(ME_DEFERRED_ERROR), $1.str);
+ }
$$= 0;
- push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
+ push_warning_printf(thd, DEFERRED_OR_NORMAL(thd->slave_thread),
ER_UNKNOWN_STORAGE_ENGINE,
ER_THD(thd, ER_UNKNOWN_STORAGE_ENGINE),
$1.str);
3
2
revision-id: cbb90f77cdbf57c02145dc6cd86acf8ebb8a88f0 (mariadb-5.5.64-12-gcbb90f7)
parent(s): eb09580b67ee19f7ac30c1a41c8307b9c7d482d1
author: Igor Babaev
committer: Igor Babaev
timestamp: 2019-05-28 23:26:36 -0700
message:
MDEV-18479 Complement
This patch complements the patch that fixes bug MDEV-18479.
This patch takes care of possible overflow when calculating the
estimated number of rows in a materialized derived table / view.
---
include/my_base.h | 1 +
mysql-test/r/derived_view.result | 20 ++++++++++----------
mysql-test/t/derived_view.test | 2 +-
sql/sql_lex.cc | 5 ++++-
sql/sql_select.cc | 21 +++++++++++++--------
5 files changed, 29 insertions(+), 20 deletions(-)
diff --git a/include/my_base.h b/include/my_base.h
index 54e8443..86be943 100644
--- a/include/my_base.h
+++ b/include/my_base.h
@@ -586,6 +586,7 @@ typedef ulong ha_rows;
#define HA_POS_ERROR (~ (ha_rows) 0)
#define HA_OFFSET_ERROR (~ (my_off_t) 0)
+#define HA_ROWS_MAX HA_POS_ERROR
#if SYSTEM_SIZEOF_OFF_T == 4
#define MAX_FILE_SIZE INT_MAX32
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index ab36393..d74b532 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -2641,7 +2641,7 @@ DROP TABLE t1, t2;
set optimizer_switch=@exit_optimizer_switch;
set join_cache_level=@exit_join_cache_level;
#
-# Bug mdev-12812: EXPLAIN for query with many expensive derived
+# Bug mdev-18479: EXPLAIN for query with many expensive derived
#
CREATE TABLE t1
(id int auto_increment primary key,
@@ -2942,15 +2942,15 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
1 SIMPLE <derived17> ALL NULL NULL NULL NULL 50328437500000 Using where; Using join buffer (incremental, BNL join)
1 SIMPLE <derived14> ALL NULL NULL NULL NULL 27680640625000000 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived7> ALL NULL NULL NULL NULL 7798774269472204288 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived8> ALL NULL NULL NULL NULL 7798774269472204288 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived9> ALL NULL NULL NULL NULL -3222391729959551616 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived10> ALL NULL NULL NULL NULL -3222391729959551616 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived11> ALL NULL NULL NULL NULL -3222391729959551616 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived12> ALL NULL NULL NULL NULL -3222391729959551616 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived13> ALL NULL NULL NULL NULL -3222391729959551616 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived15> ALL NULL NULL NULL NULL -3222391729959551616 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE <derived16> ALL NULL NULL NULL NULL -3222391729959551616 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived9> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived10> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived11> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived12> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived13> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived15> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived16> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived7> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived8> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join)
17 DERIVED t2 system NULL NULL NULL NULL 1
17 DERIVED p4 ALL NULL NULL NULL NULL 550 Using where
17 DERIVED p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (flat, BNL join)
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index f6613e2..61c4278 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -1936,7 +1936,7 @@ set optimizer_switch=@exit_optimizer_switch;
set join_cache_level=@exit_join_cache_level;
--echo #
---echo # Bug mdev-12812: EXPLAIN for query with many expensive derived
+--echo # Bug mdev-18479: EXPLAIN for query with many expensive derived
--echo #
CREATE TABLE t1
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 3e20cdb..28f5628 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -4100,7 +4100,10 @@ void SELECT_LEX::increase_derived_records(ha_rows records)
DBUG_ASSERT(unit->derived);
select_union *result= (select_union*)unit->result;
- result->records+= records;
+ if (HA_ROWS_MAX - records > result->records)
+ result->records+= records;
+ else
+ result->records= HA_ROWS_MAX;
}
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 961a7da..37f8292 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -3830,7 +3830,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
DBUG_RETURN(TRUE); /* purecov: inspected */
{
- ha_rows records= 1;
+ double records= 1;
SELECT_LEX_UNIT *unit= join->select_lex->master_unit();
/* Find an optimal join order of the non-constant tables. */
@@ -3855,10 +3855,14 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
table/view.
*/
for (i= 0; i < join->table_count ; i++)
- records*= join->best_positions[i].records_read ?
- (ha_rows)join->best_positions[i].records_read : 1;
- set_if_smaller(records, unit->select_limit_cnt);
- join->select_lex->increase_derived_records(records);
+ {
+ records= COST_MULT(records,
+ join->best_positions[i].records_read ?
+ join->best_positions[i].records_read : 1);
+ }
+ ha_rows rows= records > HA_ROWS_MAX ? HA_ROWS_MAX : (ha_rows) records;
+ set_if_smaller(rows, unit->select_limit_cnt);
+ join->select_lex->increase_derived_records(rows);
}
}
@@ -10795,7 +10799,7 @@ ha_rows JOIN_TAB::get_examined_rows()
}
}
else
- examined_rows= (ha_rows) records_read;
+ examined_rows= (ha_rows) records_read;
return examined_rows;
}
@@ -22924,8 +22928,9 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
else
{
ha_rows examined_rows= tab->get_examined_rows();
-
- item_list.push_back(new Item_int((longlong) (ulonglong) examined_rows,
+ ha_rows displ_rows= examined_rows;
+ set_if_smaller(displ_rows, HA_ROWS_MAX/2);
+ item_list.push_back(new Item_int((longlong) (ulonglong) displ_rows,
MY_INT64_NUM_DECIMAL_DIGITS));
/* Add "filtered" field to item_list. */
1
0
28 May '19
revision-id: eb09580b67ee19f7ac30c1a41c8307b9c7d482d1 (mariadb-5.5.64-11-geb09580)
parent(s): 0955462d0aafab01def9c1a5ec131eb641cb9e68
author: Igor Babaev
committer: Igor Babaev
timestamp: 2019-05-28 14:53:08 -0700
message:
MDEV-19588 Wrong results from query, using left join.
This bug could happen when queries with nested outer joins were
executed employing join buffers. At such an execution if the method
JOIN_CACHE::join_records() is called when a join buffer has become
full no 'first_unmatched' field should be cleaned up in the JOIN_TAB
structure to which the join cache with this buffer is attached.
---
mysql-test/r/join_nested.result | 33 +++++++++++++++++++++++++++++++++
mysql-test/r/join_nested_jcl6.result | 33 +++++++++++++++++++++++++++++++++
mysql-test/t/join_nested.test | 34 ++++++++++++++++++++++++++++++++++
sql/sql_join_cache.cc | 3 ++-
4 files changed, 102 insertions(+), 1 deletion(-)
diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result
index 6ddd39c..d618f7c 100644
--- a/mysql-test/r/join_nested.result
+++ b/mysql-test/r/join_nested.result
@@ -1966,3 +1966,36 @@ Note 1003 select 1 AS `K1`,'T1Row1' AS `Name`,`t2a`.`K2` AS `K2`,`t2a`.`K1r` AS
DROP VIEW v1;
DROP TABLE t1,t2;
set optimizer_search_depth= @tmp_mdev621;
+#
+# MDEV-19588: Nested left joins using optimized join cache
+#
+set optimizer_switch='optimize_join_buffer_size=on';
+set @save_join_cache_level= @@join_cache_level;
+set join_cache_level=2;
+CREATE TABLE t1 (i1 int, c1 varchar(20), pk int) engine=myisam;
+CREATE TABLE t2 (pk int, c1 varchar(20), i1 int) engine=myisam;
+INSERT INTO t2 VALUES (7,'a',-912),(8,'a',5);
+CREATE TABLE t3 (pk int, c1 varchar(20), i1 int) engine=myisam;
+INSERT INTO t3 VALUES
+(1,'a',-145),(2,'a',6),(3,'a',1),(7,'a',NULL),(8,'a',889),(9,'a',146),
+(10,'a',177),(16,'a',-433),(17,'a',NULL),(18,'a',2),(19,'a',3),(20,'a',5),
+(21,'a',-484),(22,'a',369),(23,'a',-192),(24,'a',-163),(25,'a',5),(26,'a',NULL);
+SELECT t3.*
+FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1
+WHERE t2.pk < 13 OR t3.i1 IS NULL;
+pk c1 i1
+7 a NULL
+17 a NULL
+26 a NULL
+explain extended SELECT t3.*
+FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1
+WHERE t2.pk < 13 OR t3.i1 IS NULL;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t3 ALL NULL NULL NULL NULL 18 100.00
+1 SIMPLE t1 ALL NULL NULL NULL NULL 0 0.00 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join)
+Warnings:
+Note 1003 select `test`.`t3`.`pk` AS `pk`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`i1` AS `i1` from `test`.`t3` left join (`test`.`t1` left join `test`.`t2` on((`test`.`t2`.`i1` = `test`.`t3`.`i1`))) on((`test`.`t1`.`i1` = `test`.`t3`.`i1`)) where ((`test`.`t2`.`pk` < 13) or isnull(`test`.`t3`.`i1`))
+DROP TABLE t1,t2,t3;
+set join_cache_level= @save_join_cache_level;
+set optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/join_nested_jcl6.result b/mysql-test/r/join_nested_jcl6.result
index bac8e1c..b99b020 100644
--- a/mysql-test/r/join_nested_jcl6.result
+++ b/mysql-test/r/join_nested_jcl6.result
@@ -1977,6 +1977,39 @@ Note 1003 select 1 AS `K1`,'T1Row1' AS `Name`,`t2a`.`K2` AS `K2`,`t2a`.`K1r` AS
DROP VIEW v1;
DROP TABLE t1,t2;
set optimizer_search_depth= @tmp_mdev621;
+#
+# MDEV-19588: Nested left joins using optimized join cache
+#
+set optimizer_switch='optimize_join_buffer_size=on';
+set @save_join_cache_level= @@join_cache_level;
+set join_cache_level=2;
+CREATE TABLE t1 (i1 int, c1 varchar(20), pk int) engine=myisam;
+CREATE TABLE t2 (pk int, c1 varchar(20), i1 int) engine=myisam;
+INSERT INTO t2 VALUES (7,'a',-912),(8,'a',5);
+CREATE TABLE t3 (pk int, c1 varchar(20), i1 int) engine=myisam;
+INSERT INTO t3 VALUES
+(1,'a',-145),(2,'a',6),(3,'a',1),(7,'a',NULL),(8,'a',889),(9,'a',146),
+(10,'a',177),(16,'a',-433),(17,'a',NULL),(18,'a',2),(19,'a',3),(20,'a',5),
+(21,'a',-484),(22,'a',369),(23,'a',-192),(24,'a',-163),(25,'a',5),(26,'a',NULL);
+SELECT t3.*
+FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1
+WHERE t2.pk < 13 OR t3.i1 IS NULL;
+pk c1 i1
+7 a NULL
+17 a NULL
+26 a NULL
+explain extended SELECT t3.*
+FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1
+WHERE t2.pk < 13 OR t3.i1 IS NULL;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t3 ALL NULL NULL NULL NULL 18 100.00
+1 SIMPLE t1 ALL NULL NULL NULL NULL 0 0.00 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join)
+Warnings:
+Note 1003 select `test`.`t3`.`pk` AS `pk`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`i1` AS `i1` from `test`.`t3` left join (`test`.`t1` left join `test`.`t2` on((`test`.`t2`.`i1` = `test`.`t3`.`i1`))) on((`test`.`t1`.`i1` = `test`.`t3`.`i1`)) where ((`test`.`t2`.`pk` < 13) or isnull(`test`.`t3`.`i1`))
+DROP TABLE t1,t2,t3;
+set join_cache_level= @save_join_cache_level;
+set optimizer_switch=@save_optimizer_switch;
CREATE TABLE t5 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
CREATE TABLE t6 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
CREATE TABLE t7 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test
index e60b782..cfb24a6 100644
--- a/mysql-test/t/join_nested.test
+++ b/mysql-test/t/join_nested.test
@@ -1380,3 +1380,37 @@ DROP VIEW v1;
DROP TABLE t1,t2;
set optimizer_search_depth= @tmp_mdev621;
+
+--echo #
+--echo # MDEV-19588: Nested left joins using optimized join cache
+--echo #
+
+set optimizer_switch='optimize_join_buffer_size=on';
+
+set @save_join_cache_level= @@join_cache_level;
+set join_cache_level=2;
+
+CREATE TABLE t1 (i1 int, c1 varchar(20), pk int) engine=myisam;
+
+CREATE TABLE t2 (pk int, c1 varchar(20), i1 int) engine=myisam;
+INSERT INTO t2 VALUES (7,'a',-912),(8,'a',5);
+
+CREATE TABLE t3 (pk int, c1 varchar(20), i1 int) engine=myisam;
+INSERT INTO t3 VALUES
+(1,'a',-145),(2,'a',6),(3,'a',1),(7,'a',NULL),(8,'a',889),(9,'a',146),
+(10,'a',177),(16,'a',-433),(17,'a',NULL),(18,'a',2),(19,'a',3),(20,'a',5),
+(21,'a',-484),(22,'a',369),(23,'a',-192),(24,'a',-163),(25,'a',5),(26,'a',NULL);
+
+let $q=
+SELECT t3.*
+FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1
+WHERE t2.pk < 13 OR t3.i1 IS NULL;
+
+eval $q;
+eval explain extended $q;
+
+DROP TABLE t1,t2,t3;
+
+set join_cache_level= @save_join_cache_level;
+
+set optimizer_switch=@save_optimizer_switch;
diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc
index 73ee201..909eeb8 100644
--- a/sql/sql_join_cache.cc
+++ b/sql/sql_join_cache.cc
@@ -2150,7 +2150,8 @@ enum_nested_loop_state JOIN_CACHE::join_records(bool skip_last)
}
finish:
- if (outer_join_first_inner)
+ if (outer_join_first_inner &&
+ join_tab->first_inner == join_tab->first_unmatched)
{
/*
All null complemented rows have been already generated for all
1
0
28 May '19
revision-id: 678cd7f4ffb86fdac88f85b270edc66c3beb37d0 (mariadb-10.4.4-114-g678cd7f4ffb)
parent(s): 54b81cf6cad395e1bc57c876566454b90a1228f3
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2019-05-28 13:31:08 +0200
message:
Improve inheritance of lex_keeper.
---
sql/sp_head.h | 56 +++++++++++++++++++++++++++++++-------------------------
sql/sql_class.h | 10 +++++-----
2 files changed, 36 insertions(+), 30 deletions(-)
diff --git a/sql/sp_head.h b/sql/sp_head.h
index 3365bf4883f..2b2ba732bb6 100644
--- a/sql/sp_head.h
+++ b/sql/sp_head.h
@@ -1242,11 +1242,28 @@ class sp_lex_keeper
TABLE_LIST **lex_query_tables_own_last;
};
+/*
+ Class for basic instructions using LEX (there are some other more complex
+ using LEX)
+*/
+
+class sp_lex_basic_instr : public sp_instr
+{
+ protected:
+
+ sp_lex_keeper m_lex_keeper;
+
+ public:
+
+ sp_lex_basic_instr(uint ip, sp_pcontext *ctx, LEX *lex, bool lex_owner)
+ : sp_instr(ip, ctx), m_lex_keeper(lex, lex_owner)
+ {}
+};
/**
Call out to some prepared SQL statement.
*/
-class sp_instr_stmt : public sp_instr
+class sp_instr_stmt : public sp_lex_basic_instr
{
sp_instr_stmt(const sp_instr_stmt &); /**< Prevent use of these */
void operator=(sp_instr_stmt &);
@@ -1256,7 +1273,7 @@ class sp_instr_stmt : public sp_instr
LEX_STRING m_query; ///< For thd->query
sp_instr_stmt(uint ip, sp_pcontext *ctx, LEX *lex)
- : sp_instr(ip, ctx), m_lex_keeper(lex, TRUE)
+ : sp_lex_basic_instr(ip, ctx, lex, TRUE)
{
m_query.str= 0;
m_query.length= 0;
@@ -1271,14 +1288,10 @@ class sp_instr_stmt : public sp_instr
virtual void print(String *str);
-private:
-
- sp_lex_keeper m_lex_keeper;
-
}; // class sp_instr_stmt : public sp_instr
-class sp_instr_set : public sp_instr
+class sp_instr_set : public sp_lex_basic_instr
{
sp_instr_set(const sp_instr_set &); /**< Prevent use of these */
void operator=(sp_instr_set &);
@@ -1289,9 +1302,8 @@ class sp_instr_set : public sp_instr
const Sp_rcontext_handler *rh,
uint offset, Item *val,
LEX *lex, bool lex_resp)
- : sp_instr(ip, ctx),
- m_rcontext_handler(rh), m_offset(offset), m_value(val),
- m_lex_keeper(lex, lex_resp)
+ : sp_lex_basic_instr(ip, ctx, lex, lex_resp),
+ m_rcontext_handler(rh), m_offset(offset), m_value(val)
{}
virtual ~sp_instr_set()
@@ -1308,7 +1320,6 @@ class sp_instr_set : public sp_instr
const Sp_rcontext_handler *m_rcontext_handler;
uint m_offset; ///< Frame offset
Item *m_value;
- sp_lex_keeper m_lex_keeper;
}; // class sp_instr_set : public sp_instr
@@ -1389,7 +1400,7 @@ class sp_instr_set_row_field_by_name : public sp_instr_set
/**
Set NEW/OLD row field value instruction. Used in triggers.
*/
-class sp_instr_set_trigger_field : public sp_instr
+class sp_instr_set_trigger_field : public sp_lex_basic_instr
{
sp_instr_set_trigger_field(const sp_instr_set_trigger_field &);
void operator=(sp_instr_set_trigger_field &);
@@ -1399,9 +1410,9 @@ class sp_instr_set_trigger_field : public sp_instr
sp_instr_set_trigger_field(uint ip, sp_pcontext *ctx,
Item_trigger_field *trg_fld,
Item *val, LEX *lex)
- : sp_instr(ip, ctx),
+ : sp_lex_basic_instr(ip, ctx, lex, TRUE),
trigger_field(trg_fld),
- value(val), m_lex_keeper(lex, TRUE)
+ value(val)
{}
virtual ~sp_instr_set_trigger_field()
@@ -1416,7 +1427,6 @@ class sp_instr_set_trigger_field : public sp_instr
private:
Item_trigger_field *trigger_field;
Item *value;
- sp_lex_keeper m_lex_keeper;
}; // class sp_instr_trigger_field : public sp_instr
@@ -1592,7 +1602,7 @@ class sp_instr_preturn : public sp_instr
}; // class sp_instr_preturn : public sp_instr
-class sp_instr_freturn : public sp_instr
+class sp_instr_freturn : public sp_lex_basic_instr
{
sp_instr_freturn(const sp_instr_freturn &); /**< Prevent use of these */
void operator=(sp_instr_freturn &);
@@ -1601,8 +1611,7 @@ class sp_instr_freturn : public sp_instr
sp_instr_freturn(uint ip, sp_pcontext *ctx,
Item *val, const Type_handler *handler, LEX *lex)
- : sp_instr(ip, ctx), m_value(val), m_type_handler(handler),
- m_lex_keeper(lex, TRUE)
+ : sp_lex_basic_instr(ip, ctx, lex, TRUE), m_value(val), m_type_handler(handler)
{}
virtual ~sp_instr_freturn()
@@ -1624,7 +1633,6 @@ class sp_instr_freturn : public sp_instr
Item *m_value;
const Type_handler *m_type_handler;
- sp_lex_keeper m_lex_keeper;
}; // class sp_instr_freturn : public sp_instr
@@ -1759,7 +1767,7 @@ class sp_instr_hreturn : public sp_instr_jump
/** This is DECLARE CURSOR */
-class sp_instr_cpush : public sp_instr,
+class sp_instr_cpush : public sp_lex_basic_instr,
public sp_cursor
{
sp_instr_cpush(const sp_instr_cpush &); /**< Prevent use of these */
@@ -1768,7 +1776,7 @@ class sp_instr_cpush : public sp_instr,
public:
sp_instr_cpush(uint ip, sp_pcontext *ctx, LEX *lex, uint offset)
- : sp_instr(ip, ctx), m_lex_keeper(lex, TRUE), m_cursor(offset)
+ : sp_lex_basic_instr(ip, ctx, lex, TRUE), m_cursor(offset)
{}
virtual ~sp_instr_cpush()
@@ -1786,7 +1794,6 @@ class sp_instr_cpush : public sp_instr,
virtual void cleanup_stmt() { /* no op */ }
private:
- sp_lex_keeper m_lex_keeper;
uint m_cursor; /**< Frame offset (for debugging) */
}; // class sp_instr_cpush : public sp_instr
@@ -1853,18 +1860,17 @@ class sp_instr_copen : public sp_instr
Initialize the structure of a cursor%ROWTYPE variable
from the LEX containing the cursor SELECT statement.
*/
-class sp_instr_cursor_copy_struct: public sp_instr
+class sp_instr_cursor_copy_struct: public sp_lex_basic_instr
{
/**< Prevent use of these */
sp_instr_cursor_copy_struct(const sp_instr_cursor_copy_struct &);
void operator=(sp_instr_cursor_copy_struct &);
- sp_lex_keeper m_lex_keeper;
uint m_cursor;
uint m_var;
public:
sp_instr_cursor_copy_struct(uint ip, sp_pcontext *ctx, uint coffs,
sp_lex_cursor *lex, uint voffs)
- : sp_instr(ip, ctx), m_lex_keeper(lex, FALSE),
+ : sp_lex_basic_instr(ip, ctx, lex, FALSE),
m_cursor(coffs),
m_var(voffs)
{}
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 9fc0f9f216c..499b3056f7e 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -5326,19 +5326,19 @@ class sp_cursor: public sp_cursor_statistics
public:
sp_cursor()
:result(NULL, false),
- m_lex_keeper(NULL),
+ m_lex_keeper_ptr(NULL),
server_side_cursor(NULL)
{ }
sp_cursor(THD *thd_arg, sp_lex_keeper *lex_keeper, bool view_structure_only)
:result(thd_arg, view_structure_only),
- m_lex_keeper(lex_keeper),
+ m_lex_keeper_ptr(lex_keeper),
server_side_cursor(NULL)
{}
virtual ~sp_cursor()
{ destroy(); }
- sp_lex_keeper *get_lex_keeper() { return m_lex_keeper; }
+ sp_lex_keeper *get_lex_keeper() { return m_lex_keeper_ptr; }
int open(THD *thd);
@@ -5355,13 +5355,13 @@ class sp_cursor: public sp_cursor_statistics
{
sp_cursor_statistics::reset();
result.reset(thd_arg);
- m_lex_keeper= lex_keeper;
+ m_lex_keeper_ptr= lex_keeper;
server_side_cursor= NULL;
}
private:
Select_fetch_into_spvars result;
- sp_lex_keeper *m_lex_keeper;
+ sp_lex_keeper *m_lex_keeper_ptr;
Server_side_cursor *server_side_cursor;
void destroy();
};
1
0
[Commits] 72abc68: MDEV-18479 Assertion `join->best_read < double(1.79769313486231570815e+308L)'
by IgorBabaev 28 May '19
by IgorBabaev 28 May '19
28 May '19
revision-id: 72abc68cf4d1c512f1d7befb4c30c0c07f3136cc (mariadb-5.5.64-7-g72abc68)
parent(s): 2c9844a438c5f0bddcb037a1e05978118f48abb6
author: Igor Babaev
committer: Igor Babaev
timestamp: 2019-05-27 19:08:00 -0700
message:
MDEV-18479 Assertion `join->best_read < double(1.79769313486231570815e+308L)'
or server crashes in JOIN::fix_all_splittings_in_plan after EXPLAIN
This patch resolves the problem of overflowing when performing
calculations to estimate the cost of an evaluated query execution plan.
The overflowing in a non-debug build could cause different kind of
problems uncluding crashes of the server.
---
mysql-test/r/derived_view.result | 401 +++++++++++++++++++++++++++++++++++++++
mysql-test/t/derived_view.test | 260 +++++++++++++++++++++++++
sql/opt_subselect.cc | 69 ++++---
sql/sql_const.h | 8 +
sql/sql_select.cc | 113 ++++++-----
5 files changed, 772 insertions(+), 79 deletions(-)
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 12811eb..ab36393 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -2640,3 +2640,404 @@ Note 1003 select straight_join `test`.`t1`.`c1` AS `c1` from `test`.`t1` where <
DROP TABLE t1, t2;
set optimizer_switch=@exit_optimizer_switch;
set join_cache_level=@exit_join_cache_level;
+#
+# Bug mdev-12812: EXPLAIN for query with many expensive derived
+#
+CREATE TABLE t1
+(id int auto_increment primary key,
+uid int NOT NULL,
+gp_id int NOT NULL,
+r int NOT NULL
+);
+INSERT INTO t1(uid,gp_id,r) VALUES
+(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),
+(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),
+(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),
+(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),
+(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),
+(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),
+(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),
+(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),
+(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),
+(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),
+(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),
+(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),
+(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),
+(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),
+(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),
+(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),
+(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),
+(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),
+(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),
+(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),
+(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),
+(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),
+(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),
+(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),
+(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),
+(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),
+(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),
+(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),
+(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),
+(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),
+(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),
+(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),
+(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),
+(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),
+(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),
+(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),
+(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),
+(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),
+(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),
+(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),
+(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),
+(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),
+(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),
+(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),
+(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),
+(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),
+(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),
+(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),
+(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),
+(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),
+(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),
+(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),
+(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),
+(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),
+(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),
+(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),
+(1,11,1);
+CREATE TABLE t2 (id int) ;
+INSERT INTO t2 VALUES (1);
+explain SELECT 1 FROM t2 JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+) gp_1 ON gp_1.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+) gp_2 ON gp_2.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p1 ON p1.r=1 AND p1.uid=t2.id
+JOIN t1 p3 ON p3.r=3 AND p3.uid=t2.id
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+) gp_3 ON gp_3.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p1 ON p1.r=1 AND p1.uid=t2.id
+JOIN t1 p3 ON p3.r=3 AND p3.uid=t2.id
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+) gp_4 ON gp_4.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p1 ON p1.r=1 AND p1.uid=t2.id
+JOIN t1 p3 ON p3.r=3 AND p3.uid=t2.id
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+) gp_5 ON gp_5.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p1 ON p1.r=1 AND p1.uid=t2.id
+JOIN t1 p3 ON p3.r=3 AND p3.uid=t2.id
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+) gp_6 ON gp_6.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p1 ON p1.r=1 AND p1.uid=t2.id
+JOIN t1 p3 ON p3.r=3 AND p3.uid=t2.id
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+WHERE p1.gp_id=7) gp_7 ON gp_7.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+) gp_8 ON gp_8.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+) gp_9 ON gp_9.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+) gp_14 ON gp_14.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+) gp_15 ON gp_15.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+) gp_16 ON gp_16.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+)gp_17 ON gp_17.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+)gp_18 ON gp_18.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+)gp_19 ON gp_19.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+) gp_20 ON gp_20.id=t2.id ;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 system NULL NULL NULL NULL 1
+1 SIMPLE t2 system NULL NULL NULL NULL 1
+1 SIMPLE t2 system NULL NULL NULL NULL 1
+1 SIMPLE t2 system NULL NULL NULL NULL 1
+1 SIMPLE t2 system NULL NULL NULL NULL 1
+1 SIMPLE t2 system NULL NULL NULL NULL 1
+1 SIMPLE p4 ALL NULL NULL NULL NULL 550 Using where
+1 SIMPLE p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE p6 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p7 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p8 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p9 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p4 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p6 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p7 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p8 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p9 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p1 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p3 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p4 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p6 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p7 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p8 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p9 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p1 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p3 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p4 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p6 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p7 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p8 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p9 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p1 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p3 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p4 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p6 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p7 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p8 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p9 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived17> ALL NULL NULL NULL NULL 50328437500000 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived14> ALL NULL NULL NULL NULL 27680640625000000 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived7> ALL NULL NULL NULL NULL 7798774269472204288 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived8> ALL NULL NULL NULL NULL 7798774269472204288 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived9> ALL NULL NULL NULL NULL -3222391729959551616 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived10> ALL NULL NULL NULL NULL -3222391729959551616 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived11> ALL NULL NULL NULL NULL -3222391729959551616 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived12> ALL NULL NULL NULL NULL -3222391729959551616 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived13> ALL NULL NULL NULL NULL -3222391729959551616 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived15> ALL NULL NULL NULL NULL -3222391729959551616 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE <derived16> ALL NULL NULL NULL NULL -3222391729959551616 Using where; Using join buffer (incremental, BNL join)
+17 DERIVED t2 system NULL NULL NULL NULL 1
+17 DERIVED p4 ALL NULL NULL NULL NULL 550 Using where
+17 DERIVED p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (flat, BNL join)
+17 DERIVED p6 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+17 DERIVED p7 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+17 DERIVED p8 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+16 DERIVED t2 system NULL NULL NULL NULL 1
+16 DERIVED p4 ALL NULL NULL NULL NULL 550 Using where
+16 DERIVED p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (flat, BNL join)
+16 DERIVED p6 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+16 DERIVED p7 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+16 DERIVED p8 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+16 DERIVED p9 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+16 DERIVED p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+15 DERIVED t2 system NULL NULL NULL NULL 1
+15 DERIVED p4 ALL NULL NULL NULL NULL 550 Using where
+15 DERIVED p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (flat, BNL join)
+15 DERIVED p6 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+15 DERIVED p7 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+15 DERIVED p8 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+15 DERIVED p9 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+15 DERIVED p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+14 DERIVED t2 system NULL NULL NULL NULL 1
+14 DERIVED p4 ALL NULL NULL NULL NULL 550 Using where
+14 DERIVED p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (flat, BNL join)
+14 DERIVED p7 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+14 DERIVED p8 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+14 DERIVED p9 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+14 DERIVED p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+13 DERIVED t2 system NULL NULL NULL NULL 1
+13 DERIVED p4 ALL NULL NULL NULL NULL 550 Using where
+13 DERIVED p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (flat, BNL join)
+13 DERIVED p6 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+13 DERIVED p7 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+13 DERIVED p8 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+13 DERIVED p9 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+13 DERIVED p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+12 DERIVED t2 system NULL NULL NULL NULL 1
+12 DERIVED p4 ALL NULL NULL NULL NULL 550 Using where
+12 DERIVED p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (flat, BNL join)
+12 DERIVED p6 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+12 DERIVED p7 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+12 DERIVED p8 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+12 DERIVED p9 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+12 DERIVED p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+11 DERIVED t2 system NULL NULL NULL NULL 1
+11 DERIVED p4 ALL NULL NULL NULL NULL 550 Using where
+11 DERIVED p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (flat, BNL join)
+11 DERIVED p6 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+11 DERIVED p7 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+11 DERIVED p8 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+11 DERIVED p9 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+11 DERIVED p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+10 DERIVED t2 system NULL NULL NULL NULL 1
+10 DERIVED p4 ALL NULL NULL NULL NULL 550 Using where
+10 DERIVED p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (flat, BNL join)
+10 DERIVED p6 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+10 DERIVED p7 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+10 DERIVED p8 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+10 DERIVED p9 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+10 DERIVED p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+9 DERIVED t2 system NULL NULL NULL NULL 1
+9 DERIVED p4 ALL NULL NULL NULL NULL 550 Using where
+9 DERIVED p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (flat, BNL join)
+9 DERIVED p6 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+9 DERIVED p7 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+9 DERIVED p8 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+9 DERIVED p9 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+9 DERIVED p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+8 DERIVED t2 system NULL NULL NULL NULL 1
+8 DERIVED p1 ALL NULL NULL NULL NULL 550 Using where
+8 DERIVED p3 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (flat, BNL join)
+8 DERIVED p4 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+8 DERIVED p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+8 DERIVED p6 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+8 DERIVED p7 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+8 DERIVED p8 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+8 DERIVED p9 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+8 DERIVED p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+7 DERIVED t2 system NULL NULL NULL NULL 1
+7 DERIVED p1 ALL NULL NULL NULL NULL 550 Using where
+7 DERIVED p3 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (flat, BNL join)
+7 DERIVED p4 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+7 DERIVED p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+7 DERIVED p6 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+7 DERIVED p7 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+7 DERIVED p8 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+7 DERIVED p9 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+7 DERIVED p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
+DROP TABLE t1, t2;
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index 07fbe49..f6613e2 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -1934,3 +1934,263 @@ DROP TABLE t1, t2;
# The following command must be the last one the file
set optimizer_switch=@exit_optimizer_switch;
set join_cache_level=@exit_join_cache_level;
+
+--echo #
+--echo # Bug mdev-12812: EXPLAIN for query with many expensive derived
+--echo #
+
+CREATE TABLE t1
+(id int auto_increment primary key,
+ uid int NOT NULL,
+ gp_id int NOT NULL,
+ r int NOT NULL
+);
+
+INSERT INTO t1(uid,gp_id,r) VALUES
+(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),
+(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),
+(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),
+(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),
+(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),
+(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),
+(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),
+(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),
+(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),
+(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),
+(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),
+(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),
+(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),
+(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),
+(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),
+(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),
+(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),
+(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),
+(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),
+(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),
+(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),
+(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),
+(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),
+(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),
+(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),
+(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),
+(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),
+(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),
+(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),
+(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),
+(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),
+(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),
+(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),
+(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),
+(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),
+(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),
+(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),
+(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),
+(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),
+(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),
+(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),
+(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),
+(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),
+(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),
+(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),
+(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),
+(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),
+(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),
+(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),
+(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),
+(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),
+(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),
+(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),
+(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),
+(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),
+(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),
+(1,11,1);
+
+CREATE TABLE t2 (id int) ;
+INSERT INTO t2 VALUES (1);
+
+explain SELECT 1 FROM t2 JOIN
+(SELECT t2.id
+ FROM t2
+ JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+ JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+ JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+ JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+ JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+ JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+ JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+ ) gp_1 ON gp_1.id=t2.id
+JOIN
+(SELECT t2.id
+ FROM t2
+ JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+ JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+ JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+ JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+ JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+ JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+ JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+ ) gp_2 ON gp_2.id=t2.id
+JOIN
+(SELECT t2.id
+ FROM t2
+ JOIN t1 p1 ON p1.r=1 AND p1.uid=t2.id
+ JOIN t1 p3 ON p3.r=3 AND p3.uid=t2.id
+ JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+ JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+ JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+ JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+ JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+ JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+ JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+ ) gp_3 ON gp_3.id=t2.id
+JOIN
+(SELECT t2.id
+ FROM t2
+ JOIN t1 p1 ON p1.r=1 AND p1.uid=t2.id
+ JOIN t1 p3 ON p3.r=3 AND p3.uid=t2.id
+ JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+ JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+ JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+ JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+ JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+ JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+ JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+ ) gp_4 ON gp_4.id=t2.id
+JOIN
+(SELECT t2.id
+ FROM t2
+ JOIN t1 p1 ON p1.r=1 AND p1.uid=t2.id
+ JOIN t1 p3 ON p3.r=3 AND p3.uid=t2.id
+ JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+ JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+ JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+ JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+ JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+ JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+ JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+ ) gp_5 ON gp_5.id=t2.id
+JOIN
+(SELECT t2.id
+ FROM t2
+ JOIN t1 p1 ON p1.r=1 AND p1.uid=t2.id
+ JOIN t1 p3 ON p3.r=3 AND p3.uid=t2.id
+ JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+ JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+ JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+ JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+ JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+ JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+ JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+ ) gp_6 ON gp_6.id=t2.id
+JOIN
+(SELECT t2.id
+ FROM t2
+ JOIN t1 p1 ON p1.r=1 AND p1.uid=t2.id
+ JOIN t1 p3 ON p3.r=3 AND p3.uid=t2.id
+ JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+ JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+ JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+ JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+ JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+ JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+ JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+ WHERE p1.gp_id=7) gp_7 ON gp_7.id=t2.id
+JOIN
+(SELECT t2.id
+ FROM t2
+ JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+ JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+ JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+ JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+ JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+ JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+ JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+ ) gp_8 ON gp_8.id=t2.id
+JOIN
+(SELECT t2.id
+ FROM t2
+ JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+ JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+ JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+ JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+ JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+ JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+ JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+ ) gp_9 ON gp_9.id=t2.id
+JOIN
+(SELECT t2.id
+ FROM t2
+ JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+ JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+ JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+ JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+ JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+ JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+ JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+ ) gp_14 ON gp_14.id=t2.id
+JOIN
+(SELECT t2.id
+ FROM t2
+ JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+ JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+ JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+ JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+ JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+ JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+ JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+ ) gp_15 ON gp_15.id=t2.id
+JOIN
+(SELECT t2.id
+ FROM t2
+ JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+ JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+ JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+ JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+ JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+ JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+ JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+ ) gp_16 ON gp_16.id=t2.id
+JOIN
+(SELECT t2.id
+ FROM t2
+ JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+ JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+
+ JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+ JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+ JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+ JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+ )gp_17 ON gp_17.id=t2.id
+JOIN
+(SELECT t2.id
+ FROM t2
+ JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+ JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+ JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+ JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+ JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+ JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+ JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+ )gp_18 ON gp_18.id=t2.id
+JOIN
+(SELECT t2.id
+ FROM t2
+ JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+ JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+ JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+ JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+ JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+ JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+ JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+ )gp_19 ON gp_19.id=t2.id
+JOIN
+(SELECT t2.id
+ FROM t2
+ JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+ JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+ JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+ JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+ JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+ ) gp_20 ON gp_20.id=t2.id ;
+
+DROP TABLE t1, t2;
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index a0e19af..cb82132 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -27,6 +27,7 @@
#endif
#include "sql_base.h"
+#include "sql_const.h"
#include "sql_select.h"
#include "filesort.h"
#include "opt_subselect.h"
@@ -1380,8 +1381,8 @@ void get_delayed_table_estimates(TABLE *table,
*startup_cost= item->jtbm_read_time;
/* Calculate cost of scanning the temptable */
- double data_size= item->jtbm_record_count *
- hash_sj_engine->tmp_table->s->reclength;
+ double data_size= COST_MULT(item->jtbm_record_count,
+ hash_sj_engine->tmp_table->s->reclength);
/* Do like in handler::read_time */
*scan_time= data_size/IO_SIZE + 2;
}
@@ -2451,7 +2452,8 @@ bool optimize_semijoin_nests(JOIN *join, table_map all_table_map)
int tableno;
double rows= 1.0;
while ((tableno = tm_it.next_bit()) != Table_map_iterator::BITMAP_END)
- rows *= join->map2table[tableno]->table->quick_condition_rows;
+ rows= COST_MULT(rows,
+ join->map2table[tableno]->table->quick_condition_rows);
sjm->rows= min(sjm->rows, rows);
}
memcpy(sjm->positions, join->best_positions + join->const_tables,
@@ -2564,7 +2566,7 @@ static uint get_tmp_table_rec_length(Item **p_items, uint elements)
static double
get_tmp_table_lookup_cost(THD *thd, double row_count, uint row_size)
{
- if (row_count * row_size > thd->variables.max_heap_table_size)
+ if (row_count > thd->variables.max_heap_table_size / (double) row_size)
return (double) DISK_TEMPTABLE_LOOKUP_COST;
else
return (double) HEAP_TEMPTABLE_LOOKUP_COST;
@@ -2970,8 +2972,11 @@ bool Sj_materialization_picker::check_qep(JOIN *join,
}
double mat_read_time= prefix_cost.total_cost();
- mat_read_time += mat_info->materialization_cost.total_cost() +
- prefix_rec_count * mat_info->lookup_cost.total_cost();
+ mat_read_time=
+ COST_ADD(mat_read_time,
+ COST_ADD(mat_info->materialization_cost.total_cost(),
+ COST_MULT(prefix_rec_count,
+ mat_info->lookup_cost.total_cost())));
/*
NOTE: When we pick to use SJM[-Scan] we don't memcpy its POSITION
@@ -3011,9 +3016,12 @@ bool Sj_materialization_picker::check_qep(JOIN *join,
}
/* Add materialization cost */
- prefix_cost += mat_info->materialization_cost.total_cost() +
- prefix_rec_count * mat_info->scan_cost.total_cost();
- prefix_rec_count *= mat_info->rows;
+ prefix_cost=
+ COST_ADD(prefix_cost,
+ COST_ADD(mat_info->materialization_cost.total_cost(),
+ COST_MULT(prefix_rec_count,
+ mat_info->scan_cost.total_cost())));
+ prefix_rec_count= COST_MULT(prefix_rec_count, mat_info->rows);
uint i;
table_map rem_tables= remaining_tables;
@@ -3027,8 +3035,8 @@ bool Sj_materialization_picker::check_qep(JOIN *join,
{
best_access_path(join, join->positions[i].table, rem_tables, i,
disable_jbuf, prefix_rec_count, &curpos, &dummy);
- prefix_rec_count *= curpos.records_read;
- prefix_cost += curpos.read_time;
+ prefix_rec_count= COST_MULT(prefix_rec_count, curpos.records_read);
+ prefix_cost= COST_ADD(prefix_cost, curpos.read_time);
}
*strategy= SJ_OPT_MATERIALIZE_SCAN;
@@ -3335,16 +3343,18 @@ bool Duplicate_weedout_picker::check_qep(JOIN *join,
for (uint j= first_dupsweedout_table; j <= idx; j++)
{
POSITION *p= join->positions + j;
- current_fanout *= p->records_read;
- dups_cost += p->read_time + current_fanout / TIME_FOR_COMPARE;
+ current_fanout= COST_MULT(current_fanout, p->records_read);
+ dups_cost= COST_ADD(dups_cost,
+ COST_ADD(p->read_time,
+ current_fanout / TIME_FOR_COMPARE));
if (p->table->emb_sj_nest)
{
- sj_inner_fanout *= p->records_read;
+ sj_inner_fanout= COST_MULT(sj_inner_fanout, p->records_read);
dups_removed_fanout |= p->table->table->map;
}
else
{
- sj_outer_fanout *= p->records_read;
+ sj_outer_fanout= COST_MULT(sj_outer_fanout, p->records_read);
temptable_rec_size += p->table->table->file->ref_length;
}
}
@@ -3363,12 +3373,13 @@ bool Duplicate_weedout_picker::check_qep(JOIN *join,
sj_outer_fanout,
temptable_rec_size);
- double write_cost= join->positions[first_tab].prefix_record_count*
- sj_outer_fanout * one_write_cost;
- double full_lookup_cost= join->positions[first_tab].prefix_record_count*
- sj_outer_fanout* sj_inner_fanout *
- one_lookup_cost;
- dups_cost += write_cost + full_lookup_cost;
+ double write_cost= COST_MULT(join->positions[first_tab].prefix_record_count,
+ sj_outer_fanout * one_write_cost);
+ double full_lookup_cost=
+ COST_MULT(join->positions[first_tab].prefix_record_count,
+ COST_MULT(sj_outer_fanout,
+ sj_inner_fanout * one_lookup_cost));
+ dups_cost= COST_ADD(dups_cost, COST_ADD(write_cost, full_lookup_cost));
*read_time= dups_cost;
*record_count= prefix_rec_count * sj_outer_fanout;
@@ -3515,8 +3526,8 @@ static void recalculate_prefix_record_count(JOIN *join, uint start, uint end)
if (j == join->const_tables)
prefix_count= 1.0;
else
- prefix_count= join->best_positions[j-1].prefix_record_count *
- join->best_positions[j-1].records_read;
+ prefix_count= COST_MULT(join->best_positions[j-1].prefix_record_count,
+ join->best_positions[j-1].records_read);
join->best_positions[j].prefix_record_count= prefix_count;
}
@@ -5807,14 +5818,16 @@ bool JOIN::choose_subquery_plan(table_map join_tables)
The cost of executing the subquery and storing its result in an indexed
temporary table.
*/
- double materialization_cost= inner_read_time_1 +
- write_cost * inner_record_count_1;
+ double materialization_cost= COST_ADD(inner_read_time_1,
+ COST_MULT(write_cost,
+ inner_record_count_1));
- materialize_strategy_cost= materialization_cost +
- outer_lookup_keys * lookup_cost;
+ materialize_strategy_cost= COST_ADD(materialization_cost,
+ COST_MULT(outer_lookup_keys,
+ lookup_cost));
/* C.2 Compute the cost of the IN=>EXISTS strategy. */
- in_exists_strategy_cost= outer_lookup_keys * inner_read_time_2;
+ in_exists_strategy_cost= COST_MULT(outer_lookup_keys, inner_read_time_2);
/* C.3 Compare the costs and choose the cheaper strategy. */
if (materialize_strategy_cost >= in_exists_strategy_cost)
diff --git a/sql/sql_const.h b/sql/sql_const.h
index 96b6448..c3b03dd 100644
--- a/sql/sql_const.h
+++ b/sql/sql_const.h
@@ -214,6 +214,14 @@
#define HEAP_TEMPTABLE_LOOKUP_COST 0.05
#define DISK_TEMPTABLE_LOOKUP_COST 1.0
+
+#define COST_MAX (DBL_MAX * (1.0 - DBL_EPSILON))
+
+#define COST_ADD(c,d) (COST_MAX - (d) > (c) ? (c) + (d) : COST_MAX)
+
+#define COST_MULT(c,f) (COST_MAX / (f) > (c) ? (c) * (f) : COST_MAX)
+
+
#define MY_CHARSET_BIN_MB_MAXLEN 1
/** Don't pack string keys shorter than this (if PACK_KEYS=1 isn't used). */
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 4886e35..961a7da 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -5676,7 +5676,7 @@ best_access_path(JOIN *join,
else
tmp= table->file->read_time(key, 1,
(ha_rows) min(tmp,s->worst_seeks));
- tmp*= record_count;
+ tmp= COST_MULT(tmp, record_count);
}
}
else
@@ -5841,18 +5841,18 @@ best_access_path(JOIN *join,
else
tmp= table->file->read_time(key, 1,
(ha_rows) min(tmp,s->worst_seeks));
- tmp*= record_count;
+ tmp= COST_MULT(tmp, record_count);
}
else
- tmp= best_time; // Do nothing
+ tmp= best_time; // Do nothing
}
- tmp += s->startup_cost;
+ tmp= COST_ADD(tmp, s->startup_cost);
loose_scan_opt.check_ref_access_part2(key, start_key, records, tmp);
} /* not ft_key */
if (tmp + 0.0001 < best_time - records/(double) TIME_FOR_COMPARE)
{
- best_time= tmp + records/(double) TIME_FOR_COMPARE;
+ best_time= COST_ADD(tmp, records/(double) TIME_FOR_COMPARE);
best= tmp;
best_records= records;
best_key= start_key;
@@ -5885,14 +5885,18 @@ best_access_path(JOIN *join,
ha_rows rnd_records= matching_candidates_in_table(s, found_constraint);
tmp= s->quick ? s->quick->read_time : s->scan_time();
- tmp+= (s->records - rnd_records)/(double) TIME_FOR_COMPARE;
+ double cmp_time= (s->records - rnd_records)/(double) TIME_FOR_COMPARE;
+ tmp= COST_ADD(tmp, cmp_time);
/* We read the table as many times as join buffer becomes full. */
- tmp*= (1.0 + floor((double) cache_record_length(join,idx) *
- record_count /
- (double) thd->variables.join_buff_size));
- best_time= tmp +
- (record_count*join_sel) / TIME_FOR_COMPARE * rnd_records;
+
+ double refills= (1.0 + floor((double) cache_record_length(join,idx) *
+ record_count /
+ (double) thd->variables.join_buff_size));
+ tmp= COST_MULT(tmp, refills);
+ best_time= COST_ADD(tmp,
+ COST_MULT((record_count*join_sel) / TIME_FOR_COMPARE,
+ rnd_records));
best= tmp;
records= rows2double(rnd_records);
best_key= hj_start_key;
@@ -5960,9 +5964,9 @@ best_access_path(JOIN *join,
access (see first else-branch below), but we don't take it into
account here for range/index_merge access. Find out why this is so.
*/
- tmp= record_count *
- (s->quick->read_time +
- (s->found_records - rnd_records)/(double) TIME_FOR_COMPARE);
+ double cmp_time= (s->found_records - rnd_records)/(double) TIME_FOR_COMPARE;
+ tmp= COST_MULT(record_count,
+ COST_ADD(s->quick->read_time, cmp_time));
loose_scan_opt.check_range_access(join, idx, s->quick);
}
@@ -5977,16 +5981,15 @@ best_access_path(JOIN *join,
- read the whole table record
- skip rows which does not satisfy join condition
*/
- tmp= record_count *
- (tmp +
- (s->records - rnd_records)/(double) TIME_FOR_COMPARE);
+ double cmp_time= (s->records - rnd_records)/(double) TIME_FOR_COMPARE;
+ tmp= COST_MULT(record_count, COST_ADD(tmp,cmp_time));
}
else
{
- /* We read the table as many times as join buffer becomes full. */
- tmp*= (1.0 + floor((double) cache_record_length(join,idx) *
- record_count /
- (double) thd->variables.join_buff_size));
+ double refills= (1.0 + floor((double) cache_record_length(join,idx) *
+ (record_count /
+ (double) thd->variables.join_buff_size)));
+ tmp= COST_MULT(tmp, refills);
/*
We don't make full cartesian product between rows in the scanned
table and existing records because we skip all rows from the
@@ -5994,7 +5997,8 @@ best_access_path(JOIN *join,
we read the table (see flush_cached_records for details). Here we
take into account cost to read and skip these records.
*/
- tmp+= (s->records - rnd_records)/(double) TIME_FOR_COMPARE;
+ double cmp_time= (s->records - rnd_records)/(double) TIME_FOR_COMPARE;
+ tmp= COST_ADD(tmp, cmp_time);
}
}
@@ -6005,9 +6009,9 @@ best_access_path(JOIN *join,
tmp give us total cost of using TABLE SCAN
*/
if (best == DBL_MAX ||
- (tmp + record_count/(double) TIME_FOR_COMPARE*rnd_records <
+ COST_ADD(tmp, record_count/(double) TIME_FOR_COMPARE*rnd_records) <
(best_key->is_for_hash_join() ? best_time :
- best + record_count/(double) TIME_FOR_COMPARE*records)))
+ COST_ADD(best, record_count/(double) TIME_FOR_COMPARE*records)))
{
/*
If the table has a range (s->quick is set) make_join_select()
@@ -6526,9 +6530,10 @@ optimize_straight_join(JOIN *join, table_map join_tables)
join->positions + idx, &loose_scan_pos);
/* compute the cost of the new plan extended with 's' */
- record_count*= join->positions[idx].records_read;
- read_time+= join->positions[idx].read_time +
- record_count / (double) TIME_FOR_COMPARE;
+ record_count= COST_MULT(record_count, join->positions[idx].records_read);
+ read_time= COST_ADD(read_time,
+ COST_ADD(join->positions[idx].read_time,
+ record_count / (double) TIME_FOR_COMPARE));
advance_sj_state(join, join_tables, idx, &record_count, &read_time,
&loose_scan_pos);
@@ -6710,9 +6715,10 @@ greedy_search(JOIN *join,
swap_variables(JOIN_TAB*, join->best_ref[idx], join->best_ref[best_idx]);
/* compute the cost of the new plan extended with 'best_table' */
- record_count*= join->positions[idx].records_read;
- read_time+= join->positions[idx].read_time +
- record_count / (double) TIME_FOR_COMPARE;
+ record_count= COST_MULT(record_count, join->positions[idx].records_read);
+ read_time= COST_ADD(read_time,
+ COST_ADD(join->positions[idx].read_time,
+ record_count / (double) TIME_FOR_COMPARE));
remaining_tables&= ~(best_table->table->map);
--size_remain;
@@ -6819,11 +6825,13 @@ void JOIN::get_partial_cost_and_fanout(int end_tab_idx,
}
if (tab->records_read && (cur_table_map & filter_map))
{
- record_count *= tab->records_read;
- read_time += tab->read_time + record_count / (double) TIME_FOR_COMPARE;
+ record_count= COST_MULT(record_count, tab->records_read);
+ read_time= COST_ADD(read_time,
+ COST_ADD(tab->read_time,
+ record_count / (double) TIME_FOR_COMPARE));
if (tab->emb_sj_nest)
- sj_inner_fanout *= tab->records_read;
- }
+ sj_inner_fanout= COST_MULT(sj_inner_fanout, tab->records_read);
+ }
if (i == last_sj_table)
{
@@ -6861,8 +6869,8 @@ void JOIN::get_prefix_cost_and_fanout(uint n_tables,
{
if (best_positions[i].records_read)
{
- record_count *= best_positions[i].records_read;
- read_time += best_positions[i].read_time;
+ record_count= COST_MULT(record_count, best_positions[i].records_read);
+ read_time= COST_ADD(read_time, best_positions[i].read_time);
}
}
*read_time_arg= read_time;// + record_count / TIME_FOR_COMPARE;
@@ -7068,10 +7076,11 @@ best_extension_by_limited_search(JOIN *join,
record_count, join->positions + idx, &loose_scan_pos);
/* Compute the cost of extending the plan with 's' */
-
- current_record_count= record_count * position->records_read;
- current_read_time=read_time + position->read_time +
- current_record_count / (double) TIME_FOR_COMPARE;
+ current_record_count= COST_MULT(record_count, position->records_read);
+ current_read_time=COST_ADD(read_time,
+ COST_ADD(position->read_time,
+ current_record_count /
+ (double) TIME_FOR_COMPARE));
advance_sj_state(join, remaining_tables, idx, ¤t_record_count,
¤t_read_time, &loose_scan_pos);
@@ -7145,7 +7154,7 @@ best_extension_by_limited_search(JOIN *join,
join->sort_by_table !=
join->positions[join->const_tables].table->table)
/* We have to make a temp table */
- current_read_time+= current_record_count;
+ current_read_time= COST_ADD(current_read_time, current_record_count);
if (current_read_time < join->best_read)
{
memcpy((uchar*) join->best_positions, (uchar*) join->positions,
@@ -7189,11 +7198,11 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
DBUG_PRINT("best",("read_time: %g record_count: %g",read_time,
record_count));
- read_time+=record_count/(double) TIME_FOR_COMPARE;
+ read_time= COST_ADD(read_time, record_count/(double) TIME_FOR_COMPARE);
if (join->sort_by_table &&
join->sort_by_table !=
join->positions[join->const_tables].table->table)
- read_time+=record_count; // We have to make a temp table
+ read_time= COST_ADD(read_time, record_count); // We have to make a temp table
if (read_time < join->best_read)
{
memcpy((uchar*) join->best_positions,(uchar*) join->positions,
@@ -7202,7 +7211,8 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
}
DBUG_RETURN(FALSE);
}
- if (read_time+record_count/(double) TIME_FOR_COMPARE >= join->best_read)
+ if (COST_ADD(read_time, record_count/(double) TIME_FOR_COMPARE)
+ >= join->best_read)
DBUG_RETURN(FALSE); /* Found better before */
JOIN_TAB *s;
@@ -7224,8 +7234,8 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
Go to the next level only if there hasn't been a better key on
this level! This will cut down the search for a lot simple cases!
*/
- double current_record_count=record_count*records;
- double current_read_time=read_time+best;
+ double current_record_count= COST_MULT(record_count, records);
+ double current_read_time= COST_ADD(read_time, best);
advance_sj_state(join, rest_tables, idx, ¤t_record_count,
¤t_read_time, &loose_scan_pos);
@@ -7552,8 +7562,8 @@ prev_record_reads(POSITION *positions, uint idx, table_map found_ref)
#max_nested_outer_joins=64-1) will not make it any more precise.
*/
if (pos->records_read)
- found*= pos->records_read;
- }
+ found= COST_MULT(found, pos->records_read);
+ }
}
return found;
}
@@ -13835,11 +13845,12 @@ void optimize_wo_join_buffering(JOIN *join, uint first_tab, uint last_tab,
pos= loose_scan_pos;
reopt_remaining_tables &= ~rs->table->map;
- rec_count *= pos.records_read;
- cost += pos.read_time;
+ rec_count= COST_MULT(rec_count, pos.records_read);
+ cost= COST_ADD(cost, pos.read_time);
+
if (!rs->emb_sj_nest)
- *outer_rec_count *= pos.records_read;
+ *outer_rec_count= COST_MULT(*outer_rec_count, pos.records_read);
}
join->cur_sj_inner_tables= save_cur_sj_inner_tables;
2
1