Eight one-line queries where SQLite and DuckDB disagree

I built a bank of SQL practice questions with one hard rule. Every query had to return exactly the same rows on SQLite and on DuckDB, two engines that share no code, or the question was thrown out. The point was to make sure no answer depended on which database the reader happened to have open.

Here are eight of the differences that rule exists to catch, measured on SQLite 3.50.4 and DuckDB 1.5.5 through their Python bindings, same table on both. This story was written with the assistance of an AI writing program, and every result in it comes from actually running the statement on both engines.

CREATE TABLE t (name VARCHAR, dept VARCHAR, pay INTEGER);
INSERT INTO t VALUES
  ('Ann', 'ops', 10),
  ('bob', 'ops', NULL),
  ('Cy',  'dev', 7),
  (NULL,  'dev', 3);

1. Integer division

SELECT 7 / 2;

SQLite returns 3. DuckDB returns 3.5, because its / always divides as floating point. When you want the truncated result in DuckDB you write 7 // 2, which gives 3.

2. LIKE and letter case

SELECT name FROM t WHERE name LIKE 'B%';

SQLite finds bob. Its LIKE ignores case for ASCII letters unless you turn on PRAGMA case_sensitive_like. DuckDB finds nothing, LIKE is case sensitive there, and ILIKE is the case insensitive version.

3. Where NULLs land in a sort

SELECT pay FROM t ORDER BY pay;

SQLite puts the NULL first. DuckDB puts it last. Both engines accept NULLS FIRST and NULLS LAST, so if the position matters, say it.

4. A bare column next to GROUP BY

SELECT dept, name FROM t GROUP BY dept;

SQLite answers, one name per department, taken from some row of the group. DuckDB stops with a binder error saying name must appear in the GROUP BY clause or be aggregated. SQLite's leniency is documented and occasionally handy, but a query written against it will break the day it moves.

5. Comparing a number with a string

SELECT COUNT(*) FROM t WHERE 1 = '1';

On SQLite this counts 0 rows. Both operands are literals, neither has a column affinity, so no conversion happens and an integer is never equal to a text value. DuckDB casts the string and counts all 4.

6. Casting something that is not a number

SELECT CAST('12abc' AS INTEGER);

SQLite keeps the leading digits and returns 12. DuckDB raises a conversion error. TRY_CAST in DuckDB returns NULL instead, if that is what you want.

7. Dividing by zero

SELECT 1 / 0;

SQLite returns NULL. DuckDB returns inf, which follows from point 1, the division is done in floating point.

8. Sorting text with a NULL in it

SELECT name FROM t ORDER BY name;

Both engines put 'Cy' before 'bob', since uppercase letters sort before lowercase in a binary collation. The NULL is where they differ, before 'Ann' on SQLite and after 'bob' on DuckDB. It is the same cause as point 3, it just hides better in a text column.

What to take from it

None of this is a bug, each engine documents what it does. It matters when you learn on one engine and are tested on another. Points 1, 3 and 5 are the ones that most often change an answer without any error to warn you.

The script that produces every result above is short, open both engines in memory, run the same statements, print the two outputs side by side. Anything that prints differently is a question that cannot have a single correct answer.

The questions that survived the rule are in a quiz. Twenty of them are free to play in the browser, no account needed, each wrong option with its own explanation. thibaudlepan77-svg.github.io/interview-questions-verified/#sql