Editing a Journal Entry results in a duplicate entry on Tax Report

@joe: possible updation should be tried first before insertion or there is a primary key issue that is not satisfied in the query....

The sql default for listing the Journal Vouchers is:

SELECT COUNT(DISTINCT gl.tran_date, a.gl_seq, gl.type, gl.type_no) FROM 1_gl_trans as gl
         LEFT JOIN 1_audit_trail as a ON
            (gl.type=a.type AND gl.type_no=a.trans_no)
         LEFT JOIN 1_comments as com ON
            (gl.type=com.type AND gl.type_no=com.id)
         LEFT JOIN 1_refs as refs ON
            (gl.type=refs.type AND gl.type_no=refs.id)
         LEFT JOIN 1_users as u ON
            a.user=u.id
      WHERE (gl.tran_date >= '2015-03-08'
        AND gl.tran_date <= '2015-04-08'
        AND gl.amount!=0 AND gl.type='0');

In such display, when any element in the GROUP part of the SQL is null, the COUNT evaluates to NULL. The function sql_gen() in includes/db_pager.inc has:

            return "SELECT COUNT($group) FROM $from $where";

The comments table does not have any primary key and is the current order of fields correct?

CREATE TABLE `1_comments` (
  `type` int(11) NOT NULL DEFAULT '0',
  `id` int(11) NOT NULL DEFAULT '0',
  `date_` date DEFAULT '0000-00-00',
  `memo_` tinytext,
  KEY `type_and_id` (`type`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When the Journal Inquiry is done, first the total number of records is (COUNT) queried to determine the page links (Next/Prev/Total...). If it is a GROUP BY query, and one of the GROUP BY fields is NULL, then the total number of records count returned is 0 (Operation on NULL).

The full fix is given in this Forum Post but the real fix would be when the input data is consistent with all NULLs removed by defining appropriate defaults (blank string, 0, etc) in the table structure itself.

I followed the link to the fix but I have no idea what to do as I don't understand code.
is there any way this can be included in next update?

Thx

Subscribed.

MarkAndrew and seahawk, thank you for discovery and reporting this bug.

Is there a plan to fix this and what is the time frame?

Hi Joe,

Has this bug been corrected already ?
We experienced the same issue as Seahawk.

The only work around we found was to void and recreate the invoices impacted.

Thanks,
René

Kindly state the Mantis Bug Number and full link for ease of access.

I found this bug 2 years ago but I cannot find the cause. Just today that I tried to fix it again and found that the SQL in report709.php seems to be unreasonable as the following.

    $sql = "SELECT tt.name as taxname, taxrec.*, taxrec.amount*ex_rate AS amount,
                taxrec.net_amount*ex_rate AS net_amount,
                IF(taxrec.trans_type=".ST_BANKPAYMENT." OR taxrec.trans_type=".ST_BANKDEPOSIT.", 
                    IF(gl.person_type_id<>".PT_MISC.", gl.memo_, gl.person_id), 
                    IF(ISNULL(supp.supp_name), debt.name, supp.supp_name)) as name,
                branch.br_name
        FROM ".TB_PREF."trans_tax_details taxrec
        LEFT JOIN ".TB_PREF."tax_types tt
            ON taxrec.tax_type_id=tt.id
        LEFT JOIN ".TB_PREF."gl_trans gl 
            ON taxrec.trans_type=gl.type AND taxrec.trans_no=gl.type_no AND 
            (tt.purchasing_gl_code=gl.account OR tt.sales_gl_code=gl.account)
        LEFT JOIN ".TB_PREF."supp_trans strans
            ON taxrec.trans_no=strans.trans_no AND taxrec.trans_type=strans.type
        LEFT JOIN ".TB_PREF."suppliers as supp ON strans.supplier_id=supp.supplier_id
        LEFT JOIN ".TB_PREF."debtor_trans dtrans
            ON taxrec.trans_no=dtrans.trans_no AND taxrec.trans_type=dtrans.type
        LEFT JOIN ".TB_PREF."debtors_master as debt ON dtrans.debtor_no=debt.debtor_no
        LEFT JOIN ".TB_PREF."cust_branch as branch ON dtrans.branch_code=branch.branch_code
        WHERE (taxrec.amount <> 0 OR taxrec.net_amount <> 0)
            AND taxrec.trans_type <> ".ST_CUSTDELIVERY."
            AND taxrec.tran_date >= '$fromdate'
            AND taxrec.tran_date <= '$todate'
        ORDER BY taxrec.trans_type, taxrec.tran_date, taxrec.trans_no, taxrec.ex_rate";

When there are 2 records in tax_trans (The amount value in one record is not 0 and the other one is 0) which linked to the same record in debtor_trans, this SQL will generate 2 records after joining.

In tax_trans, the record of amount value 0 is filtered out by WHERE parameters, and only 1 tax_tran will be activated to join. But in joining gl_trans, the record of amount 0 is not filtered out so they all will be joined with this 1 tax_tran and get 2 transactions in the report.

Solution:
Just add " AND (gl.amount <> 0)" into the LEFT JOIN of gl_trans. This is what I have done so far. However, I do not really know the side effect as I have never thoroughly get into the codes of FrontAccounting.

I have talked with Janusz and he claims that this bug has already been fixed.
And this is correct. Look into the sql in the committed rep709 and you will see the correction.

Joe