Re: [sqlite] Weird CHECK behavior

[Duquette, William H (318K)]

On 4/7/11 4:37 PM, "Jay A. Kreibich"  wrote:


>On Thu, Apr 07, 2011 at 03:45:18PM -0700, Duquette, William H (318K)
>scratched on the wall:
>
>> Hmmm.  I tried this; but this constraint fails for ANY value I give it.
>
>> I tried this:
>> 
>>  CHECK (0.0 <= CAST (value AS real))
>> 
>> But this just casts the string 'NONESUCH' to 0.0, which is no help.
>
>
>  What about   CHECK( value == CAST( value AS real ) ) ?

It took me a while to get back to this--but, YES, that does the trick.
Thanks very much!

Will


>
>
>  Otherwise, I'm not a Tcl guy, so someone else will need to jump in.
>
>   -j
>
>-- 
>Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
>"Intelligence is like underwear: it is important that you have it,
> but showing it to the wrong people has the tendency to make them
> feel uncomfortable." -- Angela Johnson

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Weird CHECK behavior

[Jay A. Kreibich]

On Thu, Apr 07, 2011 at 03:45:18PM -0700, Duquette, William H (318K) scratched 
on the wall:

> Hmmm.  I tried this; but this constraint fails for ANY value I give it.

> I tried this:
> 
>  CHECK (0.0 <= CAST (value AS real))
> 
> But this just casts the string 'NONESUCH' to 0.0, which is no help.


  What about   CHECK( value == CAST( value AS real ) ) ?


  Otherwise, I'm not a Tcl guy, so someone else will need to jump in.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Weird CHECK behavior

[Duquette, William H (318K)]

On 4/7/11 2:52 PM, "Jay A. Kreibich"  wrote:


>On Thu, Apr 07, 2011 at 02:44:49PM -0700, Duquette, William H (318K)
>scratched on the wall:
>> Howdy!
>> 
>> I have a database with tables defined like this:
>> 
>> CREATE TABLE table1 (
>> idINTEGER PRIMARY KEY,
>> value REAL DEFAULT 1.0 CHECK (0.0 <= value)
>> );
>> 
>> CREATE TABLE table2 (
>> idINTEGER PRIMARY KEY,
>> value REAL DEFAULT 1.0 CHECK (0.0 <= value AND value <= 1.0)
>> );
>> 
>> The following statements should each fail with a constraint error:
>> 
>> UPDATE table1 SET value='NONESUCH' WHERE id=1;
>> UPDATE table2 SET value='NONESUCH' WHERE id=1;
>> 
>> 
>> For some reason, setting the value in table2 fails, as it should, but
>> setting the value in table1 succeeds when it shouldn't.
>
>  Text values are greater than numeric values.  Hence the check is
>  valid:  http://sqlite.org/datatype3.html#comparisons
>
>
>  You might want to addCHECK ( typeof( value ) == 'real' )

Hmmm.  I tried this; but this constraint fails for ANY value I give it.
I'm using the  Tcl interface to SQLite3; a numeric value in Tcl can be
passed to SQLite3 as a number or as a string, depending on how it has been
used in the Tcl.  When it's passed as a string, SQLite3 will still use it
as a number in expressions, but apparently it isn't converting it to an
actual number before the constraint is checked.

I tried this:

 CHECK (0.0 <= CAST (value AS real))

But this just casts the string 'NONESUCH' to 0.0, which is no help.

I tried this:

 CHECK (0.0 <= (value + 0.0))

hoping that that would fail if value was really non-numeric, but succeed
otherwise  Nope; no help.

Is there a way that I can write a constraint that accepts both real
numbers and numeric strings, and does the range checking, but rejects
non-numeric strings?  I can do the type-checking in Tcl if I have to, but
I'd rather not have to.

Will









>
>
>   -j
>
>-- 
>Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
>"Intelligence is like underwear: it is important that you have it,
> but showing it to the wrong people has the tendency to make them
> feel uncomfortable." -- Angela Johnson
>___
>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] Weird CHECK behavior

[Duquette, William H (318K)]

On 4/7/11 2:52 PM, "Jay A. Kreibich"  wrote:


>On Thu, Apr 07, 2011 at 02:44:49PM -0700, Duquette, William H (318K)
>scratched on the wall:
>> Howdy!
>> 
>> I have a database with tables defined like this:
>> 
>> CREATE TABLE table1 (
>> idINTEGER PRIMARY KEY,
>> value REAL DEFAULT 1.0 CHECK (0.0 <= value)
>> );
>> 
>> CREATE TABLE table2 (
>> idINTEGER PRIMARY KEY,
>> value REAL DEFAULT 1.0 CHECK (0.0 <= value AND value <= 1.0)
>> );
>> 
>> The following statements should each fail with a constraint error:
>> 
>> UPDATE table1 SET value='NONESUCH' WHERE id=1;
>> UPDATE table2 SET value='NONESUCH' WHERE id=1;
>> 
>> 
>> For some reason, setting the value in table2 fails, as it should, but
>> setting the value in table1 succeeds when it shouldn't.
>
>  Text values are greater than numeric values.  Hence the check is
>  valid:  http://sqlite.org/datatype3.html#comparisons
>
>
>  You might want to addCHECK ( typeof( value ) == 'real' )

Aha!  Thanks very much.

Will

>
>
>   -j
>
>-- 
>Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
>"Intelligence is like underwear: it is important that you have it,
> but showing it to the wrong people has the tendency to make them
> feel uncomfortable." -- Angela Johnson
>___
>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] Weird CHECK behavior

[Jay A. Kreibich]

On Thu, Apr 07, 2011 at 02:44:49PM -0700, Duquette, William H (318K) scratched 
on the wall:
> Howdy!
> 
> I have a database with tables defined like this:
> 
> CREATE TABLE table1 (
> idINTEGER PRIMARY KEY,
> value REAL DEFAULT 1.0 CHECK (0.0 <= value)
> );
> 
> CREATE TABLE table2 (
> idINTEGER PRIMARY KEY,
> value REAL DEFAULT 1.0 CHECK (0.0 <= value AND value <= 1.0)
> );
> 
> The following statements should each fail with a constraint error:
> 
> UPDATE table1 SET value='NONESUCH' WHERE id=1;
> UPDATE table2 SET value='NONESUCH' WHERE id=1;
> 
> 
> For some reason, setting the value in table2 fails, as it should, but
> setting the value in table1 succeeds when it shouldn't.

  Text values are greater than numeric values.  Hence the check is
  valid:  http://sqlite.org/datatype3.html#comparisons


  You might want to addCHECK ( typeof( value ) == 'real' )


   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users