Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Simon Slavin]

On 9 Mar 2013, at 8:01pm, James K. Lowden  wrote:

> Looking at the sources, the name of the constraint would have to be
> dragged from the parser through the virtual machine, where it would be
> associated with the generated code that enforces the constraint.

You'll be glad to know that this issue has been raised on this list before.  It 
would require substantial changes to SQLite internals to allow it to always 
identify the constraint that failed. It's unlikely that this will ever appears 
in SQLite3.  But it's possible that since SQLite4 is a thorough rewrite it may 
be a feature in SQLite4.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[James K. Lowden]

On Sat, 9 Mar 2013 09:43:35 -0800
Peter Haworth <p...@lcsql.com> wrote:

> Seems like there are several ways to skin this cat.  The problem I
> have with CHECK is the generic error message it produces.  If a table
> has several columns with CHECK statements, how does my application
> know which one failed?  

Well, yes, that's a problem, and it won't be remedied easily.  

Looking at the sources, the name of the constraint would have to be
dragged from the parser through the virtual machine, where it would be
associated with the generated code that enforces the constraint.
Compare that with, for instance, verifying a uniqueness constraint for
a key: when that fails the message "column FOO is not unique" is
relatively easy to generate because the offending column name is readily
available.  

--jkl

> 
> On Sat, Mar 9, 2013 at 9:00 AM, <sqlite-users-requ...@sqlite.org>
> wrote:
> 
> > Message: 17
> > Date: Fri, 8 Mar 2013 14:26:06 -0500
> > From: "James K. Lowden" <jklow...@schemamania.org>
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer
> > join inconsistency]
> > Message-ID: <20130308142606.15776668.jklow...@schemamania.org>
> > Content-Type: text/plain; charset=ISO-8859-1
> >
> > On Thu, 7 Mar 2013 19:20:44 +0100
> > Petite Abeille <petite.abei...@gmail.com> wrote:
> >
> > > > In conclusion, if you want to allow affine type conversions on
> > > > INSERT, but not disallow values that cannot be so converted,
> > > > then CHECK(my_column = CAST(my_column AS )) works.  And
> > > > if you want to disallow values of incorrect types even when
> > > > type conversion is possible then use CHECK(typeof(my_column =
> > > > ).  That's pretty cool, IMO.
> > >
> > > Yeah? 'cool' is not necessarily how I would describe it? having a
> > > check constraint 'magically' coerce - change! - the inserted data
> > > type is? well? not cool. I would call it a misfeature :)
> >
> > To be clear, the contraints Nico described don't coerce anything.
> > By preventing the insertion of values outside the column's domain
> > (per its affinity), the constraints ensure that future coersions --
> > as part of a join, say -- won't lose data.
> >
> 
> 
> 
> Pete
> lcSQL Software <http://www.lcsql.com>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Peter Haworth]

Seems like there are several ways to skin this cat.  The problem I have
with CHECK is the generic error message it produces.  If a table has
several columns with CHECK statements, how does my application know which
one failed?  I favor the trigger approach because I can define a meaningful
error message.

On Sat, Mar 9, 2013 at 9:00 AM, <sqlite-users-requ...@sqlite.org> wrote:

> Message: 17
> Date: Fri, 8 Mar 2013 14:26:06 -0500
> From: "James K. Lowden" <jklow...@schemamania.org>
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join
> inconsistency]
> Message-ID: <20130308142606.15776668.jklow...@schemamania.org>
> Content-Type: text/plain; charset=ISO-8859-1
>
> On Thu, 7 Mar 2013 19:20:44 +0100
> Petite Abeille <petite.abei...@gmail.com> wrote:
>
> > > In conclusion, if you want to allow affine type conversions on
> > > INSERT, but not disallow values that cannot be so converted, then
> > > CHECK(my_column = CAST(my_column AS )) works.  And if you want
> > > to disallow values of incorrect types even when type conversion is
> > > possible then use CHECK(typeof(my_column = ).  That's pretty
> > > cool, IMO.
> >
> > Yeah? 'cool' is not necessarily how I would describe it? having a
> > check constraint 'magically' coerce - change! - the inserted data
> > type is? well? not cool. I would call it a misfeature :)
>
> To be clear, the contraints Nico described don't coerce anything.  By
> preventing the insertion of values outside the column's domain (per its
> affinity), the constraints ensure that future coersions -- as part of a
> join, say -- won't lose data.
>



Pete
lcSQL Software <http://www.lcsql.com>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Keith Medcalf]

The problem only arises if you do not properly type your application.  Why not 
just "pretend" it is strongly typed, use the correct types, and you will 
achieve your onjective.

Or is the complaint really just that  SQLite does not barf when you screw up?

---
Sent from Samsung Mobile 


 Original message 
From: Nico Williams <n...@cryptonector.com> 
Date:  
To: General Discussion of SQLite Database <sqlite-users@sqlite.org> 
Subject: Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join   
inconsistency] 
 
_______
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Nico Williams]

On Fri, Mar 8, 2013 at 1:26 PM, James K. Lowden
 wrote:
> On Thu, 7 Mar 2013 19:20:44 +0100
> Petite Abeille  wrote:
>> Yeah? 'cool' is not necessarily how I would describe it? having a
>> check constraint 'magically' coerce - change! - the inserted data
>> type is? well? not cool. I would call it a misfeature :)
>
> To be clear, the contraints Nico described don't coerce anything.  By
> preventing the insertion of values outside the column's domain (per its
> affinity), the constraints ensure that future coersions -- as part of a
> join, say -- won't lose data.

This.  Thanks Jim.

I dunno, I prefer strong typing, but I'm not going to dictate that it
must be so.  I like having options.  Also, even a fan of strong typing
will occasionally have handy uses for duck typing.  So I see nothing
terribly wrong here except with the way CAST to numeric works, and
there's a simple workaround for that, so in the end, nothing terribly
wrong.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Marc L. Allen]

If I have any doubt, I add .5 (or .05, .005, whatever) before the operation.  I 
know that breaks algebraic rounding, but that's one I live with.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of James K. Lowden
Sent: Friday, March 08, 2013 2:45 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join 
inconsistency]

On Thu, 7 Mar 2013 18:45:23 +
Simon Slavin <slav...@bigfraud.org> wrote:

> what do you think the desired behaviour would be for
> 
> CAST('0.9' AS INTEGER)
> 
> I know what I want.  Perhaps this can be fixed in SQLite4.  

