#45259 [NEW]: PDO prepared statements using SQLite can't bind non-text values in expressions

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