> On 2/11/2018, at 8:23 AM, James K. Lowden <jklow...@schemamania.org> wrote:
> 
> On Wed, 31 Oct 2018 23:05:19 -0300
> Bernardo Sulzbach <berna...@bernardosulzbach.com> wrote:
> 
>> So if you are inserting integers
>> into a real column, you are going to store integers. However, when you
>> select from it they are presented as reals and mix up (looking as if
>> there were duplicates [...]
> 
> I don't think that explanation holds water.  
> 
> sqlite> 
> DROP TABLE IF EXISTS TestReal;CREATE TABLE TestReal(A REAL
> UNIQUE);INSERT INTO TestReal values (9223372036854775807);INSERT INTO
> TestReal values (9223372036854775807 - 1);INSERT INTO TestReal values
> (9223372036854775807 - 2);INSERT INTO TestReal values
> (9223372036854775807 - 3);sqlite>    ...>    ...>    ...>    ...> 
> 
> sqlite> select cast(A as integer) from TestReal;
> 9223372036854775807
> 9223372036854775807
> 9223372036854775807
> 9223372036854775807
> 
> sqlite> select hex(A) from TestReal;
> 392E3232333337323033363835343738652B3138
> 392E3232333337323033363835343738652B3138
> 392E3232333337323033363835343738652B3138
> 392E3232333337323033363835343738652B3138
> 
> sqlite> select count(*), hex(A) from TestReal group by hex(A);
> 4|392E3232333337323033363835343738652B3138
> 
> sqlite> .schema TestReal
> CREATE TABLE TestReal(A REAL
> UNIQUE);
> sqlite> 
> 
> Curiouser and curiouser.  

Continuing with James’s test case above, the following shows that the integer 
values are actually stored in the database as real.

select A,typeof(A) from TestReal;
9.22337203685478e+18|real
9.22337203685478e+18|real
9.22337203685478e+18|real
9.22337203685478e+18|real

Testing with smaller values:

INSERT INTO TestReal values(1); INSERT INTO TestReal values(1);
Error: UNIQUE constraint failed: TestReal.A

That did what I expected.

SELECT cast(A as integer) from TestReal;
9223372036854775807
9223372036854775807
9223372036854775807
9223372036854775807
1

INSERT INTO TestReal values(9223372036854775807);
Error: UNIQUE constraint failed: TestReal.A

The UNIQUE constraint works if the integer exactly matches the real-to-integer 
conversion of existing values in the column.

INSERT INTO TestReal values(9223372036854775807+1);

No error this time. The UNIQUE constraint didn’t pick that 
(9223372036854775807+1) will match existing values in the column when converted 
to real.

SELECT cast(A as integer) from TestReal;
9223372036854775807
9223372036854775807
9223372036854775807
9223372036854775807
1
9223372036854775807

Therefore it looks like the UNIQUE test is being done using the type of the 
value being inserted, rather than the type that will be stored in the column.


_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to