Criteria

Criteria may be:

  • Predicates that evaluate to true or false

  • Logical criteria that combines criteria (AND, OR, NOT)

  • A value expression with type boolean

Usage:

criteria AND|OR criteria
NOT criteria
(criteria)
expression (=|<>|!=|<|>|<=|>=) (expression|((ANY|ALL|SOME) subquery|(array_expression)))
expression IS [NOT] DISTINCT FROM expression

IS DISTINCT FROM considers null values equivalent and never produces an UNKNOWN value.

Note
 Using IS DISTINCT FROM in a join predicate that is not pushed down will not yet procedure as performant of a plan as a regular comparison as the optimizer is not tuned to handling IS DISTINCT FROM. 
expression [NOT] IS NULL
expression [NOT] IN (expression [,expression]*)|subquery
expression [NOT] LIKE pattern [ESCAPE char]

Matches the string expression against the given string pattern. The pattern may contain % to match any number of characters and _ to match any single character. The escape character can be used to escape the match characters % and _.

expression [NOT] SIMILAR TO pattern [ESCAPE char]

SIMILAR TO is a cross between LIKE and standard regular expression syntax. % and _ are still used, rather than .* and . respectively.

Note
 Teiid does not exhaustively validate SIMILAR TO pattern values. Rather the pattern is converted to an equivalent regular expression. Care should be taken not to rely on general regular expression features when using SIMILAR TO. If additional features are needed, then LIKE_REGEX should be used. Usage of a non-literal pattern is discouraged as pushdown support is limited. 
expression [NOT] LIKE_REGEX pattern

LIKE_REGEX allows for standard regular expression syntax to be used for matching. This differs from SIMILAR TO and LIKE in that the escape character is no longer used (\ is already the standard escape mechanism in regular expressions and % and _ have no special meaning. The runtime engine uses the JRE implementation of regular expressions - see the java.util.regex.Pattern class for details.

Note
 Teiid does not exhaustively validate LIKE_REGEX pattern values. It is possible to use JRE only regular expression features that are not specified by the SQL specification. Additional not all sources support the same regular expression flavor or extensions. Care should be taken in pushdown situations to ensure that the pattern used will have same meaning in Teiid and across all applicable sources. 
EXISTS (subquery)
expression [NOT] BETWEEN minExpression AND maxExpression

Teiid converts BETWEEN into the equivalent form expression >= minExpression AND expression ⇐ maxExpression

expression

Where expression has type boolean.

Syntax Rules:

  • The precedence ordering from lowest to highest is comparison, NOT, AND, OR

  • Criteria nested by parenthesis will be logically evaluated prior to evaluating the parent criteria.

Some examples of valid criteria are:

  • (balance > 2500.0)

  • 100*(50 - x)/(25 - y) > z

  • concat(areaCode,concat(’-`,phone)) LIKE ’314%1'

Comparing null Values

Tip
 Null values represent an unknown value. Comparison with a null value will evaluate to `unknown', which can never be true even if `not' is used.

Criteria Precedence

Teiid parses and evaluates conditions with higher precedence before those with lower precedence. Conditions with equal precedence are left associative. Condition precedence listed from high to low:

Condition Description

sql operators

See Expressions

EXISTS, LIKE, SIMILAR TO, LIKE_REGEX, BETWEEN, IN, IS NULL, IS DISTINCT, <, ⇐, >, >=, =, <>

comparison

NOT

negation

AND

conjunction

OR

disjunction

Note however that to prevent lookaheads the parser does not accept all possible criteria sequences.  For example "a = b is null" is not accepted, since by the left associative parsing we first recognize "a =", then look for a common value expression.  "b is null" is not a valid common value expression.  Thus nesting must be used, for example "(a = b) is null".  See BNF for SQL Grammar for all parsing rules.

results matching ""

    No results matching ""