MDEV-20502 Queries against spider tables return wrong values for columns following constant declarations.

When executing a query like "select id, 0 as const, val from ...", there are 3 columns(items) in Query->select at handlerton->create_group_by(). After that, MariaDB makes a temporary table with 2 columns. The skipped items are const item, so fixing Spider to skip const items for items at Query->select.

10 files changed, 216 insertions, 4 deletions

diff --git a/storage/spider/mysql-test/spider/bugfix/include/mdev_20502_deinit.inc b/storage/spider/mysql-test/spider/bugfix/include/mdev_20502_deinit.inc
new file mode 100644
index 00000000000..76b7582abfe
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/include/mdev_20502_deinit.inc
@@ -0,0 +1,11 @@
+--let $MASTER_1_COMMENT_2_1= $MASTER_1_COMMENT_2_1_BACKUP
+--let $CHILD2_1_DROP_TABLES= $CHILD2_1_DROP_TABLES_BACKUP
+--let $CHILD2_1_CREATE_TABLES= $CHILD2_1_CREATE_TABLES_BACKUP
+--let $CHILD2_1_SELECT_TABLES= $CHILD2_1_SELECT_TABLES_BACKUP
+--disable_warnings
+--disable_query_log
+--disable_result_log
+--source ../t/test_deinit.inc
+--enable_result_log
+--enable_query_log
+--enable_warnings
diff --git a/storage/spider/mysql-test/spider/bugfix/include/mdev_20502_init.inc b/storage/spider/mysql-test/spider/bugfix/include/mdev_20502_init.inc
new file mode 100644
index 00000000000..fd8cc0d8170
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/include/mdev_20502_init.inc
@@ -0,0 +1,25 @@
+--disable_warnings
+--disable_query_log
+--disable_result_log
+--source ../t/test_init.inc
+--enable_result_log
+--enable_query_log
+--enable_warnings
+--let $MASTER_1_COMMENT_2_1_BACKUP= $MASTER_1_COMMENT_2_1
+let $MASTER_1_COMMENT_2_1=
+  COMMENT='table "tbl_a", srv "s_2_1"';
+--let $CHILD2_1_DROP_TABLES_BACKUP= $CHILD2_1_DROP_TABLES
+let $CHILD2_1_DROP_TABLES=
+  DROP TABLE IF EXISTS tbl_a;
+--let $CHILD2_1_CREATE_TABLES_BACKUP= $CHILD2_1_CREATE_TABLES
+let $CHILD2_1_CREATE_TABLES=
+  CREATE TABLE tbl_a (
+    id int(10) unsigned NOT NULL AUTO_INCREMENT,  
+    val int(10) unsigned DEFAULT NULL,
+    PRIMARY KEY (id)
+  ) $CHILD2_1_ENGINE $CHILD2_1_CHARSET;
+--let $CHILD2_1_SELECT_TABLES_BACKUP= $CHILD2_1_SELECT_TABLES
+let $CHILD2_1_SELECT_TABLES=
+  SELECT id, val FROM tbl_a ORDER BY id;
+let $CHILD2_1_SELECT_ARGUMENT1=
+  SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_20502.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_20502.result
new file mode 100644
index 00000000000..284f62e522e
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_20502.result
@@ -0,0 +1,61 @@
+for master_1
+for child2
+child2_1
+child2_2
+child2_3
+for child3
+
+this test is for MDEV-20502
+
+drop and create databases
+connection master_1;
+CREATE DATABASE auto_test_local;
+USE auto_test_local;
+connection child2_1;
+SET @old_log_output = @@global.log_output;
+SET GLOBAL log_output = 'TABLE,FILE';
+CREATE DATABASE auto_test_remote;
+USE auto_test_remote;
+
+create table and insert
+connection child2_1;
+CHILD2_1_CREATE_TABLES
+TRUNCATE TABLE mysql.general_log;
+connection master_1;
+CREATE TABLE tbl_a (
+id int(10) unsigned NOT NULL AUTO_INCREMENT,
+val int(10) unsigned DEFAULT NULL,
+PRIMARY KEY(id)
+) ENGINE=Spider COMMENT='table "tbl_a", srv "s_2_1"'
+INSERT INTO tbl_a (val) VALUES (1);
+
+test 1
+connection child2_1;
+TRUNCATE TABLE mysql.general_log;
+connection master_1;
+SELECT id, 0 AS const, val FROM tbl_a;
+id	const	val
+1	0	1
+connection child2_1;
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
+argument
+select t0.`id` `id`,t0.`val` `val` from `auto_test_remote`.`tbl_a` t0
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'
+SELECT id, val FROM tbl_a ORDER BY id;
+id	val
+1	1
+
+deinit
+connection master_1;
+DROP DATABASE IF EXISTS auto_test_local;
+connection child2_1;
+DROP DATABASE IF EXISTS auto_test_remote;
+SET GLOBAL log_output = @old_log_output;
+for master_1
+for child2
+child2_1
+child2_2
+child2_3
+for child3
+
+end of test
diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_20502.cnf b/storage/spider/mysql-test/spider/bugfix/t/mdev_20502.cnf
new file mode 100644
index 00000000000..05dfd8a0bce
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_20502.cnf
@@ -0,0 +1,3 @@
+!include include/default_mysqld.cnf
+!include ../my_1_1.cnf
+!include ../my_2_1.cnf
diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_20502.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_20502.test
new file mode 100644
index 00000000000..4693f2f7409
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_20502.test
@@ -0,0 +1,71 @@
+--source ../include/mdev_20502_init.inc
+--echo
+--echo this test is for MDEV-20502
+--echo
+--echo drop and create databases
+
+--connection master_1
+--disable_warnings
+CREATE DATABASE auto_test_local;
+USE auto_test_local;
+
+--connection child2_1
+SET @old_log_output = @@global.log_output;
+SET GLOBAL log_output = 'TABLE,FILE';
+CREATE DATABASE auto_test_remote;
+USE auto_test_remote;
+--enable_warnings
+
+--echo
+--echo create table and insert
+
+--connection child2_1
+--disable_query_log
+echo CHILD2_1_CREATE_TABLES;
+eval $CHILD2_1_CREATE_TABLES;
+--enable_query_log
+TRUNCATE TABLE mysql.general_log;
+
+--connection master_1
+--disable_query_log
+echo CREATE TABLE tbl_a (
+  id int(10) unsigned NOT NULL AUTO_INCREMENT,
+  val int(10) unsigned DEFAULT NULL,
+  PRIMARY KEY(id)
+) $MASTER_1_ENGINE $MASTER_1_COMMENT_2_1;
+eval CREATE TABLE tbl_a (
+  id int(10) unsigned NOT NULL AUTO_INCREMENT,
+  val int(10) unsigned DEFAULT NULL,
+  PRIMARY KEY(id)
+) $MASTER_1_ENGINE $MASTER_1_COMMENT_2_1;
+--enable_query_log
+INSERT INTO tbl_a (val) VALUES (1);
+
+--echo
+--echo test 1
+
+--connection child2_1
+TRUNCATE TABLE mysql.general_log;
+
+--connection master_1
+SELECT id, 0 AS const, val FROM tbl_a;
+
+--connection child2_1
+eval $CHILD2_1_SELECT_ARGUMENT1;
+eval $CHILD2_1_SELECT_TABLES;
+
+--echo
+--echo deinit
+--disable_warnings
+
+--connection master_1
+DROP DATABASE IF EXISTS auto_test_local;
+
+--connection child2_1
+DROP DATABASE IF EXISTS auto_test_remote;
+SET GLOBAL log_output = @old_log_output;
+
+--enable_warnings
+--source ../include/mdev_20502_deinit.inc
+--echo
+--echo end of test
diff --git a/storage/spider/mysql-test/spider/r/timestamp.result b/storage/spider/mysql-test/spider/r/timestamp.result
index bd1f442d462..4e4badccc41 100644
--- a/storage/spider/mysql-test/spider/r/timestamp.result
+++ b/storage/spider/mysql-test/spider/r/timestamp.result
@@ -396,7 +396,7 @@ select t0.`col_d` `col_d`,t0.`col_t` `col_t` from `ts_test_remote`.`tbl_f` t0
 select (timestamp(t0.`col_d` , t0.`col_t`)) `TIMESTAMP(col_d, col_t)` from `ts_test_remote`.`tbl_f` t0
 select (timestamp('2018-06-25' , t0.`col_t`)) `TIMESTAMP('2018-06-25', col_t)` from `ts_test_remote`.`tbl_f` t0
 select (timestamp(t0.`col_d` , '10:43:21')) `TIMESTAMP(col_d, '10:43:21')` from `ts_test_remote`.`tbl_f` t0
