Details
Description
When using IN() operator, if the provided values are both within the 1st half and the 2nd half of the column size ( < 9223372036854775808 and >= 9223372036854775808 for unsigned bigint), the index is not used.
This behavior has changed from the 10.1.* versions where the index was correctly used.
SQL to reproduce:
CREATE TABLE `a` ( |
`id` bigint AUTO_INCREMENT PRIMARY KEY, |
`c1` bigint unsigned, |
KEY (`c1`) |
) ENGINE=InnoDB;
|
|
|
INSERT INTO `a` VALUES (1,9223382399205928659),(2,9223384207280813348),(3,9223385953115437234),(4,9223387250780556749),(5,9223387354282558788),(6,9223387603870501596),(7,9223389270813433667),(8,9223389903231468827),(9,9223390280789586779),(10,9223391591398222899),(11,9223391875473564350),(12,9223393152250049433),(13,9223393939696790223),(14,9223394417225350415),(15,9223397646397141015),(16,9223398025879291243),(17,9223399038671098072),(18,9223399534968874556),(19,9223400449518009285),(20,9223400860292643549),(21,9223400940692256924),(22,9223401073791948119),(23,9223402820804649616),(24,9223403470951992681),(25,9223405581879567267),(26,9223405754978563829),(27,9223405972966828221); |
|
|
MariaDB [test]> explain SELECT c1 FROM a WHERE c1 IN ( 1, 9223372036854775807 ); |
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ |
| 1 | SIMPLE | a | range | c1 | c1 | 9 | NULL | 2 | Using where; Using index | |
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ |
|
|
|
|
MariaDB [test]> explain SELECT c1 FROM a WHERE c1 IN ( 1, 9223372036854775808 ); |
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ |
| 1 | SIMPLE | a | index | c1 | c1 | 9 | NULL | 27 | Using where; Using index | |
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ |
Attachments
Issue Links
- relates to
-
MDEV-18319 BIGINT UNSIGNED Performance issue
-
- Closed
-
-
-
- Closed
-
Activity
Hi bar!
Please review this patch that fixes a "regression" introduced by MDEV-11497.
There isn't necessarily a problem with MDEV-11497, however the changes there introduced some limitations to the optimizer, that were previously not present.
Ideally, I'd like to push this to 10.4, so it fixes the user's problem, but please review this in the context of 11.3 for now and if you think that it is safe enough, we can push this to older versions.
I have tried various approaches to make this work and this is the cleanest I could come up with, without taking apart the multiple-inheritance that Item_func_in has.
I've explained the bugfix in the commit message, as well as code comments. Let me know if it is clear enough.
Hi cvicentiu,
Please review a modified version of the patch implementing (as discussed on slack) it in a slightly different way:
- There is no a need to aggregate for comparison all (predicant,valueN) pairs again to detect a mix of integers with different signess. We can just analyze the state of Predicant_to_list_comparator after} the {{prepare_predicant_and_values() call. Then we can iterate through all individual arguments to check if they are all integers.
- The class in_longlong supports a mixture of integers of different signess. There is no a need to use in_decimal.
https://github.com/MariaDB/server/commit/3096cdb57b3c5b3c0c38e4538186f5c6f5fc4d03
The patch is good starting from 10.5. For 10.4 it would need some additional changes, let's skip 10.4.
Thanks.
Hi bar.
Patch looks good to me. Buildbot should clear it first of course, but if no other tests fail, this fixes the issue.
Preliminary investigation in this:
This is related to the type system. The fix will require a bit more analysis, however I have a workaround:
Cast all values in the IN clause as UNSIGNED.
explain SELECT c1 FROM a WHERE c1 IN ( 1, 9223372036854775808 );id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE a index c1 c1 9 NULL 27 Using where; Using indexexplain SELECT c1 FROM a WHERE c1 IN ( CAST(1 AS UNSIGNED), CAST(9223372036854775808 AS UNSIGNED));id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE a range c1 c1 9 NULL 2 Using where; Using indexThis will make the optimizer know that the types are compatible. I'll work on a proper fix for this.