Jian H. L. <[email protected]> wrote: > The first and the third queries are the same. > > The second one changes the where clause from the form of: > 'where a - b > 0' > > to: > 'where a > b' > > Shoudn't the two where clauses be the same?
Only if a and b are both numeric. But strftime() returns a string. When you do (number - string), the string is coerced to a number, and then the subtraction is performed on two numbers. With comparison operators, the story is much more complicated. You can piece it together from http://sqlite.org/datatype3.html , but the bottom line is that, in the expression endtime + duration > strftime(...) both sides of the comparison have affinity NONE, so no type coercion is performed, and integers are always compared less than strings regardless of actual values. You can fix it, for example, by writing endtime + duration > cast(strftime(...) as integer) -- Igor Tandetnik _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
