MDEV-18995 Some bugs in direct join (#1285)

Add const_table support to the direct join feature and condition pushdown feature

15 files changed, 663 insertions, 49 deletions

diff --git a/storage/spider/mysql-test/spider/r/direct_join.result b/storage/spider/mysql-test/spider/r/direct_join.result
index 0a76c3246f9..a1018c35fbf 100644
--- a/storage/spider/mysql-test/spider/r/direct_join.result
+++ b/storage/spider/mysql-test/spider/r/direct_join.result
@@ -167,7 +167,7 @@ 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 count(0) `count(0)` from `auto_test_remote`.`tbl_ncd_cm_person` t0 where ((t0.`person_id` = '24FC3F0A5119432BAE13DD65AABAA39C') and (t0.`diseaseKind_id` = '52A0328740914BCE86ED10A4D2521816'))
 SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'
 SELECT * FROM tbl_person;
 id	hr_status	region_code	region
diff --git a/storage/spider/mysql-test/spider/regression/e1121/include/direct_join_by_pkey_key_deinit.inc b/storage/spider/mysql-test/spider/regression/e1121/include/direct_join_by_pkey_key_deinit.inc
new file mode 100644
index 00000000000..ad30aac5d6e
--- /dev/null
+++ b/storage/spider/mysql-test/spider/regression/e1121/include/direct_join_by_pkey_key_deinit.inc
@@ -0,0 +1,12 @@
+--let $MASTER_1_COMMENT_2_1= $MASTER_1_COMMENT_2_1_BACKUP
+--let $MASTER_1_COMMENT_2_1_2= $MASTER_1_COMMENT_2_1_2_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/regression/e1121/include/direct_join_by_pkey_key_init.inc b/storage/spider/mysql-test/spider/regression/e1121/include/direct_join_by_pkey_key_init.inc
new file mode 100644
index 00000000000..695d46101ae
--- /dev/null
+++ b/storage/spider/mysql-test/spider/regression/e1121/include/direct_join_by_pkey_key_init.inc
@@ -0,0 +1,36 @@
+--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 $MASTER_1_COMMENT_2_1_2_BACKUP= $MASTER_1_COMMENT_2_1_2
+let $MASTER_1_COMMENT_2_1_2=
+  COMMENT='table "tbl_b", 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 $STR_SEMICOLON
+  DROP TABLE IF EXISTS tbl_b;
+--let $CHILD2_1_CREATE_TABLES_BACKUP= $CHILD2_1_CREATE_TABLES
+let $CHILD2_1_CREATE_TABLES=
+  CREATE TABLE tbl_a (
+    akey int NOT NULL,
+    val int NOT NULL,
+    KEY idx1 (akey)
+  ) $CHILD2_1_ENGINE $CHILD2_1_CHARSET $STR_SEMICOLON
+  CREATE TABLE tbl_b (
+    bkey int NOT NULL,
+    akey int NOT NULL,
+    PRIMARY KEY (bkey)
+  ) $CHILD2_1_ENGINE $CHILD2_1_CHARSET;
+--let $CHILD2_1_SELECT_TABLES_BACKUP= $CHILD2_1_SELECT_TABLES
+let $CHILD2_1_SELECT_TABLES=
+  SELECT akey, val FROM tbl_a ORDER BY akey $STR_SEMICOLON
+  SELECT bkey, akey FROM tbl_b ORDER BY bkey;
+let $CHILD2_1_SELECT_ARGUMENT1=
+  SELECT argument FROM mysql.general_log WHERE argument LIKE '%`tbl_a`%' $STR_SEMICOLON
+  SELECT argument FROM mysql.general_log WHERE argument LIKE '%`tbl_b`%';
diff --git a/storage/spider/mysql-test/spider/regression/e1121/include/direct_join_by_pkey_pkey_deinit.inc b/storage/spider/mysql-test/spider/regression/e1121/include/direct_join_by_pkey_pkey_deinit.inc
new file mode 100644
index 00000000000..ad30aac5d6e
--- /dev/null
+++ b/storage/spider/mysql-test/spider/regression/e1121/include/direct_join_by_pkey_pkey_deinit.inc
@@ -0,0 +1,12 @@
+--let $MASTER_1_COMMENT_2_1= $MASTER_1_COMMENT_2_1_BACKUP
+--let $MASTER_1_COMMENT_2_1_2= $MASTER_1_COMMENT_2_1_2_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/regression/e1121/include/direct_join_by_pkey_pkey_init.inc b/storage/spider/mysql-test/spider/regression/e1121/include/direct_join_by_pkey_pkey_init.inc
new file mode 100644
index 00000000000..e364489c05f
--- /dev/null
+++ b/storage/spider/mysql-test/spider/regression/e1121/include/direct_join_by_pkey_pkey_init.inc
@@ -0,0 +1,36 @@
+--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 $MASTER_1_COMMENT_2_1_2_BACKUP= $MASTER_1_COMMENT_2_1_2
+let $MASTER_1_COMMENT_2_1_2=
+  COMMENT='table "tbl_b", 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 $STR_SEMICOLON
+  DROP TABLE IF EXISTS tbl_b;
+--let $CHILD2_1_CREATE_TABLES_BACKUP= $CHILD2_1_CREATE_TABLES
+let $CHILD2_1_CREATE_TABLES=
+  CREATE TABLE tbl_a (
+    akey int NOT NULL,
+    val int NOT NULL,
+    PRIMARY KEY (akey)
+  ) $CHILD2_1_ENGINE $CHILD2_1_CHARSET $STR_SEMICOLON
+  CREATE TABLE tbl_b (
+    bkey int NOT NULL,
+    akey int NOT NULL,
+    PRIMARY KEY (bkey)
+  ) $CHILD2_1_ENGINE $CHILD2_1_CHARSET;
+--let $CHILD2_1_SELECT_TABLES_BACKUP= $CHILD2_1_SELECT_TABLES
+let $CHILD2_1_SELECT_TABLES=
+  SELECT akey, val FROM tbl_a ORDER BY akey $STR_SEMICOLON
+  SELECT bkey, akey FROM tbl_b ORDER BY bkey;
+let $CHILD2_1_SELECT_ARGUMENT1=
+  SELECT argument FROM mysql.general_log WHERE argument LIKE '%`tbl_a`%' $STR_SEMICOLON
+  SELECT argument FROM mysql.general_log WHERE argument LIKE '%`tbl_b`%';
diff --git a/storage/spider/mysql-test/spider/regression/e1121/my.cnf b/storage/spider/mysql-test/spider/regression/e1121/my.cnf
new file mode 100644
index 00000000000..47558d85fc6
--- /dev/null
+++ b/storage/spider/mysql-test/spider/regression/e1121/my.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/regression/e1121/my_1_1.cnf b/storage/spider/mysql-test/spider/regression/e1121/my_1_1.cnf
new file mode 100644
index 00000000000..5f17295d895
--- /dev/null
+++ b/storage/spider/mysql-test/spider/regression/e1121/my_1_1.cnf
@@ -0,0 +1,44 @@
+[mysqld.1.1]
+log-bin=                  master-bin
+loose_handlersocket_port=                20000
+loose_handlersocket_port_wr=             20001
+loose_handlersocket_threads=             2
+loose_handlersocket_threads_wr=          1
+loose_handlersocket_support_merge_table= 0
+loose_handlersocket_direct_update_mode=  2
+loose_handlersocket_unlimited_boundary=  65536
+loose_handlersocket_bulk_insert=         0
+loose_handlersocket_bulk_insert_timeout= 0
+loose_handlersocket_general_log=         1
+loose_handlersocket_timeout=             30
+loose_handlersocket_close_table_interval=2
+open_files_limit=                        4096
+loose_partition=                         1
+
+[ENV]
+USE_GEOMETRY_TEST=        1
+USE_FULLTEXT_TEST=        1
+USE_HA_TEST=              1
+USE_GENERAL_LOG=          1
+USE_REPLICATION=          1
+MASTER_1_MYPORT=          @mysqld.1.1.port
+MASTER_1_HSRPORT=         20000
+MASTER_1_HSWPORT=         20001
+MASTER_1_MYSOCK=          @mysqld.1.1.socket
+MASTER_1_ENGINE_TYPE=     Spider
+#MASTER_1_ENGINE_TYPE=     MyISAM
+MASTER_1_ENGINE=          ENGINE=Spider
+MASTER_1_CHARSET=         DEFAULT CHARSET=utf8
+MASTER_1_ENGINE2=         ENGINE=MyISAM
+MASTER_1_CHARSET2=        DEFAULT CHARSET=utf8
+MASTER_1_CHARSET3=        DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
+
+STR_SEMICOLON=            ;
+
+#The followings are set in include/init_xxx.inc files
+# MASTER_1_COMMENT_2_1
+# MASTER_1_COMMENT2_2_1
+# MASTER_1_COMMENT3_2_1
+# MASTER_1_COMMENT4_2_1
+# MASTER_1_COMMENT5_2_1
+# MASTER_1_COMMENT_P_2_1
diff --git a/storage/spider/mysql-test/spider/regression/e1121/my_2_1.cnf b/storage/spider/mysql-test/spider/regression/e1121/my_2_1.cnf
new file mode 100644
index 00000000000..24161645607
--- /dev/null
+++ b/storage/spider/mysql-test/spider/regression/e1121/my_2_1.cnf
@@ -0,0 +1,56 @@
+[mysqld.2.1]
+loose_handlersocket_port=                20002
+loose_handlersocket_port_wr=             20003
+loose_handlersocket_threads=             2
+loose_handlersocket_threads_wr=          1
+loose_handlersocket_support_merge_table= 0
+loose_handlersocket_direct_update_mode=  2
+loose_handlersocket_unlimited_boundary=  65536
+loose_handlersocket_bulk_insert=         0
+loose_handlersocket_bulk_insert_timeout= 0
+loose_handlersocket_general_log=         1
+loose_handlersocket_timeout=             30
+loose_handlersocket_close_table_interval=2
+open_files_limit=                        4096
+
+[ENV]
+USE_CHILD_GROUP2=         1
+OUTPUT_CHILD_GROUP2=      0
+CHILD2_1_MYPORT=          @mysqld.2.1.port
+CHILD2_1_HSRPORT=         20002
+CHILD2_1_HSWPORT=         20003
+CHILD2_1_MYSOCK=          @mysqld.2.1.socket
+CHILD2_1_ENGINE_TYPE=     InnoDB
+CHILD2_1_ENGINE=          ENGINE=InnoDB
+CHILD2_1_CHARSET=         DEFAULT CHARSET=utf8
+CHILD2_1_CHARSET2=        DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
+CHILD2_1_FT_MYPORT=       @mysqld.2.1.port
+CHILD2_1_FT_MYSOCK=       @mysqld.2.1.socket
+CHILD2_1_FT_ENGINE_TYPE=  MyISAM
+CHILD2_1_FT_ENGINE=       ENGINE=MyISAM
+CHILD2_1_FT_CHARSET=      DEFAULT CHARSET=utf8
+CHILD2_1_GM_MYPORT=       @mysqld.2.1.port
+CHILD2_1_GM_MYSOCK=       @mysqld.2.1.socket
+CHILD2_1_GM_ENGINE_TYPE=  MyISAM
+CHILD2_1_GM_ENGINE=       ENGINE=MyISAM
+CHILD2_1_GM_CHARSET=      DEFAULT CHARSET=utf8
+
+#The followings are set in include/init_xxx.inc files
+# CHILD2_1_DROP_TABLES
+# CHILD2_1_CREATE_TABLES
+# CHILD2_1_SELECT_TABLES
+# CHILD2_1_DROP_TABLES2
+# CHILD2_1_CREATE_TABLES2
+# CHILD2_1_SELECT_TABLES2
+# CHILD2_1_DROP_TABLES3
+# CHILD2_1_CREATE_TABLES3
+# CHILD2_1_SELECT_TABLES3
+# CHILD2_1_DROP_TABLES4
+# CHILD2_1_CREATE_TABLES4
+# CHILD2_1_SELECT_TABLES4
+# CHILD2_1_DROP_TABLES5
+# CHILD2_1_CREATE_TABLES5
+# CHILD2_1_SELECT_TABLES5
+# CHILD2_1_DROP_TABLES6
+# CHILD2_1_CREATE_TABLES6
+# CHILD2_1_SELECT_TABLES6
diff --git a/storage/spider/mysql-test/spider/regression/e1121/r/direct_join_by_pkey_key.result b/storage/spider/mysql-test/spider/regression/e1121/r/direct_join_by_pkey_key.result
new file mode 100644
index 00000000000..4b04b71aee2
--- /dev/null
+++ b/storage/spider/mysql-test/spider/regression/e1121/r/direct_join_by_pkey_key.result
@@ -0,0 +1,94 @@
+for master_1
+for child2
+child2_1
+child2_2
+child2_3
+for child3
+
+this test is for MDEV-18995
+
+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 (
+akey int NOT NULL,
+val int NOT NULL,
+KEY idx1 (akey)
+) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1
+CREATE TABLE tbl_b (
+bkey int NOT NULL,
+akey int NOT NULL,
+PRIMARY KEY (bkey)
+) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1_2
+INSERT INTO tbl_a (akey,val) VALUES (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+INSERT INTO tbl_b (bkey,akey) VALUES (0,0),(1,1),(2,2),(3,3),(4,4),(5,4),(6,3),(7,2),(8,1),(9,0);
+
+select test 1
+connection child2_1;
+TRUNCATE TABLE mysql.general_log;
+connection master_1;
+SELECT a.val, a.akey FROM tbl_a a, tbl_b b WHERE a.akey = b.akey AND b.bkey = 5;
+val	akey
+4	4
+connection child2_1;
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%`tbl_a`%' ;
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%`tbl_b`%';
+argument
+select t0.`val` `val`,t0.`akey` `akey` from `auto_test_remote`.`tbl_a` t0 where (t0.`akey` = '4')
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%`tbl_a`%' ;
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%`tbl_b`%'
+argument
+select `bkey`,`akey` from `auto_test_remote`.`tbl_b` where `bkey` = 5
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%`tbl_a`%' ;
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%`tbl_b`%'
+SELECT akey, val FROM tbl_a ORDER BY akey ;
+SELECT bkey, akey FROM tbl_b ORDER BY bkey;
+akey	val
+0	0
+1	1
+2	2
+3	3
+4	4
+5	5
+6	6
+7	7
+8	8
+9	9
+bkey	akey
+0	0
+1	1
+2	2
+3	3
+4	4
+5	4
+6	3
+7	2
+8	1
+9	0
+
+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/regression/e1121/r/direct_join_by_pkey_pkey.result b/storage/spider/mysql-test/spider/regression/e1121/r/direct_join_by_pkey_pkey.result
new file mode 100644
index 00000000000..9a75cc691fe
--- /dev/null
+++ b/storage/spider/mysql-test/spider/regression/e1121/r/direct_join_by_pkey_pkey.result
@@ -0,0 +1,94 @@
+for master_1
+for child2
+child2_1
+child2_2
+child2_3
+for child3
+
+this test is for MDEV-18995
+
+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 (
+akey int NOT NULL,
+val int NOT NULL,
+PRIMARY KEY (akey)
+) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1
+CREATE TABLE tbl_b (
+bkey int NOT NULL,
+akey int NOT NULL,
+PRIMARY KEY (bkey)
+) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1_2
+INSERT INTO tbl_a (akey,val) VALUES (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+INSERT INTO tbl_b (bkey,akey) VALUES (0,0),(1,1),(2,2),(3,3),(4,4),(5,4),(6,3),(7,2),(8,1),(9,0);
+
+select test 1
+connection child2_1;
+TRUNCATE TABLE mysql.general_log;
+connection master_1;
+SELECT a.val, a.akey FROM tbl_a a, tbl_b b WHERE a.akey = b.akey AND b.bkey = 5;
+val	akey
+4	4
+connection child2_1;
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%`tbl_a`%' ;
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%`tbl_b`%';
+argument
+select `akey`,`val` from `auto_test_remote`.`tbl_a` where `akey` = 4
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%`tbl_a`%' ;
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%`tbl_b`%'
+argument
+select `bkey`,`akey` from `auto_test_remote`.`tbl_b` where `bkey` = 5
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%`tbl_a`%' ;
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%`tbl_b`%'
+SELECT akey, val FROM tbl_a ORDER BY akey ;
+SELECT bkey, akey FROM tbl_b ORDER BY bkey;
+akey	val
+0	0
+1	1
+2	2
+3	3
+4	4
+5	5
+6	6
+7	7
+8	8
+9	9
+bkey	akey
+0	0
+1	1
+2	2
+3	3
+4	4
+5	4
+6	3
+7	2
+8	1
+9	0
+
+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/regression/e1121/suite.opt b/storage/spider/mysql-test/spider/regression/e1121/suite.opt
new file mode 100644
index 00000000000..672a3b37d4f
--- /dev/null
+++ b/storage/spider/mysql-test/spider/regression/e1121/suite.opt
@@ -0,0 +1 @@
+--loose-innodb --loose-skip-performance-schema
diff --git a/storage/spider/mysql-test/spider/regression/e1121/suite.pm b/storage/spider/mysql-test/spider/regression/e1121/suite.pm
new file mode 100644
index 00000000000..f106147deb6
--- /dev/null
+++ b/storage/spider/mysql-test/spider/regression/e1121/suite.pm
@@ -0,0 +1,12 @@
+package My::Suite::Spider;
+
+@ISA = qw(My::Suite);
+
+return "No Spider engine" unless $ENV{HA_SPIDER_SO};
+return "Not run for embedded server" if $::opt_embedded_server;
+return "Test needs --big-test" unless $::opt_big_test;
+
+sub is_default { 1 }
+
+bless { };
+
diff --git a/storage/spider/mysql-test/spider/regression/e1121/t/direct_join_by_pkey_key.test b/storage/spider/mysql-test/spider/regression/e1121/t/direct_join_by_pkey_key.test
new file mode 100644
index 00000000000..e915a21fd4a
--- /dev/null
+++ b/storage/spider/mysql-test/spider/regression/e1121/t/direct_join_by_pkey_key.test
@@ -0,0 +1,82 @@
+--source ../include/direct_join_by_pkey_key_init.inc
+--echo
+--echo this test is for MDEV-18995
+--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 (
+    akey int NOT NULL,
+    val int NOT NULL,
+    KEY idx1 (akey)
+) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1;
+eval CREATE TABLE tbl_a (
+    akey int NOT NULL,
+    val int NOT NULL,
+    KEY idx1 (akey)
+) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT_2_1;
+echo CREATE TABLE tbl_b (
+    bkey int NOT NULL,
+    akey int NOT NULL,
+    PRIMARY KEY (bkey)
+) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1_2;
+eval CREATE TABLE tbl_b (
+    bkey int NOT NULL,
+    akey int NOT NULL,
+    PRIMARY KEY (bkey)
+) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT_2_1_2;
+--enable_query_log
+INSERT INTO tbl_a (akey,val) VALUES (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+INSERT INTO tbl_b (bkey,akey) VALUES (0,0),(1,1),(2,2),(3,3),(4,4),(5,4),(6,3),(7,2),(8,1),(9,0);
+
+--echo
+--echo select test 1
+
+--connection child2_1
+TRUNCATE TABLE mysql.general_log;
+
+--connection master_1
+SELECT a.val, a.akey FROM tbl_a a, tbl_b b WHERE a.akey = b.akey AND b.bkey = 5;
+
+--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/direct_join_by_pkey_key_deinit.inc
+--echo
+--echo end of test
diff --git a/storage/spider/mysql-test/spider/regression/e1121/t/direct_join_by_pkey_pkey.test b/storage/spider/mysql-test/spider/regression/e1121/t/direct_join_by_pkey_pkey.test
new file mode 100644
index 00000000000..dcd6e3a4535
--- /dev/null
+++ b/storage/spider/mysql-test/spider/regression/e1121/t/direct_join_by_pkey_pkey.test
@@ -0,0 +1,82 @@
+--source ../include/direct_join_by_pkey_pkey_init.inc
+--echo
+--echo this test is for MDEV-18995
+--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 (
+    akey int NOT NULL,
+    val int NOT NULL,
+    PRIMARY KEY (akey)
+) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1;
+eval CREATE TABLE tbl_a (
+    akey int NOT NULL,
+    val int NOT NULL,
+    PRIMARY KEY (akey)
+) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT_2_1;
+echo CREATE TABLE tbl_b (
+    bkey int NOT NULL,
+    akey int NOT NULL,
+    PRIMARY KEY (bkey)
+) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1_2;
+eval CREATE TABLE tbl_b (
+    bkey int NOT NULL,
+    akey int NOT NULL,
+    PRIMARY KEY (bkey)
+) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT_2_1_2;
+--enable_query_log
+INSERT INTO tbl_a (akey,val) VALUES (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+INSERT INTO tbl_b (bkey,akey) VALUES (0,0),(1,1),(2,2),(3,3),(4,4),(5,4),(6,3),(7,2),(8,1),(9,0);
+
+--echo
+--echo select test 1
+
+--connection child2_1
+TRUNCATE TABLE mysql.general_log;
+
+--connection master_1
+SELECT a.val, a.akey FROM tbl_a a, tbl_b b WHERE a.akey = b.akey AND b.bkey = 5;
+
+--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/direct_join_by_pkey_pkey_deinit.inc
+--echo
+--echo end of test
diff --git a/storage/spider/spd_db_conn.cc b/storage/spider/spd_db_conn.cc
index bc2c62387dd..06e7223b01e 100644
--- a/storage/spider/spd_db_conn.cc
+++ b/storage/spider/spd_db_conn.cc
@@ -8908,34 +8908,58 @@ int spider_db_open_item_ident(
     Field *field = item_ident->cached_table->table->field[
       item_ident->cached_field_index];
     DBUG_PRINT("info",("spider use cached_field_index"));
-    if (!use_fields)
+    DBUG_PRINT("info",("spider const_table=%s",
+      field->table->const_table ? "TRUE" : "FALSE"));
+    if (field->table->const_table)
     {
-      if (!(field = spider->field_exchange(field)))
-        DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM);
       if (str)
       {
-        if ((error_num = share->dbton_share[dbton_id]->
-          append_column_name_with_alias(str, field->field_index,
-          alias, alias_length)))
-          DBUG_RETURN(error_num);
+        String str_value;
+        String *tmp_str;
+        tmp_str = field->val_str(&str_value);
+        if (!tmp_str)
+        {
+          DBUG_RETURN(HA_ERR_OUT_OF_MEM);
+        }
+        if (str->reserve(SPIDER_SQL_VALUE_QUOTE_LEN * 2 +
+          tmp_str->length() * 2))
+        {
+          DBUG_RETURN(HA_ERR_OUT_OF_MEM);
+        }
+        str->q_append(SPIDER_SQL_VALUE_QUOTE_STR, SPIDER_SQL_VALUE_QUOTE_LEN);
+        str->append_escape_string(tmp_str->ptr(), tmp_str->length());
+        str->q_append(SPIDER_SQL_VALUE_QUOTE_STR, SPIDER_SQL_VALUE_QUOTE_LEN);
       }
     } else {
-      if (str)
+      if (!use_fields)
       {
-        SPIDER_FIELD_CHAIN *field_chain = fields->get_next_field_chain();
-        SPIDER_FIELD_HOLDER *field_holder = field_chain->field_holder;
-        spider = field_holder->spider;
-        share = spider->share;
-        field = spider->field_exchange(field);
-        DBUG_ASSERT(field);
-        if ((error_num = share->dbton_share[dbton_id]->
-          append_column_name_with_alias(str, field->field_index,
-          field_holder->alias->ptr(), field_holder->alias->length())))
-          DBUG_RETURN(error_num);
+        if (!(field = spider->field_exchange(field)))
+          DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM);
+        if (str)
+        {
+          if ((error_num = share->dbton_share[dbton_id]->
+            append_column_name_with_alias(str, field->field_index,
+            alias, alias_length)))
+            DBUG_RETURN(error_num);
+        }
       } else {
-        if ((error_num = fields->add_field(field)))
+        if (str)
         {
-          DBUG_RETURN(error_num);
+          SPIDER_FIELD_CHAIN *field_chain = fields->get_next_field_chain();
+          SPIDER_FIELD_HOLDER *field_holder = field_chain->field_holder;
+          spider = field_holder->spider;
+          share = spider->share;
+          field = spider->field_exchange(field);
+          DBUG_ASSERT(field);
+          if ((error_num = share->dbton_share[dbton_id]->
+            append_column_name_with_alias(str, field->field_index,
+            field_holder->alias->ptr(), field_holder->alias->length())))
+            DBUG_RETURN(error_num);
+        } else {
+          if ((error_num = fields->add_field(field)))
+          {
+            DBUG_RETURN(error_num);
+          }
         }
       }
     }
@@ -9005,46 +9029,72 @@ int spider_db_open_item_field(
   Field *field = item_field->field;
   SPIDER_SHARE *share = spider->share;
   DBUG_ENTER("spider_db_open_item_field");
-  if (field && !field->table->const_table)
+  if (field)
   {
     DBUG_PRINT("info",("spider field=%p", field));
     DBUG_PRINT("info",("spider db=%s", field->table->s->db.str));
-    DBUG_PRINT("info",("spider table_name=%s", field->table->s->table_name.str));
-    DBUG_PRINT("info",("spider tmp_table=%u", field->table->s->tmp_table));
-    if (field->table->s->tmp_table != INTERNAL_TMP_TABLE)
+    DBUG_PRINT("info",("spider table_name=%s",
+      field->table->s->table_name.str));
+    DBUG_PRINT("info",("spider const_table=%s",
+      field->table->const_table ? "TRUE" : "FALSE"));
+    if (field->table->const_table)
     {
-      if (!use_fields)
+      if (str)
       {
-        if (!(field = spider->field_exchange(field)))
-          DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM);
-        if (str)
+        String str_value;
+        String *tmp_str;
+        tmp_str = field->val_str(&str_value);
+        if (!tmp_str)
         {
-          if ((error_num = share->dbton_share[dbton_id]->
-            append_column_name_with_alias(str, field->field_index,
-            alias, alias_length)))
-            DBUG_RETURN(error_num);
+          DBUG_RETURN(HA_ERR_OUT_OF_MEM);
         }
-        DBUG_RETURN(0);
-      } else {
-        if (str)
+        if (str->reserve(SPIDER_SQL_VALUE_QUOTE_LEN * 2 +
+          tmp_str->length() * 2))
         {
-          SPIDER_FIELD_CHAIN *field_chain = fields->get_next_field_chain();
-          SPIDER_FIELD_HOLDER *field_holder = field_chain->field_holder;
-          spider = field_holder->spider;
-          share = spider->share;
-          field = spider->field_exchange(field);
-          DBUG_ASSERT(field);
-          if ((error_num = share->dbton_share[dbton_id]->
-            append_column_name_with_alias(str, field->field_index,
-            field_holder->alias->ptr(), field_holder->alias->length())))
-            DBUG_RETURN(error_num);
+          DBUG_RETURN(HA_ERR_OUT_OF_MEM);
+        }
+        str->q_append(SPIDER_SQL_VALUE_QUOTE_STR, SPIDER_SQL_VALUE_QUOTE_LEN);
+        str->append_escape_string(tmp_str->ptr(), tmp_str->length());
+        str->q_append(SPIDER_SQL_VALUE_QUOTE_STR, SPIDER_SQL_VALUE_QUOTE_LEN);
+      }
+      DBUG_RETURN(0);
+    } else {
+      DBUG_PRINT("info",("spider tmp_table=%u", field->table->s->tmp_table));
+      if (field->table->s->tmp_table != INTERNAL_TMP_TABLE)
+      {
+        if (!use_fields)
+        {
+          if (!(field = spider->field_exchange(field)))
+            DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM);
+          if (str)
+          {
+            if ((error_num = share->dbton_share[dbton_id]->
+              append_column_name_with_alias(str, field->field_index,
+              alias, alias_length)))
+              DBUG_RETURN(error_num);
+          }
+          DBUG_RETURN(0);
         } else {
-          if ((error_num = fields->add_field(field)))
+          if (str)
           {
-            DBUG_RETURN(error_num);
+            SPIDER_FIELD_CHAIN *field_chain = fields->get_next_field_chain();
+            SPIDER_FIELD_HOLDER *field_holder = field_chain->field_holder;
+            spider = field_holder->spider;
+            share = spider->share;
+            field = spider->field_exchange(field);
+            DBUG_ASSERT(field);
+            if ((error_num = share->dbton_share[dbton_id]->
+              append_column_name_with_alias(str, field->field_index,
+              field_holder->alias->ptr(), field_holder->alias->length())))
+              DBUG_RETURN(error_num);
+          } else {
+            if ((error_num = fields->add_field(field)))
+            {
+              DBUG_RETURN(error_num);
+            }
           }
+          DBUG_RETURN(0);
         }
-        DBUG_RETURN(0);
       }
     }
   }