Possibly corrupt sqlite database?

Question

So I have this SQLite3 database. I've noticed a problem that when trying to select songs using the uid column, it does not return any rows; for example:

SELECT * FROM songs WHERE uid = 'yzoiaVuicn5ISq1+4DaKGbM3trht/z/ONNm+vA=='

Even though a row with the given uid exists. Replacing the '=' with 'LIKE' returns the correct row.

I've been able to fix it using:

UPDATE songs SET uid = uid || ''

Is this a typical case of corrupt db? Or could it be an sqlite bug? How could this have happened?

Answer 1

I don't believe this is an indication of corruption rather how = is handled.

For example consider the following :-

SELECT uid, 
    TRIM(uid) = 'yzoiaVuicn5ISq1+4DaKGbM3trht/z/ONNm+vA==' AS trim1match,
    TRIM(uid) = TRIM('yzoiaVuicn5ISq1+4DaKGbM3trht/z/ONNm+vA==') AS trimbothmatch,
    CAST(uid AS TEXT) = 'yzoiaVuicn5ISq1+4DaKGbM3trht/z/ONNm+vA==' AS casttotextmatch,
    'yzoiaVuicn5ISq1+4DaKGbM3trht/z/ONNm+vA==' LIKE(uid) AS likematch,
    'yzoiaVuicn5ISq1+4DaKGbM3trht/z/ONNm+vA==' = uid AS reversematch
FROM songs 
WHERE trim(uid) = 'yzoiaVuicn5ISq1+4DaKGbM3trht/z/ONNm+vA=='

The WHERE clause, using TRIM(uid) selects the appropriate row. The resultant output is :-

• Noting that 'yzoiaVuicn5ISq1+4DaKGbM3trht/z/ONNm+vA==' = uid AS reversematch returns false, but all other comparisons return 1 i.e. true and thus a match.

The Issue (uid stored as a BLOB)

The issue is that the uid column has a storage class of BLOB and thus a type affinity of BLOB. This can be seen by adding column that extracts the type of the column/row by using the typeof function e.g. :-

SELECT uid, typeof(uid),
    trim(uid) = 'yzoiaVuicn5ISq1+4DaKGbM3trht/z/ONNm+vA==' AS trim1match,
    trim(uid) = trim('yzoiaVuicn5ISq1+4DaKGbM3trht/z/ONNm+vA==') AS trimbothmatch,
    CAST(uid AS TEXT) = 'yzoiaVuicn5ISq1+4DaKGbM3trht/z/ONNm+vA==' AS casttotextmatch,
    'yzoiaVuicn5ISq1+4DaKGbM3trht/z/ONNm+vA==' LIKE(uid) AS likematch,
    'yzoiaVuicn5ISq1+4DaKGbM3trht/z/ONNm+vA==' = uid AS reversematch
FROM songs 
WHERE trim(uid) = 'yzoiaVuicn5ISq1+4DaKGbM3trht/z/ONNm+vA=='

results in :-

Hence converting the column to a type of TEXT explicitly (CAST) or implicitly (some functions such as TRIM) resolves the issue. Note that SUBSTR return bytes from a BLOB so will not convert the column type and hence substr(uid,1) doesn't work.

---

Note

Running the above less the where clause indicates that some rows have a column type of TEXT for the uid column, as per :-