Search code examples
sqliteuuid

Query SQLite using uuid returns nothing

I'm querying a SQLite db file using the id column, which is UNIQUEIDENTIFIER type.

The two commands are :

SELECT * FROM scanned_images WHERE id = cast('5D878B98-71B2-4DEE-BA43-61D11C8EA497' as uniqueidentifier)

or

SELECT * FROM scanned_images WHERE id = '5D878B98-71B2-4DEE-BA43-61D11C8EA497'

However, the above commands both returned nothing.

I also tried:

SELECT * FROM scanned_images WHERE rowid = 1

this command returns the correct data.

enter image description here

enter image description here

enter image description here

Solution

  • There is no uniqueidentifier data type in SQLite.
    According to the rules of type affinity described here in 3.1. Determination Of Column Affinity, the column's affinity is numeric.
    All that this expression does:

    cast('5D878B98-71B2-4DEE-BA43-61D11C8EA497' as uniqueidentifier)

    is return 5.

    You should have defined the column's data type as TEXT, because you have to treat it like TEXT and write the condition:

    WHERE id = '5D878B98-71B2-4DEE-BA43-61D11C8EA497'

    or:

    WHERE id = '(5D878B98-71B2-4DEE-BA43-61D11C8EA497)'

    if as shown in the image it contains surrounding parentheses.