SQLite queries that return the wrong answer quietly

SQLite is built to accept what it is given, and that is the whole reason a wrong query often does not look wrong. A statement that another engine would refuse is run here, and the rows that come back look plausible: a share of zero percent, a list that is empty, a total that is one day short, a name that belongs to a different row. Nothing in the log says so. This guide goes through the traps that matter most when a query is written from a plain-language question, says what the SQLite documentation states about each, and ends with a tiny database whose correct answers are known, so a query can be tested instead of trusted.

The numbers in this guide come from one small run on an in-memory database with SQLite 3.53.0, and the script and the output are kept with the post's sources.

A division that throws the fraction away

The expression page says that arithmetic is done on integers when both operands are integers, and that an integer divide gives an integer, with the fraction cut off toward zero (SQLite expressions). So integer division makes 5/2 equal to 2, and -7/2 equal to -3. A question such as "what share of orders are paid" is then easy to get wrong: with five of six orders paid, SUM(paid)/COUNT(*) returned 0 in our run, and 100*SUM(paid)/COUNT(*) returned 83, which looks like an answer and drops the decimals. Writing 100.0*SUM(paid)/COUNT(*) gives 83.3, and a rounding call at the very end sets how many decimals are shown.

Division by zero does not fail either. In the run 1/0 returned NULL instead of raising an error, so a report that divides by a count can show blanks rather than crash.

Dates are text, and only some text sorts

The datatype page states that SQLite has no date storage class. A date may be stored as ISO-8601 text, a Julian day count or Unix seconds, and the date functions take the first two directly and the third with a modifier (SQLite datatypes, SQLite date functions). Text that begins with the year, month and day in that order, zero-padded, compares correctly as plain text, because the characters line up from the most significant part. Everything else does not.

Four consequences show up in practice:

• A day-first value such as 31/10/2026 is just a string. In the run it compared as greater than 2026-11-01, since the first character sorts later, and date() on it returned NULL because the format is not among those the function documents.
• A date missing its zero padding, such as 2026-1-5, also produced NULL from date().
• A date that does not exist is not rejected. date('2026-02-30') returned 2026-03-02, so a typo moves a row to another day rather than failing. That was observed for this guide and is not stated on the cited page, so check it on the version you run.
• The word now means the current time in UTC, which the page states, so "today" computed in the database can differ from the reader's local date near midnight.

The most common date mistake is the end of a period. If timestamps are stored with a time part, a BETWEEN that runs from the first day to the last day of the month leaves out everything on the last day after midnight, because a timestamp such as the one in order 2 is longer than the bare date and so sorts after it. In the run, five orders were placed in October and the BETWEEN version counted four. The half-open form, with a lower bound that includes the opening day of October and an upper bound that excludes the opening day of November, counted all five.

NULL makes some questions return nothing

The expression documentation lays out a table for IN and NOT IN. When the right-hand side contains a NULL and the left value is not found in it, the result of NOT IN is NULL, and a row whose condition is NULL is not returned (SQLite expressions). So "customers with no orders" written as name NOT IN (SELECT customer FROM orders) returns an empty result as soon as one order has no customer, a guest checkout for instance. In the run, a single such order turned the right answer, one customer, into zero rows. Rewriting the question with NOT EXISTS, or removing the NULL rows from the subquery, returned that customer.

Counting has a quieter version of the same rule. The aggregate page says count(X) counts the rows where X is not NULL, while count(*) returns the number of rows in the group (SQLite aggregate functions). In our table, COUNT(*) was 6 and COUNT(coupon) was 2, and neither is wrong: they answer "how many orders" and "how many orders used a coupon". The mistake is using the one that answers the neighbouring question. COUNT(DISTINCT customer) is a third answer again.

Sorting needs the same care. The select page says NULL counts as smaller than any other value, so NULLs come first in ascending order and last in descending order, and that can be changed with NULLS LAST or NULLS FIRST (SQLite SELECT). A list of "earliest due dates" that sorts ascending shows the rows with no date at the top. Likewise a total taken over an empty set is NULL and not zero, which the run confirmed.

LIKE and capital letters outside ASCII

