The following code fragment from explain output illustrates the problem:

asql> explain insert into t values (0);
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
5     Integer        0     3     0                    00  NULL
6     Copy           3     5     0                    00  NULL
7     Function       0     5     4     typeof(1)      01  NULL
8     String8        0     6     0     real           00  NULL
9     Eq             6     11    4                    6a  NULL
10    Halt           19    2     0     constraint float failed  00  NULL
11    MakeRecord     3     1     6     e              00  NULL

The typeof function is called on a copy of the value, which is an integer, and 
causes the check constraint to fail, *before* the MakeRecord opcode would cause 
the integer value to be stored as a float.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [] Im 
Auftrag von Cecil Westerhof
Gesendet: Donnerstag, 07. September 2017 00:06
An: SQLite mailing list <>
Betreff: [EXTERNAL] Re: [sqlite] Should the INTEGER not be cast to a REAL

2017-09-06 23:58 GMT+02:00 R Smith <>:

> On 2017/09/06 11:37 PM, Cecil Westerhof wrote:
>> But should in the first case the 0 not be cast to a 0.0?
> What makes you believe SQLite should massage the data into specific
> types for you without you requesting it explicitly?

​At I read:
A column with REAL affinity behaves like a column with NUMERIC affinity except 
that it forces integer values into floating point representation.​

> In fact, that would consume valuable extra CPU cycles and would
> definitely make most of us unhappy.
> Your CHECK constraint should really find that the value is acceptable
> when it is either a REAL, OR an INT, because both those types of data
> satisfies your requirement.

​Because of the above I thought it not necessary.

I could change it of-course. The only thing could be if they enter am integer, 
then maybe they made a mistake.

Cecil Westerhof
sqlite-users mailing list

 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

sqlite-users mailing list

Reply via email to