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