WHERE seconds > 60 Returned a 9-Second Clip and Skipped a 120-Second One: SQLite's .import Made the Column TEXT

I loaded a six-row CSV of camera clip lengths into SQLite and asked for every clip longer than a minute. The answer included a 9-second clip and left out a 120-second one. In the same session, sum() over the same column gave the right total.

Here is the whole file:

name,seconds,mb
clip_01.mp4,9,4
clip_02.mp4,120,58
clip_03.mp4,61,30
clip_04.mp4,600,290
clip_05.mp4,45,22
clip_06.mp4,75,36

And here is what the sqlite3 command-line shell did with it, run as sqlite3 a.db < auto.sql on a fresh database:

.import --csv clips.csv clips
.schema clips
SELECT name, seconds FROM clips WHERE seconds > 60;
SELECT max(seconds), min(seconds), sum(seconds) FROM clips;
SELECT group_concat(seconds, ' ') FROM (SELECT seconds FROM clips ORDER BY seconds DESC);
SELECT DISTINCT typeof(seconds) FROM clips;

CREATE TABLE IF NOT EXISTS "clips"(
  "name" TEXT,
  "seconds" TEXT,
  "mb" TEXT
);
clip_01.mp4|9
clip_03.mp4|61
clip_04.mp4|600
clip_06.mp4|75
9|120|910
9 75 61 600 45 120
text

Four rows came back for seconds > 60, and one of them is clip_01 at 9 seconds. clip_02, at 120 seconds, is missing. max() says the longest clip is 9 seconds and min() says the shortest is 120. The descending sort puts 9 first and 120 last. And sum() reports 910, which is correct: 9 + 120 + 61 + 600 + 45 + 75.

Nothing in the data is wrong. The schema line explains all of it.

Disclosure: I make an Android camera app. Nothing below uses its code or describes how it stores anything. I wrote the CSV for this test. Every output is pasted unedited, from the sqlite3 shell and library at version 3.37.2 and Python 3.10 on Linux.

The auto-created table is all TEXT

The SQLite shell documentation describes what .import does when the target table does not exist yet: "If it does not exist, the table is automatically created and the content of the first input row is used to set the name of all the columns in the table."

That sentence covers the column names. It says nothing about the column types, and on 3.37.2 the .schema output above shows what it picked: every column is TEXT. The CSV format has no way to say that 9 is a number, so every value went in as a string.

That alone would be harmless. The trouble is how SQLite compares a string column with a number.

Why 9 is greater than 60

SQLite's datatype page lists the rules it applies to the two sides of a comparison before comparing them, in order:

• "If one operand has INTEGER, REAL or NUMERIC affinity and the other operand has TEXT or BLOB or no affinity then NUMERIC affinity is applied to the other operand."
• "If one operand has TEXT affinity and the other has no affinity, then TEXT affinity is applied to the other operand."
• "Otherwise, no affinity is applied and both operands are compared as is."

In WHERE seconds > 60, the left side is a column declared TEXT, so it has TEXT affinity. The literal 60 is an expression, and a bare literal has no affinity. The first rule does not match, because neither side has a numeric affinity. The second rule does. So 60 is converted to the string '60', and the comparison becomes a string comparison, character by character.

The same page has a worked example with a column named a declared TEXT, and its comment says the numbers on the right-hand side are converted to text before the comparison happens.

Run as plain string comparisons, the results line up with every row above:

SELECT '9' > '60', '120' > '60', '45' > '60';

1|0|0

'9' beats '60' because the character 9 sorts after 6. '120' loses because 1 sorts before 6. '45' loses because 4 sorts before 6. Following the same rule, '61', '600' and '75' pass, which is exactly the four rows the query returned.

max(), min() and ORDER BY follow the same logic. The datatype page says of sorting: "No storage class conversions occur before the sort." All six values are text, so they are ordered as text, and as text the largest is '9' and the smallest is '120'.

