SQL query returns values when selecting multiple columns, but no data when selecting only one column

I can’t get data to return when selecting only one column. This column is an integer format with a column name of “date”. I’m using SQLite3.

sqlite> .once table.txt
sqlite> select "date",* from messages limit 3;
sqlite> .once justColumn.txt
sqlite> select "date" from messages limit 3;

Outputs table.txt

date|id|folderID|messageKey|conversationID|date|headerMessageID|deleted|jsonAttributes|notability
1556338536000000|32|2|13330|1|1556338536000000|[e-mail]|0|{"43":1,"44":[2],"45":[],"46":[],"50":false,"51":[],"52":[1,2],"53":[2],"55":[[2,1]],"57":[],"58":false,"59":false,"60":false,"61":false}|1
1556339727000000|33|8|2|2|1556339727000000|[e-mail]|0|{"43":1,"44":[2],"45":[],"46":[],"50":false,"51":[],"52":[1,2],"53":[2],"57":[],"58":false,"59":true,"60":false,"61":false}|0
1557332781000000|34|2|13430|3|1557332781000000|1605547663.1557332777559.JavaMail.cfservice@SL132APP2|0|{"43":3,"44":[2],"45":[],"46":[],"50":false,"51":[],"52":[3,2],"53":[2],"55":[[2,3]],"57":[],"58":false,"59":false,"60":false,"61":false}|1

justColumn.txt

date

I’d expect both queries to return the same data in the first column, but that’s not what’s happening. What is?

Answer

From the documentation:

If a SELECT statement that returns more than one row does not have an ORDER BY clause, the order in which the rows are returned is undefined.

I’m able to reproduce what you’re seeing with this table:

CREATE TABLE test(id INTEGER PRIMARY KEY, date INTEGER, foo, bar);
INSERT INTO test(date, foo, bar) VALUES (25, 'a', 'b'), (50, 'c', 'd'), (75, 'e', 'f');
INSERT INTO test(foo,bar) VALUES ('g', 'h'), ('i', 'j'), ('k', 'l'); -- null dates
CREATE INDEX test_idx_date ON test(date);

First query:

sqlite> SELECT * FROM test LIMIT 3;
id          date        foo         bar       
----------  ----------  ----------  ----------
1           25          a           b         
2           50          c           d         
3           75          e           f         
sqlite> EXPLAIN QUERY PLAN SELECT * FROM test LIMIT 3;
QUERY PLAN
`--SCAN TABLE test

Second query:

sqlite> .null (null)
sqlite> SELECT date FROM test LIMIT 3;
date      
----------
(null)
(null)
(null)
sqlite> EXPLAIN QUERY PLAN SELECT date FROM test LIMIT 3;
QUERY PLAN
`--SCAN TABLE test USING COVERING INDEX test_idx_date

The second one pulls results from an index with the requested column instead of the full table, and the rows in that index are returned in a different order than the ones in the table, thus different results. I imagine the situation is similar for your table.