MDEV-16889: Spider Crash mysqld got exception 0xc0000005bb-10.3-MDEV-16889

The SELECT with the INNER JOIN is executed with one of the two tables being
optimized as a constant table, which is pre-read.  Spider nevertheless attempts
to push down the join to the data node.  The crash occurs because the constant
table is excluded from the optimized query that Spider attempts to push down.

In order for Spider to be able to push down a join, the following conditions
need to be met:
- All of the tables involved in the join need to be included in the optimized
  query that Spider pushes down.  When any of the tables involved in the join
  is a constant table, it is excluded from the optimized query that Spider
  attempts to push down.
- All fields involved in the query need to be members of tables included in the
  optimized query.

I fixed the problem by preventing Spider from pushing down queries that include
a field that is not a member of a table included in the optimized query.  This
solution fixes the reported problem and also fixes other potential problems.

Author:
  Jacob Mathew.

Reviewer:
  Kentoku Shiba.

5 files changed, 315 insertions, 0 deletions

diff --git a/storage/spider/mysql-test/spider/include/direct_join_init.inc b/storage/spider/mysql-test/spider/include/direct_join_init.inc
index 7e4947bf078..25c5e7ca39b 100644
--- a/storage/spider/mysql-test/spider/include/direct_join_init.inc
+++ b/storage/spider/mysql-test/spider/include/direct_join_init.inc
@@ -5,6 +5,46 @@
 --enable_result_log
 --enable_query_log
 --enable_warnings
+let $MASTER_1_COMMENT_CONST_TABLE_JOIN=
+  COMMENT 'table "tbl_person"';
+let $MASTER_1_COMMENT_CONST_TABLE_JOIN=
+  COMMENT 'table "tbl_ncd_cm_person"';
+let $MASTER_1_ROW_FORMAT_CONST_TABLE_JOIN=
+  ROW_FORMAT = Dynamic;
+let $MASTER_1_PART_CONST_TABLE_JOIN=
+  PARTITION BY LIST COLUMNS (region) PARTITIONS 1
+  (PARTITION pt1 values in (510411)
+   COMMENT = 'tbl "tbl_person", srv "s_2_1"' MAX_ROWS = 0 MIN_ROWS = 0);
+let $MASTER_1_PART_CONST_TABLE2_JOIN=
+  PARTITION BY LIST COLUMNS (region) PARTITIONS 1
+  (PARTITION pt1 values in (510411)
+   COMMENT = 'tbl "tbl_ncd_cm_person", srv "s_2_1"' MAX_ROWS = 0 MIN_ROWS = 0);
+let $CHILD2_1_ROW_FORMAT_CONST_TABLE_JOIN=
+  ROW_FORMAT = Dynamic;
+let $CHILD2_1_DROP_CONST_TABLE_JOIN=
+  DROP TABLE IF EXISTS tbl_person;
+let $CHILD2_1_CREATE_CONST_TABLE_JOIN=
+  CREATE TABLE tbl_person (
+    id VARCHAR(50) NOT NULL,
+    hr_status VARCHAR(50) NULL DEFAULT NULL,
+    region_code VARCHAR(50) NULL DEFAULT NULL,
+    region INT(11) NOT NULL,
+    PRIMARY KEY (id, region) USING BTREE
+  ) $CHILD2_1_ENGINE $CHILD2_1_CHARSET $CHILD2_1_ROW_FORMAT_CONST_TABLE_JOIN;
+let $CHILD2_1_DROP_CONST_TABLE2_JOIN=
+  DROP TABLE IF EXISTS tbl_ncd_cm_person;
+let $CHILD2_1_CREATE_CONST_TABLE2_JOIN=
+  CREATE TABLE tbl_ncd_cm_person (
+    id VARCHAR(50) NOT NULL,
+    person_id VARCHAR(50) NULL DEFAULT '',
+    diseaseKind_id VARCHAR(50) NULL DEFAULT NULL,
+    region INT(11) NOT NULL,
+    PRIMARY KEY (id, region) USING BTREE
+  ) $CHILD2_1_ENGINE $CHILD2_1_CHARSET $CHILD2_1_ROW_FORMAT_CONST_TABLE_JOIN;
+let $CHILD2_1_SELECT_CONST_TABLE_JOIN=
+  SELECT * FROM tbl_person;
+let $CHILD2_1_SELECT_CONST_TABLE2_JOIN=
+  SELECT * FROM tbl_ncd_cm_person;
 let $CHILD2_1_SELECT_ARGUMENT1=
   SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
 --let $OUTPUT_CHILD_GROUP2_BACKUP= $OUTPUT_CHILD_GROUP2
