Edit report at https://bugs.php.net/bug.php?id=80934&edit=1
ID: 80934
Comment by: kenneth dot lynch at live dot co dot uk
Reported by: kenneth dot lynch at live dot co dot uk
Summary: Can't compare numeric values with SQLite and
json_extract to bound parameter
Status: Not a bug
Type: Bug
Package: PDO SQLite
PHP Version: 7.3.27
Block user comment: N
Private report: N
New Comment:
Sorry, test script was incomplete.
<?php
$conn = new PDO('sqlite::memory:');
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->exec('CREATE TABLE products (id INTEGER PRIMARY KEY, price REAL, json_price
TEXT)');
$insert = $conn->prepare('INSERT INTO products (price, json_price) VALUES (?, ?)');
$insert->execute([10.50, json_encode(['price' => 10.50])]);
$insert->execute([11.50, json_encode(['price' => 11.50])]);
$insert->execute([12.50, json_encode(['price' => 12.50])]);
$q1 = $conn->prepare('SELECT * FROM products WHERE price >= ?');
$q2 = $conn->prepare('SELECT * FROM products WHERE json_extract(json_price,
\'$.price\') >= ?');
echo "Comparison with price\n\n";
foreach ([10, 10.25, 10.50, 10.75, 11, 11.25, 11.50, 11.75, 12.00] as $price) {
echo 'Price >= '. $price ."\n=============\n";
$q1->bindValue(1, $price, PDO::PARAM_STR);
$q1->execute();
foreach ($q1 as $row) {
var_dump($row);
}
echo "\n";
}
echo "Comparison with json_price\n\n";
foreach ([10, 10.25, 10.50, 10.75, 11, 11.25, 11.50, 11.75, 12.00] as $price) {
echo 'Price >= '. $price ."\n=============\n";
$q2->bindValue(1, $price, PDO::PARAM_INT);
$q2->execute();
foreach ($q2 as $row) {
var_dump($row);
}
echo "\n";
}
Previous Comments:
------------------------------------------------------------------------
[2021-04-06 13:02:26] kenneth dot lynch at live dot co dot uk
The issue is related to PDO, SQLite and the JSON1 extension, the just does not work no matter how
you cast the parameter using PDO driver. It WORKS using the SQLite3 PHP database extension.
This script works comparing against a regular column but does not work comparing against the
json_price column. Changing the bind type to PDO::PARAM_INT doesn't work as this truncates the
parameter value resulting rows being selected that shouldn't.
echo "Comparison with price\n\n";
foreach ([10, 10.25, 10.50, 10.75, 11, 11.25, 11.50, 11.75, 12.00] as $price) {
echo 'Price >= '. $price ."\n=============\n";
$q1->bindValue(1, $price, PDO::PARAM_STR);
$q1->execute();
foreach ($q1 as $row) {
var_dump($row);
}
echo "\n";
}
echo "Comparison with json_price\n\n";
foreach ([10, 10.25, 10.50, 10.75, 11, 11.25, 11.50, 11.75, 12.00] as $price) {
echo 'Price >= '. $price ."\n=============\n";
$q2->bindValue(1, $price, PDO::PARAM_STR);
$q2->execute();
foreach ($q2 as $row) {
var_dump($row);
}
echo "\n";
}
------------------------------------------------------------------------
[2021-04-04 13:01:27] dharman@php.net
This is unfortunately how SQLite database works. This is not a problem with PDO as PDO doesn't
dictate how the data selection process is carried out by the database. PDO sends the query to the
server as-is and the server then is responsible for parsing it and executing it.
Your example could be simplified not to use any JSON functions:
---------------
<?php
$conn = new PDO('sqlite::memory:');
$query = $conn->prepare("SELECT 14.99 >= ? ");
$query->bindValue(1, 14.00, PDO::PARAM_STR);
$query->execute();
foreach ($query as $row) {
var_dump($row);
}
---------------
We perform the comparison 14.99>=14.00. The expected outcome should be 1 but as a result, we get
0. However, please note that the default binding type in PDO is string. If you change it to an
integer then the comparison will work. If you want to understand the strange logic behind the SQLite
comparison and datatype juggling please read https://www.sqlite.org/datatype3.html#comparison_expressions
"An INTEGER or REAL value is less than any TEXT or BLOB value. When an INTEGER or REAL is
compared to another INTEGER or REAL, a numerical comparison is performed."
Usually, SQLite applies type affinity but in this case neither of the operands has any affinity. If
you apply one yourself then the usual rules will apply. The exact same example as above but this
time with type affinity:
---------------
$query = $conn->prepare("SELECT 14.99 >= CAST(? AS STRING) ");
$query->bindValue(1, 14.00, PDO::PARAM_STR);
$query->execute();
---------------
Is it strange how SQLite doesn't know the type of literals? Maybe. Is it a bug in PHP? No!
------------------------------------------------------------------------
[2021-04-04 09:56:47] kenneth dot lynch at live dot co dot uk
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 this bug report at https://bugs.php.net/bug.php?id=80934&edit=1