On Tue, Dec 07, 2021 at 07:01:45PM +0300, Sergey Petrunia wrote:
== Issue #1: ordered index scan is not handled in ha_partition ==
I've hit this again when trying to get range optmizer to work. Please find the patch below. It follows MySQL-8's approach.
Testcase:
create table t10 (a int, b int, key(a desc)) partition by hash(a) partitions 4; insert into t10 select seq, seq from seq_0_to_999;
MariaDB [test]> select * from t10 order by a limit 3; +------+------+ | a | b | +------+------+ | 3 | 3 | | 7 | 7 | | 11 | 11 | +------+------+ 3 rows in set (0.002 sec)
Correct output:
MariaDB [test]> select * from t10 use index() order by a limit 3; +------+------+ | a | b | +------+------+ | 0 | 0 | | 1 | 1 | | 2 | 2 | +------+------+ 3 rows in set (0.011 sec)
diff --git a/sql/key.cc b/sql/key.cc index f2cebfe6d82..c43d3c36d5d 100644 --- a/sql/key.cc +++ b/sql/key.cc @@ -495,6 +495,7 @@ int key_cmp(KEY_PART_INFO *key_part, const uchar *key, uint key_length) { int cmp; store_length= key_part->store_length; + int sort_order = (key_part->key_part_flag & HA_REVERSE_SORT) ? -1 : 1; if (key_part->null_bit) { /* This key part allows null values; NULL is lower than everything */ @@ -503,19 +504,19 @@ int key_cmp(KEY_PART_INFO *key_part, const uchar *key, uint key_length) { /* the range is expecting a null value */ if (!field_is_null) - return 1; // Found key is > range + return sort_order; // Found key is > range /* null -- exact match, go to next key part */ continue; } else if (field_is_null) - return -1; // NULL is less than any value + return -sort_order; // NULL is less than any value key++; // Skip null byte store_length--; } if ((cmp=key_part->field->key_cmp(key, key_part->length)) < 0) - return -1; + return -sort_order; if (cmp > 0) - return 1; + return sort_order; } return 0; // Keys are equal } @@ -574,6 +575,7 @@ int key_rec_cmp(void *key_p, uchar *first_rec, uchar *second_rec) do { field= key_part->field; + int sort_order = (key_part->key_part_flag & HA_REVERSE_SORT) ? -1 : 1; if (key_part->null_bit) { @@ -593,12 +595,12 @@ int key_rec_cmp(void *key_p, uchar *first_rec, uchar *second_rec) ; /* Fall through, no NULL fields */ else { - DBUG_RETURN(+1); + DBUG_RETURN(sort_order); } } else if (!sec_is_null) { - DBUG_RETURN(-1); + DBUG_RETURN(-sort_order); } else goto next_loop; /* Both were NULL */ @@ -612,7 +614,7 @@ int key_rec_cmp(void *key_p, uchar *first_rec, uchar *second_rec) */ if ((result= field->cmp_prefix(field->ptr+first_diff, field->ptr+sec_diff, key_part->length))) - DBUG_RETURN(result); + DBUG_RETURN(result * sort_order); next_loop: key_part++; key_part_num++; BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net