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

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

ite-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. out

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

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 str

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

ung 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] ______

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

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

, 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 > > CA

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

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

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

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,

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

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

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

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

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

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

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

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

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

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.

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

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

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

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

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

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

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

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

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

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

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

qlite.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..

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

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

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

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 >

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

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

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

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

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?!?

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

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.

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

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

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

[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