Edgewall Software
Modify

Opened 6 years ago

Closed 5 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:

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 by Jun Omae, 6 years ago

Owner: set to Jun Omae
Status: newassigned

comment:2 by Christian Boos, 6 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 Jun Omae, 6 years ago

Keywords: custom fields added
Release Notes: modified (diff)
Resolution: fixed
Status: assignedclosed

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 Jun Omae, 6 years ago

Resolution: fixed
Status: closedreopened

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 Ryan J Ollos <ryan.j.ollos@…>, 6 years ago

Cc: ryan.j.ollos@… added

comment:6 by Jun Omae, 6 years ago

Resolution: fixed
Status: reopenedclosed

[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 Jun Omae, 5 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].

comment:8 by Jun Omae, 5 years ago

Cc: Ryan J Ollos added; ryan.j.ollos@… removed
Resolution: fixed
Status: closedreopened

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 5 years ago by Jun Omae (previous) (diff)

in reply to:  8 comment:9 by Jun Omae, 5 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 Jun Omae, 5 years ago

Resolution: fixed
Status: reopenedclosed

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

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Jun Omae.
The resolution will be deleted.
to The owner will be changed from Jun Omae to the specified user.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.