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 iss
gt; 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.jk
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]
> Messa
ung 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@sqlit
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
riday, 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 I
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 th
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.
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 t
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
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 surp
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 surp
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 ol
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
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
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
>
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:
>
>
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 r
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 ac
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
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
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,
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 at
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 dynami
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
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 (selec
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,
> disallowin
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
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,
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 t
+ 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
T
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 va
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" --
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...
>
>
> se
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 sanit
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 happen
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 c
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.
___
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 boolea
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?
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 chec
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.
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.
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 long
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 (selec
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 t
46 matches
Mail list logo
