[sqlite] RE: string affinity mode

[Voronkov, Basil]

Guys,

How we can set a strict affinity mode in sqlite3?

 

Thanks

RE: [sqlite] Unlucky number for the ROUND function

[D. Richard Hipp]

On Tue, 2005-08-30 at 15:04 -0400, Ned Batchelder wrote:

> As near as I can tell, this line in printf.c (line 445):
> 
>   while( realvalue>10.0 && exp<=350 ){ realvalue *= 0.1; exp++; }
> 
> should be:
> 
>   while( realvalue>=10.0 && exp<=350 ){ realvalue *= 0.1; exp++; }
> 

Thanks.  This is the problem and it is being fixed now.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

RE: [sqlite] Unlucky number for the ROUND function

[Ned Batchelder]

One more thing: this seems to happen because realvalue is
9.9493, and rounder is 0.050003, and adding them
together produces exactly 10.000!  Other platforms or libraries
may be producing slightly different values which ended up being a hair less
than 10 or a hair more.  An unlucky value indeed!  You flipped a quarter,
and it landed on the edge.

--Ned.
http://nedbatchelder.com
 

-Original Message-
From: Ned Batchelder [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, 30 August, 2005 3:04 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Unlucky number for the ROUND function

I reproduced it here as well.

As near as I can tell, this line in printf.c (line 445):

  while( realvalue>10.0 && exp<=350 ){ realvalue *= 0.1; exp++; }

should be:

  while( realvalue>=10.0 && exp<=350 ){ realvalue *= 0.1; exp++; }

The comment at line 440 says it's trying to make realvalue be less than 10.
Changing the line makes the round example given produce the right answer.

The colon in the wrong answer is because et_getdigit is being passed 10.0 as
the real value, and returing '0'+10 as the digit, which is ':'.

--Ned.
http://nedbatchelder.com
 

-Original Message-
From: Bob Dankert [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, 30 August, 2005 2:35 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Unlucky number for the ROUND function

Using the sqlite.exe 3.2.5 binary off the website with WinXP SP2 here, I
get the ":.0" result (less the quotes).  I have tried this on a couple
machines in the office here running similar environments.  

Bob

Envision Information Technologies
Associate
[EMAIL PROTECTED]
v. 608.256.5680
f. 608.256.3780
 

-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 30, 2005 1:08 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Unlucky number for the ROUND function

On Tue, 2005-08-30 at 12:46 -0500, Bob Dankert wrote:
> According to that, it rounds to the nearest even number.  Shouldn't
9.95
> go to 10 then, and 9.85 go to 9.8?
> 
> After additional testing with SQLite 3.2.2, I have the following
> results:
> 
> Round(9.95,1)  -> 9.9*Rounded Down*
> Round(9.85,1)  -> 9.8*Rounded Down*
> Round(9.5,0)   -> 10 *Rounded Up*
> Round(9.995,2) -> 9.99   *Rounded Down*
> 
> I really see no pattern or sense to the results.
> 

The reason you see no pattern is because you are thinking in
decimal whereas SQLite thinks in binary.

The number 9.95 does not a have finite representation in
binary.  The closest you can get with a 64-bit IEEE float
is:

   9.949289457264239899814128875732421875

So when you type "9.95" into an SQL statement, SQLite really
inserts the number shown above, not 9.95.  And the number 
shown above rounds down.

9.5 does have an exact representation in binary so it rounds
as you would expect.  But neither 9.85 nor 9.995 do - the
binary values chosen to represent them are both just a little
less than their decimal values.  Hence they both round
down.

So I'm not overly worried when I see round(9.95,1) come out
with 9.9.  But I am concerned about the people who are seeing
results like ":.0".  I wish I could reproduce that problem.

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

RE: [sqlite] Unlucky number for the ROUND function

[Ned Batchelder]

I reproduced it here as well.

As near as I can tell, this line in printf.c (line 445):

  while( realvalue>10.0 && exp<=350 ){ realvalue *= 0.1; exp++; }

should be:

  while( realvalue>=10.0 && exp<=350 ){ realvalue *= 0.1; exp++; }

The comment at line 440 says it's trying to make realvalue be less than 10.
Changing the line makes the round example given produce the right answer.

The colon in the wrong answer is because et_getdigit is being passed 10.0 as
the real value, and returing '0'+10 as the digit, which is ':'.

--Ned.
http://nedbatchelder.com
 

-Original Message-
From: Bob Dankert [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, 30 August, 2005 2:35 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Unlucky number for the ROUND function

Using the sqlite.exe 3.2.5 binary off the website with WinXP SP2 here, I
get the ":.0" result (less the quotes).  I have tried this on a couple
machines in the office here running similar environments.  

Bob

Envision Information Technologies
Associate
[EMAIL PROTECTED]
v. 608.256.5680
f. 608.256.3780
 

-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 30, 2005 1:08 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Unlucky number for the ROUND function

On Tue, 2005-08-30 at 12:46 -0500, Bob Dankert wrote:
> According to that, it rounds to the nearest even number.  Shouldn't
9.95
> go to 10 then, and 9.85 go to 9.8?
> 
> After additional testing with SQLite 3.2.2, I have the following
> results:
> 
> Round(9.95,1)  -> 9.9*Rounded Down*
> Round(9.85,1)  -> 9.8*Rounded Down*
> Round(9.5,0)   -> 10 *Rounded Up*
> Round(9.995,2) -> 9.99   *Rounded Down*
> 
> I really see no pattern or sense to the results.
> 

The reason you see no pattern is because you are thinking in
decimal whereas SQLite thinks in binary.

The number 9.95 does not a have finite representation in
binary.  The closest you can get with a 64-bit IEEE float
is:

   9.949289457264239899814128875732421875

So when you type "9.95" into an SQL statement, SQLite really
inserts the number shown above, not 9.95.  And the number 
shown above rounds down.

9.5 does have an exact representation in binary so it rounds
as you would expect.  But neither 9.85 nor 9.995 do - the
binary values chosen to represent them are both just a little
less than their decimal values.  Hence they both round
down.

So I'm not overly worried when I see round(9.95,1) come out
with 9.9.  But I am concerned about the people who are seeing
results like ":.0".  I wish I could reproduce that problem.

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

Re: [sqlite] Unlucky number for the ROUND function

[Robert Simpson]

It appears to be a problem with the sqlite3.exe itself and not the sqlite3 
DLL.  SELECT ROUND(9.95, 1); from the command-line util gives me :.0, but 
executing the statement externally gives me 9.9


Robert

- Original Message - 
From: "Bob Dankert" <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, August 30, 2005 11:35 AM
Subject: RE: [sqlite] Unlucky number for the ROUND function


Using the sqlite.exe 3.2.5 binary off the website with WinXP SP2 here, I
get the ":.0" result (less the quotes).  I have tried this on a couple
machines in the office here running similar environments.

Bob

Envision Information Technologies
Associate
[EMAIL PROTECTED]
v. 608.256.5680
f. 608.256.3780


-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 30, 2005 1:08 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Unlucky number for the ROUND function

On Tue, 2005-08-30 at 12:46 -0500, Bob Dankert wrote:

According to that, it rounds to the nearest even number.  Shouldn't

9.95

go to 10 then, and 9.85 go to 9.8?

After additional testing with SQLite 3.2.2, I have the following
results:

Round(9.95,1)  -> 9.9*Rounded Down*
Round(9.85,1)  -> 9.8*Rounded Down*
Round(9.5,0)   -> 10 *Rounded Up*
Round(9.995,2) -> 9.99   *Rounded Down*

I really see no pattern or sense to the results.



The reason you see no pattern is because you are thinking in
decimal whereas SQLite thinks in binary.

The number 9.95 does not a have finite representation in
binary.  The closest you can get with a 64-bit IEEE float
is:

  9.949289457264239899814128875732421875

So when you type "9.95" into an SQL statement, SQLite really
inserts the number shown above, not 9.95.  And the number
shown above rounds down.

9.5 does have an exact representation in binary so it rounds
as you would expect.  But neither 9.85 nor 9.995 do - the
binary values chosen to represent them are both just a little
less than their decimal values.  Hence they both round
down.

So I'm not overly worried when I see round(9.95,1) come out
with 9.9.  But I am concerned about the people who are seeing
results like ":.0".  I wish I could reproduce that problem.

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

RE: [sqlite] Unlucky number for the ROUND function

[Bob Dankert]

Using the sqlite.exe 3.2.5 binary off the website with WinXP SP2 here, I
get the ":.0" result (less the quotes).  I have tried this on a couple
machines in the office here running similar environments.  

Bob

Envision Information Technologies
Associate
[EMAIL PROTECTED]
v. 608.256.5680
f. 608.256.3780
 

-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 30, 2005 1:08 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Unlucky number for the ROUND function

On Tue, 2005-08-30 at 12:46 -0500, Bob Dankert wrote:
> According to that, it rounds to the nearest even number.  Shouldn't
9.95
> go to 10 then, and 9.85 go to 9.8?
> 
> After additional testing with SQLite 3.2.2, I have the following
> results:
> 
> Round(9.95,1)  -> 9.9*Rounded Down*
> Round(9.85,1)  -> 9.8*Rounded Down*
> Round(9.5,0)   -> 10 *Rounded Up*
> Round(9.995,2) -> 9.99   *Rounded Down*
> 
> I really see no pattern or sense to the results.
> 

The reason you see no pattern is because you are thinking in
decimal whereas SQLite thinks in binary.

The number 9.95 does not a have finite representation in
binary.  The closest you can get with a 64-bit IEEE float
is:

   9.949289457264239899814128875732421875

So when you type "9.95" into an SQL statement, SQLite really
inserts the number shown above, not 9.95.  And the number 
shown above rounds down.

9.5 does have an exact representation in binary so it rounds
as you would expect.  But neither 9.85 nor 9.995 do - the
binary values chosen to represent them are both just a little
less than their decimal values.  Hence they both round
down.

So I'm not overly worried when I see round(9.95,1) come out
with 9.9.  But I am concerned about the people who are seeing
results like ":.0".  I wish I could reproduce that problem.

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

RE: [sqlite] Unlucky number for the ROUND function

[D. Richard Hipp]

On Tue, 2005-08-30 at 12:46 -0500, Bob Dankert wrote:
> According to that, it rounds to the nearest even number.  Shouldn't 9.95
> go to 10 then, and 9.85 go to 9.8?
> 
> After additional testing with SQLite 3.2.2, I have the following
> results:
> 
> Round(9.95,1)  -> 9.9*Rounded Down*
> Round(9.85,1)  -> 9.8*Rounded Down*
> Round(9.5,0)   -> 10 *Rounded Up*
> Round(9.995,2) -> 9.99   *Rounded Down*
> 
> I really see no pattern or sense to the results.
> 

The reason you see no pattern is because you are thinking in
decimal whereas SQLite thinks in binary.

The number 9.95 does not a have finite representation in
binary.  The closest you can get with a 64-bit IEEE float
is:

   9.949289457264239899814128875732421875

So when you type "9.95" into an SQL statement, SQLite really
inserts the number shown above, not 9.95.  And the number 
shown above rounds down.

9.5 does have an exact representation in binary so it rounds
as you would expect.  But neither 9.85 nor 9.995 do - the
binary values chosen to represent them are both just a little
less than their decimal values.  Hence they both round
down.

So I'm not overly worried when I see round(9.95,1) come out
with 9.9.  But I am concerned about the people who are seeing
results like ":.0".  I wish I could reproduce that problem.

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

Re: [sqlite] Unlucky number for the ROUND function

[Kervin L. Pierre]

Do numbers with a final digit of five and
rounding precision greater than zero ever
round up?

Bob Dankert wrote:

According to that, it rounds to the nearest even number.  Shouldn't 9.95
go to 10 then, and 9.85 go to 9.8?

After additional testing with SQLite 3.2.2, I have the following
results:

Round(9.95,1)  -> 9.9*Rounded Down*
Round(9.85,1)  -> 9.8*Rounded Down*
Round(9.5,0)   -> 10 *Rounded Up*
Round(9.995,2) -> 9.99   *Rounded Down*

I really see no pattern or sense to the results.

Bob

Envision Information Technologies
Associate
[EMAIL PROTECTED]
v. 608.256.5680
f. 608.256.3780
 


-Original Message-
From: Kervin L. Pierre [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 30, 2005 12:18 PM

To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Unlucky number for the ROUND function


Interesting...

Bankers' Rounding
http://blogs.msdn.com/ericlippert/archive/2003/09/26/53107.aspx

Brass Tilde wrote:


From: "Bob Dankert"




Using an older version which I compiled, I get 9.9, though it
seems it should round up to 10.0?



This may be dependent upon the math library linked into the


application


by the compiler used to build SQLite.  Some libraries appear to now be
using so-called "banking rounding" (though there are other names),


where


a "5" digit is rounded up or down depending upon the digit immediately
to its left.  If that digit is odd, it rounds one way, if even, the
other.  It looks like in this case, 9.95 would round to 9.9, while


9.85


would likely round to 9.9 as well.  Try rounding 9.85 and see what you
get.

Brad






.

RE: [sqlite] Unlucky number for the ROUND function

[Bob Dankert]

According to that, it rounds to the nearest even number.  Shouldn't 9.95
go to 10 then, and 9.85 go to 9.8?

After additional testing with SQLite 3.2.2, I have the following
results:

Round(9.95,1)  -> 9.9*Rounded Down*
Round(9.85,1)  -> 9.8*Rounded Down*
Round(9.5,0)   -> 10 *Rounded Up*
Round(9.995,2) -> 9.99   *Rounded Down*

I really see no pattern or sense to the results.

Bob

Envision Information Technologies
Associate
[EMAIL PROTECTED]
v. 608.256.5680
f. 608.256.3780
 

-Original Message-
From: Kervin L. Pierre [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 30, 2005 12:18 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Unlucky number for the ROUND function


Interesting...

Bankers' Rounding
http://blogs.msdn.com/ericlippert/archive/2003/09/26/53107.aspx

Brass Tilde wrote:
>>From: "Bob Dankert"
> 
> 
>>Using an older version which I compiled, I get 9.9, though it
>>seems it should round up to 10.0?
> 
> 
> This may be dependent upon the math library linked into the
application
> by the compiler used to build SQLite.  Some libraries appear to now be
> using so-called "banking rounding" (though there are other names),
where
> a "5" digit is rounded up or down depending upon the digit immediately
> to its left.  If that digit is odd, it rounds one way, if even, the
> other.  It looks like in this case, 9.95 would round to 9.9, while
9.85
> would likely round to 9.9 as well.  Try rounding 9.85 and see what you
> get.
> 
> Brad
> 
> 
> 
> 
> 
> 
> .
> 

Re: [sqlite] Unlucky number for the ROUND function

[Kervin L. Pierre]

Interesting...

Bankers' Rounding
http://blogs.msdn.com/ericlippert/archive/2003/09/26/53107.aspx

Brass Tilde wrote:

From: "Bob Dankert"




Using an older version which I compiled, I get 9.9, though it
seems it should round up to 10.0?



This may be dependent upon the math library linked into the application
by the compiler used to build SQLite.  Some libraries appear to now be
using so-called "banking rounding" (though there are other names), where
a "5" digit is rounded up or down depending upon the digit immediately
to its left.  If that digit is odd, it rounds one way, if even, the
other.  It looks like in this case, 9.95 would round to 9.9, while 9.85
would likely round to 9.9 as well.  Try rounding 9.85 and see what you
get.

Brad






.

Re: [sqlite] Unlucky number for the ROUND function

[Brass Tilde]

> From: "Bob Dankert"

> Using an older version which I compiled, I get 9.9, though it
> seems it should round up to 10.0?

This may be dependent upon the math library linked into the application
by the compiler used to build SQLite.  Some libraries appear to now be
using so-called "banking rounding" (though there are other names), where
a "5" digit is rounded up or down depending upon the digit immediately
to its left.  If that digit is odd, it rounds one way, if even, the
other.  It looks like in this case, 9.95 would round to 9.9, while 9.85
would likely round to 9.9 as well.  Try rounding 9.85 and see what you
get.

Brad

Re: [sqlite] Unlucky number for the ROUND function

[Eugene Wee]

Hi,

On Windows XP testing for SQLite 3.2.5:
:.0 when using the sqlite3 executable downloaded
9.9 when running a program linked to an SQlite3 static library compiled by the 
MinGW port of GCC 3.4.2


Eugene Wee

Edzard Pasma wrote:

I found a number where the ROUND () function goes wrong:

SQLite version 3.2.5
Enter ".help" for instructions
sqlite> select round (9.95, 1);
:.0 


Sorry, it must be that I have bad luck today

Edzard Pasma

_
Tired of spam and viruses? Get a VolcanoMail account with SpamShield Pro and 
Anti-Virus technology! http://www.volcanomail.com/

RE: [sqlite] Unlucky number for the ROUND function

[Thomas Briggs]

   I can reproduce this behavior using 3.2.5 compiled with MS VC++ 7.

   3.2.2 didn't produce consistent behavior cross-platform when rounding
numbers that ended with .5, so yes, it should have rounded up to 10.0.
:)

   -Tom

> -Original Message-
> From: Bob Dankert [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, August 30, 2005 12:02 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Unlucky number for the ROUND function
> 
> Using the downloaded command-line tool for 3.2.5, I get the same :.0
> result.  Using an older version which I compiled, I get 9.9, though it
> seems it should round up to 10.0?
> 
> Bob
> 
> Envision Information Technologies
> Associate
> [EMAIL PROTECTED]
> v. 608.256.5680
> f. 608.256.3780
>  
> 
> -Original Message-
> From: Eric Bohlman [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, August 30, 2005 11:05 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Unlucky number for the ROUND function
> 
> Edzard Pasma wrote:
> > I found a number where the ROUND () function goes wrong:
> > 
> > SQLite version 3.2.5
> > Enter ".help" for instructions
> > sqlite> select round (9.95, 1);
> > :.0 
> 
> I get 9.9 (running on Win98, compiled with MingW).
> 

Re: [sqlite] Unlucky number for the ROUND function

[Keith Herold]

2.8.15 gives 9.9

--Keith

On 8/30/05, Bob Dankert <[EMAIL PROTECTED]> wrote:
> 
> Using the downloaded command-line tool for 3.2.5, I get the same :.0
> result. Using an older version which I compiled, I get 9.9, though it
> seems it should round up to 10.0?
> 
> Bob
> 
> Envision Information Technologies
> Associate
> [EMAIL PROTECTED]
> v. 608.256.5680
> f. 608.256.3780
> 
> 
> -Original Message-
> From: Eric Bohlman [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, August 30, 2005 11:05 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Unlucky number for the ROUND function
> 
> Edzard Pasma wrote:
> > I found a number where the ROUND () function goes wrong:
> >
> > SQLite version 3.2.5
> > Enter ".help" for instructions
> > sqlite> select round (9.95, 1);
> > :.0
> 
> I get 9.9 (running on Win98, compiled with MingW).
>

RE: [sqlite] Unlucky number for the ROUND function

[Bob Dankert]

Using the downloaded command-line tool for 3.2.5, I get the same :.0
result.  Using an older version which I compiled, I get 9.9, though it
seems it should round up to 10.0?

Bob

Envision Information Technologies
Associate
[EMAIL PROTECTED]
v. 608.256.5680
f. 608.256.3780
 

-Original Message-
From: Eric Bohlman [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 30, 2005 11:05 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Unlucky number for the ROUND function

Edzard Pasma wrote:
> I found a number where the ROUND () function goes wrong:
> 
> SQLite version 3.2.5
> Enter ".help" for instructions
> sqlite> select round (9.95, 1);
> :.0 

I get 9.9 (running on Win98, compiled with MingW).

Re: [sqlite] Unlucky number for the ROUND function

[Eric Bohlman]

Edzard Pasma wrote:

I found a number where the ROUND () function goes wrong:

SQLite version 3.2.5
Enter ".help" for instructions
sqlite> select round (9.95, 1);
:.0 


I get 9.9 (running on Win98, compiled with MingW).

[sqlite] Unlucky number for the ROUND function

[Edzard Pasma]

I found a number where the ROUND () function goes wrong:

SQLite version 3.2.5
Enter ".help" for instructions
sqlite> select round (9.95, 1);
:.0 

Sorry, it must be that I have bad luck today

Edzard Pasma

_
Tired of spam and viruses? Get a VolcanoMail account with SpamShield Pro and 
Anti-Virus technology! http://www.volcanomail.com/

[sqlite] ERROR: internal SQLite implementation flaw

[Joga Singh]

Hi Guys,
I am using sqlite 3.0.8 on LInux.

When I execute the following sql using 'sqlite3_get_table' api, I am getting 
'internal SQLite implementation flaw' error. This used to work fine earlier.

select * from users where usrid='admin' and passwd='admin'


Any ideas?? 
Same is working fine in 'sqlite3' command line utility.

Thanks & Regards:

JS