Sorry, but CAST is not a math function.  There's probably a language somewhere 
out there that rounds floating point when assigned to integer, but every 
language I've ever used that supported those types truncated the fractional 
portion.  It's also what the SQL standard specifies.  

Consider, do you want 

CAST(0.5 as integer)

to be 1?  What about 0.49?  What about 0.499?  

These issues are why round() exists.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[James K. Lowden]

On Thu, 7 Mar 2013 18:45:23 +
Simon Slavin  wrote:

> what do you think the desired behaviour would be for
> 
> CAST('0.9' AS INTEGER)
> 
> I know what I want.  Perhaps this can be fixed in SQLite4.  

Sorry, but CAST is not a math function.  There's probably a language
somewhere out there that rounds floating point when assigned to
integer, but every language I've ever used that supported those types
truncated the fractional portion.  It's also what the SQL standard
specifies.  

Consider, do you want 

CAST(0.5 as integer)

to be 1?  What about 0.49?  What about 0.499?  

These issues are why round() exists.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[James K. Lowden]

On Thu, 7 Mar 2013 07:36:25 -0600
"Michael Black"  wrote:

> Personally I think this behavior is horrid.  
...
> Why is this behavior allowed now?

As Zero Mostel sang: "Tradition!"

See your handy atoi() man page.  :-)  

I don't know if that's really why, but it's not as if there's no prior
art.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[James K. Lowden]

On Thu, 7 Mar 2013 19:20:44 +0100
Petite Abeille  wrote:

> > In conclusion, if you want to allow affine type conversions on
> > INSERT, but not disallow values that cannot be so converted, then
> > CHECK(my_column = CAST(my_column AS )) works.  And if you want
> > to disallow values of incorrect types even when type conversion is
> > possible then use CHECK(typeof(my_column = ).  That's pretty
> > cool, IMO.
> 
> Yeah? 'cool' is not necessarily how I would describe it? having a
> check constraint 'magically' coerce - change! - the inserted data
> type is? well? not cool. I would call it a misfeature :)

To be clear, the contraints Nico described don't coerce anything.  By
preventing the insertion of values outside the column's domain (per its
affinity), the constraints ensure that future coersions -- as part of a
join, say -- won't lose data.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Ryan Johnson]

On 07/03/2013 3:14 PM, Nico Williams wrote:

On Thu, Mar 7, 2013 at 12:53 PM, Ryan Johnson
 wrote:

Meanwhile, though, I'd be delighted if column affinity, cast(), implicit
conversions performed by arithmetic operations, check(), and triggers all
behaved the same way, with the current behavior of column affinity probably
the least surprising/troublesome. Right now those five operations have four
different behaviors to keep track of.

I think there's just two type conversion behaviors: implicit type
conversions and CAST.

2

CHECK applies to values w/o any conversions (I
consider this a good thing),

1

affinity applies at various times and is
really a property of values derived from their sources' type affinity
(this is odd, but really a consequence of SQLite3's dynamic typing
design).

2+ (probably 4+, but let's be conservative)

Triggers don't have any special role here, save that BEFORE triggers
get values after implicit type conversions.
1 (thank goodness BEFORE and AFTER at least match behavior, even if the 
underlying source of data differs slightly)

This one I tend to think
is a problem, because one cannot implement CHECK via equivalent
TRIGGERs, and the ability to add/modify table constraints by using
triggers instead is kinda nice given the inability to change
table/column constraints after a schema is set.


You just enumerated at least five different behaviors to worry about if 
you might get a string or real where you expected an int.


I rest my case.

Ryan

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Nico Williams]

On Thu, Mar 7, 2013 at 12:53 PM, Ryan Johnson
 wrote:
> Meanwhile, though, I'd be delighted if column affinity, cast(), implicit
> conversions performed by arithmetic operations, check(), and triggers all
> behaved the same way, with the current behavior of column affinity probably
> the least surprising/troublesome. Right now those five operations have four
> different behaviors to keep track of.

I think there's just two type conversion behaviors: implicit type
conversions and CAST.  The rest is related but orthogonal and -to my
mind- unavoidable: CHECK applies to values w/o any conversions (I
consider this a good thing), affinity applies at various times and is
really a property of values derived from their sources' type affinity
(this is odd, but really a consequence of SQLite3's dynamic typing
design).

Triggers don't have any special role here, save that BEFORE triggers
get values after implicit type conversions.  This one I tend to think
is a problem, because one cannot implement CHECK via equivalent
TRIGGERs, and the ability to add/modify table constraints by using
triggers instead is kinda nice given the inability to change
table/column constraints after a schema is set.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Petite Abeille]

On Mar 7, 2013, at 7:53 PM, Ryan Johnson  wrote:

> Meanwhile, though, I'd be delighted if column affinity, cast(), implicit 
> conversions performed by arithmetic operations, check(), and triggers all 
> behaved the same way, with the current behavior of column affinity probably 
> the least surprising/troublesome. Right now those five operations have four 
> different behaviors to keep track of.

select 1 + '1abc';
> 2

select '1abc' + '1abc';
> 2

… okidoki...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Ryan Johnson]

On 07/03/2013 1:48 PM, Nico Williams wrote:

On Thu, Mar 7, 2013 at 12:20 PM, Ryan Johnson
 wrote:

On 07/03/2013 1:07 PM, Nico Williams wrote:

You might defer checks, but not type conversions.  In any case, I see
no value in deferring check constraints.

Anything constraining cardinality. The old example of "there must always be
three doctors on duty in the ER" comes to mind: if you check() for an exact
count, it becomes very hard to make changes to the schedule without deferred
checking.

That can't be done with a CHECK expression.  I have proposed
transaction-level triggers for this sort of thing, and I have an
implementation lying around (but it's not finished, and quite rotted
by now).
Fair enough. It might be worth deferring a check until transaction end 
if it allows verifying a bulk update with an efficient index probe, but 
that would only be true for really expensive checks and really big bulk 
updates.





Anyways, think of CHECK constraints as equivalent to BEFORE
INSERT/UPDATE triggers.

They're not equivalent. The before trigger sees only the value that was
actually stored, because it runs as a separate program after the
insert/update completes.

WAT?

Yup. I've had several emails in this thread with examples. Here's the short
version:

BEFORE INSERT/UPDATE triggers may apply after type conversions, but
not after storing the new/updated row.  That's what my WAT was about
:)

Check out the vdbe generated by an insert on a table with a before
insert trigger.

Fair enough. I should have said:

They're not equivalent. The before trigger sees only the value that will 
actually be stored.


Ryan

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Ryan Johnson]

On 07/03/2013 1:45 PM, Simon Slavin wrote:

