MySQL Bugs: #23277: ALGORITHM=MERGE not allowed when subquery is in select list

Bug #23277	ALGORITHM=MERGE not allowed when subquery is in select list
Submitted:	13 Oct 2006 20:15	Modified:	30 Oct 2006 14:14
Reporter:	Ryan Sorensen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.0.26-max-log	OS:	Linux (Linux)
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	merge view subquery select

Description:
A view can not be created using MERGE when there's a subquery in the select list.

The documentation for updatablity of views mentions subqueries in the select list, but the minimum requirements for MERGE don't.

There still exists a one to one relationship with the underlying table.

How to repeat:
> create table test_a ( `a` int );
Query OK, 0 rows affected (0.00 sec)
> create algorithm=merge view test_a_view as select a, (select 1) from test_a;
Query OK, 0 rows affected, 1 warning (0.01 sec)

> 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)

The documentation lists the following limitation preventing a MERGE:

  * Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)

probably a subquery in the field list falls into the same category?

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.

Sanja confirms that subquery in the select list is one of the limitations.
I've updated the manual accordingly.