Red Hat Training

A Red Hat training course is available for Red Hat JBoss Data Virtualization

3.3. Expressions

3.3.1. Expressions

Identifiers, literals, and functions can be combined into expressions. Expressions can be used almost anywhere in a query -- SELECT, FROM (if specifying join criteria), WHERE, GROUP BY, HAVING, or ORDER BY.
JBoss Data Virtualization supports the following types of expressions:
Column identifiers
Refer to Section 3.3.2, “Column Identifiers”.
Literals
Refer to Section 3.3.3, “Literals”.
Aggregate functions
Refer to Section 3.3.4, “Aggregate Functions”.
Window functions
Refer to Section 3.3.5, “Window Functions”.
Case and searched case
Refer to Section 3.3.8, “Case and Searched Case”.
Scalar subqueries
Refer to Section 3.3.9, “Scalar Subqueries”.
Parameter references
Refer to Section 3.3.10, “Parameter References”.
Criteria
Refer to Section 3.3.11, “Criteria”.

3.3.2. Column Identifiers

Column identifiers are used to specify the output columns in SELECT statements, the columns and their values for INSERT and UPDATE statements, and criteria used in WHERE and FROM clauses. They are also used in GROUP BY, HAVING, and ORDER BY clauses. The syntax for column identifiers is defined in Section 3.2.1, “Identifiers”.

3.3.3. Literals

Literal values represent fixed values. These can be any of the standard data types. See Section 4.1, “Supported Types”.
Syntax Rules:
  • Integer values will be assigned an integral data type big enough to hold the value (integer, long, or biginteger).
  • Floating point values will always be parsed as a double.
  • The keyword 'null' is used to represent an absent or unknown value and is inherently untyped. In many cases, a null literal value will be assigned an implied type based on context. For example, in the function '5 + null', the null value will be assigned the type 'integer' to match the type of the value '5'. A null literal used in the SELECT clause of a query with no implied context will be assigned to type 'string'.
Some examples of simple literal values are:
  • 'abc'
  • 'isn''t true' - use an extra single tick to escape a tick in a string with single ticks
  • 5
  • -37.75e01 - scientific notation
  • 100.0 - parsed as BigDecimal
  • true
  • false
  • '\u0027' - unicode character

3.3.4. Aggregate Functions

