How to test if a blob in an sqlite table is not null, without reading the blob entirely?

Question

I am trying to query a table that has a blob column, and need to filter the results to only provide rows that have content (any content) in the blob column.

However, doing SELECT column_name FROM table_name WHERE blob_column IS NOT NULL takes a long time, which I assume is due to the fact that some of the blobs are quite heavy. It seems that the WHERE clause is reading the entire content of the blobs and comparing them to null.

Is there a way to test whether a blob column is null or not, without having sqlite read the entire contents of the blob?

There's a related question in http://stackoverflow.com/questions/6899363, where the asker was looking to get the actual size of the blob, rather than merely whether it was empty. — waldyrious, Apr 20 '17 at 11:30

score 6 · Accepted Answer · edited Apr 20 '17 at 12:19

6

Since version 3.7.12, using the length() or typeof() functions avoids reading the binary data (the query is much faster). So to avoid the unnecessary overhead, one must change

SELECT column_name FROM table_name WHERE blob_column IS NOT NULL

to

SELECT column_name FROM table_name WHERE LENGTH(blob_column) IS NOT NULL

or

SELECT column_name FROM table_name WHERE TYPEOF(blob_column) != 'null'

edited Apr 20 '17 at 12:19

CL.

173,858
17
217
259

answered Apr 20 '17 at 11:29

waldyrious

3,683
4
33
41

2

Wow! That is highly non-intuitive. – Gordon Linoff Apr 20 '17 at 11:40
For me only `LENGTH(blob_column) != 0` worked – rightaway717 Aug 30 '23 at 15:19
Do you mean `TYPEOF` did not work and you had to use `LENGTH`, or `'null'` did not work and you had to use `0`? – waldyrious Sep 01 '23 at 07:25

score 0 · Answer 2 · answered Apr 20 '17 at 11:39

0

Use sqlite3_blob_read() On success returns SQLITE_OK. Otherwise, an error code

answered Apr 20 '17 at 11:39

Anand

31
1

How to test if a blob in an sqlite table is not null, without reading the blob entirely?

2 Answers2