Skip to main content
  1. Tags/


SQLite database WHERE clause condition not filtering rows

·5 mins

It is possible to create a table in an SQLite database where the columns have no type.

For example, the columns in this STATS table have no type specified:

cid  name       type  notnull  dflt_value  pk
---  ---------  ----  -------  ----------  --
0    PLAYER_ID        0                    0 
1    YEAR             0                    0 
2    TEAM             0                    0 
3    AB               0                    0 
4    HITS             0                    0 
5    HR               0                    0 
6    RBI              0                    0 

This doesn’t seem to make much of a difference if you are storing strings / text data, and writing queries to retrieve things based on those strings.

However, if you are storing numbers, then you’ll notice a problem if you try queries like this:

GNU Terry Pratchett through the Looking Glasses

“A man is not dead while his name is still spoken.”

One day I discovered the existence of Looking Glasses. It was so long ago now that I don’t remember exactly what led to it, but I know I found and became quite intrigued by it.

A Looking Glass is a system that network operators might use to find out Internet routing and BGP-related information. They provide insight into how a particular router connects the Autonomous Systems that make up the internet.

But this post isn’t about Looking Glasses, it’s about something else I found. So back to my story…