-select (timestamp('2018-06-25' , '10:43:21')) `TIMESTAMP('2018-06-25', '10:43:21')` from `ts_test_remote`.`tbl_f` t0
+select 1 from `ts_test_remote`.`tbl_f` t0
 SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'
 SELECT col_d, col_t FROM tbl_f;
 col_d	col_t
diff --git a/storage/spider/spd_db_conn.cc b/storage/spider/spd_db_conn.cc
index e87321f896d..cea85b46657 100644
--- a/storage/spider/spd_db_conn.cc
+++ b/storage/spider/spd_db_conn.cc
@@ -3013,6 +3013,9 @@ int spider_db_fetch_table(
 #ifndef DBUG_OFF
         dbug_tmp_restore_column_map(table->write_set, tmp_map);
 #endif
+      } else {
+        DBUG_PRINT("info", ("spider bitmap is not set %s",
+          SPIDER_field_name_str(*field)));
       }
       row->next();
     }
diff --git a/storage/spider/spd_db_mysql.cc b/storage/spider/spd_db_mysql.cc
index 128cd494e7f..cac6b063ad0 100644
--- a/storage/spider/spd_db_mysql.cc
+++ b/storage/spider/spd_db_mysql.cc
@@ -14277,15 +14277,21 @@ int spider_mbase_handler::append_list_item_select(
   spider_fields *fields
 ) {
   int error_num;
-  uint32 length;
+  uint32 length, begin;
   List_iterator_fast<Item> it(*select);
   Item *item;
   Field *field;
   const char *item_name;
   DBUG_ENTER("spider_mbase_handler::append_list_item_select");
   DBUG_PRINT("info",("spider this=%p", this));
+  begin = str->length();
   while ((item = it++))
   {
+    if (item->const_item())
+    {
+      DBUG_PRINT("info",("spider const item"));
+      continue;
+    }
     if ((error_num = spider_db_print_item_type(item, NULL, spider, str,
       alias, alias_length, dbton_id, use_fields, fields)))
     {
@@ -14313,7 +14319,17 @@ int spider_mbase_handler::append_list_item_select(
     }
     str->q_append(SPIDER_SQL_COMMA_STR, SPIDER_SQL_COMMA_LEN);
   }
-  str->length(str->length() - SPIDER_SQL_COMMA_LEN);
+  if (begin == str->length())
+  {
+    /* no columns */
+    if (str->reserve(SPIDER_SQL_ONE_LEN))
+    {
+      DBUG_RETURN(HA_ERR_OUT_OF_MEM);
+    }
+    str->q_append(SPIDER_SQL_ONE_STR, SPIDER_SQL_ONE_LEN);
+  } else {
+    str->length(str->length() - SPIDER_SQL_COMMA_LEN);
+  }
   DBUG_RETURN(0);
 }
 
diff --git a/storage/spider/spd_db_oracle.cc b/storage/spider/spd_db_oracle.cc
index d3147c19f2f..b7e8100e480 100644
--- a/storage/spider/spd_db_oracle.cc
+++ b/storage/spider/spd_db_oracle.cc
@@ -12739,14 +12739,21 @@ int spider_oracle_handler::append_list_item_select(
 ) {
   int error_num;
   uint dbton_id = spider_dbton_oracle.dbton_id, length;
+  uint32 begin;
   List_iterator_fast<Item> it(*select);
   Item *item;
   Field *field;
   const char *item_name;
   DBUG_ENTER("spider_oracle_handler::append_list_item_select");
   DBUG_PRINT("info",("spider this=%p", this));
+  begin = str->length();
   while ((item = it++))
   {
+    if (item->const_item())
+    {
+      DBUG_PRINT("info",("spider const item"));
+      continue;
+    }
     if ((error_num = spider_db_print_item_type(item, NULL, spider, str,
       alias, alias_length, dbton_id, use_fields, fields)))
     {
@@ -12774,7 +12781,17 @@ int spider_oracle_handler::append_list_item_select(
     }
     str->q_append(SPIDER_SQL_COMMA_STR, SPIDER_SQL_COMMA_LEN);
   }
-  str->length(str->length() - SPIDER_SQL_COMMA_LEN);
+  if (begin == str->length())
+  {
+    /* no columns */
+    if (str->reserve(SPIDER_SQL_ONE_LEN))
+    {
+      DBUG_RETURN(HA_ERR_OUT_OF_MEM);
+    }
+    str->q_append(SPIDER_SQL_ONE_STR, SPIDER_SQL_ONE_LEN);
+  } else {
+    str->length(str->length() - SPIDER_SQL_COMMA_LEN);
+  }
   DBUG_RETURN(0);
 }
 
diff --git a/storage/spider/spd_group_by_handler.cc b/storage/spider/spd_group_by_handler.cc
index c83ee753e5b..8bd0eca507f 100644
--- a/storage/spider/spd_group_by_handler.cc
+++ b/storage/spider/spd_group_by_handler.cc
@@ -1794,6 +1794,11 @@ group_by_handler *spider_create_group_by_handler(
       while ((item = it++))
       {
         DBUG_PRINT("info",("spider select item=%p", item));
+        if (item->const_item())
+        {
+          DBUG_PRINT("info",("spider const item"));
+          continue;
+        }
         if (spider_db_print_item_type(item, NULL, spider, NULL, NULL, 0,
           roop_count, TRUE, fields_arg))
         {