[MDEV-18898] SELECT using wrong index when using operator IN with mixed types - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Duplicate
Affects Version/s: 10.1.37, 10.1.38, 5.5(EOL), 10.0(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: N/A
Component/s: Optimizer, Storage Engine - InnoDB
Labels:
- datatype
- innodb
- optimizer
- performance
- types
Environment:
Experienced on CentOS 7 (centos-release-7-5.1804.5.el7.centos.x86_64) and able to reproduce also on WSL Ubuntu 18.04 under Win10 Insiders build 18351

Description

Hello good people,

I accidentaly stumbled upon a weird behavior of SELECT query optimizer, which doesn't seem to handle values of mixed types very well when using operator IN in WHERE condition. This ultimately results in suboptimal usage of indexes, even though sufficient information is IMHO available for handling the query better (i.e. using correct index).

I was able to come up with MVCE of this sort:

CREATE TEMPORARY TABLE __t1 (

  id int(10) unsigned NOT NULL AUTO_INCREMENT,

  name varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `name` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO __t1 SELECT seq, MD5(seq) FROM seq_1_to_500000;

ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, 2);

ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', 2); -- Uses wrong index

ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, '2'); -- Uses wrong index

ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', '2');

ANALYZE SELECT id, name FROM __t1 WHERE id = 1;

ANALYZE SELECT id, name FROM __t1 WHERE id = '2';

ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = 2;

ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = '2';

ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = '2';

ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = 2;

Resulting in:

> ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, 2);

+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+

| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra       |

+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+

|    1 | SIMPLE      | __t1  | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   2.00 |   100.00 |     100.00 | Using where |

+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+

1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', 2); -- Wrong index

+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+

| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | r_rows     | filtered | r_filtered | Extra                    |

+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+

|    1 | SIMPLE      | __t1  | index | PRIMARY       | name | 302     | NULL | 996723 | 1000000.00 |   100.00 |       0.00 | Using where; Using index |

+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+

1 row in set (0.21 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, '2'); -- Wrong index

+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+

| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | r_rows     | filtered | r_filtered | Extra                    |

+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+

|    1 | SIMPLE      | __t1  | index | PRIMARY       | name | 302     | NULL | 996723 | 1000000.00 |   100.00 |       0.00 | Using where; Using index |

+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+

