Re: [sqlite] Should the INTEGER not be cast to a REAL
sqlite> insert into weights values (null); Error: CHECK constraint failed: float How about ...check (cast(float as real) = float)... ? sqlite> insert into weights values (1); sqlite> insert into weights values (0); sqlite> insert into weights values ('Hello'); Error: CHECK constraint failed: float sqlite> insert into weights values (null); sqlite> insert into weights values ('0'); --possible problem here since it takes it, but... sqlite> select float, typeof(float) from weights; float|typeof(float) 1.0|real 0.0|real |null 0.0|real --it stores it as a real anyway Run Time: real 0.000 user 0.00 sys 0.00 -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Wednesday, September 06, 2017 8:15 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Should the INTEGER not be cast to a REAL On 2017/09/07 12:35 AM, Cecil Westerhof wrote: > It does not, but this does: > CREATE TABLE weights( > float REAL, > > CONSTRAINT float CHECK(TYPEOF(float) IN ("real","integer")) > ); > > Instead of "int" you need "integer". > yes of course... My bad, sorry, but at least you've solved it :) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should the INTEGER not be cast to a REAL
On 2017/09/07 12:35 AM, Cecil Westerhof wrote: It does not, but this does: CREATE TABLE weights( float REAL, CONSTRAINT float CHECK(TYPEOF(float) IN ("real","integer")) ); Instead of "int" you need "integer". yes of course... My bad, sorry, but at least you've solved it :) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should the INTEGER not be cast to a REAL
2017-09-07 0:57 GMT+02:00 Simon Slavin : > > > On 6 Sep 2017, at 11:31pm, Cecil Westerhof wrote: > > > 2017-09-07 0:20 GMT+02:00 Richard Hipp : > > > >> On 9/6/17, Cecil Westerhof wrote: > >> > >>> Maybe this is correct, but it is certainly confusing. > >> > >> The constraint check occurs before the implicit conversion. > > > > Should that not be the other way around? > > This is an important point. But I’d say not. Constraint checks are there > to make sure that the programmers are doing the Right Thing, not that the > DBMS is doing the Right Thing. So it’s the source value which is checked, > not the value stored in the database. > > To do it the other way around suggests that SQLite needs to check that > SQLite is doing the Right Thing, which would be a waste of cycles, and a > sign that the developers need to consult a psychiatrist. > I would not see it as database checking, but that is just my opinion. ;-) But it would be a good idea to mention this. I just tested my assumption, but maybe someone else ‘knows’ he only has to check for real. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should the INTEGER not be cast to a REAL
2017-09-07 0:36 GMT+02:00 Wolfgang Enzinger : > Am Thu, 7 Sep 2017 00:28:56 +0200 schrieb Cecil Westerhof: > > > 2017-09-07 0:20 GMT+02:00 Wolfgang Enzinger : > > >> Add this trigger and everything is fine. ;-) > >> > >> CREATE TRIGGER weights_float_force_datatype > >> BEFORE INSERT ON weights > >> FOR EACH ROW > >> BEGIN > >> INSERT INTO weights(float) VALUES (CAST (new.float AS REAL)); > >> SELECT RAISE(IGNORE); > >> END > >> > > > > I do not think I should do that. > > Executing: > > SELECT CAST("Hello" AS REAL), TYPEOF(CAST("Hello" AS REAL)) > > > > Gives: > > "0.0""real" > > Depends. ;-) What else do you think CAST("Hello" AS REAL) should be > converted to? > I think the cast itself is not wrong, but if I would use the mentioned trigger then "Hello" would be inserted as 0.0 instead of generating an exception. > Seriously: in case you cannot be sure that only numbers will be inserted > into this column you should probably expand the trigger with a CASE WHEN > ... ELSE ... END construct. > It seems that: CONSTRAINT float CHECK(TYPEOF(float) IN ("real","integer")) is doing what I want. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should the INTEGER not be cast to a REAL
On 6 Sep 2017, at 11:31pm, Cecil Westerhof wrote: > 2017-09-07 0:20 GMT+02:00 Richard Hipp : > >> On 9/6/17, Cecil Westerhof wrote: >> >>> Maybe this is correct, but it is certainly confusing. >> >> The constraint check occurs before the implicit conversion. > > Should that not be the other way around? This is an important point. But I’d say not. Constraint checks are there to make sure that the programmers are doing the Right Thing, not that the DBMS is doing the Right Thing. So it’s the source value which is checked, not the value stored in the database. To do it the other way around suggests that SQLite needs to check that SQLite is doing the Right Thing, which would be a waste of cycles, and a sign that the developers need to consult a psychiatrist. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should the INTEGER not be cast to a REAL
Am Thu, 7 Sep 2017 00:28:56 +0200 schrieb Cecil Westerhof: > 2017-09-07 0:20 GMT+02:00 Wolfgang Enzinger : >> Add this trigger and everything is fine. ;-) >> >> CREATE TRIGGER weights_float_force_datatype >> BEFORE INSERT ON weights >> FOR EACH ROW >> BEGIN >> INSERT INTO weights(float) VALUES (CAST (new.float AS REAL)); >> SELECT RAISE(IGNORE); >> END >> > > I do not think I should do that. > Executing: > SELECT CAST("Hello" AS REAL), TYPEOF(CAST("Hello" AS REAL)) > > Gives: > "0.0""real" Depends. ;-) What else do you think CAST("Hello" AS REAL) should be converted to? Seriously: in case you cannot be sure that only numbers will be inserted into this column you should probably expand the trigger with a CASE WHEN ... ELSE ... END construct. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should the INTEGER not be cast to a REAL
2017-09-07 0:05 GMT+02:00 R Smith : > On 2017/09/06 11:58 PM, R Smith wrote: > >> 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. >> >> > To be specific, this should work for you: > > CREATE TABLE weights( > float REAL, > CONSTRAINT float CHECK(TYPEOF(float) IN ("real","int")) > ); It does not, but this does: CREATE TABLE weights( float REAL, CONSTRAINT float CHECK(TYPEOF(float) IN ("real","integer")) ); Instead of "int" you need "integer". -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should the INTEGER not be cast to a REAL
2017-09-07 0:20 GMT+02:00 Richard Hipp : > On 9/6/17, Cecil Westerhof wrote: > > > > Maybe this is correct, but it is certainly confusing. > > > > The constraint check occurs before the implicit conversion. > Should that not be the other way around? But probably not doable, because there could be code out there that depends on this behaviour. But maybe but a warning somewhere. Because it really got me by surprise. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should the INTEGER not be cast to a REAL
2017-09-07 0:20 GMT+02:00 Wolfgang Enzinger : > Am Thu, 7 Sep 2017 00:15:39 +0200 schrieb Cecil Westerhof: > > > 2017-09-07 0:05 GMT+02:00 R Smith : > > > >> On 2017/09/06 11:58 PM, R Smith wrote: > >> > >>> 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. > >>> > >>> > >> To be specific, this should work for you: > >> > >> CREATE TABLE weights( > >> float REAL, > >> CONSTRAINT float CHECK(TYPEOF(float) IN ("real","int")) > >> ); > > > > > > But it does not. > > > > The strange thing is: when I remove the constraint and do: > > INSERT INTO testing > > (float) > > VALUES > > (0) > > > > The insert is successful of-course. > > When I then execute: > > SELECT float, TYPEOF(float) > > FROM testing > > > > I get: > > "0.0""real" > > > > > > Maybe this is correct, but it is certainly confusing. > > Add this trigger and everything is fine. ;-) > > CREATE TRIGGER weights_float_force_datatype > BEFORE INSERT ON weights > FOR EACH ROW > BEGIN > INSERT INTO weights(float) VALUES (CAST (new.float AS REAL)); > SELECT RAISE(IGNORE); > END > I do not think I should do that. Executing: SELECT CAST("Hello" AS REAL), TYPEOF(CAST("Hello" AS REAL)) Gives: "0.0""real" -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should the INTEGER not be cast to a REAL
Am Thu, 7 Sep 2017 00:15:39 +0200 schrieb Cecil Westerhof: > 2017-09-07 0:05 GMT+02:00 R Smith : > >> On 2017/09/06 11:58 PM, R Smith wrote: >> >>> 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. >>> >>> >> To be specific, this should work for you: >> >> CREATE TABLE weights( >> float REAL, >> CONSTRAINT float CHECK(TYPEOF(float) IN ("real","int")) >> ); > > > But it does not. > > The strange thing is: when I remove the constraint and do: > INSERT INTO testing > (float) > VALUES > (0) > > The insert is successful of-course. > When I then execute: > SELECT float, TYPEOF(float) > FROM testing > > I get: > "0.0""real" > > > Maybe this is correct, but it is certainly confusing. Add this trigger and everything is fine. ;-) CREATE TRIGGER weights_float_force_datatype BEFORE INSERT ON weights FOR EACH ROW BEGIN INSERT INTO weights(float) VALUES (CAST (new.float AS REAL)); SELECT RAISE(IGNORE); END ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should the INTEGER not be cast to a REAL
On 9/6/17, Cecil Westerhof wrote: > > Maybe this is correct, but it is certainly confusing. > The constraint check occurs before the implicit conversion. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should the INTEGER not be cast to a REAL
2017-09-07 0:05 GMT+02:00 R Smith : > On 2017/09/06 11:58 PM, R Smith wrote: > >> 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. >> >> > To be specific, this should work for you: > > CREATE TABLE weights( > float REAL, > CONSTRAINT float CHECK(TYPEOF(float) IN ("real","int")) > ); But it does not. The strange thing is: when I remove the constraint and do: INSERT INTO testing (float) VALUES (0) The insert is successful of-course. When I then execute: SELECT float, TYPEOF(float) FROM testing I get: "0.0""real" Maybe this is correct, but it is certainly confusing. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should the INTEGER not be cast to a REAL
On 2017/09/06 11:58 PM, R Smith wrote: 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. To be specific, this should work for you: CREATE TABLE weights( float REAL, CONSTRAINT float CHECK(TYPEOF(float) IN ("real","int")) ); ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 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
Re: [sqlite] Should the INTEGER not be cast to a REAL
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? 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. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should the INTEGER not be cast to a REAL
2017-09-06 23:49 GMT+02:00 Jens Alfke : > > > > On Sep 6, 2017, at 2:37 PM, Cecil Westerhof > wrote: > > > > But should in the first case the 0 not be cast to a 0.0? > > No, SQLite ignores column type declarations. There's a whole article on > the website on SQLite's dynamic approach to data typing. > OK, I have to look into that then. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should the INTEGER not be cast to a REAL
> On Sep 6, 2017, at 2:37 PM, Cecil Westerhof wrote: > > But should in the first case the 0 not be cast to a 0.0? No, SQLite ignores column type declarations. There's a whole article on the website on SQLite's dynamic approach to data typing. —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Should the INTEGER not be cast to a REAL
I defined the following table: CREATE TABLE weights( float REAL, CONSTRAINT float CHECK(TYPEOF(float) = "real") ); I try the following insert: INSERT INTO testing (float) VALUES (0) But this gives: CHECK constraint failed: float When I try this insert: INSERT INTO testing (float) VALUES (0.0) I get: Query executed successfully But should in the first case the 0 not be cast to a 0.0? I do this in DBBrowser which uses 3.15.2. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users