← Back to team overview

maria-developers team mailing list archive

Re: Patch for MDEV-12874 - UPDATE statements with the same source and target


Hi, Jérôme!

Sorry, it took a while, there were releases in the plan :(

On Aug 18, jerome brauge wrote:
> Hi, Sergei
> Here is a version that reuses used logic when one searches and updates
> the same index.
> The principal impact is that we can't use the can_compare optimization
> because we have to store all new values for all keys of tempfile.

I don't quite understand that. You have to store new values in the
tempfile, yes, but why does it mean that can_compare cannot be used?

But here's a bug in your implementation, you cannot simply write
table->record[0] to a temp file. This will not write blobs, because
they're stored out of record.

The correct solution is not to use an IO_CACHE for records, but to
create a temporary table. It's create_tmp_table() function, and,
for example, multi_update::initialize_tables() uses it.

Hmm, I wonder, whether we could just reuse multi_update.
Could be rather simple solution, if it'll work.

> But this correct a bug in my previous implementation (on update
> through a view).  This simplifies the code a bit, but not much (no
> needs of tempfile_key).  I don't see how to do this more simpler.
> If you have better ideas, I listen your suggestions.

The way I see it now, there are essentially two distinct features.
Using the same table in the WHERE clase. And in the SET clause.

When the table is only present in the WHERE clause, we could use pretty
much the same code as for used_key_is_modified (an additional loop over
records before the main update loop, and a tempfile).

When the table is only present in the SET clause, we don't need
used_key_is_modified, but we need your tempfile_newdata and an
additional loop after the main update loop.

In the worst case you have three loops now (although this could,
probably, be reduced to two).

But I would think that this use case isn't particularly common and it is
not worth the troubles distinguishing these use cases yet. So, there's
no need to redo anything in this regard.

Chief Architect MariaDB
and security@xxxxxxxxxxx

Follow ups