1 row in set (0.22 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', '2');

+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+

| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra       |

+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+

|    1 | SIMPLE      | __t1  | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   2.00 |   100.00 |     100.00 | Using where |

+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+

1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = 1;

+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+

| id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | r_rows | filtered | r_filtered | Extra |

+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+

|    1 | SIMPLE      | __t1  | const | PRIMARY       | PRIMARY | 4       | const |    1 |   NULL |   100.00 |       NULL |       |

+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+

1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = '2';

+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+

| id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | r_rows | filtered | r_filtered | Extra |

+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+

|    1 | SIMPLE      | __t1  | const | PRIMARY       | PRIMARY | 4       | const |    1 |   NULL |   100.00 |       NULL |       |

+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+

1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = 2;

+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+

| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra       |

+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+

|    1 | SIMPLE      | __t1  | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   2.00 |   100.00 |     100.00 | Using where |

+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+

1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = '2';

+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+

| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra       |

+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+

|    1 | SIMPLE      | __t1  | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   2.00 |   100.00 |     100.00 | Using where |

+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+

1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = '2';

+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+

| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra       |

+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+

|    1 | SIMPLE      | __t1  | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   2.00 |   100.00 |     100.00 | Using where |

+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+

1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = 2;

+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+

| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra       |

+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+

|    1 | SIMPLE      | __t1  | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   2.00 |   100.00 |     100.00 | Using where |

+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+

1 row in set (0.00 sec)

As you can see the second and third SELECT queries employ wrong index and thus both result in (unnecessary?) full scan, even though it is clear that the condition is based solely on the id column, which acts as a primary key.

SELECT queries with mixed types (integer and string) inside IN condition are the ones affected by this suboptimal index usage.

SELECTs with values of a single common type use correct indexes - even if they're not matching the id column's type (integer), e.g. when values inside IN are numeric strings. These queries are handled well.

SELECTs with values of mixed types used with the OR operator instead (the last four queries) also use correct the index. These queries are handled well, too.

The performance difference there is not trivial - and it becomes worse with bigger tables.

One might argue that one should just always use the correct types of values with these conditions, but there are environments in the wild that don't really care about differences between integers and numeric strings - one prime example being PHP. These can (and will) pass these values in unexpected and inconsistent fashion. In fact this is how I found out about this.

(I was unable test this with different versions of MariaDB than those mentioned above. Also I apologize in advance if I made any wrong assumptions about the MariaDB's internals.)

Attachments

Issue Links

is blocked by

MDEV-15759 Expect "Impossible WHERE" for indexed_int_column=out_of_range_int_constant

Closed

MDEV-18964 Non-optimal execution of WHERE num_field=const AND num_field=const

Open

MDEV-18968 Both (WHERE 0.1) and (WHERE NOT 0.1) return empty set

Closed

MDEV-19008 Slow EXPLAIN SELECT ... WHERE col IN (const1,const2,(subquery))

Closed

relates to

MDEV-8787 Return Impossible WHERE instead of Full table scan on some admittedly false field=const expressions

Open

MDEV-31303 Key not used when IN clause has both signed and unsigned values

Closed

MDEV-18319 BIGINT UNSIGNED Performance issue

Closed

(2 relates to)

Activity

Ascending order - Click to sort in descending order

Přemysl Karbula created issue - 2019-03-12 09:28

Přemysl Karbula made changes - 2019-03-12 09:32

Field	Original Value	New Value
Description	Hello good people, I accidentaly stumbled upon a weird (?) behavior of SELECT query optimizer, which doesn't seem to handle values of mixed types very well when using operator IN in WHERE condition. This ultimately results in suboptimal usage of indexes, even though sufficient information is IMHO available for handling the query better (i.e. using correct index). I was able to come up with MVCE of this sort: {code:sql} -- Preparation DROP TABLE IF EXISTS __t1; CREATE TABLE __t1 ( id int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO __t1 SELECT seq, MD5(seq) FROM seq_1_to_500000; -- Preparation ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, 2); ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', 2); -- Uses wrong index ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, '2'); -- Uses wrong index ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', '2'); ANALYZE SELECT id, name FROM __t1 WHERE id = 1; ANALYZE SELECT id, name FROM __t1 WHERE id = '2'; ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = 2; ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = '2'; ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = '2'; ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = 2; {code} Resulting in: {noformat} > ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, 2); +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| r_rows \| filtered \| r_filtered \| Extra \| +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ \| 1 \| SIMPLE \| __t1 \| range \| PRIMARY \| PRIMARY \| 4 \| NULL \| 2 \| 2.00 \| 100.00 \| 100.00 \| Using where \| +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ 1 row in set (0.00 sec) > ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', 2); -- Wrong index +------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| r_rows \| filtered \| r_filtered \| Extra \| +------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+ \| 1 \| SIMPLE \| __t1 \| index \| PRIMARY \| name \| 302 \| NULL \| 996723 \| 1000000.00 \| 100.00 \| 0.00 \| Using where; Using index \| +------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+ 1 row in set (0.21 sec) > ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, '2'); -- Wrong index +------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| r_rows \| filtered \| r_filtered \| Extra \| +------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+ \| 1 \| SIMPLE \| __t1 \| index \| PRIMARY \| name \| 302 \| NULL \| 996723 \| 1000000.00 \| 100.00 \| 0.00 \| Using where; Using index \| +------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+ 1 row in set (0.22 sec) > ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', '2'); +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| r_rows \| filtered \| r_filtered \| Extra \| +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ \| 1 \| SIMPLE \| __t1 \| range \| PRIMARY \| PRIMARY \| 4 \| NULL \| 2 \| 2.00 \| 100.00 \| 100.00 \| Using where \| +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ 1 row in set (0.00 sec) > ANALYZE SELECT id, name FROM __t1 WHERE id = 1; +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| r_rows \| filtered \| r_filtered \| Extra \| +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+ \| 1 \| SIMPLE \| __t1 \| const \| PRIMARY \| PRIMARY \| 4 \| const \| 1 \| NULL \| 100.00 \| NULL \| \| +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+ 1 row in set (0.00 sec) > ANALYZE SELECT id, name FROM __t1 WHERE id = '2'; +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| r_rows \| filtered \| r_filtered \| Extra \| +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+ \| 1 \| SIMPLE \| __t1 \| const \| PRIMARY \| PRIMARY \| 4 \| const \| 1 \| NULL \| 100.00 \| NULL \| \| +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+ 1 row in set (0.00 sec) > ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = 2; +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| r_rows \| filtered \| r_filtered \| Extra \| +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ \| 1 \| SIMPLE \| __t1 \| range \| PRIMARY \| PRIMARY \| 4 \| NULL \| 2 \| 2.00 \| 100.00 \| 100.00 \| Using where \| +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ 1 row in set (0.00 sec) > ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = '2'; +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| r_rows \| filtered \| r_filtered \| Extra \| +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ \| 1 \| SIMPLE \| __t1 \| range \| PRIMARY \| PRIMARY \| 4 \| NULL \| 2 \| 2.00 \| 100.00 \| 100.00 \| Using where \| +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ 1 row in set (0.00 sec) > ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = '2'; +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| r_rows \| filtered \| r_filtered \| Extra \| +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ \| 1 \| SIMPLE \| __t1 \| range \| PRIMARY \| PRIMARY \| 4 \| NULL \| 2 \| 2.00 \| 100.00 \| 100.00 \| Using where \| +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ 1 row in set (0.00 sec) > ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = 2; +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| r_rows \| filtered \| r_filtered \| Extra \| +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ \| 1 \| SIMPLE \| __t1 \| range \| PRIMARY \| PRIMARY \| 4 \| NULL \| 2 \| 2.00 \| 100.00 \| 100.00 \| Using where \| +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ 1 row in set (0.00 sec) {noformat} As you can see the second and third SELECT queries employ wrong index and thus both result in (unnecessary?) full scan, even though it is clear that the condition is based solely on the {{id}} column, which acts as a primary key. SELECT queries with mixed types (integer and string) inside IN condition are the ones affected by this suboptimal index usage. SELECTs with values of a single common type use correct indexes - even if they're not matching the {{id}} column's type (integer), e.g. when values inside IN are numeric strings. These queries are handled well. SELECTs with values of mixed types used with the OR operator instead _(the last four queries)_also use correct the index. These queries are handled well, too. The performance difference there is not trivial - and it becomes worse with bigger tables. One might argue that one should just always use the correct types of values with these conditions, but there are environments in the wild that don't really care about differences between integers and numeric strings _- one prime example being PHP_. These can (and will) pass these values in unexpected and inconsistent fashion. In fact this is how I found out about this. _(I was unable test this with different versions of MariaDB than those mentioned above. Also I apologize in advance if I made any wrong assumptions about the MariaDB's internals.)_	Hello good people, I accidentaly stumbled upon a weird (?) behavior of SELECT query optimizer, which doesn't seem to handle values of mixed types very well when using operator IN in WHERE condition. This ultimately results in suboptimal usage of indexes, even though sufficient information is IMHO available for handling the query better (i.e. using correct index). I was able to come up with MVCE of this sort: {code:sql} DROP TABLE IF EXISTS __t1; CREATE TABLE __t1 ( id int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO __t1 SELECT seq, MD5(seq) FROM seq_1_to_500000; ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, 2); ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', 2); -- Uses wrong index ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, '2'); -- Uses wrong index ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', '2'); ANALYZE SELECT id, name FROM __t1 WHERE id = 1; ANALYZE SELECT id, name FROM __t1 WHERE id = '2'; ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = 2; ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = '2'; ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = '2'; ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = 2; {code} Resulting in: {noformat} > ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, 2); +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| r_rows \| filtered \| r_filtered \| Extra \| +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ \| 1 \| SIMPLE \| __t1 \| range \| PRIMARY \| PRIMARY \| 4 \| NULL \| 2 \| 2.00 \| 100.00 \| 100.00 \| Using where \| +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ 1 row in set (0.00 sec) > ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', 2); -- Wrong index +------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| r_rows \| filtered \| r_filtered \| Extra \| +------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+ \| 1 \| SIMPLE \| __t1 \| index \| PRIMARY \| name \| 302 \| NULL \| 996723 \| 1000000.00 \| 100.00 \| 0.00 \| Using where; Using index \| +------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+ 1 row in set (0.21 sec) > ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, '2'); -- Wrong index +------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| r_rows \| filtered \| r_filtered \| Extra \| +------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+ \| 1 \| SIMPLE \| __t1 \| index \| PRIMARY \| name \| 302 \| NULL \| 996723 \| 1000000.00 \| 100.00 \| 0.00 \| Using where; Using index \| +------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+ 1 row in set (0.22 sec) > ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', '2'); +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| r_rows \| filtered \| r_filtered \| Extra \| +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ \| 1 \| SIMPLE \| __t1 \| range \| PRIMARY \| PRIMARY \| 4 \| NULL \| 2 \| 2.00 \| 100.00 \| 100.00 \| Using where \| +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ 1 row in set (0.00 sec) > ANALYZE SELECT id, name FROM __t1 WHERE id = 1; +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| r_rows \| filtered \| r_filtered \| Extra \| +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+ \| 1 \| SIMPLE \| __t1 \| const \| PRIMARY \| PRIMARY \| 4 \| const \| 1 \| NULL \| 100.00 \| NULL \| \| +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+ 1 row in set (0.00 sec) > ANALYZE SELECT id, name FROM __t1 WHERE id = '2'; +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| r_rows \| filtered \| r_filtered \| Extra \| +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+ \| 1 \| SIMPLE \| __t1 \| const \| PRIMARY \| PRIMARY \| 4 \| const \| 1 \| NULL \| 100.00 \| NULL \| \| +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+ 1 row in set (0.00 sec) > ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = 2; +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| r_rows \| filtered \| r_filtered \| Extra \| +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ \| 1 \| SIMPLE \| __t1 \| range \| PRIMARY \| PRIMARY \| 4 \| NULL \| 2 \| 2.00 \| 100.00 \| 100.00 \| Using where \| +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ 1 row in set (0.00 sec) > ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = '2'; +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| r_rows \| filtered \| r_filtered \| Extra \| +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ \| 1 \| SIMPLE \| __t1 \| range \| PRIMARY \| PRIMARY \| 4 \| NULL \| 2 \| 2.00 \| 100.00 \| 100.00 \| Using where \| +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ 1 row in set (0.00 sec) > ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = '2'; +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| r_rows \| filtered \| r_filtered \| Extra \| +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ \| 1 \| SIMPLE \| __t1 \| range \| PRIMARY \| PRIMARY \| 4 \| NULL \| 2 \| 2.00 \| 100.00 \| 100.00 \| Using where \| +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ 1 row in set (0.00 sec) > ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = 2; +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| r_rows \| filtered \| r_filtered \| Extra \| +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ \| 1 \| SIMPLE \| __t1 \| range \| PRIMARY \| PRIMARY \| 4 \| NULL \| 2 \| 2.00 \| 100.00 \| 100.00 \| Using where \| +------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+ 1 row in set (0.00 sec) {noformat} As you can see the second and third SELECT queries employ wrong index and thus both result in (unnecessary?) full scan, even though it is clear that the condition is based solely on the {{id}} column, which acts as a primary key. SELECT queries with mixed types (integer and string) inside IN condition are the ones affected by this suboptimal index usage. SELECTs with values of a single common type use correct indexes - even if they're not matching the {{id}} column's type (integer), e.g. when values inside IN are numeric strings. These queries are handled well. SELECTs with values of mixed types used with the OR operator instead _(the last four queries)_also use correct the index. These queries are handled well, too. The performance difference there is not trivial - and it becomes worse with bigger tables. One might argue that one should just always use the correct types of values with these conditions, but there are environments in the wild that don't really care about differences between integers and numeric strings _- one prime example being PHP_. These can (and will) pass these values in unexpected and inconsistent fashion. In fact this is how I found out about this. _(I was unable test this with different versions of MariaDB than those mentioned above. Also I apologize in advance if I made any wrong assumptions about the MariaDB's internals.)_

