|
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 |
|
+------+--------------------+-----------------+----------------+---------------+--------------+---------+----------------------------------------+------+---------------------------------+
|
|
bb-trunk-pm.sql