`Query` with many custom fields doesn't work

[Jun Omae]

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 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 ...

[Jun Omae]

Worked in jomae.git:ticket11140/0.12, jomae.git:ticket11140/1.0.

[Christian Boos]

Ingenious!

Looks good to me. Tests pass on my side (1.0 + sqlite, 0.12 + mysql), plus a few direct testing with my own custom fields.

[Jun Omae]

Thanks, Christian!

All unit and functional tests pass with SQLite 3.3.6, MySQL 5.0.95 and PostgreSQL 8.1.23. Committed in [11753].