Welcome! Log In Create A New Profile

Advanced

[PHP-DEV] [RFC] PDO Float Type

Posted by Adam Baratz 
Adam Baratz
[PHP-DEV] [RFC] PDO Float Type
April 05, 2017 05:40PM
Hi,

The PDO extension does not have a type to represent floating point values.
The current recommended practice is to use PDO::PARAM_STR.

I had poked at this topic in an earlier thread:
https://externals.io/thread/551

There was some hesitation about how complicated this would be to implement.
After looking through each of the supported drivers, it seems like it would
actually be a fairly light lift. In some cases, switching PDO::PARAM_STR
for a new float type constant will save a type cast and have the same
results.

I wrote up a proposal here:
https://wiki.php.net/rfc/pdo_float_type

I tried to be as thorough as possible in understanding the impact on each
supported driver. I'd appreciate any feedback on this concept as well as
its impact on drivers.

Thanks,
Adam
Matteo Beccati
Re: [PHP-DEV] [RFC] PDO Float Type
April 10, 2017 09:10AM
Hi Adam,

On 05/04/2017 17:30, Adam Baratz wrote:
> Hi,
>
> The PDO extension does not have a type to represent floating point values.
> The current recommended practice is to use PDO::PARAM_STR.
>
> I had poked at this topic in an earlier thread:
> https://externals.io/thread/551
>
> There was some hesitation about how complicated this would be to implement.
> After looking through each of the supported drivers, it seems like it would
> actually be a fairly light lift. In some cases, switching PDO::PARAM_STR
> for a new float type constant will save a type cast and have the same
> results.
>
> I wrote up a proposal here:
> https://wiki.php.net/rfc/pdo_float_type
>
> I tried to be as thorough as possible in understanding the impact on each
> supported driver. I'd appreciate any feedback on this concept as well as
> its impact on drivers.

Thanks for that. I generally have very little use for float types on a
database, but I guess their support should have been included from day 1
in PDO.

That said, I think the proposed type is likely to be misused for
NUMERIC/DECIMAL fields, which would be pretty bad. Maybe we should also
add PDO::PARAM_NUMERIC in order to avoid mistakes?


Cheers
--
Matteo Beccati

Development & Consulting - http://www.beccati.com/

--
PHP Internals - PHP Runtime Development Mailing List
To unsubscribe, visit: http://www.php.net/unsub.php
Adam Baratz
Re: [PHP-DEV] [RFC] PDO Float Type
April 10, 2017 05:50PM
>
> That said, I think the proposed type is likely to be misused for
> NUMERIC/DECIMAL fields, which would be pretty bad. Maybe we should also
> add PDO::PARAM_NUMERIC in order to avoid mistakes?
>

Just so I understand your concern, it's that fixed-precision types are
meaningfully different and there could be clashes with other types of
floats? I agree with you on that, but I'm not sure what the right solution
is. There isn't a C type for fixed-precision floats, or even a PHP type.
How would the flow of data work so nothing's lost/altered along the way? My
general thought would be that if fixed-precision matters, then you should
be storing values as strings, that there'd be a whole other set of pitfalls
opened up with a PDO::PARAM_NUMERIC type.

I realize this is all downsides, which isn't the most constructive way to
respond. Happy to talk through details if you have specific suggestions for
how this type would work in practice.

Thanks,
Adam
Matteo Beccati
Re: [PHP-DEV] [RFC] PDO Float Type
April 10, 2017 06:10PM
Hi Adam,

On 10/04/2017 17:42, Adam Baratz wrote:
> Just so I understand your concern, it's that fixed-precision types are
> meaningfully different and there could be clashes with other types of
> floats? I agree with you on that, but I'm not sure what the right
> solution is. There isn't a C type for fixed-precision floats, or even a
> PHP type. How would the flow of data work so nothing's lost/altered
> along the way? My general thought would be that if fixed-precision
> matters, then you should be storing values as strings, that there'd be a
> whole other set of pitfalls opened up with a PDO::PARAM_NUMERIC type.
>
> I realize this is all downsides, which isn't the most constructive way
> to respond. Happy to talk through details if you have specific
> suggestions for how this type would work in practice.

My concern is that numeric/decimal types should be treated/sent as
strings (and eventually dealt with using e.g. bcmath) and not converted
to floats, unless one seeks trouble and loves rounding errors, while the
new PDO::PARAM_FLOAT const could instead be seen as a good fit to many
unexperienced (and some experienced) people.

