SQLite Forum

Records not fetched when making comparison between integer column and string literal
Login

Records not fetched when making comparison between integer column and string literal

(1) By anonymous on 2020-07-04 09:31:48 [link] [source]

I have the following SQL script to set up a simple table and some dummy data.

DROP TABLE IF EXISTS tardiest;
CREATE TABLE tardiest (
 id integer,
 anyplace integer,
 planning integer,
 patterson integer,
 casting numeric(10, 2),
 speccing varchar(50),
 capping varchar(50),
 PRIMARY KEY (id)
);
INSERT INTO tardiest(id,anyplace,planning,patterson,casting,speccing,capping) VALUES (1,0,5,2,2,'Patti','Rotterdam');
INSERT INTO tardiest(id,anyplace,planning,patterson,casting,speccing,capping) VALUES (2,3,-1,5,0,'excerpts','wannabe');
INSERT INTO tardiest(id,anyplace,planning,patterson,casting,speccing,capping) VALUES (3,0,7,3,-6,'breakers','left');
INSERT INTO tardiest(id,anyplace,planning,patterson,casting,speccing,capping) VALUES (4,-9,-9,9,7,'flier','mommies');
INSERT INTO tardiest(id,anyplace,planning,patterson,casting,speccing,capping) VALUES (5,-8,-9,-4,2,'hangout','nestlings');

When I run the following SQL query

sqlite> SELECT `tardiest`.`id`, `tardiest`.`patterson` FROM `tardiest` AS `tardiest` WHERE (`tardiest`.`id` < 'oil');

I get all records back as expected (since tardiest.id < 'oil' evaluates to TRUE).

However, when I attempt to sort the records of this query as follows:

sqlite> SELECT `tardiest`.`id`, `tardiest`.`patterson` FROM `tardiest` AS `tardiest` WHERE (`tardiest`.`id` < 'oil') ORDER BY `tardiest`.`id` DESC, `tardiest`.`patterson` DESC;

I get no results back (i.e, an empty set).

(2) By Richard Hipp (drh) on 2020-07-04 10:41:57 in reply to 1 [source]

This bug was fixed on 2019-05-08 and release with version 3.29.0. (Version 3.32.3 is current.) See also: