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/18 17:00] – voting widget 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.3+  * Version: 0.5
   * Date: 2017-04-05   * Date: 2017-04-05
   * Author: Adam Baratz adambaratz@php.net   * Author: Adam Baratz adambaratz@php.net
-  * Status: Voting+  * 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.
  
-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.+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. +
- +
-The addition of ''PDO::PARAM_FLT'' will ensure numbers are quoted as numbers in queries.+
  
 ==== 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.
  
-This database offers these types: +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'').
-  * ''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.+
  
 ==== 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. +
- +
-The MySQL native driver [[https://dev.mysql.com/doc/apis-php/en/apis-php-mysqlnd.html|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.+
  
 ==== 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. There could be a future optimization to remove this string cast. OCI only has single- and double-precision [[https://docs.oracle.com/cd/B19306_01/appdev.102/b14250/oci03typ.htm||types]].+
  
 ==== 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. There could be a future optimization to remove this string cast. Similarly to the InterBase API, ODBC maps ''DECIMAL'' and ''NUMERIC'' columns to [[https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/sql-to-c-data-conversion-examples||one of several C types]] depending on the specified precision.+
  
 ==== 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. Since the value is always presented as a string, there would be no meaningful different to adding different handling for fixed-precision values.+==== pdo_sqlite ==== 
 +This driver will require a small modification.
  
-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.+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''.
  
-==== pdo_sqlite ====+===== Backward Incompatible Changes ===== 
 +This is entirely new functionality. Existing code will work as is.
  
-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''.+===== Future Scope =====
  
-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.+==== 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.
  
-===== Backward Incompatible Changes ===== +==== Driver Modifications (Output) ==== 
-This is entirely new functionality. Existing code will work as is.+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 137: Line 73:
 This project requires a 50%+1 majority. This project requires a 50%+1 majority.
  
-<doodle title="pdo_float_type" auth="adambaratz" voteType="single" closed="false"> +===== Patches and Tests ===== 
-   * Yes +  * PR without tests: [[https://github.com/php/php-src/pull/2500]]
-   * No +
-</doodle>+
  
 ===== 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]]   * Discussion of this RFC: [[https://externals.io/thread/805]]
rfc/pdo_float_type.1492534803.txt.gz · Last modified: 2017/09/22 13:28 (external edit)

Page Tools