Hence my suggestion of a PDO::PARAM_NUMERIC const that could in fact
just be an alias for PDO::PARAM_STR for most of the drivers.

The most important thing to me is to discourage the misuse of
PDO::PARAM_FLOAT for types that are not actually floating points.


Cheers
--
Matteo Beccati

Development & Consulting - http://www.beccati.com/

--
PHP Internals - PHP Runtime Development Mailing List
To unsubscribe, visit: http://www.php.net/unsub.php
Andrea Faulds
Re: [PHP-DEV] [RFC] PDO Float Type
April 11, 2017 07:30PM
Hi,

Matteo Beccati wrote:
> a PDO::PARAM_NUMERIC const that could in fact
> just be an alias for PDO::PARAM_STR for most of the drivers

This is a technical solution to a documentation problem. I think it runs
the risk of confusing people who do know what they're doing, and assume
NUMERIC is like FLOAT.

I'd suggest adding a warning to the manual instead.

--
Andrea Faulds
https://ajf.me/

--
PHP Internals - PHP Runtime Development Mailing List
To unsubscribe, visit: http://www.php.net/unsub.php
Matteo Beccati
Re: [PHP-DEV] [RFC] PDO Float Type
April 12, 2017 01:00AM
Hi Andrea,

On 11/04/2017 19:25, Andrea Faulds wrote:
> Matteo Beccati wrote:
>> a PDO::PARAM_NUMERIC const that could in fact just be an alias for
>> PDO::PARAM_STR for most of the drivers
>
> This is a technical solution to a documentation problem.

Yes, and I'd tend to agree. But technically PDO::PARAM_NUMERIC could
also allow to pass parameters in an appropriate format: I believe it
wouldn't be much of an improvement for pdo_pgsql, but to be certain some
level of investigation would be required.

> I think it runs the risk of confusing people who do know what they're
> doing, and assume NUMERIC is like FLOAT.

Hey, that was precisely my point! ;)

I.e. to assume that numeric and float are similar enough that
PDO::PARAM_FLOAT would be a good fit for a numeric field.

I know you meant it the other way around, but to me it is more confusing
to use PARAM_STR for numerics when only PARAM_FLOAT is available as
alternative vs using PARAM_FLOAT on a numeric when both are available.


> I'd suggest adding a warning to the manual instead.

I think that's a requirement in any case.


Cheers
--
Matteo Beccati

Development & Consulting - http://www.beccati.com/

--
PHP Internals - PHP Runtime Development Mailing List
To unsubscribe, visit: http://www.php.net/unsub.php
Adam Baratz
Re: [PHP-DEV] [RFC] PDO Float Type
April 12, 2017 05:30PM
>
> > I'd suggest adding a warning to the manual instead.
>
> I think that's a requirement in any case.


I'd be most comfortable with this approach. I'd worry about adding a
PDO::PARAM_NUMERIC type without investigating how it needs to function for
each DB, which feels like scope creep. If it starts off as an alias for
PDO::PARAM_STR, there could be issues updating it to work correctly,
especially if the right design involves modeling the precision somewhere. I
added a "Future Scope" section covering this.

Let me know if there are major problems with this or other points to cover.
Otherwise, I'll aim to open voting on Monday.

Thanks,
Adam
Matteo Beccati
Re: [PHP-DEV] [RFC] PDO Float Type
April 12, 2017 06:00PM
On 12/04/2017 17:21, Adam Baratz wrote:
>>
>>> I'd suggest adding a warning to the manual instead.
>>
>> I think that's a requirement in any case.
>
>
> I'd be most comfortable with this approach. I'd worry about adding a
> PDO::PARAM_NUMERIC type without investigating how it needs to function for
> each DB, which feels like scope creep. If it starts off as an alias for
> PDO::PARAM_STR, there could be issues updating it to work correctly,
> especially if the right design involves modeling the precision somewhere. I
> added a "Future Scope" section covering this.
>
> Let me know if there are major problems with this or other points to cover.
> Otherwise, I'll aim to open voting on Monday.

Let's just agree to disagree. I believe they should be investigated and
proposed in a single RFC. Having just one and relying on an obscure
documentation warning is not enough IMHO.