Přemysl Karbula made changes - 2019-03-12 09:34

Description

Hello good people,

I accidentaly stumbled upon a weird (?) behavior of SELECT query optimizer, which *doesn't seem to handle values of mixed types very well when using operator IN* in WHERE condition. This ultimately results in suboptimal usage of indexes, even though sufficient information is IMHO available for handling the query better (i.e. using correct index).

I was able to come up with MVCE of this sort:

{code:sql}
DROP TABLE IF EXISTS __t1;
CREATE TABLE __t1 (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO __t1 SELECT seq, MD5(seq) FROM seq_1_to_500000;

ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, 2);
ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', 2); -- Uses wrong index
ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, '2'); -- Uses wrong index
ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', '2');
ANALYZE SELECT id, name FROM __t1 WHERE id = 1;
ANALYZE SELECT id, name FROM __t1 WHERE id = '2';
ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = 2;
ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = '2';
ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = '2';
ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = 2;
{code}

Resulting in:

{noformat}
> ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, 2);
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | __t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 2.00 | 100.00 | 100.00 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', 2); -- Wrong index
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+
| 1 | SIMPLE | __t1 | index | PRIMARY | name | 302 | NULL | 996723 | 1000000.00 | 100.00 | 0.00 | Using where; Using index |
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+
1 row in set (0.21 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, '2'); -- Wrong index
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+
| 1 | SIMPLE | __t1 | index | PRIMARY | name | 302 | NULL | 996723 | 1000000.00 | 100.00 | 0.00 | Using where; Using index |
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+
1 row in set (0.22 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', '2');
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | __t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 2.00 | 100.00 | 100.00 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = 1;
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+
| 1 | SIMPLE | __t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | 100.00 | NULL | |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = '2';
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+
| 1 | SIMPLE | __t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | 100.00 | NULL | |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = 2;
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | __t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 2.00 | 100.00 | 100.00 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = '2';
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | __t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 2.00 | 100.00 | 100.00 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = '2';
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | __t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 2.00 | 100.00 | 100.00 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = 2;
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | __t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 2.00 | 100.00 | 100.00 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.00 sec)
{noformat}

