ASA SQL User's Guide
  Common Table Expressions

Datatype declarations in recursive common table expressions

The datatypes of the columns in the temporary view are defined by those of the initial subquery. The datatypes of the columns from the recursive subquery must match. The database server automatically attempts to convert the values returned by the recursive subquery to match those of the initial query. If this is not possible, or if information may be lost in the conversion, an error is generated.

In general, explicit casts are often required when the initial subquery returns a literal value or NULL. Explicit casts may also be required when the initial subquery selects values from different columns than the recursive subquery.

Casts may be required if the columns of the initial subquery do not have the same domains as those of the recursive subquery. Casts must always be applied to NULL values in the initial subquery.

For example, the bookshelf parts explosion sample works correctly because the initial subquery returns rows from the bookcase table, and thus inherits the datatypes of the selected columns.

For more information, see Parts explosion problem.

If this query is rewritten as follows, explicit casts are required.

WITH RECURSIVE parts (component, subcomponent, quantity) AS
( SELECT NULL, 'bookcase', 1         -- ERROR! Wrong domains!
    UNION ALL
  SELECT b.component, b.subcomponent,
         p.quantity * b.quantity
  FROM parts p JOIN bookcase b
    ON p.subcomponent = b.component )
SELECT * FROM parts
ORDER BY component, subcomponent

Without casts, errors result for the following reasons:

• The correct datatype for component names is VARCHAR, but the first column is NULL.

• The digit 1 is assumed to be a short integer, but the datatype of the quantity column is INT.

No cast is required for the second column because this column of the initial query is already a string.

Casting the datatypes in the initial subquery allows the query to behave as intended:

WITH RECURSIVE parts (component, subcomponent, quantity) AS
( SELECT CAST(NULL AS VARCHAR),  -- CASTs must be used
         'bookcase',             -- to declare the
         CAST( 1   AS INT)       -- correct datatypes
      UNION ALL
  SELECT b.component, b.subcomponent,
         p.quantity * b.quantity
  FROM parts p JOIN bookcase b
    ON p.subcomponent = b.component )
SELECT * FROM parts
ORDER BY component, subcomponent