Someone please correct me if I'm wrong, but I believe it's mandated by the SQL standard that integer division is used when both operands are integers.
Your synthetic example doesn't use a fixed table, but if it did the easiest solution for you would probably be to define any columns where you need arbitrary precision as REAL rather than INTEGER, and SQLite column affinity would do the rest. Otherwise, yes, I believe you would need to cast. On December 14, 2017 11:36:19 AM EST, Tony Papadimitriou <to...@acm.org> wrote: >I’ve noticed this (very annoying) behavior: > >select column1*(24/100) wrong from (values(100)); > >Removing the parentheses yields the correct result: > >select column1*24/100 correct from (values(100)); > >This obviously behaves like integer math is used and (24/100) gets >truncated to zero. > >If I add a dot to either number (e.g., 24. or 100.) I get the correct >result. >But, with named fields, it’s not as easy as adding a dot: > >select column1*(column2/column3) wrong from (values(100,24,100)); >select column1*column2/column3 correct from (values(100,24,100)); > >So, to get correct answer I have to use a cast for either field? > >select column1*(cast(column2 as float)/column3) correct from >(values(100,24,100)); > >In this example removing the parentheses is a simple solution. >But if the expression was column1*(1+column2/column3) a cast is the >only way? (Hope not!) > >Anyway, if all this happens to be so by design, is there at least some >way to default to float math rather than integer? > >Thanks. >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Sent from my Android device with K-9 Mail. Please excuse my brevity. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