On 7 Mar 2013, at 6:27pm, Ryan Johnson  wrote:


The problem is sqlite3 doesn't cast to REAL first. It just parses the string 
until it hits '.' (which isn't a valid part of an integer) and then returns 
whatever it had accumulated so far. That breaks in creative ways for values 
like:

cast('10e-1' as integer) -- 10
cast('abc' as integer) -- 0

Ah, thank you.  Good explanation.

I consider this behaviour wrong, too.  Casting a string as INTEGER should take 
into account what it means as a REAL.  For instance, as a 
programmer/statistician, what do you think the desired behaviour would be for

CAST('0.9' AS INTEGER)

I know what I want.  Perhaps this can be fixed in SQLite4.
I tend to agree that some intelligent rounding is in order (those darn 
epsilons in floating point!).


Meanwhile, though, I'd be delighted if column affinity, cast(), implicit 
conversions performed by arithmetic operations, check(), and triggers 
all behaved the same way, with the current behavior of column affinity 
probably the least surprising/troublesome. Right now those five 
operations have four different behaviors to keep track of.


And that's *before* you factor in what happens when you use several of 
those operations on the same column, e.g.:


sqlite> select x,typeof(x),0+x from t1;
3|integer|3
3.0|real|3.0
3.0|real|3.0
3|text|3
3.0|text|3.0
30e-1|text|3.0
sqlite> select x,0+x,typeof(x) from t1;
3|3|integer
3.0|3.0|real
3.0|3.0|real
3|3|integer
3.0|3.0|real
30e-1|3.0|real

Ryan

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Petite Abeille]

On Mar 7, 2013, at 6:21 AM, Nico Williams  wrote:

> In conclusion, if you want to allow affine type conversions on INSERT,
> but not disallow values that cannot be so converted, then
> CHECK(my_column = CAST(my_column AS )) works.  And if you want
> to disallow values of incorrect types even when type conversion is
> possible then use CHECK(typeof(my_column = ).  That's pretty
> cool, IMO.

Yeah… 'cool' is not necessarily how I would describe it… having a check 
constraint 'magically' coerce - change! - the inserted data type is… well… not 
cool. I would call it a misfeature :)

Yet another SQLite gotcha to watch out… sigh... 

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Nico Williams]

On Thu, Mar 7, 2013 at 12:20 PM, Ryan Johnson
 wrote:
> On 07/03/2013 1:07 PM, Nico Williams wrote:
>> You might defer checks, but not type conversions.  In any case, I see
>> no value in deferring check constraints.
>
> Anything constraining cardinality. The old example of "there must always be
> three doctors on duty in the ER" comes to mind: if you check() for an exact
> count, it becomes very hard to make changes to the schedule without deferred
> checking.

That can't be done with a CHECK expression.  I have proposed
transaction-level triggers for this sort of thing, and I have an
implementation lying around (but it's not finished, and quite rotted
by now).

 Anyways, think of CHECK constraints as equivalent to BEFORE
 INSERT/UPDATE triggers.
>>>
>>> They're not equivalent. The before trigger sees only the value that was
>>> actually stored, because it runs as a separate program after the
>>> insert/update completes.
>>
>> WAT?
>
> Yup. I've had several emails in this thread with examples. Here's the short
> version:

BEFORE INSERT/UPDATE triggers may apply after type conversions, but
not after storing the new/updated row.  That's what my WAT was about
:)

Check out the vdbe generated by an insert on a table with a before
insert trigger.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Simon Slavin]

On 7 Mar 2013, at 6:27pm, Ryan Johnson  wrote:

> The problem is sqlite3 doesn't cast to REAL first. It just parses the string 
> until it hits '.' (which isn't a valid part of an integer) and then returns 
> whatever it had accumulated so far. That breaks in creative ways for values 
> like:
> 
> cast('10e-1' as integer) -- 10
> cast('abc' as integer) -- 0

Ah, thank you.  Good explanation.

I consider this behaviour wrong, too.  Casting a string as INTEGER should take 
into account what it means as a REAL.  For instance, as a 
programmer/statistician, what do you think the desired behaviour would be for

CAST('0.9' AS INTEGER)

I know what I want.  Perhaps this can be fixed in SQLite4.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Ryan Johnson]

On 07/03/2013 1:15 PM, Simon Slavin wrote:

On 7 Mar 2013, at 4:07pm, Ryan Johnson  wrote:


That does leave the question of what to do with cast ('1.0' as integer), though. 
Without the prefix-based matching that would now return NULL rather than 1, even 
though cast(1.0 as integer) would still return 1. Then again, disallowing all 
floats might be better than the current practice of returning 1 from a cast of 
both '1e-10' and '1e10' (the real->integer casts do the right thing, as does 
assignment to a column with integer affinity).

Can you (or someone) explain the problem with


cast ('1.0' as integer)

?  Possibly by explaining what "prefix-based matching" is.  I see no problem 
with SQLite /internally/ first casting as a REAL and then casting that REAL as an 
INTEGER, resulting in the integer 1.  The real question is what you'd get if you tried
The problem is sqlite3 doesn't cast to REAL first. It just parses the 
string until it hits '.' (which isn't a valid part of an integer) and 
then returns whatever it had accumulated so far. That breaks in creative 
ways for values like:


cast('10e-1' as integer) -- 10
cast('abc' as integer) -- 0


cast ('1.1' as integer)

Can you acceptably cast a non-integer REAL as an INTEGER ?  Or should /that/ 
produce a NULL ?  Since what you're actually doing is depending on some 
mathematical function like round().
IMHO, casting string version of reals to actual integers should do 
whatever happens when you cast an actual real to integer. Whether that 
means NULL, truncate, or round is debatable, but there's pretty strong 
precedent for truncation when casting real to int.


Ryan

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Ryan Johnson]

On 07/03/2013 1:07 PM, Nico Williams wrote:

On Thu, Mar 7, 2013 at 11:44 AM, Ryan Johnson
 wrote:

On 07/03/2013 12:27 PM, Nico Williams wrote:

On Thu, Mar 7, 2013 at 11:12 AM, Ryan Johnson
 wrote:

I would argue that, if a column has type affinity, CHECK should work with
the value that would actually get stored, not the one that was assigned.

But then you couldn't check the value that was attempted to store.
You'd be stuck with dynamic type conversions in all cases.

Is that a bad thing? Forbidding dynamic type conversions to occur at all is
very different than requiring that they always succeed if/when they do
occur, and overly strict IMO.

