Hi Zardosht,
I believe this is rather a fragile solution. I can think about problems
at least with counters, wrong auto_increment. Also triggers will get
processed for this non-inserted record.
Just out of curiosity, what kind of optimization you're trying to
achieve?
Regards,
Sergey
On Wed, Feb 24, 2010 at 01:20:12PM -0500, Zardosht Kasheff wrote:
> Ok, so if I understand this correctly, the following if clause:
>
> thd->lex->ignore && thd->lex->duplicates == SQL_ERROR
>
> if it evaluates to true, I can assume that the user is doing "insert ignore".
>
> How I plan to use this is as follows. Suppose I have a table, (a int,
> b int, primary key (a)), that has the element (1,1) inserted.
>
> Given the following statements:
> insert ignore into table values (2,2), (1,2);
> select * from table;
>
> The expected result of the select * is to return (1,1),(2,2). The
> insert will make two calls to handler::write_row. For (2,2),
> handler::write_row will insert the element, and return success. With
> other storage engines, for (1,2), handler::write_row will insert
> nothing and return HA_ERR_FOUND_DUPP_KEY. What my change will do is
> still insert nothing, but instead of returning HA_ERR_FOUND_DUPP_KEY,
> it will return success.
>
> I assume there is nothing wrong with this?
>
> Thanks
> -Zardosht
>
> On Wed, Feb 24, 2010 at 1:05 PM, Venu Kalyan <mydb08@stripped> wrote:
> > Hi !
> >
> > On Tue, Feb 23, 2010 at 3:23 PM, Zardosht Kasheff <zardosht@stripped>
> wrote:
> >> But does this distinguish the commands "insert ignore" and "insert on
> >> duplicate key update"?
> >
> > Actually its contrary and unknown behavior even though MySQL allows
> > the synax to use IGNORE and ON DUPLICATE KEY / REPLACE together in
> > single statement, and it should in general through an error when used
> > together as server does not which one to prefer.
> >
> > In this case (Ignore and ON DUPLICATE KEY together in single insert)
> > ON DUPLICATE KEY takes precedence in the code due to the following
> > condition..
> >
> > if (duplic != DUP_ERROR || ignore)
> > table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY);
> >
> > For your case; you can validate this using :
> >
> > if (thd->lex->ignore && thd->lex->duplicates == SQL_ERROR)
> {
> > // safe to ignore my dups
> > }
> >
> > if (thd->lex->ignore && thd->lex->duplicates != SQL_ERROR)
> {
> > // used ignore together with IGNORE and ON KEY DUPLICATE / REPLACE
> > statements, I will decide what needs to be done here
> > }
> >
> > Hope this helps.
> >
> > Thanks
> > Venu Anuganti
> >
> >>
> >> On Tue, Feb 23, 2010 at 6:20 PM, Venu Kalyan <mydb08@stripped>
> wrote:
> >>> You should look for both sql_command type and lex->ignore value (both
> are
> >>> part of thd->lex->sql_command and thd->lex->ignore) to
> determine if the
> >>> storage engine can safely return success on dup cases. But remember,
> doing
> >>> so will result in wrong stats (number of rows inserted/updated)
> >>>
> >>> Apart from insert/replace, load data also uses the same logic.
> >>>
> >>> On Tue, Feb 23, 2010 at 12:07 PM, Zardosht Kasheff
> <zardosht@stripped>
> >>> wrote:
> >>>>
> >>>> Hello,
> >>>>
> >>>> Does anyone have any guidance here?
> >>>>
> >>>> Thanks
> >>>> -Zardosht
> >>>>
> >>>> On Wed, Feb 17, 2010 at 3:47 PM, Zardosht Kasheff
> <zardosht@stripped>
> >>>> wrote:
> >>>> > So it seems that just looking at this flag will not work,
> because
> >>>> > INSERT IGNORE and INSERT...ON DUPLICATE KEY UPDATE both set
> this flag.
> >>>> >
> >>>> > I guess what I am wondering is the following, is there any way
> for the
> >>>> > handler to know for sure that the user has called "insert
> ignore", and
> >>>> > if so, would it be ok for the handler to return success even
> if
> >>>> > internally a duplicate key is found?
> >>>> >
> >>>> > Thanks
> >>>> > -Zardosht
> >>>> >
> >>>> > On Fri, Feb 12, 2010 at 5:08 PM, Zardosht Kasheff
> <zardosht@stripped>
> >>>> > wrote:
> >>>> >> Hello all,
> >>>> >>
> >>>> >> I am trying to implement an optimization in our storage
> engine if
> >>>> >> HA_EXTRA_IGNORE_DUP_KEY is set. Would it be ok if
> handler::write_row
> >>>> >> returns success if this flag is set, the sql statement is
> NOT
> >>>> >> SQLCOM_REPLACE or SQLCOM_REPLACE_SELECT, and the row is not
> inserted
> >>>> >> if a duplicate is otherwise there.
> >>>> >>
> >>>> >> Basically, the question is if this flag is set, is it ok to
> not return
> >>>> >> HA_ERR_FOUND_DUPP_KEY in the cases where the statement is
> not "replace
> >>>> >> into".
> >>>> >>
> >>>> >> Thanks
> >>>> >> -Zardosht
> >>>> >>
> >>>> >
> >>>>
> >>>> --
> >>>> MySQL Internals Mailing List
> >>>> For list archives: http://lists.mysql.com/internals
> >>>> To unsubscribe:
> http://lists.mysql.com/internals?unsub=1
> >>>>
> >>>
> >>>
> >>
> >
>
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe: http://lists.mysql.com/internals?unsub=1
>
--
Sergey Vojtovich <svoj@stripped>
MySQL AB, Software Engineer
Izhevsk, Russia, www.mysql.com
