MDEV-27581 Wrong result with DESC key on partitioned Spider table

take descending indexes into account when generating a query

also fixes MDEV-27617

5 files changed, 140 insertions, 37 deletions

diff --git a/mysql-test/main/myisam.result b/mysql-test/main/myisam.result
index 956f1558342..981131889e4 100644
--- a/mysql-test/main/myisam.result
+++ b/mysql-test/main/myisam.result
@@ -2796,5 +2796,17 @@ hex(c)	c	i
 C3A4	ä	1
 drop table t;
 #
+# MDEV-27617 HANDLER KEY > (x) does not work with DESC keys, Spider is affected and returns wrong results
+#
+create table t (a int, b char(1), primary key(a desc)) engine=myisam;
+insert into t VALUES (1,'f'),(2,'g'),(3,'j'), (4,'i'),(5,'h');
+handler t open;
+# MUST return 2,'g'. '>' always means READ_AFTER_KEY. desc or not.
+handler t read `primary` > (3);
+a	b
+2	g
+handler t close;
+drop table t;
+#
 # End of 10.8 tests
 #
diff --git a/mysql-test/main/myisam.test b/mysql-test/main/myisam.test
index 7509a23cd09..c087af7b9a9 100644
--- a/mysql-test/main/myisam.test
+++ b/mysql-test/main/myisam.test
@@ -1896,5 +1896,16 @@ select hex(c),c,i from t order by c, i;
 drop table t;
 
 --echo #
+--echo # MDEV-27617 HANDLER KEY > (x) does not work with DESC keys, Spider is affected and returns wrong results
+--echo #
+create table t (a int, b char(1), primary key(a desc)) engine=myisam;
+insert into t VALUES (1,'f'),(2,'g'),(3,'j'), (4,'i'),(5,'h');
+handler t open;
+--echo # MUST return 2,'g'. '>' always means READ_AFTER_KEY. desc or not.
+handler t read `primary` > (3);
+handler t close;
+drop table t;
+
+--echo #
 --echo # End of 10.8 tests
 --echo #
diff --git a/storage/spider/mysql-test/spider/bugfix/r/index.result b/storage/spider/mysql-test/spider/bugfix/r/index.result
index ddc1af42a12..3ef02f23448 100644
--- a/storage/spider/mysql-test/spider/bugfix/r/index.result
+++ b/storage/spider/mysql-test/spider/bugfix/r/index.result
@@ -38,6 +38,39 @@ id
 5
 6
 drop table t_sp1, auto_test_local.t;
+#
+# MDEV-27581 Wrong result with DESC key on partitioned Spider table
+#
+create table auto_test_local.t1 (a int primary key);
+create table auto_test_local.t2 (a int primary key);
+create table t (a int, primary key (a desc)) engine=spider
+engine=spider comment='wrapper "mysql", srv "s_1"'
+partition by range (a)
+(partition p1 values less than (4) comment "table 't1'",
+partition p2 values less than (maxvalue) comment "table 't2'");
+insert into t values (1),(2),(10),(11);
+select * from t where a > 1 order by a;
+a
+2
+10
+11
+drop table t, auto_test_local.t1, auto_test_local.t2;
+#
+# MDEV-27617 HANDLER KEY > (x) does not work with DESC keys, Spider is affected and returns wrong results
+#
+create table auto_test_local.t (a int, b char(1), primary key(a desc));
+insert into auto_test_local.t VALUES (1,'f'),(2,'g'),(3,'j'), (4,'i'),(5,'h');
+create table ts (a int, b char(1), primary key(a desc))
+engine=spider comment='wrapper "mysql", srv "s_1", table "t"';
+set spider_use_handler=3;
+select a, b from ts where a > 0 and b = 'g' order by a;
+a	b
+2	g
+set spider_use_handler=default;
+select a, b from ts where a > 0 and b = 'g' order by a;
+a	b
+2	g
+drop table ts, auto_test_local.t;
 drop database auto_test_local;
 for master_1
 connection master_1;
diff --git a/storage/spider/mysql-test/spider/bugfix/t/index.test b/storage/spider/mysql-test/spider/bugfix/t/index.test
index 5efd58c2958..5167e40e8c4 100644
--- a/storage/spider/mysql-test/spider/bugfix/t/index.test
+++ b/storage/spider/mysql-test/spider/bugfix/t/index.test
@@ -5,7 +5,7 @@ set spider_same_server_link= on;
 --echo #
 --echo # MDEV-27590 Auto-increment on Spider tables with DESC PK does not work properly
 --echo #