I prefer strong static typing and no automatic type conversions, but
the way SQLite3 is now I can get all of:

  - duck typing (no constraints)
  - strong typing with automatic type conversions (see earlier posts in
this thread)
  - strong typing with no automatic type conversions (ditto)

That's fairly flexible.  There's something to be said for that.
True, but I'd happily give up static strong typing rather than deal with 
the current mess. YMMV.




Besides, the check is defined to verify the column, not on the value that
came from the user. In theory you should be able to defer all checks until
the end of a transaction (for all the same reasons deferred FK checking can
be important), and that would cause a behavior change as things currently
stand (the attempted-to-store value would be long gone by then).

You might defer checks, but not type conversions.  In any case, I see
no value in deferring check constraints.
Anything constraining cardinality. The old example of "there must always 
be three doctors on duty in the ER" comes to mind: if you check() for an 
exact count, it becomes very hard to make changes to the schedule 
without deferred checking.



Anyways, think of CHECK constraints as equivalent to BEFORE
INSERT/UPDATE triggers.

They're not equivalent. The before trigger sees only the value that was
actually stored, because it runs as a separate program after the
insert/update completes.

WAT?
Yup. I've had several emails in this thread with examples. Here's the 
short version:


sqlite> create table t1(x);
sqlite> create table t2(x integer);
sqlite> create table t3(x integer check(typeof(x)='integer'));
sqlite> create table t4(x integer);
sqlite> create trigger t4t before insert on t4 begin select raise(FAIL, 
'ints only!') where typeof(new.x)!='integer'; end;

sqlite> insert into t1 values(3);
sqlite> insert into t1 values(3.0);
sqlite> insert into t1 values(30e-1);
sqlite> insert into t1 values('3');
sqlite> insert into t1 values('3.0');
sqlite> insert into t1 values('30e-1');
sqlite> insert into t2 select * from t1;
sqlite> insert into t3 select * from t1;
Error: constraint failed
sqlite> insert into t4 select * from t1;

Ryan

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Simon Slavin]

On 7 Mar 2013, at 4:07pm, Ryan Johnson  wrote:

> That does leave the question of what to do with cast ('1.0' as integer), 
> though. Without the prefix-based matching that would now return NULL rather 
> than 1, even though cast(1.0 as integer) would still return 1. Then again, 
> disallowing all floats might be better than the current practice of returning 
> 1 from a cast of both '1e-10' and '1e10' (the real->integer casts do the 
> right thing, as does assignment to a column with integer affinity).

Can you (or someone) explain the problem with

> cast ('1.0' as integer)

?  Possibly by explaining what "prefix-based matching" is.  I see no problem 
with SQLite /internally/ first casting as a REAL and then casting that REAL as 
an INTEGER, resulting in the integer 1.  The real question is what you'd get if 
you tried

> cast ('1.1' as integer)

Can you acceptably cast a non-integer REAL as an INTEGER ?  Or should /that/ 
produce a NULL ?  Since what you're actually doing is depending on some 
mathematical function like round().

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Nico Williams]

On Thu, Mar 7, 2013 at 11:44 AM, Ryan Johnson
 wrote:
> On 07/03/2013 12:27 PM, Nico Williams wrote:
>>
>> On Thu, Mar 7, 2013 at 11:12 AM, Ryan Johnson
>>  wrote:
>>>
>>> I would argue that, if a column has type affinity, CHECK should work with
>>> the value that would actually get stored, not the one that was assigned.
>>
>> But then you couldn't check the value that was attempted to store.
>> You'd be stuck with dynamic type conversions in all cases.
>
> Is that a bad thing? Forbidding dynamic type conversions to occur at all is
> very different than requiring that they always succeed if/when they do
> occur, and overly strict IMO.

I prefer strong static typing and no automatic type conversions, but
the way SQLite3 is now I can get all of:

 - duck typing (no constraints)
 - strong typing with automatic type conversions (see earlier posts in
this thread)
 - strong typing with no automatic type conversions (ditto)

That's fairly flexible.  There's something to be said for that.

> Besides, the check is defined to verify the column, not on the value that
> came from the user. In theory you should be able to defer all checks until
> the end of a transaction (for all the same reasons deferred FK checking can
> be important), and that would cause a behavior change as things currently
> stand (the attempted-to-store value would be long gone by then).

You might defer checks, but not type conversions.  In any case, I see
no value in deferring check constraints.

>
>> Anyways, think of CHECK constraints as equivalent to BEFORE
>> INSERT/UPDATE triggers.
>
> They're not equivalent. The before trigger sees only the value that was
> actually stored, because it runs as a separate program after the
> insert/update completes.

WAT?

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Ryan Johnson]

On 07/03/2013 12:18 PM, Ryan Johnson wrote:

On 07/03/2013 11:14 AM, Doug Currie wrote:
On Mar 7, 2013, at 11:07 AM, Ryan Johnson 
 wrote:
That does leave the question of what to do with cast ('1.0' as 
integer), though. Without the prefix-based matching that would now 
return NULL rather than 1, even though cast(1.0 as integer) would 
still return 1. Then again, disallowing all floats might be better 
than the current practice of returning 1 from a cast of both '1e-10' 
and '1e10' (the real->integer casts do the right thing, as does 
assignment to a column with integer affinity).

Would

   cast(cast(x as real) as integer)

do what you want?
Looks like it, though I'd probably make the first cast be to numeric 
(may as well go directly to int if you can).


It's also a bit more efficient (2 VDBE ops fewer) and less brittle 
than the nasty hack I came up with:


x=cast(cast(x as numeric) as integer)
vs.
x+0=x and cast(x as integer)=x
Here's some more weirdness... typeof(cast('30e-1' as numeric)) returns 
'integer' but casting 30e-1 returns 'real'. Same for '3.0' vs 3.0. And, 
of course, casting 'abc' also yields an integer.


Ryan

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Ryan Johnson]

On 07/03/2013 12:27 PM, Nico Williams wrote:

On Thu, Mar 7, 2013 at 11:12 AM, Ryan Johnson
 wrote:

I would argue that, if a column has type affinity, CHECK should work with
the value that would actually get stored, not the one that was assigned.

But then you couldn't check the value that was attempted to store.
You'd be stuck with dynamic type conversions in all cases.
Is that a bad thing? Forbidding dynamic type conversions to occur at all 
is very different than requiring that they always succeed if/when they 
do occur, and overly strict IMO.


Besides, the check is defined to verify the column, not on the value 
that came from the user. In theory you should be able to defer all 
checks until the end of a transaction (for all the same reasons deferred 
FK checking can be important), and that would cause a behavior change as 
things currently stand (the attempted-to-store value would be long gone 
by then).



