Hello Sergei, I agree, I can probably use can_compare_record in my second loop. The use of a temporary table only to handle the case of blobs, seems to me too heavy. If I add a check to exclude update of blob column (or switch to multi update) , is my patch be acceptable ? I will try to understand multi_update code to see if I can use it (but my skills are a bit limited) Many thanks. Jérôme.
-----Message d'origine----- De : Sergei Golubchik [mailto:serg@mariadb.org] Envoyé : mardi 29 août 2017 14:01 À : jerome brauge Cc : maria-developers@lists.launchpad.net; Alexander Barkov Objet : 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.
Regards, Sergei Chief Architect MariaDB and security@mariadb.org