I found a substitute for sqlite3 called apsw. With this the query is working as expected. Still it would be good to understand what am I missing with the sqlite3 query.