Fun and Games With PHP Precision
Posted on: 12 July 2014
This is another of those "interesting" tech support cases (where "interesting" means painful and tedious).
Our platform collects lots of data for our customers, and generates lots of nice juicy reports for them in an assortment of formats. Being a sensible developer, I'd done this using a typical MVC pattern, so there are models which get data out of the database, and then different views to format it into each report. The support case was that some numbers were being displayed with a ridiculous number of decimal places in Excel reports.
I pretty quickly reproduced the problem on my dev machine, which always makes things much easier, and then discovered that the numbers were formatted perfectly in the web application, in PDF reports, and in PowerPoint reports - it was only the Excel reports which had a problem.
I whipped out my debugger (XDebug in PHPStorm - if you're developing in PHP and don't have a debugger, stop reading NOW and check these out) and traced through the model. This part was pretty simple:
- read number from database - it's stored as part of a serialised array of data
- round the number to two decimal places
- return the model to the controller to present it in the request view
Step 2 was where things got weird.
When I stepped through an HTML request, it rounded the number to two decimal places.
When I stepped through a PDF request, it rounded the number to two decimal places.
When I stepped through a PowerPoint request, it rounded the number to two decimal places.
When I stepped through an Excel request, it rounded the number to twelve decimal places.
WTF? It's the same code, loading the same serialised data from the database, doing the same rounding. How can it give a different result?
After an awful lot of head-scratching, I discovered that the Excel rendering library we use (the otherwise excellent PHPExcel) was changing the PHP "precision" setting from the default of 14 to 16, which then caused the "round" command to behave slightly differently for certain numbers.
Once I knew this, resolving the issue was pretty straightforward, and I then pretty quickly found I wasn't the first to notice. So, be careful of using any third party library for anything. It's probably a good idea to do a quick grep of any third party code for ini_set statements, as these can change behaviour of other parts of your system, and not always in such a cosmetic way as this bug.