Re: [sqlite] INSERT OR IGNORE ignores constraints. Bug ?

[Keith Medcalf]

Simon,

Policy is being enforced.  You specifically declared in the table definition 
that rows must have (a > 10) in order to be "in the table".  The IGNORE as in 
INSERT OR IGNORE means exactly and precisely what it says:  INSERT the record 
if it is valid and IGNORE it oherwise.  You tried to insert a "bad" row and 
specified to ignore the error causing rows so that operation was ignored and 
the record was not inserted.

This is working as designed and as documented.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
>Sent: Thursday, 21 December, 2017 10:50
>To: SQLite mailing list
>Subject: [sqlite] INSERT OR IGNORE ignores constraints. Bug ?
>
>
>
>On 21 Dec 2017, at 3:46pm, David Raymond 
>wrote:
>
>> The only potential problem with "insert or ignore into" is that it
>will ignore any constraint violation for that record insert
>
>Wait.  What ?
>
>SQLite version 3.19.3 2017-06-27 16:48:08
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> CREATE TABLE MyTable (a INTEGER, CONSTRAINT noless CHECK (a >
>10));
>sqlite> INSERT INTO MyTable VALUES (15);
>sqlite> INSERT INTO MyTable VALUES (5);
>Error: CHECK constraint failed: noless
>sqlite> INSERT OR IGNORE INTO MyTable VALUES (6);
>sqlite> SELECT * FROM MyTable;
>15
>sqlite>
>
>What the hell ?  Why does that work ?  Isn’t it a huge bug ?  How did
>you discover it ?
>
>My understanding is that using INSERT OR IGNORE meant that bad
>inserts would fail, but they would do so silently, without triggering
>an error result.
>
><https://sqlite.org/lang_conflict.html>
>
>"When an applicable constraint violation occurs, the IGNORE
>resolution algorithm skips the one row that contains the constraint
>violation and continues processing subsequent rows of the SQL
>statement as if nothing went wrong. Other rows before and after the
>row that contained the constraint violation are inserted or updated
>normally."
>
>If I understand correctly, "the IGNORE resolution algorithm skips the
>one row that contains the constraint violation and continues
>processing subsequent rows of the SQL statement as if nothing went
>wrong" means that a row that violates constraints will not be
>inserted.
>
>I thought I could enforce policy by setting constraints.  Apparently
>not.
>
>Simon.
>___
>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] INSERT OR IGNORE ignores constraints. Bug ?

[Simon Slavin]

On 21 Dec 2017, at 5:58pm, Igor Tandetnik  wrote:

> Isn't that precisely what happened in your example? Inserting 6 failed 
> silently. What again seems to be the problem?

I’m sorry.  You and Scott are quite right.  I have no idea what I was thinking.

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

Re: [sqlite] INSERT OR IGNORE ignores constraints. Bug ?

[Scott Robison]

On Dec 21, 2017 10:50 AM, "Simon Slavin"  wrote:



On 21 Dec 2017, at 3:46pm, David Raymond  wrote:

> The only potential problem with "insert or ignore into" is that it will
ignore any constraint violation for that record insert

Wait.  What ?

SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE MyTable (a INTEGER, CONSTRAINT noless CHECK (a > 10));
sqlite> INSERT INTO MyTable VALUES (15);
sqlite> INSERT INTO MyTable VALUES (5);
Error: CHECK constraint failed: noless
sqlite> INSERT OR IGNORE INTO MyTable VALUES (6);
sqlite> SELECT * FROM MyTable;
15
sqlite>

What the hell ?  Why does that work ?  Isn’t it a huge bug ?  How did you
discover it ?

My understanding is that using INSERT OR IGNORE meant that bad inserts
would fail, but they would do so silently, without triggering an error
result.


Insert 15 succeeded, 5 failed with error, 6 failed without error. Seems
exactly as advertised. What am I missing?

Note that or ignore is statement specific, not transaction specific, if I
understand correctly.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR IGNORE ignores constraints. Bug ?

[Igor Tandetnik]

On 12/21/2017 12:50 PM, Simon Slavin wrote:

SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE MyTable (a INTEGER, CONSTRAINT noless CHECK (a > 10));
sqlite> INSERT INTO MyTable VALUES (15);
sqlite> INSERT INTO MyTable VALUES (5);
Error: CHECK constraint failed: noless
sqlite> INSERT OR IGNORE INTO MyTable VALUES (6);
sqlite> SELECT * FROM MyTable;
15
sqlite>

What the hell ?  Why does that work ?  Isn’t it a huge bug ?  How did you 
discover it ?

My understanding is that using INSERT OR IGNORE meant that bad inserts would 
fail, but they would do so silently, without triggering an error result.


Isn't that precisely what happened in your example? Inserting 6 failed 
silently. What again seems to be the problem?


If I understand correctly, "the IGNORE resolution algorithm skips the one row that 
contains the constraint violation and continues processing subsequent rows of the SQL 
statement as if nothing went wrong" means that a row that violates constraints will 
not be inserted.


And in your example, it was not.
--
Igor Tandetnik

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

[sqlite] INSERT OR IGNORE ignores constraints. Bug ?

[Simon Slavin]

On 21 Dec 2017, at 3:46pm, David Raymond  wrote:

> The only potential problem with "insert or ignore into" is that it will 
> ignore any constraint violation for that record insert

Wait.  What ?

SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE MyTable (a INTEGER, CONSTRAINT noless CHECK (a > 10));
sqlite> INSERT INTO MyTable VALUES (15);
sqlite> INSERT INTO MyTable VALUES (5);
Error: CHECK constraint failed: noless
sqlite> INSERT OR IGNORE INTO MyTable VALUES (6);
sqlite> SELECT * FROM MyTable;
15
sqlite> 

What the hell ?  Why does that work ?  Isn’t it a huge bug ?  How did you 
discover it ?

My understanding is that using INSERT OR IGNORE meant that bad inserts would 
fail, but they would do so silently, without triggering an error result.



"When an applicable constraint violation occurs, the IGNORE resolution 
algorithm skips the one row that contains the constraint violation and 
continues processing subsequent rows of the SQL statement as if nothing went 
wrong. Other rows before and after the row that contained the constraint 
violation are inserted or updated normally."

If I understand correctly, "the IGNORE resolution algorithm skips the one row 
that contains the constraint violation and continues processing subsequent rows 
of the SQL statement as if nothing went wrong" means that a row that violates 
constraints will not be inserted.

I thought I could enforce policy by setting constraints.  Apparently not.

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