`Query` with many custom fields doesn't work
|Reported by:||Jun Omae||Owned by:||Jun Omae|
|Severity:||normal||Keywords:||sqlite mysql custom fields|
|Cc:||Ryan J Ollos|
Avoid limitation of joins with many custom fields for ticket query
The following error occurs when selecting many custom fields in query page. Also the same has occured with th:ExcelDownloadPlugin which internally adds all custom fields to query.
OperationalError: at most 32 tables in a join
OperationalError: (1116, 'Too many tables; MySQL can only use 61 tables in a join')
This error is caused by the limitation of joins. PostgreSQL has no limitation of joins.
Workaround, using sub queries instead of
JOINs to retrieve custom fields to avoid it.
SELECT id,summary,....,`field_1`,`field_2` FROM ticket AS t LEFT JOIN (SELECT id, (SELECT value FROM ticket_custom c WHERE c.ticket=t.id AND c.name='field_1') AS `field_1`, (SELECT value FROM ticket_custom c WHERE c.ticket=t.id AND c.name='field_2') AS `field_2` FROM ticket) AS c WHERE ...
Change History (10)
comment:3 Changed 2 years ago by
- Keywords custom fields added
- Release Notes modified (diff)
- Resolution set to fixed
- Status changed from assigned to closed
comment:8 follow-up: ↓ 9 Changed 21 months ago by
- Cc Ryan J Ollos added; ryan.j.ollos@… removed
- Resolution fixed deleted
- Status changed from closed to reopened