ORDER BY CLAUSE USING AN ALIAS
Before the fix for Bug #11923239 ERROR WITH CORRELATED SUBQUERY IN
VIEW WITH ONLY_FULL_GROUP_BY SQL MODE,
this statement:
CREATE VIEW v1 AS SELECT 2*a AS z FROM t1 ORDER BY z;
produced this definition (written to the view's FRM file):
CREATE VIEW v1 AS SELECT 2*a AS z FROM t1 ORDER BY 2*a;
in other words, the alias in ORDER BY was rewritten to its expression.
The same was done for an alias in GROUP BY.
The fix removed this rewriting. It was a good fix because:
– using the alias made only_full_group_by logic work better
– staying close to the original query "feels better".
However, this introduced a regression. Indeed, if we select from
the view above without selecting 'z':
SELECT 1 FROM v1;
After view merging, this query becomes
SELECT 1 FROM t1 ORDER BY z;
and the resolver fails to find what 'z' is (ORDER item 'z', created
during mysql_make_view(), is searched in the top query's SELECT
list and then in the name resolution context of the item i.e. {t1}).
In this patch, we partially undo the changes of Bug #11923239:
if the view is merge-able, we write the expression instead of
the alias, again. This corrects the regression. It does not
reintroduce any problem for only_full_group_by, because the
merge-able view never has GROUP BY.