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

[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] CHECK constraints

[Nemanja Corlija]

> Does the check constraint fail or not?  Or do different
> database engines do different things?
In Firebird 1.5 it does fail.

Though I agree with Darren's remarks, so not failing it seems to be
more flexible.

--
Nemanja Corlija <[EMAIL PROTECTED]>

RE: [sqlite] CHECK constraints

[Marcus Welz]

Indeed.

CREATE TABLE ex1(
x INTEGER,
y REAL,
CHECK(xmailto:[EMAIL PROTECTED] 
Sent: Wednesday, November 02, 2005 8:12 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] CHECK constraints

*snip*

At least in Oracle, no, your example insert works fine.  If you want
the insert to fail, you need to add a "not null" constraint as well.
Nulls are always allowed unless you have a not null constraint.

*snip*

-- 
Andrew Piskorski <[EMAIL PROTECTED]>
http://www.piskorski.com/

Re: [sqlite] CHECK constraints

[Andrew Piskorski]

On Wed, Nov 02, 2005 at 06:30:51PM -0500, [EMAIL PROTECTED] wrote:
> In a CHECK constraint, if the expression is NULL (neither true
> nor false) does the constraint fail?

At least in Oracle, no, your example insert works fine.  If you want
the insert to fail, you need to add a "not null" constraint as well.
Nulls are always allowed unless you have a not null constraint.

I'm not certain, but I believe this is standard behavior in all SQL
RDBMSs that support constraints, not just Oracle.

-- 
Andrew Piskorski <[EMAIL PROTECTED]>
http://www.piskorski.com/

RE: [sqlite] CHECK constraints

[rbundy]

My information is that MySQL does not enforce check constraints - your
testing supports this.

Regards.

rayB



|-+>
| |   "Marcus Welz"|
| |   <[EMAIL PROTECTED]|
| |   om>  |
| ||
| |   03/11/2005 11:59 |
| |   Please respond to|
| |   sqlite-users |
| ||
|-+>
  
>--|
  | 
 |
  |   To:      
 |
  |   cc:   
 |
  |   Subject:  RE: [sqlite] CHECK constraints  
 |
  
>--|




To be honest, I didn't expect that either. I guess with NULL meaning
"absence of a value" the logic here is that since it cannot determine the
value of NULL, it let's it pass. Why that was chosen over failing the check
doesn't make sense to me.

MySQL 3.23.58 gives a syntax error on the table definition.

MySQL 4.0.24 inserts the record fine -- but it also inserts (5, 4). i.e. it
seems to ignore the check altogether.


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 02, 2005 7:43 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] CHECK constraints

"Marcus Welz" <[EMAIL PROTECTED]> wrote:
> PostgreSQL 8.0 will happily insert (5, NULL).
>

Hmmm..  Not what I expected, nor what I implemented.
But the implementation is easily changed and there is
no point in trying to be "logical" about the behavior
of NULLs, I've learned.  I will probably modify SQLite
to conform to PostgreSQL unless there is a serious
outcry against this, or unless someone learns that
PostgreSQL intends to change their behavior...

I'm eager to hear what other RDBMSes do.

--
D. Richard Hipp <[EMAIL PROTECTED]>








** PLEASE CONSIDER OUR ENVIRONMENT BEFORE PRINTING *
*** Confidentiality and Privilege Notice ***

This e-mail is intended only to be read or used by the addressee. It is
confidential and may contain legally privileged information. If you are not
the addressee indicated in this message (or responsible for delivery of the
message to such person), you may not copy or deliver this message to anyone,
and you should destroy this message and kindly notify the sender by reply
e-mail. Confidentiality and legal privilege are not waived or lost by reason
of mistaken delivery to you.

Qantas Airways Limited
ABN 16 009 661 901

Visit Qantas online at http://qantas.com

Re: [sqlite] CHECK constraints

[Darren Duncan]

I'm not sure if this applies, but in my experience it is normal for a 
unique value constraint to be satisfied on columns with null values, 
as is a foreign key constraint, which is only evaluated on not-null 
values.  Following that precedent, I would say that the CHECK 
constraint should pass if its expression results to null.


If you're trying to enforce a certain kind of behaviour in a 
particular check constraint, you probably want to add some IS NULL 
expressions to explicitly declare the behaviour you want, to specify 
times when a null input would result in a check failure.


-- Darren Duncan

At 6:30 PM -0500 11/2/05, [EMAIL PROTECTED] wrote:

In a CHECK constraint, if the expression is NULL (neither true
nor false) does the constraint fail?