- 
+
 select @@spider_auto_increment_mode;
 create or replace table auto_test_local.t (id int primary key) engine=InnoDB;
 create or replace table t_sp1 (id int auto_increment, primary key(id desc))
@@ -15,6 +15,34 @@ insert into t_sp1 () values (),(),();
 select * from t_sp1;
 drop table t_sp1, auto_test_local.t;
 
+--echo #
+--echo # MDEV-27581 Wrong result with DESC key on partitioned Spider table
+--echo #
+create table auto_test_local.t1 (a int primary key);
+create table auto_test_local.t2 (a int primary key);
+create table t (a int, primary key (a desc)) engine=spider
+engine=spider comment='wrapper "mysql", srv "s_1"'
+partition by range (a)
+(partition p1 values less than (4) comment "table 't1'",
+ partition p2 values less than (maxvalue) comment "table 't2'");
+
+insert into t values (1),(2),(10),(11);
+select * from t where a > 1 order by a;
+drop table t, auto_test_local.t1, auto_test_local.t2;
+
+--echo #
+--echo # MDEV-27617 HANDLER KEY > (x) does not work with DESC keys, Spider is affected and returns wrong results
+--echo #
+
+create table auto_test_local.t (a int, b char(1), primary key(a desc));
+insert into auto_test_local.t VALUES (1,'f'),(2,'g'),(3,'j'), (4,'i'),(5,'h');
+create table ts (a int, b char(1), primary key(a desc))
+engine=spider comment='wrapper "mysql", srv "s_1", table "t"';
+set spider_use_handler=3;
+select a, b from ts where a > 0 and b = 'g' order by a;
+set spider_use_handler=default;
+select a, b from ts where a > 0 and b = 'g' order by a;
+drop table ts, auto_test_local.t;
 
 drop database auto_test_local;
 --source ../t/test_deinit.inc
diff --git a/storage/spider/spd_db_conn.cc b/storage/spider/spd_db_conn.cc
index ca19412c195..549279fb180 100644
--- a/storage/spider/spd_db_conn.cc
+++ b/storage/spider/spd_db_conn.cc
@@ -1732,6 +1732,7 @@ int spider_db_append_key_where_internal(
     key_count++
   ) {
     DBUG_PRINT("info", ("spider tgt_key_part_map=%lu", tgt_key_part_map));
+    bool rev = key_part->key_part_flag & HA_REVERSE_SORT;
     store_length = key_part->store_length;
     field = key_part->field;
     key_name_length = dbton_share->get_column_name_length(field->field_index);
@@ -1901,11 +1902,11 @@ int spider_db_append_key_where_internal(
               const char* op_str;
               uint32 op_len;
               if (start_key_part_map == 1) {
-                op_str = SPIDER_SQL_GT_STR;
-                op_len = SPIDER_SQL_GT_LEN;
+                op_str = rev ? SPIDER_SQL_LT_STR : SPIDER_SQL_GT_STR;
+                op_len = rev ? SPIDER_SQL_LT_LEN : SPIDER_SQL_GT_LEN;
               } else {
-                op_str = SPIDER_SQL_GTEQUAL_STR;
-                op_len = SPIDER_SQL_GTEQUAL_LEN;
+                op_str = rev ? SPIDER_SQL_LTEQUAL_STR : SPIDER_SQL_GTEQUAL_STR;
+                op_len = rev ? SPIDER_SQL_LTEQUAL_LEN : SPIDER_SQL_GTEQUAL_LEN;
               }
               if (str->reserve(store_length + key_name_length +
                 /* SPIDER_SQL_NAME_QUOTE_LEN */ 2 + op_len))
@@ -1930,7 +1931,10 @@ int spider_db_append_key_where_internal(
                 SPIDER_SQL_GT_LEN))
                 DBUG_RETURN(HA_ERR_OUT_OF_MEM);
               dbton_share->append_column_name(str_part2, field->field_index);
-              str_part2->q_append(SPIDER_SQL_GT_STR, SPIDER_SQL_GT_LEN);
+              if (rev)
+                str_part2->q_append(SPIDER_SQL_LT_STR, SPIDER_SQL_LT_LEN);
+              else
+                str_part2->q_append(SPIDER_SQL_GT_STR, SPIDER_SQL_GT_LEN);
               if (spider_dbton[dbton_id].db_util->
                 append_column_value(spider, str_part2, field, ptr,
                   share->access_charset))
@@ -1971,11 +1975,11 @@ int spider_db_append_key_where_internal(
               const char* op_str;
               uint32 op_len;
               if (start_key_part_map == 1) {
-                op_str = SPIDER_SQL_LT_STR;
-                op_len = SPIDER_SQL_LT_LEN;
+                op_str = rev ? SPIDER_SQL_GT_STR : SPIDER_SQL_LT_STR;
+                op_len = rev ? SPIDER_SQL_GT_LEN : SPIDER_SQL_LT_LEN;
               } else {
-                op_str = SPIDER_SQL_LTEQUAL_STR;
-                op_len = SPIDER_SQL_LTEQUAL_LEN;
+                op_str = rev ? SPIDER_SQL_GTEQUAL_STR : SPIDER_SQL_LTEQUAL_STR;
+                op_len = rev ? SPIDER_SQL_GTEQUAL_LEN : SPIDER_SQL_LTEQUAL_LEN;
               }
               if (str->reserve(store_length + key_name_length +
                 /* SPIDER_SQL_NAME_QUOTE_LEN */ 2 + op_len))
@@ -2000,7 +2004,10 @@ int spider_db_append_key_where_internal(
                 SPIDER_SQL_LT_LEN))
                 DBUG_RETURN(HA_ERR_OUT_OF_MEM);
               dbton_share->append_column_name(str_part2, field->field_index);
-              str_part2->q_append(SPIDER_SQL_LT_STR, SPIDER_SQL_LT_LEN);
+              if (rev)
+                str_part2->q_append(SPIDER_SQL_GT_STR, SPIDER_SQL_GT_LEN);
+              else
+                str_part2->q_append(SPIDER_SQL_LT_STR, SPIDER_SQL_LT_LEN);
               if (spider_dbton[dbton_id].db_util->
                 append_column_value(spider, str_part2, field, ptr,
                   share->access_charset))
@@ -2050,7 +2057,10 @@ int spider_db_append_key_where_internal(
                 SPIDER_SQL_LTEQUAL_LEN))
                 DBUG_RETURN(HA_ERR_OUT_OF_MEM);
               dbton_share->append_column_name(str, field->field_index);
