Bug #5502 Views: ALGORITHM clause should be changed and moved
Submitted: 10 Sep 2004 2:09 Modified: 19 Aug 2005 10:49
Reporter: Peter Gulutzan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Views Severity:S4 (Feature request)
Version:5.0.2-alpha OS:Linux (SuSE 8.2)
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[10 Sep 2004 2:09] Peter Gulutzan
Description:
The ALGORITHM clause needs improvement. 
 
Re "Warning | 1353 | View merge algorithm can't be used 
here for now (assumed undefined algorithm)": 
This is hard to understand. 
 
It would look nicer if one allowed UNDEFINED, i.e. 
MERGE = {MERGE | TEMPTABLE | UNDEFINED} 
since UNDEFINED is the default. Then one could say: 
"Warning | 1353 | ALGORITHM=MERGE can't be used, 
assuming ALGORITHM=UNDEFINED" 
This is easier to understand. 
 
Also, if ALTER VIEW survives, 
then the ALGORITHM= clause can be changed by it. So 
why not put it at the end, after WITH CHECK OPTION? 
 
Or: 
Do not put the ALGORITHM= clause in CREATE VIEW. 
Put it in SELECT if you are selecting from a view. 
 

How to repeat:
mysql> create algorithm=merge view v as select 'a'; 
Query OK, 0 rows affected, 1 warning (0.00 sec) 
 
mysql> show warnings; 
+---------+------+-------------------------------------------------------------------------------+ 
| Level   | Code | Message                                                                       | 
+---------+------+-------------------------------------------------------------------------------+ 
| Warning | 1354 | View merge algorithm can't be used here for now (assumed undefined 
algorithm) | 
+---------+------+-------------------------------------------------------------------------------+ 
1 row in set (0.00 sec)
[19 Aug 2005 10:49] Victoria Reznichenko
Verified with 5.0.12
[17 Oct 2005 16:28] Tobias Asplund
The placement of the ALGORITHM clause is very inconvenient.

I can never place it right, since options like this are usually either after the resource name (ie, CREATE VIEW view_name ALGORITHM=... AS...) or at the end of the statement (ie, CREATE VIEW view_name AS ..... ALGORITHM=...).

It's just placed in a very illogical place, and people keep running into problems finding where to put it, since it's not even in a place where most people try even if just blindly trying to put it somewhere.
[3 Aug 2007 12:40] Arjen Lentz
This bug was partially fixed, the syntax now accepts =UNDEFINED.

The syntax was not changed to have the ALGORITHM clause in a different location, but that's probably not a viable suggestion since it's been out there for so long now.

Perhaps best to note this bug as fixed and close it?