MariaDB [SANDBOX]>  -- First join TEST_RDT_2 AND then TEST_RDT_3 : does not works

MariaDB [SANDBOX]> SELECT * from TEST_RDT_2, TEST_RDT_3 LEFT OUTER JOIN TEST_RDT_1 ON TEST_RDT_1.ID = TEST_RDT_2.ID WHERE TEST_RDT_2.ID = TEST_RDT_3.ID;

ERROR 1054 (42S22): Unknown column 'TEST_RDT_2.ID' in 'on clause'

MariaDB [SANDBOX]> SELECT * FROM TEST_RDT_2;

+----+-----------+

| ID | LASTNAME  |

+----+-----------+

|  1 | DEMONGEOT |

|  2 | VAROQUI   |

+----+-----------+

2 rows in set (0.006 sec)

MariaDB [SANDBOX]>  -- First join TEST_RDT_3 AND then TEST_RDT_2 works.

MariaDB [SANDBOX]> SELECT * from TEST_RDT_3, TEST_RDT_2 LEFT OUTER JOIN TEST_RDT_1 ON TEST_RDT_1.ID = TEST_RDT_2.ID WHERE TEST_RDT_2.ID = TEST_RDT_3.ID;

+----+---------+----+-----------+------+-----------+

| ID | SURNAME | ID | LASTNAME  | ID   | FIRSTNAME |

+----+---------+----+-----------+------+-----------+

|  1 | CON     |  1 | DEMONGEOT |    1 | RICHARD   |

|  2 | MOYEN   |  2 | VAROQUI   |    2 | STEPHANE  |

+----+---------+----+-----------+------+-----------+

2 rows in set (0.009 sec)

MariaDB [SANDBOX]> SELECT * from TEST_RDT_3 JOIN TEST_RDT_2 ON TEST_RDT_2.ID = TEST_RDT_3.ID LEFT OUTER JOIN TEST_RDT_1 ON TEST_RDT_1.ID = TEST_RDT_2.ID;

+----+---------+----+-----------+------+-----------+

| ID | SURNAME | ID | LASTNAME  | ID   | FIRSTNAME |

+----+---------+----+-----------+------+-----------+

|  1 | CON     |  1 | DEMONGEOT |    1 | RICHARD   |

|  2 | MOYEN   |  2 | VAROQUI   |    2 | STEPHANE  |

+----+---------+----+-----------+------+-----------+

2 rows in set (0.009 sec)

MariaDB [SANDBOX]> -- With a subqueries (but can't use SELECT *, loosing an ID)

MariaDB [SANDBOX]> SELECT * FROM ( SELECT TEST_RDT_2.ID, SURNAME, LASTNAME FROM TEST_RDT_2 JOIN TEST_RDT_3 ON TEST_RDT_2.ID = TEST_RDT_3.ID) A LEFT OUTER JOIN TEST_RDT_1 ON TEST_RDT_1.ID = A.ID; 

+----+---------+-----------+------+-----------+

| ID | SURNAME | LASTNAME  | ID   | FIRSTNAME |

+----+---------+-----------+------+-----------+

|  1 | CON     | DEMONGEOT |    1 | RICHARD   |

|  2 | MOYEN   | VAROQUI   |    2 | STEPHANE  |

+----+---------+-----------+------+-----------+

2 rows in set (0.011 sec)

MariaDB [SANDBOX]> 

info: spider query=select t0.`ID` `ID`,t0.`LASTNAME` `LASTNAME`,t1.`ID` `ID`,t1.`SURNAME` `SURNAME`,t2.`ID` `ID`,t2.`FIRSTNAME` `FIRSTNAME` from `test`.`TEST_RDT_2` t0,`test`.`TEST_RDT_3` t1 left join `test`.`TEST_RDT_1` t2 on (t2.`ID` = t0.`ID`) where (t1.`ID` = t0.`ID`)

--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');

--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/spd_db_mysql.cc b/storage/spider/spd_db_mysql.cc

index ee3c25d914c..b0f868110fd 100644

--- a/storage/spider/spd_db_mysql.cc

+++ b/storage/spider/spd_db_mysql.cc

@@ -7070,11 +7070,11 @@ int spider_db_mbase_util::append_table(

       } else {

         if (str)

         {

-          if (str->reserve(SPIDER_SQL_COMMA_LEN))

+          if (str->reserve(SPIDER_SQL_JOIN_LEN))

           {

             DBUG_RETURN(HA_ERR_OUT_OF_MEM);

           }

-          str->q_append(SPIDER_SQL_COMMA_STR, SPIDER_SQL_COMMA_LEN);

+          str->q_append(SPIDER_SQL_JOIN_STR, SPIDER_SQL_JOIN_LEN);

         }

       }

     }