Example:

  CREATE TABLE ex1(
x INTEGER,
y REAL,
CHECK( x

RE: [sqlite] CHECK constraints

[Marcus Welz]

To be honest, I didn't expect that either. I guess with NULL meaning
"absence of a value" the logic here is that since it cannot determine the
value of NULL, it let's it pass. Why that was chosen over failing the check
doesn't make sense to me.

MySQL 3.23.58 gives a syntax error on the table definition.

MySQL 4.0.24 inserts the record fine -- but it also inserts (5, 4). i.e. it
seems to ignore the check altogether.


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, November 02, 2005 7:43 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] CHECK constraints

"Marcus Welz" <[EMAIL PROTECTED]> wrote:
> PostgreSQL 8.0 will happily insert (5, NULL).
> 

Hmmm..  Not what I expected, nor what I implemented.
But the implementation is easily changed and there is
no point in trying to be "logical" about the behavior
of NULLs, I've learned.  I will probably modify SQLite
to conform to PostgreSQL unless there is a serious
outcry against this, or unless someone learns that
PostgreSQL intends to change their behavior...

I'm eager to hear what other RDBMSes do.

--
D. Richard Hipp <[EMAIL PROTECTED]>

RE: [sqlite] CHECK constraints

[Preston Z]

MS SQL 2000 wil alsol insert (5, NULL)




From: "Marcus Welz" <[EMAIL PROTECTED]>
Reply-To: sqlite-users@sqlite.org
To: 
Subject: RE: [sqlite] CHECK constraints
Date: Wed, 2 Nov 2005 19:24:09 -0500

PostgreSQL 8.0 will happily insert (5, NULL).


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 02, 2005 6:31 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] CHECK constraints

In a CHECK constraint, if the expression is NULL (neither true
nor false) does the constraint fail?

