Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

2005-11-03 Thread Joe Wilson
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

2005-11-03 Thread Dennis Cote

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

2005-11-03 Thread John Stanton

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

2005-11-03 Thread John Stanton

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

2005-11-03 Thread John Stanton
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

2005-11-03 Thread Henry Miller



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

2005-11-02 Thread Chris Hanson

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

2005-11-02 Thread Gerry Snyder

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

2005-11-02 Thread Brass Tilde
> 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

2005-11-02 Thread Eric Bohlman

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

2005-11-02 Thread Brad DerManouelian
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

2005-11-02 Thread Clark Christensen
> 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

2005-11-02 Thread Florian Weimer
> 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

2005-11-02 Thread Jay Sprenkle
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

2005-11-02 Thread Clark Christensen


- 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

2005-11-02 Thread Jay Sprenkle
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

2005-11-02 Thread Brass Tilde
> > 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

2005-11-02 Thread Brass Tilde
> 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

2005-11-02 Thread David Pitcher

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

2005-11-02 Thread Fred Williams
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

2005-11-02 Thread Fred Williams
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

2005-11-02 Thread Brad DerManouelian
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

2005-11-02 Thread Rob Lohman

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

2005-11-02 Thread Nemanja Corlija
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

2005-11-02 Thread Jay Sprenkle
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

2005-11-02 Thread Drew, Stephen
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

2005-11-02 Thread Brass Tilde
> 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

2005-11-02 Thread Puneet Kishor


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

2005-11-02 Thread drh
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

2005-11-02 Thread Dennis Cote

[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

2005-11-02 Thread Brass Tilde
> 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

2005-11-02 Thread drh
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

2005-11-02 Thread Henry Miller



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

2005-11-02 Thread Fred Williams
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

2005-11-02 Thread David Pitcher

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

2005-11-02 Thread David Pitcher

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

2005-11-02 Thread Brass Tilde
> 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

2005-11-02 Thread John Stanton

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

2005-11-02 Thread John Stanton


[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

2005-11-02 Thread John Stanton
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

2005-11-02 Thread John Stanton
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

2005-11-02 Thread Darren Duncan

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

2005-11-02 Thread Zibetti Paolo
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

2005-11-01 Thread Arjen Markus
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

2005-11-01 Thread Darren Duncan
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

2005-11-01 Thread Lindsay




[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

2005-11-01 Thread drh
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

2005-11-01 Thread John Stanton
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

2005-11-01 Thread Joe Wilson
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

2005-11-01 Thread Darren Duncan

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

2005-11-01 Thread Darren Duncan

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

2005-11-01 Thread Jay Sprenkle
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

2005-11-01 Thread Joe Wilson
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

2005-11-01 Thread Jackson, Douglas H
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

2005-11-01 Thread Dennis Cote

[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

2005-11-01 Thread René Tegel

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

2005-11-01 Thread Andrew Piskorski
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

2005-11-01 Thread Peter Bierman

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

2005-11-01 Thread Jay Sprenkle
> > 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

2005-11-01 Thread Paul G

- 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

2005-11-01 Thread Clay Dowling

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

2005-11-01 Thread drh
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

2005-11-01 Thread Fred Williams
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

2005-11-01 Thread Jay Sprenkle
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

2005-11-01 Thread Preston Z

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

2005-11-01 Thread drh
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

2005-11-01 Thread Hamid Benhocine

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

2005-11-01 Thread Fred Williams
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

2005-11-01 Thread Fred Williams
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

2005-11-01 Thread Doug Currie
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

2005-11-01 Thread drh
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

2005-11-01 Thread drh
"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

2005-11-01 Thread Dennis Cote

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

2005-11-01 Thread Joe Wilson
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

2005-11-01 Thread Jay Sprenkle
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

2005-11-01 Thread Fred Williams
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

2005-11-01 Thread drh
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

2005-11-01 Thread drh
"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

2005-11-01 Thread Fred Williams
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

2005-11-01 Thread Preston Zaugg

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

2005-11-01 Thread Joe Wilson
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

2005-11-01 Thread drh
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]>