Re: [sqlite] SQLite4 key encoding bug

[Richard Hipp]

On Tue, Jul 10, 2012 at 11:54 AM, Igor Tandetnik wrote:

> On 7/10/2012 11:44 AM, Richard Hipp wrote:
>
>> SQLite4 is still using "double" internally for floating point
>> computations.  (Yes, there are plans to change that but it has not
>> happened
>> yet.)  The bug above is due to rounding errors in the doubles,
>> specifically
>> rounding errors that occur when doing the key encoding.
>>
>
> Both 123.0 and 12323.0 should be exactly representable in a double, as far
> as I can tell.


They are.  The rounding error occurs in my code that converts them into the Key
Encoding .


>
> --
> Igor Tandetnik
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite4 key encoding bug

[Igor Tandetnik]

On 7/10/2012 11:44 AM, Richard Hipp wrote:

SQLite4 is still using "double" internally for floating point
computations.  (Yes, there are plans to change that but it has not happened
yet.)  The bug above is due to rounding errors in the doubles, specifically
rounding errors that occur when doing the key encoding.


Both 123.0 and 12323.0 should be exactly representable in a double, as 
far as I can tell.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite4 key encoding bug

[Richard Hipp]

On Tue, Jul 10, 2012 at 11:33 AM, Igor Tandetnik wrote:

> On 7/10/2012 11:24 AM, Ryan Johnson wrote:
>
>> On 10/07/2012 9:58 AM,
>> bardzotajneko...@interia.pl wrote:
>>
>>> SQLite version 4.0.0 2012-07-07 12:21:48
>>> Enter ".help" for instructions
>>> Enter SQL statements terminated with a ";"
>>> sqlite> create table t(a);
>>> sqlite> insert into t values(123);
>>> sqlite> insert into t values(123.0);
>>> sqlite> insert into t values(12323);
>>> sqlite> insert into t values(12323.0);
>>> sqlite> select * from t group by 1;
>>> 123.0
>>> 12323
>>> 12323.0
>>> sqlite>
>>>
>> This is correct: 123 != 123.0 != "123" because they all have different
>> types (int/float/string).
>>
>
> Could you explain why 123 and 123.0 got grouped together, but 12323 and
> 12323.0 did not? Shouldn't the same logic apply? It's this inconsistency
> that looks qutie suspicious.
>

SQLite4 is still using "double" internally for floating point
computations.  (Yes, there are plans to change that but it has not happened
yet.)  The bug above is due to rounding errors in the doubles, specifically
rounding errors that occur when doing the key encoding.


> --
> Igor Tandetnik
>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite4 key encoding bug

[Igor Tandetnik]

On 7/10/2012 11:24 AM, Ryan Johnson wrote:

On 10/07/2012 9:58 AM,
bardzotajneko...@interia.pl wrote:

SQLite version 4.0.0 2012-07-07 12:21:48
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t(a);
sqlite> insert into t values(123);
sqlite> insert into t values(123.0);
sqlite> insert into t values(12323);
sqlite> insert into t values(12323.0);
sqlite> select * from t group by 1;
123.0
12323
12323.0
sqlite>

This is correct: 123 != 123.0 != "123" because they all have different
types (int/float/string).


Could you explain why 123 and 123.0 got grouped together, but 12323 and 
12323.0 did not? Shouldn't the same logic apply? It's this inconsistency 
that looks qutie suspicious.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite4 key encoding bug

[Ryan Johnson]

On 10/07/2012 9:58 AM, bardzotajneko...@interia.pl wrote:

SQLite version 4.0.0 2012-07-07 12:21:48
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t(a);
sqlite> insert into t values(123);
sqlite> insert into t values(123.0);
sqlite> insert into t values(12323);
sqlite> insert into t values(12323.0);
sqlite> select * from t group by 1;
123.0
12323
12323.0
sqlite>
This is correct: 123 != 123.0 != "123" because they all have different 
types (int/float/string). Either create the table with typed columns to 
encourage all values to have the same type [1] or cast the group by key 
to coerce all values to the same type (not sure what happens if the type 
cast fails).


[1] See the SQLite docs for rules about automatic type conversions for 
typed columns, it's not a panacea.


Ryan

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users