PHP RFC: PDO Float Type

• Version: 0.1
• Date: 2017-04-05
• Author: Adam Baratz adambaratz@php.net
• Status: Under Discussion
• 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.

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 API comes with two types, SQL_DOUBLE and SQL_FLOAT, that represent floating point values. They correspond directly to the C types of the same names. Since zvals store floating point values as doubles, PDO should pass all floating point values as SQL_DOUBLE.

The PDO_PARAM_EVT_EXEC_PRE hook (see ext/pdo_firebird/firebird_statement.c:firebird_stmt_param_hook) will 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 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 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.

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.

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.

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.

There could be possible confusion with fixed-precision types (DECIMAL, NUMERIC, etc.). The documentation should state that PDO::PARAM_STR should continue to be used for those types. It would be a future project to add a fixed-precision type to PDO.

Next PHP 7.x.

This project requires a 50%+1 majority.

• Earlier discussion on this topic: https://externals.io/thread/551