Unexpected SELECT output from SQLite

5 mins ago was testing a query against SQLite database and got the following issue with < operator. Here’s the info:

[Table schema]

CREATE TABLE products (
    name      TEXT,
    number    TEXT,
    category  TEXT,
    quantity  TEXT,
    available TEXT,
    price     TEXT,
    info      TEXT,
    prodlinks TEXT,
    pictures  BLOB
);

There are 100 items saved in the database in which I’m searching for specific based on the quantity value:

SELECT * FROM products WHERE quantity < 2;

This query is used in php script (part of it):

$result = $db->prepare("SELECT * FROM products WHERE quantity < 2");
$result->execute();
$result = $result->fetchAll(PDO::FETCH_ASSOC);

The result is that all items that have quantity below 2 are returned, but there are returned items with quantity values above 2 – for example 11 or 15. Never saw that before, so do you have some idea where is the problem? If the < operator is reversed with > the items with 11 or 15 qty are not returned.

Answer

The quantity column is defined as text, so the < operator will be interpreted lexicographically. If you can, you should probably redefine it as an integer. If this is not possible, you’ll need to cast it when you query it:

SELECT * FROM products WHERE CAST(quantity AS INTEGER) < 2

Advertisement

Answer