Opened 11 years ago
Last modified 10 years ago
#11140 closed defect
`Query` with many custom fields doesn't work — at Initial Version
Reported by: | Jun Omae | Owned by: | |
---|---|---|---|
Priority: | normal | Milestone: | 0.12.6 |
Component: | query system | Version: | 0.12-stable |
Severity: | normal | Keywords: | sqlite mysql custom fields |
Cc: | Ryan J Ollos | Branch: | |
Release Notes: | |||
API Changes: | |||
Internal Changes: |
Description
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.
- SQLite
-
OperationalError: at most 32 tables in a join
- MySQL
-
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 JOIN
s 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 ...
Note:
See TracTickets
for help on using tickets.