sum() is the odd one out. In this run it read strings that look like numbers as numbers, so it added them correctly. That is what makes this hard to spot: a report that shows a total and a longest clip side by side will show a right total next to a wrong maximum, and the right number makes the wrong one look trustworthy.

Declare the types first, and the header becomes a row

The obvious fix is to create the table yourself with numeric columns and then import into it. That fixes the comparison, and introduces a different problem:

CREATE TABLE clips (name TEXT, seconds INTEGER, mb INTEGER);
.import --csv clips.csv clips
SELECT count(*) FROM clips;
SELECT name, seconds, typeof(seconds) FROM clips WHERE seconds > 60;
SELECT max(seconds) FROM clips;

7
name|seconds|text
clip_02.mp4|120|integer
clip_03.mp4|61|integer
clip_04.mp4|600|integer
clip_06.mp4|75|integer
seconds

The count is 7, not 6. The shell documentation explains why: "If the target table already exists, every row of the input, including the first, is taken to be actual data content." The header line name,seconds,mb went in as a clip named name with a length of seconds.

An INTEGER column takes that word anyway: "The important idea here is that the type is recommended, not required." The text seconds cannot be turned into a number, so it is stored as text. The five real values were converted to integers, which is why typeof(seconds) now says integer on those rows.

Now the comparison runs the other way. The column has INTEGER affinity, so the first rule applies and 60 stays a number. The four real rows over a minute come back correctly. So does the header row, because of a separate rule on the same page: "An INTEGER or REAL value is less than any TEXT or BLOB value." The word seconds is text, every number is smaller than it, and max(seconds) returns the word seconds.

Skip the header: six rows, right answers

The shell documentation gives the fix for this case in the same paragraph: use the --skip 1 option when the input starts with a row of column labels.

CREATE TABLE clips (name TEXT, seconds INTEGER, mb INTEGER);
.import --csv --skip 1 clips.csv clips
SELECT count(*) FROM clips;
SELECT name, seconds FROM clips WHERE seconds > 60;
SELECT max(seconds), min(seconds), sum(seconds) FROM clips;
SELECT DISTINCT typeof(seconds) FROM clips;

6
clip_02.mp4|120
clip_03.mp4|61
clip_04.mp4|600
clip_06.mp4|75
600|9|910
integer

Six rows. The four clips over a minute and nothing else. max() is 600, min() is 9, sum() is still 910, and every value is stored as an integer. This is the version I would use for a quick look at a CSV: write the CREATE TABLE with the types you mean, then import with --skip 1.

STRICT refuses the header out loud

SQLite has had STRICT tables since version 3.37.0. The STRICT tables page describes what happens when a value does not fit the declared type: "If the value cannot be losslessly converted in the specified datatype, then an SQLITE_CONSTRAINT_DATATYPE error is raised."

Here is the same import into a STRICT table, without --skip:

CREATE TABLE clips (name TEXT, seconds INTEGER, mb INTEGER) STRICT;
.import --csv clips.csv clips
SELECT count(*) FROM clips;
SELECT max(seconds) FROM clips;

clips.csv:1: INSERT failed: cannot store TEXT value in INTEGER column clips.seconds
6
600

The header row was rejected with a message naming the file, line and column. The other six rows went in and max() is 600. The forgotten --skip 1 became an error on screen instead of a seventh row. One caution: in my run the shell carried on after that error and the script finished normally, so a script that only checks whether the shell exited cleanly would not notice. Read the output.

Leave the table as it is and cast

If the auto-created table is already there and full of data, you can fix the query instead of the table:

.import --csv clips.csv clips
SELECT name, seconds FROM clips WHERE CAST(seconds AS INTEGER) > 60;
SELECT max(CAST(seconds AS INTEGER)) FROM clips;

clip_02.mp4|120
clip_03.mp4|61
clip_04.mp4|600
clip_06.mp4|75
600