Anyways, think of CHECK constraints as equivalent to BEFORE
INSERT/UPDATE triggers.
They're not equivalent. The before trigger sees only the value that was 
actually stored, because it runs as a separate program after the 
insert/update completes.


Ryan

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Nico Williams]

On Thu, Mar 7, 2013 at 11:12 AM, Ryan Johnson
 wrote:
> I would argue that, if a column has type affinity, CHECK should work with
> the value that would actually get stored, not the one that was assigned.

But then you couldn't check the value that was attempted to store.
You'd be stuck with dynamic type conversions in all cases.

Anyways, think of CHECK constraints as equivalent to BEFORE
INSERT/UPDATE triggers.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Ryan Johnson]

On 07/03/2013 11:14 AM, Doug Currie wrote:

On Mar 7, 2013, at 11:07 AM, Ryan Johnson  wrote:

That does leave the question of what to do with cast ('1.0' as integer), though. 
Without the prefix-based matching that would now return NULL rather than 1, even 
though cast(1.0 as integer) would still return 1. Then again, disallowing all 
floats might be better than the current practice of returning 1 from a cast of 
both '1e-10' and '1e10' (the real->integer casts do the right thing, as does 
assignment to a column with integer affinity).

Would

   cast(cast(x as real) as integer)

do what you want?
Looks like it, though I'd probably make the first cast be to numeric 
(may as well go directly to int if you can).


It's also a bit more efficient (2 VDBE ops fewer) and less brittle than 
the nasty hack I came up with:


x=cast(cast(x as numeric) as integer)
vs.
x+0=x and cast(x as integer)=x

Ryan

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Ryan Johnson]

On 06/03/2013 10:30 AM, Dominique Devienne wrote:

On Wed, Mar 6, 2013 at 3:29 PM, Ryan Johnson 
wrote:

Off topic, I'd love a way to request strong typing for a column (so that

attempts to store 'abc' into an int column would fail). You can emulate it
with a pair of before/update triggers (select raise(...) where
typeof(intcol)!='integer'), but that's clunky. Oh well... in retrospect,
most of the times I've been bitten by type mismatches were probably either
due to this bug or (more likely) due to my not specifying any affinity at
all and then being surprised when 1 != '1'.

You don't have to use triggers, you can use a check constraint instead
(simpler, but also perhaps faster as well?).

If you do, you loose some of the implicit type conversions SQLite does,
based on type affinity, so the "1" no longer gets converted to 1.

I also would prefer a strong-typing mode though (as an opt-in pragma for
example), rather than adding a bunch of check constraints, and built-in
strong-typing would likely be faster. But Dr. Hipp prefers dynamic typing,
so dynamic typing it is :).

--DD

sqlite> create table t (id number);
sqlite> insert into t values (1);
sqlite> insert into t values ("1");
sqlite> insert into t values ("1x");
sqlite> select id, typeof(id) from t;
1|integer
1|integer
1x|text

sqlite> create table tt (id number check (typeof(id) = 'integer'));
sqlite> insert into tt values (1);
sqlite> insert into tt values ("1");
Error: constraint failed
sqlite> insert into tt values ("1x");
Error: constraint failed
sqlite> select id, typeof(id) from tt;
1|integer

// recent SQLite versions also now report the name of the constraint that
failed, if available.
sqlite> create table ttt (id number, constraint id_is_integer check
(typeof(id) = 'integer'));
sqlite> insert into ttt values (1);
sqlite> insert into ttt values ("1");
Error: constraint id_is_integer failed
I would argue that, if a column has type affinity, CHECK should work 
with the value that would actually get stored, not the one that was 
assigned. Otherwise you get situations where the non-checked table ended 
up with an integer inside even though the checked table rejected the 
change. This doesn't usually show up because most check constraints on 
numeric fields use operators that try to convert strings to numbers 
(though the way they do it varies).


Looking at this some more, I'd say there are several inconsistent 
behaviors here. Most are strange type conversion behaviors that may or 
may not be intentional, but two definitely look like bugs:


1. Predicates can behave differently for WHERE vs CHECK (see table t3
   in the typescript below).
2. The type of x (as reported by typeof, but also visible in other
   ways) can change depending on how x has been used before (see query
   before table t4 in the typescript and the check constraint in table t8).

In any case, the following (see table t8) seems to enforce integer-ness 
properly: check(x+0=x and cast(x as integer)=x)


It exploits several behaviors:

1. The expression "x+0" causes all numeric strings to be converted to
   numbers (real or int)
2. Comparing the result against x discards garbage like '3bc' or 'abc'
   that would otherwise slip through.
3. The numeric type of "x+0" changes the type of x to numeric, so that
   values like '30e-1' typecast properly afterward.
4. Comparing the output of the cast against x discards reals values,
   leaving only ints.

You have to evaluate #1 before #3, though, or the whole thing falls 
apart... which is scary and brittle.


The VDBE changes from 13 to 20 instructions, with the following bit added:

4|Integer|1|2|0||00|
5|Integer|0|4|0||00|
6|Add|4|2|3||00|
7|Ne|2|11|3|collseq(BINARY)|64|
8|SCopy|2|3|0||00|
9|ToInt|3|0|0||00|
10|Eq|2|12|3|collseq(BINARY)|6b|
11|Halt|19|2|0||00|


Proper support for strong typing would be vastly more efficient; a 
single OP_MustBeInt should work beautifully for integers, and similar 
opcodes could be defined easily for the remaining types.


Meanwhile, the trigger is bulkier to code up and more expensive at 
runtime, but much less likely to break in subtle ways when some future 
release of sqlite3 starts reordering predicate expressions.


Ryan

 begin typescript ---
sqlite> create table t1(x);
sqlite> insert into t1 values(3);
sqlite> insert into t1 values('3');
sqlite> insert into t1 values(3.0);
sqlite> insert into t1 values('3.0');
sqlite> insert into t1 values(30e-1);
sqlite> insert into t1 values('30e-1');
sqlite> insert into t1 values(3.3);
sqlite> insert into t1 values('3.3');
sqlite> insert into t1 values(3e-1);
sqlite> insert into t1 values('3e-1');
sqlite> insert into t1 values('3bc');
sqlite> insert into t1 values('abc');
sqlite> select x,typeof(x) from t1;
3|integer
3|text
3.0|real
3.0|text
3.0|real
30e-1|text
3.3|real
3.3|text
0.3|real
3e-1|text
3bc|text
abc|text
sqlite> -- so far so good
sqlite>
sqlite> create table t2(x integer);
sqlite> insert into t2 select x from 

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Doug Currie]