As you can see *the second and third SELECT queries employ wrong index and thus both result in (unnecessary?) full scan*, even though it is clear that the condition is based solely on the {{id}} column, which acts as a primary key.

SELECT queries with mixed types (integer and string) inside IN condition are the ones affected by this suboptimal index usage.

SELECTs with values of a single common type use correct indexes - even if they're not matching the {{id}} column's type (integer), e.g. when values inside IN are numeric strings. These queries are handled well.

SELECTs with values of mixed types used with the OR operator instead _(the last four queries)_also use correct the index. These queries are handled well, too.

*The performance difference there is not trivial* - and it becomes worse with bigger tables.

One might argue that one should just always use the correct types of values with these conditions, but there are environments in the wild that don't really care about differences between integers and numeric strings _- one prime example being PHP_. These can (and will) pass these values in unexpected and inconsistent fashion. In fact this is how I found out about this.

_(I was unable test this with different versions of MariaDB than those mentioned above. Also I apologize in advance if I made any wrong assumptions about the MariaDB's internals.)_

Hello good people,

I accidentaly stumbled upon a weird (?) behavior of SELECT query optimizer, which *doesn't seem to handle values of mixed types very well when using operator IN* in WHERE condition. This ultimately results in suboptimal usage of indexes, even though sufficient information is IMHO available for handling the query better (i.e. using correct index).

I was able to come up with MVCE of this sort:

{code:sql}
DROP TABLE IF EXISTS __t1;
CREATE TABLE __t1 (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO __t1 SELECT seq, MD5(seq) FROM seq_1_to_500000;

ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, 2);
ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', 2); -- Uses wrong index
ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, '2'); -- Uses wrong index
ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', '2');
ANALYZE SELECT id, name FROM __t1 WHERE id = 1;
ANALYZE SELECT id, name FROM __t1 WHERE id = '2';
ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = 2;
ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = '2';
ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = '2';
ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = 2;
{code}

