Opened 11 years ago
Closed 10 years ago
#11140 closed defect (fixed)
`Query` with many custom fields doesn't work
Reported by: | Jun Omae | Owned by: | Jun Omae |
---|---|---|---|
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: |
Avoid limitation of joins with many custom fields for ticket query |
||
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 ...
Attachments (0)
Change History (10)
comment:1 by , 11 years ago
Owner: | set to |
---|---|
Status: | new → assigned |
comment:2 by , 11 years ago
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.
comment:3 by , 11 years ago
Keywords: | custom fields added |
---|---|
Release Notes: | modified (diff) |
Resolution: | → fixed |
Status: | assigned → closed |
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].
comment:4 by , 11 years ago
Resolution: | fixed |
---|---|
Status: | closed → reopened |
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
comment:5 by , 11 years ago
Cc: | added |
---|
comment:6 by , 11 years ago
Resolution: | → fixed |
---|---|
Status: | reopened → closed |
[7dd500f6/jomae.git], verified on SQLite 3.3.6, MySQL 5.0.95 and PostgreSQL 8.1.23. Fixed again in [11785-11787].
comment:7 by , 11 years ago
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].
follow-up: 9 comment:8 by , 10 years ago
Cc: | added; removed |
---|---|
Resolution: | fixed |
Status: | closed → reopened |
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 17:28:52,972 Trac[formatter] DEBUG: Executing Wiki macro TicketQuery by provider <trac.ticket.query.TicketQueryMacro object at 0x8a9086c> 2013-12-04 17:28:53,014 Trac[util] DEBUG: SQL: u'SELECT COUNT(*) FROM (SELECT t.status AS status,t.summary AS summary,t.id AS id,t.summary AS summary,t.component AS component,t.status AS status,t.version AS version,t.milestone AS milestone,t.priority AS priority,t.time AS time,t.reporter AS reporter,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.summary AS summary,t.component AS component,t.status AS status,t.version AS version,t.milestone AS milestone,t.priority AS priority,t.time AS time,t.reporter AS reporter,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)\nWHERE ((CAST(t.time AS bigint)>=%s))\nORDER BY COALESCE(t.time,0)=0,t.time,t.id) AS foo' 2013-12-04 17:28:53,014 Trac[util] DEBUG: args: [1386082800000000L] 2013-12-04 17:28:53,016 Trac[util] DEBUG: execute exception: ProgrammingError('column reference "status" is ambiguous\n',) 2013-12-04 17:28:53,019 Trac[formatter] ERROR: Macro TicketQuery(order=time,time=today..,col=id|summary|component|status|version|milestone|priority|time|reporter|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 54, in execute r = self.cursor.execute(sql_escape_percent(sql), args) ProgrammingError: column reference "status" is ambiguous
comment:9 by , 10 years ago
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'")
comment:10 by , 10 years ago
Resolution: | → fixed |
---|---|
Status: | reopened → closed |
The issues in comment:8 and comment:9 have been fixed in [12298] and merged in [12299-12300].
Worked in jomae.git:ticket11140/0.12, jomae.git:ticket11140/1.0.