Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.3(EOL), 10.4(EOL), 10.5
-
None
Description
Here is a test case demonstrating the issue
MariaDB [test]> create table t1(a int); |
Query OK, 0 rows affected (0.047 sec) |
|
|
MariaDB [test]> insert into t1 select seq from seq_1_to_100; |
Query OK, 100 rows affected (0.025 sec) |
|
|
MariaDB [test]> set in_predicate_conversion_threshold=9; |
Query OK, 0 rows affected (0.001 sec) |
Query 1:
SELECT * FROM t1 WHERE a IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9);
the column a has the same datatype as the elements in the IN list.
MariaDB [test]> explain select * from t1 where a in (0, 1, 2, 3, 4, 5, 6, 7, 8, 9);
|
+------+--------------+-------------+--------+---------------+--------------+---------+------+------+----------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------+-------------+--------+---------------+--------------+---------+------+------+----------------+
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 100 | |
|
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | |
|
| 2 | MATERIALIZED | <derived3> | ALL | NULL | NULL | NULL | NULL | 10 | |
|
| 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
|
+------+--------------+-------------+--------+---------------+--------------+---------+------+------+----------------+
|
4 rows in set (0.005 sec)
|
Query 2:
select * from t1 where a in ('0','1','2','3','4','5','6','7','8','9')
the column a has different datatype as the elements in the IN list.
MariaDB [test]> explain select * from t1 where a in ('0','1','2','3','4','5','6','7','8','9');
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 100 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
1 row in set (0.002 sec)
|
For query 2 it would be good if we could convert the the elements of the IN list to DOUBLE and do the comparison in DOUBLE. By doing this we could do materialization of the IN subquery, when the IN predicate gets converted.
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
MDEV-24319 Create semi-join materialization tables with the collations used for performing the IN-comparison
-
- Open
-