-              str->q_append(SPIDER_SQL_LTEQUAL_STR, SPIDER_SQL_LTEQUAL_LEN);
+              if (rev)
+                str->q_append(SPIDER_SQL_GTEQUAL_STR, SPIDER_SQL_GTEQUAL_LEN);
+              else
+                str->q_append(SPIDER_SQL_LTEQUAL_STR, SPIDER_SQL_LTEQUAL_LEN);
               if (spider_dbton[dbton_id].db_util->
                 append_column_value(spider, str, field, ptr,
                   share->access_charset))
@@ -2067,8 +2077,10 @@ int spider_db_append_key_where_internal(
                 SPIDER_SQL_LTEQUAL_LEN))
                 DBUG_RETURN(HA_ERR_OUT_OF_MEM);
               dbton_share->append_column_name(str_part2, field->field_index);
-              str_part2->q_append(SPIDER_SQL_LTEQUAL_STR,
-                SPIDER_SQL_LTEQUAL_LEN);
+              if (rev)
+                str_part2->q_append(SPIDER_SQL_GTEQUAL_STR, SPIDER_SQL_GTEQUAL_LEN);
+              else
+                str_part2->q_append(SPIDER_SQL_LTEQUAL_STR, SPIDER_SQL_LTEQUAL_LEN);
               if (spider_dbton[dbton_id].db_util->
                 append_column_value(spider, str_part2, field, ptr,
                   share->access_charset))
