[Maria-developers] FirstMatch strategy and interleaving
Hi! I was looking through subquery code and found the following issue with FirstMatch strategy: Our original intent with FirstMatch strategy was to support join orders where sj-inner tables are interleaved with outer tables that are not correlated with the subquery. FirstMatch spec is here, http://forge.mysql.com/worklog/task.php?id=3750, the question of interleaving is covered in section 2.2. [I assumed] I have coded this for non-buffered join execution, both optimizer and executioner support. The first problem I saw was that it didn't seem to be possible to come up with dataset/query that would cause the join optimizer to pick such join order. I don't know whether this is because the cost formulas make the choice impossible or I'm just not finding the right examples. Either way, mysql-test-run suite has no coverage for FirstMatch+interleaving. Now, when I look at the source code and/or force the choice of FirstMatch+ interleaving join order by changing costs from gdb, I find out that: - setup_semijoin_dups_elimination() has a bug that will make the query produce incorrect result - Join bufferring now supports FirstMatch with multiple inner tables but doesn't support FirstMatch+interleaving. Since I'm not comfortable with making fixes for something that I can't have testcases for, I'm considering disabling FirstMatch+interleaving. We can get back to it when we have a better understanding of what goes on in the cost model. Any objections? BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog
Hi!
"Sergey" == Sergey Petrunya <psergey@askmonty.org> writes:
Sergey> Hi! Sergey> I was looking through subquery code and found the following issue Sergey> with FirstMatch strategy: Sergey> Our original intent with FirstMatch strategy was to support join orders where Sergey> sj-inner tables are interleaved with outer tables that are not correlated Sergey> with the subquery. FirstMatch spec is here, Sergey> http://forge.mysql.com/worklog/task.php?id=3750, the question of interleaving Sergey> is covered in section 2.2. Sergey> [I assumed] I have coded this for non-buffered join execution, both optimizer Sergey> and executioner support. The first problem I saw was that it didn't seem to Sergey> be possible to come up with dataset/query that would cause the join optimizer Sergey> to pick such join order. I don't know whether this is because the cost formulas Sergey> make the choice impossible or I'm just not finding the right examples. Either Sergey> way, mysql-test-run suite has no coverage for FirstMatch+interleaving. Sergey> Now, when I look at the source code and/or force the choice of FirstMatch+ Sergey> interleaving join order by changing costs from gdb, I find out that: Sergey> - setup_semijoin_dups_elimination() has a bug that will make the query produce Sergey> incorrect result Sergey> - Join bufferring now supports FirstMatch with multiple inner tables but Sergey> doesn't support FirstMatch+interleaving. Sergey> Since I'm not comfortable with making fixes for something that I can't have Sergey> testcases for, I'm considering disabling FirstMatch+interleaving. We can get Sergey> back to it when we have a better understanding of what goes on in the cost Sergey> model. Sergey> Any objections? I assume it's better to disable something that can't not work than to keep the old code there (assuming the disabling is safe). But please put on your TODO to try to create a test case to execute the suspected code. Regards, Monty
participants (2)
-
Michael Widenius -
Sergey Petrunya