Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]
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]
On Sat, 9 Mar 2013 09:43:35 -0800 Peter Haworth 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, > wrote: > > > Message: 17 > > Date: Fri, 8 Mar 2013 14:26:06 -0500 > > From: "James K. Lowden" > > 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 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]
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, wrote: > Message: 17 > Date: Fri, 8 Mar 2013 14:26:06 -0500 > From: "James K. Lowden" > 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 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]
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 Date: To: General Discussion of SQLite Database 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]
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]
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 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]
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]
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]
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]
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]
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]
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]
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]
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]
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]
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]
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]
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]
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]
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]
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]
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]
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]
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]
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]
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 t1; sqlite> select x,typeof(x) f
Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]
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]
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]
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]
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]
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 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]
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]
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]
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]
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]
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]
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]
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]
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]
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]
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]
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]
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]
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]
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]
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