MDEV-26247 Spider: Valid LEFT JOIN results in ERROR 1064bb-10.3-MDEV-26247

The current implementation of Spider's GROUP BY handler cannot
handle some type of queries yet. Thus, we disable the handler for
the queries.

This is tentative fix and more and more queries should be handled
by smart handlers.

4 files changed, 139 insertions, 1 deletions

diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_26247.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_26247.result
new file mode 100644
index 00000000000..b405021a6b9
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_26247.result
@@ -0,0 +1,55 @@
+#
+# MDEV-26247 Spider: Valid LEFT JOIN results in ERROR 1064
+#
+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 (
+a int,
+primary key (a)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+CREATE TABLE tbl_b (
+c int,
+d int,
+primary key (c, d)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO tbl_a VALUES (1), (2), (3);
+INSERT INTO tbl_b VALUES (1, 11), (2, 22), (3, 33);
+connection master_1;
+CREATE DATABASE auto_test_local;
+USE auto_test_local;
+CREATE TABLE tbl_a (
+a int,
+primary key (a)
+) ENGINE=Spider DEFAULT CHARSET=utf8 COMMENT='srv "s_2_1", table "tbl_a"';
+CREATE TABLE tbl_b (
+c int,
+d int,
+primary key (c, d)
+) ENGINE=Spider DEFAULT CHARSET=utf8 COMMENT='srv "s_2_1", table "tbl_b"';
+SELECT d FROM tbl_a LEFT JOIN tbl_b ON a = c WHERE a IN (1);
+d
+11
+SELECT d FROM tbl_b RIGHT JOIN tbl_a ON c = a WHERE a IN (1);
+d
+11
+SELECT d FROM tbl_b RIGHT JOIN tbl_a ON c = a WHERE a IN (1,2);
+d
+11
+22
+connection master_1;
+DROP DATABASE IF EXISTS auto_test_local;
+connection child2_1;
+DROP DATABASE IF EXISTS 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_26247.cnf b/storage/spider/mysql-test/spider/bugfix/t/mdev_26247.cnf
new file mode 100644
index 00000000000..05dfd8a0bce
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_26247.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_26247.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_26247.test
new file mode 100644
index 00000000000..fb92066530e
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_26247.test
@@ -0,0 +1,57 @@
+--echo #
+--echo # MDEV-26247 Spider: Valid LEFT JOIN results in ERROR 1064
+--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 (
+  a int,
+  primary key (a)
+) $CHILD2_1_ENGINE $CHILD2_1_CHARSET;
+
+eval CREATE TABLE tbl_b (
+  c int,
+  d int,
+  primary key (c, d)
+) $CHILD2_1_ENGINE $CHILD2_1_CHARSET;
+
+INSERT INTO tbl_a VALUES (1), (2), (3);
+INSERT INTO tbl_b VALUES (1, 11), (2, 22), (3, 33);
+
+--connection master_1
+CREATE DATABASE auto_test_local;
+USE auto_test_local;
+
+eval CREATE TABLE tbl_a (
+  a int,
+  primary key (a)
+) $MASTER_1_ENGINE $MASTER_1_CHARSET COMMENT='srv "s_2_1", table "tbl_a"';
+
+eval CREATE TABLE tbl_b (
+  c int,
+  d int,
+  primary key (c, d)
+) $MASTER_1_ENGINE $MASTER_1_CHARSET COMMENT='srv "s_2_1", table "tbl_b"';
+
+SELECT d FROM tbl_a LEFT JOIN tbl_b ON a = c WHERE a IN (1);
+SELECT d FROM tbl_b RIGHT JOIN tbl_a ON c = a WHERE a IN (1);
+SELECT d FROM tbl_b RIGHT JOIN tbl_a ON c = a WHERE a IN (1,2);
+
+--connection master_1
+DROP DATABASE IF EXISTS auto_test_local;
+--connection child2_1
+DROP DATABASE IF EXISTS auto_test_remote;
+
+--disable_query_log
+--disable_result_log
+--source ../t/test_deinit.inc
+--enable_query_log
+--enable_result_log
diff --git a/storage/spider/spd_group_by_handler.cc b/storage/spider/spd_group_by_handler.cc
index de041897239..fefe3dba0e0 100644
--- a/storage/spider/spd_group_by_handler.cc
+++ b/storage/spider/spd_group_by_handler.cc
@@ -1672,8 +1672,31 @@ group_by_handler *spider_create_group_by_handler(
   } while ((from = from->next_local));
 #endif
 
-  table_idx = 0;
   from = query->from;
+  if ((from && from->next_local && !from->next_local->next_local) &&
+      (from->table->const_table || from->next_local->table->const_table))
+  {
+    /*
+      Spider's GROUP BY handler does not support this case yet. However, this
+      wouldn't be a big problem.
+
+      Only two table is involved in the query->from table list and one of them
+      is const table. Then, Spider only need to scan a single table.
+    */
+    DBUG_RETURN(nullptr);
+  }
+
+  if (!from->embedding &&
+      (from->outer_join || from->on_expr || from->join_using_fields))
+  {
+    /*
+      Spider's GROUP BY handler does not support this case yet too.
+    */
+    DBUG_RETURN(nullptr);
+  }
+
+  table_idx= 0;
+  from= query->from;
   while (from && from->table->const_table)
   {
     from = from->next_local;