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

[Jun Omae]

If both id and any custom fields are filtered in query page, the following error occurs. The issue has been reported in ​th:#11028.

OperationalError: ambiguous column name: id

[Jun Omae]

[7dd500f6/jomae.git], verified on SQLite 3.3.6, MySQL 5.0.95 and PostgreSQL 8.1.23. Fixed again in [11785-11787].

[Jun Omae]

After r11753, the Query module is very slow on MySQL if ticket_custom table is large (> 200,000 records). The problem has been fixed in [11978] and merged in [11979-11980].

[Jun Omae]

The following macro with PostgreSQL leads ProgrammingError: column reference "status" is ambiguous. The issue originally is reported in #11385.

[[TicketQuery(col=id|status|col1)]]

2013-12-04 18:50:49,140 Trac[util] DEBUG: SQL: u'SELECT COUNT(*) FROM (SELECT t.status AS status,t.summary AS summary,t.id AS id,t.status AS status,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS priority_value,t."col1" AS "col1"\nFROM (\n  SELECT t.status AS status,t.summary AS summary,t.id AS id,t.status AS status,t.priority AS priority,t.time AS time,t.changetime AS changetime,\n  (SELECT c.value FROM ticket_custom c WHERE c.ticket=t.id AND c.name=\'col1\') AS "col1"\n  FROM ticket AS t) AS t\n  LEFT OUTER JOIN enum AS priority ON (priority.type=\'priority\' AND priority.name=priority)\nORDER BY COALESCE(t.id,0)=0,t.id) AS foo'
2013-12-04 18:50:49,143 Trac[util] DEBUG: execute exception: <psycopg2.ProgrammingError instance at 0xa4ce38c>
2013-12-04 18:50:49,148 Trac[formatter] ERROR: Macro TicketQuery(col=id|status|col1) failed:
Traceback (most recent call last):
  File "/home/jun66j5/src/trac/edgewall/git/trac/wiki/formatter.py", line 720, in _macro_formatter
    return macro.process(args, in_paragraph=True)
  File "/home/jun66j5/src/trac/edgewall/git/trac/wiki/formatter.py", line 304, in process
    text = self.processor(text)
  File "/home/jun66j5/src/trac/edgewall/git/trac/wiki/formatter.py", line 291, in _macro_processor
    text)
  File "/home/jun66j5/src/trac/edgewall/git/trac/ticket/query.py", line 1302, in expand_macro
    tickets = query.execute(req)
  File "/home/jun66j5/src/trac/edgewall/git/trac/ticket/query.py", line 302, in execute
    self.num_items = self._count(sql, args, db)
  File "/home/jun66j5/src/trac/edgewall/git/trac/ticket/query.py", line 283, in _count
    cursor.execute(count_sql, args)
  File "/home/jun66j5/src/trac/edgewall/git/trac/db/util.py", line 56, in execute
    r = self.cursor.execute(sql)
ProgrammingError: column reference "status" is ambiguous

[Jun Omae]

The following macro with PostgreSQL leads ProgrammingError: column reference "status" is ambiguous. The issue originally is reported in #11385.

[[TicketQuery(col=id|status|col1)]]

The issue is reproduced when the macro with col parameter and other format=table. The col parameter is used for format=table. The macro works well if format=table.

Also, Trac 0.12.5 with MySQL has the same issue. I'll fix it here.

2013-12-04 20:00:41,692 Trac[util] DEBUG: SQL: u"SELECT COUNT(*) FROM (SELECT t.status AS status,t.summary AS summary,t.id AS id,t.status AS status,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS priority_value,`col1`.value AS `col1`\nFROM ticket AS t\n  LEFT OUTER JOIN ticket_custom AS `col1` ON (id=`col1`.ticket AND `col1`.name='col1')\n  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)\nORDER BY COALESCE(t.id,0)=0,t.id) AS foo"
2013-12-04 20:00:41,693 Trac[util] DEBUG: execute exception: <_mysql_exceptions.OperationalError instance at 0x92d9f2c>
2013-12-04 20:00:41,725 Trac[formatter] ERROR: Macro TicketQuery(col=id|status|col1) failed:
Traceback (most recent call last):
  File "/home/jun66j5/venv/trac/0.12.5/lib/python2.4/site-packages/trac/wiki/formatter.py", line 720, in _macro_formatter
    return macro.process(args, in_paragraph=True)
  File "/home/jun66j5/venv/trac/0.12.5/lib/python2.4/site-packages/trac/wiki/formatter.py", line 304, in process
    text = self.processor(text)
  File "/home/jun66j5/venv/trac/0.12.5/lib/python2.4/site-packages/trac/wiki/formatter.py", line 291, in _macro_processor
    text)
  File "/home/jun66j5/venv/trac/0.12.5/lib/python2.4/site-packages/trac/ticket/query.py", line 1295, in expand_macro
    tickets = query.execute(req)
  File "/home/jun66j5/venv/trac/0.12.5/lib/python2.4/site-packages/trac/ticket/query.py", line 301, in execute
    self.num_items = self._count(sql, args, db)
  File "/home/jun66j5/venv/trac/0.12.5/lib/python2.4/site-packages/trac/ticket/query.py", line 282, in _count
    cursor.execute(count_sql, args)
  File "/home/jun66j5/venv/trac/0.12.5/lib/python2.4/site-packages/trac/db/util.py", line 56, in execute
    r = self.cursor.execute(sql)
  File "/usr/lib/python2.4/site-packages/MySQLdb/cursors.py", line 173, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.4/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (1060, "Duplicate column name 'status'")

[Jun Omae]

The issues in comment:8 and comment:9 have been fixed in [12298] and merged in [12299-12300].