PHP RFC: PDO Float Type

• Version: 0.3
• Date: 2017-04-05
• Author: Adam Baratz adambaratz@php.net
• Status: Voting
• First Published at: http://wiki.php.net/rfc/pdo_float_type

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

Queries formed this way produce the correct results, but with some inefficiencies. It can force unnecessary string casts on zvals, which results in extra memory allocations. If emulated prepared statements are enabled -- the default for pdo_mysql, the only option for pdo_dblib -- values will be quoted incorrectly, which results in implicit casts.

A new type, PDO::PARAM_FLT, will be added. It will indicate that a parameter should be interpreted as and presented to the database server as a floating point value. This name was selected to correspond to the PHP type system. PDO is meant to be a generic layer, so it shouldn't hew closely to any one database. The implication is that these values will be represented internally using the double C type. When emulated prepared statements are enabled, these values would be interpolated in decimal form. Beyond that, implementation details will vary with each PDO driver. Several will require no modifications to start benefiting from the new type.

Due to the mechanics of each supported driver, this type should also be used for fixed-precision values. Since all APIs involved use C types, double is the most precise option available. The following section goes into more detail on why this is appropriate for each driver.

This driver can only use emulated prepared statements. That means it's only necessary to make sure these values are quoted correctly. There are two constant formats for these values: decimal form and scientific notation. While the documentation maps them to different types -- decimal form for decimal, scientific notation for float/real -- they can be used interchangably without forcing implicit casts. This was validated using the following test table:

create table numbers (
  id int not null identity(1,1),
  number float not null
)

declare @count int = 0
while @count < 10000
begin
  insert into numbers (number) values (rand())
  select @count = @count + 1
end

create index idx_number on numbers(number)

The execution plan for this query was examined to make sure it used the index:

select * from numbers where number = 0.1234

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

The addition of PDO::PARAM_FLT will ensure numbers are quoted as numbers in queries.

This database offers these types:

• DOUBLE PRECISION. These should be passed using the double C type.
• FLOAT. These should be passed using the float C type.
• DECIMAL and NUMERIC. Depending on the specified precision, they are stored internally as SMALLINT (int), INTEGER (long), DOUBLE PRECISION (double), or 64-bit integer (ISC_INT64) types.

It would work correctly enough of the time to use PDO::PARAM_FLT with each of these. That type would ensure pdo_firebird passes a double value to the InterBase API. Since zvals store floating point values as doubles, any type coercion performed by the API would result in the same loss of precision involved in converting a double to a float anywhere else.

There is already code in the PDO_PARAM_EVT_EXEC_PRE hook (see ext/pdo_firebird/firebird_statement.c:firebird_stmt_param_hook) to check for double zvals. They will be prepared as a SQL_DOUBLE. However, this code won't be reached because any zval doubles will be cast to other types before they reach this hook.

The addition of PDO::PARAM_FLT will prevent numbers from being unnecessarily cast to strings before binding them as parameters.

The PDO_PARAM_EVT_EXEC_PRE hook (see ext/pdo_mysql/mysql_statement.c:pdo_mysql_stmt_param_hook) will check for double zvals. They will be prepared as a MYSQL_TYPE_DOUBLE. However, this code won't be reached because any zval doubles will be cast to other types before they reach this hook.

By just adding a new constant, this code will behave correctly and unnecessary casts will be avoided:

$db = new PDO($dsn, $user, $pass);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);

$db->query('DROP TABLE IF EXISTS test');
$db->query('create table `test`( `value` float )');

$stmt = $db->prepare('INSERT INTO test (value) VALUES (:value)');

$stmt->bindValue(':value', 0.12);
$stmt->execute();

$stmt->bindValue(':value', '0.34');
$stmt->execute();

$stmt->bindValue(':value', 0.56, PDO::PARAM_FLT);
$stmt->execute();

$stmt->bindValue(':value', '0.78', PDO::PARAM_FLT);
$stmt->execute();

$stmt = $db->query('SELECT * FROM test');
var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));

$db->query('DROP TABLE IF EXISTS test');

MySQL has two formats for number literals: decimal form and scientific notation. A test similar to the one used with MSSQL showed that an index will be hit for decimal and float columns using decimal form values. Meaning, there would be no impact to query performance with emulated prepared statements.

The MySQL native driver documentation doesn't go into detail on its API. It appears that the types used to bind values should correspond to the value you pass. In this case, MYSQL_TYPE_DOUBLE is appropriate for double zvals. While there's a MYSQL_TYPE_DECIMAL type, it's not clear what C type it maps to. A misuse of PDO::PARAM_FLT for a fixed-precision column may result in type coercion by the database, but it will avoid a potentially unnecessary string cast in PDO.

The APIs for binding values, OCIBindByName and OCIBindByPos, take a dty parameter to describe the data type. The existing implementation treats any parameter that doesn't use PDO::PARAM_LOB as a string.

A PDO::PARAM_FLT type could ensure that a value was formatted like a double. The rest of the existing code would handle everything correctly. There could be a future optimization to remove this string cast. OCI only has single- and double-precision |types.

All parameters that don't use PDO::PARAM_LOB are converted to strings. This is handled by the PDO_PARAM_EVT_EXEC_PRE hook (see ext/pdo_odbc/odbc_stmt.c:odbc_stmt_param_hook).

A PDO::PARAM_FLT type could ensure that a value was formatted like a double. The rest of the existing code would handle everything correctly. There could be a future optimization to remove this string cast. Similarly to the InterBase API, ODBC maps DECIMAL and NUMERIC columns to |one of several C types depending on the specified precision.

The APIs for executing statements describe parameters with a few pieces of data:

• Type: OIDs are used here. If unspecified, the DB server will make a best guess. pdo_pgsql never specifies types.
• Format: Whether parameters are text or binary. pdo_pgsql specifies binary for PDO::PARAM_LOB, text for everything else.
• Value: A null-terminated text string (for text format) or binary data in the format expected by the server (for binary format).
• Length: The actual data lengths of binary-format parameters.

A PDO::PARAM_FLT type could ensure that a value was formatted like a double. The rest of the existing code would handle everything correctly. Since the value is always presented as a string, there would be no meaningful different to adding different handling for fixed-precision values.

PostgreSQL has two formats for numeric constants: decimal form and scientific notation. A test similar to the one above showed that an index will be hit for double and numeric columns using decimal form values. Meaning, there would be no impact to query performance with emulated prepared statements.

SQLite 3 has type “affinities” rather than fixed types (see https://www.sqlite.org/datatype3.htmldocumentation on data types). The REAL type is stored as an 8-byte IEEE floating point number. When binding values, a double is the only floating point type that can be bound to column. There are no existing references to sqlite3_bind_double.

A single PDO::PARAM_FLT type would fully represent floating point types for SQLite and prevent unnecessary casts. Parameter binding would be updated to use sqlite3_bind_double with those values.

This is entirely new functionality. Existing code will work as is.

Next PHP 7.x.

This project requires a 50%+1 majority. Voting opened on 18 April 2017. It will close on 26 April at 0:00 UTC.

pdo_float_type
Real name	Yes	No
adambaratz (adambaratz)
ashnazg (ashnazg)
emir (emir)
galvao (galvao)
hywan (hywan)
mariano (mariano)
mbeccati (mbeccati)
nikic (nikic)
ocramius (ocramius)
sebastian (sebastian)
Count:	4	6

• Earlier discussion on this topic: https://externals.io/thread/551
• Discussion of this RFC: https://externals.io/thread/805