Resulting in:

{noformat}
> ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, 2);
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | __t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 2.00 | 100.00 | 100.00 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', 2); -- Wrong index
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+
| 1 | SIMPLE | __t1 | index | PRIMARY | name | 302 | NULL | 996723 | 1000000.00 | 100.00 | 0.00 | Using where; Using index |
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+
1 row in set (0.21 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, '2'); -- Wrong index
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+
| 1 | SIMPLE | __t1 | index | PRIMARY | name | 302 | NULL | 996723 | 1000000.00 | 100.00 | 0.00 | Using where; Using index |
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+
1 row in set (0.22 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', '2');
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | __t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 2.00 | 100.00 | 100.00 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = 1;
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+
| 1 | SIMPLE | __t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | 100.00 | NULL | |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = '2';
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+
| 1 | SIMPLE | __t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | 100.00 | NULL | |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = 2;
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | __t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 2.00 | 100.00 | 100.00 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = '2';
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | __t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 2.00 | 100.00 | 100.00 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = '2';
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | __t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 2.00 | 100.00 | 100.00 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = 2;
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | __t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 2.00 | 100.00 | 100.00 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.00 sec)
{noformat}

As you can see *the second and third SELECT queries employ wrong index and thus both result in (unnecessary?) full scan*, even though it is clear that the condition is based solely on the {{id}} column, which acts as a primary key.

