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
Next revisionBoth sides next revision
rfc:pdo_float_type [2017/04/05 15:26] – add link to internals thread adambaratzrfc:pdo_float_type [2017/04/26 16:13] – declined adambaratz
Line 1: Line 1:
 ====== PHP RFC: PDO Float Type ====== ====== PHP RFC: PDO Float Type ======
-  * Version: 0.1+  * Version: 0.3
   * Date: 2017-04-05   * Date: 2017-04-05
   * Author: Adam Baratz adambaratz@php.net   * Author: Adam Baratz adambaratz@php.net
-  * Status: Under Discussion+  * Status: Declined
   * First Published at: http://wiki.php.net/rfc/pdo_float_type   * First Published at: http://wiki.php.net/rfc/pdo_float_type
  
Line 13: Line 13:
 ===== 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_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.
  
 ===== Changes To Supported PDO Drivers ===== ===== Changes To Supported PDO Drivers =====
Line 43: Line 45:
  
 This test was repeated using the ''numeric'' type for the number column. 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 ====
  
-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''.+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 ''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 addition of ''PDO::PARAM_FLT'' will prevent numbers from being unnecessarily cast to strings before binding them as parameters.
  
 ==== pdo_mysql ==== ==== pdo_mysql ====
Line 84: Line 95:
  
 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. 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 ====
Line 89: Line 102:
 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. 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.
  
-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.+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 ====
Line 95: Line 108:
 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 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.+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 ====
Line 105: Line 118:
   * Length: The actual data lengths of binary-format parameters.   * 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.+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 [[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. 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.
Line 122: Line 135:
  
 ===== Proposed Voting Choices ===== ===== Proposed Voting Choices =====
-This project requires a 50%+1 majority.+This project requires a 50%+1 majority. Voting opened on 18 April 2017. It will close on 26 April at 0:00 UTC. 
 + 
 +<doodle title="pdo_float_type" auth="adambaratz" voteType="single" closed="true"> 
 +   * Yes 
 +   * 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]]
rfc/pdo_float_type.txt · Last modified: 2019/05/07 15:03 by adambaratz

Page Tools