On Mar 7, 2013, at 11:07 AM, Ryan Johnson  wrote:
> 
> That does leave the question of what to do with cast ('1.0' as integer), 
> though. Without the prefix-based matching that would now return NULL rather 
> than 1, even though cast(1.0 as integer) would still return 1. Then again, 
> disallowing all floats might be better than the current practice of returning 
> 1 from a cast of both '1e-10' and '1e10' (the real->integer casts do the 
> right thing, as does assignment to a column with integer affinity).

Would

  cast(cast(x as real) as integer)

do what you want?

e

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Ryan Johnson]

On 07/03/2013 9:28 AM, Simon Slavin wrote:

On 7 Mar 2013, at 1:36pm, "Michael Black"  wrote:


New:
select cast('2' as integer);
2
select cast('2a' as integer);
0

Sorry, but that's very bad.  There is no way that the string '2a' could 
represent 0.  I agree that interpreting '2a' as the integer 2 may be considered 
wrong, but I think you're solving the problem the wrong way.

If you want to indicate a problem, I would like to suggest that you either 
return NULL or have sqlite_step() generate an actual error code.  Of the two I 
think returning NULL is more within the spirit of SQL but I haven't though that 
through yet.


In a world where 1/0 returns NULL, it makes sense for an invalid 
typecast to do the same. For purposes of enforcing strong typing, a 
non-null integer constraint would be "check (cast(x as integer) is not 
null)" and a nullable integer constraint would just need to add "or x is 
null" -- a pretty clean solution IMO.


That does leave the question of what to do with cast ('1.0' as integer), 
though. Without the prefix-based matching that would now return NULL 
rather than 1, even though cast(1.0 as integer) would still return 1. 
Then again, disallowing all floats might be better than the current 
practice of returning 1 from a cast of both '1e-10' and '1e10' (the 
real->integer casts do the right thing, as does assignment to a column 
with integer affinity).


Ryan

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Nico Williams]

On Thu, Mar 7, 2013 at 7:36 AM, Michael Black  wrote:
> Personally I think this behavior is horrid.  Is there some scenario where
> this wouldn't be a latent bug?

I don't like it either.  I also share Simon's (and yours!) opinion
regarding your patch: if cast be fixed at all (and it shouldn't be, in
SQLite3, for compatibility reasons) then it should be fixed to raise
an error or return NULL, not 0.

> Here's a patch against 3.7.14.1 which behaves the way it should IMHO
> Though I think it should actually throw an error when not parseable
> correctly.

Right.  But it's almost certainly too late to fix this in SQLite3.
Maybe with a PRAGMA?

The current behavior of CAST seems best suited for a function, not for CAST.

> Why is this behavior allowed now?

Someone from the SQLite team might be able to tell you, but my guess
is that this is academic now.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Simon Slavin]

On 7 Mar 2013, at 1:36pm, "Michael Black"  wrote:

> New:
> select cast('2' as integer);
> 2
> select cast('2a' as integer);
> 0

Sorry, but that's very bad.  There is no way that the string '2a' could 
represent 0.  I agree that interpreting '2a' as the integer 2 may be considered 
wrong, but I think you're solving the problem the wrong way.

If you want to indicate a problem, I would like to suggest that you either 
return NULL or have sqlite_step() generate an actual error code.  Of the two I 
think returning NULL is more within the spirit of SQL but I haven't though that 
through yet.



Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Michael Black]

Personally I think this behavior is horrid.  Is there some scenario where
this wouldn't be a latent bug?
Here's a patch against 3.7.14.1 which behaves the way it should IMHO
Though I think it should actually throw an error when not parseable
correctly.
Why is this behavior allowed now?

Old:
select cast('2' as integer);
2
select cast('2a' as integer);
2
select cast('2.1' as integer);
2
select cast('2.6' as integer);
2
select cast('2.6f' as integer);
2
select cast('2.6' as float);
2.6
Select cast('2.6f' as float);
2.6

New:
select cast('2' as integer);
2
select cast('2a' as integer);
0
select cast('2.1' as integer);
0
select cast('2.6' as integer);
0
select cast('2.6f' as integer);
0
select cast('2.6' as float);
2.6
select cast('2.6f' as float);
0

*** sqlite3.old Thu Mar 07 07:06:32 2013
--- sqlite3.c   Thu Mar 07 07:26:59 2013
***
*** 21199,21205 
int nDigits = 0;

*pResult = 0.0;   /* Default return value, in case of an error */
-
if( enc==SQLITE_UTF16BE ) z++;

/* skip leading spaces */
--- 21199,21204 
***
*** 21239,21244 
--- 21238,21249 
  }
  /* skip non-significant digits */
  while( z=zEnd ) goto do_atof_calc;

