Edgewall Software

Opened 9 years ago

Closed 9 years ago

Last modified 9 years ago

#12175 closed defect (fixed)

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

Reported by: Jun Omae Owned by:
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))

Change History (1)

comment:1 by Jun Omae, 9 years ago

Release Notes: modified (diff)
Resolution: → fixed
Status: new → closed

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

Note: See TracTickets for help on using tickets.