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.
• There is no ORDER BY, result offset, or fetch first clause 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'