MDEV-27434 DESC attribute does not work with auto-increment on secondary column of multi-part index

when searching for the last auto-inc value, it's HA_READ_PREFIX_LAST for
the ASC keypart, but HA_READ_PREFIX for the DESC one

also fixes MDEV-27585

6 files changed, 88 insertions, 12 deletions

diff --git a/mysql-test/main/myisam.result b/mysql-test/main/myisam.result
index e9b86e8454e..956f1558342 100644
--- a/mysql-test/main/myisam.result
+++ b/mysql-test/main/myisam.result
@@ -1,6 +1,5 @@
 call mtr.add_suppression("Can't find record in '.*'");
 call mtr.add_suppression("Table 't1' is marked as crashed and should be repaired");
-drop table if exists t1,t2,t3;
 SET SQL_WARNINGS=1;
 CREATE TABLE t1 (
 STRING_DATA char(255) default NULL,
@@ -2776,5 +2775,26 @@ bar
 NULL
 drop table t;
 #
+# MDEV-27434 DESC attribute does not work with auto-increment on secondary column of multi-part index
+#
+create table t (a int auto_increment, b int, unique(b,a desc)) engine=myisam;
+insert ignore into t (b) values (10),(10),(10);
+select * from t;
+a	b
+3	10
+2	10
+1	10
+drop table t;
+#
+# MDEV-27585 Auto-increment on secondary column increments unexpectedly with DESC key
+#
+create table t (c char(16), i int auto_increment, index (c,i desc)) engine=myisam collate latin1_swedish_ci;
+insert into t (c) values ('ä'),('a');
+select hex(c),c,i from t order by c, i;
+hex(c)	c	i
+61	a	1
+C3A4	ä	1
+drop table t;
+#
 # End of 10.8 tests
 #
diff --git a/mysql-test/main/myisam.test b/mysql-test/main/myisam.test
index 09f7dd441ea..7509a23cd09 100644
--- a/mysql-test/main/myisam.test
+++ b/mysql-test/main/myisam.test
@@ -8,9 +8,6 @@ call mtr.add_suppression("Can't find record in '.*'");
 call mtr.add_suppression("Table 't1' is marked as crashed and should be repaired");
 
 # Initialise
---disable_warnings
-drop table if exists t1,t2,t3;
---enable_warnings
 SET SQL_WARNINGS=1;
 
 #
@@ -1883,5 +1880,21 @@ select c from t;
 drop table t;
 
 --echo #
+--echo # MDEV-27434 DESC attribute does not work with auto-increment on secondary column of multi-part index
+--echo #
+create table t (a int auto_increment, b int, unique(b,a desc)) engine=myisam;
+insert ignore into t (b) values (10),(10),(10);
+select * from t;
+drop table t;
+
+--echo #
+--echo # MDEV-27585 Auto-increment on secondary column increments unexpectedly with DESC key
+--echo #
+create table t (c char(16), i int auto_increment, index (c,i desc)) engine=myisam collate latin1_swedish_ci;
+insert into t (c) values ('ä'),('a');
+select hex(c),c,i from t order by c, i;
+drop table t;
+
+--echo #
 --echo # End of 10.8 tests
 --echo #
diff --git a/mysql-test/suite/maria/maria2.result b/mysql-test/suite/maria/maria2.result
index 2480369744f..c25659ae1f8 100644
--- a/mysql-test/suite/maria/maria2.result
+++ b/mysql-test/suite/maria/maria2.result
@@ -1,4 +1,3 @@
-drop table if exists t1,t2;
 CREATE TABLE t1 (
 line BLOB,
 kind ENUM('po', 'pp', 'rr', 'dr', 'rd', 'ts', 'cl') NOT NULL DEFAULT 'po',
@@ -141,5 +140,26 @@ bar
 NULL
 drop table t;
 #
+# MDEV-27434 DESC attribute does not work with auto-increment on secondary column of multi-part index
+#
+create table t (a int auto_increment, b int, unique(b,a desc)) engine=aria;
+insert ignore into t (b) values (10),(10),(10);
+select * from t;
+a	b
+3	10
+2	10
+1	10
+drop table t;
+#
+# MDEV-27585 Auto-increment on secondary column increments unexpectedly with DESC key
+#
+create table t (c char(16), i int auto_increment, index (c,i desc)) engine=aria collate latin1_swedish_ci;
+insert into t (c) values ('ä'),('a');
+select hex(c),c,i from t order by c, i;
+hex(c)	c	i
+61	a	1
+C3A4	ä	1
+drop table t;
+#
 # End of 10.8 tests
 #
diff --git a/mysql-test/suite/maria/maria2.test b/mysql-test/suite/maria/maria2.test
index 565f74b1296..0f4a50ad99d 100644
--- a/mysql-test/suite/maria/maria2.test
+++ b/mysql-test/suite/maria/maria2.test
@@ -1,10 +1,5 @@
 --source include/have_maria.inc
 
-# Initialise
---disable_warnings
-drop table if exists t1,t2;
---enable_warnings
-
 # Test for BUG#36319
 # "Aria: table is not empty but DELETE and SELECT find no rows"
 
@@ -166,5 +161,21 @@ select c from t;
 drop table t;
 
 --echo #
+--echo # MDEV-27434 DESC attribute does not work with auto-increment on secondary column of multi-part index
+--echo #
+create table t (a int auto_increment, b int, unique(b,a desc)) engine=aria;
+insert ignore into t (b) values (10),(10),(10);
+select * from t;
+drop table t;
+
+--echo #
+--echo # MDEV-27585 Auto-increment on secondary column increments unexpectedly with DESC key
+--echo #
+create table t (c char(16), i int auto_increment, index (c,i desc)) engine=aria collate latin1_swedish_ci;
+insert into t (c) values ('ä'),('a');
+select hex(c),c,i from t order by c, i;
+drop table t;
+
+--echo #
 --echo # End of 10.8 tests
 --echo #
diff --git a/storage/maria/ha_maria.cc b/storage/maria/ha_maria.cc
index 6ee0dd5af79..809c6409131 100644
--- a/storage/maria/ha_maria.cc
+++ b/storage/maria/ha_maria.cc
@@ -3342,6 +3342,7 @@ void ha_maria::get_auto_increment(ulonglong offset, ulonglong increment,
   ulonglong nr;
   int error;
   uchar key[MARIA_MAX_KEY_BUFF];
+  enum ha_rkey_function search_flag= HA_READ_PREFIX_LAST;
 
   if (!table->s->next_number_key_offset)
   {                                             // Autoincrement at key-start
@@ -3355,13 +3356,18 @@ void ha_maria::get_auto_increment(ulonglong offset, ulonglong increment,
   /* it's safe to call the following if bulk_insert isn't on */
   maria_flush_bulk_insert(file, table->s->next_number_index);
 
+  if (unlikely(table->key_info[table->s->next_number_index].
+                  key_part[table->s->next_number_keypart].key_part_flag &
+                    HA_REVERSE_SORT))
+    search_flag= HA_READ_KEY_EXACT;
+
   (void) extra(HA_EXTRA_KEYREAD);
   key_copy(key, table->record[0],
            table->key_info + table->s->next_number_index,
            table->s->next_number_key_offset);
   error= maria_rkey(file, table->record[1], (int) table->s->next_number_index,
                     key, make_prev_keypart_map(table->s->next_number_keypart),
-                    HA_READ_PREFIX_LAST);
+                    search_flag);
   if (error)
     nr= 1;
   else
diff --git a/storage/myisam/ha_myisam.cc b/storage/myisam/ha_myisam.cc
index 7fc42ac8aa4..f1174c97751 100644
--- a/storage/myisam/ha_myisam.cc
+++ b/storage/myisam/ha_myisam.cc
@@ -2305,6 +2305,7 @@ void ha_myisam::get_auto_increment(ulonglong offset, ulonglong increment,
   ulonglong nr;
   int error;
   uchar key[HA_MAX_KEY_LENGTH];
+  enum ha_rkey_function search_flag= HA_READ_PREFIX_LAST;
 
   if (!table->s->next_number_key_offset)
   {						// Autoincrement at key-start
@@ -2318,13 +2319,18 @@ void ha_myisam::get_auto_increment(ulonglong offset, ulonglong increment,
   /* it's safe to call the following if bulk_insert isn't on */
   mi_flush_bulk_insert(file, table->s->next_number_index);
 
+  if (unlikely(table->key_info[table->s->next_number_index].
+                  key_part[table->s->next_number_keypart].key_part_flag &
+                    HA_REVERSE_SORT))
+    search_flag= HA_READ_KEY_EXACT;
+
   (void) extra(HA_EXTRA_KEYREAD);
   key_copy(key, table->record[0],
            table->key_info + table->s->next_number_index,
            table->s->next_number_key_offset);
   error= mi_rkey(file, table->record[1], (int) table->s->next_number_index,
                  key, make_prev_keypart_map(table->s->next_number_keypart),
-                 HA_READ_PREFIX_LAST);
+                 search_flag);
   if (error)
     nr= 1;
   else