diff --git a/storage/spider/mysql-test/spider/r/direct_join.result b/storage/spider/mysql-test/spider/r/direct_join.result
index 5cc44a56910..0a76c3246f9 100644
--- a/storage/spider/mysql-test/spider/r/direct_join.result
+++ b/storage/spider/mysql-test/spider/r/direct_join.result
@@ -86,6 +86,98 @@ a	b	date_format(c, '%Y-%m-%d %H:%i:%s')
 4	d	2000-01-04 00:00:00
 5	e	2000-01-05 00:00:00
 
+Test JOIN on a constant table.
+Spider should NOT push down the join because the tbl_person table
+is optimized as a constant table.
+connection child2_1;
+CHILD2_1_DROP_CONST_TABLE_JOIN
+CHILD2_1_DROP_CONST_TABLE2_JOIN
+CHILD2_1_CREATE_CONST_TABLE_JOIN
+CHILD2_1_CREATE_CONST_TABLE2_JOIN
+TRUNCATE TABLE mysql.general_log;
+connection master_1;
+DROP TABLE IF EXISTS tbl_person;
+DROP TABLE IF EXISTS tbl_ncd_cm_person;
+CREATE TABLE tbl_person (
+id VARCHAR(50) NOT NULL,
+hr_status VARCHAR(50) NULL DEFAULT NULL,
+region_code VARCHAR(50) NULL DEFAULT NULL,
+region INT(11) NOT NULL,
+PRIMARY KEY (id, region) USING BTREE
+) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_CONST_TABLE_JOIN
+MASTER_1_ROW_FORMAT_CONST_TABLE_JOIN MASTER_1_PART_CONST_TABLE_JOIN
+SHOW CREATE TABLE tbl_person
+Table	Create Table
+tbl_person	CREATE TABLE `tbl_person` (
+  `id` varchar(50) NOT NULL,
+  `hr_status` varchar(50) DEFAULT NULL,
+  `region_code` varchar(50) DEFAULT NULL,
+  `region` int(11) NOT NULL,
+  PRIMARY KEY (`id`,`region`) USING BTREE
+) ENGINE=SPIDER DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='table "tbl_ncd_cm_person"'
+ PARTITION BY LIST  COLUMNS(`region`)
+(PARTITION `pt1` VALUES IN (510411) COMMENT = 'tbl "tbl_person", srv "s_2_1"' ENGINE = SPIDER)
+CREATE TABLE tbl_ncd_cm_person (
+id VARCHAR(50) NOT NULL,
+person_id VARCHAR(50) NULL DEFAULT '',
+diseaseKind_id VARCHAR(50) NULL DEFAULT NULL,
+region INT(11) NOT NULL,
+PRIMARY KEY (id, region) USING BTREE
+) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_CONST_TABLE2_JOIN
+MASTER_1_ROW_FORMAT_CONST_TABLE_JOIN MASTER_1_PART_CONST_TABLE2_JOIN
+SHOW CREATE TABLE tbl_ncd_cm_person
+Table	Create Table
+tbl_ncd_cm_person	CREATE TABLE `tbl_ncd_cm_person` (
+  `id` varchar(50) NOT NULL,
+  `person_id` varchar(50) DEFAULT '',
+  `diseaseKind_id` varchar(50) DEFAULT NULL,
+  `region` int(11) NOT NULL,
+  PRIMARY KEY (`id`,`region`) USING BTREE
+) ENGINE=SPIDER DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
+ PARTITION BY LIST  COLUMNS(`region`)
+(PARTITION `pt1` VALUES IN (510411) COMMENT = 'tbl "tbl_ncd_cm_person", srv "s_2_1"' ENGINE = SPIDER)
+INSERT INTO tbl_person VALUES ('24FC3F0A5119432BAE13DD65AABAA39C',
+'1', '123-51041110620301-321', 510411);
+INSERT INTO tbl_person VALUES ('123456789012345678901234567890AB',
+'1', '123-51041110620301-321', 510411);
+INSERT INTO tbl_ncd_cm_person VALUES ('123456789',
+'24FC3F0A5119432BAE13DD65AABAA39C',
+'52A0328740914BCE86ED10A4D2521816',
+510411);
+INSERT INTO tbl_ncd_cm_person VALUES ('123456789AB',
+'123456789012345678901234567890AB',
+'52A0328740914BCE86ED10A4D2521816',
+510411);
+DELETE FROM tbl_ncd_cm_person;
+INSERT INTO tbl_ncd_cm_person VALUES ('123456789',
+'24FC3F0A5119432BAE13DD65AABAA39C',
+'52A0328740914BCE86ED10A4D2521816',
+510411);
+INSERT INTO tbl_ncd_cm_person VALUES ('123456789AB',
+'123456789012345678901234567890AB',
+'52A0328740914BCE86ED10A4D2521816',
+510411);
+connection child2_1;
+TRUNCATE TABLE mysql.general_log;
+connection master_1;
+SELECT count(0) FROM tbl_person tp INNER JOIN tbl_ncd_cm_person tncp ON tp.id = tncp.person_id WHERE 1 = 1 AND tp.hr_status != "99" AND tp.hr_status != "2" AND tp.region_code LIKE CONCAT(CONCAT('%', '51041110620301', '%')) AND tp.id = '24FC3F0A5119432BAE13DD65AABAA39C' AND tncp.diseaseKind_id = '52A0328740914BCE86ED10A4D2521816' AND tp.region = 510411;
+count(0)
+1
+connection child2_1;
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
+argument
+select `id`,`hr_status`,`region_code`,`region` from `auto_test_remote`.`tbl_person` where `id` = '24FC3F0A5119432BAE13DD65AABAA39C' and `region` = 510411
+select `person_id`,`diseaseKind_id` from `auto_test_remote`.`tbl_ncd_cm_person` where ((`diseaseKind_id` = '52A0328740914BCE86ED10A4D2521816'))
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'
+SELECT * FROM tbl_person;
+id	hr_status	region_code	region
+123456789012345678901234567890AB	1	123-51041110620301-321	510411
+24FC3F0A5119432BAE13DD65AABAA39C	1	123-51041110620301-321	510411
+SELECT * FROM tbl_ncd_cm_person;
+id	person_id	diseaseKind_id	region
+123456789	24FC3F0A5119432BAE13DD65AABAA39C	52A0328740914BCE86ED10A4D2521816	510411
+123456789AB	123456789012345678901234567890AB	52A0328740914BCE86ED10A4D2521816	510411
+
 deinit
 connection master_1;
 DROP DATABASE IF EXISTS auto_test_local;
