[Maria-developers] PLEASE REVIEWA: pre-requisite patch for the data type plugins, also fixing a number of optimizer bugs
Hello, While working on pluggable data types, I noticed a few problems related to optimizer:
MDEV-6950 Bad results with joins comparing DATE/DATETIME and INT/DECIMAL/DOUBLE/ENUM/VARCHAR columns MDEV-6971 Bad results with joins comparing TIME and DOUBLE/DECIMAL columns MDEV-6978 Bad results with joins comparing case insensitive VARCHAR/ENUM/SET expression to a _bin ENUM column MDEV-6982 LEFT JOIN table elimination is not always used when it could MDEV-6989 BINARY and COLLATE xxx_bin comparisions are not used for optimization in some cases MDEV-6990 GROUP_MIN_MAX optimization is not applied in some cases when it could MDEV-6991 GROUP_MIN_MAX optimization is erroneously applied in some cases
In some cases wrong result sets are returned. So it would be nice to have this fixed in 10.0. I made these changes as a standalone patch. Please review. I also noticed some other optimizer bugs but not sure how to fix them properly:
MDEV-6986 Bad results with join comparing INT and VARCHAR columns MDEV-6969 Bad results with joins comparing DOUBLE to BIGINT/DECIMAL columns MDEV-6993 Bad results with join comparing DECIMAL and ENUM/SET columns Igor or SergeyP can possibly fix them on top of my changes.
Also, I found some other problems (not related to optimizer):
MDEV-6973 XOR aggregates argument collations MDEV-7005 NULLIF does not work as documented Not sure which version to fix them in. Please suggest.
Thanks.
Hi, Alexander! On Nov 03, Alexander Barkov wrote:
Hello,
While working on pluggable data types, I noticed a few problems related to optimizer:
MDEV-6950 Bad results with joins comparing DATE/DATETIME and INT/DECIMAL/DOUBLE/ENUM/VARCHAR columns MDEV-6971 Bad results with joins comparing TIME and DOUBLE/DECIMAL columns MDEV-6978 Bad results with joins comparing case insensitive VARCHAR/ENUM/SET expression to a _bin ENUM column MDEV-6982 LEFT JOIN table elimination is not always used when it could MDEV-6989 BINARY and COLLATE xxx_bin comparisions are not used for optimization in some cases MDEV-6990 GROUP_MIN_MAX optimization is not applied in some cases when it could MDEV-6991 GROUP_MIN_MAX optimization is erroneously applied in some cases
In some cases wrong result sets are returned. So it would be nice to have this fixed in 10.0.
I made these changes as a standalone patch. Please review.
It's a big patch that introduces quite a log of new code and new concepts. What is a "Tool"? I would rather have it in 10.1, not in 10.0. It was difficult to see what was wrong with the old code, that is, what were actual bug fixes? Could you explain, please?
I also noticed some other optimizer bugs but not sure how to fix them properly:
MDEV-6986 Bad results with join comparing INT and VARCHAR columns
I don't see how you can fix it. The correct fix would be to disable the index in the second query and compare as doubles. But I could only imagine how many applications it will break.
MDEV-6969 Bad results with joins comparing DOUBLE to BIGINT/DECIMAL columns
This one, I think, we can safely fix.
MDEV-6993 Bad results with join comparing DECIMAL and ENUM/SET columns
And this one.
Also, I found some other problems (not related to optimizer):
MDEV-6973 XOR aggregates argument collations MDEV-7005 NULLIF does not work as documented Not sure which version to fix them in. Please suggest.
10.0 looks ok. Regards, Sergei
Hello Sergei, On 11/12/2014 01:28 PM, Sergei Golubchik wrote:
Hi, Alexander!
On Nov 03, Alexander Barkov wrote:
Hello,
While working on pluggable data types, I noticed a few problems related to optimizer:
MDEV-6950 Bad results with joins comparing DATE/DATETIME and INT/DECIMAL/DOUBLE/ENUM/VARCHAR columns MDEV-6971 Bad results with joins comparing TIME and DOUBLE/DECIMAL columns MDEV-6978 Bad results with joins comparing case insensitive VARCHAR/ENUM/SET expression to a _bin ENUM column MDEV-6982 LEFT JOIN table elimination is not always used when it could MDEV-6989 BINARY and COLLATE xxx_bin comparisions are not used for optimization in some cases MDEV-6990 GROUP_MIN_MAX optimization is not applied in some cases when it could MDEV-6991 GROUP_MIN_MAX optimization is erroneously applied in some cases
In some cases wrong result sets are returned. So it would be nice to have this fixed in 10.0.
I made these changes as a standalone patch. Please review.
It's a big patch that introduces quite a log of new code and new concepts. What is a "Tool"?
It's an internal protected subclass in Field_optimizer. Allows to implement all optimization operations (e.g. ref access, range access, hash join, etc) in a single virtual method can_optimize() using a switch on operation type (Tooltype). Another option would be to have separate 9 *virtual* methods, one per operation. But as they looks quite similar inside a particular Field_xxx (Field, Field_temporal, Field_longstr, Field_geom, Field_enum), I thought having a switch is easier.
I would rather have it in 10.1, not in 10.0.
Okey.
It was difficult to see what was wrong with the old code, that is, what were actual bug fixes? Could you explain, please?
Generally, the problem was that the tests that checked if a particular optimization operation can be applied (in opt_range.cc, opt_table_elimination.cc, sql_select.cc) were not precise. For example, the tests for STRING_RESULT did not take into account that the field can actually be enum or temporal and the operation behaviour should actually be different from what is correct for a regular character string data type. So I ended up in 5 different implementations of can_optimize(). Field - for numeric and bit types Field_temporal Field_longstr Field_geom Field_enum
I also noticed some other optimizer bugs but not sure how to fix them properly:
MDEV-6986 Bad results with join comparing INT and VARCHAR columns
I don't see how you can fix it. The correct fix would be to disable the index in the second query and compare as doubles. But I could only imagine how many applications it will break.
What do you suggest? Won't fix? An idea (but I'm not sure): comparing INT and VARCHAR as DECIMAL should give a more precise result, and the index should still be usable. INT linearly and distinctly maps to DECIMAL (unlike DOUBLE).
MDEV-6969 Bad results with joins comparing DOUBLE to BIGINT/DECIMAL columns
This one, I think, we can safely fix.
MDEV-6993 Bad results with join comparing DECIMAL and ENUM/SET columns
And this one.
Also, I found some other problems (not related to optimizer):
MDEV-6973 XOR aggregates argument collations MDEV-7005 NULLIF does not work as documented Not sure which version to fix them in. Please suggest.
10.0 looks ok.
Okey.
Regards, Sergei
participants (2)
-
Alexander Barkov -
Sergei Golubchik