From: ahp at byu dot edu
Operating system: Ubuntu 8.04
PHP version: 5.2.6
PHP Bug Type: PDO related
Bug description: PDO prepared statements using SQLite can't bind non-text
values in expressions
Description:
------------
When using PDO with the SQLite backend and prepared SQL statements, it
appears to be impossible to bind anything but a text or NULL value to a
parameter.
The example program prints '3', even though 2 is less than 3. In fact, it
will always print "3" no matter what value is bound to the ':value'
parameter (with the exception of Null).
Although SQLite's type affinity system can automatically convert values
compared directly against a table column that has a type affinity, the
inability to bind non-string-typed values presents a problem with values
used in expressions, comparing against columns (especially computed
columns) in views and unions, and comparisons against columns that do not
have a type affinity.
My specific problem occurred when trying to filter for an ID value against
a key column through a view. The only workaround I found on the web is to
not use bound parameters and prepared statements, but rather construct the
SQL string afresh.
Reproduce code:
---------------
#!/usr/bin/php
<script language="php">
$db=new PDO("sqlite:temp.db");
$q=$db->prepare("SELECT min(3, :value) AS result;");
$q->bindValue(':value', 2, PDO::PARAM_INT);
$q->execute();
$row=$q->fetch();
print $row['result']."\n"; // Always prints '3'
</script>
Expected result:
----------------
The program should print '2' since min(3, 2) is 2.
Actual result:
--------------
The program prints:
3
--
Edit bug report at http://bugs.php.net/?id=45259&edit=1
--
Try a CVS snapshot (PHP 5.2):
http://bugs.php.net/fix.php?id=45259&r=trysnapshot52
Try a CVS snapshot (PHP 5.3):
http://bugs.php.net/fix.php?id=45259&r=trysnapshot53
Try a CVS snapshot (PHP 6.0):
http://bugs.php.net/fix.php?id=45259&r=trysnapshot60
Fixed in CVS: http://bugs.php.net/fix.php?id=45259&r=fixedcvs
Fixed in release:
http://bugs.php.net/fix.php?id=45259&r=alreadyfixed
Need backtrace: http://bugs.php.net/fix.php?id=45259&r=needtrace
Need Reproduce Script: http://bugs.php.net/fix.php?id=45259&r=needscript
Try newer version: http://bugs.php.net/fix.php?id=45259&r=oldversion
Not developer issue: http://bugs.php.net/fix.php?id=45259&r=support
Expected behavior: http://bugs.php.net/fix.php?id=45259&r=notwrong
Not enough info:
http://bugs.php.net/fix.php?id=45259&r=notenoughinfo
Submitted twice:
http://bugs.php.net/fix.php?id=45259&r=submittedtwice
register_globals: http://bugs.php.net/fix.php?id=45259&r=globals
PHP 4 support discontinued: http://bugs.php.net/fix.php?id=45259&r=php4
Daylight Savings: http://bugs.php.net/fix.php?id=45259&r=dst
IIS Stability: http://bugs.php.net/fix.php?id=45259&r=isapi
Install GNU Sed: http://bugs.php.net/fix.php?id=45259&r=gnused
Floating point limitations: http://bugs.php.net/fix.php?id=45259&r=float
No Zend Extensions: http://bugs.php.net/fix.php?id=45259&r=nozend
MySQL Configuration Error: http://bugs.php.net/fix.php?id=45259&r=mysqlcfg
