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

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

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

Example

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

Array literals and the ARRAY function are supported for array inserts:

CREATE TABLE ar1 (ar INT[]);
INSERT INTO ar1 VALUES ({1,2,3});
CREATE TABLE ar1 (ar INT[]);
INSERT INTO ar1 VALUES ({1,2,3},{4.0,5.0},{1.2,3.4});
CREATE TABLE ar1 (ar INT[])
INSERT INTO ar1 VALUES (ARRAY[1,2,3],ARRAY[4.0,5.0],ARRAY[1.2,3.4]);
 

SELECT

query:
  |   WITH withItem [ , withItem ]* query
  |   {
          select
      }
      [ ORDER BY orderItem [, orderItem ]* ]
      [ LIMIT [ start, ] { count | ALL } ]
      [ OFFSET start { ROW | ROWS } ]

withItem:
      name
      [ '(' column [, column ]* ')' ]
      AS '(' query ')'

orderItem:
      expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]

select:
      SELECT [ DISTINCT ]
          { * | projectItem [, projectItem ]* }
      FROM tableExpression
      [ WHERE booleanExpression ]
      [ GROUP BY { groupItem [, groupItem ]* } ]
      [ HAVING booleanExpression ]

projectItem:
      expression [ [ AS ] columnAlias ]
  |   tableAlias . *

tableExpression:
      tableReference [, tableReference ]*
  |   tableExpression [ ( LEFT ) [ OUTER ] ] JOIN tableExpression [ joinCondition ]

joinCondition:
      ON booleanExpression
  |   USING '(' column [, column ]* ')'

tableReference:
      tablePrimary
      [ [ AS ] alias ]

tablePrimary:
      [ catalogName . ] tableName
  |   '(' query ')'

groupItem:
      expression
  |   '(' expression [, expression ]* ')'

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

UPDATE table_name SET assign [, assign ]* [ WHERE booleanExpression ]

Changes the values of the specified columns based on the assign argument (identifier=expression) in all rows that satisfy the condition in the WHERE clause.

Example

UPDATE UFOs SET shape='ovate' where shape='eggish'; 
Note Currently, OmniSci does not support updating a geo column type (POINT, LINESTRING, POLYGON, or MULTIPOLYGON) in a table.

DELETE

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

Deletes rows that satisfy the WHERE clause from the specified table. If the WHERE clause is absent, all rows in the table are deleted, resulting in 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. However, at the top of the listing, 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.

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

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

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

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

omnisql> explain calcite (select * from movies order by title);
Explanation
LogicalSort(sort0=[$1], dir0=[ASC])
   LogicalProject(movieId=[$0], title=[$1], genres=[$2])
      LogicalTableScan(table=[[CATALOG, omnisci, 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.

omnisql> 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, omnisci, BOOK]])
                    LogicalTableScan(table=[[CATALOG, omnisci, BOOK_CUSTOMER]])
                LogicalTableScan(table=[[CATALOG, omnisci, BOOK_ORDER]])
            LogicalTableScan(table=[[CATALOG, omnisci, SHIPPER]])

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

omnisql> 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, omnisci, BOOK_ORDER]])
                  LogicalTableScan(table=[[CATALOG, omnisci, BOOK_CUSTOMER]])
                LogicalTableScan(table=[[CATALOG, omnisci, BOOK]])
            LogicalTableScan(table=[[CATALOG, omnisci, SHIPPER]])

Table Expression and Join Support

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

If a join column name or alias is not unique, it must be prefixed by its table name.

You can use BIGINT, INTEGER, SMALLINT, TINYINT, DATE, TIME, TIMESTAMP, 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 approximately 2,150,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.

Geospatial Joins

By default, a join involving a geospatial operator (such as ST_Contains) utilizes the loop join framework.

To allow all loop joins, set the allow-loop-joins flag to true at either the command line when starting OmniSci, or in mapd.conf. Running geo join queries without allow-loop-joins set to true results in the following error:

Hash join failed: no equijoin expression found.

If you set trivial-loop-join-threshold, loop joins are allowed if the inner table has fewer rows than the trivial join loop threshold you specifiy. The default value is 1,000 rows.

For geospatial joins, the inner table should always be the more complicated primitive. For example, for ST_Contains(polygon, point), the point table should be the outer table and the polygon table should be the inner table.

Note
  • Geo join best practice is to increase the trivial join loop threshold for the size of the inner table.
  • When you increase the trivial loop join threshold by 1, you increase the run time by the number of rows in your outer table. For example, if your outer table has 100,000,000 rows, and your trivial loop join threshold is 10, you potentially run 1,000,000,000 operations in the loop. If you increase the loop join to 100, you increase the number of operations run to 10,000,000,000.

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

OmniSci supports arrays in dictionary-encoded text and number fields (TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, and DOUBLE). Data stored in arrays are not normalized. For example, {green,yellow} is not the same as {yellow,green}. As with many SQL-based services, OmniSci array indexes are 1-based.

Expression Description
ArrayCol[n] ... Returns value(s) from specific location n in the array.
UNNEST(ArrayCol) Extract the values in the array to a set of rows. Requires GROUP BY; projecting UNNEST is not currently supported.
test = ANY ArrayCol ANY compares a scalar value with a single row or set of values in an array, returning results in which at least one item in the array matches. ANY must be preceded by a comparison operator.
test = ALL ArrayCol ALL compares a scalar value with a single row or set of values in an array, returning results in which all records in the array field are compared to the scalar value. ALL must be preceded by a comparison operator.
CARDINALITY(<ArrayCol>) Returns the number of elements in an array. For example:
omnisql> \d arr
CREATE TABLE arr (
sia SMALLINT[])
omnisql> select sia, CARDINALITY(sia) from arr;
sia|EXPR$0
{NULL}|1
{1}|1
{2,2}|2
{3,3,3}|3

Examples

The following examples show query results based on the table test_array created with the following statement:

CREATE TABLE test_array (name TEXT ENCODING DICT(32),colors TEXT[] ENCODING DICT(32), qty INT[]);

omnisql> SELECT * FROM test_array;
name|colors|qty
Banana|{green, yellow}|{1, 2}
Cherry|{red, black}|{1, 1}
Olive|{green, black}|{1, 0}
Onion|{red, white}|{1, 1}
Pepper|{red, green, yellow}|{1, 2, 3}
omnisql> SELECT UNNEST(colors) AS c FROM test_array;
Exception: UNNEST not supported in the projection list yet.
omnisql> SELECT UNNEST(colors) AS c, count(*) FROM test_array group by c;
c|EXPR$1
green|3
yellow|2
red|3
black|2
white|1
omnisql> SELECT name, colors [2] FROM test_array;
name|EXPR$1
Banana|yellow
Cherry|black
Olive|black
Onion|white
Pepper|green
omnisql> SELECT name, colors FROM test_array WHERE colors[1]='green';
name|colors
Banana|{green, yellow}
Olive|{green, black}

The following queries use arrays in an INTEGER field:

omnisql> SELECT name, qty FROM test_array WHERE qty[2] >1;
name|qty
Banana|{1, 2}
Pepper|{1, 2, 3}
omnisql> SELECT name, qty FROM test_array WHERE 15< ALL qty;
No rows returned.
omnisql> SELECT name, qty FROM test_array WHERE 2 = ANY qty;
name|qty
Banana|{1, 2}
Pepper|{1, 2, 3}

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 side of an expression to be evaluated first. This allows the right side of the query to be skipped when possible. For example, in the clause UNLIKELY(A) AND B, if A evaluates to FALSE, B does not need to be evaluated.

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 y > 42 is applied. If x is not one of those values, the filter y > 42 is not applied.