Even your RFC claims that it should be used for numeric types :(

"This test was repeated using the numeric type for the number column."


Cheers
--
Matteo Beccati

Development & Consulting - http://www.beccati.com/

--
PHP Internals - PHP Runtime Development Mailing List
To unsubscribe, visit: http://www.php.net/unsub.php
Adam Baratz
Re: [PHP-DEV] [RFC] PDO Float Type
April 18, 2017 07:00PM
>
> > I'd be most comfortable with this approach. I'd worry about adding a
> > PDO::PARAM_NUMERIC type without investigating how it needs to function
> for
> > each DB, which feels like scope creep. If it starts off as an alias for
> > PDO::PARAM_STR, there could be issues updating it to work correctly,
> > especially if the right design involves modeling the precision
> somewhere. I
> > added a "Future Scope" section covering this.
> >
> > Let me know if there are major problems with this or other points to
> cover.
> > Otherwise, I'll aim to open voting on Monday.
>
> Let's just agree to disagree. I believe they should be investigated and
> proposed in a single RFC. Having just one and relying on an obscure
> documentation warning is not enough IMHO.


I looked more closely at each of the APIs. My conclusion was that a single
type will be appropriate for floats, doubles, and fixed-precision. I
updated the RFC with details. If it's accepted, it could be worth including
some of this content in the documentation so people better understand the
impact of each PDO param type.

I'll create a separate thread to announce the vote.

Thanks,
Adam
Matteo Beccati
Re: [PHP-DEV] [RFC] PDO Float Type
April 19, 2017 10:20AM
Hi Adam,

On 18/04/2017 18:58, Adam Baratz wrote:
> I looked more closely at each of the APIs. My conclusion was that a single
> type will be appropriate for floats, doubles, and fixed-precision. I
> updated the RFC with details. If it's accepted, it could be worth including
> some of this content in the documentation so people better understand the
> impact of each PDO param type.
>
> I'll create a separate thread to announce the vote.

Thanks for allowing everyone the time to look at the RFC changes before
opening the vote ;)

Anyway, your suggestion that a single floating point type is appropriate
for both fixed-precision and floating points seems ill-advised, as is
probably the API you are basing your decisions off of.


Cheers
--
Matteo Beccati

Development & Consulting - http://www.beccati.com/

--
PHP Internals - PHP Runtime Development Mailing List
To unsubscribe, visit: http://www.php.net/unsub.php
Adam Baratz
Re: [PHP-DEV] [RFC] PDO Float Type
April 19, 2017 06:20PM
Hi,

Thanks for allowing everyone the time to look at the RFC changes before
> opening the vote ;)
>

I didn't think I needed to extend discussion for the changes I made. The
substance of the proposal hasn't changed. I just responded to your
criticism that there'd need to be a separate type for fixed-precision
values.

Anyway, your suggestion that a single floating point type is appropriate
> for both fixed-precision and floating points seems ill-advised, as is
> probably the API you are basing your decisions off of.


I am trying to be as thorough as possible in addressing your concerns.
That's why, after your last response, I delayed the vote and took the time
to work out how each API differentiates between floating point and
fixed-precision values. I'm sorry if it seems like I'm trying to force this
through. I'm just trying to avoid spinning tires when it seems like we're
likely to "agree to disagree."

That said, I'm not sure how to respond to you when your feedback is very
brief. Simply calling something "ill-advised" doesn't give me a lot to go
on, especially when I feel like I provided a pretty rich level of detail in
the RFC.

I'd like to get a better handle on how we should discuss RFCs. We seem to
hit impasses pretty frequently. If you have any specifics on what's helpful
for you, I'm happy to adjust my style.

Thanks,
Adam
Matteo Beccati
Re: [PHP-DEV] [RFC] PDO Float Type
April 19, 2017 08:40PM
Hi Adam,

On 19/04/2017 18:08, Adam Baratz wrote:
> I am trying to be as thorough as possible in addressing your concerns.
> That's why, after your last response, I delayed the vote and took the
> time to work out how each API differentiates between floating point and
> fixed-precision values. I'm sorry if it seems like I'm trying to force
> this through. I'm just trying to avoid spinning tires when it seems like
> we're likely to "agree to disagree."
>
> That said, I'm not sure how to respond to you when your feedback is very
> brief. Simply calling something "ill-advised" doesn't give me a lot to
> go on, especially when I feel like I provided a pretty rich level of
> detail in the RFC.

I apologise if I've been harsh, but I am truly disappointed. I tried to
sway the RFC in a certain direction, that is conveying the pretty basic
notion that using floating points for fixed precision numbers is wrong:
there's lots of literature on that and especially why floats shouldn't
be used to represent money. The result was instead "floats all the
things!", which is indeed the opposite of what I was expecting.

> I'd like to get a better handle on how we should discuss RFCs. We seem
> to hit impasses pretty frequently. If you have any specifics on what's
> helpful for you, I'm happy to adjust my style.

