message.
On Sat, Mar 9, 2013 at 9:00 AM, sqlite-users-requ...@sqlite.org wrote:
Message: 17
Date: Fri, 8 Mar 2013 14:26:06 -0500
From: James K. Lowden jklow...@schemamania.org
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join
Date: Fri, 8 Mar 2013 14:26:06 -0500
From: James K. Lowden jklow...@schemamania.org
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer
join inconsistency]
Message-ID: 20130308142606.15776668.jklow...@schemamania.org
Content-Type: text/plain; charset
On 9 Mar 2013, at 8:01pm, James K. Lowden jklow...@schemamania.org 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
On Thu, 7 Mar 2013 19:20:44 +0100
Petite Abeille petite.abei...@gmail.com wrote:
In conclusion, if you want to allow affine type conversions on
INSERT, but not disallow values that cannot be so converted, then
CHECK(my_column = CAST(my_column AS TYPE)) works. And if you want
to disallow
On Thu, 7 Mar 2013 07:36:25 -0600
Michael Black mdblac...@yahoo.com 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
On Thu, 7 Mar 2013 18:45:23 +
Simon Slavin slav...@bigfraud.org wrote:
what do you think the desired behaviour would be for
CAST('0.9' AS INTEGER)
I know what I want. Perhaps this can be fixed in SQLite4.
Sorry, but CAST is not a math function. There's probably a language
, March 08, 2013 2:45 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join
inconsistency]
On Thu, 7 Mar 2013 18:45:23 +
Simon Slavin slav...@bigfraud.org wrote:
what do you think the desired behaviour would be for
CAST('0.9' AS INTEGER
On Fri, Mar 8, 2013 at 1:26 PM, James K. Lowden
jklow...@schemamania.org wrote:
On Thu, 7 Mar 2013 19:20:44 +0100
Petite Abeille petite.abei...@gmail.com wrote:
Yeah? 'cool' is not necessarily how I would describe it? having a
check constraint 'magically' coerce - change! - the inserted data
Original message
From: Nico Williams n...@cryptonector.com
Date:
To: General Discussion of SQLite Database sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join
inconsistency]
___
sqlite-users
[WAS: inner vs. outer join
inconsistency]
On Mar 6, 2013, at 10:49 PM, Nico Williams n...@cryptonector.com wrote:
Ah, your confusion comes from the fact that type conversion still
happens when the INSERT gets around to making the record. The CHECK
constraint happens before the record is made
On 7 Mar 2013, at 1:36pm, Michael Black mdblac...@yahoo.com 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
On Thu, Mar 7, 2013 at 7:36 AM, Michael Black mdblac...@yahoo.com 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
On 07/03/2013 9:28 AM, Simon Slavin wrote:
On 7 Mar 2013, at 1:36pm, Michael Black mdblac...@yahoo.com 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
On Mar 7, 2013, at 11:07 AM, Ryan Johnson ryan.john...@cs.utoronto.ca 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 06/03/2013 10:30 AM, Dominique Devienne wrote:
On Wed, Mar 6, 2013 at 3:29 PM, Ryan Johnson ryan.john...@cs.utoronto.ca
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 07/03/2013 11:14 AM, Doug Currie wrote:
On Mar 7, 2013, at 11:07 AM, Ryan Johnson ryan.john...@cs.utoronto.ca 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 Thu, Mar 7, 2013 at 11:12 AM, Ryan Johnson
ryan.john...@cs.utoronto.ca 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.
On 07/03/2013 12:27 PM, Nico Williams wrote:
On Thu, Mar 7, 2013 at 11:12 AM, Ryan Johnson
ryan.john...@cs.utoronto.ca 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
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
ryan.john...@cs.utoronto.ca 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
On Thu, Mar 7, 2013 at 11:44 AM, Ryan Johnson
ryan.john...@cs.utoronto.ca wrote:
On 07/03/2013 12:27 PM, Nico Williams wrote:
On Thu, Mar 7, 2013 at 11:12 AM, Ryan Johnson
ryan.john...@cs.utoronto.ca wrote:
I would argue that, if a column has type affinity, CHECK should work with
the value
On 7 Mar 2013, at 4:07pm, Ryan Johnson ryan.john...@cs.utoronto.ca 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
On 07/03/2013 1:07 PM, Nico Williams wrote:
On Thu, Mar 7, 2013 at 11:44 AM, Ryan Johnson
ryan.john...@cs.utoronto.ca wrote:
On 07/03/2013 12:27 PM, Nico Williams wrote:
On Thu, Mar 7, 2013 at 11:12 AM, Ryan Johnson
ryan.john...@cs.utoronto.ca wrote:
I would argue that, if a column has type
On 07/03/2013 1:15 PM, Simon Slavin wrote:
On 7 Mar 2013, at 4:07pm, Ryan Johnson ryan.john...@cs.utoronto.ca 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 7 Mar 2013, at 6:27pm, Ryan Johnson ryan.john...@cs.utoronto.ca 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
On Thu, Mar 7, 2013 at 12:20 PM, Ryan Johnson
ryan.john...@cs.utoronto.ca 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
On Mar 7, 2013, at 6:21 AM, Nico Williams n...@cryptonector.com wrote:
In conclusion, if you want to allow affine type conversions on INSERT,
but not disallow values that cannot be so converted, then
CHECK(my_column = CAST(my_column AS TYPE)) works. And if you want
to disallow values of
On 07/03/2013 1:45 PM, Simon Slavin wrote:
On 7 Mar 2013, at 6:27pm, Ryan Johnson ryan.john...@cs.utoronto.ca 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 07/03/2013 1:48 PM, Nico Williams wrote:
On Thu, Mar 7, 2013 at 12:20 PM, Ryan Johnson
ryan.john...@cs.utoronto.ca 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 Mar 7, 2013, at 7:53 PM, Ryan Johnson ryan.john...@cs.utoronto.ca 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
On Thu, Mar 7, 2013 at 12:53 PM, Ryan Johnson
ryan.john...@cs.utoronto.ca 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
On 07/03/2013 3:14 PM, Nico Williams wrote:
On Thu, Mar 7, 2013 at 12:53 PM, Ryan Johnson
ryan.john...@cs.utoronto.ca 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 Wed, Mar 6, 2013 at 3:29 PM, Ryan Johnson ryan.john...@cs.utoronto.ca
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
On 06/03/2013 10:30 AM, Dominique Devienne wrote:
On Wed, Mar 6, 2013 at 3:29 PM, Ryan Johnson ryan.john...@cs.utoronto.ca
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 Wed, Mar 6, 2013 at 9:30 AM, Dominique Devienne ddevie...@gmail.com 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
On Mar 6, 2013, at 10:24 PM, Nico Williams n...@cryptonector.com 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 3:29 PM, Petite Abeille petite.abei...@gmail.com wrote:
On Mar 6, 2013, at 10:24 PM, Nico Williams n...@cryptonector.com 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
On Mar 6, 2013, at 10:24 PM, Nico Williams n...@cryptonector.com 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
On Wed, Mar 6, 2013 at 3:43 PM, Petite Abeille petite.abei...@gmail.com wrote:
On Mar 6, 2013, at 10:24 PM, Nico Williams n...@cryptonector.com 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.
On Mar 6, 2013, at 10:43 PM, Petite Abeille petite.abei...@gmail.com 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
On Wed, Mar 6, 2013 at 3:47 PM, Petite Abeille petite.abei...@gmail.com 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
On Mar 6, 2013, at 10:47 PM, Nico Williams n...@cryptonector.com 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.
On Wed, Mar 6, 2013 at 3:49 PM, Nico Williams n...@cryptonector.com wrote:
On Wed, Mar 6, 2013 at 3:47 PM, Petite Abeille petite.abei...@gmail.com
wrote:
Indeed. Never mind :)
Ah, your confusion comes from the fact that type conversion still
happens when the INSERT gets around to making the
On Mar 6, 2013, at 10:49 PM, Nico Williams n...@cryptonector.com wrote:
Ah, your confusion comes from the fact that type conversion still
happens when the INSERT gets around to making the record. The CHECK
constraint happens before the record is made. See the vdbe that gets
generated.
All
On Wed, Mar 6, 2013 at 4:20 PM, Petite Abeille petite.abei...@gmail.com 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…
On Mar 6, 2013, at 11:53 PM, Nico Williams n...@cryptonector.com 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
On Wed, Mar 6, 2013 at 4:20 PM, Petite Abeille petite.abei...@gmail.com 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
46 matches
Mail list logo
