Bug #80934 [NEW]: Can't compare numeric values with SQLite and json_extract to bound parameter
From: kenneth dot lynch at live dot co dot uk
Operating system:
PHP version: 7.3.27
Package: PDO SQLite
Bug Type: Bug
Bug description:Can't compare numeric values with SQLite and json_extract to bound parameter
Description:
------------
Using json_extract you can't correctly compare a numeric value to a
bound parameter in a prepared statement. The test script should select
product 2 from the data, but it doesn't. The query works perfectly using
PHP's SQLite3 extension instead.
Test script:
---------------
<?php
$conn = new PDO('sqlite::memory:');
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->exec('CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
json_data TEXT)');
$conn->exec("INSERT INTO products (json_data) VALUES ('{\"description\":
\"Product 1\", \"price\": 12.50}'), ('{\"description\":
\"Product 2\",
\"price\": 14.99}'), ('{\"description\": \"Product 3\",
\"price\":
10.50}')");
$query = $conn->prepare("SELECT * FROM products WHERE
json_extract(json_data, '$.price') >= ?");
$query->bindValue(1, 14.00);
$query->execute();
foreach ($query as $row) {
var_dump($row);
}
Expected result:
----------------
array(2) {
["product_id"]=>
int(2)
["json_data"]=>
string(44) "{"description": "Product 2", "price": 14.99}"
}
--
Edit bug report at https://bugs.php.net/bug.php?id=80934&edit=1
--
Fix committed: https://bugs.php.net/fix.php?id=80934&r=fixed
Fixed in release: https://bugs.php.net/fix.php?id=80934&r=alreadyfixed
Need backtrace: https://bugs.php.net/fix.php?id=80934&r=needtrace
Need Reproduce Script: https://bugs.php.net/fix.php?id=80934&r=needscript
Try newer version: https://bugs.php.net/fix.php?id=80934&r=oldversion
Not developer issue: https://bugs.php.net/fix.php?id=80934&r=support
Expected behavior: https://bugs.php.net/fix.php?id=80934&r=notwrong
Not enough info: https://bugs.php.net/fix.php?id=80934&r=notenoughinfo
Submitted twice: https://bugs.php.net/fix.php?id=80934&r=submittedtwice
register_globals: https://bugs.php.net/fix.php?id=80934&r=globals
PHP version support discontinued: https://bugs.php.net/fix.php?id=80934&r=phptooold
Daylight Savings: https://bugs.php.net/fix.php?id=80934&r=dst
IIS Stability: https://bugs.php.net/fix.php?id=80934&r=isapi
Install GNU Sed: https://bugs.php.net/fix.php?id=80934&r=gnused
Floating point limitations: https://bugs.php.net/fix.php?id=80934&r=float
No Zend Extensions: https://bugs.php.net/fix.php?id=80934&r=nozend
MySQL Configuration Error: https://bugs.php.net/fix.php?id=80934&r=mysqlcfg
Thread (4 messages)
- kenneth dot lynch at live dot co dot uk