rfc:pdo_float_type

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
rfc:pdo_float_type [2017/04/05 15:26] – add link to internals thread adambaratzrfc:pdo_float_type [2019/05/07 15:03] (current) – withdrawn adambaratz
Line 1: Line 1:
 ====== PHP RFC: PDO Float Type ====== ====== PHP RFC: PDO Float Type ======
-  * Version: 0.1+  * Version: 0.5
   * Date: 2017-04-05   * Date: 2017-04-05
   * Author: Adam Baratz adambaratz@php.net   * Author: Adam Baratz adambaratz@php.net
-  * Status: Under Discussion+  * Status: Withdrawn
   * First Published at: http://wiki.php.net/rfc/pdo_float_type   * First Published at: http://wiki.php.net/rfc/pdo_float_type
  
 ===== Introduction ===== ===== Introduction =====
-The PDO extension does not have a type to represent floating point values. The current recommended practice is to use ''PDO::PARAM_STR''.+The PDO extension does not have a type to represent floating point values.
  
-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.+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 result 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.
  
 ===== Proposal ===== ===== Proposal =====
-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.+A new type, ''PDO::PARAM_FLOAT'', will be added. It will indicate that a parameter can be interpreted as and presented to the database server as a floating point value. The name was selected to correspond to the PHP type system. 
 + 
 +As indicated by the comments on [[https://github.com/php/php-src/blob/master/ext/pdo/php_pdo_driver.h#L51|pdo_param_type]], each type should correspond to a C type. PHP floats are represented as doubles and the APIs used by each supported PDO driver represent floating point values as doubles, so ''PDO::PARAM_FLOAT'' will correspond to double. This is mainly relevant for fetching column data, which is otherwise considered out of scope for this RFC (see Future Scope). 
 + 
 +The supported PDO drivers will be modified as little as possible for this type to be supported. In fact, only pdo_sqlite will require modifications. When emulated prepared statements are enabled, these values will be interpolated in decimal form.
  
 ===== Changes To Supported PDO Drivers ===== ===== Changes To Supported PDO Drivers =====
  
 ==== pdo_dblib ==== ==== pdo_dblib ====
- +This driver will require no modifications. It only supports emulated prepared statements. Changes to that shared code will ensure that queries are executed without implicit casts.
-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 [[https://docs.microsoft.com/en-us/sql/t-sql/data-types/constants-transact-sql|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: +
- +
-<code> +
-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) +
-</code> +
- +
-The execution plan for this query was examined to make sure it used the index: +
- +
-<code> +
-select * from numbers where number = 0.1234 +
-</code> +
- +
-This test was repeated using the ''numeric'' type for the number column.+
  
 ==== pdo_firebird ==== ==== pdo_firebird ====
 +This driver will require no modifications. The addition of ''PDO::PARAM_FLOAT'' will prevent floats from being cast to strings before binding them. Other values won't be cast.
  
-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'') checks for double zvals. This code isn't reached because any zval doubles will be cast to other types before they reach this hook (see ''ext/pdo/pdo_stmt.c:really_register_bound_param'').
- +
-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.+
  
 ==== pdo_mysql ==== ==== pdo_mysql ====
 +This driver will require no modifications. The addition of ''PDO::PARAM_FLOAT'' will prevent floats from being cast to strings before binding them. Other values won't be cast.
  
-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. +The ''PDO_PARAM_EVT_EXEC_PRE'' hook (see ''ext/pdo_mysql/mysql_statement.c:pdo_mysql_stmt_param_hook'') will check for double zvals. This code isn't reached because any zval doubles will be cast to other types before they reach this hook (see ''ext/pdo/pdo_stmt.c:really_register_bound_param'').
- +
-By just adding a new constant, this code will behave correctly and unnecessary casts will be avoided: +
- +
-<code> +
-$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'); +
-</code> +
- +
-MySQL has two formats for [[https://dev.mysql.com/doc/refman/5.7/en/number-literals.html|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.+
  
 ==== pdo_oci ==== ==== pdo_oci ====
 +This driver will require no modifications.
  
-The APIs for binding values, [[https://docs.oracle.com/cd/B10501_01/appdev.920/a96584/oci15r29.htm|OCIBindByName]] and [[https://docs.oracle.com/cd/B10501_01/appdev.920/a96584/oci15r30.htm|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. +All values not bound with ''PDO::PARAM_LOB'' are cast to strings (see ''ext/pdo_oci/oct_statement.c:oci_bind_input_cb'').
- +
-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.+
  
 ==== pdo_odbc ==== ==== pdo_odbc ====
 +This driver will require no modifications.
  
-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''). +All values not bound with ''PDO::PARAM_LOB'' are cast to strings (see the ''PDO_PARAM_EVT_EXEC_PRE'' hook in ''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.+
  
 ==== pdo_pgsql ==== ==== pdo_pgsql ====
 +This driver will require no modifications.
  
-The [[https://www.postgresql.org/docs/9.6/static/libpq-exec.html|APIs for executing statements]] describe parameters with a few pieces of data: +All values not bound with ''PDO::PARAM_LOB'' are cast to strings. This is a requirement of the [[https://www.postgresql.org/docs/9.6/static/libpq-exec.html|APIs for executing statements]].
-  * Type: [[https://www.postgresql.org/docs/current/static/datatype-oid.html|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 [[https://www.postgresql.org/docs/9.6/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS-NUMERIC|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.+
  
 ==== pdo_sqlite ==== ==== pdo_sqlite ====
 +This driver will require a small modification.
  
-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 [[https://www.sqlite.org/c3ref/bind_blob.html|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''. +It currently casts values to the type implied by the PDO type so they can be bound with the most appropriate [[https://www.sqlite.org/c3ref/bind_blob.html|API]]. This behavior would be replicated for ''PDO::PARAM_FLOAT''.
- +
-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.+
  
 ===== Backward Incompatible Changes ===== ===== Backward Incompatible Changes =====
 This is entirely new functionality. Existing code will work as is. This is entirely new functionality. Existing code will work as is.
 +
 +===== Future Scope =====
 +
 +==== Driver Modifications (Input) ====
 +pdo_oci and pdo_odbc could be modified to avoid casting values to strings. This could save a little memory churn, but this decision is best made by the maintainers of those drivers. End users shouldn't notice a difference in functionality either way.
 +
 +==== Driver Modifications (Output) ====
 +Some drivers could be modified to return float column data as PHP floats. It's currently possible to do this -- pdo_dblib does -- so it's being considered intentional that others don't.
 +
 +==== Fixed Precision Types ====
 +These types have different storage requirements from floats. They often vary a bit between the APIs used by supported drivers. It would be valuable for PDO to support these types, but this can be handled by a separate RFC. The documentation for ''PDO::PARAM_FLOAT'' should state clearly that it's not intended to be used for fixed precision values. Since these values are currently bound as ''PDO::PARAM_STR'', the introduction of ''PDO::PARAM_FLOAT'' shouldn't harm how they're presented to DBs.
  
 ===== Proposed PHP Version(s) ===== ===== Proposed PHP Version(s) =====
Line 123: Line 72:
 ===== Proposed Voting Choices ===== ===== Proposed Voting Choices =====
 This project requires a 50%+1 majority. This project requires a 50%+1 majority.
 +
 +===== Patches and Tests =====
 +  * PR without tests: [[https://github.com/php/php-src/pull/2500]]
  
 ===== References ===== ===== References =====
   * Earlier discussion on this topic: [[https://externals.io/thread/551]]   * Earlier discussion on this topic: [[https://externals.io/thread/551]]
 +  * Discussion of this RFC: [[https://externals.io/thread/805]]
rfc/pdo_float_type.1491405969.txt.gz · Last modified: 2017/09/22 13:28 (external edit)

Page Tools