Edgewall Software
Modify

Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#12175 closed defect (fixed)

"OperationalError: at most 32 tables in a join" from trac.ticket.tests.query with SQLite 32-bit

Reported by: Jun Omae Owned by: Jun Omae
Priority: normal Milestone: 1.0.9
Component: query system Version: 1.0.8
Severity: normal Keywords:
Cc: Branch:
Release Notes:

Fix regression in [14140], at most 32 tables in a join on SQLite 32-bit.

API Changes:
Internal Changes:

Description

Regression from [14140] (#12113). I didn't test with Python 32-bit.

python setup.py -q test -s trac.ticket.tests.query.suite
...........................................E...................
======================================================================
ERROR: test_query_using_joins (trac.ticket.tests.query.QueryTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/src/trac/edgewall/git/trac/ticket/tests/query.py", line 466, in test_query_using_joins
    tickets = query.execute(self.req)
  File "/src/trac/edgewall/git/trac/ticket/query.py", line 301, in execute
    self.num_items = self._count(sql, args)
  File "/src/trac/edgewall/git/trac/ticket/query.py", line 282, in _count
    % sql, args)[0][0]
  File "/src/trac/edgewall/git/trac/db/api.py", line 124, in execute
    return db.execute(query, params)
  File "/src/trac/edgewall/git/trac/db/util.py", line 128, in execute
    cursor.execute(query, params if params is not None else [])
  File "/src/trac/edgewall/git/trac/db/util.py", line 61, in execute
    r = self.cursor.execute(sql_escape_percent(sql), args)
  File "/src/trac/edgewall/git/trac/db/sqlite_backend.py", line 82, in execute
    result = PyFormatCursor.execute(self, *args)
  File "/src/trac/edgewall/git/trac/db/sqlite_backend.py", line 60, in execute
    args or [])
  File "/src/trac/edgewall/git/trac/db/sqlite_backend.py", line 52, in _rollback_on_error
    return function(self, *args, **kwargs)
OperationalError: at most 32 tables in a join
  • trac/ticket/query.py

    diff --git a/trac/ticket/query.py b/trac/ticket/query.py
    index 53f2737..94db7b5 100644
    a b class Query(object):  
    449449        list_fields = [f['name'] for f in self.fields
    450450                                 if f['type'] == 'text' and
    451451                                    f.get('format') == 'list']
    452         # 32 is max of joins in SQLite 32-bit, 3 is for order, group and
     452        # 31 is max of joins in SQLite 32-bit, 3 is for order, group and
    453453        # "priority" columns
    454         use_joins = len(set(cols) & set(custom_fields)) <= 32 - 3
     454        use_joins = len(set(cols) & set(custom_fields)) <= 31 - 3
    455455
    456456        sql = []
    457457        sql.append("SELECT " + ",".join(['t.%s AS %s' % (c, c) for c in cols
  • trac/ticket/tests/query.py

    diff --git a/trac/ticket/tests/query.py b/trac/ticket/tests/query.py
    index bb4edfd..9a4a435 100644
    a b ORDER BY COALESCE(t.id,0)=0,t.id""")  
    459459        query = Query.from_string(
    460460            self.env, 'col_12=12.col_12&' +
    461461                      'order=resolution&group=severity&col=id&col=summary' +
    462                       ''.join('&col=col_%02d' % idx for idx in xrange(29)))
     462                      ''.join('&col=col_%02d' % idx for idx in xrange(28)))
    463463        sql, args = query.get_sql()
    464         self.assertEqual(['enum'] * 3 + ['ticket_custom'] * 29,
     464        self.assertEqual(['enum'] * 3 + ['ticket_custom'] * 28,
    465465                         self._get_join_tables(sql))
    466466        tickets = query.execute(self.req)
    467467        self.assertEqual(1, len(tickets))
    ORDER BY COALESCE(t.id,0)=0,t.id""")  
    469469        query = Query.from_string(
    470470            self.env, 'col_12=12.col_12&' +
    471471                      'order=milestone&group=version&col=id&col=summary' +
    472                       ''.join('&col=col_%02d' % idx for idx in xrange(29)))
     472                      ''.join('&col=col_%02d' % idx for idx in xrange(28)))
    473473        sql, args = query.get_sql()
    474         self.assertEqual(['enum', 'milestone'] + ['ticket_custom'] * 29 +
     474        self.assertEqual(['enum', 'milestone'] + ['ticket_custom'] * 28 +
    475475                         ['version'],
    476476                         self._get_join_tables(sql))
    477477        tickets = query.execute(self.req)
    ORDER BY COALESCE(t.id,0)=0,t.id""")  
    480480        query = Query.from_string(
    481481            self.env, 'col_12=12.col_12&' +
    482482                      'order=resolution&group=severity&col=id&col=summary' +
    483                       ''.join('&col=col_%02d' % idx for idx in xrange(30)))
     483                      ''.join('&col=col_%02d' % idx for idx in xrange(29)))
    484484        sql, args = query.get_sql()
    485485        self.assertEqual(['enum'] * 3, self._get_join_tables(sql))
    486486        tickets = query.execute(self.req)
    ORDER BY COALESCE(t.id,0)=0,t.id""")  
    490490        query = Query.from_string(
    491491            self.env, 'col_12=12.col_12&' +
    492492                      'order=milestone&group=version&col=id&col=summary' +
    493                       ''.join('&col=col_%02d' % idx for idx in xrange(30)))
     493                      ''.join('&col=col_%02d' % idx for idx in xrange(29)))
    494494        sql, args = query.get_sql()
    495495        self.assertEqual(['enum', 'milestone', 'version'],
    496496                         self._get_join_tables(sql))

Attachments (0)

Change History (2)

comment:1 by Jun Omae, 5 years ago

Release Notes: modified (diff)
Resolution: fixed
Status: newclosed

Fixed in [14268] and merged to trunk in [14269].

comment:2 by Ryan J Ollos, 5 years ago

Owner: set to Jun Omae

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Jun Omae.
The resolution will be deleted. Next status will be 'reopened'.
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.