Records not fetched when making comparison between integer column and string literal
(1) By anonymous on 2020-07-04 09:31:48 [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 [link] [source]
This bug was fixed on 2019-05-08 and release with version 3.29.0. (Version 3.32.3 is current.) See also: