Proposed 3.3.0 changes. Was: 5/2==2

classic Classic list List threaded Threaded
92 messages Options
12345
Reply | Threaded
Open this post in threaded view
|

Proposed 3.3.0 changes. Was: 5/2==2

D. Richard Hipp
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 <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Proposed 3.3.0 changes. Was: 5/2==2

developir@yahoo.com
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.

--- [hidden email] 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 <[hidden email]>
>
>



               
__________________________________
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com
Reply | Threaded
Open this post in threaded view
|

RE: Proposed 3.3.0 changes. Was: 5/2==2

Preston Zaugg
In reply to this post by D. Richard Hipp
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: [hidden email]
>Reply-To: [hidden email]
>To: [hidden email]
>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 <[hidden email]>
>


Reply | Threaded
Open this post in threaded view
|

RE: Proposed 3.3.0 changes. Was: 5/2==2

Fred Williams
In reply to this post by developir@yahoo.com
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:[hidden email]]
> Sent: Tuesday, November 01, 2005 9:10 AM
> To: [hidden email]
> 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.
>
> --- [hidden email] 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 <[hidden email]>
> >
> >
>
>
>
>
> __________________________________
> Yahoo! FareChase: Search multiple travel sites in one click.
> http://farechase.yahoo.com

Reply | Threaded
Open this post in threaded view
|

Re: Proposed 3.3.0 changes. Was: 5/2==2

D. Richard Hipp
In reply to this post by Preston Zaugg
"Preston Zaugg" <[hidden email]> 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 <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Proposed 3.3.0 changes. Was: 5/2==2

D. Richard Hipp
In reply to this post by Preston Zaugg
Joe Wilson <[hidden email]> 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 <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

RE: Proposed 3.3.0 changes. Was: 5/2==2

Fred Williams
In reply to this post by D. Richard Hipp
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: [hidden email] [mailto:[hidden email]]
> Sent: Tuesday, November 01, 2005 9:43 AM
> To: [hidden email]
> Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
>
>
> "Preston Zaugg" <[hidden email]> 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 <[hidden email]>
>

Reply | Threaded
Open this post in threaded view
|

Re: Proposed 3.3.0 changes. Was: 5/2==2

Jay Sprenkle
In reply to this post by D. Richard Hipp
On 11/1/05, [hidden email] <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

Re: Proposed 3.3.0 changes. Was: 5/2==2

developir@yahoo.com
In reply to this post by D. Richard Hipp
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?

--- [hidden email] wrote:

> Joe Wilson <[hidden email]> 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 <[hidden email]>
>
>



               
__________________________________
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com
Reply | Threaded
Open this post in threaded view
|

Re: Proposed 3.3.0 changes. Was: 5/2==2

Dennis Cote
In reply to this post by Preston Zaugg
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 for the primary key column but not for other
columns with the same declared type. Only the last of the following
inserts causes an error.

  CREATE TABLE t (a INTEGER, b INTEGER, PRIMARY KEY(a));
  INSERT INTO t VALUES (5, 2);
  INSERT INTO t VALUES (6, 2.1);
  INSERT INTO t VALUES (7.1, 2);

I think this is change should also be made to ensure that explicitly
declared column types are honored by SQLite.

I would also like to thank DRH for taking the time to request and
evaluate input from the group before making this decision and changing
SQLite's behavior.

Dennis Cote

Reply | Threaded
Open this post in threaded view
|

Re: Proposed 3.3.0 changes. Was: 5/2==2

D. Richard Hipp
In reply to this post by Preston Zaugg
"Fred Williams" <[hidden email]> 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 <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Proposed 3.3.0 changes. Was: 5/2==2

D. Richard Hipp
In reply to this post by Preston Zaugg
Joe Wilson <[hidden email]> 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 <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Proposed 3.3.0 changes. Was: 5/2==2

Doug Currie-2
In reply to this post by D. Richard Hipp
Tuesday, November 1, 2005, 11:53:11 AM, [hidden email] 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

Reply | Threaded
Open this post in threaded view
|

RE: Proposed 3.3.0 changes. Was: 5/2==2

Fred Williams
In reply to this post by D. Richard Hipp
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: [hidden email] [mailto:[hidden email]]
> Sent: Tuesday, November 01, 2005 10:53 AM
> To: [hidden email]
> Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
>
>
> "Fred Williams" <[hidden email]> 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 <[hidden email]>
>

Reply | Threaded
Open this post in threaded view
|

RE: Proposed 3.3.0 changes. Was: 5/2==2

Fred Williams
In reply to this post by Doug Currie-2
In Delphi we have the Type: "Variant," which is pretty much "Type-less"
for OOP purposes.

> -----Original Message-----
> From: Doug Currie [mailto:[hidden email]]
> Sent: Tuesday, November 01, 2005 11:25 AM
> To: [hidden email]
> Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
>
>
> Tuesday, November 1, 2005, 11:53:11 AM, [hidden email] 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
>

Reply | Threaded
Open this post in threaded view
|

Re: Proposed 3.3.0 changes. Was: 5/2==2

Hamid Benhocine
In reply to this post by Dennis Cote
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, &stmt, 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



Reply | Threaded
Open this post in threaded view
|

Re: Proposed 3.3.0 changes. Was: 5/2==2

D. Richard Hipp
In reply to this post by Preston Zaugg
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 <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Proposed 3.3.0 changes. Was: 5/2==2

Preston Zaugg
In reply to this post by Doug Currie-2
>There is an ongoing debate on this subject at Lambda the Ultimate. One
>approach that is sound is to introduce a new static type, called
>DYNAMIC, that permits any value. Summarizing and seconding Dennis
>Cote's suggestion, perhaps columns that have no type declared, or that
>are declared DYNAMIC, behave as SQLite does today, and columns that
>have static declarations behave as if they are statically typed.

I like that suggestion, but I am sure that this is beyond the scope of the
question.

Truth be told, manifest typing has never been a ?selling point? (if I can
use that term for free software) for me, rather it has been something to be
aware of / work around. I know at one point there was talk of a ?strict?
affinity mode for sqlite (at least I saw something that indicated that
somewhere on the website: http://www.sqlite.org/datatype3.html bullet point
6.) I personally would love to see a strict affinity mode db with the
inclusion of a dynamically typed column that can still be used.


BTW: a dynamically typed column is actually included in MSSQL 2005.


ALso is there a "roadmap" type document that talks about the future of
sqlite?

--Preston


Reply | Threaded
Open this post in threaded view
|

Re: Proposed 3.3.0 changes. Was: 5/2==2

Jay Sprenkle
In reply to this post by D. Richard Hipp
On 11/1/05, [hidden email] <[hidden email]> 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"?
Reply | Threaded
Open this post in threaded view
|

RE: Proposed 3.3.0 changes. Was: 5/2==2

Fred Williams
In reply to this post by D. Richard Hipp
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: [hidden email] [mailto:[hidden email]]
> Sent: Tuesday, November 01, 2005 12:55 PM
> To: [hidden email]
> 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.
>
...
>

12345