https://www.sqlite.org/datatype3.html#comparison_expressions has the rules
for how values of different types are ordered and when type conversions
automatically happen.
Numbers are always less than strings, so in that example you should be
using round() instead of printf() as you discovered.
On Sat, Sep 14, 2019, 3:47 AM kapil <kapilgarg1...@gmail.com> wrote:
> Hi all,
>
> I was wondering whether sqlite does type checking for fields which are
> referenced in WHERE clause. Because when i tried to compare a field
> generated by printf function and tried to compare it with a float value,
> the comparison didn't work
>
> Eg.
> SELECT count.theme as theme, printf("%.2f",
> (count.num_sets*100.00/sum.total)) as percentage
>
> FROM count,sum
>
> WHERE percentage >= 5.00;
>
> It was not giving expected results.
> When i replaced printf with ROUND function, it worked.
> So i got to think whether this was because printf returns string value.
>
> If so then shouldn't there be type check on fields in comparisons so
> that user can get a meaningful error message or in this case, any error
> message at all
>
> Regards
> Kapil Garg
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
