View flattening

When evaluating a statement that references a view, Derby internally transforms a view into a derived table. This derived table might also be a candidate for flattening into the outer query block.

A view or derived table can be flattened into the outer query block if all of the following conditions are met:

The select list is composed entirely of simple column references and constants.
There is no GROUP BY clause in the view.
There is no DISTINCT in the view.

For example, given view v1(a,b):

SELECT Cities.city_name, Countries.country_iso_code
FROM Cities, Countries
WHERE Cities.country_iso_code = Countries.country_iso_code

and a SELECT that references it:

SELECT a, b
FROM v1 WHERE a = 'Melbourne'

after the view is transformed into a derived table, the internal query is

SELECT a, b
FROM (select Cities.city_name, Countries.country_iso_code
FROM Cities, Countries
WHERE Cities.country_iso_code = Countries.country_iso_code) v1(a, b)
WHERE a = 'Melbourne'

After view flattening it becomes

SELECT Cities.city_name, Countries.country_iso_code
FROM Cities, Countries
WHERE Cities.country_iso_code = Countries.country_iso_code
AND Cities.city_name = 'Melbourne'