Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.5
-
Arch x86_64
Description
Two Tables:
CREATE TABLE `tage` ( |
`datum` date NOT NULL, |
KEY `i_datum` (`datum`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
 |
CREATE TABLE `mitarbeiter` ( |
`pk` int(11) NOT NULL DEFAULT 0, |
`in_aw_liste` int(1) DEFAULT 1, |
`deaktiviert` int(1) NOT NULL DEFAULT 0 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
Query:
select distinct mar.deaktiviert, in_aw_liste from mitarbeiter mar join tage t where t.datum >= '2017-04-01' and mar.deaktiviert = 0 and in_aw_liste=1; |
Result in Version 10.2.5:
+-------------+-------------+
|
| deaktiviert | in_aw_liste |
|
+-------------+-------------+
|
| 0 | 1 |
|
| 1 | 1 |
|
| 0 | 0 |
|
| 1 | 0 |
|
+-------------+-------------+
|
but should be (as in Version 10.2.3 or/and without the index i_datum):
+-------------+-------------+
|
| deaktiviert | in_aw_liste |
|
+-------------+-------------+
|
| 0 | 1 |
|
+-------------+-------------+
|
The result of
explain extended select distinct mar.deaktiviert, in_aw_liste from mitarbeiter mar join tage t where t.datum >= '2017-04-01' and mar.deaktiviert = 0 and in_aw_liste=1; |
with index i_datum
+------+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------------------------------------+
|
| 1 | SIMPLE | t | range | i_datum | i_datum | 3 | NULL | 275 | 100.00 | Using where; Using index; Using temporary |
|
| 1 | SIMPLE | mar | ALL | NULL | NULL | NULL | NULL | 575 | 100.00 | Using where |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------------------------------------+
|
show warnings;
|
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select distinct `tmp`.`mar`.`deaktiviert` AS `deaktiviert`,`tmp`.`mar`.`in_aw_liste` AS `in_aw_liste` from `tmp`.`mitarbeiter` `mar` join `tmp`.`tage` `t` where `tmp`.`mar`.`deaktiviert` = 0 and `tmp`.`mar`.`in_aw_liste` = 1 and `tmp`.`t`.`datum` >= '2017-04-01' |
|
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
The same without index on tage (datum):
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------------------+
|
| 1 | SIMPLE | mar | ALL | NULL | NULL | NULL | NULL | 575 | 100.00 | Using where; Using temporary |
|
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 4018 | 100.00 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------------------+
|
Warning:
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select distinct `tmp`.`mar`.`deaktiviert` AS `deaktiviert`,`tmp`.`mar`.`in_aw_liste` AS `in_aw_liste` from `tmp`.`mitarbeiter` `mar` join `tmp`.`tage` `t` where `tmp`.`mar`.`deaktiviert` = 0 and `tmp`.`mar`.`in_aw_liste` = 1 and `tmp`.`t`.`datum` >= '2017-04-01' |
|
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Attachments
Issue Links
- is caused by
-
-
- Closed
-