> 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
