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 [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Cecil Westerhof Gesendet: Donnerstag, 07. September 2017 00:06 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] Re: [sqlite] Should the INTEGER not be cast to a REAL 2017-09-06 23:58 GMT+02:00 R Smith <rsm...@rsweb.co.za>: > 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 https://www.sqlite.org/datatype3.html 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ 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 E-Mail: h...@scigames.at 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
