Ucanaccess calculated field bugs

[Robert Robertino]

Hello. So, I got tired of waiting for the jackcess team and I updated to 2.0.7. The first thing I noticed was that read-only is serious business since even opening a simple connection to the database produces write-type operations:
WARNING:The table data is read only in statement [ALTER TABLE CUSTOMERS ADD PRIMARY KEY (ID) ]
Which at some point throws up:
net.ucanaccess.jdbc.UcanaccessSQLException: a UNIQUE constraint does not exist on referenced columns: CUSTOMERS in statement [ALTER TABLE ORDERS ADD CONSTRAINT ORDERS_NEW_CUSTOMERONORDERS FOREIGN KEY ("CUSTOMER ID") REFERENCES CUSTOMERS (ID) ]

However once I deleted the calculated field everything worked properly, with one exception:
WARNING:a UNIQUE constraint already exists on the set of columns in statement [ALTER TABLE "PURCHASE ORDERS" ADD CONSTRAINT PURCHASE_ORDERS_ID UNIQUE ("PURCHASE ORDER ID") ]

I did not previously receive this WARNING with 2.0.6.2. It is true that I've fiddled with the database a bit since then, though. For your testing purposes I'm attaching my database in case you made a small mistake in 2.0.7.
http://speedy.sh/VVFMW/accessDB.accdb (url will expire in ~30 days)

So I scrapped that database for the purpose of this test and I started anew with a single-table database. Here is where trouble starts that was not included in the 2.0.7 changelog.

First thing that I tried was a simple formula: Log(((([ID]+10)^2)5))
ro.jxeelab.core.exception.FailedConnectionException: user lacks privilege or object not found: LOG
Onto square root: Sqr([ID]30)
ro.jxeelab.core.exception.FailedConnectionException: user lacks privilege or object not found: SQR

Logarithmic, square root and most probably other mathematical operators do not work.

Onto operators:
Even the simplest expression ((([ID]+10)^2)*5) does not work. ro.jxeelab.core.exception.FailedConnectionException: unknown token:
The problem is the ^ operator (pow).

I finished my testing with boolean operators. (True is -1, False is 0 in access)
The formula (Not ([ID]>=5)=False) does not work even if the calculated field is of type YES/NO. I do not know exactly if this is related to the jackcess bug or not.

Finally, ([ID]+2*5-3)/4 try this formula. I use getObject() and I only receive rounded DOWN numbers when in fact I should receive 2, 2.25, 2.5 etc..

In conclusion, I cannot recommend 2.0.7 yet. DO NOT UPDATE!

[Marco Amadei]

Hi Robert,
and thank you for this very useful report (but the 2.0.7 has several improvements not related to the sperimental implementation of calculated fields, please don't be so disappointed). Anyway, excellent testing work.
I had better turn off the misleading warning :a UNIQUE constraint already exists on the set of columns in statement. It's the result of bug fix. This bug had effect only with the keepMirror working mode. Ignore it if you can. IT'S NOT a BUG.
The table data is read only in statement [ALTER TABLE CUSTOMERS ADD PRIMARY KEY (ID) is a trivial bug I did because of the sperimental and partial calculated field implementation. It will be fixed in the next few days, in the 2.0.7.1.
About the other issues you mentioned, I think they will be solved too in the 2.0.7.1
BUT
I'm having a bad time and I can't answer now.
I'm going to answer point by point tomorrow or the day after, before implementing and releasing the patches in the 2.0.7.1.

[Robert Robertino]

Hello Marco.
I'm glad you found the information useful. I'll try and test it sooner as soon as you release the next version.

[Marco Amadei]

Hi Robert,
here some more effort is needed.

Math functions like log or sqr aren't really a problem. It's only a lack of formal implementation.

Rounding problem: to ensure that UCanAccess always interpreters a integer litteral (like 1,3 or 4) as double is possible (that's different from hsqldb) and, for the moment, it could be limited to the expressions interpreter(thus to the calculated fields).
(Note from hsqldb documentation:"In SQL Statements, an integer literal is treated as INTEGER, unless its value does not fit. In this case it is treated as BIGINT or DECIMAL, depending on the value").

The primitive operators ^ and mod aren't supported neither in hsqldb nor in java,
yet related math functions are supported.
This is the most challenging task: I have to transform an expression like ([id]+3)^2 into power(([id]+3),2)

Whether or not the boolean issue source is jackcess, I can "easly" solve it by using the return type property. Anyway I think (and hope) that jackcess 2.0.5 will be able to provide more correct type informations.

I'll give notice here about the developments done and/or committed on svn.
If you want to test a 2.0.7.1 preview, you can write an email to amadei.mar@gmail.com
Cheers Marco

[Marco Amadei]

Fixed on svn trunk.