Edgewall Software
Modify

Opened 16 months ago

Closed 8 months ago

#11140 closed defect (fixed)

`Query` with many custom fields doesn't work

Reported by: jomae Owned by: jomae
Priority: normal Milestone: 0.12.6
Component: query system Version: 0.12-stable
Severity: normal Keywords: sqlite mysql custom fields
Cc: rjollos
Release Notes:

Avoid limitation of joins with many custom fields for ticket query

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

Attachments (0)

Change History (10)

comment:1 Changed 16 months ago by jomae

  • Owner set to jomae
  • Status changed from new to assigned

comment:2 Changed 16 months ago by cboos

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 Changed 16 months ago by jomae

  • Keywords custom fields added
  • Release Notes modified (diff)
  • Resolution set to fixed
  • Status changed from assigned to 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 Changed 15 months ago by jomae

  • Resolution fixed deleted
  • Status changed from closed to 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 Changed 15 months ago by Ryan J Ollos <ryan.j.ollos@…>

  • Cc ryan.j.ollos@… added

comment:6 Changed 15 months ago by jomae

  • Resolution set to fixed
  • Status changed from reopened to 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 Changed 11 months ago by jomae

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

comment:8 follow-up: Changed 8 months ago by jomae

  • Cc rjollos added; ryan.j.ollos@… removed
  • Resolution fixed deleted
  • Status changed from closed to 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 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
Last edited 8 months ago by jomae (previous) (diff)

comment:9 in reply to: ↑ 8 Changed 8 months ago by jomae

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 Changed 8 months ago by jomae

  • Resolution set to fixed
  • Status changed from reopened to closed

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

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed The owner will remain jomae.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from jomae to the specified user.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.