Aggregate functions take sets of values from a group produced by an explicit or implicit GROUP BY and return a single scalar value computed from the group.
JBoss Data Virtualization supports the following aggregate functions:
  • COUNT(*) - count the number of values (including nulls and duplicates) in a group
  • COUNT(x) - count the number of values (excluding nulls) in a group
  • SUM(x) - sum of the values (excluding nulls) in a group
  • AVG(x) - average of the values (excluding nulls) in a group
  • MIN(x) - minimum value in a group (excluding null)
  • MAX(x) - maximum value in a group (excluding null)
  • ANY(x)/SOME(x) - returns TRUE if any value in the group is TRUE (excluding null)
  • EVERY(x) - returns TRUE if every value in the group is TRUE (excluding null)
  • VAR_POP(x) - biased variance (excluding null) logically equals (sum(x^2) - sum(x)^2/count(x))/count(x); returns a double; null if count = 0
  • VAR_SAMP(x) - sample variance (excluding null) logically equals (sum(x^2) - sum(x)^2/count(x))/(count(x) - 1); returns a double; null if count < 2
  • STDDEV_POP(x) - standard deviation (excluding null) logically equals SQRT(VAR_POP(x))
  • STDDEV_SAMP(x) - sample standard deviation (excluding null) logically equals SQRT(VAR_SAMP(x))
  • TEXTAGG(FOR (expression [as name], ... [DELIMITER char] [QUOTE char] [HEADER] [ENCODING id] [ORDER BY ...]) - CSV text aggregation of all expressions in each row of a group. When DELIMITER is not specified, by default comma (,) is used as delimiter. Double quotes(") is the default quote character. Use QUOTE to specify a different value. All non-null values will be quoted. If HEADER is specified, the result contains the header row as the first line. The header line will be present even if there are no rows in a group. This aggregation returns a BLOB. See Section 3.6.15, “ORDER BY Clause”. Example: 
    TEXTAGG(col1, col2 as name DELIMITER '|' HEADER ORDER BY col1)
  • XMLAGG(xml_expr [ORDER BY ...]) - XML concatenation of all XML expressions in a group (excluding null). The ORDER BY clause cannot reference alias names or use positional ordering. See Section 3.6.15, “ORDER BY Clause”.
  • JSONARRAY_AGG(x [ORDER BY ...]) - creates a JSON array result as a CLOB including null value. The ORDER BY clause cannot reference alias names or use positional ordering. Also see Section 3.4.15, “JSON Functions”. Integer value example: 
    jsonArray_Agg(col1 order by col1 nulls first)
    could return 
    [null,null,1,2,3]
  • STRING_AGG(x, delim) - creates a lob results from the concatenation of x using the delimiter delim. If either argument is null, no value is concatenated. Both arguments are expected to be character (string/clob) or binary (varbinary, blob) and the result will be clob or blob respectively. DISTINCT and ORDER BY are allowed in STRING_AGG. Example: 
    string_agg(col1, ',' ORDER BY col1 ASC)
    could return 
    'a,b,c'
  • agg([DISTINCT|ALL] arg ... [ORDER BY ...]) - a user defined aggregate function
Syntax Rules:
  • Some aggregate functions may contain the keyword 'DISTINCT' before the expression, indicating that duplicate expression values should be ignored. DISTINCT is not allowed in COUNT(*) and is not meaningful in MIN or MAX (result would be unchanged), so it can be used in COUNT, SUM, and AVG.
  • Aggregate functions cannot be used in FROM, GROUP BY, or WHERE clauses without an intervening query expression.
  • Aggregate functions cannot be nested within another aggregate function without an intervening query expression.
  • Aggregate functions may be nested inside other functions.
  • Any aggregate function may take an optional FILTER clause of the following form: 
    FILTER ( WHERE condition )
    The condition may be any boolean value expression that does not contain a subquery or a correlated variable. The filter will logically be evaluated for each row prior to the grouping operation. If false, the aggregate function will not accumulate a value for the given row.
  • User defined aggregate functions need ALL specified if no other aggregate specific constructs are used to distinguish the function as an aggregate rather than normal function.
For more information on aggregates, refer to Section 3.6.13, “GROUP BY Clause” and Section 3.6.14, “HAVING Clause”.

3.3.5. Window Functions

JBoss Data Virtualization supports ANSI SQL 2003 window functions. A window function allows an aggregate function to be applied to a subset of the result set, without the need for a GROUP BY clause. A window function is similar to an aggregate function, but requires the use of an OVER clause or window specification.
Usage: 
aggregate|ranking OVER ([PARTITION BY expression [, expression]*] [ORDER BY ...])
In the above example, aggregate can be any of those in Section 3.3.4, “Aggregate Functions”. Ranking can be one of ROW_NUMBER(), RANK(), DENSE_RANK().
Syntax Rules:
  • Window functions can only appear in the SELECT and ORDER BY clauses of a query expression.
  • Window functions cannot be nested in one another.
  • Partitioning and ORDER BY expressions cannot contain subqueries or outer references.
  • The ranking (ROW_NUMBER, RANK, DENSE_RANK) functions require the use of the window specification ORDER BY clause.
  • An XMLAGG ORDER BY clause cannot be used when windowed.
  • The window specification ORDER BY clause cannot reference alias names or use positional ordering.
  • Windowed aggregates may not use DISTINCT if the window specification is ordered.

3.3.6. Window Functions: Analytical Function Definitions

  • ROW_NUMBER() - functionally the same as COUNT(*) with the same window specification. Assigns a number to each row in a partition starting at 1.
  • RANK() - Assigns a number to each unique ordering value within each partition starting at 1, such that the next rank is equal to the count of prior rows.
  • DENSE_RANK() - Assigns a number to each unique ordering value within each partition starting at 1, such that the next rank is sequential.

3.3.7. Window Functions: Processing

Window functions are logically processed just before creating the output from the SELECT clause. Window functions can use nested aggregates if a GROUP BY clause is present. There is no guaranteed effect on the output ordering from the presence of window functions. The SELECT statement must have an ORDER BY clause to have a predictable ordering.
JBoss Data Virtualization will process all window functions with the same window specification together. In general, a full pass over the row values coming into the SELECT clause will be required for each unique window specification. For each window specification the values will be grouped according to the PARTITION BY clause. If no PARTITION BY clause is specified, then the entire input is treated as a single partition. The output value is determined based upon the current row value, its peers (that is rows that are the same with respect to their ordering), and all prior row values based upon ordering in the partition. The ROW_NUMBER function will assign a unique value to every row regardless of the number of peers.
Example windowed results: 
SELECT name, salary, max(salary) over (partition by name) as max_sal,
          rank() over (order by salary) as rank, dense_rank() over (order by salary) as dense_rank,
          row_number() over (order by salary) as row_num FROM employees
name
salary
max_sal
rank
dense_rank
row_num
John
100000
100000
2
2
2
Henry
50000
100000
5
4
5
John
60000
60000
3
3
3
Suzie
60000
150000
3
3
4
Suzie
150000
150000
1
1
1

3.3.8. Case and Searched Case

JBoss Data Virtualization supports two forms of the CASE expression which allows conditional logic in a scalar expression.
Supported forms:
  • CASE <expr> ( WHEN <expr> THEN <expr>)+ [ELSE expr] END
  • CASE ( WHEN <criteria> THEN <expr>)+ [ELSE expr] END
Each form allows for an output based on conditional logic. The first form starts with an initial expression and evaluates WHEN expressions until the values match, and outputs the THEN expression. If no WHEN is matched, the ELSE expression is output. If no WHEN is matched and no ELSE is specified, a null literal value is output. The second form (the searched case expression) searches the WHEN clauses, which specify an arbitrary criteria to evaluate. If any criteria evaluates to true, the THEN expression is evaluated and output. If no WHEN is true, the ELSE is evaluated or NULL is output if none exists.

3.3.9. Scalar Subqueries

Subqueries can be used to produce a single scalar value in the SELECT, WHERE, or HAVING clauses only. A scalar subquery must have a single column in the SELECT clause and should return either 0 or 1 row. If no rows are returned, null will be returned as the scalar subquery value. For other types of subqueries, refer to Section 3.5.10, “Subqueries”.

3.3.10. Parameter References

Parameters are specified using a '?' symbol. Parameters may only be used with prepared statements or callable statements in JDBC. Each parameter is linked to a value specified by a one-based index in the JDBC API.

3.3.11. 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))
  • expression [NOT] IS NULL
  • expression [NOT] IN (expression[,expression]*)|subquery
  • expression [NOT] LIKE pattern [ESCAPE char]
    LIKE 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

    JBoss Data Virtualization 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 mechansim 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.

    Important

    JBoss Data Virtualization 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. Additionally, not all sources support the same regular expression syntax or extensions. Care should be taken in pushdown situations to ensure that the pattern used will have the same meaning in JBoss Data Virtualization and across all applicable sources. 
  • EXISTS(subquery)
  • expression [NOT] BETWEEN minExpression AND maxExpression
    JBoss Data Virtualization 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'

Note

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.

3.3.12. Operator Precedence

JBoss Data Virtualization parses and evaluates operators with higher precedence before those with lower precedence. Operators with equal precedence are left associative. The following operator precedence is listed from highest to lowest:
Operator
Description
+,-
positive/negative value expression
*,/
multiplication/division
+,-
addition/subtraction
||
concat
criteria
see Section 3.3.11, “Criteria”

3.3.13. Criteria Precedence

JBoss Data Virtualization parses and evaluates conditions with higher precedence before those with lower precedence. Conditions with equal precedence are left associative. The following condition precedence is listed from highest to lowest:
Condition
Description
SQL operators
See Section 3.3.1, “Expressions”
EXISTS, LIKE, SIMILAR TO, LIKE_REGEX, BETWEEN, IN, IS NULL, <, <=, >, >=, =, <>
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.