SELECT queries with mixed types (integer and string) inside IN condition are the ones affected by this suboptimal index usage.

SELECTs with values of a single common type use correct indexes - even if they're not matching the {{id}} column's type (integer), e.g. when values inside IN are numeric strings. These queries are handled well.

SELECTs with values of mixed types used with the OR operator instead _(the last four queries)_ also use correct the index. These queries are handled well, too.

*The performance difference there is not trivial* - and it becomes worse with bigger tables.

One might argue that one should just always use the correct types of values with these conditions, but there are environments in the wild that don't really care about differences between integers and numeric strings _- one prime example being PHP_. These can (and will) pass these values in unexpected and inconsistent fashion. In fact this is how I found out about this.

_(I was unable test this with different versions of MariaDB than those mentioned above. Also I apologize in advance if I made any wrong assumptions about the MariaDB's internals.)_

Přemysl Karbula made changes - 2019-03-12 09:53

Description

Hello good people,

I accidentaly stumbled upon a weird (?) behavior of SELECT query optimizer, which *doesn't seem to handle values of mixed types very well when using operator IN* in WHERE condition. This ultimately results in suboptimal usage of indexes, even though sufficient information is IMHO available for handling the query better (i.e. using correct index).

I was able to come up with MVCE of this sort:

{code:sql}
DROP TABLE IF EXISTS __t1;
CREATE TABLE __t1 (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO __t1 SELECT seq, MD5(seq) FROM seq_1_to_500000;

ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, 2);
ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', 2); -- Uses wrong index
ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, '2'); -- Uses wrong index
ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', '2');
ANALYZE SELECT id, name FROM __t1 WHERE id = 1;
ANALYZE SELECT id, name FROM __t1 WHERE id = '2';
ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = 2;
ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = '2';
ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = '2';
ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = 2;
{code}

Resulting in:

{noformat}
> ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, 2);
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | __t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 2.00 | 100.00 | 100.00 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', 2); -- Wrong index
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+
| 1 | SIMPLE | __t1 | index | PRIMARY | name | 302 | NULL | 996723 | 1000000.00 | 100.00 | 0.00 | Using where; Using index |
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+
1 row in set (0.21 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, '2'); -- Wrong index
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+
| 1 | SIMPLE | __t1 | index | PRIMARY | name | 302 | NULL | 996723 | 1000000.00 | 100.00 | 0.00 | Using where; Using index |
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+
1 row in set (0.22 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', '2');
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | __t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 2.00 | 100.00 | 100.00 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = 1;
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+
| 1 | SIMPLE | __t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | 100.00 | NULL | |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = '2';
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+
| 1 | SIMPLE | __t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | 100.00 | NULL | |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = 2;
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | __t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 2.00 | 100.00 | 100.00 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = '2';
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | __t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 2.00 | 100.00 | 100.00 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = '2';
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | __t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 2.00 | 100.00 | 100.00 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = 2;
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | __t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 2.00 | 100.00 | 100.00 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.00 sec)
{noformat}