I have no specific suggestion. I'm just worried about the potential
disruptions of features that benefit a tiny portion of our users.

While I'm at it, could you please clarify the following for me: Why is
there so much effort devoted to the legacy dblib driver when pdo_sqlsrv
seems a much better replacement on paper? Googling for info didn't help,
so there surely must be something I'm missing here.


Cheers
--
Matteo Beccati

Development & Consulting - http://www.beccati.com/

--
PHP Internals - PHP Runtime Development Mailing List
To unsubscribe, visit: http://www.php.net/unsub.php
Adam Baratz
Re: [PHP-DEV] [RFC] PDO Float Type
April 20, 2017 01:00AM
>
> I apologise if I've been harsh, but I am truly disappointed. I tried to
> sway the RFC in a certain direction, that is conveying the pretty basic
> notion that using floating points for fixed precision numbers is wrong:
> there's lots of literature on that and especially why floats shouldn't
> be used to represent money. The result was instead "floats all the
> things!", which is indeed the opposite of what I was expecting.
>

The reason I went that way was I couldn't find a DB API that differentiates
between the two types. They all represent them as a double, so it seemed
like a needless distinction to create two PDO types, especially when PHP
floats are the only built-in type for these numbers. Creating a dependency
between bcmath and pdo (or something similar) feels awkward because it
would offer false guarantees about what gets communicated to the DB server.

In retrospect, I should've waited to open the vote to make sure everyone
was on the same page about this conclusion. I assumed my explanation was
clearer than it was.

I have no specific suggestion. I'm just worried about the potential
> disruptions of features that benefit a tiny portion of our users.
>

That's fair. I do realize ensuring "significant traction" for new features
is emphasized in the RFC template. That said, small improvements are a nice
way of signaling "active development," especially when very little has
changed in PDO in a long while.

The top Google results for "pdo float param" are three StackOverflow
questions (first from 2009) asking why there isn't a float type, the
PDOStatement::bindValue() documentation, and a feature request on
bugs.php.net (from 2007). Whether or not this RFC is the right approach, I
feel like there has been demand for this. It's a negative signal about the
usefulness of PDO to have feature requests left open for 10 years. If
they're outside the scope of what PDO should do, then let's update the
documentation or whatever to be clearer about that.

While I'm at it, could you please clarify the following for me: Why is
> there so much effort devoted to the legacy dblib driver when pdo_sqlsrv
> seems a much better replacement on paper? Googling for info didn't help,
> so there surely must be something I'm missing here.


Speaking only for myself: legacy reasons. My company uses MSSQL, we have
hundreds of software engineers and a lot of systems that have been attached
to pdo_dblib for a while. We want to get off it in the not-too-distant
future, but for now I'm helping support it.

As far as pdo_sqlsrv goes, it's still a "preview." The Linux ODBC driver it
depends on only became supported in January of this year. Eventually, it
could make sense to deprecate pdo_dblib in favor of something supported by
Microsoft, but the timeline for that is unclear.

Thanks,
Adam
Matteo Beccati
Re: [PHP-DEV] [RFC] PDO Float Type
April 21, 2017 01:10PM
Hi Adam,

On 20/04/2017 00:51, Adam Baratz wrote:
> The reason I went that way was I couldn't find a DB API that
> differentiates between the two types. They all represent them as a
> double, so it seemed like a needless distinction to create two PDO
> types, especially when PHP floats are the only built-in type for these
> numbers. Creating a dependency between bcmath and pdo (or something
> similar) feels awkward because it would offer false guarantees about
> what gets communicated to the DB server.

Sorry, your research wasn't probably as accurate as you think. Certainly
libpq doesn't do that and, from what I gather, mysql doesn't either,
according to what I read in:

https://schlueters.de/blog/archives/182-Types-in-PHP-and-MySQL.html
(search for DECIMAL, applies to mysqli, but the concept is the same)

If some database APIs aren't capable of properly handling fixed
precision, it is their fault (dblib?, firebird?), but PDO shouldn't
behave like the less capable ones.

Also your assertion that "PHP floats are the only built-in type for
these numbers" is not entirely true. I'm perfectly happy to get my fixed
precision numbers as strings and print them as-is or use bcmath or
whatever other means to do some calculations. If I want to, sure I can
cast them to float, but I don't want PDO to start making that choice for me.


