MySQL Bugs: #21646: ALGORITHM=MERGE CREATE VIEW FAILS FOR INNER JOINS WITH SUBQUERIES USING AGG FUNC

Bug #21646	ALGORITHM=MERGE CREATE VIEW FAILS FOR INNER JOINS WITH SUBQUERIES USING AGG FUNC
Submitted:	15 Aug 2006 6:04	Modified:	6 Oct 2006 15:44
Reporter:	Martin Ross	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.0.25-BK, 5.0.24-standard	OS:	Linux (Linux, REDHAT 9)
Assigned to:	Igor Babaev	CPU Architecture:	Any
Tags:	ALGORITHM, MAX, merge, VIEW

Description:
View is not created using merge algorithm (and it should be) although a logically equivalent statement lets it be successfully created.  The problem appears to be in MERGE algorithms detection of aggregate functions..  Aggregates should be allowed in subqueries (and they appear to be allowed in the WHERE clause, just not in the ON clause)

See the example case since this illustrates the problem better than I can explain it.

How to repeat:
create table a  (
    pk int,
    primary key (pk)
);

drop table b
create table b (
    pk int,
    fk int,
    ver int,
    org int,
    primary key (pk)
);

CREATE OR
REPLACE ALGORITHM=MERGE VIEW c
as
select a.* from a
join b 
on b.fk = a.pk
and b.ver = (select max(x.ver) from b x where x.org = b.org);

Warnings: ---> 
   W (1): View merge algorithm can't be used here for now (assumed undefined algorithm)
          <--- 

 0 record(s) affected 

BUT
replace the ON clause with a WHERE clause

CREATE OR
REPLACE ALGORITHM=MERGE VIEW c
as
select a.* from a
join b 
on b.fk = a.pk
where b.ver = (select max(x.ver) from b x where x.org = b.org);

and the view is successfully created as ALGORITHM=MERGE!

Suggested fix:
Fix MERGE view logic to let on subquery aggregates to work.

Thank you for a bug report. Verified just as described with 5.0.25-BK on Linux:

mysql> CREATE OR
    -> REPLACE ALGORITHM=MERGE VIEW c
    -> as
    -> select a.* from a
    -> join b
    -> on b.fk = a.pk
    -> and b.ver = (select max(x.ver) from b x where x.org = b.org);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1354
Message: View merge algorithm can't be used here for now (assumed undefined algo
rithm)
1 row in set (0.01 sec)

mysql> drop view c;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE OR
    -> REPLACE ALGORITHM=MERGE VIEW c
    -> as
    -> select a.* from a
    -> join b
    -> on b.fk = a.pk
    -> where b.ver = (select max(x.ver) from b x where x.org = b.org);
Query OK, 0 rows affected (0.01 sec)

This is inconsistent and, thus, a bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12468

ChangeSet@1.2278, 2006-09-25 06:15:14-07:00, igor@rurik.mysql.com +5 -0
  Fixed bug #21646.
  Presence of a subquery in the ON expression of a join 
  should not block merging the view that contains this join.
  Before this patch the such views were converted into 
  into temporary table views.

Pushed in 5.0.26/5.1.12

Noted in 5.0.26, 5.1.12 changelogs.

The presence of a subquery in the ON clause of a join in a view
definition prevented the MERGE algorithm from being used for the view
in cases where it should be allowed.