On 30 Oct 2009, at 9:47pm, Alexey Pechnikov wrote: > Now SQLite think that 1 is equal to '1' in some causes and think > different in other.
Just like every other language, once you get into it you have to learn how the language works to understand what's going on. Your problem is not really with the comparison, it's with what happens to a value when it is stored in a table. Strongly typed languages usually do one of two things: A) not allow the comparison at all (you get a syntax error from the compiler) B) say that two values of different types never equal one-another I think either of those would suit you and fortunately you can implement each one in whatever programming language you're using, before the values get near SQLite. You're a person who thinks along the lines of strong typing, so do what suits you. > Do you know some language where 1='1' or 1!='1' randomly? Not randomly, no. But I know of quite a few languages where storing a value somewhere can modify it: Start with value V. Store it somewhere. Get it back again. Compare with the original and they do not match. In C, try storing a 'double float' value in a 'single float', getting it back out into a 'double float' again, then comparing that with the original value. (Make sure your compiler doesn't optimise out the operations.) You can find plenty of initial values that this fails for. So obviously, the operation of storing-then-retrieving the value makes it difficult to tell how it behaves in the future. > The correct way is to convert numeric values to text before check > of equality. That is no more correct than to refuse to compare numeric and text values. The thing you have spotted does really exist, but I don't think it's a bug. If you want to prove it's a bug, I think this is where to look: <http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt> The key word seems to be 'comparable'. It might be in the section on page 25 on 'coercibility rules for dyadic operators' or section 8.2 on page 207 on comparison operators. I don't think the standard says definitely one way or another whether you can compare a string with a number, but I didn't read it thoroughly taking notes. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