@@ -2081,8 +2093,7 @@ int spider_db_append_key_where_internal(
                 {
                   if (str->reserve(SPIDER_SQL_LTEQUAL_LEN))
                     DBUG_RETURN(HA_ERR_OUT_OF_MEM);
-                  str->q_append(SPIDER_SQL_LTEQUAL_STR,
-                    SPIDER_SQL_LTEQUAL_LEN);
+                  str->q_append(SPIDER_SQL_LTEQUAL_STR, SPIDER_SQL_LTEQUAL_LEN);
                 }
                 if (spider_dbton[dbton_id].db_util->
                   append_column_value(spider, str_part, field, ptr,
@@ -2093,8 +2104,7 @@ int spider_db_append_key_where_internal(
                 {
                   if (str->reserve(SPIDER_SQL_LTEQUAL_LEN))
                     DBUG_RETURN(HA_ERR_OUT_OF_MEM);
-                  str->q_append(SPIDER_SQL_LTEQUAL_STR,
-                    SPIDER_SQL_LTEQUAL_LEN);
+                  str->q_append(SPIDER_SQL_LTEQUAL_STR, SPIDER_SQL_LTEQUAL_LEN);
                   if (spider_dbton[dbton_id].db_util->
                     append_column_value(spider, str_part, field, ptr,
                       share->access_charset))
@@ -2201,7 +2211,10 @@ int spider_db_append_key_where_internal(
                 SPIDER_SQL_GTEQUAL_LEN))
                 DBUG_RETURN(HA_ERR_OUT_OF_MEM);
               dbton_share->append_column_name(str, field->field_index);
-              str->q_append(SPIDER_SQL_GTEQUAL_STR, SPIDER_SQL_GTEQUAL_LEN);
+              if (rev)
+                str->q_append(SPIDER_SQL_LTEQUAL_STR, SPIDER_SQL_LTEQUAL_LEN);
+              else
+                str->q_append(SPIDER_SQL_GTEQUAL_STR, SPIDER_SQL_GTEQUAL_LEN);
               if (spider_dbton[dbton_id].db_util->
                 append_column_value(spider, str, field, ptr,
                   share->access_charset))
@@ -2218,8 +2231,10 @@ int spider_db_append_key_where_internal(
                 SPIDER_SQL_GTEQUAL_LEN))
                 DBUG_RETURN(HA_ERR_OUT_OF_MEM);
               dbton_share->append_column_name(str_part2, field->field_index);
-              str_part2->q_append(SPIDER_SQL_GTEQUAL_STR,
-                SPIDER_SQL_GTEQUAL_LEN);
+              if (rev)
+                str_part2->q_append(SPIDER_SQL_LTEQUAL_STR, SPIDER_SQL_LTEQUAL_LEN);
+              else
+                str_part2->q_append(SPIDER_SQL_GTEQUAL_STR, SPIDER_SQL_GTEQUAL_LEN);
               if (spider_dbton[dbton_id].db_util->
                 append_column_value(spider, str_part2, field, ptr,
                   share->access_charset))
@@ -2232,8 +2247,7 @@ int spider_db_append_key_where_internal(
                 {
                   if (str->reserve(SPIDER_SQL_GTEQUAL_LEN))
                     DBUG_RETURN(HA_ERR_OUT_OF_MEM);
-                  str->q_append(SPIDER_SQL_GTEQUAL_STR,
-                    SPIDER_SQL_GTEQUAL_LEN);
+                  str->q_append(SPIDER_SQL_GTEQUAL_STR, SPIDER_SQL_GTEQUAL_LEN);
                 }
                 if (spider_dbton[dbton_id].db_util->
                   append_column_value(spider, str_part, field, ptr,
@@ -2244,8 +2258,7 @@ int spider_db_append_key_where_internal(
                 {
                   if (str->reserve(SPIDER_SQL_GTEQUAL_LEN))
                     DBUG_RETURN(HA_ERR_OUT_OF_MEM);
-                  str->q_append(SPIDER_SQL_GTEQUAL_STR,
-                    SPIDER_SQL_GTEQUAL_LEN);
+                  str->q_append(SPIDER_SQL_GTEQUAL_STR, SPIDER_SQL_GTEQUAL_LEN);
                   if (spider_dbton[dbton_id].db_util->
                     append_column_value(spider, str_part, field, ptr,
                       share->access_charset))
@@ -2358,11 +2371,11 @@ int spider_db_append_key_where_internal(
                 const char* op_str;
                 uint32 op_len;
                 if (end_key_part_map == 1) {
-                  op_str = SPIDER_SQL_LT_STR;
-                  op_len = SPIDER_SQL_LT_LEN;
+                  op_str = rev ? SPIDER_SQL_GT_STR : SPIDER_SQL_LT_STR;
+                  op_len = rev ? SPIDER_SQL_GT_LEN : SPIDER_SQL_LT_LEN;
                 } else {
-                  op_str = SPIDER_SQL_LTEQUAL_STR;
-                  op_len = SPIDER_SQL_LTEQUAL_LEN;
+                  op_str = rev ? SPIDER_SQL_GTEQUAL_STR : SPIDER_SQL_LTEQUAL_STR;
+                  op_len = rev ? SPIDER_SQL_GTEQUAL_LEN : SPIDER_SQL_LTEQUAL_LEN;
                 }
                 if (str->reserve(store_length + key_name_length +
                   /* SPIDER_SQL_NAME_QUOTE_LEN */ 2 + op_len))
@@ -2386,7 +2399,10 @@ int spider_db_append_key_where_internal(
                   SPIDER_SQL_LT_LEN))
                   DBUG_RETURN(HA_ERR_OUT_OF_MEM);
                 dbton_share->append_column_name(str_part2, field->field_index);
-                str_part2->q_append(SPIDER_SQL_LT_STR, SPIDER_SQL_LT_LEN);
+                if (rev)
+                  str_part2->q_append(SPIDER_SQL_GT_STR, SPIDER_SQL_GT_LEN);
+                else
+                  str_part2->q_append(SPIDER_SQL_LT_STR, SPIDER_SQL_LT_LEN);
                 if (spider_dbton[dbton_id].db_util->
                   append_column_value(spider, str_part2, field, ptr,
                     share->access_charset))
@@ -2428,7 +2444,10 @@ int spider_db_append_key_where_internal(
                   SPIDER_SQL_LTEQUAL_LEN))
                   DBUG_RETURN(HA_ERR_OUT_OF_MEM);
                 dbton_share->append_column_name(str, field->field_index);
-                str->q_append(SPIDER_SQL_LTEQUAL_STR, SPIDER_SQL_LTEQUAL_LEN);
+                if (rev)
+                  str->q_append(SPIDER_SQL_GTEQUAL_STR, SPIDER_SQL_GTEQUAL_LEN);
+                else
+                  str->q_append(SPIDER_SQL_LTEQUAL_STR, SPIDER_SQL_LTEQUAL_LEN);
                 if (spider_dbton[dbton_id].db_util->
                   append_column_value(spider, str, field, ptr,
                     share->access_charset))
@@ -2444,8 +2463,10 @@ int spider_db_append_key_where_internal(
                   SPIDER_SQL_LTEQUAL_LEN))
                   DBUG_RETURN(HA_ERR_OUT_OF_MEM);
                 dbton_share->append_column_name(str_part2, field->field_index);
-                str_part2->q_append(SPIDER_SQL_LTEQUAL_STR,
-                  SPIDER_SQL_LTEQUAL_LEN);
+                if (rev)
+                  str_part2->q_append(SPIDER_SQL_GTEQUAL_STR, SPIDER_SQL_GTEQUAL_LEN);
+                else
+                  str_part2->q_append(SPIDER_SQL_LTEQUAL_STR, SPIDER_SQL_LTEQUAL_LEN);
                 if (spider_dbton[dbton_id].db_util->
                   append_column_value(spider, str_part2, field, ptr,
                     share->access_charset))
@@ -2458,8 +2479,7 @@ int spider_db_append_key_where_internal(
                   {
                     if (str->reserve(SPIDER_SQL_LTEQUAL_LEN))
                       DBUG_RETURN(HA_ERR_OUT_OF_MEM);
-                    str->q_append(SPIDER_SQL_LTEQUAL_STR,
-                      SPIDER_SQL_LTEQUAL_LEN);
+                    str->q_append(SPIDER_SQL_LTEQUAL_STR, SPIDER_SQL_LTEQUAL_LEN);
                   }
                   if (spider_dbton[dbton_id].db_util->
                     append_column_value(spider, str_part, field, ptr,
@@ -2470,8 +2490,7 @@ int spider_db_append_key_where_internal(
                   {
                     if (str->reserve(SPIDER_SQL_LTEQUAL_LEN))
                       DBUG_RETURN(HA_ERR_OUT_OF_MEM);
-                    str->q_append(SPIDER_SQL_LTEQUAL_STR,
-                      SPIDER_SQL_LTEQUAL_LEN);
+                    str->q_append(SPIDER_SQL_LTEQUAL_STR, SPIDER_SQL_LTEQUAL_LEN);
                     if (spider_dbton[dbton_id].db_util->
                       append_column_value(spider, str_part, field, ptr,
                         share->access_charset))