The datatype page says an expression of the form CAST(expr AS type) gets the same affinity as a column declared with that type. Once the left side has INTEGER affinity, the first comparison rule applies and the comparison is numeric. This works, but one WHERE clause without the cast brings the 9-second clip back.

Python's sqlite3 module gives a third answer

Load the CSV with Python's csv module into a table created without types, and the same question gets another answer:

import csv, sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE clips (name, seconds, mb)")   # no declared types
with open("clips.csv", newline="") as f:
    rows = csv.reader(f)
    next(rows)                                          # skip the header
    db.executemany("INSERT INTO clips VALUES (?, ?, ?)", rows)

print(db.execute("SELECT name FROM clips WHERE seconds > 60").fetchall())
print(db.execute("SELECT DISTINCT typeof(seconds) FROM clips").fetchall())

[('clip_01.mp4',), ('clip_02.mp4',), ('clip_03.mp4',), ('clip_04.mp4',), ('clip_05.mp4',), ('clip_06.mp4',)]
[('text',)]

All six clips, including the 45-second one. The datatype page says that when no type is specified the column has BLOB affinity, and that such a column does not try to convert values from one storage class to another. So the CSV strings stay strings. In the comparison neither side has an affinity, so the third rule applies, nothing is converted, and the rule about storage classes decides it: any number is less than any text. Every clip length, stored as text, is greater than 60.

Newer shells pick a different column type

The TEXT columns above are what 3.37.2 creates, and this has changed in newer releases. A thread on the SQLite forum from June 2026, titled "cli import csv defaults to ANY instead of TEXT", reports this schema from version 3.53.1:

CREATE TABLE "t"( "Id" ANY, "Market" ANY, "Symbol" ANY, "InstrumentType" ANY);

In an ordinary table, a column declared ANY falls through to NUMERIC affinity under the type-name rules. The STRICT tables page describes the effect: "For an ordinary non-strict table, a column of type ANY will attempt to convert strings that look like numbers into a numeric value, and if successful will store the numeric value rather than the original string." That would make seconds > 60 correct. It would also strip leading zeros, which is what the person in that thread reported: "the issue is this removes 0 prefix from strings unexpectedly." I checked that behaviour with the 3.37.2 library:

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE t (code ANY)")                 # ordinary table, not STRICT
db.execute("INSERT INTO t VALUES ('0045')")
print(db.execute("SELECT code, typeof(code) FROM t").fetchall())

[(45, 'integer')]

Later in the same thread, a SQLite developer replied that they had "tentatively removed the affinity altogether, which should resolve the regression". I have not run a release with that change, so I cannot tell you what a current shell creates. If it creates columns with no declared type, the Python result above suggests what to expect: BLOB affinity, strings left as strings, and every row greater than any number.

The practical point is that the auto-created schema depends on the version of the shell you happen to have. It has changed at least once, and a developer has since described a further change. Run .schema after any import into a table you did not create, and run SELECT DISTINCT typeof(col) on the columns you plan to compare.

What this does not cover

• Other versions. Everything was run on 3.37.2. For newer shells I have the forum thread and nothing I ran.
• Other import tools. Spreadsheet exports, pandas.to_sql and other loaders make their own type choices. Only the shell and plain Python inserts were tested.
• Messier data. Blanks, decimals or units inside the column would add cases this does not test.

Sources. The comparison and sorting rules, the type-name rules and the CAST rule are from SQLite's Datatypes In SQLite page. The .import first-row behaviour and --skip are from the Command Line Shell For SQLite page. The STRICT error and the ANY behaviour in ordinary tables are from STRICT Tables. The 3.53.1 schema and the developer's reply are from the forum thread cli import csv defaults to ANY instead of TEXT.

If you want an old Android phone recording in the background so there is a folder of clips worth keeping a log of, the app I make is Background Camera RemoteStream on Google Play. Nothing above depends on it.

A table SQLite built for you can hold numbers and still compare them as words, and the first sign is usually a max() that looks wrong sitting next to a sum() that looks right.