Example:

  CREATE TABLE ex1(
x INTEGER,
y REAL,
CHECK( x

Re: [sqlite] CHECK constraints

[drh]

"Marcus Welz" <[EMAIL PROTECTED]> wrote:
> PostgreSQL 8.0 will happily insert (5, NULL).
> 

Hmmm..  Not what I expected, nor what I implemented.
But the implementation is easily changed and there is
no point in trying to be "logical" about the behavior
of NULLs, I've learned.  I will probably modify SQLite
to conform to PostgreSQL unless there is a serious
outcry against this, or unless someone learns that
PostgreSQL intends to change their behavior...

I'm eager to hear what other RDBMSes do.

--
D. Richard Hipp <[EMAIL PROTECTED]>

RE: [sqlite] CHECK constraints

[Marcus Welz]

PostgreSQL 8.0 will happily insert (5, NULL).


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, November 02, 2005 6:31 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] CHECK constraints

In a CHECK constraint, if the expression is NULL (neither true
nor false) does the constraint fail?

Example:

  CREATE TABLE ex1(
x INTEGER,
y REAL,
CHECK( x

[sqlite] CHECK constraints

[drh]

In a CHECK constraint, if the expression is NULL (neither true
nor false) does the constraint fail?

Example:

  CREATE TABLE ex1(
x INTEGER,
y REAL,
CHECK( x

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

[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

[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

[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] type confusion

[Jay Sprenkle]

> > Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
>
> > > So don't make the field 10 bytes long, make it only 8.  SQLite won't
> > > care a bit, and will give you the value in whatever format you want.
> >
> > Then it's not type agnostic any more. You now have an 8 byte numeric
> > and a 10 byte numeric. Which is no different than integer and real.
>
> Wrong, and obviously so.  I mean really, how many bytes LONG a value
> is must DETERMINE whether it represents an integer or a floating point
> number?  Must?  In what bizarre alternate universe is that true?

You're so garbled here I'm not sure what you're trying to say.


Here's the premise as I understood it:
"All division operations should be performed identically." From:

> > Am I alone in thinking that a division operator that does different
> > things depending on the declared datatype of a column is an
> > abomination?

Examing that proposal:

Some basic math theory:

 2 / 3
If your division result is an integer you lose precision.
2/3 = . -> 0. becomes zero when assigned to an integer.

Therefore: you must use floating point for all results since you're only
allowed to have one way to divide. We can't do conversions since that
breaks the premise of "one way to divide". The poster doesn't want
conversions.

Conclusion: In order to have one method for division, and not lose
precision, and not have conversions, you must use floating point
results for all numeric calculations.

You have to do conversions to a common type and possibly conversions
back to the destination type. Well, to be accurate, you could if you
have only one numeric type, floating point. Most Interpreted
languages, and sql engines, hide this basic fact by doing the
conversions invisibly, like: SELECT 5/'2' . (See end of message for
references)

If you have an untyped database or language it converts the operands
from variant to a numeric internally, then does the math, then
converts it back to a variant again.

I think the original poster's real complaint is that the coversions
weren't done automatically and it was too much effort for him to learn
to do it.



My response to the proposed "typeless database with automatic conversion":

Unless you can come up with a variant class as space efficient
as the types it replaces what's the advantage?

You use more storage (the variant represenation is larger)
and have a slower system  (longer retrieval because of more data +
slower calculations).

The choice of typing or not comes down to Efficiency versus Ease of programming.
I thought the basic idea of SQLite was to be fast and light. "Typeless
SQLite" seems to be a step backward from efficiency in both areas to
me.

I proposed splitting the project into two branches so people who wanted
standards compliance and the people who wanted ease of programming
could both have what they wanted. The suggestion was called "lame"
and "purist fetishism". Why does everyone insist on having only one
tool in their toolbox and trying to use it for everything even when it's not
suited for it? I don't suppose I should suggest an easy programming
vs an efficient version either? They're fundamentally different goals
and need different solutions.


>
> > > From SQLite's standpoint it is agnostic.  SQLite neither knows nor cares
> > > what is actually stored in the column; that's up to your application to
>
> > The only way for this to work will be to remove all mathematic
> > operations.  You can't make it agnostic of types if you have more
> > than one type and allow operations to be performed on the types.
>
> Again wrong.

You missed the assumption at the beginning of the thread.




>
> (Note that deciding to do math on values, even if you do it via the
> "+" operator in a SQL query, *IS* part of the application.  It's
> certainly not part of the data storage layer, at any rate.)

 SELECT 5/2;

Is not evaluated by the application. Maybe you meant to
say it "ought to be part of the application"? Or by "data layer"
you mean Sqlite?


>
> Jay, it's painful to see you put your foot in your mouth over and over
> again.  Please learn enough so that you stop sticking it in there.

I've been trying to not be unpleasant. I'm sorry you can't do the same.
Perhaps you should take more time to cool off before posting.
If you have a logical argument, rather than insults, I'm perfectly
willing to listen.
I think we're talking about different things here. I'm trying to understand your
point, but are you trying to understand mine?


>
> E.g., Tcl can be reasonably described as type agnostic, yet it can do
> math.  Since DRH is also a member of the Tcl Core Team, presumably Tcl
> was a design influence on SQLite.  It might be useful to look at it
> for comparison.

You missed the basic assumption that conversions aren't allowed.

References on conversion:

TCL:

The very first google result on "tcl arithmetic conversion" returns
someone complaining about conversions not working well:

"Abstract

This TIP 

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

[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.

[sqlite] type confusion

[Andrew Piskorski]

On Wed, Nov 02, 2005 at 10:45:54AM -0600, Jay Sprenkle wrote:
> Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

> > So don't make the field 10 bytes long, make it only 8.  SQLite won't
> > care a bit, and will give you the value in whatever format you want.
> 
> Then it's not type agnostic any more. You now have an 8 byte numeric
> and a 10 byte numeric. Which is no different than integer and real.

Wrong, and obviously so.  I mean really, how many bytes LONG a value
is must DETERMINE whether it represents an integer or a floating point
number?  Must?  In what bizarre alternate universe is that true?

> > From SQLite's standpoint it is agnostic.  SQLite neither knows nor cares
> > what is actually stored in the column; that's up to your application to

> The only way for this to work will be to remove all mathematic
> operations.  You can't make it agnostic of types if you have more
> than one type and allow operations to be performed on the types.

Again wrong.

(Note that deciding to do math on values, even if you do it via the
"+" operator in a SQL query, *IS* part of the application.  It's
certainly not part of the data storage layer, at any rate.)

Jay, it's painful to see you put your foot in your mouth over and over
again.  Please learn enough so that you stop sticking it in there.

E.g., Tcl can be reasonably described as type agnostic, yet it can do
math.  Since DRH is also a member of the Tcl Core Team, presumably Tcl
was a design influence on SQLite.  It might be useful to look at it
for comparison.

In Tcl, each function decides how to interpret its arguments, whether
as integers, strings, floats, or whatever.  But the Tcl runtime
storing, copying, or doing whatever else with those values basically
does NOT care about their "type".  You can assign any value to any
variable, etc.  Thus it makes sense to describe Tcl as "type
agnostic".  That seems to have certain parallels to SQLite's manifest
typing.

-- 
Andrew Piskorski <[EMAIL PROTECTED]>
http://www.piskorski.com/

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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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: 
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] Thanks for making my product possible

[Jay Sprenkle]

Seconded! Give yourself a raise! ;)

I've seen Sqlite popping up all over. I was particularly pleased to see it being
supported as a data source option for open office.


On 11/1/05, Edward Wilson <[EMAIL PROTECTED]> wrote:
> Ditto - sqlite is pure beauty - thanks.
>
> --- Clay Dowling <[EMAIL PROTECTED]> wrote:
> > Amid all the wailing and gnashing of teeth I thought that I'd just say
> > thanks for making a great embeddable database that puts a very minimal
> > burden on the developer.  My product, at least, would never have seen

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

[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: 
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

[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

[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

[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

[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

[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

[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

[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] basic question about sqlite

[Clay Dowling]

Edward Wilson said:
> What is the best approach to concurrency in this scenario?
>

Don't choke when you get a SQLITE_BUSY error.  Just wait and try again in
a second or so.

Clay Dowling
-- 
Simple Content Management
http://www.ceamus.com

Re: [sqlite] basic question about sqlite

[Edward Wilson]

What is the best approach to concurrency in this scenario?

--- Clay Dowling <[EMAIL PROTECTED]> wrote:

> 
> Dave Dyer said:
> >
> > If I designed a sqlite database to be used by a bunch of independent
> > applications, for example a bunch of CGI scripts each of which
> > opened the database, did some processing, and exited, would
> > that be (a) safe (b) effecient ?
> 
> It's very safe.  My own product does that (see the tag line).  In many
> ways it's more efficient than using a database server, since opening a
> file is usually faster than opening a network connection to a database
> server.
> 
> Clay
> -- 
> Simple Content Management
> http://www.ceamus.com
> 
> 




__ 
Start your day with Yahoo! - Make it your home page! 
http://www.yahoo.com/r/hs

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

[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

[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

[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

[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] basic question about sqlite

[Clay Dowling]

Dave Dyer said:
>
> If I designed a sqlite database to be used by a bunch of independent
> applications, for example a bunch of CGI scripts each of which
> opened the database, did some processing, and exited, would
> that be (a) safe (b) effecient ?

It's very safe.  My own product does that (see the tag line).  In many
ways it's more efficient than using a database server, since opening a
file is usually faster than opening a network connection to a database
server.

Clay
-- 
Simple Content Management
http://www.ceamus.com

Re: [sqlite] SQLite as a Windows kernel tool

[Russ Freeman]

What about these three warnings do you think is a concern?
Have you actually looked at the code in question to see
if the possibility of data loss is real and unintentional?
What makes you think that these warnings are not just a case
of the compiler blowing smoke?

--
D. Richard Hipp <[EMAIL PROTECTED]>

I may be a bit late in the discussion but...

Personally I like to use the compilers highest level of warnings - and 
use "warnings as errors" where possible. If I'm feeling really 
enthusiastic then I may run Lint over the code. These tools, lint and 
compiler warnings, are there to help us.


Let's assume one warning is a valid one. It's a new one in code someone 
wrote just recently. It's a warning that is now lost in the noise of the 
"blowing smoke" warnings. You'll never know because you have tuned the 
compilers warnings out as simply "blowing smoke". You won't see it until 
an obscure bug shows it's face...and even when staring at it you won't 
see it because you don't consider these warnings as errors.


Personally I consider an alarm and "alarm" and work to fix them all. 
Sometimes with compiler options (pragmas in MS compiler), sometimes with 
lint comments, but mostly by fixing them **right at the time of writing 
the code in the first place**.


It saddens me when I use someone else's code at warning level 3 and 
warnings as mere warnings as there is the implication that the code has 
not had that last few ounces of effort put into it.


russ.

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

[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

[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

[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

[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

[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] basic question about sqlite

[John Stanton]

Yes to both questions.  Just make sure that your application can handle 
the situation of occasionally finding the database locked momentarily.
In our tests on an old 200MHz server such a CGI process executes in 10mS 
or less and would permit maybe as many as 200 simultaneous users.

JS

Dave Dyer wrote:

If I designed a sqlite database to be used by a bunch of independent
applications, for example a bunch of CGI scripts each of which
opened the database, did some processing, and exited, would
that be (a) safe (b) effecient ?

Re: [sqlite] Re: Number of rows in a query result

[John Stanton]

No DB product can give you the number of rows in a result set until the 
search has been completed.  The Sqlite callback and step approach gives 
the application the ability to process the query result as the query 
finds each row, avoiding the need for intermediate storage, a most 
efficient and robust approach.  You should take advantage of it, not 
fight it.


Edward Wilson wrote:

What I was trying to say was: with other db products the drivers (or something 
somewhere)
calculated the number of rows returned in a query automagicly.  I have never 
had to do anything
'extra' to get the number of rows in a query other than 
resultset-object.rowcout - 'rowcout' being
whatever the syntax was for that particular environment.  So what I meant was, 
I have always taken
for granted that the rowcount was 'apart of' the query returned from the 
database and not
something that I had to do 'extra' in addition to fetching the data to begin 
with.  I hope this
was clear.

-
ed

--- Puneet Kishor <[EMAIL PROTECTED]> wrote:



On Oct 31, 2005, at 7:54 PM, Edward Wilson wrote:



I simply count the number of elements in my record set
thereby avoiding a double query to the database.


Yes, exactly, I take for granted that the resultset is accumulated at 
the database level and not

at the application level.


sorry, I don't quite understand what you imply by the above. Obviously 
this discussion stems from the fact that you can't take that for 
granted, at least not without paying some cost for it. Because I don't 
want to tie up the db doing double queries, I just do it in the 
application.







-
ed

--- Puneet Kishor <[EMAIL PROTECTED]> wrote:



On Oct 28, 2005, at 7:20 PM, SRS wrote:



Edward Wilson wrote:



The idea of issuing two selects is, well, a hack, and knowing how
many records one has in a
result-set is a powerful feature



Are you needing a progress bar for the search (ie the query?) Or some
action based on the result set?  If the later, get the result set as
your favorite container.. ask the container the size.  If its the
first then a "feature" won't help.  It still has to 'run' the query 
in
order to get the count.  It would be like me asking you to tell me 
how

many red Skittles are in a package before you open it. As for being a
'hack' .. all your 'feature' would be is a pretty programming
interface around that hack.  As I said before, how can the database
know the number of items that will be returned without first 
searching

for them.



I think the problem is not so much (at least IMHO) that two queries
have to be performed (that itself is a reasonable expectation), but
that the COUNT(*) query is likely to be slow because of the full table
scan. One option is to use an aftermarket solution... for example, in
my Perl applications once I have queried the db for the columns based
on my criteria, I simply count the number of elements in my record set
thereby avoiding a double query to the database. Although, in reality,
I personally don't mind the COUNT(*) option... none of my databases 
are

that large to merit worrying about this.







__
Start your day with Yahoo! - Make it your home page!
http://www.yahoo.com/r/hs








__ 
Start your day with Yahoo! - Make it your home page! 
http://www.yahoo.com/r/hs

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

[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

[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] Re: Number of rows in a query result

[Paolo Vernazza]

Edward Wilson wrote:


What I was trying to say was: with other db products the drivers (or something 
somewhere)
calculated the number of rows returned in a query automagicly.  I have never 
had to do anything
'extra' to get the number of rows in a query other than 
resultset-object.rowcout - 'rowcout' being
whatever the syntax was for that particular environment.  So what I meant was, 
I have always taken
for granted that the rowcount was 'apart of' the query returned from the 
database and not
something that I had to do 'extra' in addition to fetching the data to begin 
with.  I hope this
was clear.

The other DB, usually, perform the query, retrive all the rows and 
stores them somewere. When you get the number of rows in your query, the 
query had been completly executed.


SQLite does the same. Your resulset is called "table" and you can get it 
using sqlite3_get_table("SELECT bla bla bla bla bla").


But Sqlite permits *also* to retrive the data as soon as they are 
calculated. This permits to save memory (no one has to store somewhere 
the resultset) and to process the results while the query is executed.


Paolo

RE: [sqlite] Page size problem

[Cariotoglou Mike]

FYI, I tried the same script on windows xp2 sqlite 3.2.7, and it worked
fine also 

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, November 01, 2005 7:13 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Page size problem
> 
> "Anton Kuznetsov" <[EMAIL PROTECTED]> wrote:
> > Hello!
> > 
> > Did anybody try to create an SQLite3 database with a custom 
> page_size (e.g.
> > 8192) and fill it with data of more than 1Gb? As for me I didn't 
> > manage (using tclsqlite-3.2.7). It says "database disk 
> image is malformed".
> > 
> 
> I just testing the script shown below.  It generates a 2 GiB 
> database that seems to work fine.
> 

Re: [sqlite] 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: [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

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