Re: [sqlite] .import fails CHECK constraint on valid data
>
> 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
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
> wrote:
>
> > On Sun, 10 Mar 2019 17:04:46 -0400
> > "James K. Lowden" 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 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
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .import fails CHECK constraint on valid data
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
wrote:
> On Sun, 10 Mar 2019 17:04:46 -0400
> "James K. Lowden" 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 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .import fails CHECK constraint on valid data
On Sun, 10 Mar 2019 17:04:46 -0400 "James K. Lowden" 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 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .import fails CHECK constraint on valid data
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 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] .import fails CHECK constraint on valid data
$ 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
