Should the INTEGER not be cast to a REAL

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|

Should the INTEGER not be cast to a REAL

Cecil Westerhof-5
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Should the INTEGER not be cast to a REAL

Jens Alfke-2


> On Sep 6, 2017, at 2:37 PM, Cecil Westerhof <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Should the INTEGER not be cast to a REAL

Cecil Westerhof-5
2017-09-06 23:49 GMT+02:00 Jens Alfke <[hidden email]>:

>
>
> > On Sep 6, 2017, at 2:37 PM, Cecil Westerhof <[hidden email]>
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Should the INTEGER not be cast to a REAL

R Smith
In reply to this post by Cecil Westerhof-5
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Should the INTEGER not be cast to a REAL

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Should the INTEGER not be cast to a REAL

Cecil Westerhof-5
In reply to this post by R Smith
2017-09-06 23:58 GMT+02:00 R Smith <[hidden email]>:

> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Should the INTEGER not be cast to a REAL

Cecil Westerhof-5
In reply to this post by R Smith
2017-09-07 0:05 GMT+02:00 R Smith <[hidden email]>:

> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Should the INTEGER not be cast to a REAL

Wolfgang Enzinger
Am Thu, 7 Sep 2017 00:15:39 +0200 schrieb Cecil Westerhof:

> 2017-09-07 0:05 GMT+02:00 R Smith <[hidden email]>:
>
>> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Should the INTEGER not be cast to a REAL

Richard Hipp-3
In reply to this post by Cecil Westerhof-5
On 9/6/17, Cecil Westerhof <[hidden email]> wrote:
>
> Maybe this is correct, but it is certainly confusing.
>

The constraint check occurs before the implicit conversion.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Should the INTEGER not be cast to a REAL

Cecil Westerhof-5
In reply to this post by Wolfgang Enzinger
2017-09-07 0:20 GMT+02:00 Wolfgang Enzinger <[hidden email]>:

> Am Thu, 7 Sep 2017 00:15:39 +0200 schrieb Cecil Westerhof:
>
> > 2017-09-07 0:05 GMT+02:00 R Smith <[hidden email]>:
> >
> >> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Should the INTEGER not be cast to a REAL

Cecil Westerhof-5
In reply to this post by Richard Hipp-3
2017-09-07 0:20 GMT+02:00 Richard Hipp <[hidden email]>:

> On 9/6/17, Cecil Westerhof <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Should the INTEGER not be cast to a REAL

Cecil Westerhof-5
In reply to this post by R Smith
2017-09-07 0:05 GMT+02:00 R Smith <[hidden email]>:

> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Should the INTEGER not be cast to a REAL

Wolfgang Enzinger
In reply to this post by Cecil Westerhof-5
Am Thu, 7 Sep 2017 00:28:56 +0200 schrieb Cecil Westerhof:

> 2017-09-07 0:20 GMT+02:00 Wolfgang Enzinger <[hidden email]>:

>> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Should the INTEGER not be cast to a REAL

Simon Slavin-3
In reply to this post by Cecil Westerhof-5


On 6 Sep 2017, at 11:31pm, Cecil Westerhof <[hidden email]> wrote:

> 2017-09-07 0:20 GMT+02:00 Richard Hipp <[hidden email]>:
>
>> On 9/6/17, Cecil Westerhof <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Should the INTEGER not be cast to a REAL

Cecil Westerhof-5
In reply to this post by Wolfgang Enzinger
2017-09-07 0:36 GMT+02:00 Wolfgang Enzinger <[hidden email]>:

> Am Thu, 7 Sep 2017 00:28:56 +0200 schrieb Cecil Westerhof:
>
> > 2017-09-07 0:20 GMT+02:00 Wolfgang Enzinger <[hidden email]>:
>
> >> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Should the INTEGER not be cast to a REAL

Cecil Westerhof-5
In reply to this post by Simon Slavin-3
2017-09-07 0:57 GMT+02:00 Simon Slavin <[hidden email]>:

>
>
> On 6 Sep 2017, at 11:31pm, Cecil Westerhof <[hidden email]> wrote:
>
> > 2017-09-07 0:20 GMT+02:00 Richard Hipp <[hidden email]>:
> >
> >> On 9/6/17, Cecil Westerhof <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Should the INTEGER not be cast to a REAL

R Smith
In reply to this post by Cecil Westerhof-5

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Hick Gunter
In reply to this post by Cecil Westerhof-5
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:[hidden email]] Im Auftrag von Cecil Westerhof
Gesendet: Donnerstag, 07. September 2017 00:06
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] Should the INTEGER not be cast to a REAL

2017-09-06 23:58 GMT+02:00 R Smith <[hidden email]>:

> 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
[hidden email]
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: [hidden email]

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Should the INTEGER not be cast to a REAL

David Raymond
In reply to this post by R Smith
sqlite> insert into weights values (null);
Error: CHECK constraint failed: float

<Insert OCD comment about single quotes for strings>

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.000000 sys 0.000000
-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of R Smith
Sent: Wednesday, September 06, 2017 8:15 PM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users