accounting report filter: period start and end are sometimes swapped

[Stéphane Bidoul (Acsone)]

In the accounting reports wizard, when the Period filter is selected, *sometimes* the start and end period are inverted.

This bug is due to the UNION statement in the query, which attempts to eliminate duplicates and does not preserve order (see http://www.postgresql.org/docs/8.4/interactive/queries-union.html).

Using UNION ALL instead solves the problem.

Patch attached.

Best regards,

-sbi

[Jignesh Rathod(OpenERP) (jir-openerp)]

Hello Stéphane,

I have checked this issue and it works properly at my end.
so would you please provide more information such a
screen shot or video.

I have checked this issue at revision no :

Trunk :

  New web Client : 1188
    Server : 3715
    addons : 5289
                Gtk -Client : 1964

Thanks & wait for your replay.

[Stéphane Bidoul (Acsone) (sbi)]

Hello,

I had the problem with our own data, but the problem is very difficult to reproduce with demo data, since it depends on the ordering behaviour of the database.

It is however very clear that UNION does not guarantee to preserve order (as explained in the link I provided above, so there are situations where the start and end date will be reversed.

Best regards,

-sbi

[Stéphane Bidoul (Acsone) (sbi)]

Hello,

I attached a short video illustrating the issue.

Best regards,

-sbi

[Jignesh Rathod(OpenERP) (jir-openerp)]

Hello Stéphane Bidoul ,

I am confirming this issue as per postgresql documentation.

Thank you.

[Bharat Devnani (Open ERP) (bde-openerp)]

Hello Stéphane Bidoul,

Thanks for your time, I have checked your mentioned problem. But according to the latest changes made in trunk, user has to select Start Period and End Period manually due to the line pointed below which is in account_report_common.py.

cr.execute('''
                SELECT * FROM (SELECT p.id
                               FROM account_period p
                               LEFT JOIN account_fiscalyear f ON (p.fiscalyear_id = f.id)
                               WHERE f.id = %s
                               AND p.special = false <------------------------
                               ORDER BY p.date_start ASC, p.special ASC
                               LIMIT 1) AS period_start
                UNION
                SELECT * FROM (SELECT p.id
                               FROM account_period p
                               LEFT JOIN account_fiscalyear f ON (p.fiscalyear_id = f.id)
                               WHERE f.id = %s
                               AND p.date_start < NOW()
                               AND p.special = false <------------------------
                               ORDER BY p.date_stop DESC
                               LIMIT 1) AS period_stop''', (fiscalyear_id, fiscalyear_id))

So, there are no chances that the start and end period are inverted through onchange, until p.special = false becomes true.

Hope this will help you.

Thanks & Regards,
Devnani Bharat R.

[Stéphane Bidoul (Acsone) (sbi)]

Hello,

I have tested by backporting the "p.special = false" line in the 6.0 code, and the inversion shown in my video is still there.

The problem is not in each individual SELECT but comes from the fact that UNION does not garantee to return the result of the first SELECT before the second, where UNION ALL does.

Best regards,

-sbi

[Bharat Devnani (Open ERP) (bde-openerp)]

Hello Stéphane Bidoul,

Thanks for reply and your contribution, we have found that the order of columns are not preserved with UNION, the solution of this bug will be merged in main addons soon. Following are Revision ID and Revision Number of the proposed branch :

Revision ID : <email address hidden>
Revision Number : 5391

Thanks & Regards,
Devnani Bharat R.

[qdp (OpenERP) (qdp)]

fix released in revision 5393

thanks

[qdp (OpenERP) (qdp)]

sorry: 5384