Delegation Rules: WHERE Clause
The WHERE clause is the first clause of an SQL query to be evaluated for delegation to the data source provider. If the WHERE clause cannot be delegated, then the rest of the query cannot be delegated.
Spectrum Spatial delegates to the data source provider as many operators that the data source provider supports, with emphasis on spatial operators. The following rules apply to the WHERE clause in determining what can be delegated to the data source provider:
- Only boolean operators can be delegated to a data source provider. Boolean functions cannot be delegated.
- Boolean operators must be of the following form:
<column> operator <constant>
. If the input query is written as<constant> operator <column>
and the operator has an inverse operator, the query may be rephrased as<column> <inverse operator> <constant>
to satisfy this rule. - If the WHERE clause contains an AND operator and the data provider supports AND, and the data provider supports mixed spatial and non-spatial operators, then all of operators supported by the data provider are delegated. Anything the data source provider does not support is handled by Spectrum Spatial.
- If the WHERE clause contains an AND operator and the data provider supports AND, and the data provider does not support mixed spatial and non-spatial operators, only the spatial operators that the data provider supports are delegated. The rest are evaluated by Spectrum Spatial.
- If the WHERE clause contains an AND operator and the data provider supports AND, and the WHERE clause contains only non-spatial operators, then the operators that the data provider supports are delegated.
- If the WHERE clause contains an AND operator and the data provider does not support AND, then all the operators are scored and the one with the highest score is delegated. Spatial operators score higher than non-spatial operators. See How AND Operations are Scored.
- If the WHERE clause contains OR or NOT, and the data provider supports OR and NOT and all operators, the entire query is delegated to the data source provider. There is no partial delegation as in the case of the AND operator.