SQLite Forum

create unique index and order by not really unique or ordered properly
Correct --- because a field containing text (that has no affinity) can never be equal to an integer constant that has no affinity.  They are different types.  

Even though they may "look the same" when viewed after certain processing (such as printing in ASCII text on paper or a CRT) they are not the same.

Your "cast" converted the text items with no affinity to integers with integer affinity, so when the result was compared (using affinity) to the constants (integers having no affinity) the comparison was integer to integer, which *could* result TRUE result if the values were equal.