MySQL Bugs: #35010: View mergeability criteria are incorrect in the documentation

Bug #35010	View mergeability criteria are incorrect in the documentation
Submitted:	3 Mar 2008 18:49	Modified:	26 Nov 2008 17:07
Reporter:	Sergey Petrunya	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
http://dev.mysql.com/doc/refman/5.0/en/create-view.html says:

 The MERGE algorithm requires a one-to-one relationship between the rows in the view and the rows in the underlying table. If this relationship does not hold, a temporary table must be used instead. Lack of a one-to-one relationship occurs if the view contains any of a number of constructs:

 * Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
 * DISTINCT
 * GROUP BY
 * HAVING
 * UNION or UNION ALL
 * Subquery in the select list
 * Refers only to literal values (in this case, there is no underlying table)

First, the criteria for alrorithm=MERGE is not that there is one-to-one relationship. Actually, one can have  alrorithm=MERGE VIEWs that are joins, where the concept of "the underlying table" is not defined.  My definition for algorithm=merge is that the VIEW's SELECT must be a "simple join" or "represent a join operation with possible selection or projection". Maybe PeterG will be able to provide a better term.

Second problem: 
The list misses "LIMIT n" clause. If a VIEW definition has LIMIT n, then MERGE algorithm cannot be used.

Third problem:
The criteria of the MERGE algorithm are not denoted. I'd really appreciate if there was an anchor so I could give people a pointer like:

http://dev.mysql.com/doc/refman/5.0/en/create-view.html#mergeability-criteria

The same goes to updateability or mergeability (perhaps adding anchors could be branched off to a separate bug entry).

How to repeat:
View #1
-------
mysql> create algorithm=merge view v1 as select A.a + B.a from t100 A, t101 B;
Query OK, 0 rows affected (0.01 sec)

mysql> show create view v1\G
*************************** 1. row ***************************
                View: v1
         Create View: CREATE ALGORITHM=MERGE DEFINER=``@`` SQL SECURITY DEFINER VIEW `v1` AS select (`A`.`a` + `B`.`a`) AS `
A.a + B.a` from (`t100` `A` join `t101` `B`)
1 row in set (0.01 sec)

^ This doesn't have any 1-to-1 mapping but still uses algorithm=merge.

View #2
-------
mysql> create algorithm=merge view v45 as select A.a from t100 A limit 4;
Query OK, 0 rows affected, 1 warning (0.01 sec)

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

^ LIMIT is not allowed for algorithm=merge.

Suggested fix:
See above

Thank you for a reasonable documentation request.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

This bug report identified three problems, which have been addressed as follows.

1) Removed the "one-to-one" language. New wording:

If the MERGE algorithm cannot be used, a temporary table must be used
instead. MERGE cannot be used if the view contains any of the
following constructs:

2) Added LIMIT to the list of constructs that cause MERGE to be inapplicable.

3) The view documentation has been revised since this bug was filed. The algorithms discussion is in its own section and can be referenced using this link:

http://dev.mysql.com/doc/refman/5.0/en/view-algorithms.html