MDEV-24343 Spider Left join failed Unknown column 't0.ID' in 'on clause'bb-10.4-mdev-24343

The Spider mixes the comma join with other join types, and thus
ERROR 1054 occurs.

This is well-known issue caused by the precedence of the operators,
as is explained in the following document.
https://dev.mysql.com/doc/refman/8.0/en/join.html

We can avoid the problem simply by using JOINs instead of commas.

6 files changed, 168 insertions, 18 deletions

diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_24343.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_24343.result
new file mode 100644
index 00000000000..1d154daa8ec
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_24343.result
@@ -0,0 +1,75 @@
+#
+# MDEV-24343 Spider Left join failed Unknown column 't0.ID' in 'on clause'
+#
+for master_1
+for child2
+child2_1
+child2_2
+child2_3
+for child3
+connection child2_1;
+CREATE DATABASE auto_test_remote;
+USE auto_test_remote;
+CREATE TABLE tbl_a (
+`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+`first_name` varchar(255) DEFAULT NULL,
+PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO `tbl_a` VALUES (1,'RICHARD'), (2,'STEPHANE'), (3,'ALAIN');
+CREATE TABLE `tbl_b` (
+`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+`last_name` varchar(255) DEFAULT NULL,
+PRIMARY KEY (`ID`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO `tbl_b` VALUES (1,'DEMONGEOT'),(2,'VAROQUI');
+CREATE TABLE `tbl_c` (
+`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+`surname` varchar(255) DEFAULT NULL,
+PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO `tbl_c` VALUES (1,'CON'),(2,'MOYEN'),(3,'MOYEN2');
+SELECT * from tbl_b JOIN tbl_c ON tbl_b.id = tbl_c.id LEFT OUTER JOIN tbl_a ON tbl_a.id = tbl_b.id;
+id	last_name	id	surname	id	first_name
+1	DEMONGEOT	1	CON	1	RICHARD
+2	VAROQUI	2	MOYEN	2	STEPHANE
+connection master_1;
+CREATE DATABASE auto_test_local;
+USE auto_test_local;
+CREATE TABLE tbl_a (
+`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+`first_name` varchar(255) DEFAULT NULL,
+PRIMARY KEY (`id`)
+) ENGINE=Spider DEFAULT CHARSET=utf8 COMMENT='table "tbl_a"'
+PARTITION BY LIST COLUMNS(`id`) (
+PARTITION `pt1` DEFAULT COMMENT = 'srv "s_2_1"'
+);
+CREATE TABLE `tbl_b` (
+`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+`last_name` varchar(255) DEFAULT NULL,
+PRIMARY KEY (`id`)
+) ENGINE=Spider DEFAULT CHARSET=utf8 COMMENT='table "tbl_b"'
+PARTITION BY LIST COLUMNS(`id`) (
+PARTITION `pt1` DEFAULT COMMENT = 'srv "s_2_1"'
+);
+CREATE TABLE `tbl_c` (
+`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+`surname` varchar(255) DEFAULT NULL,
+PRIMARY KEY (`id`)
+) ENGINE=Spider DEFAULT CHARSET=utf8 COMMENT='table "tbl_c"'
+PARTITION BY LIST COLUMNS(`id`) (
+PARTITION `pt1` DEFAULT COMMENT = 'srv "s_2_1"'
+);
+SELECT * from tbl_b JOIN tbl_c ON tbl_b.id = tbl_c.id LEFT OUTER JOIN tbl_a ON tbl_a.id = tbl_b.id;
+id	last_name	id	surname	id	first_name
+1	DEMONGEOT	1	CON	1	RICHARD
+2	VAROQUI	2	MOYEN	2	STEPHANE
+connection master_1;
+DROP DATABASE auto_test_local;
+connection child2_1;
+DROP DATABASE auto_test_remote;
+for master_1
+for child2
+child2_1
+child2_2
+child2_3
+for child3
diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_24343.cnf b/storage/spider/mysql-test/spider/bugfix/t/mdev_24343.cnf
new file mode 100644
index 00000000000..05dfd8a0bce
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_24343.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_24343.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_24343.test
new file mode 100644
index 00000000000..5756cbac993
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_24343.test
@@ -0,0 +1,84 @@
+--echo #
+--echo # MDEV-24343 Spider Left join failed Unknown column 't0.ID' in 'on clause'
+--echo #
+
+--disable_query_log
+--disable_result_log
+--source ../t/test_init.inc
+--enable_result_log
+--enable_query_log
+
+--connection child2_1
+CREATE DATABASE auto_test_remote;
+USE auto_test_remote;
+
+eval CREATE TABLE tbl_a (
+    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+    `first_name` varchar(255) DEFAULT NULL,
+    PRIMARY KEY (`id`)
+) $CHILD2_1_ENGINE $CHILD2_1_CHARSET;
+
+INSERT INTO `tbl_a` VALUES (1,'RICHARD'), (2,'STEPHANE'), (3,'ALAIN');
+
+eval CREATE TABLE `tbl_b` (
+    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+    `last_name` varchar(255) DEFAULT NULL,
+    PRIMARY KEY (`ID`)
+) $CHILD2_1_ENGINE $CHILD2_1_CHARSET;
+
+INSERT INTO `tbl_b` VALUES (1,'DEMONGEOT'),(2,'VAROQUI');
+
+eval CREATE TABLE `tbl_c` (
+    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+    `surname` varchar(255) DEFAULT NULL,
+    PRIMARY KEY (`id`)
+) $CHILD2_1_ENGINE $CHILD2_1_CHARSET;
+
+INSERT INTO `tbl_c` VALUES (1,'CON'),(2,'MOYEN'),(3,'MOYEN2');
+
+SELECT * from tbl_b JOIN tbl_c ON tbl_b.id = tbl_c.id LEFT OUTER JOIN tbl_a ON tbl_a.id = tbl_b.id;
+
+--connection master_1
+CREATE DATABASE auto_test_local;
+USE auto_test_local;
+
+eval CREATE TABLE tbl_a (
+    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+    `first_name` varchar(255) DEFAULT NULL,
+    PRIMARY KEY (`id`)
+) $MASTER_1_ENGINE $MASTER_1_CHARSET COMMENT='table "tbl_a"'
+PARTITION BY LIST COLUMNS(`id`) (
+    PARTITION `pt1` DEFAULT COMMENT = 'srv "s_2_1"'
+);
+
+eval CREATE TABLE `tbl_b` (
+    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+    `last_name` varchar(255) DEFAULT NULL,
+    PRIMARY KEY (`id`)
+) $MASTER_1_ENGINE $MASTER_1_CHARSET COMMENT='table "tbl_b"'
+PARTITION BY LIST COLUMNS(`id`) (
+    PARTITION `pt1` DEFAULT COMMENT = 'srv "s_2_1"'
+);
+
+eval CREATE TABLE `tbl_c` (
+    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+    `surname` varchar(255) DEFAULT NULL,
+    PRIMARY KEY (`id`)
+) $MASTER_1_ENGINE $MASTER_1_CHARSET COMMENT='table "tbl_c"'
+PARTITION BY LIST COLUMNS(`id`) (
+    PARTITION `pt1` DEFAULT COMMENT = 'srv "s_2_1"'
+);
+
+SELECT * from tbl_b JOIN tbl_c ON tbl_b.id = tbl_c.id LEFT OUTER JOIN tbl_a ON tbl_a.id = tbl_b.id;
+
+--connection master_1
+DROP DATABASE auto_test_local;
+
+--connection child2_1
+DROP DATABASE auto_test_remote;
+
+--disable_query_log
+--disable_result_log
+--source ../t/test_deinit.inc
+--enable_result_log
+--enable_query_log
diff --git a/storage/spider/mysql-test/spider/r/direct_join.result b/storage/spider/mysql-test/spider/r/direct_join.result
index a1018c35fbf..36728ad5d45 100644
--- a/storage/spider/mysql-test/spider/r/direct_join.result
+++ b/storage/spider/mysql-test/spider/r/direct_join.result
@@ -76,7 +76,7 @@ a	b	c
 connection child2_1;
 SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
 argument
-select t0.`b` `b`,t0.`a` `a`,t2.`b` `b`,t2.`c` `c` from `auto_test_remote`.`ta_r` t0,`auto_test_remote`.`ta_r_3` t1,`auto_test_remote`.`ta_r_int` t2 where ((t0.`a` = t1.`a`) and (t2.`a` = t1.`a`)) order by t0.`b` desc limit 1,2
+select t0.`b` `b`,t0.`a` `a`,t2.`b` `b`,t2.`c` `c` from `auto_test_remote`.`ta_r` t0 join `auto_test_remote`.`ta_r_3` t1 join `auto_test_remote`.`ta_r_int` t2 where ((t0.`a` = t1.`a`) and (t2.`a` = t1.`a`)) order by t0.`b` desc limit 1,2
 SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'
 SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_r ORDER BY a;
 a	b	date_format(c, '%Y-%m-%d %H:%i:%s')
diff --git a/storage/spider/mysql-test/spider/r/direct_join_using.result b/storage/spider/mysql-test/spider/r/direct_join_using.result
index 66ae1503f9f..93df7b8cd3f 100644
--- a/storage/spider/mysql-test/spider/r/direct_join_using.result
+++ b/storage/spider/mysql-test/spider/r/direct_join_using.result
@@ -79,7 +79,7 @@ a	b	c
 connection child2_1;
 SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
 argument
-select t0.`b` `b`,t0.`a` `a`,t2.`b` `b`,t2.`c` `c` from `auto_test_remote`.`ta_r` t0,`auto_test_remote`.`ta_r_3` t1,`auto_test_remote`.`ta_r_int` t2 where ((t0.`a` = t1.`a`) and (t2.`a` = t1.`a`)) order by t0.`b` desc
+select t0.`b` `b`,t0.`a` `a`,t2.`b` `b`,t2.`c` `c` from `auto_test_remote`.`ta_r` t0 join `auto_test_remote`.`ta_r_3` t1 join `auto_test_remote`.`ta_r_int` t2 where ((t0.`a` = t1.`a`) and (t2.`a` = t1.`a`)) order by t0.`b` desc
 SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'
 SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_r ORDER BY a;
 a	b	date_format(c, '%Y-%m-%d %H:%i:%s')
diff --git a/storage/spider/spd_db_mysql.cc b/storage/spider/spd_db_mysql.cc
index ee3c25d914c..17dff093061 100644
--- a/storage/spider/spd_db_mysql.cc
+++ b/storage/spider/spd_db_mysql.cc
@@ -7057,25 +7057,13 @@ int spider_db_mbase_util::append_table(
     } else if (*current_pos > 0 && !first)
     {
       DBUG_PRINT("info",("spider no condition"));
-      if (top_down)
+      if (str)
       {
-        if (str)
+        if (str->reserve(SPIDER_SQL_JOIN_LEN))
         {
-          if (str->reserve(SPIDER_SQL_JOIN_LEN))
-          {
-            DBUG_RETURN(HA_ERR_OUT_OF_MEM);
-          }
-          str->q_append(SPIDER_SQL_JOIN_STR, SPIDER_SQL_JOIN_LEN);
-        }
-      } else {
-        if (str)
-        {
-          if (str->reserve(SPIDER_SQL_COMMA_LEN))
-          {
-            DBUG_RETURN(HA_ERR_OUT_OF_MEM);
-          }
-          str->q_append(SPIDER_SQL_COMMA_STR, SPIDER_SQL_COMMA_LEN);
+          DBUG_RETURN(HA_ERR_OUT_OF_MEM);
         }
+        str->q_append(SPIDER_SQL_JOIN_STR, SPIDER_SQL_JOIN_LEN);
       }
     }