List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:November 7 2001 5:05pm
Subject:Re: INSERT ... SELECT implies IGNORE (bug?)
View as plain text  
At 10:58 AM -0500 11/7/01, Christopher Book wrote:
>No this is really a good thing.  I often do stuff like this with temporary
>tables to mimic a UNION.
>You can create a temp table, insert several sets of rows based on many
>different selects, and allow the unique keys to prevent duplicates.

That's how INSERT IGNORE should work, but not INSERT.
The behavior described below sounds problematic.

Keith, do you get an error as you expect for earlier versions of MySQL.

>
>Chris
>
>
>>>>>>>>>>>>>>>>I'm using MySQL 3.23.44
> and doing inserts like this:
>
>INSERT INTO documents_test
>(collection_id, fileset_id, abstract, bates_begin, bates_end, date,
>document_code, file_code_begin, file_code_end, notes, ocr,
>page_count, quotes, rank, title, import_collection_id,
>import_document_code)
>SELECT 41, fileset_id, abstract, bates_begin, bates_end, date,
>document_code, file_code_begin, file_code_end, notes, ocr,
>page_count, quotes, rank, title, collection_id, document_code
>FROM documents_product_design
>WHERE collection_id = 54
>   AND document_id = 2
>
>If I run the query once, it inserts one row as expected.  If I run it
>again, then I'd expect an error, because documents_test has a unique
>key on collection_id + document_code.  What actually happens is that
>no rows are inserted but there is no error, just as if I had used
>INSERT IGNORE.  Is this a bug?
>--
>Keith C. Ivey <keith@stripped>
>Washington, DC
Thread
INSERT ... SELECT implies IGNORE (bug?)Christopher Book7 Nov
  • RE: INSERT ... SELECT implies IGNORE (bug?)Carsten H. Pedersen7 Nov
  • Re: INSERT ... SELECT implies IGNORE (bug?)Paul DuBois7 Nov
    • Re: INSERT ... SELECT implies IGNORE (bug?)Keith C. Ivey7 Nov
RE: INSERT ... SELECT implies IGNORE (bug?)Rick Emery7 Nov
  • RE: INSERT ... SELECT implies IGNORE (bug?)Keith C. Ivey7 Nov
RE: INSERT ... SELECT implies IGNORE (bug?)Christopher Book7 Nov
RE: INSERT ... SELECT implies IGNORE (bug?)Heikki Tuuri7 Nov
  • RE: INSERT ... SELECT implies IGNORE (bug?)Michael Widenius8 Nov
RE: INSERT ... SELECT implies IGNORE (bug?)Christopher Book7 Nov
  • RE: INSERT ... SELECT implies IGNORE (bug?)Keith C. Ivey8 Nov