php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #80934 Can't compare numeric values with SQLite and json_extract to bound parameter
Submitted: 2021-04-04 09:56 UTC Modified: 2021-04-04 13:01 UTC
From: kenneth dot lynch at live dot co dot uk Assigned:
Status: Not a bug Package: PDO SQLite
PHP Version: 7.3.27 OS:
Private report: No CVE-ID: None
 [2021-04-04 09:56 UTC] 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}"
}


Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2021-04-04 13:01 UTC] dharman@php.net
-Status: Open +Status: Not a bug
 [2021-04-04 13:01 UTC] 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-06 13:02 UTC] 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-06 13:04 UTC] kenneth dot lynch at live dot co dot uk
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";
}
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Oct 31 23:01:28 2024 UTC