On Mon, Feb 3, 2020 at 5:35 PM Richard Hipp <d...@sqlite.org> wrote:
> On 2/3/20, Dominique Devienne <ddevie...@gmail.com> wrote:
> > On Sun, Feb 2, 2020 at 12:50 AM Richard Hipp <d...@sqlite.org> wrote:
>
> This is the SQL:
>
> CREATE TABLE t1(x INT CHECK(typeof(x)=='integer'));
> INSERT INTO t1(x) VALUES('123');
>
> You say that you want to prevent the use of the string literal '123'
> for inserting into the integer field x. That will no longer be
> possible in SQLite beginning with 3.32.0 (assuming the change
> currently on trunk goes through.)
>
> But, why do you want to do that? How do you prevent the use of a
> string literal to initialize an integer field in MySQL, PosgreSQL, SQL
> Server, and Oracle - all of which accept and run the SQL above
> (without the CHECK constraint) with no errors?
Right. Implicit conversion also happen in these other DBs (I just
checked Oracle,
but I trust you're way more qualified to assert that me).
> If your goal is to prevent an actual string from being stored in the
> "x" column, then the legacy CHECK constraint still works for that.
> The following insert still fails:
>
> INSERT INTO t1(x) VALUES('xyzzy');
Right again. It fails with "ORA-01722: invalid number" on Oracle.
(no need for a CHECK constraint of course)
> But, you will no longer be allowed to prevent the type coercion that
> forces the '123' value into an integer 123, I think. At least, I do
> not see a way to do that on trunk right now.
OK. I was more thinking of the '123' staying as text-typed in the DB.
But if it is coerced into the column's type (well, "affinity", not type per se),
then whether the value is bound as a string or a integer should be immaterial.
I still think my code shouldn't be binding values of a type different
than the column's,
and would still greatly prefer "strong *static* typing", which I
emulated with CHECK typeof(),
since it smells like a bug in the code IMHO, but as long as the stored
value is "OK", sure
that makes little differences in the end.
So now that I understand the better, so be it I guess.
I'm sure you have a good reason to make that change, despite the
surprising break in BC for SQLite.
Thanks for taking the time to spell it out for me. --DD
PS: I still wish for a pragma for strong static typing (no need for
CHECK typeof()),
and now also wish for that to happen even before implicit
conversions. But I've long
accepted this is unlikely to ever happen :(
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
