SQL Capabilities

DML allows you to update and query data stored in OmniSci.

See Using Geospatial Objects: Geospatial Functions for details on geospatial functions.

`INSERT`

INSERT INTO <table> VALUES (value, ...);

Use this statement for single-row ad hoc inserts. (When inserting many rows, use the more efficient COPY command.)

CREATE TABLE foo (a INT, b FLOAT, c TEXT, d TIMESTAMP);
INSERT INTO foo VALUES (NULL, 3.1415, 'xyz', '2015-05-11 211720`);

For array inserts, use the following syntax:

CREATE TABLE ar1 (ar INT[]);
INSERT INTO ar1 VALUES ({1,2,3});

`SELECT`

[ WITH <alias> AS <query>,... ]
SELECT [ALL|DISTINCT] <expr> [AS [<alias>]], ...
  FROM <table> [ <alias> ], ...
  [WHERE <expr>]
  [GROUP BY <expr>, ...]
  [HAVING <expr>]
  [ORDER BY <expr> [ ASC | DESC ] , ...] [ NULLS FIRST | NULLS LAST ]
  [LIMIT {<number>|ALL} [OFFSET <number> [ROWS]]]
  [ANY | ALL (subquery) ;

Usage Notes

ORDER BY

Sort order defaults to ascending (ASC).
Sorts null values after non-null values by default in an ascending sort, before non-null values in a descending sort. For any query, you can use NULLS FIRST to sort null values to the top of the results or NULLS LAST to sort null values to the bottom of the results.
Allows you to use a positional reference to choose the sort column. For example, the command SELECT colA,colB FROM table1 ORDER BY 2 sorts the results on colB because it is in position 2.

For more information, see SELECT.

`UPDATE`

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

UPDATE changes the values of the specified columns in all rows that satisfy the condition. For more information, see UPDATE.

`DELETE`

DELETE FROM table_name [ * ] [ [ AS ] alias ]
[ WHERE condition ]

DELETE deletes rows that satisfy the WHERE clause from the specified table. If the WHERE clause is absent, the effect is to delete all rows in the table. The result is a valid, but empty table. For more information, see DELETE.

`EXPLAIN`

Shows generated Intermediate Representation (IR) code, identifying whether it is executed on GPU or CPU. This is primarily used internally by OmniSci to monitor behavior.

EXPLAIN <STMT>;

For example, when you use the EXPLAIN command on a basic statement, the utility returns 90 lines of IR code that is not meant to be human readable. At the top of the listing, though, a heading indicates whether it is IR for the CPU or IR for the GPU, which can be useful to know in some situations.

`EXPLAIN CALCITE`

Returns a Relational Algebra tree describing the high-level plan to execute the statement.

EXPLAIN CALCITE <STMT>;

The table below lists the relational algebra classes used to describe the execution plan for a SQL statement.

Method	Description
`LogicalAggregate`	Relational operator that eliminates duplicates and computes totals
`LogicalCalc`	Relational expression that computes project expressions and also filters
`LogicalChi`	Relational operator that converts a stream to a relation
`LogicalCorrelate`	Relational operator that performs nested-loop joins
`LogicalDelta`	Relational operator that converts a relation to a stream
`LogicalExchange`	Relational expression that imposes a particular distribution on its input without otherwise changing its content
`LogicalFilter`	Relational expression that iterates over its input and returns elements for which a condition evaluates to true
`LogicalIntersect`	Relational expression that returns the intersection of the rows of its inputs
`LogicalJoin`	Relational expression that combines two relational expressions according to some condition
`LogicalMatch`	Relational expression that represents a MATCH_RECOGNIZE node
`LogicalMinus`	Relational expression that returns the rows of its first input minus any matching rows from its other inputs. Corresponds to the SQL EXCEPT operator
`LogicalProject`	Relational expression that computes a set of ‘select expressions’ from its input relational expression
`LogicalSort`	Relational expression that imposes a particular sort order on its input without otherwise changing its content
`LogicalTableFunctionScan`	Relational expression that calls a table-valued function
`LogicalTableModify`	Relational expression that modifies a table. It is similar to TableScan, but represents a request to modify a table rather than read from it
`LogicalTableScan`	Reads all the rows from a RelOptTable
`LogicalUnion`	Relational expression that returns the union of the rows of its inputs, optionally eliminating duplicates
`LogicalValues`	Relational expression whose value is a sequence of zero or more literal row values
`LogicalWindow`	Relational expression representing a set of window aggregates

For example, a SELECT statement is described as a table scan and projection.

mapdql> explain calcite (select * from movies);
Explanation
LogicalProject(movieId=[$0], title=[$1], genres=[$2])
   LogicalTableScan(table=[[CATALOG, mapd, MOVIES]])

If you add a sort order, the table projection is folded under a LogicalSort procedure.

mapdql> explain calcite (select * from movies order by title);
Explanation
LogicalSort(sort0=[$1], dir0=[ASC])
   LogicalProject(movieId=[$0], title=[$1], genres=[$2])
      LogicalTableScan(table=[[CATALOG, mapd, MOVIES]])

When the SQL statement is simple, the EXPLAIN CALCITE version is actually less “human readable.” EXPLAIN CALCITE is more useful when you work with more complex SQL statements, like the one that follows. This query performs a scan on the BOOK table before scanning the BOOK_ORDER table.

mapdql> explain calcite SELECT bc.firstname, bc.lastname, b.title, bo.orderdate, s.name
FROM book b, book_customer bc, book_order bo, shipper s
WHERE bo.cust_id = bc.cust_id AND b.book_id = bo.book_id AND bo.shipper_id = s.shipper_id
AND s.name = 'UPS';
Explanation
LogicalProject(firstname=[$5], lastname=[$6], title=[$2], orderdate=[$11], name=[$14])
    LogicalFilter(condition=[AND(=($9, $4), =($0, $8), =($10, $13), =($14, 'UPS'))])
        LogicalJoin(condition=[true], joinType=[inner])
            LogicalJoin(condition=[true], joinType=[inner])
                LogicalJoin(condition=[true], joinType=[inner])
                    LogicalTableScan(table=[[CATALOG, mapd, BOOK]])
                    LogicalTableScan(table=[[CATALOG, mapd, BOOK_CUSTOMER]])
                LogicalTableScan(table=[[CATALOG, mapd, BOOK_ORDER]])
            LogicalTableScan(table=[[CATALOG, mapd, SHIPPER]])

Revising the original SQL command results in a more natural selection order and a more performant query.

mapdql> explain calcite SELECT bc.firstname, bc.lastname, b.title, bo.orderdate, s.name
FROM book_order bo, book_customer bc, book b, shipper s
WHERE bo.cust_id = bc.cust_id AND bo.book_id = b.book_id AND bo.shipper_id = s.shipper_id
AND s.name = 'UPS';
Explanation
LogicalProject(firstname=[$10], lastname=[$11], title=[$7], orderdate=[$3], name=[$14])
    LogicalFilter(condition=[AND(=($1, $9), =($5, $0), =($2, $13), =($14, 'UPS'))])
        LogicalJoin(condition=[true], joinType=[inner])
            LogicalJoin(condition=[true], joinType=[inner])
                LogicalJoin(condition=[true], joinType=[inner])
                  LogicalTableScan(table=[[CATALOG, mapd, BOOK_ORDER]])
                  LogicalTableScan(table=[[CATALOG, mapd, BOOK_CUSTOMER]])
                LogicalTableScan(table=[[CATALOG, mapd, BOOK]])
            LogicalTableScan(table=[[CATALOG, mapd, SHIPPER]])

Table Expression and Join Support

<table> , <table> WHERE <column> = <column>
<table> [ LEFT ] JOIN <table> ON <column> = <column>

Usage Notes

If a join column name or alias is not unique, it must be prefixed by its table name.
You can use BIGINT, INTEGER, SMALLINT, DATE, or TEXT ENCODING DICT data types. TEXT ENCODING DICT is the most efficient because corresponding dictionary IDs are sequential and span a smaller range than, for example, the 65,535 values supported in a SMALLINT field. Depending on the number of values in your field, you can use TEXT ENCODING DICT(32) (up to 1,000,000,000 distinct values), TEXT ENCODING DICT(16) (up to 64,000 distinct values), or TEXT ENCODING DICT(8) (up to 255 distinct values). For more information, see Data Types and Fixed Encoding.
Data types of join columns must match exactly. For example, a SMALLINT column cannot be joined to a BIGINT column.
For all but the first table list in the from-list, the data values in the join column must be unique. In data warehouse terms, list the “fact” table first, followed by any number of “dimension” tables.

Logical Operator Support

Operator	Description
`AND`	Logical AND
`NOT`	Negates value
`OR`	Logical OR

Conditional Expression Support

Expression	Description
`CASE WHEN condition THEN result ELSE default END`	Case operator
`COALESCE(val1, val2, ..)`	Returns the first non-null value in the list

Subquery Expression Support

Expression	Description
`expr IN (subquery or list of values)`	Evaluates whether expr equals any value of the IN list.
`expr NOT IN (subquery or list of values)`	Evaluates whether expr does not equal any value of the IN list.

Usage Notes

You can use a subquery anywhere an expression can be used, subject to any runtime constraints of that expression. For example, a subquery in a CASE statement must return exactly one row, but a subquery can return multiple values to an IN expression.
You can use a subquery anywhere a table is allowed (for example, FROM subquery), using aliases to name any reference to the table and columns returned by the subquery.

Type Cast Support

Expression	Example	Description
`CAST(expr AS type`)	`CAST(1.25 AS FLOAT)`	Converts an expression to another data type

The following table shows cast type conversion support.

FROM/TO:	`TINYINT`	`SMALLINT`	`INTEGER`	`BIGINT`	`FLOAT`	`DOUBLE`	`DECIMAL`	`TEXT`	`BOOLEAN`	`DATE`	`TIME`	`TIMESTAMP`
`TINYINT`	-	Yes	Yes	Yes	Yes	Yes	Yes	No	No	No	No	n/a
`SMALLINT`	Yes	-	Yes	Yes	Yes	Yes	Yes	No	No	No	No	n/a
`INTEGER`	Yes	Yes	-	Yes	Yes	Yes	Yes	No	No	No	No	No
`BIGINT`	Yes	Yes	Yes	-	Yes	Yes	Yes	No	No	No	No	No
`FLOAT`	Yes	Yes	Yes	Yes	-	Yes	No	No	No	No	No	No
`DOUBLE`	Yes	Yes	Yes	Yes	Yes	-	No	No	No	No	No	n/a
`DECIMAL`	Yes	Yes	Yes	Yes	Yes	Yes	-	No	No	No	No	n/a
`TEXT`	No	No	No	No	No	No	No	-	No	No	No	No
`BOOLEAN`	No	No	No	No	No	No	No	No	-	n/a	n/a	n/a
`DATE`	No	No	No	No	No	No	No	No	n/a	-	No	Yes
`TIME`	No	No	No	No	No	No	No	No	n/a	No	-	n/a
`TIMESTAMP`	No	No	No	No	No	No	No	No	n/a	Yes	No	-

Array Support

Expression	Description
`SELECT <ArrayCol>[n] ...`	Query array elements n of column `ArrayCol`.
`UNNEST(<ArrayCol>) ...`	Expand the array `ArrayCol` to a set of rows.
`SELECT <some_column> FROM <your_table> WHERE <test> = ANY <array_column>`	ANY compares a scalar value with a single set of values (in the text array <array_column>), and returns TRUE when the result contains at least one item. ANY must be preceded by a comparison operator.
`SELECT <some_column> FROM <your_table> WHERE <test> = ALL <array_column>`	ALL compares a scalar value with a single set of values (in the text array <array_column>), and returns TRUE when the result specified is TRUE for all items in the array. ALL must be preceded by a comparison operator.
`ARRAYINDEX(row_index)`	Returns a value from a specific location in an array.

Note: As with many SQL-based services, OmniSci array indexes are 1-based.

`LIKELY/UNLIKELY`

Expression	Description
`LIKELY(X)`	Provides a hint to the query planner that argument X is a Boolean value that is usually true. The planner can prioritize filters on the value X earlier in the execution cycle and return results more efficiently.
`UNLIKELY(X)`	Provides a hint to the query planner that argument X is a Boolean value that is usually not true. The planner can prioritize filters on the value X later in the execution cycle and return results more efficiently.

Usage Notes

SQL normally assumes that terms in the WHERE clause that cannot be used by indices are usually true. If this assumption is incorrect, it could lead to a suboptimal query plan. Use the LIKELY(X) and UNLIKELY(X) SQL functions to provide hints to the query planner about clause terms that are probably not true, which helps the query planner to select the best possible plan.

Use LIKELY/UNLIKELY to optimize evaluation of OR/AND logical expressions. LIKELY/UNLIKELY causes the left-hand side of an expression to be evaluated first. This allows the right-hand side of the query to be skipped when possible. For example, in the clause "UNLIKELY(A) AND B", if A evaluates to FALSE, there is no need to evaluate B.

Consider the following:

SELECT COUNT(*) FROM test WHERE UNLIKELY(x IN (7, 8, 9, 10)) AND y > 42;

If x is one of the values 7, 8, 9, or 10, the filter of y > 42 is applied. If x is not one of the values 7, 8, 9, or 10, the filter of y > 42 is not applied.

Main Content

SQL Capabilities

INSERT

SELECT

Usage Notes

UPDATE

DELETE

EXPLAIN

EXPLAIN CALCITE

Table Expression and Join Support

Logical Operator Support

Conditional Expression Support

Subquery Expression Support

Type Cast Support

Array Support

LIKELY/UNLIKELY

`INSERT`

`SELECT`

`UPDATE`

`DELETE`

`EXPLAIN`

`EXPLAIN CALCITE`

`LIKELY/UNLIKELY`