As you can see *the second and third SELECT queries employ wrong index and thus both result in (unnecessary?) full scan*, even though it is clear that the condition is based solely on the {{id}} column, which acts as a primary key.

SELECT queries with mixed types (integer and string) inside IN condition are the ones affected by this suboptimal index usage.

SELECTs with values of a single common type use correct indexes - even if they're not matching the {{id}} column's type (integer), e.g. when values inside IN are numeric strings. These queries are handled well.

SELECTs with values of mixed types used with the OR operator instead _(the last four queries)_ also use correct the index. These queries are handled well, too.

*The performance difference there is not trivial* - and it becomes worse with bigger tables.

One might argue that one should just always use the correct types of values with these conditions, but there are environments in the wild that don't really care about differences between integers and numeric strings _- one prime example being PHP_. These can (and will) pass these values in unexpected and inconsistent fashion. In fact this is how I found out about this.

_(I was unable test this with different versions of MariaDB than those mentioned above. Also I apologize in advance if I made any wrong assumptions about the MariaDB's internals.)_

Hello good people,

I accidentaly stumbled upon a weird (?) behavior of SELECT query optimizer, which *doesn't seem to handle values of mixed types very well when using operator IN* in WHERE condition. This ultimately results in suboptimal usage of indexes, even though sufficient information is IMHO available for handling the query better (i.e. using correct index).

I was able to come up with MVCE of this sort:

{code:sql}
CREATE TEMPORARY TABLE __t1 (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO __t1 SELECT seq, MD5(seq) FROM seq_1_to_500000;

ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, 2);
ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', 2); -- Uses wrong index
ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, '2'); -- Uses wrong index
ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', '2');
ANALYZE SELECT id, name FROM __t1 WHERE id = 1;
ANALYZE SELECT id, name FROM __t1 WHERE id = '2';
ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = 2;
ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = '2';
ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = '2';
ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = 2;
{code}

Resulting in:

