From 2c9844a438c5f0bddcb037a1e05978118f48abb6 Mon Sep 17 00:00:00 2001
From: Igor Babaev <igor@askmonty.org>
Date: Sun, 19 May 2019 11:44:34 -0700
Subject: MDEV-18896 Crash in convert_join_subqueries_to_semijoins : Correction

This patch complements the original patch for MDEV-18896 that prevents
conversions to semi-joins in tableless selects used in INSERT statements
in post-5.5 versions of the server.
The test case was corrected as well to ensure that potential conversion
to jtbm semi-joins is also checked (the problem was that one of
the preceeding testcases in subselect_sj.test did not restore the
state of the optimizer switch leaving the 'materialization' in the state
'off' and so blocking this check).
Noticed an inconsistency in the state of select_lex::table_list used
in INSERT statements and left a comment about this.
---
 mysql-test/r/subselect_sj.result      | 1 +
 mysql-test/r/subselect_sj_jcl6.result | 1 +
 mysql-test/t/subselect_sj.test        | 2 ++
 sql/opt_subselect.cc                  | 2 +-
 sql/sql_parse.cc                      | 7 +++++++
 5 files changed, 12 insertions(+), 1 deletion(-)

diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index 73c620b2973..d0b8b626ba2 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -3181,6 +3181,7 @@ drop table t1,t2,t3;
 #
 # MDEV-18896: IN subquery in WHERE of a table-less query used for INSERT
 #
+set @@optimizer_switch= @subselect_sj_tmp;
 create table t1 (a1 varchar(25));
 create table t2 (a2 varchar(25)) ;
 insert into t1 select 'xxx' from dual where 'xxx' in (select a2 from t2);
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index 03cf368f32c..e9a8b7301f7 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -3195,6 +3195,7 @@ drop table t1,t2,t3;
 #
 # MDEV-18896: IN subquery in WHERE of a table-less query used for INSERT
 #
+set @@optimizer_switch= @subselect_sj_tmp;
 create table t1 (a1 varchar(25));
 create table t2 (a2 varchar(25)) ;
 insert into t1 select 'xxx' from dual where 'xxx' in (select a2 from t2);
diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
index aabb21b4245..6b59049bc4f 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -2873,6 +2873,8 @@ drop table t1,t2,t3;
 --echo # MDEV-18896: IN subquery in WHERE of a table-less query used for INSERT
 --echo #
 
