FrontAccounting forum — Bug in gl/includes/db/gl_journal.inc

Re: Bug in gl/includes/db/gl_journal.inc

null@example.com (notrinos) — Wed, 11 Aug 2021 16:06:33 +0000

Actually the old query INSERT INTO table1 SELECT col.. FROM table2 is a right syntax.
But better to keep the new query because if the subquery SELECT return null the system will raise an error meanwhile the old query can run successfully even when the SELECT subquery returns null

Re: Bug in gl/includes/db/gl_journal.inc

null@example.com (joe) — Sun, 08 Aug 2021 15:46:24 +0000

Yes, it is amazing that this bug is first detected now after so many minor releases.

Maybe because the entrance is mostly done by the Sales routines.

Joe

Re: Bug in gl/includes/db/gl_journal.inc

null@example.com (barbarian) — Sun, 08 Aug 2021 12:07:36 +0000

Thanks. U have checked this bug has persisted since at least 2.4.2 and it is quite critical because data entry via journal entry feature that relate to account receivable will write the cust_trans using this method and it will fail because the query is wrong.

Re: Bug in gl/includes/db/gl_journal.inc

null@example.com (joe) — Sun, 08 Aug 2021 10:34:21 +0000

This has been fixed and committed to stable repo. Thanks @barbarian for finding this.

A fixed copy can be downloaded and replaced.

/Joe

Re: Bug in gl/includes/db/gl_journal.inc

null@example.com (kvvaradha) — Sun, 08 Aug 2021 07:07:58 +0000

@joe,
Can you update the code and commit it to stable version downloads.

Re: Bug in gl/includes/db/gl_journal.inc

null@example.com (barbarian) — Sat, 07 Aug 2021 23:18:00 +0000

Instead of

$sql = "INSERT INTO ".TB_PREF."debtor_trans (trans_no, type, debtor_no, branch_code, tran_date, reference, ov_amount, rate)
        SELECT "
        .db_escape($trans_no).", ".db_escape($trans_type).", debtor_no,"
        .db_escape($branch_id).",'".date2sql($date)."', ".db_escape($ref).","
        .db_escape($amount).", ".db_escape($rate)."
        FROM ".TB_PREF."cust_branch WHERE branch_code=".db_escape($branch_id);

It should be

$sql = "INSERT INTO ".TB_PREF."debtor_trans (trans_no, type, debtor_no, branch_code, tran_date, reference, ov_amount, rate)
        VALUES ("
        .db_escape($trans_no).", ".db_escape($trans_type).", (SELECT debtor_no FROM ".TB_PREF."cust_branch WHERE branch_code=".db_escape($branch_id). ") ,"
        .db_escape($branch_id).",'".date2sql($date)."', ".db_escape($ref).","
        .db_escape($amount).", ".db_escape($rate).")";

The sub query SELECT debtor_no ... result should bebecome the debtor_no column when inserting.

Re: Bug in gl/includes/db/gl_journal.inc

null@example.com (barbarian) — Sat, 07 Aug 2021 18:08:08 +0000

barbarian wrote:

I found this incorrect sql query:
Screenshot

The SQL Query is wrong. INSERT INTO .... SELECT FROM ...

Re: Bug in gl/includes/db/gl_journal.inc

null@example.com (kvvaradha) — Sat, 07 Aug 2021 14:58:42 +0000

Can you elaborate the issue in text?

Bug in gl/includes/db/gl_journal.inc

null@example.com (barbarian) — Sat, 07 Aug 2021 14:22:01 +0000

I found this incorrect sql query:

Screenshot