Bug #23069 See Comments

Text Input field when set as number is rounding values

Version: 3.5.6 Reporter: JT Thompson

This is an old bug from back in the day, and for all I know it was never squashed because I rarely use anything but ALL for this field type. I know about the 4 decimals issue (EE rounds everything to 4 decimal places - which in my opinion is stupid - make it an option if you want it so bad), however I didn’t know EE rounds WHOLE NUMBERS, which is really - really BAD.


Create a text input field and set it for all defaults and Number for the allowed content. Now in a channel entry using the that field enter in the number ‘23273033’, click on save and you’ll notice EE changes the number to: ‘23273000’.

Currently we are using it to hold the length (in bytes) of an audio media file for a RSS feed, although I can see this being a problem for anything that requires a specific value.

We’ve stop using the Number and Decimal types for text input fields and have just relented to use the ALL type. Of course now there could be issues with client entries (because people make entry mistakes) - and what would have been a simple error check has now become a big pain - we have to do our own error proccessing on the front end.

I hope you can correct this horrible condition because as it stands now the number and decimal types are unusable - ever.

  • I agree these limitations can be unexpected. The cause of the behavior has to do with the technology stack ExpressionEngine relies on, namely MySQL and PHP, not some random or thoughtless rules that our developers decided to employ for those datatypes.

    “Number”: relies on MySQL FLOAT which has its own issues of precision that make it an approximate value, with platform dependencies that mean the behavior can’t be relied on from one environment to the next.

    “Integer”: Uses MySQL INT which has bounds of -2147483648 and 2147483647. BIGINT can store -9223372036854775808 to 9223372036854775807, but then PHP is involved, and must be a 64-bit platform to go that high.

    “Decimal”: Uses MySQL Decimal which is responsible for the rounding. Some limitation has to be specified for this data type, and 1 million seemed like a reasonable number to balance the majority of content needs with storage size in the database. This is most commonly used for currency, hence DECIMAL(10,4) allowing for +/-999,999.9999.

    So that’s the boring technical explanation, that I recognize means absolutely nothing to content authors, and isn’t something they should have to think about. I’ve got some ideas for how to work around all of the aboveā€”but it will require storing the data in a technically incorrect way, and potentially backwards-incompatible, so addressing these will have to come in the next major version rather than a patch fix.

    Derek Jones
    20th April, 2017 at 12:46pm
  • Excellent explanation, however I would strongly disagree with your reasoning for limiting the MySQL “Decimal” function to DECIMAL(10, 4), that seems way too restrictive and arbitrary since you can use upto DECIMAL(65, 30).

    As for the MySQL FLOAT issue, I understand your dilemma and would suggest you use the MySQL Decimal value to store the value (with a much larger DECIMAL(M, D) value) and dump FLOAT entirely, the only other option that I can think of is to do a string type conversion in PHP to preserve the value (MySQL actually stores the value correctly - it is PHP that is doing the rounding).

    I would recommend the DECIMAL option as it would have less headaches in coding.

    Whatever you decide it would be lovely if you’d put this in your documentation as there is nothing in there describing what you just said (https://docs.expressionengine.com/latest/fieldtypes/text.html), and developers/users really should know the limitations of these choices.

    JT Thompson
    20th April, 2017 at 3:33pm
  • that seems way too restrictive and arbitrary since you can use upto DECIMAL(65, 30)

    I can see the case for that, but again it was an attempt to balance the majority of real world use of that numeric format, with the storage space that MySQL will use to store the content.

    All in all I agree with you, the requirements are onerous, don’t make sense, and are not communicated well.

    Derek Jones
    20th April, 2017 at 3:41pm
  • Normally I would let this lie where it sits, because I love agreement, however, because I think this issue is a problem for many EE users and developers I’m going to attempt to push it a bit towards change.

    I understand your reasoning for wanting to keep it a smaller footprint on the Db, and I’ll even except that the limitations where reasonable at the time. I would like to STRONGLY advocate that your reasoning is out of date with the world as it is today, here are some things to consider:

    1. Geo location is huge now - and you can’t handle the location data in ANY of the formats listed.
    2. 1,000,000 isn’t what it used to be - way too low a maximum for many things.
    3. Any type of scientific notation is out the window - and that cuts out a huge market.
    4. Data aggregation functions will break with those limitations - too often to use the field type modifier at all.
    5. Statistical analysis will be almost impossible with the limitations imposed (sale and web stats to name a few).
    6. Forget about aggregating data from multiple locations (like sale data from multiple sites), you’re going to hit the limitations fast.

    There are more, but these should do. At the very least, please PLEASE make the decimal modifier DECIMAL(18,9), we’re not talking about a huge increase in storage space.

    Hope you will think on this carefully - I can’t tell you how many developers hate these limitations. As stated - long ago we just relented to use ‘all’ for everything and did the processing ourselves - but this consumes many hours of programing that shouldn’t be needed.


    JT Thompson
    22nd April, 2017 at 12:48am
  • If I could get the sky I’d use DECIMAL(27,14) - I like to be forward looking.. 😊

    JT Thompson
    22nd April, 2017 at 12:56am
  • My intended solution is better than any that you’ve proposed, but I don’t want to make promises until I’ve built and delivered. 😉

    Derek Jones
    22nd April, 2017 at 1:01am
  • As long as it comes quickly I’m onboard, you do have me curious though now - and to be fair I was doing my best to stay within the current framework, if we can change the framework - well that is a different story entirely. 😉

    I’m all for better number handling, and while we are at it - character limiting on text type field data built in (for output of the data on the frontend, not backend entry stuff) - the number one thing we have to deal with, really hate dealing with that thru plugins - and EE3 plugins that do that are getting really rare now.


    JT Thompson
    22nd April, 2017 at 1:23am
  • Have you checked on GitHub? Most basic string manipulation plugins are open source, with superior versions available for v3.

    Derek Jones
    22nd April, 2017 at 2:19am
  • I usually use devot:ee as a resource for addons - I’ll go check it out.

    Thank You!

    JT Thompson
    22nd April, 2017 at 6:13am

You must be signed in to comment on a bug report.

ExpressionEngine News

#eecms, #events, #releases