9.26. Best Effort Union

Virtuoso offers a SQL extension for an error tolerant UNION operation. The idea is that when querying multiple remote data sources in a single union construct some of the participating data sources may be allowed to fail while still returning a result for the successfully queried data sources.

The construct is introduced by the BEST keyword before UNION or UNION ALL. If a query expression of multiple unions has a single BEST keyword the entire union chain is considered as a best effort union. It is however recommended to have the BEST keyword in FROM of all the UNION keywords.

When a run time error occurs during the evaluation of a term in a best effort union the evaluation of the term is interrupted and the union continues with the next term. The partial result set that may have been generated by the failed term is considered when making the result.

Aliasing constant columns selected in the terms of the union by the names __SQLSTATE, __MESSAGE and __SET_NO retrieve individual error messages. If these are present and a union term encounters an error an extra row is generated for the term with all NULLs and the __SQLSTATE, __MESSAGE and __SET_NO columns set to the SQL state, SQL message and the union term number respectively. If neither of these is specified and a union term fails without producing any result rows the error will not be visible.

[Note] Note

No error encountered during a best effort union will be signalled in the normal fashion.

If a term of a best effort union meets the criteria for a pass through query on a specific remote database and a transaction error occurs when evaluating it, the transaction on the VDB is not aborted as would normally happen as a result of a VDB transaction error.

Thus if a pass through term dies of deadlock on its data source the query continues normally for other data sources referenced in the best effort union. The VDB will however get the transaction error when attempting to commit the transaction where the best effort union took place since the remote transaction branch will still be deadlocked.

Example 9.42. Examples

select 2222, 1 / 0 from sys_users best union all select key_id, 1 / (1000 - key_id)  from sys_keys;

The first term will immediately hit the /0 error and will produce no rows. The second term will produce a few rows for system tables but will hit /0 when getting to key_id 1001.

To see the errors one can write:

select '00000' as __sqlstate, '' as __message, 2222, 1 / 0 from sys_users best union
all select '00000' as __sqlstate, '', key_id, 1 / (1001 - key_id)  from sys_keys;

[Note] Note

The columns are named by the first term, hence the AS declaration in the second term is optional.

The BEST keyword does not affect the ALL or CORRESPONDING BY options of UNION.