***
*** 21465,21470 
--- 21470,21476 
sqlite_int64 v = 0;
int i, c;
int neg = 0;
+   printf("sqlite3GetInt32\n");
if( zNum[0]=='-' ){
  neg = 1;
  zNum++;
***
*** 21474,21479 
--- 21480,21488 
while( zNum[0]=='0' ) zNum++;
for(i=0; i<11 && (c = zNum[i] - '0')>=0 && c<=9; i++){
  v = v*10 + c;
+   }
+   if (zNum[i] != 0) {
+ return 0;
}

/* The longest decimal representation of a 32 bit integer is 10 digits:

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Petite Abeille
Sent: Wednesday, March 06, 2013 4:21 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join
inconsistency]


On Mar 6, 2013, at 10:49 PM, Nico Williams <n...@cryptonector.com> wrote:

> Ah, your confusion comes from the fact that type conversion still
> happens when the INSERT gets around to making the record.  The CHECK
> constraint happens before the record is made.  See the vdbe that gets
> generated.

All good. Small sanity check though:


select 2 = cast( '2' as integer );
> 1

Ok. '2' is can be casted to 2. great...


select 2 = cast( 'a2' as integer );
> 0

Ok. 'a2' cannot really be casted to an integer. cool...


select 2 = cast( '2.1' as integer );
> 1

Hmmm..


select 2 = cast( '2abc' as integer );
> 1

What?!? Oh. "When casting a TEXT value to INTEGER, the longest possible
prefix of the value that can be interpreted as an integer number is
extracted from the TEXT value and the remainder ignored. ". ah. ok. weird.

So.

select cast( 'abc' as integer );
> 0

o k i d o k i . . .







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

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Nico Williams]

On Wed, Mar 6, 2013 at 4:20 PM, Petite Abeille  wrote:
> All good. Small sanity check though:
>
> select 2 = cast( '2.1' as integer );
>> 1
>
> Hmmm….
>
>
> select 2 = cast( '2abc' as integer );
>> 1
>
> What?!? Oh… "When casting a TEXT value to INTEGER, the longest possible 
> prefix of the value that can be interpreted as an integer number is extracted 
> from the TEXT value and the remainder ignored. "… ah… ok… weird…

Actually, while this is true here, it doesn't render the CHECK
constraint I mentioned earlier unsafe:

sqlite> CREATE TABLE toy1(a INTEGER);
sqlite> CREATE TABLE toy2(a INTEGER CHECK(typeof(a) = 'integer'));
sqlite> CREATE TABLE toy3(a INTEGER CHECK(a = CAST(a AS INTEGER)));
sqlite> INSERT INTO toy1 VALUES ('2a');
sqlite> INSERT INTO toy2 VALUES ('2a');
Error: constraint failed
sqlite> INSERT INTO toy3 VALUES ('2a');
Error: constraint failed
sqlite> INSERT INTO toy3 VALUES ('2.1');
Error: constraint failed
sqlite> INSERT INTO toy3 VALUES (2);
sqlite>
sqlite> INSERT INTO toy1 VALUES (2.1);
sqlite> INSERT INTO toy1 VALUES ('2a');
sqlite> INSERT INTO toy1 VALUES ('2.1');
sqlite> SELECT a, quote(a), typeof(a) FROM toy1;
2.1|2.1|real
2a|'2a'|text
2.1|2.1|real
sqlite>

The reason for this: the CAST didn't work the same as affinity type
conversions.  The value of [a] fails to get converted to integer when
it's '2a', '2.1', or 2.1, causing the equality conversion to fail.

This works well for INTEGER, REAL, and NUMERIC (check for yourself).
For BLOB and TEXT CHECK(a = CAST(a AS ...)) has the same effect as
CHECK(typeof(a) = '...').  If you use "a NONE CHECK(a = CAST(a AS
NONE))" that seems to work as if one has used NUMERIC.

In conclusion, if you want to allow affine type conversions on INSERT,
but not disallow values that cannot be so converted, then
CHECK(my_column = CAST(my_column AS )) works.  And if you want
to disallow values of incorrect types even when type conversion is
possible then use CHECK(typeof(my_column = ).  That's pretty
cool, IMO.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Petite Abeille]

On Mar 6, 2013, at 11:53 PM, Nico Williams  wrote:

>> o k i d o k i . . .
> 
> Oh.  Oh..  Ew..   Never mind then!

Yeah… a bit of a mind melt… nevertheless… such check should work as advertised… 
even handles nulls properly… perhaps too clever too... :D

"All magic comes with a price, Dearie" -- Mr Gold

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Nico Williams]

On Wed, Mar 6, 2013 at 4:20 PM, Petite Abeille  wrote:
> All good. Small sanity check though:
>
>
> select 2 = cast( '2' as integer );
>> 1
>
> Ok… '2' is can be casted to 2… great...
>
>
> select 2 = cast( 'a2' as integer );
>> 0
>
> Ok… 'a2' cannot really be casted to an integer… cool...
>
>
> select 2 = cast( '2.1' as integer );
>> 1
>
> Hmmm….
>
>
> select 2 = cast( '2abc' as integer );
>> 1
>
> What?!? Oh… "When casting a TEXT value to INTEGER, the longest possible 
> prefix of the value that can be interpreted as an integer number is extracted 
> from the TEXT value and the remainder ignored. "… ah… ok… weird…
>
> So…
>
> select cast( 'abc' as integer );
>> 0
>
> o k i d o k i . . .

Oh.  Oh..  Ew..   Never mind then!

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Petite Abeille]

On Mar 6, 2013, at 10:49 PM, Nico Williams  wrote:

> Ah, your confusion comes from the fact that type conversion still
> happens when the INSERT gets around to making the record.  The CHECK
> constraint happens before the record is made.  See the vdbe that gets
> generated.

All good. Small sanity check though:


select 2 = cast( '2' as integer );
> 1

Ok… '2' is can be casted to 2… great...


select 2 = cast( 'a2' as integer );
> 0

Ok… 'a2' cannot really be casted to an integer… cool...


select 2 = cast( '2.1' as integer );
> 1

Hmmm….


select 2 = cast( '2abc' as integer );
> 1

What?!? Oh… "When casting a TEXT value to INTEGER, the longest possible 
prefix of the value that can be interpreted as an integer number is extracted 
from the TEXT value and the remainder ignored. "… ah… ok… weird…

So…

select cast( 'abc' as integer );
> 0

o k i d o k i . . .







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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Nico Williams]

On Wed, Mar 6, 2013 at 3:49 PM, Nico Williams  wrote:
> On Wed, Mar 6, 2013 at 3:47 PM, Petite Abeille  
> wrote:
>> Indeed. Never mind :)
>
> Ah, your confusion comes from the fact that type conversion still
> happens when the INSERT gets around to making the record.  The CHECK
> constraint happens before the record is made.  See the vdbe that gets
> generated.

Oh... I wonder if there's a guarantee of that, or if it'd be possible
that in the future CHECK constraints might get post-type conversion
values.  But I think it'd be risky to ever make such a change.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Petite Abeille]

On Mar 6, 2013, at 10:47 PM, Nico Williams  wrote:

>> Hmmm… on second thought… is that an assignment in that check constraint?!? 
>> I.e. are you reassigning a to a new cast value?!?
> 
> No.  The only place where = is an assignment is in UPDATE statements,
> in the SET clause.

Yeah… dazed and confused… should get a grip... :))

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Nico Williams]

On Wed, Mar 6, 2013 at 3:47 PM, Petite Abeille  wrote:
> Indeed. Never mind :)

Ah, your confusion comes from the fact that type conversion still
happens when the INSERT gets around to making the record.  The CHECK
constraint happens before the record is made.  See the vdbe that gets
generated.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Petite Abeille]

On Mar 6, 2013, at 10:43 PM, Petite Abeille  wrote:

>> CREATE TABLE toy(a INTEGER CHECK(a = CAST(a AS INTEGER)));
> 
> Hmmm… on second thought… is that an assignment in that check constraint?!? 
> I.e. are you reassigning a to a new cast value?!?
> 
> Are not check constraint suppose to be boolean expression? 
> 
> Dazed and confused.

Indeed. Never mind :)

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Nico Williams]

On Wed, Mar 6, 2013 at 3:43 PM, Petite Abeille  wrote:
> On Mar 6, 2013, at 10:24 PM, Nico Williams  wrote:
>> CREATE TABLE toy(a INTEGER CHECK(a = CAST(a AS INTEGER)));
>
> Hmmm… on second thought… is that an assignment in that check constraint?!? 
> I.e. are you reassigning a to a new cast value?!?

No.  The only place where = is an assignment is in UPDATE statements,
in the SET clause.

> Are not check constraint suppose to be boolean expression?

It is.  That's an equality operator.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Petite Abeille]

On Mar 6, 2013, at 10:24 PM, Nico Williams  wrote:

> Nah, use this sort of CHECK constraint:
> 
> CREATE TABLE toy(a INTEGER CHECK(a = CAST(a AS INTEGER)));

Hmmm… on second thought… is that an assignment in that check constraint?!? I.e. 
are you reassigning a to a new cast value?!?

Are not check constraint suppose to be boolean expression? 

Dazed and confused.

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Nico Williams]

On Wed, Mar 6, 2013 at 3:29 PM, Petite Abeille  wrote:
> On Mar 6, 2013, at 10:24 PM, Nico Williams  wrote:
>> CREATE TABLE toy(a INTEGER CHECK(a = CAST(a AS INTEGER)));
>
> Any idea on the cost of such check? In term of overhead? Just curious.

Well, it's more than the cost of no check constraint.  Here's the
additional opcodes generated in my case:

5|SCopy|2|3|0||00|
6|ToInt|3|0|0||00|
7|Eq|3|9|2|collseq(BINARY)|6b|
8|Halt|19|2|0||00|

Of these the expensive one will be ToInt, though maybe SCopy too.
Dunno how expensive.

Note that the typeof() check constraint generates one more opcode:

5|Copy|2|4|0||00|
6|Function|0|4|3|typeof(1)|01|
7|String8|0|5|0|integer|00|
8|Eq|5|10|3||6a|
9|Halt|19|2|0||00|

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Petite Abeille]

On Mar 6, 2013, at 10:24 PM, Nico Williams  wrote:

> CREATE TABLE toy(a INTEGER CHECK(a = CAST(a AS INTEGER)));

Any idea on the cost of such check? In term of overhead? Just curious.

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Nico Williams]

On Wed, Mar 6, 2013 at 9:30 AM, Dominique Devienne  wrote:
> You don't have to use triggers, you can use a check constraint instead
> (simpler, but also perhaps faster as well?).
>
> If you do, you loose some of the implicit type conversions SQLite does,
> based on type affinity, so the "1" no longer gets converted to 1.

Nah, use this sort of CHECK constraint:

CREATE TABLE toy(a INTEGER CHECK(a = CAST(a AS INTEGER)));

sqlite> insert into toy values (1);
sqlite> insert into toy values ('1');
sqlite> select quote(a) from toy;
1
1
sqlite>

Cool, no?

> I also would prefer a strong-typing mode though (as an opt-in pragma for
> example), rather than adding a bunch of check constraints, and built-in
> strong-typing would likely be faster. But Dr. Hipp prefers dynamic typing,
> so dynamic typing it is :).

I too would like an option for stronger typing, but CHECK constraints
will do for now.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Ryan Johnson]

On 06/03/2013 10:30 AM, Dominique Devienne wrote:

On Wed, Mar 6, 2013 at 3:29 PM, Ryan Johnson 
wrote:

Off topic, I'd love a way to request strong typing for a column (so that

attempts to store 'abc' into an int column would fail). You can emulate it
with a pair of before/update triggers (select raise(...) where
typeof(intcol)!='integer'), but that's clunky. Oh well... in retrospect,
most of the times I've been bitten by type mismatches were probably either
due to this bug or (more likely) due to my not specifying any affinity at
all and then being surprised when 1 != '1'.

You don't have to use triggers, you can use a check constraint instead
(simpler, but also perhaps faster as well?).

If you do, you loose some of the implicit type conversions SQLite does,
based on type affinity, so the "1" no longer gets converted to 1.
... which is why I prefer triggers. They kick in after the column's 
numeric affinity has a chance to convert "1" to 1.


Gives the best of both worlds, once you get past the boilerplate:

sqlite> create table t(x number);
sqlite> create trigger t1 before insert on t begin
   ...> select raise(FAIL, 'Numbers only!') where typeof(new.x) =='text';
   ...> end;
sqlite> insert into t values(1);
sqlite> insert into t values('1');
sqlite> insert into t values('a');
Error: Numbers only!
sqlite> select x,typeof(x) from t;
1|integer
1|integer

Ryan

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

[sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Dominique Devienne]

On Wed, Mar 6, 2013 at 3:29 PM, Ryan Johnson 
wrote:
> Off topic, I'd love a way to request strong typing for a column (so that
attempts to store 'abc' into an int column would fail). You can emulate it
with a pair of before/update triggers (select raise(...) where
typeof(intcol)!='integer'), but that's clunky. Oh well... in retrospect,
most of the times I've been bitten by type mismatches were probably either
due to this bug or (more likely) due to my not specifying any affinity at
all and then being surprised when 1 != '1'.

You don't have to use triggers, you can use a check constraint instead
(simpler, but also perhaps faster as well?).

If you do, you loose some of the implicit type conversions SQLite does,
based on type affinity, so the "1" no longer gets converted to 1.

I also would prefer a strong-typing mode though (as an opt-in pragma for
example), rather than adding a bunch of check constraints, and built-in
strong-typing would likely be faster. But Dr. Hipp prefers dynamic typing,
so dynamic typing it is :).

--DD

sqlite> create table t (id number);
sqlite> insert into t values (1);
sqlite> insert into t values ("1");
sqlite> insert into t values ("1x");
sqlite> select id, typeof(id) from t;
1|integer
1|integer
1x|text

sqlite> create table tt (id number check (typeof(id) = 'integer'));
sqlite> insert into tt values (1);
sqlite> insert into tt values ("1");
Error: constraint failed
sqlite> insert into tt values ("1x");
Error: constraint failed
sqlite> select id, typeof(id) from tt;
1|integer

// recent SQLite versions also now report the name of the constraint that
failed, if available.
sqlite> create table ttt (id number, constraint id_is_integer check
(typeof(id) = 'integer'));
sqlite> insert into ttt values (1);
sqlite> insert into ttt values ("1");
Error: constraint id_is_integer failed
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users