Runaway cpu and temporary table problem when creating view in account_analytic_analysis

[Ronald Portier (Therp)]

The view account_analytic_analysis_summary_user contains an unqualified join in a subselect, leading to a cartesian product of the form res_users * res_users * account_analytic_account.

When these tabels contain a high number of rows, the attempt to create the view will result in 100% cpu usage, and/or may crash.

We see this in the log:

Server Traceback (most recent call last):
  File "/home/oerhbrupg61/openerp-web/addons/web/common/http.py", line 592, in send
    result = openerp.netsvc.dispatch_rpc(service_name, method, args)
  File "/home/oerhbrupg61/bzr/openobject-server/openerp/netsvc.py", line 360, in dispatch_rpc
    result = ExportService.getService(service_name).dispatch(method, params)
  File "/home/oerhbrupg61/bzr/openobject-server/openerp/service/web_services.py", line 586, in dispatch
    res = fn(db, uid, *params)
  File "/home/oerhbrupg61/bzr/openobject-server/openerp/osv/osv.py", line 167, in execute_kw
    return self.execute(db, uid, obj, method, *args, **kw or {})
  File "/home/oerhbrupg61/bzr/openobject-server/openerp/osv/osv.py", line 121, in wrapper
    return f(self, dbname, *args, **kwargs)
  File "/home/oerhbrupg61/bzr/openobject-server/openerp/osv/osv.py", line 176, in execute
    res = self.execute_cr(cr, uid, obj, method, *args, **kw)
  File "/home/oerhbrupg61/openobject-addons/audittrail/audittrail.py", line 495, in execute_cr
    return fct_src(cr, uid, model, method, *args)
  File "/home/oerhbrupg61/bzr/openobject-server/openerp/osv/osv.py", line 164, in execute_cr
    return getattr(object, method)(cr, uid, *args, **kw)
  File "/home/oerhbrupg61/bzr/openobject-server/openerp/osv/orm.py", line 3406, in read
    result = self._read_flat(cr, user, select, fields, context, load)
  File "/home/oerhbrupg61/bzr/openobject-server/openerp/osv/orm.py", line 3465, in _read_flat
    cr.execute(query, (tuple(sub_ids),))
  File "/home/oerhbrupg61/bzr/openobject-server/openerp/sql_db.py", line 152, in wrapper
    return f(self, *args, **kwargs)
  File "/home/oerhbrupg61/bzr/openobject-server/openerp/sql_db.py", line 212, in execute
    res = self._obj.execute(query, params)
OperationalError: could not write block 1059628 of temporary file: No space left on device
HINT: Perhaps out of disk space?

The problem view creation looks like this (in file account-analytic_analysis.py):

    def init(self, cr):
        tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_user')
        cr.execute('CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (' \
                'SELECT ' \
                    '(u.account_id * u.max_user) + u."user" AS id, ' \
                    'u.account_id AS account_id, ' \
                    'u."user" AS "user", ' \
                    'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
                'FROM ' \
                    '(SELECT ' \
                        'a.id AS account_id, ' \
                        'u1.id AS "user", ' \
                        'MAX(u2.id) AS max_user ' \
                    'FROM ' \
                        'res_users AS u1, ' \
                        'res_users AS u2, ' \
                        'account_analytic_account AS a ' \
                    'GROUP BY u1.id, a.id ' \
                    ') AS u ' \
                'LEFT JOIN ' \
                    '(SELECT ' \
                        'l.account_id AS account_id, ' \
                        'l.user_id AS "user", ' \
                        'SUM(l.unit_amount) AS unit_amount ' \
                    'FROM account_analytic_line AS l, ' \
                        'account_analytic_journal AS j ' \
                    'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
                    'GROUP BY l.account_id, l.user_id ' \
                    ') AS l '
                    'ON (' \
                        'u.account_id = l.account_id ' \
                        'AND u."user" = l."user"' \
                    ') ' \
                'GROUP BY u."user", u.account_id, u.max_user' \
                ')')

The problem might be resolved by rewriting the view to prevent the cartesian product explosion.

The problematic code is present in 6.0, 6.1 and in trunk.

We will try to submit a fix.

[Stefan Rijnhart (Opener) (stefan-opener)]

For the record, after the branch on lp:openobject-addons was merged, I merged it likewise on lp:therp-backports/6.1 (that is, while keeping the drop view statement). So there is no MP on the backports branch.