.import fails CHECK constraint on valid data

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

.import fails CHECK constraint on valid data

James K. Lowden
$ sqlite3 db "create table T (t integer not null);"
$ sqlite3 db "create table Tc (t integer not null
                                check(typeof(t) = 'integer'));"
$ echo 1 > dat
$ sqlite3 db ".import 'dat' T"
$ sqlite3 db ".import 'dat' Tc"
dat:1: INSERT failed: CHECK constraint failed: Tc
$ sqlite3 db "insert into Tc select * from T;"
$ sqlite3 db "select * from Tc"
1
$ sqlite3 db "select typeof(t) from T"
integer
$ sqlite3 db "select typeof(t) from Tc"
integer

Why does the .import command cause the CHECK constraint to fail, when
an ordinary INSERT does not?  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: .import fails CHECK constraint on valid data

Shawn Wagner
The check constraint is probably being evaluated (with t as a string)
before any type conversion to match the column affinity is done.

On Sun, Mar 10, 2019, 2:05 PM James K. Lowden <[hidden email]>
wrote:

> $ sqlite3 db "create table T (t integer not null);"
> $ sqlite3 db "create table Tc (t integer not null
>                                 check(typeof(t) = 'integer'));"
> $ echo 1 > dat
> $ sqlite3 db ".import 'dat' T"
> $ sqlite3 db ".import 'dat' Tc"
> dat:1: INSERT failed: CHECK constraint failed: Tc
> $ sqlite3 db "insert into Tc select * from T;"
> $ sqlite3 db "select * from Tc"
> 1
> $ sqlite3 db "select typeof(t) from T"
> integer
> $ sqlite3 db "select typeof(t) from Tc"
> integer
>
> Why does the .import command cause the CHECK constraint to fail, when
> an ordinary INSERT does not?
>
> --jkl
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: .import fails CHECK constraint on valid data

James K. Lowden
In reply to this post by James K. Lowden
On Sun, 10 Mar 2019 17:04:46 -0400
"James K. Lowden" <[hidden email]> wrote:

> Why does the .import command cause the CHECK constraint to fail, when
> an ordinary INSERT does not?  

On Sun, 10 Mar 2019 14:12:33 -0700
Shawn Wagner <[hidden email]> wrote:

> The check constraint is probably being evaluated (with t as a string)
> before any type conversion to match the column affinity is done.

Does anyone have a better answer?  Isn't .import supposed to work like
INSERT?  If it doesn't, CHECK constraints for type safety are useless
for tables that are loaded from files.  

--jkl

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: .import fails CHECK constraint on valid data

Shawn Wagner
A manual INSERT demonstrates the same behavior, actually. Using your Tc
table:

sqlite> insert into Tc values ('12');
Error: CHECK constraint failed: Tc

The thing about .import is that, instead of guessing what type each value
it reads is, they're all just bound to an insert statement as strings. When
the row is actually stored in the table, those strings are converted to
numeric types if the relevant columns have the appropriate affinity and it
can be done losslessly. Details:
https://www.sqlite.org/datatype3.html#type_affinity

For good or bad, check constraints appear to be evaluated before this
conversion.

On Tue, Mar 19, 2019 at 1:43 PM James K. Lowden <[hidden email]>
wrote:

> On Sun, 10 Mar 2019 17:04:46 -0400
> "James K. Lowden" <[hidden email]> wrote:
>
> > Why does the .import command cause the CHECK constraint to fail, when
> > an ordinary INSERT does not?
>
> On Sun, 10 Mar 2019 14:12:33 -0700
> Shawn Wagner <[hidden email]> wrote:
>
> > The check constraint is probably being evaluated (with t as a string)
> > before any type conversion to match the column affinity is done.
>
> Does anyone have a better answer?  Isn't .import supposed to work like
> INSERT?  If it doesn't, CHECK constraints for type safety are useless
> for tables that are loaded from files.
>
> --jkl
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: .import fails CHECK constraint on valid data

D Burgess
>
> For good or bad, check constraints appear to be evaluated before this
>
conversion.

I call that very bad.

On Wed, Mar 20, 2019 at 7:58 AM Shawn Wagner <[hidden email]>
wrote:

> A manual INSERT demonstrates the same behavior, actually. Using your Tc
> table:
>
> sqlite> insert into Tc values ('12');
> Error: CHECK constraint failed: Tc
>
> The thing about .import is that, instead of guessing what type each value
> it reads is, they're all just bound to an insert statement as strings. When
> the row is actually stored in the table, those strings are converted to
> numeric types if the relevant columns have the appropriate affinity and it
> can be done losslessly. Details:
> https://www.sqlite.org/datatype3.html#type_affinity
>
> For good or bad, check constraints appear to be evaluated before this
> conversion.
>
> On Tue, Mar 19, 2019 at 1:43 PM James K. Lowden <[hidden email]>
> wrote:
>
> > On Sun, 10 Mar 2019 17:04:46 -0400
> > "James K. Lowden" <[hidden email]> wrote:
> >
> > > Why does the .import command cause the CHECK constraint to fail, when
> > > an ordinary INSERT does not?
> >
> > On Sun, 10 Mar 2019 14:12:33 -0700
> > Shawn Wagner <[hidden email]> wrote:
> >
> > > The check constraint is probably being evaluated (with t as a string)
> > > before any type conversion to match the column affinity is done.
> >
> > Does anyone have a better answer?  Isn't .import supposed to work like
> > INSERT?  If it doesn't, CHECK constraints for type safety are useless
> > for tables that are loaded from files.
> >
> > --jkl
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users