Database dependent issue in getBalances (run tests on postgres) about jpos-ee HOT 3 CLOSED

This is a very good catch and a reminder of a very important issue related to native queries, which are fast, but have some constraints related to composite accounts.

The initial ORM-based query knows that a given account is a child of a given parent correctly using the acct.parent reference. The native queries cut some corners in order to take advantage of the database index, it assumes that the parent shares the acct.code prefix.

What happens here is that the test data has a parent account 11 with children 111,112,113 and 120:

https://github.com/jpos/jPOS-EE/blob/dc905a2a/modules/minigl/src/test/resources/testdata.xml#L52-L66

ORM code considers account 120 as a child of 11 when computing the rule, but native code doesn't.

For the time being, I've changed testdata.xml so that account 120 becomes 114, but the native queries need further work to address this issue or at the very least, GLSession.addAccount should raise an exception if the child's code doesn't start with the same as parent.

Regarding your way of testing this, which is good, there's a simpler one; there's no need to create an hibernate.cfg.xml, you can use just the db-postgresql module and eventually tweak the db.properties file to point to your database. Also, there's now a handy gls.forceDialect(GLSession.NativeDialect.ORM); that you can use in Postgresql (or MySQL) to force the use of the ORM getBalances.

I'm keen to close this issue and open a related one as reminder that native composite balance has this issue, that we need to either fix (without bringing down performance) or at least clearly address in the documentation with a super big warning. Will wait for comments, though.

from jpos-ee.

Thanks for looking into this! I see, that would explain it. I was aware of that quirk but didn't think of it this time. Quickly tried out replacing 120 with 114 and that passes for me.

Definitely agree on the big warning about the nested account code behaviour in getBalances, and a check in GLSession.addAccount. Should Import.createFinal should perform this check as well, if we provide a way disable the check for legacy systems that have non-canonical account codes but don't use mysql or postgres?

from jpos-ee.

Fully agree on addAcount and Import.createFinal checks.

from jpos-ee.