Details
-
New Feature
-
Status: In Review (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
Q3/2025 Maintenance
Description
create table t1 (a int, b int, vcol int as (a+1), index(vcol)); |
insert into t1 (a,b) select seq, seq from seq_1_to_1000; |
Here, the rewrite works:
MariaDB [test]> explain select * from t1 where (a+1) IN (1,2,3);
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | range | vcol | vcol | 5 | NULL | 3 | Using where |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
1 row in set (0.006 sec)
|
Here, it doesn't:
MariaDB [test]> explain select * from t1 where (a+1, b) IN ((1,1),(2,2),(3,3));
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 6 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
1 row in set (0.007 sec)
|
while it would work if it was done "manually":
 |
MariaDB [test]> explain select * from t1 where (vcol, b) IN ((1,1),(2,2),(3,3));
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | range | vcol | vcol | 5 | NULL | 3 | Using where |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
1 row in set (0.006 sec)
|
Attachments
Issue Links
- is part of
-
MDEV-35913 Assertion `m_comparator.cmp_type() != ROW_RESULT' failed in bool Item_func_in::compatible_types_scalar_bisection_possible()
-
- Closed
-