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
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
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
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]
__
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
, 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
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
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
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
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,
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
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
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
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
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
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
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
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
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
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.
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
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
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
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
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
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
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 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
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
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
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..
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
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
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
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
>
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
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
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
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
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?!?
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
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.
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
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
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
45 matches
Mail list logo