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

[15 Aug 2006 6:04] Martin Ross
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.
[26 Aug 2006 10:36] Valeriy Kravchuk
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.
[25 Sep 2006 13:46] Bugs System
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.
[1 Oct 2006 8:59] Georgi Kodinov
Pushed in 5.0.26/5.1.12
[6 Oct 2006 15:44] Paul Dubois
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.