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
Hi Adam,
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/551There 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_typeI 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/
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
Hi Adam,
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/
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/
Hi Andrea,
Matteo Beccati wrote:
a PDO::PARAM_NUMERIC const that could in fact just be an alias for
PDO::PARAM_STR for most of the driversThis 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/
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
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/
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
Hi Adam,
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/
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
Hi Adam,
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/
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
Hi Adam,
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/
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
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
Hi Adam,
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.
Yes, I brought fetching on the table because we should try to be
consistent. If numerics are received as strings, they shouldn't be bound
as PARAM_FLT, unless the user is willing to potentially lose some
information.
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.
Again, there's no limit coming from the C type system: no one is forcing
you to cast fixed precision to floating point. In fact it is the
opposite: they are normally sent as strings and optionally using the
internal (non-float) representation.
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.
If anything, the database libraries will be using their SQL types system
as a reference, specifying what is the C-type you can use to send or
retrieve data. On the other hand you argue that we should bring
low-level decisions to PDO users.
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.
I don't know, but I wouldn't as I've already explained a number of
times. If anything, what I see is that very few care.
Cheers
Matteo Beccati
Development & Consulting - http://www.beccati.com/