Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.4.11
-
None
Description
Following query get a different result in 10.4.11 from 10.4.10:
SELECT DISTINCT |
egw_pm_projects.*,
|
egw_pm_projects.pm_id AS pm_id, |
BIT_OR(role_acl) AS role_acl |
FROM
|
egw_pm_projects
|
LEFT JOIN egw_pm_members ON( |
egw_pm_projects.pm_id = egw_pm_members.pm_id AND egw_pm_members.member_uid IN(5, -2, -189, -1, -11) |
)
|
LEFT JOIN egw_pm_roles ON egw_pm_members.role_id = egw_pm_roles.role_id |
WHERE
|
egw_pm_projects.pm_id IN( |
SELECT
|
*
|
FROM
|
(
|
SELECT DISTINCT |
egw_pm_projects.pm_id
|
FROM
|
egw_pm_projects
|
LEFT JOIN egw_pm_members ON( |
egw_pm_projects.pm_id = egw_pm_members.pm_id AND egw_pm_members.member_uid IN(5, -2, -189, -1, -11) |
)
|
LEFT JOIN egw_pm_roles ON egw_pm_members.role_id = egw_pm_roles.role_id |
LEFT JOIN egw_pm_extra ON egw_pm_projects.pm_id = egw_pm_extra.pm_id |
WHERE
|
pm_status = 'active' AND( |
pm_access = 'anonym' OR pm_access = 'public' AND pm_creator IN(-2,5,6,12,14,18,27,32,70,73,76,101,164,-1,7,13,16,44,62,64,68,69,81,89,116,122,146,169,183,203,381) OR pm_access = 'private' AND pm_creator IN(5) OR role_acl != 0 |
) AND( |
(
|
(
|
(
|
CONCAT(
|
CAST( |
COALESCE(egw_pm_projects.pm_number, '') AS CHAR |
),
|
CAST( |
COALESCE(egw_pm_projects.pm_title, '') AS CHAR |
),
|
CAST( |
COALESCE( |
egw_pm_projects.pm_description,
|
''
|
) AS CHAR |
),
|
CAST( |
COALESCE(egw_pm_projects.pm_access, '') AS CHAR |
),
|
CAST( |
COALESCE(egw_pm_projects.pm_status, '') AS CHAR |
),
|
CAST( |
COALESCE( |
egw_pm_projects.pm_accounting_type,
|
''
|
) AS CHAR |
)
|
) LIKE '%test%' OR egw_pm_projects.pm_id IN( |
SELECT
|
pm_id
|
FROM
|
egw_pm_extra
|
WHERE
|
pm_extra_value LIKE '%test%' AND pm_extra_name IN( |
'comments', |
'project_intern', |
'staatsanwalt', |
'tracker', |
'todo', |
'salesoffer', |
'select-account', |
'Abrechnungsinfo'
|
)
|
)
|
)
|
)
|
)
|
)
|
GROUP BY |
egw_pm_projects.pm_id
|
ORDER BY |
pm_created
|
DESC
|
LIMIT 0,
|
1000
|
) AS something |
)
|
GROUP BY |
egw_pm_projects.pm_id
|
ORDER BY |
pm_created
|
DESC |
When disabling split_materialized optimizer switch the query works correctly.
Before disabling the switch the explain showed :
10.4.10(good)
+------+--------------+-----------------+--------+---------------+---------+---------+----------------------------------------+------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------+-----------------+--------+---------------+---------+---------+----------------------------------------+------+-------------------------------------------------+
|
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 235 | Using temporary; Using filesort |
|
| 1 | PRIMARY | egw_pm_projects | eq_ref | PRIMARY | PRIMARY | 4 | something.pm_id | 1 | |
|
| 1 | PRIMARY | egw_pm_members | ref | PRIMARY | PRIMARY | 4 | something.pm_id | 1 | Using where |
|
| 1 | PRIMARY | egw_pm_roles | ALL | PRIMARY | NULL | NULL | NULL | 6 | Using where; Using join buffer (flat, BNL join) |
|
| 2 | MATERIALIZED | <derived3> | ALL | NULL | NULL | NULL | NULL | 235 | |
|
| 3 | DERIVED | egw_pm_projects | ALL | PRIMARY | NULL | NULL | NULL | 235 | Using where; Using temporary; Using filesort |
|
| 3 | DERIVED | egw_pm_members | ref | PRIMARY | PRIMARY | 4 | egw_pm_projects.egw_pm_projects.pm_id | 1 | Using where |
|
| 3 | DERIVED | egw_pm_roles | eq_ref | PRIMARY | PRIMARY | 4 | egw_pm_projects.egw_pm_members.role_id | 1 | Using where |
|
| 3 | DERIVED | egw_pm_extra | ref | PRIMARY | PRIMARY | 4 | egw_pm_projects.egw_pm_projects.pm_id | 1 | Using index |
|
| 4 | MATERIALIZED | egw_pm_extra | ALL | PRIMARY | NULL | NULL | NULL | 14 | Using where |
|
+------+--------------+-----------------+--------+---------------+---------+---------+----------------------------------------+------+-------------------------------------------------+
|
10.4.11 (bad)
+------+--------------------+-----------------+----------------+---------------+--------------+---------+----------------------------------------+------+---------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-----------------+----------------+---------------+--------------+---------+----------------------------------------+------+---------------------------------+
|
| 1 | PRIMARY | egw_pm_projects | index | PRIMARY | PRIMARY | 4 | NULL | 235 | Using temporary; Using filesort |
|
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | |
|
| 1 | PRIMARY | egw_pm_members | ref | PRIMARY | PRIMARY | 4 | egw_pm_projects.egw_pm_projects.pm_id | 1 | Using where |
|
| 1 | PRIMARY | egw_pm_roles | eq_ref | PRIMARY | PRIMARY | 4 | egw_pm_projects.egw_pm_members.role_id | 1 | Using where |
|
| 2 | MATERIALIZED | <derived3> | ALL | NULL | NULL | NULL | NULL | 235 | |
|
| 3 | LATERAL DERIVED | egw_pm_projects | eq_ref | PRIMARY | PRIMARY | 4 | egw_pm_projects.egw_pm_projects.pm_id | 1 | Using where |
|
| 3 | LATERAL DERIVED | egw_pm_members | ref | PRIMARY | PRIMARY | 4 | egw_pm_projects.egw_pm_projects.pm_id | 1 | Using where |
|
| 3 | LATERAL DERIVED | egw_pm_roles | eq_ref | PRIMARY | PRIMARY | 4 | egw_pm_projects.egw_pm_members.role_id | 1 | Using where |
|
| 3 | LATERAL DERIVED | egw_pm_extra | ref | PRIMARY | PRIMARY | 4 | egw_pm_projects.egw_pm_projects.pm_id | 1 | Using index |
|
| 4 | DEPENDENT SUBQUERY | egw_pm_extra | index_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
|
+------+--------------------+-----------------+----------------+---------------+--------------+---------+----------------------------------------+------+---------------------------------+
|
Attachments
Issue Links
- duplicates
-
-
- Closed
-
- relates to
-
-
- Closed
-