+set @@optimizer_switch= @subselect_sj_tmp;
+
 create table t1 (a1 varchar(25));
 create table t2 (a2 varchar(25)) ;
 insert into t1 select 'xxx' from dual where 'xxx' in (select a2 from t2);
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index fee68d33c33..a0e19af3d4e 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -525,7 +525,7 @@ bool is_materialization_applicable(THD *thd, Item_in_subselect *in_subs,
         parent_unit->first_select()->leaf_tables.elements &&          // 2
         (thd->lex->sql_command == SQLCOM_SELECT ||                     // *
          thd->lex->sql_command == SQLCOM_CREATE_TABLE) &&              // *
-        child_select->outer_select()->leaf_tables.elements &&           // 2A
+        child_select->outer_select()->table_list.first &&            // 2A
         subquery_types_allow_materialization(in_subs) &&
         (in_subs->is_top_level_item() ||                               //3
          optimizer_flag(thd,
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 4ed2bcaed38..1d5733a646b 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -3034,6 +3034,13 @@ end_with_restore_list:
       */
       /* Skip first table, which is the table we are inserting in */
       TABLE_LIST *second_table= first_table->next_local;
+      /*
+        This is a hack: this leaves select_lex->table_list in an inconsistent
+        state as 'elements' does not contain number of elements in the list.
+        Moreover, if second_table == NULL then 'next' becomes invalid.
+        TODO: fix it by removing the front element (restoring of it should
+        be done properly as well)
+      */
       select_lex->table_list.first= second_table;
       select_lex->context.table_list= 
         select_lex->context.first_name_resolution_table= second_table;
-- 
cgit v1.2.1


From 7b59ec6f345986db0cbc6f8b1d504a6e7e21cb5f Mon Sep 17 00:00:00 2001
From: Eugene Kosov <claprix@yandex.ru>
Date: Wed, 28 Nov 2018 13:25:43 +0300
Subject: MDEV-17799 Add ASAN-poisoned redzones for MEM_ROOT and mem_heap_t

This patch is for MEM_ROOT only.
In debug mode add 8 byte of poisoned memory before every allocated chunk.
On the right of every chunk there will be either 1-7 trailing poisoned bytes, or
next chunk's redzone, or poisoned non allocated memory or redzone of a
malloc()ed buffer.
---
 include/my_valgrind.h | 7 +++++++
 mysys/my_alloc.c      | 3 ++-
 2 files changed, 9 insertions(+), 1 deletion(-)

diff --git a/include/my_valgrind.h b/include/my_valgrind.h
index 6df8f5a772e..14db2af2cb3 100644
--- a/include/my_valgrind.h
+++ b/include/my_valgrind.h
@@ -13,6 +13,9 @@
    along with this program; if not, write to the Free Software
    Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA */
 
+#ifndef _my_valgrind_h
+#define _my_valgrind_h
+
 /* clang -> gcc */
 #ifndef __has_feature
 # define __has_feature(x) 0
@@ -49,9 +52,13 @@ https://github.com/google/sanitizers/wiki/AddressSanitizerManualPoisoning */
 #endif /* HAVE_VALGRIND */
 
 #ifndef DBUG_OFF
+static const size_t REDZONE_SIZE= 8;
 #define TRASH_FILL(A,B,C) do { const size_t trash_tmp= (B); MEM_UNDEFINED(A, trash_tmp); memset(A, C, trash_tmp); } while (0)
 #else
+static const size_t REDZONE_SIZE= 0;
 #define TRASH_FILL(A,B,C) do { MEM_UNDEFINED((A), (B)); } while (0)
 #endif
 #define TRASH_ALLOC(A,B) do { TRASH_FILL(A,B,0xA5); MEM_UNDEFINED(A,B); } while(0)
 #define TRASH_FREE(A,B) do { TRASH_FILL(A,B,0x8F); MEM_NOACCESS(A,B); } while(0)
+
+#endif /* _my_valgrind_h */
diff --git a/mysys/my_alloc.c b/mysys/my_alloc.c
index 15b96e8a20a..9e5b6809dea 100644
--- a/mysys/my_alloc.c
+++ b/mysys/my_alloc.c
@@ -197,7 +197,7 @@ void *alloc_root(MEM_ROOT *mem_root, size_t length)
                     DBUG_SET("-d,simulate_out_of_memory");
                     DBUG_RETURN((void*) 0); /* purecov: inspected */
                   });
-  length= ALIGN_SIZE(length);
+  length= ALIGN_SIZE(length) + REDZONE_SIZE;
   if ((*(prev= &mem_root->free)) != NULL)
   {
     if ((*prev)->left < length &&
@@ -242,6 +242,7 @@ void *alloc_root(MEM_ROOT *mem_root, size_t length)
     mem_root->used= next;
     mem_root->first_block_usage= 0;
   }
+  point+= REDZONE_SIZE;
   TRASH_ALLOC(point, original_length);
   DBUG_PRINT("exit",("ptr: 0x%lx", (ulong) point));
   DBUG_RETURN((void*) point);
-- 
cgit v1.2.1


From 5034b31b01b790ce1c28159402d754f7f951edc5 Mon Sep 17 00:00:00 2001
From: Sergei Golubchik <serg@mariadb.org>
Date: Mon, 20 May 2019 18:23:10 +0200
Subject: MDEV-17799 Add ASAN-poisoned redzones for MEM_ROOT

post-merge changes

Closes #954
---
 include/my_valgrind.h | 11 ++++++-----
 1 file changed, 6 insertions(+), 5 deletions(-)

diff --git a/include/my_valgrind.h b/include/my_valgrind.h
index 14db2af2cb3..ad22f0cad40 100644
--- a/include/my_valgrind.h
+++ b/include/my_valgrind.h
@@ -13,8 +13,8 @@
    along with this program; if not, write to the Free Software
    Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA */
 
-#ifndef _my_valgrind_h
-#define _my_valgrind_h
+#ifndef MY_VALGRIND_INCLUDED
+#define MY_VALGRIND_INCLUDED
 
 /* clang -> gcc */
 #ifndef __has_feature
@@ -36,6 +36,7 @@
 # define MEM_NOACCESS(a,len) VALGRIND_MAKE_MEM_NOACCESS(a,len)
 # define MEM_CHECK_ADDRESSABLE(a,len) VALGRIND_CHECK_MEM_IS_ADDRESSABLE(a,len)
 # define MEM_CHECK_DEFINED(a,len) VALGRIND_CHECK_MEM_IS_DEFINED(a,len)
+# define REDZONE_SIZE 8
 #elif defined(__SANITIZE_ADDRESS__)
 # include <sanitizer/asan_interface.h>
 /* How to do manual poisoning:
@@ -44,21 +45,21 @@ https://github.com/google/sanitizers/wiki/AddressSanitizerManualPoisoning */
 # define MEM_NOACCESS(a,len) ASAN_POISON_MEMORY_REGION(a,len)
 # define MEM_CHECK_ADDRESSABLE(a,len) ((void) 0)
 # define MEM_CHECK_DEFINED(a,len) ((void) 0)
+# define REDZONE_SIZE 8
 #else
 # define MEM_UNDEFINED(a,len) ((void) (a), (void) (len))
 # define MEM_NOACCESS(a,len) ((void) 0)
 # define MEM_CHECK_ADDRESSABLE(a,len) ((void) 0)
 # define MEM_CHECK_DEFINED(a,len) ((void) 0)
+# define REDZONE_SIZE 0
 #endif /* HAVE_VALGRIND */
 
 #ifndef DBUG_OFF
-static const size_t REDZONE_SIZE= 8;
 #define TRASH_FILL(A,B,C) do { const size_t trash_tmp= (B); MEM_UNDEFINED(A, trash_tmp); memset(A, C, trash_tmp); } while (0)
 #else
-static const size_t REDZONE_SIZE= 0;
 #define TRASH_FILL(A,B,C) do { MEM_UNDEFINED((A), (B)); } while (0)
 #endif
 #define TRASH_ALLOC(A,B) do { TRASH_FILL(A,B,0xA5); MEM_UNDEFINED(A,B); } while(0)
 #define TRASH_FREE(A,B) do { TRASH_FILL(A,B,0x8F); MEM_NOACCESS(A,B); } while(0)
 
-#endif /* _my_valgrind_h */
+#endif /* MY_VALGRIND_INCLUDED */
-- 
cgit v1.2.1


From 4584c186312a6e9b16271c5d863c76f939d4df53 Mon Sep 17 00:00:00 2001
From: Sergei Golubchik <serg@mariadb.org>
Date: Wed, 22 May 2019 00:52:15 +0200
Subject: make CPACK_RPM_DEBUGINFO_PACKAGE configurable from the command-line

---
 cmake/cpack_rpm.cmake | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/cmake/cpack_rpm.cmake b/cmake/cpack_rpm.cmake
index 4e9410306a8..10afa56aec1 100644
--- a/cmake/cpack_rpm.cmake
+++ b/cmake/cpack_rpm.cmake
@@ -32,7 +32,7 @@ IF(CMAKE_VERSION VERSION_LESS "3.6.0")
   SET(CPACK_PACKAGE_FILE_NAME "${CPACK_RPM_PACKAGE_NAME}-${VERSION}-${RPM}-${CMAKE_SYSTEM_PROCESSOR}")
 ELSE()
   SET(CPACK_RPM_FILE_NAME "RPM-DEFAULT")
-  SET(CPACK_RPM_DEBUGINFO_PACKAGE ON)
+  SET(CPACK_RPM_DEBUGINFO_PACKAGE ON CACHE INTERNAL "")
 ENDIF()
 
 SET(CPACK_RPM_PACKAGE_RELEASE "1%{?dist}")
-- 
cgit v1.2.1


From 0955462d0aafab01def9c1a5ec131eb641cb9e68 Mon Sep 17 00:00:00 2001
From: Igor Babaev <igor@askmonty.org>
Date: Mon, 27 May 2019 19:08:00 -0700
Subject: 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 12811ebc6b3..ab363934e8f 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 07fbe4980a5..f6613e2593a 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 a0e19af3d4e..cb821325e57 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 96b64481936..c3b03dd7848 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 4886e35c82e..961a7dac265 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, &current_record_count,
                        &current_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, &current_record_count, 
                        &current_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;
 
-- 
cgit v1.2.1