Queries

SQL compatibility

Kubling provides most of the functionality of SQL-92 DML. The idea of this document is not to cover SQL exhaustively, but rather highlights how SQL is used in Kubling.

Identifiers

SQL commands contain references to tables and columns. These references are in the form of identifiers, which uniquely identify the tables and columns in the context of the command. All queries are processed in the context of a virtual database, or VDB. Because information can be federated across multiple sources, tables and columns must be scoped in some manner to avoid conflicts. This scoping is provided by schemas, which contain the information for each data source or set of views.

Fully-qualified table and column names are of the following form, where the separate `parts' of the identifier are delimited by periods.

• TABLE: <schema_name>.<table_spec>
• COLUMN: <schema_name>.<table_spec>.<column_name>

Syntax

• Identifiers can consist of alphanumeric characters, or the underscore (_) character, and must begin with an alphabetic character. Any Unicode character may be used in an identifier.

• Identifiers in double quotes can have any contents. The double quote character can be used if is escaped with an additional double quote; for example, "some "" id".

• Because different data sources organize tables in different ways, with some prepending catalog, schema, or user information, Kubling allows table specification to be a dot-delimited construct.

• Columns, column aliases, and schemas cannot contain a dot (.) character.

• Identifiers, even when quoted, are not case-sensitive in Kubling.

Examples of valid, fully-qualified TABLE identifiers are:

• Kubernetes.DEPLOYMENTS
• "Kubernetes.Deployments"

Examples of valid, fully-qualified COLUMN identifiers are:

• Kubernetes.DEPLOYMENTS.metadata__name
• "Kubernetes.Deployments"."metadata__name"

Operator precedence

Expressions

Identifiers, literals, and functions can be combined into expressions. Expressions can be used in a query with nearly any keyword, including SELECT, FROM (if specifying join criteria), WHERE, GROUP BY, HAVING, or ORDER BY.

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 was defined in the Identifiers section above.

Literals

Literal values represent fixed values. These can be any of the 'standard' data types. For information about data types, see Data 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 nullvalue will be assigned the type `integer` to match the type of the value `5`. Anull`` literal used in the SELECT clause of a query with no implied context will be assigned to type string.

Examples

'abc'

'isn"t true'

5

-37.75e01

true

false

Unicode character:

'\u0027'

Binary:

X'0F0A'

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.

COUNT(*) Count the number of values (including nulls and duplicates) in a group. Returns an integer - an exception will be thrown if a larger count is computed.

COUNT(x) Count the number of values (excluding nulls) in a group. Returns an integer - an exception will be thrown if a larger count is computed.

COUNT_BIG(*) Count the number of values (including nulls and duplicates) in a group. Returns a long - an exception will be thrown if a larger count is computed.

COUNT_BIG(x) Count the number of values (excluding nulls) in a group. Returns a long - an exception will be thrown if a larger count is computed.

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)).

Case and searched case

To include conditional logic in a scalar expression, you can use the following two forms of the CASE expression:

• 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.

Examples

SELECT CASE columnA WHEN '10' THEN 'ten' WHEN '20' THEN 'twenty' END AS myExample
 
SELECT CASE WHEN columnA = '10' THEN 'ten' WHEN columnA = '20' THEN 'twenty' END AS myExample

Arrays

Empty arrays

()
(,)
ARRAY[]

Single element array

(expr,)
ARRAY[expr]

General array syntax

(expr, expr ... [,])
ARRAY[expr, ...]

If all of the elements in the array are of the same type, the array will be of a matching base type. If the elements types differ the array base type, it will be of object.

An array element reference takes the form of:

array_expr[index_expr]

index_expr must resolve to an integer value. This syntax is effectively the same as the array_get system function and expects 1-based indexing.

Criteria

Criteria can be any of the following items:

• Predicates that evaluate to true or false.
• Logical criteria that combine criteria (AND, OR, NOT).
• A value expression of type boolean.

General usage:

criteria AND|OR criteria

NOT criteria

(criteria)

expression (=|<>|!=|<|>|<=|>=) (expression|((ANY|ALL|SOME) subquery|(array_expression)))

expression IS [NOT] DISTINCT FROM expression

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.

expression [NOT] LIKE_REGEX pattern

EXISTS (subquery)

expression [NOT] BETWEEN minExpression AND maxExpression

The Engine 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 and OR.

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

Some examples of valid criteria are:

• (requests > 2500.0)
• 100*(50 - x)/(25 - y) > z
• concat(areaCode,concat('-',phone)) LIKE '314%1'

Criteria precedence The Engine parses and evaluates conditions with higher precedence before those with lower precedence. Conditions with equal precedence are left-associative. The following table lists condition precedence from high to low:

Scalar Functions

Numeric Functions

String Functions

Unless specified, all of the arguments and return types in the following table are strings and all indexes are 1-based. The 0 index is considered to be before the start of the string.

Type Conversion Functions

Data and Time Functions

Decode Functions

Document Functions v24.5.3+

Query Planner Limitations on WHERE Clauses with json Objects

As of version v24.5.3, the query planner is not yet optimized to perform efficient comparisons on json objects in the WHERE clause, even when the value expression is within a type conversion function. Consider the following example:

SELECT * 
FROM 
	kube.EVENT 
WHERE 
	convert(jsonPath(regarding, '$.uid'), string) = '7e613d43'

Assuming that such a uid exists, we expect the query planner to drop the entire query due to its current limitations in creating an optimized node based on json objects.

For these cases, you should use jsonPathAsString or jsonJqAsString instead, as shown below:

SELECT * 
FROM 
	kube.EVENT 
WHERE 
	jsonPathAsString(regarding, '$.uid') = '7e613d43'

Choice functions

SELECT command

The SELECT command is used to retrieve records for any number of relations.

A SELECT command can contain the following clauses:

Except for the OPTION clause, all of the preceding clauses are defined by the SQL specification. The specification also tells the order in which these clauses are logically processed. Processing occurs in stages, with each stage passing a set of rows to the following stage. The processing model is logical, and does not represent the way that a Engine performs the actual processing, but it is a useful model for understanding how SQL works.
The SELECT command processes clauses in the following stages:

WITH clause

The Engine provides access to common table expressions via the WITH clause. You can reference WITH clause items as tables in subsequent WITH clause items, and in the main query. You can think of the WITH clause as providing query-scoped temporary tables.

Usage:

WITH name [(column, ...)] AS [/*+ no_inline|materialize */] (query expression) ...

Syntax rules:

• All of the projected column names must be unique. If they are not unique, then the column name list must be provided.
• If the columns of the WITH clause item are declared, then they must match the number of columns projected by the query expression.
• Each WITH clause item must have a unique name.
• The optional no_inline hint indicates to the QP optimizer that the query expression should not be substituted as an inline view where referenced. It is possible with no_inline for multiple evaluations of the common table as needed by source queries.
• The optional materialize hint requires that the common table be created as a temporary table. This forces a single evaluation of the common table.

Examples:

WITH n (x) AS (select col from tbl) select x from n, n as n1

WITH n (x) AS /*+ no_inline */ (select col from tbl) select x from n, n as n1

FROM clause

The FROM clause specifies the target tables for SELECT, UPDATE, and DELETE statements.

Example Syntax:

FROM table [[AS] alias]

FROM table1 [INNER|LEFT OUTER|RIGHT OUTER|FULL OUTER] JOIN table2 ON join-criteria

FROM table1 CROSS JOIN table2

FROM (subquery) [AS] alias

FROM TABLE(subquery) [AS] alias

FROM clause hints

FROM clause hints are typically specified in a comment block preceding the affected clause. MAKEDEP and MAKENOTDEP may also appear after in non-comment form after the affected clause. If multiple hints apply to that clause, the hints should be placed in the same comment block.

Example:

FROM /*+ MAKEDEP PRESERVE */ (tbl1 inner join tbl2 inner join tbl3 on tbl2.col1 = tbl3.col1 on tbl1.col1 = tbl2.col1), tbl3 WHERE tbl1.col1 = tbl2.col1

Dependent JOIN hints

MAKEIND, MAKEDEP, and MAKENOTDEP are hints that you can use to control dependent join behavior. Use them only in situations where the optimizer does not choose the most optimal plan based upon query structure, metadata, and costing information. The hints can appear in a comment that follows the FROM keyword. The hints can be specified against any FROM clause, not just a named table.

MAKEIND

Indicates that the clause should be the independent (feeder) side of a dependent join.

MAKEDEP

Indicates that the clause should be the dependent (filtered) side of a join.

MAKENOTDEP

Prevents the clause from being the dependent (filtered) side of a join.

MAKEDEP(JOIN)

Indicates that the entire JOIN should be pushed.

MAKEDEP(NO JOIN)

Indicates that the entire JOIN should not be pushed.

MAKEDEP(MAX:val)

Indicates that the dependent JOIN should only be performed if there are less than the maximum number of values from the independent side.

When working with a poor or absent cardinality, which is very frequent in the case of Script Modules since the systems they interact with exchanges JSON documents, it is not possible for the query planner to determine what to get first, sometimes making queries to not work properly or even failing, due to the fact that the Engine will try to perform a TABLE full scan.

Since the concept is a bit complex, let's use the following example, which is actually a part of the samples repo:

  SELECT
    a.NAME as app_name,
    a.ID as app_id,
    c.ID as component_id, 
    c.NAME as component_name, 
    r.html_url as repo_location,
    cd.DEPLOYMENT_IDENTIFIER,
    cd.environment,
    deploy.clusterName as cluster,
    deploy.status__replicas  as desired_replicas,
    deploy.status__availableReplicas as available_replicas,
    deploy.status__readyReplicas as ready_replicas
  FROM
    app_db.APPLICATION a
    LEFT JOIN app_db.COMPONENT c ON c.APPLICATION_ID = a.ID
    LEFT JOIN COMPONENT_CODE_REPO_JOIN cc ON cc.COMPONENT_ID = c.ID
    LEFT JOIN app_db.COMPONENT_DEPLOYMENT_JOIN cd ON cd.COMPONENT_ID = c.ID
    LEFT JOIN /*+ makedep */ github.CODE_REPO r ON (r.org = cc.CODE_REPO_ORG AND r.name = cc.CODE_REPO_NAME)
    LEFT JOIN /*+ makedep */ k8s.DEPLOYMENT deploy  ON (deploy.identifier = cd.DEPLOYMENT_IDENTIFIER)
  WHERE a.NAME='{{ app_name }}'

This query runs on a VDB that has 5 Data Sources:

• 1 POSTGRESQL
• 2 KUBERNETES
• 1 SCRIPT_DOCUMENT_JS
• 1 aggregator

The query above gets all applications registered in the POSTGRESQL, with some extra information, like their GitHub repositories (SCRIPT_DOCUMENT_JS) and their deployment clusters (KUBERNETES).

In our context we support multiple organizations but, by design, github.CODE_REPO forces to filter, at least, by one organization since the API works that way.
Due to the fact that the QP does not see any relationship between github.CODE_REPO and cc.CODE_REPO_ORG, it may decide to run a full scan over github.CODE_REPO and then apply the criteria. However, the full scan will never complete since github.CODE_REPO will throw an error due to the lack of the organization in the WHERE clause.

By using the /*+ makedep */ hint, we ensure that clause depends on the filter side of the join, in other words, the QP will first get the list of cc.CODE_REPO_ORG and cc.CODE_REPO_NAME before generating a query to route to github.CODE_REPO.
Therefore, your script will receive a standard SELECT with a WHERE clause in the form of IN or OR list.

WHERE clause

The WHERE clause defines the criteria to limit the records affected by SELECT, UPDATE, and DELETE statements.
See its general form.

GROUP BY clause

The GROUP BY clause denotes that rows should be grouped according to the specified expression values. One row is returned for each group, after optionally filtering those aggregate rows based on a HAVING clause.

The general form of the GROUP BY:

GROUP BY expression [,expression]*

GROUP BY ROLLUP(expression [,expression]*)

Syntax Rules:

• Column references in the group by cannot be made to alias names in the SELECT clause.
• Expressions used in the group by must appear in the select clause.
• Column references and expressions in the SELECT/HAVING/ORDER BY clauses that are not used in the group by clause must appear in aggregate functions.
• If an aggregate function is used in the SELECT clause and no GROUP BY is specified, an implicit GROUP BY will be performed with the entire result set as a single group. In this case, every column in the SELECT must be an aggregate function as no other column value will be fixed across the entire group.
• The GROUP BY columns must be of a comparable type.

HAVING clause

The HAVING clause operates exactly as the WHERE clause, although it operates on the output of a GROUP BY. You can use the same syntax with the HAVING clause as with the WHERE clause.

Syntax Rule:

• Expressions used in the GROUP BY clause must contain either an aggregate function (COUNT, AVG, SUM, MIN, MAX), or be one of the grouping expressions.

SELECT clause

Queries that start with the SELECT keyword and are often referred to as SELECT statements. Usage:

SELECT [DISTINCT|ALL] ((expression [[AS] name])|(group identifier.STAR))*|STAR ...

Syntax Rules:

• Aliased expressions are only used as the output column names and in the ORDER BY clause. They cannot be used in other clauses of the query.
• DISTINCT may only be specified if the SELECT symbols are comparable.

ORDER BY clause

The ORDER BY clause specifies how records are sorted. The options are ASC (ascending) or DESC (descending).

Usage:

ORDER BY expression [ASC|DESC] [NULLS (FIRST|LAST)], ...

Syntax rules:

• Sort columns can be specified by SELECT clause alias name, by SELECT clause expression, or by an unrelated expression.
• Column references can appear in the SELECT clause as the expression for an aliased column, or can reference columns from tables in the FROM clause. If the column reference is not in the SELECT clause, the query cannot be a set operation, specify SELECT DISTINCT, or contain a GROUP BY clause.
• Unrelated expressions, expressions not appearing as an aliased expression in the select clause, are allowed in the ORDER BY clause of a non-set QUERY. The columns referenced in the expression must come from the from clause table references. The column references cannot be to alias names.
• The ORDER BY columns must be of a comparable type.
• If an ORDER BY is used in an inline view or view definition without a LIMIT clause, it is removed by the query optimizer.
• If NULLS FIRST/LAST is specified, then nulls are guaranteed to be sorted either first or last. If the null ordering is not specified, then results will typically be sorted with nulls as low values, which is the default internal sorting behavior. However, not all data Sources return results with nulls sorted as low values by default, and the Engine might return results with different null orderings.

LIMIT clause

The LIMIT clause specifies a limit on the number of records returned by the SELECT command. An optional offset can be specified (the number of rows to skip). The LIMIT clause can also be specified using the SQL 2008 OFFSET/FETCH FIRST clauses. If an ORDER BY is also specified, it will be applied before the OFFSET/LIMIT are applied. If an ORDER BY is not specified there is generally no guarantee what subset of rows will be returned.

Usage:

LIMIT [offset,] limit

LIMIT limit OFFSET offset

[OFFSET offset ROW|ROWS] [FETCH FIRST|NEXT [limit] ROW|ROWS ONLY]

Syntax rules:

• The LIMIT/OFFSET expressions must be a non-negative integer or a parameter reference (?). An offset of 0 is ignored. A limit of 0 returns no rows.
• The terms FIRST/NEXT are interchangeable as well as ROW/ROWS.
• The LIMIT clause can take an optional preceding NON_STRICT hint to indicate that pushdown operations should not be inhibited, even if the results are not consistent with the logical application of the limit.
  The hint is only needed on unordered limits, for example, SELECT * FROM VW /*+ NON_STRICT */ LIMIT 2.

INTO clause

When the INTO clause is specified with a SELECT, the results of the query are inserted into the specified table.
This is often used to copy records from one Data Source to another, using an as-is approach or applying transforming functions to the original value. In some specific circumstances, it is also useful to insert records into a temporary table, although in Kubling we discourage the use of local storage.
The INTO clause immediately precedes the FROM clause.

Usage:

INTO [TABLE] FROM...

Syntax rules:

• The INTO clause is logically applied last in processing, after the ORDER BY and LIMIT clauses.
• In the SELECT INTO the target of the INTO clause is a table where the result of the SELECT command will be inserted.
• SELECT INTO can't be combined with a UNION query.

Example:

SELECT col1, col2 INTO [TABLE_TABLE] FROM [SOURCE_TABLE]