> > Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 > > > > So don't make the field 10 bytes long, make it only 8. SQLite won't > > > care a bit, and will give you the value in whatever format you want. > > > > Then it's not type agnostic any more. You now have an 8 byte numeric > > and a 10 byte numeric. Which is no different than integer and real. > > Wrong, and obviously so. I mean really, how many bytes LONG a value > is must DETERMINE whether it represents an integer or a floating point > number? Must? In what bizarre alternate universe is that true?
You're so garbled here I'm not sure what you're trying to say. Here's the premise as I understood it: "All division operations should be performed identically." From: > > Am I alone in thinking that a division operator that does different > > things depending on the declared datatype of a column is an > > abomination? Examing that proposal: Some basic math theory: 2 / 3 If your division result is an integer you lose precision. 2/3 = .6666 -> 0.6666 becomes zero when assigned to an integer. Therefore: you must use floating point for all results since you're only allowed to have one way to divide. We can't do conversions since that breaks the premise of "one way to divide". The poster doesn't want conversions. Conclusion: In order to have one method for division, and not lose precision, and not have conversions, you must use floating point results for all numeric calculations. You have to do conversions to a common type and possibly conversions back to the destination type. Well, to be accurate, you could if you have only one numeric type, floating point. Most Interpreted languages, and sql engines, hide this basic fact by doing the conversions invisibly, like: SELECT 5/'2' . (See end of message for references) If you have an untyped database or language it converts the operands from variant to a numeric internally, then does the math, then converts it back to a variant again. I think the original poster's real complaint is that the coversions weren't done automatically and it was too much effort for him to learn to do it. My response to the proposed "typeless database with automatic conversion": Unless you can come up with a variant class as space efficient as the types it replaces what's the advantage? You use more storage (the variant represenation is larger) and have a slower system (longer retrieval because of more data + slower calculations). The choice of typing or not comes down to Efficiency versus Ease of programming. I thought the basic idea of SQLite was to be fast and light. "Typeless SQLite" seems to be a step backward from efficiency in both areas to me. I proposed splitting the project into two branches so people who wanted standards compliance and the people who wanted ease of programming could both have what they wanted. The suggestion was called "lame" and "purist fetishism". Why does everyone insist on having only one tool in their toolbox and trying to use it for everything even when it's not suited for it? I don't suppose I should suggest an easy programming vs an efficient version either? They're fundamentally different goals and need different solutions. > > > > From SQLite's standpoint it is agnostic. SQLite neither knows nor cares > > > what is actually stored in the column; that's up to your application to > > > The only way for this to work will be to remove all mathematic > > operations. You can't make it agnostic of types if you have more > > than one type and allow operations to be performed on the types. > > Again wrong. You missed the assumption at the beginning of the thread. > > (Note that deciding to do math on values, even if you do it via the > "+" operator in a SQL query, *IS* part of the application. It's > certainly not part of the data storage layer, at any rate.) SELECT 5/2; Is not evaluated by the application. Maybe you meant to say it "ought to be part of the application"? Or by "data layer" you mean Sqlite? > > Jay, it's painful to see you put your foot in your mouth over and over > again. Please learn enough so that you stop sticking it in there. I've been trying to not be unpleasant. I'm sorry you can't do the same. Perhaps you should take more time to cool off before posting. If you have a logical argument, rather than insults, I'm perfectly willing to listen. I think we're talking about different things here. I'm trying to understand your point, but are you trying to understand mine? > > E.g., Tcl can be reasonably described as type agnostic, yet it can do > math. Since DRH is also a member of the Tcl Core Team, presumably Tcl > was a design influence on SQLite. It might be useful to look at it > for comparison. You missed the basic assumption that conversions aren't allowed. References on conversion: TCL: The very first google result on "tcl arithmetic conversion" returns someone complaining about conversions not working well: "Abstract This TIP proposes several changes to the conversion between floating point numbers and character strings. The changes are made to restore the "everything is a string" contract that Tcl implicitly makes; without them, there are observable differences in the behavior of floating point numbers, depending on the state of the internal representation. Rationale In today's (8.4) Tcl, there are several gaffes that make floating-point arithmetic less useful than it might be, and cause confusion for the users. Chief among these is that string equality does not imply value equality for floating point numbers:" http://www.tcl.tk/cgi-bin/tct/tip/132.html ---- TCL math conversions: "TYPES, OVERFLOW, AND PRECISION All internal computations involving integers are done with the C type long, and all internal computations involving floating-point are done with the C type double. When converting a string to floating-point, exponent overflow is detected and results in a Tcl error. For conversion to integer from string, detection of overflow depends on the behavior of some routines in the local C library, so it should be regarded as unreliable. In any case, integer overflow and underflow are generally not detected reliably for intermediate results. Floating-point overflow and underflow are detected to the degree supported by the hardware, which is generally pretty reliable. Conversion among internal representations for integer, floating-point, and string operands is done automatically as needed. For arithmetic computations, integers are used until some floating-point number is introduced, after which floating-point is used. " http://tmml.sourceforge.net/doc/tcl/expr.html --- C language conversions: "Usual Arithmetic Conversions Most C operators perform type conversions to bring the operands of an expression to a common type or to extend short values to the integer size used in machine operations. The conversions performed by C operators depend on the specific operator and the type of the operand or operands. However, many operators perform similar conversions on operands of integral and floating types. These conversions are known as "arithmetic conversions." Conversion of an operand value to a compatible type causes no change to its value. The arithmetic conversions summarized below are called "usual arithmetic conversions." These steps are applied only for binary operators that expect arithmetic type and only if the two operands do not have the same type. The purpose is to yield a common type which is also the type of the result. To determine which conversions actually take place, the compiler applies the following algorithm to binary operations in the expression. The steps below are not a precedence order. " MSDN