> The top Google results for "pdo float param" are three StackOverflow
> questions (first from 2009) asking why there isn't a float type, the
> PDOStatement::bindValue() documentation, and a feature request on
> bugs.php.net http://bugs.php.net (from 2007). Whether or not this RFC
> is the right approach, I feel like there has been demand for this. It's
> a negative signal about the usefulness of PDO to have feature requests
> left open for 10 years. If they're outside the scope of what PDO should
> do, then let's update the documentation or whatever to be clearer about
> that.

Sure, that's true, although they're not the most popular topics on
StaskOverflow. If we do something about it, I still think we should
strive for more clarity, whereas to me suggesting PARAM_STR for numerics
(instead of PARAM_FLT) is as confusing as having no PARAM_FLT at all.

That's my personal opinion of course. What is a fact though, is that the
RFC isn't accurate and presents PARAM_FLT as the perfect solution for
numerics too, which is plain wrong.


> As far as pdo_sqlsrv goes, it's still a "preview." The Linux ODBC driver
> it depends on only became supported in January of this year. Eventually,
> it could make sense to deprecate pdo_dblib in favor of something
> supported by Microsoft, but the timeline for that is unclear.

Cool, thanks for the clarification.


Cheers
--
Matteo Beccati

Development & Consulting - http://www.beccati.com/

--
PHP Internals - PHP Runtime Development Mailing List
To unsubscribe, visit: http://www.php.net/unsub.php
Lester Caine
Re: [PHP-DEV] [RFC] PDO Float Type
April 21, 2017 06:50PM
On 21/04/17 12:08, Matteo Beccati wrote:
> If some database APIs aren't capable of properly handling fixed
> precision, it is their fault (dblib?, firebird?), but PDO shouldn't
> behave like the less capable ones.

The whole point of PDO was that it would provide a consistent DATA
interface to any engine and that the base should be what can be provided
transparently across all drivers. It should certainly not mess things up
by providing a floating point value where a fixed precision number is
being used. The problem is that PHP can't handle the data that databases
do actually produce and none of the type systems being bodged in
currently can handle fixed precision numbers, so leave these as strings
and allow third party add-ons to properly process this data.

--
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

--
PHP Internals - PHP Runtime Development Mailing List
To unsubscribe, visit: http://www.php.net/unsub.php
Adam Baratz
Re: [PHP-DEV] [RFC] PDO Float Type
April 24, 2017 07:50PM
>
> On 20/04/2017 00:51, Adam Baratz wrote:
> > The reason I went that way was I couldn't find a DB API that
> > differentiates between the two types. They all represent them as a
> > double, so it seemed like a needless distinction to create two PDO
> > types, especially when PHP floats are the only built-in type for these
> > numbers. Creating a dependency between bcmath and pdo (or something
> > similar) feels awkward because it would offer false guarantees about
> > what gets communicated to the DB server.
>
> Sorry, your research wasn't probably as accurate as you think. Certainly
> libpq doesn't do that and, from what I gather, mysql doesn't either,
> according to what I read in:
>

I think we're talking about different things. I'm talking about binding
parameters. It seems like you're talking about fetching column data. You
are correct that fixed-precision values are typically fetched as strings.
One thing to note is that this can be a choice made by the PDO driver
extension. With libpq, PQgetvalue[1] returns all column data as strings. If
you look at pgsql_stmt_get_col, you'll see extra logic to cast to a
zend_bool or zend_long. Other drivers, like pdo_odbc, fetch everything as a
string.

If some database APIs aren't capable of properly handling fixed
> precision, it is their fault (dblib?, firebird?), but PDO shouldn't
> behave like the less capable ones.
>

I wrote in the RFC how each API works. They all lack this ability, because
they're all limited by the C type system. This is why I was arguing to
merge floats and fixed-precision types. It's a difference that doesn't
exist when sending data to the database. I'll admit I wasn't paying a lot
of attention to retrieving data. There's a lot of variability between PDO
drivers and I didn't want to complicate the scope of the RFC.

If I can summarize our underlying arguments, mine is that PDO parameters
should correspond to zval types and C types, yours is that PDO parameters
should correspond to database column types. A lot of PDO internals tend to
assume the former, even if the latter is the conventional wisdom.

Honestly, I'm not very attached to having a single type for floats and
fixed-precision. If this RFC doesn't pass, which it doesn't look like it
will, I would be just as happy to cut fixed-precision values out of it.
Though I don't have a good handle on how many of the people who voted "no"
would change their minds if fixed-precision was simply left out of scope.

Thanks,
Adam

---
[1] https://www.postgresql.org/docs/9.6/static/libpq-exec.ht
ml#LIBPQ-PQGETVALUE
Sorry, only registered users may post in this forum.

Click here to login