I submitted a patch to implement CREATE [[NO] FORCE] VIEW this weekend, but there's an issue to resolve regarding behavior of "CREATE FORCE VIEW view1 SELECT *".

According to sql_view.cc:

"View definition query – a SELECT statement that fully defines view. It
is generated from the Item-tree built from the original (specified by
the user) query. The idea is that generated query should eliminates all
ambiguities and fix view structure at CREATE-time (once for all)."

The view definition query for CREATE VIEW view1 AS SELECT * FROM TABLE1 cannot be disambiguated if the base tables don't exist yet, and doing that at view creation time may be wrong at execution time anyway.

Currently, "CREATE FORCE VIEW view1 AS SELECT * FROM TABLE1" is generated as "CREATE FORCE VIEW view1 AS SELECT AS * FROM TABLE1", with 2 "AS" tokens, causing an error on SELECT:

> select * from view1;
ERROR 1356 (HY000): View 'view_test.view1' references invalid table(s)
or column(s) or function(s) or definer/invoker of view lack rights to use them

These are the choices that can be made

1) leave the original query alone as SELECT * FROM
2) see if the base tables exist first and try to find the column names as normal for disambiguation, and silently allow the view creation if the columns cannot be found. (sql_view.cc would have to be changed to ignore errors in several places for the FORCE option to still work in the presence of missing columns.)
3) generate an error that says "error: column names must be specified with the FORCE option".

So far, I think #3 is the best choice, as the user performs the disambiguation and it's the least intrusive change to sql_view.cc.

If anybody has access to Oracle Enterprise 10g or higher, maybe they can test how it's implemented there.

Thanks, James.