By default, the expression page says, SQLite understands upper and lower case only for ASCII characters. 'a' LIKE 'A' is true and 'æ' LIKE 'Æ' is false, and the ICU extension is the documented way to get full case folding (SQLite expressions). We saw the same with another pair, an accented é against É, which returned false. The equals sign compares text exactly by default, so a capital letter never matches its lowercase twin. A name search that works for English data therefore misses capitalised names in Cyrillic, Greek or accented Latin, and the failure looks like "no such customer" instead of an error. If such data is in scope, test with a capitalised non-ASCII example before trusting a search.

Bare columns in GROUP BY

The select documentation describes what it calls bare columns: a column in an aggregate query that is neither aggregated nor part of the GROUP BY. SQLite allows them, and most other engines raise an error. With a single min() or max() in the query, the bare columns come from a row that holds that extreme; with a tie, or with two such aggregates, or with other aggregates only, the row is arbitrary (SQLite SELECT).

That makes a question like "each customer with their order count and the item count of an order" hazardous. The query SELECT customer, items, COUNT(*) ... GROUP BY customer returned items 2 for a customer who had orders of 2 and 4 items, and the documentation says the choice cannot be predicted. The query ran, the layout was right and the number was a coin toss that happened to fall one way. The safe versions name the row on purpose: take the maximum of the ordering column in a subquery, or use a window function that numbers the rows and keep the first.

LIMIT with no ORDER BY

The select page says that when a query returns several rows and has no ORDER BY, the rows come back in no defined order, and that rows tied on every sort expression come out in an undefined order too (SQLite SELECT). A LIMIT is applied after that order. So LIMIT 1 for "the latest order" returns whichever row the engine reached first, and it may appear correct for months because the table's physical order happens to match the intended one. Always add an order that ends in a unique column, and use a subquery on the maximum when a tie should return every tied row. The cost side of such queries is the subject of our write-up on queries that read the whole table, which matters on D1, where reads are billed.

A check that can fail

A query written from a question deserves the same test as code: a table small enough to answer by hand, and the expected answers written down before the query is run. The table used for this guide has six orders and four customers, and each of its traps is present once:

CREATE TABLE customers(id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO customers VALUES (1,'Ada'),(2,'Ben'),(3,'Cy'),(4,'Dee');
CREATE TABLE orders(id INTEGER PRIMARY KEY, customer TEXT,
  placed TEXT NOT NULL, items INTEGER NOT NULL, paid INTEGER NOT NULL, coupon TEXT);
INSERT INTO orders VALUES
 (1,'Ada','2026-10-03',2,1,NULL),
 (2,'Ben','2026-10-31T18:45:00Z',1,1,'SAVE5'),
 (3,'Ada','2026-10-15',4,0,NULL),
 (4,'Cy','2026-11-01',3,1,'SAVE5'),
 (5,'Cy','2026-10-20',5,1,NULL),
 (6,NULL,'2026-10-09',1,1,NULL);

Run the candidate query and set its result beside the middle column, not beside what looks sensible. Then confirm the check can fail: replace the correct query with the trap version from the right-hand column and make sure the comparison turns red each time. A check that accepts both versions has not measured anything. Add one row of your own that carries a NULL, one that shares a value with a neighbour and one that sits on the edge of the date range, because those are exactly the rows these traps feed on.

When this does not apply

Only SQLite is in scope: as documented, as tested here, and therefore Cloudflare D1 and Turso, which are based on it; they may run a different version, and the behaviour described for the date functions is from the page as read on one day. It does not cover other engines, query speed beyond the single link above, or full-text and JSON functions. The examples came from one tiny database, run once on one SQLite build with no repetition, so they show that each trap exists, not how often a real data set triggers it. The date normalisation of an impossible day is our own observation; the documentation page is silent on it. The list is also not complete: type affinity, collations, joins that multiply rows and window functions have their own traps that this guide leaves out.

Writing the query from the question

For turning a plain-language question and a pasted schema into one statement, the sqlite-query-from-question skill writes a query for SQLite, D1 or Turso and returns the statement alone. It reads comments on the columns as facts about the data, takes the date or format the question gives rather than the clock, and checks its first draft against the traps listed here, among others. If the schema lacks a fact that the question depends on, or two readings would return different rows, it refuses in a single short line that says what is missing, rather than inventing a column.