diff --git a/storage/spider/mysql-test/spider/t/direct_join.test b/storage/spider/mysql-test/spider/t/direct_join.test
index 680164006c8..3f7c692d2c2 100644
--- a/storage/spider/mysql-test/spider/t/direct_join.test
+++ b/storage/spider/mysql-test/spider/t/direct_join.test
@@ -178,6 +178,152 @@ if ($USE_CHILD_GROUP2)
 }
 
 --echo
+--echo Test JOIN on a constant table.
+--echo Spider should NOT push down the join because the tbl_person table
+--echo is optimized as a constant table.
+if ($USE_CHILD_GROUP2)
+{
+  if (!$OUTPUT_CHILD_GROUP2)
+  {
+    --disable_query_log
+    --disable_result_log
+  }
+  --connection child2_1
+  if ($OUTPUT_CHILD_GROUP2)
+  {
+    --disable_query_log
+    echo CHILD2_1_DROP_CONST_TABLE_JOIN;
+    echo CHILD2_1_DROP_CONST_TABLE2_JOIN;
+    echo CHILD2_1_CREATE_CONST_TABLE_JOIN;
+    echo CHILD2_1_CREATE_CONST_TABLE2_JOIN;
+  }
+  --disable_warnings
+  eval $CHILD2_1_DROP_CONST_TABLE_JOIN;
+  eval $CHILD2_1_DROP_CONST_TABLE2_JOIN;
+  --enable_warnings
+  eval $CHILD2_1_CREATE_CONST_TABLE_JOIN;
+  eval $CHILD2_1_CREATE_CONST_TABLE2_JOIN;
+  if ($OUTPUT_CHILD_GROUP2)
+  {
+    --enable_query_log
+  }
+  if ($USE_GENERAL_LOG)
+  {
+    TRUNCATE TABLE mysql.general_log;
+  }
+  if (!$OUTPUT_CHILD_GROUP2)
+  {
+    --enable_query_log
+    --enable_result_log
+  }
+}
+--connection master_1
+--disable_warnings
+DROP TABLE IF EXISTS tbl_person;
+DROP TABLE IF EXISTS tbl_ncd_cm_person;
+--enable_warnings
+--disable_query_log
+echo CREATE TABLE tbl_person (
+    id VARCHAR(50) NOT NULL,
+    hr_status VARCHAR(50) NULL DEFAULT NULL,
+    region_code VARCHAR(50) NULL DEFAULT NULL,
+    region INT(11) NOT NULL,
+    PRIMARY KEY (id, region) USING BTREE
+  ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_CONST_TABLE_JOIN
+    MASTER_1_ROW_FORMAT_CONST_TABLE_JOIN MASTER_1_PART_CONST_TABLE_JOIN;
+eval CREATE TABLE tbl_person (
+    id VARCHAR(50) NOT NULL,
+    hr_status VARCHAR(50) NULL DEFAULT NULL,
+    region_code VARCHAR(50) NULL DEFAULT NULL,
+    region INT(11) NOT NULL,
+    PRIMARY KEY (id, region) USING BTREE
+  ) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT_CONST_TABLE_JOIN
+    $MASTER_1_ROW_FORMAT_CONST_TABLE_JOIN $MASTER_1_PART_CONST_TABLE_JOIN;
+echo SHOW CREATE TABLE tbl_person;
+SHOW CREATE TABLE tbl_person;
+echo CREATE TABLE tbl_ncd_cm_person (
+    id VARCHAR(50) NOT NULL,
+    person_id VARCHAR(50) NULL DEFAULT '',
+    diseaseKind_id VARCHAR(50) NULL DEFAULT NULL,
+    region INT(11) NOT NULL,
+    PRIMARY KEY (id, region) USING BTREE
+  ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_CONST_TABLE2_JOIN
+    MASTER_1_ROW_FORMAT_CONST_TABLE_JOIN MASTER_1_PART_CONST_TABLE2_JOIN;
+eval CREATE TABLE tbl_ncd_cm_person (
+    id VARCHAR(50) NOT NULL,
+    person_id VARCHAR(50) NULL DEFAULT '',
+    diseaseKind_id VARCHAR(50) NULL DEFAULT NULL,
+    region INT(11) NOT NULL,
+    PRIMARY KEY (id, region) USING BTREE
+  ) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT_CONST_TABLE2_JOIN
+    $MASTER_1_ROW_FORMAT_CONST_TABLE_JOIN $MASTER_1_PART_CONST_TABLE2_JOIN;
+echo SHOW CREATE TABLE tbl_ncd_cm_person;
+SHOW CREATE TABLE tbl_ncd_cm_person;
+--enable_query_log
+INSERT INTO tbl_person VALUES ('24FC3F0A5119432BAE13DD65AABAA39C',
+                               '1', '123-51041110620301-321', 510411);
+INSERT INTO tbl_person VALUES ('123456789012345678901234567890AB',
+                               '1', '123-51041110620301-321', 510411);
+INSERT INTO tbl_ncd_cm_person VALUES ('123456789',
+                                      '24FC3F0A5119432BAE13DD65AABAA39C',
+                                      '52A0328740914BCE86ED10A4D2521816',
+                                      510411);
+INSERT INTO tbl_ncd_cm_person VALUES ('123456789AB',
+                                      '123456789012345678901234567890AB',
+                                      '52A0328740914BCE86ED10A4D2521816',
+                                      510411);
+DELETE FROM tbl_ncd_cm_person;
+INSERT INTO tbl_ncd_cm_person VALUES ('123456789',
+                                      '24FC3F0A5119432BAE13DD65AABAA39C',
+                                      '52A0328740914BCE86ED10A4D2521816',
+                                      510411);
+INSERT INTO tbl_ncd_cm_person VALUES ('123456789AB',
+                                      '123456789012345678901234567890AB',
+                                      '52A0328740914BCE86ED10A4D2521816',
+                                      510411);
+if ($USE_CHILD_GROUP2)
+{
+  if (!$OUTPUT_CHILD_GROUP2)
+  {
+    --disable_query_log
+    --disable_result_log
+  }
+  --connection child2_1
+  if ($USE_GENERAL_LOG)
+  {
+    TRUNCATE TABLE mysql.general_log;
+  }
+  if (!$OUTPUT_CHILD_GROUP2)
+  {
+    --enable_query_log
+    --enable_result_log
+  }
+}
+
+--connection master_1
+SELECT count(0) FROM tbl_person tp INNER JOIN tbl_ncd_cm_person tncp ON tp.id = tncp.person_id WHERE 1 = 1 AND tp.hr_status != "99" AND tp.hr_status != "2" AND tp.region_code LIKE CONCAT(CONCAT('%', '51041110620301', '%')) AND tp.id = '24FC3F0A5119432BAE13DD65AABAA39C' AND tncp.diseaseKind_id = '52A0328740914BCE86ED10A4D2521816' AND tp.region = 510411;
+if ($USE_CHILD_GROUP2)
+{
+  if (!$OUTPUT_CHILD_GROUP2)
+  {
+    --disable_query_log
+    --disable_result_log
+  }
+  --connection child2_1
+  if ($USE_GENERAL_LOG)
+  {
+    eval $CHILD2_1_SELECT_ARGUMENT1;
+  }
+  eval $CHILD2_1_SELECT_CONST_TABLE_JOIN;
+  eval $CHILD2_1_SELECT_CONST_TABLE2_JOIN;
+  if (!$OUTPUT_CHILD_GROUP2)
+  {
+    --enable_query_log
+    --enable_result_log
+  }
+}
+
+--echo
 --echo deinit
 --disable_warnings
 --connection master_1
diff --git a/storage/spider/spd_db_include.h b/storage/spider/spd_db_include.h
index 639bb6dac2d..d19306b1b1a 100644
--- a/storage/spider/spd_db_include.h
+++ b/storage/spider/spd_db_include.h
@@ -697,6 +697,7 @@ public:
   SPIDER_TABLE_HOLDER *add_table(
     ha_spider *spider_arg
   );
+  bool all_query_fields_are_query_table_members();
   int create_table_holder(
     uint table_count_arg
   );
diff --git a/storage/spider/spd_group_by_handler.cc b/storage/spider/spd_group_by_handler.cc
index a5328464bb6..0f8479b6ca6 100644
--- a/storage/spider/spd_group_by_handler.cc
+++ b/storage/spider/spd_group_by_handler.cc
@@ -911,6 +911,35 @@ SPIDER_TABLE_HOLDER *spider_fields::add_table(
   DBUG_RETURN(return_table_holder);
 }
 
+/**
+  Verify that all fields in the query are members of tables that are in the
+  query.
+
+  @return TRUE              All fields in the query are members of tables
+                            that are in the query.
+          FALSE             At least one field in the query is not a
+                            member of a table that is in the query.
+*/
+
+bool spider_fields::all_query_fields_are_query_table_members()
+{
+  SPIDER_FIELD_HOLDER *field_holder;
+  DBUG_ENTER("spider_fields::all_fields_are_query_table_fields");
+  DBUG_PRINT("info", ("spider this=%p", this));
+
+  set_pos_to_first_field_holder();
+  while ((field_holder = get_next_field_holder()))
+  {
+    if (!field_holder->spider)
+    {
+      DBUG_PRINT("info", ("spider field is not a member of a query table"));
+      DBUG_RETURN(FALSE);
+    }
+  }
+
+  DBUG_RETURN(TRUE);
+}
+
 int spider_fields::create_table_holder(
   uint table_count_arg
 ) {
@@ -1997,6 +2026,13 @@ group_by_handler *spider_create_group_by_handler(
     }
   }
 
+  if (!fields->all_query_fields_are_query_table_members())
+  {
+    DBUG_PRINT("info", ("spider found a query field that is not a query table member"));
+    delete fields;
+    DBUG_RETURN(NULL);
+  }
+
   fields->check_support_dbton(dbton_bitmap);
   if (!fields->has_conn_holder())
   {