Re: [sqlite] Should the INTEGER not be cast to a REAL

2017-09-07 Thread David Raymond 
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

2017-09-06 Thread R Smith 


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-06 Thread Cecil Westerhof 
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-06 Thread Cecil Westerhof 
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

2017-09-06 Thread 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.

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

2017-09-06 Thread 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?

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-06 Thread 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"))
> );


​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-06 Thread Cecil Westerhof 
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-06 Thread Cecil Westerhof 
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

2017-09-06 Thread 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

___
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 Thread 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.

-- 
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-06 Thread 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.

-- 
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-06 Thread 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"))
);



___
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 Thread Cecil Westerhof 
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

2017-09-06 Thread 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?


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 Thread Cecil Westerhof 
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

2017-09-06 Thread 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.

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