Details
-
Type:
Bug
-
Status: Closed (View Workflow)
-
Priority:
Major
-
Resolution: Duplicate
-
Affects Version/s: 10.4.13, 10.6.3
-
Fix Version/s: N/A
-
Component/s: Optimizer
-
Environment:Linux n.a.
-
Epic/Theme:
Description
Optimizer does a wrong cast from column string to number instead of constant number to string:
source sales_flat_order_dump.sql
|
analyze table sales_flat_order;
|
 |
explain
|
SELECT `main_table`.* FROM `sales_flat_order` AS `main_table` WHERE (`increment_id` >= 238900 AND `increment_id` <= 238907)
|
;
|
+------+-------------+------------+------+-----------------------------------+------+---------+------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+------+-----------------------------------+------+---------+------+--------+-------------+
|
| 1 | SIMPLE | main_table | ALL | UNQ_SALES_FLAT_ORDER_INCREMENT_ID | NULL | NULL | NULL | 260559 | Using where |
|
+------+-------------+------------+------+-----------------------------------+------+---------+------+--------+-------------+
|
|
Execution time:
3 rows in set (1.467 sec)
explain
|
SELECT `main_table`.* FROM `sales_flat_order` AS `main_table` WHERE (`increment_id` >= '238900' AND `increment_id` <= '238907')
|
;
|
+------+-------------+------------+-------+-----------------------------------+-----------------------------------+---------+------+------+-----------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+-------+-----------------------------------+-----------------------------------+---------+------+------+-----------------------+
|
| 1 | SIMPLE | main_table | range | UNQ_SALES_FLAT_ORDER_INCREMENT_ID | UNQ_SALES_FLAT_ORDER_INCREMENT_ID | 153 | NULL | 3 | Using index condition |
|
+------+-------------+------------+-------+-----------------------------------+-----------------------------------+---------+------+------+-----------------------+
|
|
Execution time:
3 rows in set (0.002 sec)
This is reproducible at will. See example attached.
Attachments
Issue Links
- is duplicated by
-
MDEV-18089 optimizer is wrong
-
- Closed
-