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.