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

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

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

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: [hidden email] [mailto:[hidden email]]
Inviato: martedì 1 novembre 2005 15.00
A: [hidden email]
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 <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

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

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: [hidden email] [mailto:[hidden email]]
>Inviato: martedì 1 novembre 2005 15.00
>A: [hidden email]
>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.