Using mariadb and the mysql datasource connector my decimal(15,12) column that is all zeros (0.000000000000) only outputs the scientific notation (0E-12). Is there a way to prevent this either on db side or CF side?

I think we've used https://cfdocs.org/precisionevaluate in the past for this scenario

What's the data type of the value coming back from the DB? (ie:

someQuery.someColumn[someRow].getClass().getName()

)? What does its

toString

method give you? Can you also clarify "outputs", you mean (eg):

<cfoutput>#someQuery.someColumn[someRow]#</cfoutput>

?

This is some crap legacy app trying to bring from PHP to CF. The db is mariadb, column type is Decimal(15,12) and your datatype snip gave me back java.math.BigDecimal. And yea output meaning just dumping the query the result shows that 0E-12 value for any item in the DB defaulted as

0.000000000000

numberFormat

is the tool for the job here. Interesting bug in Lucee surfaced by this: https://trycf.com/gist/9c4509396b9bdb5b0b9a076c51161af8/lucee5?theme=monokai note this bit:

Using writeOutput & numberFormat with 0s: 0.000000000001
Using writeOutput & numberFormat with 9s: 0.000000000000

(cf with CF)

Ah yes that does show another bug. But would this be a bug that it cannot output as expected? Or would that be a jdbc bug/issue?

"as expected" is doing a bit of work there. As expected by whom? CFML has no notion of "BigDecimals", and 0.000000000000 is "0" as far as CFML's numeric type works. If you want it formatted, then you need to format it. if you don't format it, yer gonna get default behaviour. Note: I am not seeing scientific notation when I simply output the value: it's just

0

. This is what I'd expect. I'd need to see a (standalone) repro of you getting scientific notation out of a BigDecimal being output to be able to comment further. Also: forget about

<cfdump>

, it's a debugging tool. It might be trying to be "helpful". That's not how you are going to be using that data, so how is it displaying for you in "actual usage"?

Well by "as expected" I meant output exactly how displayed in the database. But even a 0 would be acceptable, but showing me mathematical equivalent seems odd.

Well by "as expected" I meant output exactly how displayed in the database.

See this is why I'm checking your expectations. "displayed in the DB". It's not displaying in the DB. it's stored in the DB. You can't "see it" in the DB. And in the DB it's stored as some sort of sequence of bytes, in binary. You don't want to "see" that, do you? No. You're looking at a DB client, and that's where it's being displayed. These are two separate things. And I'm not being pedantic: this is important to understand. A DB client is one sort of software, and it has its own UI/UX expectations. A CFML application server is acting as a data interchange mechanism. And you are writing the UI for this data interchange. The CFML server has some rules if you ask it to output the binary data it has from the DB, and those rules are arbitrary (and sometimes unhelpful based on your current definition of "helpful", which it can't guess). If you wanna format this data in a specific way in the UI that you are creating... this is on you. --- I'm over-egging that a bit for the sake of clarity, but this is what's going on. And hopefully an explanation of a value "output[ing] exactly how displayed in the database [client]" isn't... quite so cut and dried as you make it seem. If you want values to be in a certain format: format them. That's what the DB client you're looking at is doing.

Well yes true, through a db client as most dev's "see" the data and yes I understand it is stored in the db in bytes etc. So this goes back to basic logic, why on earth would someone what their bytes to be spit out with a mathmatical method between db bytes (stored data) and show me (output); didnt ask for that extra task for the jdbc connector (with the assumption that calculated value is being calculated to show me in that manner).

BigDecimal toPlainString() method instead of toString(). #PrecisionEvaluate(1/1000*0)# -> 0E-20 #PrecisionEvaluate(1/1000*0).toPlainString()# -> 0.00000000000000000000

Thanks for the info @kuro5! Again, brought this up more so if it was a bug but still great to know how to deal with it for any other use cases.

It's not a bug. It's documented behaviour. https://docs.oracle.com/javase/8/docs/api/java/math/BigDecimal.html#toString-- If you ask for that much precision, then when it's converted to a string (which uses

toString

), it followed well-defined rules. Why those specific rules? [shrug], but it will be for a well-informed reason. it is Java, after all. And Java provides the

toPlainString

method as per above, and CFML provides

numberFormat

(as per slightly more above). If you ask for less precision, then at a point, it'll start returning a std decimal number.