QofQ rounds decimal type to integer

I tested this in and there was no problem. The latest 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.

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.

I’ve looked also, but didn’t find anything that works with newQuery()

Regardless of whether I can repro with a fully defined DECIMAL column, QofQ did used to return the expected value in 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


changing the decimal to a double, it works

classic problem of cfml using dynamic typing and sql being more statically typed

So I guess Lucee 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.

yeah, there are several ways we can solve this, just need to figure out which one works best

pretty sure columns coming back form a db, include this info, but i need to investigate

internally all numbers in lucee are java doubles

anyway, please file a bug in jira

Will do.

I just tested this in 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.


1 Like

good news, i think i have a solution, will publish a snapshot soon, see task


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


Thanks for the quick response to this!

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

LDEV-4693 Queries returning numbers with trailing 0s

The fix, using hsqldb’s sql.enforce_strict_size=false option has been added to and (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

2 Likes resolves the trailing 0 problem [LDEV-4693] - Lucee

Newbie question. My servers do not have access to the internet so updates must be applied manually. When updating from minor versions, like to, 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 windows executable file if that matters.

Drop the Lucee .lco in the Lucee-server deploy folder

1 Like

Nice and easy. LOVE it!!! Adobe updates were always so painful.

3 Likes QofQ rounds decimal type to integer

Thank you very much for the useful tip!

I actually wanted to update our productive server to 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