• From: Indonesia, Japan
  • Registered: 01/08/2014
  • Posts: 190

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

I found this incorrect sql query:

Screenshot

Post's attachments

GLJournalBug.png 64.1 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.
  • From: India, TN, Karur
  • Registered: 05/16/2014
  • Posts: 1,195

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

Can you elaborate the issue in text?

Subscription service based on FA
HRM CRM POS batch Themes

kvvaradha's Website

  • From: Indonesia, Japan
  • Registered: 01/08/2014
  • Posts: 190

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

barbarian wrote:

I found this incorrect sql query:

Screenshot

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

  • From: Indonesia, Japan
  • Registered: 01/08/2014
  • Posts: 190

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

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.

  • From: India, TN, Karur
  • Registered: 05/16/2014
  • Posts: 1,195

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

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

Subscription service based on FA
HRM CRM POS batch Themes

kvvaradha's Website

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

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

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

A fixed copy can be downloaded and replaced.

/Joe

Post's attachments

gl_journal.inc 5.4 kb, 3 downloads since 2021-08-08 

You don't have the permssions to download the attachments of this post.
  • From: Indonesia, Japan
  • Registered: 01/08/2014
  • Posts: 190

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

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.

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

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

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

  • From: Vietnam
  • Registered: 09/28/2016
  • Posts: 349

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

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

Phuong

notrinos's Website