I tested this in 5.3.12.1 and there was no problem. The latest snapshot (5.4.3.9-SNAPSHOT) also incorrectly rounds the decimal value to an integer. Here’s a repro:
q1 = QueryNew("id,dec", "integer,decimal");
q1.addRow({id: 1, dec: 5.57});
q2 = QueryNew("id,str", "integer,varchar");
q2.addRow({id: 1, str: "testing"});
q3sql = "
select *
from q1, q2
where q1.id = q2.id
";
q3 = QueryExecute(q3sql, {}, {dbtype: "query"});
// q3.dec should be 5.57. It is 6 instead.
writeDump(q3);
abort;
I’m on Ubuntu 22.04 using Java 11.0.18. Happy to provide additional info if needed.
Hi & welcome! I’m really not sure, but from looking in the docs I understand that creating the DECIMAL SQL type usually also expects further parameters. If not set it will fall back to default with a scale=0. I’m still wondering how/if such parameters (e.g. also varchar(5) or decimal(4,2)) can be passed to the queryNew() function. So if you define DECIMAL without further parameters I think 6 would be what to expect. However, FLOAT works, but according to the docs that would not be as precise as using a DECIMAL with the correct paramaters.
Yes, the real problem is likely not being able to specify the number of decimal places in QueryNew. I’ll see if I can repro with a query that has a fully defined DECIMAL column.
Regardless of whether I can repro with a fully defined DECIMAL column, QofQ did used to return the expected value in 5.3.12.1. So IMO this should still be considered a bug.
hmm, curly one, the problem is that we don’t have the scale information for a decimal column, especially when created with queryNew, so when the column is created in hsqldb due to the join, it’s being created a a double with no precision, hence the rounding
So I guess Lucee 5.3.12.1 used an older version of HSQLDB that somehow managed to retain the precision of the decimal value from the queryNew decimal type column? It would be a shame to lose that magic, however it was done.
I just tested this in 5.4.3.2 using actual database tables with a decimal(12,2) column. Sorry to say it is rounding those values as well. So this is not just restricted to queryNew.
one test is failing, but it’s only returning a number with lots of extra zeros with 5.4, so mathematically it’s now correct, which is way better than the precision loss
I have created a new issue regarding the trailing zeros problem and have merged the fix in for this problem, which as I mentioned, is far worse than the trailing zeros
The fix, using hsqldb’s sql.enforce_strict_size=false option has been added to 5.4.3.10-SNAPSHOT and 6.0.0.542-SNAPSHOT (note the current 6 SNAPSHOTs spit out a lot of console debugging)
I think this should solve this problem as well, please test thoroughly and report back
Newbie question. My servers do not have access to the internet so updates must be applied manually. When updating from minor versions, like 5.4.3.2 to 5.4.4.0, is it still done by adding the snapshot.jar file to the patches folder? Is it the same for a 5.4 release to a 5.5 release? What about from 5.4 to 6.0? Or should major versions be a completely fresh install?
Windows Server with IIS. My original installation was from the 5.4.3.2 windows executable file if that matters.
I actually wanted to update our productive server to 5.4.3.2 next week. However, since our billing system uses a lot of QoQs, in the worst case all billings might have been incorrect. That would be a major disaster.
To be on the safe side, I will now test everything extensively myself before I update to 5.4.3.2.