Timour, It's ok to push this patch as a fix. Yet remember that this fix leads us to a performance regression 5.2 >> 5.3 when the range expression contains a constant [single-row] subquery. This is due to the changes when and how we build range trees in 5.3. When we have a range expression with constant expensive subquery we just bail out and do not include this range into the range condition at all. The worst thing about this is that we bail out even for dynamic ranges belonging to te range trees that are build at the execution time when subquery evaluation is allowed. Please open a task to fix all these regressions (it's quite possible). Regards, Igor. On 02/06/2013 03:27 AM, Timour Katchaounov wrote:
Igor,
please review the following simple patch.
Timour
------------------------------------------------------------ revno: 3622 revision-id: timour@askmonty.org-20130206112530-nwo4xto0cwv65ece parent: sergii@pisem.net-20130128081223-mp9rsd3t9soz8lly fixes bug(s): https://mariadb.atlassian.net/browse/MDEV-4140 committer: timour@askmonty.org branch nick: 5.3-md4140 timestamp: Wed 2013-02-06 13:25:30 +0200 message: Fix for MDEV-4140
Analysis: Range analysis detects that the subquery is expensive and doesn't build a range access method. Later, the applicability test for loose scan doesn't take that into account, and builds a loose scan method without a range scan on the min/max column. As a result loose scan fetches the first key in each group, rather than the first key that satisfies the condition on the min/max column.
Solution: Since there is no SEL_ARG tree to be used for the min/max column, it is not possible to use loose scan if the min/max column is compared with an expensive scalar subquery. Make the test for loose scan applicability to be in sync with the range analysis code by testing if the min/max argument is compared with an expensive predicate.