Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5.11
-
None
-
None
-
Debian Buster
Description
Hello people of MariaDB,
we have observed a minor but annoying bug, hopefully not a feature. Please see below.
mysql> SELECT VERSION();
|
+--------------------------------------------+
|
| VERSION() |
|
+--------------------------------------------+
|
| 10.5.11-MariaDB-1:10.5.11+maria~buster-log | |
+--------------------------------------------+
|
|
|
mysql> desc rating;
|
+------------------------------+-----------------------+------+-----+---------------------+-------------------------------+
|
| Field | Type | Null | Key | Default | Extra |
|
+------------------------------+-----------------------+------+-----+---------------------+-------------------------------+
|
| user_id | bigint(20) unsigned | NO | PRI | 0 | | |
.
|
.
|
.
|
+------------------------------+-----------------------+------+-----+---------------------+-------------------------------+
|
|
|
|
|
mysql> explain select user_id from rating where user_id in (3322217720125498802, 17417331430921724792, 4812081455371997970); |
+------+-------------+--------+-------+---------------+------------------------+---------+------+----------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------+-------+---------------+------------------------+---------+------+----------+--------------------------+
|
| 1 | SIMPLE | rating | index | *PRIMARY* | last_record_update_idx | 4 | NULL | 10882993 | Using where; Using index | |
+------+-------------+--------+-------+---------------+------------------------+---------+------+----------+--------------------------+
|
|
|
mysql> explain delete from rating where user_id in (3322217720125498802, 17417331430921724792, 4812081455371997970); |
+------+-------------+--------+------+---------------+------+---------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------+------+---------------+------+---------+------+----------+-------------+
|
| 1 | SIMPLE | rating | ALL | *NULL* | NULL | NULL | NULL | 10882740 | Using where | |
+------+-------------+--------+------+---------------+------+---------+------+----------+-------------+
|
|
|
mysql> explain delete from rating where user_id in ('3322217720125498802', '17417331430921724792', '4812081455371997970'); |
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|
| 1 | SIMPLE | rating | range | *PRIMARY* | PRIMARY | 8 | NULL | 3 | Using where | |
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|
So as you can see, if user_ids in brackets aren't chars, primary key is not used when deleting. This is weird, because ids are not chars but bigints, therefore this should work even with numbers only.
Can you please check, if this is a bug or a feature? This used to work for us in 10.2.
Thanks and regards,
Alan