{noformat}
> ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, 2);
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | __t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 2.00 | 100.00 | 100.00 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', 2); -- Wrong index
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+
| 1 | SIMPLE | __t1 | index | PRIMARY | name | 302 | NULL | 996723 | 1000000.00 | 100.00 | 0.00 | Using where; Using index |
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+
1 row in set (0.21 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id IN (1, '2'); -- Wrong index
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+
| 1 | SIMPLE | __t1 | index | PRIMARY | name | 302 | NULL | 996723 | 1000000.00 | 100.00 | 0.00 | Using where; Using index |
+------+-------------+-------+-------+---------------+------+---------+------+--------+------------+----------+------------+--------------------------+
1 row in set (0.22 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id IN ('1', '2');
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | __t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 2.00 | 100.00 | 100.00 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = 1;
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+
| 1 | SIMPLE | __t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | 100.00 | NULL | |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = '2';
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+
| 1 | SIMPLE | __t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | 100.00 | NULL | |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = 2;
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | __t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 2.00 | 100.00 | 100.00 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = '2';
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | __t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 2.00 | 100.00 | 100.00 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = 1 OR id = '2';
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | __t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 2.00 | 100.00 | 100.00 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.00 sec)

> ANALYZE SELECT id, name FROM __t1 WHERE id = '1' OR id = 2;
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | __t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 2.00 | 100.00 | 100.00 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------+----------+------------+-------------+
1 row in set (0.00 sec)
{noformat}

As you can see *the second and third SELECT queries employ wrong index and thus both result in (unnecessary?) full scan*, even though it is clear that the condition is based solely on the {{id}} column, which acts as a primary key.

SELECT queries with mixed types (integer and string) inside IN condition are the ones affected by this suboptimal index usage.

SELECTs with values of a single common type use correct indexes - even if they're not matching the {{id}} column's type (integer), e.g. when values inside IN are numeric strings. These queries are handled well.

SELECTs with values of mixed types used with the OR operator instead _(the last four queries)_ also use correct the index. These queries are handled well, too.

*The performance difference there is not trivial* - and it becomes worse with bigger tables.

One might argue that one should just always use the correct types of values with these conditions, but there are environments in the wild that don't really care about differences between integers and numeric strings _- one prime example being PHP_. These can (and will) pass these values in unexpected and inconsistent fashion. In fact this is how I found out about this.

_(I was unable test this with different versions of MariaDB than those mentioned above. Also I apologize in advance if I made any wrong assumptions about the MariaDB's internals.)_

Elena Stepanova made changes - 2019-03-12 15:10

Fix Version/s		10.1 [ 16100 ]
Assignee		Oleksandr Byelkin [ sanja ]

Sergei Golubchik made changes - 2019-03-14 09:52

Assignee

Oleksandr Byelkin [ sanja ]

Alexander Barkov [ bar ]

Sergei Golubchik made changes - 2019-03-14 09:52

Priority

Minor [ 4 ]

Major [ 3 ]

Sergei Golubchik made changes - 2019-03-14 09:52

Fix Version/s		10.2 [ 14601 ]
Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]

Alexander Barkov made changes - 2019-03-15 10:57

Affects Version/s		5.5 [ 15800 ]
Affects Version/s		10.0 [ 16000 ]

Alexander Barkov made changes - 2019-03-15 10:58

Affects Version/s		10.2 [ 14601 ]
Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.4 [ 22408 ]

Alexander Barkov made changes - 2019-03-19 05:54

Link

This issue is blocked by MDEV-18964 [ MDEV-18964 ]

Alexander Barkov made changes - 2019-03-19 06:06

Link

This issue is blocked by ~~MDEV-15759~~ [ ~~MDEV-15759~~ ]

Alexander Barkov made changes - 2019-03-19 06:07

Link

This issue is blocked by MDEV-8787 [ MDEV-8787 ]

Alexander Barkov made changes - 2019-03-19 10:32

Link

This issue is blocked by MDEV-8787 [ MDEV-8787 ]

Alexander Barkov made changes - 2019-03-19 10:33

Link

This issue is blocked by ~~MDEV-18968~~ [ ~~MDEV-18968~~ ]

Alexander Barkov made changes - 2019-03-21 05:18

Link

This issue relates to ~~MDEV-18319~~ [ ~~MDEV-18319~~ ]

Alexander Barkov made changes - 2019-03-21 07:34

Link

This issue is blocked by ~~MDEV-19008~~ [ ~~MDEV-19008~~ ]

Alexander Barkov made changes - 2019-03-26 04:26

Link

This issue relates to MDEV-8787 [ MDEV-8787 ]

Julien Fritsch made changes - 2020-12-01 12:38

Fix Version/s

10.1 [ 16100 ]

Sergei Golubchik made changes - 2021-12-06 21:33

Workflow

MariaDB v3 [ 93208 ]

MariaDB v4 [ 141196 ]

Ralf Gebhardt made changes - 2022-08-04 08:41

Fix Version/s

10.2 [ 14601 ]

Julien Fritsch made changes - 2023-04-27 14:27

Fix Version/s

10.3 [ 22126 ]

Alexander Barkov made changes - 2024-04-09 09:09

Link

This issue relates to ~~MDEV-31303~~ [ ~~MDEV-31303~~ ]

Alexander Barkov made changes - 2024-04-09 12:43

issue.field.resolutiondate

2024-04-09 12:43:00.0

2024-04-09 12:42:59.633

Alexander Barkov made changes - 2024-04-09 12:43

Fix Version/s		N/A [ 14700 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Duplicate [ 3 ]
Status	Open [ 1 ]	Closed [ 6 ]

People

Assignee:: Alexander Barkov

Reporter:: Přemysl Karbula

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2019-03-12 09:28

Updated:: 2024-04-16 15:22

Resolved:: 2024-04-09 12:43

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration