We have previously discussed the issue of fixed point. It is my
contention that Sqlite would be much enhanced by having a fixed point
numeric type using ASCII decimal digits in display format. It would be
very much in keeping with the general philosophy of avoiding strict
typing and would prevent users from trying to use floating point
incorrectly.
We have to use TEXT type to store money amounts and perform arithmetic
in the application in the absence of an Sqlite fixed point type to avoid
the amateurish errors introduced by trying to use FP.
The lack of a fixed point type in Sqlite is the issue and expecting
floating point to somehow not be floating point because of the lack of
fixed point is specious.
I do agree with your concern about wrappers. If they are conceived in
such a way as to blind the user to what is really happening then they
are a menace. It is an old adage that every problem in CS can be solved
by yet another level of indirection, but that does not mean that
additional levels of indirection will always solve the problem - they
can so easily be the problem.
Cariotoglou Mike wrote:
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 th
e 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: sqlite-users@sqlite.org
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.
