Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
If I am not mistaken, you can configure Sqlite to not use floating point at all. Divisions in such an environment ought to be truncated integers without additional effort. But I could be wrong. --- Dennis Cote <[EMAIL PROTECTED]> wrote: > I also think we should consider the use of SQLite in embedded > application, many of which don't have hardware support for floating > point math. In this environment, the conversions from integer to > floating point and the use of floating point math could introduce > significant performance penalties. In this case the user will almost > certainly declare their columns as integer, and often desire integer > math. With the proposed change sqlite will automatically convert the > integer values to floating point and do a floating point divide, then > the user will have to add additional SQL to truncate the floating point > quotient back to an integer value in order to get the same effect as the > current integer division. This extra work will take additional time. __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Chris Hanson wrote: On Nov 2, 2005, at 7:36 AM, [EMAIL PROTECTED] wrote: Am I alone in thinking that a division operator that does different things depending on the declared datatype of a column is an abomination? I don't think you're alone. I do think you're wrong. By declaring column a as INTEGER, and column b as REAL, I am *explicitly* asking for SQLite to treat the values I put in a as INTEGERs and in b as REALs. If I declare column a as NUMERIC, or without a type, I would expect your proposed behavior. And of course if I ask for a value as a particular type - whether through the SQLite API or using a cast expression - I expect it to be treated as that type. I like to think of this as a "have my cake and eat it too" approach: The typing can be as static or as dynamic as I need it to be for a particular application, and I have a full set of tools for making that determination. The database isn't preventing me from doing what I need in *either* direction. To give you an analogy, Objective-C is a dynamic object-oriented extension to C that's modeled on Smalltalk. All message dispatch is dynamic and happens at runtime; however, users can declare variables that point to objects using either an "id" type that means "any kind of object" or by explicitly specifying a class. If you do specify a class, the compiler will do what type checking it can and warn you if it notices something out of the ordinary (or give you an error for a type/class it's never encountered before). This lets you avoid extensive compile-time type checking when you don't want it, but also obtain extensive compile-time type checking when you do want it. This is the kind of behavior I think would be very valuable in SQLite. If I specify a particular affinity, SQLite should adhere to it, because I probably had a reason for specifying it. But if I *didn't* specify an affinity, I probably had a reason for *that too*. They are both valuable options, and I think it would be very valuable for SQLite to let me choose rather than force them one way (always require affinity, e.g. the SQL standard way) or the other (never enforce affinity, e.g. the proposed SQLite way). Does anybody have a real-world example where any of this will actually make a difference, or is this really just an academic argument? It could lead to cascading inaccuracy in situations where developers do expect, plan for, or rely upon traditional integer truncation behavior. One area where this may be the case is in graphics: Many developers are used to working in whole-pixel units with integer truncation on division, and performing more exact calculations as part of SELECT statements may actually give erroneous results. Particularly if they declared columns as having type INTEGER and expect those columns to have such affinity. An example of this might be in locating images that can be trivially scaled (e.g. by sampling every second pixel) to fit a particular display area. The desired-area calculation could occur in the SELECT, and the proposed SQLite behavior could weed out images that would actually fit (e.g. (x/2 < u) is false in SQLite but true in C). I think behaving differently from the SQL standard in this situation -- if an affinity is specified -- would be seen as a bug in SQLite. -- Chris I agree whole heartedly with everything you said Chris. I also think we should consider the use of SQLite in embedded application, many of which don't have hardware support for floating point math. In this environment, the conversions from integer to floating point and the use of floating point math could introduce significant performance penalties. In this case the user will almost certainly declare their columns as integer, and often desire integer math. With the proposed change sqlite will automatically convert the integer values to floating point and do a floating point divide, then the user will have to add additional SQL to truncate the floating point quotient back to an integer value in order to get the same effect as the current integer division. This extra work will take additional time. Dennis Cote
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Brass Tilde wrote: The problem with the grade-school rule is that, assuming the last digit is uniformly distributed, you'll be rounding up 5 times out of 9 and rounding down 4 times out of 9. No, if the last digit is uniformly distributed, then 0 is as likely as any other. You round down on 0, 1, 2, 3 & 4 and round up on 5, 6, 7, 8 & 9. The fact that rounding down on 0 is the same as the unrounded number isn't significant. A quote on rounding rules to limit bias - There are three general rules for rounding: * Rule 1- if the remainder beyond the last digit to be reported is less than 5, drop the last digit. Rounding to one decimal place, the number 5.3467 becomes 5.3. * Rule 2-if the remainder is greater than 5, increase the final digit by 1. The number 5.798 becomes 5.8 if rounding to 1 digit. * Rule 3- To prevent rounding bias, if the remainder is exactly 5, then round the last digit to the closest even number.Thus the number 3.55 (rounded to 1 digit) would be 3.6 (rounding up) and the number 6.450 would round to 6.4 (rounding down)if rounding to 1 decimal. See page 12 in Hurlburt, R. (1994) Comprehending Behavioral Statistics, Brooks/Cole, Pacific Grove, CA.
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Henry Miller wrote: On 11/1/2005 at 21:59 [EMAIL PROTECTED] wrote: John Stanton <[EMAIL PROTECTED]> wrote: Users love such a number system because it is natural and works like the Arithmetic they learned in Grade School. I find the idea of dividing two integers stored in binary form and getting a gratuitous conversion to floating point ugly and potentially very annoying. I admit that it has been 4 decades since I was in grade school, but back in the 60's we were taught that 5/2 is 2.5. Are they teaching something different now? Or have I misunderstood the comment? You are missing the point: What did your grade school teacher tell you about 1/3? What about pi, or e, to name two common numbers that cannot be stored easily. As for 5/2, my grade school teachers taught me that if I round it at all, the answer is 3, NEVER 2. It is only latter in life that I learned about bankers rounding which sometimes is 2, sometimes 3.I have never seen a justification for rounding to 2, except for the bogus answer that it is easy for the computer to do. Thus I conclude whatever the final answer is, sqlite would be wrong if 5/2 is always 2. After reading all the comments I've concluded that the only correct answer is to make all the arithmetic operators replaceable. Let those who care define their own math. When I do math homework (though why you would use sqlite for homework problems I don't know) if the formula is pi*r^2 (area of a circle), if r is 4, my answer should be 8*pi. If I'm doing engineering calculations on the same thing, I want 24.3 (perhaps to a few more decimal places. Bankers may want 5/2 to be 2, or 3, depending on how they round it. I say do whatever you want - anyone who cares about this issue would not be doing math in any database anyway. because the SQL standard requires the wrong answer for their real-world application. It will be a consistent wrong answer, but it will still be wrong. a. The purpose of rounding rules is to avoid drift. b. In school 1/3 is taught to be precisely 1.3' (should be a superscript dot to indicate repeat). It is taught to be 1.33 to two decimal places precision. c. Transcendental/irrational numbers are always a problem regardless of how you store them. Calculating them to the precision you need is a sound practice. d. The Ancient Egyptians had a number system based on fractions, and it was deadly accurate and quite concise. Grade school teachers don't explain the Egyptian method, but good Math teachers do a little further along in the education process.
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
BCD goes back to the early days of computing. The COBOL "COMP3" type was the mainstay of commercial data processing for decades. Commercial computers had BCD decimal arithmetic instructions and could handle accurate tabulations efficiently. These days storage is not so expensive so we have less pressure to store decimal digits two to a byte in BCD. We pay an insignificant penalty for storing a decimal digit per byte. One of our compilers has decimal numbers which are in display format. Its use is intuitive to programmers who can just read and write numbers knowing that the decimal points will be preserved and that precision is assured. It also performs rounding in the classic manner to minimize drift. With such a number type it is very simple for a programmer to have the debit and credit side balance precisely and line up right justified. These numbers made it to an ANSI X standard. JS Fred Williams wrote: No, but I would bet somewhere near the 90%+ range are "commercial" applications, requiring the management of fixed point bankers ("European" or "English") rounding numeric data. Why should the many suffer for the benefit of the few? BCD ain't rocket science. I learned the concept in the Military many years ago. Many database engines are beginning to support the data type in some manner, with few calling it BCD. Fred -Original Message- From: Brass Tilde [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 02, 2005 7:03 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 Well, since you put it that way. May I go one step farther and request that this new Dynamic Type also adhere to "Bankers Rounding" commonly implemented as BCD in other so equipped databases. Please, no. I'll handle rounding in my application, I don't need it on the database. Not all database applications are banking applications. Brad
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
On 11/2/2005 at 16:13 Eric Bohlman wrote: >Henry Miller wrote: >> As for 5/2, my grade school teachers taught me that if I round it at >> all, the answer is 3, NEVER 2. It is only latter in life that I >> learned about bankers rounding which sometimes is 2, sometimes 3. I >> have never seen a justification for rounding to 2, except for the bogus >> answer that it is easy for the computer to do. Thus I conclude >> whatever the final answer is, sqlite would be wrong if 5/2 is always 2. > >The problem with the grade-school rule is that, assuming the last digit >is uniformly distributed, you'll be rounding up 5 times out of 9 and >rounding down 4 times out of 9. That means that if you add up a large >number of rounded numbers, the result will always be larger than the >what you'd get if you added up the unrounded numbers and then rounded >the sum. That introduces a systematic bias in financial and statistical >calculations. Hence bankers rounding, which I mentioned latter.This has nothing to do with the subject, which is not rounding per se, but division in general. That has nothing to do with my point though: 5/2 can never be ALWAYS rounded down to 2 correctly. You would be correct to always round up to 3 - by my grade school math. You would be correct to use one of the bankers rounding schemes which would round up or down. Maybe I should change the problem to 5/3. SQLITE will currently round this down to 1. This is not rounding, this is a floor function. Floor functions have their use, but they are not rounding, and should not be the default when doing division and turning the result into an integer. The question is since sqlite is clearly doing the wrong thing when dividing two integers, would be we ok to just turn the result into a real, or must we implement real rounding. (with some way to select from the different bankers rounding, or simple rounding)If a floor function is useful, sqlite could provide that too, but that is a different discussion (which I would likely argue against unless someone has a good argument for).
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
On Nov 2, 2005, at 7:36 AM, [EMAIL PROTECTED] wrote: Am I alone in thinking that a division operator that does different things depending on the declared datatype of a column is an abomination? I don't think you're alone. I do think you're wrong. By declaring column a as INTEGER, and column b as REAL, I am *explicitly* asking for SQLite to treat the values I put in a as INTEGERs and in b as REALs. If I declare column a as NUMERIC, or without a type, I would expect your proposed behavior. And of course if I ask for a value as a particular type - whether through the SQLite API or using a cast expression - I expect it to be treated as that type. I like to think of this as a "have my cake and eat it too" approach: The typing can be as static or as dynamic as I need it to be for a particular application, and I have a full set of tools for making that determination. The database isn't preventing me from doing what I need in *either* direction. To give you an analogy, Objective-C is a dynamic object-oriented extension to C that's modeled on Smalltalk. All message dispatch is dynamic and happens at runtime; however, users can declare variables that point to objects using either an "id" type that means "any kind of object" or by explicitly specifying a class. If you do specify a class, the compiler will do what type checking it can and warn you if it notices something out of the ordinary (or give you an error for a type/class it's never encountered before). This lets you avoid extensive compile-time type checking when you don't want it, but also obtain extensive compile-time type checking when you do want it. This is the kind of behavior I think would be very valuable in SQLite. If I specify a particular affinity, SQLite should adhere to it, because I probably had a reason for specifying it. But if I *didn't* specify an affinity, I probably had a reason for *that too*. They are both valuable options, and I think it would be very valuable for SQLite to let me choose rather than force them one way (always require affinity, e.g. the SQL standard way) or the other (never enforce affinity, e.g. the proposed SQLite way). Does anybody have a real-world example where any of this will actually make a difference, or is this really just an academic argument? It could lead to cascading inaccuracy in situations where developers do expect, plan for, or rely upon traditional integer truncation behavior. One area where this may be the case is in graphics: Many developers are used to working in whole-pixel units with integer truncation on division, and performing more exact calculations as part of SELECT statements may actually give erroneous results. Particularly if they declared columns as having type INTEGER and expect those columns to have such affinity. An example of this might be in locating images that can be trivially scaled (e.g. by sampling every second pixel) to fit a particular display area. The desired-area calculation could occur in the SELECT, and the proposed SQLite behavior could weed out images that would actually fit (e.g. (x/2 < u) is false in SQLite but true in C). I think behaving differently from the SQL standard in this situation -- if an affinity is specified -- would be seen as a bug in SQLite. -- Chris
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Brass Tilde wrote: The problem with the grade-school rule is that, assuming the last digit is uniformly distributed, you'll be rounding up 5 times out of 9 and rounding down 4 times out of 9. No, if the last digit is uniformly distributed, then 0 is as likely as any other. You round down on 0, 1, 2, 3 & 4 and round up on 5, 6, 7, 8 & 9. The fact that rounding down on 0 is the same as the unrounded number isn't significant. Er, uh, I think it is. The average amount added is greater than the average amount subtracted. Suppose you start with twenty numbers: 0.0, 0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 1.0, 1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 1.8, 1.9 which sum to 19. (Pair 0.1 and 1.9, 0.2 and 1.8, etc., and you have 9 sums of 2 plus the 0.0 and 1.0 left over) If you use the rule "x.5 rounds to x+1" rule the sum of the rounds is 20 because round(0.5)+round(1.5) = 3. The rule "round x.5 to the nearest even" fixes this. Gerry -- -- Gerry Snyder American Iris Society Director, Symposium Chair in warm, winterless Los Angeles -- USDA zone 9b, Sunset 18-19
RE: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
> The problem with the grade-school rule is that, assuming the > last digit is uniformly distributed, you'll be rounding up 5 > times out of 9 and rounding down 4 times out of 9. No, if the last digit is uniformly distributed, then 0 is as likely as any other. You round down on 0, 1, 2, 3 & 4 and round up on 5, 6, 7, 8 & 9. The fact that rounding down on 0 is the same as the unrounded number isn't significant.
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Henry Miller wrote: As for 5/2, my grade school teachers taught me that if I round it at all, the answer is 3, NEVER 2. It is only latter in life that I learned about bankers rounding which sometimes is 2, sometimes 3.I have never seen a justification for rounding to 2, except for the bogus answer that it is easy for the computer to do. Thus I conclude whatever the final answer is, sqlite would be wrong if 5/2 is always 2. The problem with the grade-school rule is that, assuming the last digit is uniformly distributed, you'll be rounding up 5 times out of 9 and rounding down 4 times out of 9. That means that if you add up a large number of rounded numbers, the result will always be larger than the what you'd get if you added up the unrounded numbers and then rounded the sum. That introduces a systematic bias in financial and statistical calculations.
RE: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
There is clearly no 1 correct answer. So instead of arguing the point over and over, why don't the people who object simply apply the proposed change and report back what issues your application has? Let's see how many people are actually using this functionality, what breaks and weigh the decision on that. -Original Message- From: Clark Christensen [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 02, 2005 1:19 PM To: SQLite List Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 > Was this intended as part of the division discussion? > If so, the original statement was: > > Am I alone in thinking that a division operator that does different > > things depending on the declared datatype of a column is an > > abomination? Yes. It was in answer to (paraphrased), "what results do other DBs return for the SQL under consideration?" Looks like I forgot to include the question with the answer. Sorry about that :-) -Clark - Original Message From: Jay Sprenkle <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org; Clark Christensen <[EMAIL PROTECTED]> Sent: Wednesday, November 02, 2005 9:29:13 AM Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 On 11/2/05, Clark Christensen <[EMAIL PROTECTED]> wrote: > > > - Original Message > From: [EMAIL PROTECTED] > To: sqlite-users@sqlite.org > Sent: Wednesday, November 02, 2005 07:36:58 > Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 > > Consider the following SQL: > >CREATE TABLE t1(a INTEGER, b REAL); >INSERT INTO t1 VALUES(5,5); >SELECT a/2, b/2 FROM t1; > > D. Richard Hipp <[EMAIL PROTECTED]> > --- > > Connected to: > Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the > Partitioning option JServer Release 8.1.6.0.0 - Production > > SQL> create table t1 (a integer, b real); > > Table created. > > SQL> insert into t1 values (5,5); > > 1 row created. > > SQL> select a/2, b/2 from t1; > > A/2B/2 > -- -- > 2.52.5 > Was this intended as part of the division discussion? If so, the original statement was: > Am I alone in thinking that a division operator that does different > things depending on the declared datatype of a column is an > abomination? This shows that the engine does do different things based on the data type. It changed the result type so it would not lose precision. If you require all divisions to be done the same way then you must have only one numerical type. Floating point.
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
> Was this intended as part of the division discussion? > If so, the original statement was: > > Am I alone in thinking that a division operator that does > > different things depending on the declared datatype of a > > column is an abomination? Yes. It was in answer to (paraphrased), "what results do other DBs return for the SQL under consideration?" Looks like I forgot to include the question with the answer. Sorry about that :-) -Clark - Original Message From: Jay Sprenkle <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org; Clark Christensen <[EMAIL PROTECTED]> Sent: Wednesday, November 02, 2005 9:29:13 AM Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 On 11/2/05, Clark Christensen <[EMAIL PROTECTED]> wrote: > > > - Original Message > From: [EMAIL PROTECTED] > To: sqlite-users@sqlite.org > Sent: Wednesday, November 02, 2005 07:36:58 > Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 > > Consider the following SQL: > >CREATE TABLE t1(a INTEGER, b REAL); >INSERT INTO t1 VALUES(5,5); >SELECT a/2, b/2 FROM t1; > > D. Richard Hipp <[EMAIL PROTECTED]> > --- > > Connected to: > Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production > With the Partitioning option > JServer Release 8.1.6.0.0 - Production > > SQL> create table t1 (a integer, b real); > > Table created. > > SQL> insert into t1 values (5,5); > > 1 row created. > > SQL> select a/2, b/2 from t1; > > A/2B/2 > -- -- > 2.52.5 > Was this intended as part of the division discussion? If so, the original statement was: > Am I alone in thinking that a division operator that does > different things depending on the declared datatype of a > column is an abomination? This shows that the engine does do different things based on the data type. It changed the result type so it would not lose precision. If you require all divisions to be done the same way then you must have only one numerical type. Floating point.
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
> Please let me restate the proposed change: I desire to change > SQLite so that it no longer distinguishes between integer and > real. Put this way, this seems to be a good idea, provided that there isn't a performance penalty. Most of the numbers I store in SQLite databases are small integers, and it would be a shame if things were slower as a result. There is a problem, though, if you use SQLite from a language with a different form of manifestant types. In current Python versions, for example, integers and floats are a different type, and integer division yields integers (but the Python developers plan to change this) -- and there is no separate division operator for floats. Currently, the Python type is preserved when writing an integer or float to the database, after your change, it won't. The fix on the Python side is not particularly pleasant: you must add calls to the float function before you can use a value returned from the database in a division operation. (Ruby has a similar problem, it seem. Most Lisps and Perl don't. Statically typed languages are unaffected by the change.)
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
On 11/2/05, Clark Christensen <[EMAIL PROTECTED]> wrote: > > > - Original Message > From: [EMAIL PROTECTED] > To: sqlite-users@sqlite.org > Sent: Wednesday, November 02, 2005 07:36:58 > Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 > > Consider the following SQL: > >CREATE TABLE t1(a INTEGER, b REAL); >INSERT INTO t1 VALUES(5,5); >SELECT a/2, b/2 FROM t1; > > D. Richard Hipp <[EMAIL PROTECTED]> > --- > > Connected to: > Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production > With the Partitioning option > JServer Release 8.1.6.0.0 - Production > > SQL> create table t1 (a integer, b real); > > Table created. > > SQL> insert into t1 values (5,5); > > 1 row created. > > SQL> select a/2, b/2 from t1; > > A/2B/2 > -- -- > 2.52.5 > Was this intended as part of the division discussion? If so, the original statement was: > Am I alone in thinking that a division operator that does > different things depending on the declared datatype of a > column is an abomination? This shows that the engine does do different things based on the data type. It changed the result type so it would not lose precision. If you require all divisions to be done the same way then you must have only one numerical type. Floating point.
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
- Original Message From: [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Wednesday, November 02, 2005 07:36:58 Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 Consider the following SQL: CREATE TABLE t1(a INTEGER, b REAL); INSERT INTO t1 VALUES(5,5); SELECT a/2, b/2 FROM t1; D. Richard Hipp <[EMAIL PROTECTED]> --- Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production SQL> create table t1 (a integer, b real); Table created. SQL> insert into t1 values (5,5); 1 row created. SQL> select a/2, b/2 from t1; A/2B/2 -- -- 2.52.5 SQL>
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
On 11/2/05, Brass Tilde <[EMAIL PROTECTED]> wrote: > > > floating point operations. If you're creating a very large database > > > why should you pay for 80 bits (an IEEE float) of storage when 8 > will > > > do just fine? > > > > 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. > > 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 > deal with. Whether those 8 bytes represent a generic number, a real > value, an integer, a floating point value, date or text is for your > application to determine, not the DB engine. At least from SQLite's > perspective. 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.
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
> > floating point operations. If you're creating a very large database > > why should you pay for 80 bits (an IEEE float) of storage when 8 will > > do just fine? > > 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. >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 deal with. Whether those 8 bytes represent a generic number, a real value, an integer, a floating point value, date or text is for your application to determine, not the DB engine. At least from SQLite's perspective.
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
> If it should do division the same way for every operation then you must > require the result to be able to represent every possible division > result. IE the result must be floating point. If you're writing the inner > loop for a quake engine there are very good reasons for not wanting to pay I'm not writing a quake look in SQL. No one with any sense would. All this change does is make SQLite even more data type agnostic than it was in the previous version. > floating point operations. If you're creating a very large database why should > you pay for 80 bits (an IEEE float) of storage when 8 will do just fine? 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. Brad
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Am I alone in thinking that a division operator that does different things depending on the declared datatype of a column is an abomination? Absolutely not. Of course he won't be alone, doesn't make him ( or you ) correct or in fact incorrect. I think allowing mathematical operators built into databases in the first place is an absurdity, but many will disagree with me ... and agree with me. So what? I'd much rather used a stored procedure for implementing my own tightly coupled operations that rely on database providers to continue to expand their products into a realm where subjectivity plays such a big part. However, the mere thought that someone would use an operation where two declared Integer values used in that operation could create a Real value without explicity declaring that you are using Real operators to do it makes me shudder. Operators are always, without fail, contextual, when they exist for multiple types. Whether you keep the same name or not for the operator is an issue that is best addressed by a standards body, IMHO. Use a hammer for hammering, and a screwdriver for screwing ( screws ). Dave.
RE: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
I don't mean to muddy the water here. It has been a very long time since I first used SQL, but I seem to remember one of the original selling points was that it was "user friendly" and therefore "Type-less" so even a "Manager" could write "English like queries" and not have to resort to dealing with the "programming department." :-) I think the underling discussion should remain on the method of storage and retrieval and insure that the change does not reduce accuracy or unduly complicate data management. Even though I have championed the "Standard." My contention is, if done properly, the Standard will be preserved. Fred > -Original Message- > From: Drew, Stephen [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 02, 2005 10:01 AM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 > > > I think this is the best solution in the real world, although I agree > with DRH that different division operators are a bad thing. > > Surely if somebody wants the result as an integer, they can use > sqlite3_column_int() to pull the result out? Or even write a function > to do whatever rounding they see fit. > > Oracle, however, returns the following: > > >CREATE TABLE test_table1(a INTEGER, b NUMBER(21,18)); > >INSERT INTO test_table1 VALUES(5,5); > >SELECT a/2, b/2 FROM test_table1; > > > > 2.5 | 2.5 > > > > -Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: 02 November 2005 15:37 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 > > Consider the following SQL: > >CREATE TABLE t1(a INTEGER, b REAL); >INSERT INTO t1 VALUES(5,5); >SELECT a/2, b/2 FROM t1; > > From the above SQL, SQLite version 3.2.7 and earlier will return > >2|2 > > If my proposed changes for 3.3.0 go in, then the result will be: > >2.5|2.5 > > If I understand what most people are saying, the SQL standard > says that > the result should be: > >2|2.5 > > Does this correctly summarize the situation? > > Do other SQL database engines consistently return the 3rd case? > > Am I alone in thinking that a division operator that does different > things depending on the declared datatype of a column is an > abomination? > > Does anybody have a real-world example where any of this will actually > make a difference, or is this really just an academic argument? > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > >
RE: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Agreed, BUT it is the Standard. I do not question the concept. Just make certain it is well documented in the "Gray Area" and user configurable. That way old code survives and new code will have the OPTION of not conforming to the Standard. Even now I am not looking forward to "upgrading" my old stuff from 2.x.x to 3.x.x because of the forward looking 3.x.x "enhancements". Not disparaging the product, just hate paying the price. I still like BCD even if the Marines don't. After all everybody knows they are tough and like eating bugs, producing columns of numbers that don't ad up with the total, and doing other tough things :-) Fred FTB2(SS) long ago > -Original Message- > From: Brass Tilde [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 02, 2005 9:44 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 > > > > Am I alone in thinking that a division operator that does > > different things depending on the declared datatype of a > > column is an abomination? > > Absolutely not.
RE: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
DB2 gives this: CREATE TABLE t1(a INTEGER, b REAL) INSERT INTO t1 VALUES(5,5) SELECT a/2, b/2 FROM t1 1 2 --- 2 +2.50E+000 1 record(s) selected. -Original Message- From: Rob Lohman [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 02, 2005 11:07 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 A quick test here on MSSQL & Oracle: Microsoft SQL 2000 & SQL 2005 (beta): create table MATHTEST ( CINT int null, CDEC decimal null, CDPREC double precision null, CFLOAT floatnull, CNUM numeric null, CREALreal null ) go insert into MATHTEST (CINT, CDEC, CDPREC, CFLOAT, CNUM, CREAL) values (5, 5, 5, 5, 5, 5); go select CINT/2, CDEC/2, CDPREC/2, CFLOAT/2, CNUM/2, CREAL/2 from MATHTEST go 2 2.50 2.5 2.5 2.50 2.5 - Oracle 8i2: create table MATHTEST ( CDEC DEC, CDECIMAL DECIMAL, CDPREC DOUBLE PRECISION, CFLOAT FLOAT, CINT INT, CNUM NUMBER, CREALREAL ); insert into MATHTEST (CDEC, CDECIMAL, CDPREC, CFLOAT, CINT, CNUM, CREAL) values (5, 5, 5, 5, 5, 5, 5); select CDEC/2, CDECIMAL/2, CDPREC/2, CFLOAT/2, CINT/2, CNUM/2, CREAL/2 from MATHTEST; CDEC/2 CDECIMAL/2 CDPREC/2 CFLOAT/2 CINT/2 CNUM/2 CREAL/2 -- -- -- -- -- -- -- 2,52,52,52,52,52,5 2,5 So it seems these two have different opinions on this as well. Rob - Original Message - From: <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Sent: Wednesday, November 02, 2005 4:36 PM Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 Consider the following SQL: CREATE TABLE t1(a INTEGER, b REAL); INSERT INTO t1 VALUES(5,5); SELECT a/2, b/2 FROM t1; >From the above SQL, SQLite version 3.2.7 and earlier will return 2|2 If my proposed changes for 3.3.0 go in, then the result will be: 2.5|2.5 If I understand what most people are saying, the SQL standard says that the result should be: 2|2.5 Does this correctly summarize the situation? Do other SQL database engines consistently return the 3rd case? Am I alone in thinking that a division operator that does different things depending on the declared datatype of a column is an abomination? Does anybody have a real-world example where any of this will actually make a difference, or is this really just an academic argument? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
A quick test here on MSSQL & Oracle: Microsoft SQL 2000 & SQL 2005 (beta): create table MATHTEST ( CINT int null, CDEC decimal null, CDPREC double precision null, CFLOAT floatnull, CNUM numeric null, CREALreal null ) go insert into MATHTEST (CINT, CDEC, CDPREC, CFLOAT, CNUM, CREAL) values (5, 5, 5, 5, 5, 5); go select CINT/2, CDEC/2, CDPREC/2, CFLOAT/2, CNUM/2, CREAL/2 from MATHTEST go 2 2.50 2.5 2.5 2.50 2.5 - Oracle 8i2: create table MATHTEST ( CDEC DEC, CDECIMAL DECIMAL, CDPREC DOUBLE PRECISION, CFLOAT FLOAT, CINT INT, CNUM NUMBER, CREALREAL ); insert into MATHTEST (CDEC, CDECIMAL, CDPREC, CFLOAT, CINT, CNUM, CREAL) values (5, 5, 5, 5, 5, 5, 5); select CDEC/2, CDECIMAL/2, CDPREC/2, CFLOAT/2, CINT/2, CNUM/2, CREAL/2 from MATHTEST; CDEC/2 CDECIMAL/2 CDPREC/2 CFLOAT/2 CINT/2 CNUM/2CREAL/2 -- -- -- -- -- -- -- 2,52,52,52,52,52,52,5 So it seems these two have different opinions on this as well. Rob - Original Message - From: <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Sent: Wednesday, November 02, 2005 4:36 PM Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 Consider the following SQL: CREATE TABLE t1(a INTEGER, b REAL); INSERT INTO t1 VALUES(5,5); SELECT a/2, b/2 FROM t1; From the above SQL, SQLite version 3.2.7 and earlier will return 2|2 If my proposed changes for 3.3.0 go in, then the result will be: 2.5|2.5 If I understand what most people are saying, the SQL standard says that the result should be: 2|2.5 Does this correctly summarize the situation? Do other SQL database engines consistently return the 3rd case? Am I alone in thinking that a division operator that does different things depending on the declared datatype of a column is an abomination? Does anybody have a real-world example where any of this will actually make a difference, or is this really just an academic argument? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
On 11/2/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: >2.5|2.5 MySQL 5 and MS Access 2003 >2|2.5 PostgreSQL 8 and FireBird 1.5 -- Nemanja Corlija <[EMAIL PROTECTED]>
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
On 11/2/05, Brass Tilde <[EMAIL PROTECTED]> wrote: > > Am I alone in thinking that a division operator that does > > different things depending on the declared datatype of a > > column is an abomination? > > Absolutely not. If it should do division the same way for every operation then you must require the result to be able to represent every possible division result. IE the result must be floating point. If you're writing the inner loop for a quake engine there are very good reasons for not wanting to pay the time penalty for floating point operations. If you're creating a very large database why should you pay for 80 bits (an IEEE float) of storage when 8 will do just fine? There are very good reasons for these things. Just because they don't apply to you doesn't make them an "abomination".
RE: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
I think this is the best solution in the real world, although I agree with DRH that different division operators are a bad thing. Surely if somebody wants the result as an integer, they can use sqlite3_column_int() to pull the result out? Or even write a function to do whatever rounding they see fit. Oracle, however, returns the following: CREATE TABLE test_table1(a INTEGER, b NUMBER(21,18)); INSERT INTO test_table1 VALUES(5,5); SELECT a/2, b/2 FROM test_table1; 2.5 | 2.5 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 02 November 2005 15:37 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 Consider the following SQL: CREATE TABLE t1(a INTEGER, b REAL); INSERT INTO t1 VALUES(5,5); SELECT a/2, b/2 FROM t1; >From the above SQL, SQLite version 3.2.7 and earlier will return 2|2 If my proposed changes for 3.3.0 go in, then the result will be: 2.5|2.5 If I understand what most people are saying, the SQL standard says that the result should be: 2|2.5 Does this correctly summarize the situation? Do other SQL database engines consistently return the 3rd case? Am I alone in thinking that a division operator that does different things depending on the declared datatype of a column is an abomination? Does anybody have a real-world example where any of this will actually make a difference, or is this really just an academic argument? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
> Am I alone in thinking that a division operator that does > different things depending on the declared datatype of a > column is an abomination? Absolutely not.
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
On Nov 2, 2005, at 9:36 AM, [EMAIL PROTECTED] wrote: Consider the following SQL: CREATE TABLE t1(a INTEGER, b REAL); INSERT INTO t1 VALUES(5,5); SELECT a/2, b/2 FROM t1; From the above SQL, SQLite version 3.2.7 and earlier will return 2|2 If my proposed changes for 3.3.0 go in, then the result will be: 2.5|2.5 If I understand what most people are saying, the SQL standard says that the result should be: 2|2.5 Does this correctly summarize the situation? very clear, very well summarized. And, my vote is FOR your proposed change. __END__ To extend what Darren Duncan said a while back, perhaps a second div operator (\) could be considered, so a/2 = 2.5 a\2 = 2 b/2 = 2.5 b\2 = 2.5 but why? The above can already be achieved. So, keep the lite burning brite in SQL. -- Puneet Kishor
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Consider the following SQL: CREATE TABLE t1(a INTEGER, b REAL); INSERT INTO t1 VALUES(5,5); SELECT a/2, b/2 FROM t1; From the above SQL, SQLite version 3.2.7 and earlier will return 2|2 If my proposed changes for 3.3.0 go in, then the result will be: 2.5|2.5 If I understand what most people are saying, the SQL standard says that the result should be: 2|2.5 Does this correctly summarize the situation? Do other SQL database engines consistently return the 3rd case? Am I alone in thinking that a division operator that does different things depending on the declared datatype of a column is an abomination? Does anybody have a real-world example where any of this will actually make a difference, or is this really just an academic argument? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
[EMAIL PROTECTED] wrote: Joe Wilson <[EMAIL PROTECTED]> wrote: I've found a potential problem - round() is not a good substitute for the old integer truncation. Consider the previous Sqlite3 behavior: sqlite> select 15/8; 1 sqlite> select round(15.0/8.0); 2 Can you recommend or provide a new function that performs correct integer trunction? I think the current code in CVS allows you to do this with CAST( 15.0/8.0 AS integer ) But I admit I need to test that case more thoroughly. -- D. Richard Hipp <[EMAIL PROTECTED]> Assuming the bitwise operators always work with integers as they do now (I don't think it makes much sense to OR two floating point numbers together), which is to truncate real arguments to integers before performing the operation, you can truncate any real value to an integer by ORing it with 0, or double complementing it like this. select (15.0/8.0)|0; select ~~(15.0/8.0); both of these expressions return the truncated value, 1. This also avoids the conversion of the values to text that round() does. HTH Dennis Cote
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
> No, but I would bet somewhere near the 90%+ range are "commercial" > applications, requiring the management of fixed point bankers > ("European" or "English") rounding numeric data. I've been writing commercial applications for about 20 years now, including 10 years on payroll applications for the Marine Corps and insurance apps for civilians, and I've never used "banking rounding". > Why should the many suffer for the benefit of the few? Well, first prove it's a many to few relationship, a fact not currently in evidence. Second, why should the database care at all, when there are plenty of libraries out there that can handle it just fine. > BCD ain't rocket science. I learned the concept in the Military many years ago. If by BCD you mean Binary Coded Decimal, or Cobol's Comp-3, (which is what *my* military experience remembers it as), I fail to see any connection, other than "they did it that way back when...", between the storage format and a method for rounding. > Many database engines are beginning to support the data type in some manner, > with few calling it BCD. A fact that is completely irrelevant when discussing mathematical operations on those data types. Brad
Re: [sqlite] R: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Zibetti Paolo <[EMAIL PROTECTED]> wrote: > > Insert into foo values(5.34); > Insert into foo values(3.0); > > Table foo will contain two rows that both contain a real-type number, so, to > read the values back from the DB, I can always use sqlite3_column_double(). > With your proposed change it appears to me that for each row I will have to > first test for the type of the field and then decide whether to use > sqlite3_column_double() or sqlite3_column_int(). > No. SQLite has always allowed values to be extracted in any datatype you want. You can use sqlite3_column_double() to retrieve an integer value and it will convert the integer to a double for you automatically. Likewise, you can do sqlite3_column_int64() on a double and it will automatically do the conversion. Or you can do sqlite3_column_text() and it will convert the value to a string and return the string. This has always been the case and it will not change. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
On 11/1/2005 at 21:59 [EMAIL PROTECTED] wrote: >John Stanton <[EMAIL PROTECTED]> wrote: >> >> Users love such a number system because it is natural and works like the >> Arithmetic they learned in Grade School. >> >> I find the idea of dividing two integers stored in binary form and >> getting a gratuitous conversion to floating point ugly and potentially >> very annoying. > >I admit that it has been 4 decades since I was in grade school, >but back in the 60's we were taught that 5/2 is 2.5. Are they >teaching something different now? Or have I misunderstood the >comment? You are missing the point: What did your grade school teacher tell you about 1/3? What about pi, or e, to name two common numbers that cannot be stored easily. As for 5/2, my grade school teachers taught me that if I round it at all, the answer is 3, NEVER 2. It is only latter in life that I learned about bankers rounding which sometimes is 2, sometimes 3.I have never seen a justification for rounding to 2, except for the bogus answer that it is easy for the computer to do. Thus I conclude whatever the final answer is, sqlite would be wrong if 5/2 is always 2. After reading all the comments I've concluded that the only correct answer is to make all the arithmetic operators replaceable. Let those who care define their own math. When I do math homework (though why you would use sqlite for homework problems I don't know) if the formula is pi*r^2 (area of a circle), if r is 4, my answer should be 8*pi. If I'm doing engineering calculations on the same thing, I want 24.3 (perhaps to a few more decimal places. Bankers may want 5/2 to be 2, or 3, depending on how they round it. I say do whatever you want - anyone who cares about this issue would not be doing math in any database anyway. because the SQL standard requires the wrong answer for their real-world application. It will be a consistent wrong answer, but it will still be wrong.
RE: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
No, but I would bet somewhere near the 90%+ range are "commercial" applications, requiring the management of fixed point bankers ("European" or "English") rounding numeric data. Why should the many suffer for the benefit of the few? BCD ain't rocket science. I learned the concept in the Military many years ago. Many database engines are beginning to support the data type in some manner, with few calling it BCD. Fred > -Original Message- > From: Brass Tilde [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 02, 2005 7:03 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 > > > > Well, since you put it that way. May I go one step farther and > request > > that this new Dynamic Type also adhere to "Bankers > Rounding" commonly > > implemented as BCD in other so equipped databases. > > Please, no. I'll handle rounding in my application, I don't > need it on > the database. Not all database applications are banking applications. > > Brad >
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Well, since you put it that way. May I go one step farther and request that this new Dynamic Type also adhere to "Bankers Rounding" commonly implemented as BCD in other so equipped databases. Please, no. I'll handle rounding in my application, I don't need it on the database. Not all database applications are banking applications. A type for every occasion perhaps? ;-) Dave.
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
1/3. Store that mysql ;-) Or what about PI? Or the square root of 10? Not all reals can be represented as fractions either. Surd-tastic. Whilst computers are finite state machines such approximations are the most reasonable compromise between accuracy and performance. Or should that be practicality and stupidity? Changing to some super-duper as yet undefined and nebulous system is an entertaining thought, but impractical. Besides, discrete integer based models are perfectly valid and require representation in databases. I believe however that reducing the boundaries between integer and real might have its real world drawbacks. Seems to me a flexible 'number' format might be more appropriate. Worrying about number formats is something thats really only truly important when you use mathematical operations in the virtual machine. If one couldn't distinguish between the outcome of basic operations based on its type, then one needs to distinguish between them by name. Regards Dave.
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
> Well, since you put it that way. May I go one step farther and request > that this new Dynamic Type also adhere to "Bankers Rounding" commonly > implemented as BCD in other so equipped databases. Please, no. I'll handle rounding in my application, I don't need it on the database. Not all database applications are banking applications. Brad
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Arjen Markus wrote: Fred Williams wrote: Well, since you put it that way. May I go one step farther and request that this new Dynamic Type also adhere to "Bankers Rounding" commonly implemented as BCD in other so equipped databases. For years I have spent countless hours testing and sweating rounding and comparisons using "Float" (Real?) fields because (IMHO) the database designers were scientific mathematicians and the world of users were 99%^ business types. So, spare me further anguish... :-) Hm, being an engineer (and not having too much experience with database, I immediately admit), I have always thought it was the other way around: administrative and financial people designing database systems :). Being an engineer and a software designer, I can assure you that the computer scientists, not practical engineers or accountants, get to design software and inflict floating point numbers on non-scientific users. I rather like the idea Richard is trying to get across. Too often people have been surprised by such awkward behaviour 5/2 becoming 2 or 5.1*5.1 turning out to be 26.00999 instead of 26.01 and the like. As for banker's rounding: if I understand it correctly, there are at least two slightly different systems - an American and a European one. That problem should be solved too, if you are going to introduce such behaviour. Regards, Arjen
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
[EMAIL PROTECTED] wrote: John Stanton <[EMAIL PROTECTED]> wrote: Users love such a number system because it is natural and works like the Arithmetic they learned in Grade School. I find the idea of dividing two integers stored in binary form and getting a gratuitous conversion to floating point ugly and potentially very annoying. I admit that it has been 4 decades since I was in grade school, but back in the 60's we were taught that 5/2 is 2.5. Are they teaching something different now? Or have I misunderstood the comment? You misunderstood the comment. A schoolchild expects to see 5/2 give exactly 2.5, not an approximate representation of 2.5 in a floating point number. The schoolchild also expects to see rounding according to the rules and for the debits to agree with the credits. I question why we have to use binary integers based on the word size of particular generations of computers when we are storing data. We have escaped from 80 column cards and 24X80 screens. Why are we still using number formats based on the underlying machines rather than on what we really use in our lives. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Fred, you are right on the money. For some reason generations of software has been inflicted on the commercial user with numbers stored as floating point, a complete design failure. The consequent rounding vagaries make the implementation of simple accounting or other fundamental arithmetic applications a misery. Way back the problem was efficiently addressed with BCD integer arithmetic and storage, but it is a lesson destined to be forgotten and perpetually re-invented. JS Fred Williams wrote: Well, since you put it that way. May I go one step farther and request that this new Dynamic Type also adhere to "Bankers Rounding" commonly implemented as BCD in other so equipped databases. For years I have spent countless hours testing and sweating rounding and comparisons using "Float" (Real?) fields because (IMHO) the database designers were scientific mathematicians and the world of users were 99%^ business types. So, spare me further anguish... :-) Fred -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 01, 2005 12:55 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 ... Please let me restate the proposed change: I desire to change SQLite so that it no longer distinguishes between integer and real. The two concepts are merged into a unified "numeric" type. Just as the same string can be represented as either UTF-8 or UTF-16 (or sometimes ISO-8859) so too can a number be represented as integer or real. But regardless of the particular representation chosen at any particular moment, the "type" of the data is always "numeric". There is no "integer". There is no "real". There is only "numeric". And because all number values are of the same type, they need to always be treated in the same way. A division should return a result that includes the fractional part, if there is one, regardless of the particular representation of the operands. This a simplification designed to make your life easier. No longer are there different rules to apply depending on whether a value or a column is "integer" or "real". Everything always works the same way. The goal is to have no special cases. ...
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
I find your logic clear and rational, but feel that you do not carry it through to its logical conclusion. If you are avoiding rigid typing and fixed size records to finally escape from the Hollerith card mindset which has afflicted IT for a century, then why embrace fixed size binary integers and the fixed size compromise which is floating point? Surely we can move ahead and store numbers accurately and without size constraints. We are certainly no longer constrained by word sizes on slow machines and simple ALUs. JS [EMAIL PROTECTED] wrote: All of the arguments against my proposed changes are basically of the form that "this is not what the SQL standard says". They are theoretical arguments based on a world-view that holds that strong-typing is good and that it is the duty of database systems to enforce types. I do not hold to that world view. SQLite seeks a better way. You may or may not agree that the SQLite way is better (I happen to think that it is, of course) but that is beside the point. What I want to know is whether or not my proposed changes will cause serious hardship to any current or future SQLite users. Please let me restate the proposed change: I desire to change SQLite so that it no longer distinguishes between integer and real. The two concepts are merged into a unified "numeric" type. Just as the same string can be represented as either UTF-8 or UTF-16 (or sometimes ISO-8859) so too can a number be represented as integer or real. But regardless of the particular representation chosen at any particular moment, the "type" of the data is always "numeric". There is no "integer". There is no "real". There is only "numeric". And because all number values are of the same type, they need to always be treated in the same way. A division should return a result that includes the fractional part, if there is one, regardless of the particular representation of the operands. This a simplification designed to make your life easier. No longer are there different rules to apply depending on whether a value or a column is "integer" or "real". Everything always works the same way. The goal is to have no special cases. I'm trying do all this while at the same time maintaining reasonable compatibility with other SQL database engines. SQLite will never be 100% compatible with other database engines. But on the other hand, I dare say you cannot find any two other SQL database engines that are 100% compatible with each other. Most claim SQL compatibility, but you can always find a corner case where two different engines will give divergent results. So even though SQLite is not 100% compatible, neither is any other database engine. With the above clarification, I will restate the question: Suppose SQLite were to merge "integer" and "real" into a single datatype "numeric" that always worked the same way. Does anybody know of a (real) usage example where this would cause an actual hardship on developers? Are there any examples of things that you can do with separate "real" and "integer" types that you cannot do with a unified "numeric" type? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] R: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Given what I've read so far, you shouldn't have to change any of your code. Conceptually speaking, all numbers would be stored as reals internally, though actually some would be stored as integers if possible for efficiency. When you invoke a SQLite accessor function such as double() or int() then the value you asked for will be coerced into the requested data type, and then returned that way. This is how it would have to work, considering that external C code actually considers those types to be different machine native formats. No C code changes should be necessary. Only some SQL code may need changing. -- Darren Duncan At 10:10 AM +0100 11/2/05, Zibetti Paolo wrote: Most of the discussion so far was about proposed change number 2, on the contrary I'm concerned about proposed change number 1. Does this mean that a number that can be stored as an integer will be stored as an integer and, thus, I will need to read it back as an integer ? Here is what I mean: with SQLIte 3.2.x, if I run these two statements Insert into foo values(5.34); Insert into foo values(3.0); Table foo will contain two rows that both contain a real-type number, so, to read the values back from the DB, I can always use sqlite3_column_double(). With your proposed change it appears to me that for each row I will have to first test for the type of the field and then decide whether to use sqlite3_column_double() or sqlite3_column_int(). Is this correct ? If so, changes will be required to the existing code to port it to Sqlite 3.3.x. Bye -Messaggio originale- Da: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Inviato:martedì 1 novembre 2005 15.00 A: sqlite-users@sqlite.org Oggetto:[sqlite] Proposed 3.3.0 changes. Was: 5/2==2 (1) Floating point values are *always* converted into integers if it is possible to do so without loss of information.
[sqlite] R: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Most of the discussion so far was about proposed change number 2, on the contrary I'm concerned about proposed change number 1. Does this mean that a number that can be stored as an integer will be stored as an integer and, thus, I will need to read it back as an integer ? Here is what I mean: with SQLIte 3.2.x, if I run these two statements Insert into foo values(5.34); Insert into foo values(3.0); Table foo will contain two rows that both contain a real-type number, so, to read the values back from the DB, I can always use sqlite3_column_double(). With your proposed change it appears to me that for each row I will have to first test for the type of the field and then decide whether to use sqlite3_column_double() or sqlite3_column_int(). Is this correct ? If so, changes will be required to the existing code to port it to Sqlite 3.3.x. Bye -Messaggio originale- Da: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Inviato:martedì 1 novembre 2005 15.00 A: sqlite-users@sqlite.org Oggetto:[sqlite] Proposed 3.3.0 changes. Was: 5/2==2 I am proposing to make the changes outlined below in SQLite version 3.3.0 and I am wondering if these changes will cause any severe hardship. Two changes working together: (1) Floating point values are *always* converted into integers if it is possible to do so without loss of information. (2) Division of two integers returns a floating point value if necessary to preserve the fractional part of the result. The effect of change (1) is to combine the integer affinity and the numeric affinity column types into a single type. The new type is called numeric affinity, but it works like integer affinity. Change (2) resolves Ralf Junker's division paradox. The only code that I can think of that this change might break is cases where the user is depending on the division of two integers returning an integer result. Such code will need to be modified to use the "round()" function to obtain the same result. I am thinking that such code should be very uncommon and that this change will have minimal impact. Nevertheless, the impact is non-zero so I will increment the minor version number as part of this change. If you can think of any other adverse impact that this change might have, please let me know. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Fred Williams wrote: > > Well, since you put it that way. May I go one step farther and request > that this new Dynamic Type also adhere to "Bankers Rounding" commonly > implemented as BCD in other so equipped databases. > > For years I have spent countless hours testing and sweating rounding > and comparisons using "Float" (Real?) fields because (IMHO) the database > designers were scientific mathematicians and the world of users were > 99%^ business types. So, spare me further anguish... :-) > Hm, being an engineer (and not having too much experience with database, I immediately admit), I have always thought it was the other way around: administrative and financial people designing database systems :). I rather like the idea Richard is trying to get across. Too often people have been surprised by such awkward behaviour 5/2 becoming 2 or 5.1*5.1 turning out to be 26.00999 instead of 26.01 and the like. As for banker's rounding: if I understand it correctly, there are at least two slightly different systems - an American and a European one. That problem should be solved too, if you are going to introduce such behaviour. Regards, Arjen
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
I can think of a very elegant solution to this whole ordeal, which is inspired by Perl's way of doing things: Have *two* division operators which have different behaviour and which look different so you can tell what will happen where they are used, regardless of their operand data types. Perl is loosely typed by default and so has elegant handling down to a fine art, having for example different operators for string and numerical comparisons, so you always know what will happen regardless of the operand data types. So I propose for SQLite that any expression having '/' will cast both of its operands as reals and the result will be a real. Also, any expression using instead 'DIV' will cast both of its operands as integers (truncating them if necessary, not rounding) and return an integer. To go with that, 'MOD' will cast both operands as integers and return the integer modulus. It works out visually, both 'word' operators use integers and the one 'symbol' operator uses reals. Sure there's a difference, and while this should help an implementer, it is useful to users because it describes *behaviour*. On a different matter ... At 9:59 PM -0500 11/1/05, [EMAIL PROTECTED] wrote: John Stanton <[EMAIL PROTECTED]> wrote: Users love such a number system because it is natural and works like the Arithmetic they learned in Grade School. I find the idea of dividing two integers stored in binary form and getting a gratuitous conversion to floating point ugly and potentially very annoying. I admit that it has been 4 decades since I was in grade school, but back in the 60's we were taught that 5/2 is 2.5. Are they teaching something different now? Or have I misunderstood the comment? Well, if you want to know ... In the youngest grades, such as kindergarten and grade 1 etc, they only work with whole numbers, such as when dividing up apples or oranges, so 5/2 is "2 with 1 remainder". Only in later grades do they start with fractional numbers, such as 5/2 is "2.5" or "2 and 1/2". So young children actually get it both ways depending on their ages. -- Darren Duncan
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
[EMAIL PROTECTED] wrote: I admit that it has been 4 decades since I was in grade school, but back in the 60's we were taught that 5/2 is 2.5. Are they teaching something different now? Or have I misunderstood the comment? Ah, but how does the 5 *feel* about being divided by 2 ? is the 2 oppressed by being under the 5 ? how is this the fault of the patriarchy ? discuss -- Lindsay
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
John Stanton <[EMAIL PROTECTED]> wrote: > > Users love such a number system because it is natural and works like the > Arithmetic they learned in Grade School. > > I find the idea of dividing two integers stored in binary form and > getting a gratuitous conversion to floating point ugly and potentially > very annoying. I admit that it has been 4 decades since I was in grade school, but back in the 60's we were taught that 5/2 is 2.5. Are they teaching something different now? Or have I misunderstood the comment? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Rather than get caught in a mess of integers and floating point numbers and the consequent unpredictable results, how about having some typing, such as integers and floats and also a decimal fixed point type which looks like a text field and displays like one but which is much more compatible with the flexible typing of Sqlite. Arithmetic on display format fields like that is not fast, but overall it is not bad when you take into account the absence of radix changes when you are inputting and outputting such numbers, often the most frequent activity on numbers in a general purpose database. Users love such a number system because it is natural and works like the Arithmetic they learned in Grade School. As I read the SQL spec it is not excluded. I find the idea of dividing two integers stored in binary form and getting a gratuitous conversion to floating point ugly and potentially very annoying. JS Fred Williams wrote: Ah the sticky wicket that is "Type less" :-) We now have issues evolving as a direct result of that feature in our cute little database. We now seem to have by backing into it: Really Restricted Integer, Real, DateTime (sort of), and Text. BLOB and CLOB away at your own risk! Is it time to officially declare/fully support some Types and clear the air? Fred -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 01, 2005 9:43 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 "Preston Zaugg" <[EMAIL PROTECTED]> wrote: As was discussed in the original post this would be NON-STANDARD behavior. The SQL-99 spec says that integer math remains an integer. The change I propose (and have now checked into CVS, btw, though I might still back it out) does not violate this specification. Think of it this way: SQLite supports only a single numeric type which is REAL. We call that type "numeric". But the type represents what we normally think of as real numbers. We permit integer values to be read from and written to the database as a convenience to the user. And internally, some values are sometimes kept as machine integers for computational and storage efficiency. But that is only an optimization. At the end of the day, there is only a single numeric data type and that type is real. An INTEGER PRIMARY KEY column seems like an exception to this rule. But perhaps not. Think of an INTEGER PRIMARY KEY column as holding a numeric value with restrictions. It is as if we added to every INTEGER PRIMARY KEY named "x" the following check constraint: CHECK( x >= -9223372036854775808 AND x <= 923372036854775807 AND x == round(x) ) So the values going in and out of an integer primary key are still real values. It just happens that their range is restricted somewhat and they do not have a fractional part. If you look at things from this point of view, SQLite does not support integer values. And so we never have to worry about integer division. For complete consistency, I suppose we might want to modify the built-in typeof() function to always return "numeric" instead of "integer". I wonder -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
The more I think about it, the proposed unified numeric model makes a lot of sense and should be the default with no pragmas or compile-time options for the old behavior. The dynamic manifest typing model of Sqlite practically begs to have uniform mathematical results given the dynamic nature of the operands: SQLite version 3.2.7 Enter ".help" for instructions sqlite> create table foo(a); sqlite> insert into foo values(7); sqlite> insert into foo values(7.0); sqlite> select a/6 from foo; 1 1.17 If the columns of tables in Sqlite would strictly enforce types - this would be a different matter. But since they do not (and will not), the proposed behavior is more logically self-consistent. __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Also, regarding the name change suggestions, I disagree. As people have said, no database product is fully SQL standard compliant, and SQLite is no different in that regard. So in the current environment, SQLite's name is *not* misleading, despite any deviations. The name is a brand anyway, and brands transcend any meaningfulness. It should stay the same. -- Darren Duncan
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
To answer Richard's question directly: I do not see the proposed change causing any hardship for me. I happen to like static typing myself due to its ability to help prevent bad data from propagating, with explicit variadic data type for people that don't want to choose a more restrictive type, but SQLite already isn't statically typed, so this change won't make things any worse. Under the circumstances, this change will actually be an improvement to useability as I see it. We have round() or truncate() or CAST when we need integer division. -- Darren Duncan
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
On 11/1/05, René Tegel <[EMAIL PROTECTED]> wrote: > Jay Sprenkle wrote: > > >If you're going to become less compliant perhaps it would be less > >misleading to remove the "SQL" from the project name. I'm not saying > >either is a bad idea, just that the name shouldn't be misleading. > > > > > I think this is kind of 'purist fetisjism'. Personally I like the > pragmatic approach more: make thinks work the way you want it to work. > Btw calling SQLite not worth the letters 'SQL' is imho just lame.. Hey Rene, I think having my suggestion called "lame" and "purist fetishism" was rude and uncalled for. I have no problem with DRH's proposal either. I didn't intend to imply it's of less worth than SQL, merely that the name was misleading if it wasn't going to be standards compliant. How about this instead: Offer an "SQLite" and an "SQLite Classic".
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
I don't see any current integer behavior that cannot be emulated with the new unified numeric type, but you might have to document the behavior of mathematical operators under the new system. Is modulus an integer operation or a floating point operation, for example? Arguably, it could be either. sqlite> select 15.3 % 8; 7.0 sqlite> select 15.3 % 8.7; 7.0 sqlite> select 15.3 % -8.7; 7.0 sqlite> select 73 % 9.17; 1.0 sqlite> select -19.4 & 7; 5 sqlite> select substr("abcdefghijklmop", 63/8, 3.99); ghi --- [EMAIL PROTECTED] wrote: > Suppose SQLite were to merge "integer" and "real" into a single > datatype "numeric" that always worked the same way. Does > anybody know of a (real) usage example where this would cause > an actual hardship on developers? Are there any examples of > things that you can do with separate "real" and "integer" > types that you cannot do with a unified "numeric" type? __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com
RE: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
I'd like to second Dennis' earlier remarks, and add some of my own. One of my greatest hopes when I found SQLite (it's "value proposition") was in having a great little database that would operate in such a familiar way. By "familiar", I mean that it should execute the SQL language, and produce (for the same DDL, and the same DML) the same result as other SQL implementations. In short, I expected it to comply with standards. Using standards is a two-way street: * I should be able to bring existing structures, and existing code from SQL Server, or Informix, or PostgreSQL, and have it not only run, but produce the same result. * I also want to use SQLite to create and test code that I may later take to one of these other platforms, and I'd like it to run the same there as it has been running in SQLite. The better it allows me to do both of the above, the more valuable it is to me. My friends and I joke that one of the greatest things about standards is that everyone gets to choose their own. I'd call SQLite's manifest typing one of "it's own". It is one of SQLite's greatest strengths, as well as it's greatest weakness. Being able to informally type a field is awesome, when I choose to use it. But it is a weakness when it influences a result unexpectedly (when it doesn't allow me to choose MY own standard). Most of my difficulty, and my greatest disappointments in using SQLite to-date has been where I got unexpected results when it did not strictly heed my formal DML instructions. My specific challenges have been with char(n)'s, but I think the learning applies equally to the discussion of real/int/numeric. Paradoxically, its greatest opportunity to grow and become more valuable to me is in allowing me the flexibility to call upon it to behave more strictly standard. By "strictly standard", I mean: * If I specify "INT", in my DML, I'd like it to behave exactly as an INT in other implementations. If it would like to abbreviate the value for compactness of storage, that's great. But I don't want it to store, nor return anything that behaves differently from an int. * Likewise FLOAT/REAL. I really don't care if SQLite stores it as a machine float, as an IEEE float, or as text. But if the field is a float, I don't want it to store, nor ever return anything that behaves differently from a float. * Likewise CHAR(n). It should not store, nor ever return any more than n characters. In situations where informal typing applies well, I'd like to be able to select this behavior explicitly. For example, by defining a column as VARIANT, or NUMERIC. If the "power" of manifest typing is its ability to recognize a value and properly convert it, and store it in a form as compact as it likes -- can that power be leveraged to retrieve a value, no matter how stored, and properly convert it back to behave exactly as the type it is expected to be, rather than as the type it was coerced to for storage? To sum it up: I place more value in how the fields behave than in how they're stored. I'd like more control, not less, in how they behave. Doug -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 01, 2005 10:55 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 With the above clarification, I will restate the question: Suppose SQLite were to merge "integer" and "real" into a single datatype "numeric" that always worked the same way. Does anybody know of a (real) usage example where this would cause an actual hardship on developers? Are there any examples of things that you can do with separate "real" and "integer" types that you cannot do with a unified "numeric" type? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
[EMAIL PROTECTED] wrote: Jay Sprenkle <[EMAIL PROTECTED]> wrote: On 11/1/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: All of the arguments against my proposed changes are basically of the form that "this is not what the SQL standard says". They are theoretical arguments based on a world-view that holds that strong-typing is good and that it is the duty of database systems to enforce types. I do not hold to that world view. SQLite seeks a better way. I think it's a bit misleading to call the library "SQL-Lite: if you're going to redesign and not be like SQL. What do you think about creating a separate project for a fast light database engine that's not "SQL Like"? SQLite is very much SQL. It only deviates from the standard to fix bugs in the original design of the standard. -- D. Richard Hipp <[EMAIL PROTECTED]> Richard, This particular point is devolving into a semantic argument, so I hesitate to continue down this road, but ... A language like SQL is defined by its standard (warts and all). If you develop something that conforms to some but not all of the standard, especially if the non-conformities are by design, the best you can say is that it is close to the standard. Further, I would suggest that the arguments against your change are more along the lines of "standards are good" rather than "strong typing is good". Many people have learned the hard way that using multiple implementations of something that are close to, but not quite standard, often leads to problems. The C language had this problem before ANSI standardization (and the release of conforming or nearly conforming compilers by almost all vendors). The same source often produced different results with different compilers. The use of language extensions (i.e. fixes to design omissions) is also often fraught with trouble. If you set out to fix "bugs" in the design of a language, you are really designing a new language. This is why we have C# , C++, and D (or even C itself, which was a redesign of BCPL). They are all languages that were developed to fix bugs that their creators saw in the C language or one of its other derivatives. The type of gratuitous non-conformance that you have proposed is almost certainly going to lead to interoperability problems between SQLite and other SQL implementations. It won't necessarily make SQLite itself better or worse than the others, simply different. That difference will then need to be allowed for by code that deals with different SQL implementations. This is your project (and I thank you very much for your work on it) so it is ultimately your decision how SQLite will work. I just think that you should very carefully consider any changes that will lead to less conformance with the standard for the language that you are trying to implement. Dennis Cote
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Jay Sprenkle wrote: If you're going to become less compliant perhaps it would be less misleading to remove the "SQL" from the project name. I'm not saying either is a bad idea, just that the name shouldn't be misleading. I think this is kind of 'purist fetisjism'. Personally I like the pragmatic approach more: make thinks work the way you want it to work. Btw calling SQLite not worth the letters 'SQL' is imho just lame.. If you'd be so kind, please name one (or more) SQL-based database engines that comply to the 'standard SQL' you are expecting to see. If you allow me to take, for comparison, MySQL. It has ignored standards in favour of practical features ever since the project started. Still it is one of the most populair engines. I am sure there are possible improvements regarding type affinity, dynamic columns etc which should all be taken into considuration. But as far as i can see Richard Hipp's proposal seems just fine, (backward)compatible and solves a couple of issues and inconveniences. best regards, Rene
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
On Tue, Nov 01, 2005 at 01:45:04PM -0600, Jay Sprenkle wrote: > > > I think it's a bit misleading to call the library "SQL-Lite: if > > > you're going to redesign and not be like SQL. What do you think > > > about creating a separate project for a fast light database > > > engine that's not "SQL Like"? > > > > SQLite is very much SQL. It only deviates from the standard > > to fix bugs in the original design of the standard. > > If you're going to become less compliant perhaps it would be less > misleading to remove the "SQL" from the project name. I'm not saying > either is a bad idea, just that the name shouldn't be misleading. Your name "Jay" appears to derive from the Sanskrit "jaya", meaning "victory". I find this a bit misleading, as clearly anyone regularly wasting time with suggestions like yours is unlikely to achieve victory in anything. I therefore suggest that you change your name from "Jay" to something less misleading. Just a thought. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
At 1:54 PM -0500 11/1/05, [EMAIL PROTECTED] wrote: Please let me restate the proposed change: I desire to change SQLite so that it no longer distinguishes between integer and real. The two concepts are merged into a unified "numeric" type. And because all number values are of the same type, they need to always be treated in the same way. A division should return a result that includes the fractional part, if there is one, regardless of the particular representation of the operands. I'm not a DB person by trade, I'm a programmer. I have grown to love SQLite for it's very 'C' like philosophy of making the underlying mechanisms transparent. Part of that is the manifest typing, which lets me control exactly what's going into the database, and lets me optimize that for best efficiency. My $0.02 is that combining INTEGER and REAL is the wrong direction. It takes information away, information that sqlite is perhaps unique in preserving. It seems that the column affinity mechanism already holds the answer to this problem. If a column could have REAL affinity, then whatever data was stored in that column would have an opportunity to act according to the SQL standard, without losing metadata about the original data. http://www.sqlite.org/datatype3.html says: A column that uses INTEGER affinity behaves in the same way as a column with NUMERIC affinity, except that if a real value with no floating point component (or text value that converts to such) is inserted it is converted to an integer and stored using the INTEGER storage class. I propose a similar affinity be added called "REAL", that would behave the same as NUMERIC, except that INTEGERs would be converted to REAL. Also, I'm surprised that the column affinity isn't applied when the data is read from the database vs inserted into the database, but that's a separate topic. -pmb
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
> > I think it's a bit misleading to call the library "SQL-Lite: if you're > > going to redesign > > and not be like SQL. What do you think about creating a separate > > project for a fast > > light database engine that's not "SQL Like"? > > SQLite is very much SQL. It only deviates from the standard > to fix bugs in the original design of the standard. If you're going to become less compliant perhaps it would be less misleading to remove the "SQL" from the project name. I'm not saying either is a bad idea, just that the name shouldn't be misleading.
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
- Original Message - From: "Jay Sprenkle" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Sent: Tuesday, November 01, 2005 2:08 PM Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 > I think it's a bit misleading to call the library "SQL-Lite: if you're > going to redesign > and not be like SQL. What do you think about creating a separate > project for a fast > light database engine that's not "SQL Like"? strict typing is not, to most people at least, a core feature of sql - the syntax and semantics of operations (ie selects+joins etc) are. as drh stated, strict typing was originally a performance enhancement/implementation complexity reduction measure. the difference between what sql prescribes and what sql does will be apparent to developers at two points: a) schema definition and b) operation semantics expectation formulation. imo, the former is a non-issue, since pretty much each rdbms will have significant differences there; sqlite would indeed probably be one of the easiest one to adapt to in this regard due to the short list of possible types. the latter issue is the one that needs to be discussed and indeed the one drh is trying to get at with his question. is asking developers to change their mindset when working with sqlite and expect, say, a number with a fraction back from a division of two integers too much? this question should also be posed to the authors of language-specific bindings, since this could be a hassle in strictly typed languages. regardless, sqlite's fudging of type handling does not make it not look like sql, although it's pretty obvious it's noncompliant in those respects. this is not a bad thing - following braindead standards faithfully is a braindead design. (disclaimer: assuming that the deviation is less braindead). besides, if you want to get into linguistic interpretations, 'sqlite' could be interpreted both as 'lightweight sql rdbms' and as 'rdbms conforming to a reduced, lightweight subset of sql'. imo, provided that there is a prominenent 'You are standing on a chair and wearing a noose around your neck!' notice given to folks, i don't see this as a problem. -p
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Jay Sprenkle said: > On 11/1/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: >> All of the arguments against my proposed changes are >> basically of the form that "this is not what the SQL standard >> says". They are theoretical arguments based on a world-view >> that holds that strong-typing is good and that it is the duty >> of database systems to enforce types. >> >> I do not hold to that world view. SQLite seeks a better way. > > I think it's a bit misleading to call the library "SQL-Lite: if you're > going to redesign > and not be like SQL. What do you think about creating a separate > project for a fast > light database engine that's not "SQL Like"? I have an even better proposal: When you're writing your code, use tools appropriate to the job. For instance, languages like C, Pascal and BASIC are really good and handling arithmetic. Likewise, SQL and database engines are really good at data storage. So when you need to divide 5 by 2, it probably makes a lot of sense to do that in C/Pascal/BASIC than in SQL. And sure, there's cases where you can't avoid it. But usually you can. Clay Dowling -- Simple Content Management http://www.ceamus.com
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Jay Sprenkle <[EMAIL PROTECTED]> wrote: > On 11/1/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > All of the arguments against my proposed changes are > > basically of the form that "this is not what the SQL standard > > says". They are theoretical arguments based on a world-view > > that holds that strong-typing is good and that it is the duty > > of database systems to enforce types. > > > > I do not hold to that world view. SQLite seeks a better way. > > I think it's a bit misleading to call the library "SQL-Lite: if you're > going to redesign > and not be like SQL. What do you think about creating a separate > project for a fast > light database engine that's not "SQL Like"? SQLite is very much SQL. It only deviates from the standard to fix bugs in the original design of the standard. -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Well, since you put it that way. May I go one step farther and request that this new Dynamic Type also adhere to "Bankers Rounding" commonly implemented as BCD in other so equipped databases. For years I have spent countless hours testing and sweating rounding and comparisons using "Float" (Real?) fields because (IMHO) the database designers were scientific mathematicians and the world of users were 99%^ business types. So, spare me further anguish... :-) Fred > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Tuesday, November 01, 2005 12:55 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 > ... > Please let me restate the proposed change: I desire to change > SQLite so that it no longer distinguishes between integer and > real. The two concepts are merged into a unified "numeric" > type. Just as the same string can be represented as either > UTF-8 or UTF-16 (or sometimes ISO-8859) so too can a number > be represented as integer or real. But regardless of the > particular representation chosen at any particular moment, > the "type" of the data is always "numeric". There is no > "integer". There is no "real". There is only "numeric". > And because all number values are of the same type, they > need to always be treated in the same way. A division should > return a result that includes the fractional part, if there > is one, regardless of the particular representation of the > operands. > > This a simplification designed to make your life easier. No > longer are there different rules to apply depending on whether > a value or a column is "integer" or "real". Everything always > works the same way. The goal is to have no special cases. > ... >
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
On 11/1/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > All of the arguments against my proposed changes are > basically of the form that "this is not what the SQL standard > says". They are theoretical arguments based on a world-view > that holds that strong-typing is good and that it is the duty > of database systems to enforce types. > > I do not hold to that world view. SQLite seeks a better way. I think it's a bit misleading to call the library "SQL-Lite: if you're going to redesign and not be like SQL. What do you think about creating a separate project for a fast light database engine that's not "SQL Like"?
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
There is an ongoing debate on this subject at Lambda the Ultimate. One approach that is sound is to introduce a new static type, called DYNAMIC, that permits any value. Summarizing and seconding Dennis Cote's suggestion, perhaps columns that have no type declared, or that are declared DYNAMIC, behave as SQLite does today, and columns that have static declarations behave as if they are statically typed. I like that suggestion, but I am sure that this is beyond the scope of the question. Truth be told, manifest typing has never been a selling point (if I can use that term for free software) for me, rather it has been something to be aware of / work around. I know at one point there was talk of a strict affinity mode for sqlite (at least I saw something that indicated that somewhere on the website: http://www.sqlite.org/datatype3.html bullet point 6.) I personally would love to see a strict affinity mode db with the inclusion of a dynamically typed column that can still be used. BTW: a dynamically typed column is actually included in MSSQL 2005. ALso is there a "roadmap" type document that talks about the future of sqlite? --Preston
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
All of the arguments against my proposed changes are basically of the form that "this is not what the SQL standard says". They are theoretical arguments based on a world-view that holds that strong-typing is good and that it is the duty of database systems to enforce types. I do not hold to that world view. SQLite seeks a better way. You may or may not agree that the SQLite way is better (I happen to think that it is, of course) but that is beside the point. What I want to know is whether or not my proposed changes will cause serious hardship to any current or future SQLite users. Please let me restate the proposed change: I desire to change SQLite so that it no longer distinguishes between integer and real. The two concepts are merged into a unified "numeric" type. Just as the same string can be represented as either UTF-8 or UTF-16 (or sometimes ISO-8859) so too can a number be represented as integer or real. But regardless of the particular representation chosen at any particular moment, the "type" of the data is always "numeric". There is no "integer". There is no "real". There is only "numeric". And because all number values are of the same type, they need to always be treated in the same way. A division should return a result that includes the fractional part, if there is one, regardless of the particular representation of the operands. This a simplification designed to make your life easier. No longer are there different rules to apply depending on whether a value or a column is "integer" or "real". Everything always works the same way. The goal is to have no special cases. I'm trying do all this while at the same time maintaining reasonable compatibility with other SQL database engines. SQLite will never be 100% compatible with other database engines. But on the other hand, I dare say you cannot find any two other SQL database engines that are 100% compatible with each other. Most claim SQL compatibility, but you can always find a corner case where two different engines will give divergent results. So even though SQLite is not 100% compatible, neither is any other database engine. With the above clarification, I will restate the question: Suppose SQLite were to merge "integer" and "real" into a single datatype "numeric" that always worked the same way. Does anybody know of a (real) usage example where this would cause an actual hardship on developers? Are there any examples of things that you can do with separate "real" and "integer" types that you cannot do with a unified "numeric" type? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Dennis Cote wrote: The second issue is demonstrated by the last last three statements. CREATE TABLE t (a REAL, b REAL); INSERT INTO t VALUES (5, 2); SELECT a / b FROM t; Here he has explicitly declared the columns a and b to be of type real. He then stores integer literal values into those columns. This is where the problem occurs, not during the division in the select statement. SQL is a typed language. SQLite was originally an untyped implementation of SQL. In version 3 SQLite was changed to introduce stronger data typing, while still trying to maintain compatibility with its previous untyped versions. It does this very well in most cases. SQLite uses manifest typing, where each data value has its own associated data type whereas the standard assumes each column has a data type. In a few cases SQLite bumps into areas where this implementation produces non-standard behavior. This is one of them. To produce standard behavior, integer values stored into columns of type real should be converted to real values. This is actualy the problem we have, even we take care of inserting /or importing data doing the right bindings (to fellow the example above) and it's diffult to care of everything .. rc = sqlite3_prepare(db, "insert into t values (?,?);", -1, , 0); if (rc == SQLITE_OK && stmt != NULL ) { sqlite3_bind_double(stmt, 1, 5); sqlite3_bind_double(stmt, 2, 2); sqlite3_step(stmt); rc = sqlite3_reset(stmt); } rc = sqlite3_finalize(stmt); sqlite3> select a,b,(a/b) from t 5.0|2.0|2.5 Ok. when a user issue an update command (i have no control on this command) on the two columns with sqlite3> update set a= 5, b =2; sqliote3> select a,b,(a/b) from t; 5|2|2 which is not correct. But i don't see a problem when explictly doing 5/2 gives 2. AS 5 and 2 are integers. regards hamid
RE: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Thank you for your insight into the state of SQLite "Typing." I think this further exposes the need to establish our level of conformance to a pretty much pervasive database standard (SQL.) I'm not certain that IBM was the initiator of the term "Gray Area" but they certainly have greatly increased it's influence at least in the world of computers. In every IBM manual I ever used there was always the Standard, white back grounded "Standard" text and a very large portion of "Gray Area," gray background text, commonly referred to as "Enhancements" or "Extensions." If one avoided the gray areas, theoretically the exercises undertaken were "Platform independent." Perhaps if we intend to venture beyond the "Standard" (SQL) it might be time to put a little prominent "Gray" on the Wiki Pages. And, as IBM did/does allow one to error in his/her own choice of death by "Enhancement." Personally I always have thought the int -> int = int can be an extreme pain in the ... But, I know about it and have adapted. The same can be said for "Dynamic Typing." I just say make that text very "Gray." And if possible "User Controlled" optional. >From my standpoint, I was merrily cruising along with the "old" SQLite 2.8.x implementation. But the switch to 3,x,x has been at best, "eventful." Seems with each new day I either find something old obscurely broken or yet another way to shoot myself in the foot with a 3.x.x "Enhancement." :-) Ah, life on the "bleeding edge"! BTW. On the Windose machine, footprint (.dll size): 2.8.x, 219K; 3.x.x, 245k (today) equals +36k, If you had yet another 36K what could you do? As modern PDA's now seem to have a minimum of 8M or so. And these kind of enhancement requests just keep coming. Fred > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Tuesday, November 01, 2005 10:53 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 > > > "Fred Williams" <[EMAIL PROTECTED]> wrote: > > Ah the sticky wicket that is "Type less" :-) We now have issues > > evolving as a direct result of that feature in our cute > little database. > > We now seem to have by backing into it: Really Restricted > Integer, Real, > > DateTime (sort of), and Text. BLOB and CLOB away at your own risk! > > > > Is it time to officially declare/fully support some Types > and clear the > > air? > > > > SQLite is not "type-less". It uses manifest or dynamic typing instead > of static-typing which is what most other SQL database engines use > (and the SQL standard specifies). Manifest or dynamic typing is a > superset of static typing. The use of static typing in the > SQL standard > is a bug in that standard, in my view, than unnecessarily complicates > and restricts what you can do with SQL. The original SQL standard > specifies static typing so that implementations can use fixed-size > records. Static typing is an artifact of the implementation showing > through into the interface. Static typing in SQL is designed not to > help the users of SQL databases, but rather to help the implementors > of SQL database engines. > > SQLite is the only SQL database engine that I am aware of that offers > dynamic typing. This is not going to change. > > The difficult faced by SQLite is not how to deal with a dynamically > typed language (that's easy) but how to deal with a dynamic typing > in a way that is backwards compatible with the (broken) static typing > behavior of SQL. That is much harder. But it is achievable, I think. > > > -- > D. Richard Hipp <[EMAIL PROTECTED]> >
RE: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
In Delphi we have the Type: "Variant," which is pretty much "Type-less" for OOP purposes. > -Original Message- > From: Doug Currie [mailto:[EMAIL PROTECTED] > Sent: Tuesday, November 01, 2005 11:25 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 > > > Tuesday, November 1, 2005, 11:53:11 AM, [EMAIL PROTECTED] wrote: > > > Static typing in SQL is designed not to help the users of SQL > > databases, but rather to help the implementors of SQL database > > engines. > > There are uses for static typing. Types in the SQL context can be used > as a kind of integrity constraint. If my design calls for a > measurement, say, in a REAL column, perhaps I don't want rows with > text (e.g., "large") in this column. Types is a way to enforce this. > > > The difficult faced by SQLite is not how to deal with a dynamically > > typed language (that's easy) but how to deal with a dynamic typing > > in a way that is backwards compatible with the (broken) > static typing > > behavior of SQL. That is much harder. But it is > achievable, I think. > > There is an ongoing debate on this subject at Lambda the Ultimate. One > approach that is sound is to introduce a new static type, called > DYNAMIC, that permits any value. Summarizing and seconding Dennis > Cote's suggestion, perhaps columns that have no type declared, or that > are declared DYNAMIC, behave as SQLite does today, and columns that > have static declarations behave as if they are statically typed. > > Regards, > > e > > -- > Doug Currie > Londonderry, NH >
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Tuesday, November 1, 2005, 11:53:11 AM, [EMAIL PROTECTED] wrote: > Static typing in SQL is designed not to help the users of SQL > databases, but rather to help the implementors of SQL database > engines. There are uses for static typing. Types in the SQL context can be used as a kind of integrity constraint. If my design calls for a measurement, say, in a REAL column, perhaps I don't want rows with text (e.g., "large") in this column. Types is a way to enforce this. > The difficult faced by SQLite is not how to deal with a dynamically > typed language (that's easy) but how to deal with a dynamic typing > in a way that is backwards compatible with the (broken) static typing > behavior of SQL. That is much harder. But it is achievable, I think. There is an ongoing debate on this subject at Lambda the Ultimate. One approach that is sound is to introduce a new static type, called DYNAMIC, that permits any value. Summarizing and seconding Dennis Cote's suggestion, perhaps columns that have no type declared, or that are declared DYNAMIC, behave as SQLite does today, and columns that have static declarations behave as if they are statically typed. Regards, e -- Doug Currie Londonderry, NH
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Joe Wilson <[EMAIL PROTECTED]> wrote: > I've found a potential problem - > round() is not a good substitute for the old integer truncation. > > Consider the previous Sqlite3 behavior: > > sqlite> select 15/8; > 1 > sqlite> select round(15.0/8.0); > 2 > > Can you recommend or provide a new function that performs correct > integer trunction? > I think the current code in CVS allows you to do this with CAST( 15.0/8.0 AS integer ) But I admit I need to test that case more thoroughly. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
"Fred Williams" <[EMAIL PROTECTED]> wrote: > Ah the sticky wicket that is "Type less" :-) We now have issues > evolving as a direct result of that feature in our cute little database. > We now seem to have by backing into it: Really Restricted Integer, Real, > DateTime (sort of), and Text. BLOB and CLOB away at your own risk! > > Is it time to officially declare/fully support some Types and clear the > air? > SQLite is not "type-less". It uses manifest or dynamic typing instead of static-typing which is what most other SQL database engines use (and the SQL standard specifies). Manifest or dynamic typing is a superset of static typing. The use of static typing in the SQL standard is a bug in that standard, in my view, than unnecessarily complicates and restricts what you can do with SQL. The original SQL standard specifies static typing so that implementations can use fixed-size records. Static typing is an artifact of the implementation showing through into the interface. Static typing in SQL is designed not to help the users of SQL databases, but rather to help the implementors of SQL database engines. SQLite is the only SQL database engine that I am aware of that offers dynamic typing. This is not going to change. The difficult faced by SQLite is not how to deal with a dynamically typed language (that's easy) but how to deal with a dynamic typing in a way that is backwards compatible with the (broken) static typing behavior of SQL. That is much harder. But it is achievable, I think. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Preston Zaugg wrote: I would NOT be in favor of this change. As was discussed in the original post this would be NON-STANDARD behavior. The SQL-99 spec says that integer math remains an integer. The only time I would like an integer to return a "real" result is if that integer is stored in a field of type “real”, then all operations on that number should return a "real" result. I understand the reasons for "int"s being stored as an "int" in a "real" column, but the change I would prefer is for it to act as a "real" if stored in a "real" column, otherwise it should act as it currently does. I agree with Preston. SQLite should follow the SQL standard whenever possible. Ralf brought up two separate but related issues in his original post: In risk of asking the obvious, I wonder if the following division should be considered correct: | Query | Result Value | Result Type| OK? --- 1 | SELECT 5 / 2; | 2| SQLITE_INTEGER | No? 2 | SELECT 5.0 / 2; | 2.5 | SQLITE_FLOAT | Yes 3 | SELECT 5 / 2.0; | 2.5 | SQLITE_FLOAT | Yes 4 | SELECT 5.0 / 2.0; | 2.5 | SQLITE_FLOAT | Yes The query in question is Query 1. Is the returned integer result correct or should it not better return the 2.5 float value instead? I understand that this probably boils down to integer arithmetics, but the decimals dropping can cause severe rounding errors if SQLite stores an integer number without further warning like in: CREATE TABLE t (a REAL, b REAL); INSERT INTO t VALUES (5, 2); Then the query SELECT a / b FROM t; returns wrong results, even though both colums are declared as REAL. In my opinion, any division which can not be represented as an integer should return a float value. The first set of select statements are doing arithmetic using literal constant values. Each of these has a type, either real or integer. SQLite is doing the arithmetic using these values according to the standard. It produces an real (or approximate result) if either argument is real, and an integer (or exact) result if both arguments are integer. The semantics of arithmetic are different in many scripting languages, but those languages are following a different standard. The SQL standard specifies how this should be done and SQLite is doing it that way now. It shouldn't be changed. The second issue is demonstrated by the last last three statements. CREATE TABLE t (a REAL, b REAL); INSERT INTO t VALUES (5, 2); SELECT a / b FROM t; Here he has explicitly declared the columns a and b to be of type real. He then stores integer literal values into those columns. This is where the problem occurs, not during the division in the select statement. SQL is a typed language. SQLite was originally an untyped implementation of SQL. In version 3 SQLite was changed to introduce stronger data typing, while still trying to maintain compatibility with its previous untyped versions. It does this very well in most cases. SQLite uses manifest typing, where each data value has its own associated data type whereas the standard assumes each column has a data type. In a few cases SQLite bumps into areas where this implementation produces non-standard behavior. This is one of them. To produce standard behavior, integer values stored into columns of type real should be converted to real values. If SQLite did this, then the select statement would be doing arithmetic on two real values and it would produce a real result. This is what the user expects because he explicitly said that these columns should contain real values. Currently SQLite is giving more weight to the fact that he didn't put a decimal point on the literal values than it is giving to the fact that he explicitly said the columns will hold real data. This change would only affect columns which are explicitly typed as real. Other columns that are untyped would continue to be able to hold any type of value as they do now. In all likelihood, any users that are explicitly declaring the data type of a column will be intending to use it to hold data of that type, and will only insert data of that type (or values hey expect to be converted to that type as in this case). Others who are using the typeless feature of SQLite will probably not declared a column data type and will get the same behavior they have now. Similar arguments can be applied to values inserted into columns that are declared to be type integer. SQLite should probably convert real valued data inserted into integer columns into integer values. This would ensure that the sum of an integer column is always an integer for example. It would also eliminate the situation we have now where real values stored into integer columns are accepted without complaint except when the integer column is also a primary key. In this case we get a "datatype mismatch" error
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
I've found a potential problem - round() is not a good substitute for the old integer truncation. Consider the previous Sqlite3 behavior: sqlite> select 15/8; 1 sqlite> select round(15.0/8.0); 2 Can you recommend or provide a new function that performs correct integer trunction? --- [EMAIL PROTECTED] wrote: > Joe Wilson <[EMAIL PROTECTED]> wrote: > > > > How do intend to treat 5/2 if passed to an Sqlite function expecting > > an integer argument? > > Exactly the same thing that happens now if you pass 2.5 > into that same function: it truncates the value to an > integer 2. > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
On 11/1/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > I am proposing to make the changes outlined below in SQLite > version 3.3.0 and I am wondering if these changes will cause > any severe hardship. Stay with the SQL standard, if that's not clear follow what other languages do.. int operator int -> int result int operator float -> float operator float -> float result etc. Just my opinion FWIW.
RE: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Ah the sticky wicket that is "Type less" :-) We now have issues evolving as a direct result of that feature in our cute little database. We now seem to have by backing into it: Really Restricted Integer, Real, DateTime (sort of), and Text. BLOB and CLOB away at your own risk! Is it time to officially declare/fully support some Types and clear the air? Fred > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Tuesday, November 01, 2005 9:43 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 > > > "Preston Zaugg" <[EMAIL PROTECTED]> wrote: > > As was discussed in the original > > post this would be NON-STANDARD behavior. > > The SQL-99 spec says that integer > > math remains an integer. > > > > The change I propose (and have now checked into CVS, btw, > though I might still back it out) does not violate this > specification. > > Think of it this way: SQLite supports only a single > numeric type which is REAL. We call that type "numeric". > But the type represents what we normally think of as real > numbers. > > We permit integer values to be read from and written to > the database as a convenience to the user. And internally, > some values are sometimes kept as machine integers for > computational and storage efficiency. But that is only > an optimization. At the end of the day, there is only a > single numeric data type and that type is real. > > An INTEGER PRIMARY KEY column seems like an exception to > this rule. But perhaps not. Think of an INTEGER PRIMARY > KEY column as holding a numeric value with restrictions. > It is as if we added to every INTEGER PRIMARY KEY named > "x" the following check constraint: > >CHECK( x >= -9223372036854775808 > AND x <= 923372036854775807 > AND x == round(x) ) > > So the values going in and out of an integer primary key > are still real values. It just happens that their range > is restricted somewhat and they do not have a fractional > part. > > If you look at things from this point of view, SQLite > does not support integer values. And so we never have > to worry about integer division. > > For complete consistency, I suppose we might want to > modify the built-in typeof() function to always return > "numeric" instead of "integer". I wonder > > -- > D. Richard Hipp <[EMAIL PROTECTED]> >
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Joe Wilson <[EMAIL PROTECTED]> wrote: > > How do intend to treat 5/2 if passed to an Sqlite function expecting > an integer argument? Exactly the same thing that happens now if you pass 2.5 into that same function: it truncates the value to an integer 2. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
"Preston Zaugg" <[EMAIL PROTECTED]> wrote: > As was discussed in the original > post this would be NON-STANDARD behavior. > The SQL-99 spec says that integer > math remains an integer. > The change I propose (and have now checked into CVS, btw, though I might still back it out) does not violate this specification. Think of it this way: SQLite supports only a single numeric type which is REAL. We call that type "numeric". But the type represents what we normally think of as real numbers. We permit integer values to be read from and written to the database as a convenience to the user. And internally, some values are sometimes kept as machine integers for computational and storage efficiency. But that is only an optimization. At the end of the day, there is only a single numeric data type and that type is real. An INTEGER PRIMARY KEY column seems like an exception to this rule. But perhaps not. Think of an INTEGER PRIMARY KEY column as holding a numeric value with restrictions. It is as if we added to every INTEGER PRIMARY KEY named "x" the following check constraint: CHECK( x >= -9223372036854775808 AND x <= 923372036854775807 AND x == round(x) ) So the values going in and out of an integer primary key are still real values. It just happens that their range is restricted somewhat and they do not have a fractional part. If you look at things from this point of view, SQLite does not support integer values. And so we never have to worry about integer division. For complete consistency, I suppose we might want to modify the built-in typeof() function to always return "numeric" instead of "integer". I wonder -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
What happened to the old: Integer arithmetic produces integer results rule? I thought that was either a "Standard" or at least a very old artifact. Is it not how most Language math functions work? I like the Pragma idea on this one. > -Original Message- > From: Joe Wilson [mailto:[EMAIL PROTECTED] > Sent: Tuesday, November 01, 2005 9:10 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 > > > Although all my Sqlite3 databases depend on integer division > truncation > and would break with your proposed change I agree that 5/2 > should equal > 2.5 in order to be more consistant with other databases. I > can migrate > my databases to use round(). But might it be possible to create a > backwards compatibilty pragma to preserve the old integer division > truncation behavior? Or perhaps a compile-time option? > > How do intend to treat 5/2 if passed to an Sqlite function expecting > an integer argument? An error? 2? 3? I would vote that it would be > treated as 2 in such a case. > > --- [EMAIL PROTECTED] wrote: > > > I am proposing to make the changes outlined below in SQLite > > version 3.3.0 and I am wondering if these changes will cause > > any severe hardship. > > > > Two changes working together: > > > > (1) Floating point values are *always* converted into > > integers if it is possible to do so without loss > > of information. > > > > (2) Division of two integers returns a floating point > > value if necessary to preserve the fractional part > > of the result. > > > > The effect of change (1) is to combine the integer affinity > > and the numeric affinity column types into a single type. > > The new type is called numeric affinity, but it works like > > integer affinity. Change (2) resolves Ralf Junker's > > division paradox. > > > > The only code that I can think of that this change might > > break is cases where the user is depending on the division > > of two integers returning an integer result. Such code > > will need to be modified to use the "round()" function > > to obtain the same result. I am thinking that such code > > should be very uncommon and that this change will have > > minimal impact. Nevertheless, the impact is non-zero so > > I will increment the minor version number as part of this > > change. > > > > If you can think of any other adverse impact that this > > change might have, please let me know. > > -- > > D. Richard Hipp <[EMAIL PROTECTED]> > > > > > > > > > __ > Yahoo! FareChase: Search multiple travel sites in one click. > http://farechase.yahoo.com
RE: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
For what it is worth I would NOT be in favor of this change. As was discussed in the original post this would be NON-STANDARD behavior. The SQL-99 spec says that integer math remains an integer. The only time I would like an integer to return a "real" result is if that integer is stored in a field of type real, then all operations on that number should return a "real" result. I understand the reasons for "int"s being stored as an "int" in a "real" column, but the change I would prefer is for it to act as a "real" if stored in a "real" column, otherwise it should act as it currently does. Thanks for asking for opinions before making the change Preston From: [EMAIL PROTECTED] Reply-To: sqlite-users@sqlite.org To: sqlite-users@sqlite.org Subject: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 Date: Tue, 01 Nov 2005 08:59:53 -0500 I am proposing to make the changes outlined below in SQLite version 3.3.0 and I am wondering if these changes will cause any severe hardship. Two changes working together: (1) Floating point values are *always* converted into integers if it is possible to do so without loss of information. (2) Division of two integers returns a floating point value if necessary to preserve the fractional part of the result. The effect of change (1) is to combine the integer affinity and the numeric affinity column types into a single type. The new type is called numeric affinity, but it works like integer affinity. Change (2) resolves Ralf Junker's division paradox. The only code that I can think of that this change might break is cases where the user is depending on the division of two integers returning an integer result. Such code will need to be modified to use the "round()" function to obtain the same result. I am thinking that such code should be very uncommon and that this change will have minimal impact. Nevertheless, the impact is non-zero so I will increment the minor version number as part of this change. If you can think of any other adverse impact that this change might have, please let me know. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Although all my Sqlite3 databases depend on integer division truncation and would break with your proposed change I agree that 5/2 should equal 2.5 in order to be more consistant with other databases. I can migrate my databases to use round(). But might it be possible to create a backwards compatibilty pragma to preserve the old integer division truncation behavior? Or perhaps a compile-time option? How do intend to treat 5/2 if passed to an Sqlite function expecting an integer argument? An error? 2? 3? I would vote that it would be treated as 2 in such a case. --- [EMAIL PROTECTED] wrote: > I am proposing to make the changes outlined below in SQLite > version 3.3.0 and I am wondering if these changes will cause > any severe hardship. > > Two changes working together: > > (1) Floating point values are *always* converted into > integers if it is possible to do so without loss > of information. > > (2) Division of two integers returns a floating point > value if necessary to preserve the fractional part > of the result. > > The effect of change (1) is to combine the integer affinity > and the numeric affinity column types into a single type. > The new type is called numeric affinity, but it works like > integer affinity. Change (2) resolves Ralf Junker's > division paradox. > > The only code that I can think of that this change might > break is cases where the user is depending on the division > of two integers returning an integer result. Such code > will need to be modified to use the "round()" function > to obtain the same result. I am thinking that such code > should be very uncommon and that this change will have > minimal impact. Nevertheless, the impact is non-zero so > I will increment the minor version number as part of this > change. > > If you can think of any other adverse impact that this > change might have, please let me know. > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com
[sqlite] Proposed 3.3.0 changes. Was: 5/2==2
I am proposing to make the changes outlined below in SQLite version 3.3.0 and I am wondering if these changes will cause any severe hardship. Two changes working together: (1) Floating point values are *always* converted into integers if it is possible to do so without loss of information. (2) Division of two integers returns a floating point value if necessary to preserve the fractional part of the result. The effect of change (1) is to combine the integer affinity and the numeric affinity column types into a single type. The new type is called numeric affinity, but it works like integer affinity. Change (2) resolves Ralf Junker's division paradox. The only code that I can think of that this change might break is cases where the user is depending on the division of two integers returning an integer result. Such code will need to be modified to use the "round()" function to obtain the same result. I am thinking that such code should be very uncommon and that this change will have minimal impact. Nevertheless, the impact is non-zero so I will increment the minor version number as part of this change. If you can think of any other adverse impact that this change might have, please let me know. -- D. Richard Hipp <[EMAIL PROTECTED]>