I see again that you all miss the point. I DO know how to handle floating point. My point is : a. a lot of people will make the error indicated. I am sure that they are poor programmers. I am also sure (judging from some of the questions posted in this list), that there is a lot of them... b. some databases (ORACLE is the only one that comes to mind) support this properly, by allowing for fixed point types. so, a type declared as NUMERIC(10,3), which is ANSI-92, will be handled properly in comparisons. Most other engines will use floating point only, and will fail.So, to the question : " should all databases implement your fix" the answer is YES if they need it. c. the REAL danger with wrappers is this: you are thinking of wrappers that hide the SQLITE api. Ok, I agree that those do not need to handle the problem, as the programmer can do it themselves. but consider: a lot of people out there, I suspect, use SQLITE through a higher-level API. take a look at the ODBC, OLEDB and .NET providers. They allow point-and-click programmers (yes, mum, they do exist, they are probably idiots, but they outnumber us "real" programmers by a factor of 10:1 at least) to use niceties as data binding, data-aware grids and so on. Now all these data providers do not just hide the API, they also add functionality. One very important one, is , to generate SQL to update the database when a data-aware control changes. The generated SQL is never under the control of the programmer, so he cannot do anything about floating point comparisons or anything else. In particular, generated UPDATE statements (and DELETE, for that matter), will add a WHERE clause, where ALL fields will be compared to their "original" value. why is this ? In order to achieve optimistic row locking, in other words to detect changes by other users in a multi-user environment. now THIS is the situation I am talking about, and there is simply no fix for this problem, unless the db engine itself can handle it. Of course, one can avoid the problem by not using floats, which also implies not using DATES since a lot of programming environments and DB apis use floats for dates (OLEDB does, Delphi does, Visual Basic does etc etc). But this will seriously reduce the usability of SQLITE with RAD environments, which try to hide the DB access from the programmer. Again, I am not advocating that point-and-click programming is good, or that RAD environments are good, or that data-aware controls are a cool idea. I am aware of the issues with all these, having used them for decades. What I am saying is that there a lot of poor sods out there that dont know any better, so they DO use these things, and they are in for a big surprise... since a solution to this issue is fairly simple, and the applicable audience is large, why not provide one? the fact that MSSQL will not be able to do the same is not an argument that has stopped drh before, has it.
________________________________ From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Wed 14-Dec-05 7:35 PM To: [email protected] Subject: Re: [sqlite] Problem with floating point fields, and a feature request Dave Dyer wrote: >>>select * from test where f=13.06; -- returns no data > > > Pardon me for throwing a bomb, but no good programmer > would ever use = to compare floating point numbers. > > Choose a more appropriate representation for your data. > It is not a bomb, just something novice programmers have to learn. There is no = in floating point, it is only possible with integers.
