Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
10.11.10
-
None
Description
Wrong optimizer result in mariadb 10.11.10 with update (with select is good)
If the engine Innodb wrong result, with Myisam is good.
|
|
DROP TABLE if EXISTS workflow_current_status; |
|
|
CREATE TABLE `workflow_current_status` ( |
`workflow_id` smallint(5) unsigned NOT NULL, |
`job_id` smallint(5) unsigned NOT NULL, |
`workflow_status` tinyint(3) unsigned NOT NULL, |
`workflow_run_id` int(11) unsigned NOT NULL, |
PRIMARY KEY (`workflow_run_id`,`job_id`), |
KEY `FK_workflow_current_status_job` (`job_id`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; |
|
|
INSERT INTO workflow_current_status (workflow_id,job_id,workflow_status,workflow_run_id) |
SELECT 1,seq,0,0 FROM seq_1_to_100; |
|
|
INSERT INTO workflow_current_status (workflow_id,job_id,workflow_status,workflow_run_id) |
SELECT 1,seq,0,1 FROM seq_1_to_100; |
INSERT INTO workflow_current_status (workflow_id,job_id,workflow_status,workflow_run_id) |
SELECT 2,seq,0,2 FROM seq_1_to_100; |
|
|
explain UPDATE workflow_current_status AS wcs |
SET wcs.workflow_status=1 |
WHERE wcs.workflow_run_id=0 AND wcs.job_id=1; |
|
|
DROP TABLE if EXISTS workflow_current_status; |
|
|
|
result in 10.6.20 (PRIMARY KEY)
+------+-------------+-------+-------+----------------------------------------+---------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+----------------------------------------+---------+---------+------+------+-------------+
|
| 1 | SIMPLE | wcs | range | PRIMARY,FK_workflow_current_status_job | PRIMARY | 6 | NULL | 1 | Using where |
|
+------+-------------+-------+-------+----------------------------------------+---------+---------+------+------+-------------+
|
|
result in 10.11.10
+------+-------------+-------+-------+----------------------------------------+--------------------------------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+----------------------------------------+--------------------------------+---------+------+------+-------------+
|
| 1 | SIMPLE | wcs | range | PRIMARY,FK_workflow_current_status_job | FK_workflow_current_status_job | 6 | NULL | 1 | Using where |
|
+------+-------------+-------+-------+----------------------------------------+--------------------------------+---------+------+------+-------------+
|
|