SQL Data Manipulation

Supported functionality related to Data Manipulation Language of SQL.

Syntax is case insensitive following this template:

KEYWORDS required_input [optional_input];

Escaping Keywords

Since version: 1.4 If you need to escape database or table or column, because it is named the same as our keyword, (e.g. if you have column named 'count' in your table and COUNT() is also a function), use brackets [] to escape it. See the example below:

SELECT t.[count] FROM My_Table AS t LIMIT 10;

SHOW Statement

SHOW DATABASES

Since version: 1.4 Shows loaded databases in memory.

SHOW DATABASES;

SHOW TABLES

Since version: 1.4 Shows tables from database. It is not needed to use database to execute this query, but it is possible.

SHOW TABLES FROM my_database;

Since version: 1.4 Also this syntax is valid:

SHOW TABLES IN my_database;

SHOW COLUMNS

Since version: 1.4 Shows columns from table. It is needed to use database to execute this query.

SHOW COLUMN FROM my_table;

Since version: 1.4 Also this syntax is valid:

SHOW COLUMNS IN my_table;

SHOW CONSTRAINTS

Since version: 1.4.2 Shows constraints - their type and name (id) and also columns to witch they apply.

SHOW CONSTRAINTS table_name;

SHOW QUERY COLUMN TYPES

Since version: 1.4.2 Shows columns and column types which will be returned as result when executing that particular query.

SHOW QUERY COLUMN TYPES query;

SELECT Statement

Since version: 1.4

SELECT expression [AS alias] [, expression [AS alias] ...]
FROM table [AS alias]
[WHERE expression]
[GROUP BY expression, ...]
[ORDER BY <expression> [ ASC | DESC ] , ...]
[LIMIT number [OFFSET number]];

AS

Since version: 1.4 Allows you to tag column or table with alias - custom string that you can use from that point instead of original name.

GROUP BY

Since version: 1.4 Allows you to group result rows into groups with the same values of the columns defined after GROUP BY keywords. Often used with aggregate functions.

LIMIT

Since version: 1.4 Allows you to get the specified number of records from the whole result.

OFFSET

Since version: 1.4 Allows you to skip the specified number of records from the result.

ORDER BY

Since version: 1.4 Sorts the result of a query according to the columns defined after ORDER BY keyword in ascending or descending order (can be specified). Default is ascending order.

WHERE

Since version: 1.4 Allows you to filter the records according to the specified conditions.

INNER JOIN [Experimental; Under Development]

Since version: 1.4 The INNER JOIN keyword selects records that have matching values in both tables. This is the default type of JOIN so it is not needed to write keyword 'INNER'. This implementation of INNER JOIN is very slow, because it is implemented as nested loop join. Also, there are some bugs, e.g. in some combinations of data types, it returns wrong results. We have decided not to improve this type of join but to rewrite it completely so it is much faster and bug free. The completely new type of JOIN will be released in December 2019 (see roadmap).

SELECT column_1 [, column_2, ...]
FROM table1
[INNER] JOIN table2
ON table_1.column_name = table_2.column_name;

INSERT INTO Statement

Since version: 1.4 Insert records of data into columns of a table.

INSERT INTO my_table (column_1 [, column_2, ...])
VALUES (value_1 [, value_2, ...]);

INSERT INTO String Values

Since version: 1.4 When inserting string values via INSERT INTO, use double quotation marks.

INSERT INTO my_table (string_column) VALUES ("string value");

INSERT INTO Geo Point or Geo Polygon Values

Since version: 1.4 When inserting geo point or geo polygon values via INSERT INTO, use double quotation marks.

INSERT INTO my_table (point_column, polygon_column) VALUES (
POINT(15 88.8695),
POLYGON((15 10, 22 89.85, 15 10), (89 115, 23 23, 89 115))
);

Operators

Since version: 1.4

Operator

Functionality

+

Add

-

Subtract

*

Multiply

/

Divide

%

Modulo

&

Bitwise AND

|

Bitwise OR

^

Bitwise XOR

=

Equal

>

Greater

<

Less

>=

Greater Or Equal

<=

Less Or Equal

<>

Not Equal

!=

Not Equal

>>

Left Shift

<<

Right Shift

AND

Logical AND

OR

Logical OR

NOT

Logical NOT

Geospatial Functions

Since version: 1.4 Geospatial functions currently work in planar (Euclidean) mode. Supported types are Point and Polygon. When geo column is selected, it is returned and displayed in well known text format, e.g. POINT(10.8 14), POLYGON((30.89 10, 40 40.2, 20 40, 10 20, 30.89 10)).

Note: Point and Polygon are keywords in SQL so tables or columns have to be named differently.

GEO CONTAINS

Since version: 1.4 Returns bool value whether point is in polygon. Both combinations are valid: GEO_CONTAINS(wkt_point_or_column, wkt_polygon_or_column) GEO_CONTAINS(wkt_polygon_or_column, wkt_point_or_column).

Example:

SELECT geo_point FROM geo_table
WHERE GEO_CONTAINS(
POLYGON((17.0779 48.1303, 17.0912 48.1303, 17 48, 17.0779 48.1303)),
geo_point);

GEO INTERSECT

Since version: 1.4 Calculates intersection of two polygons. Meaning that every point in the intersection appears in both first and second polygon. Returning type is Polygon.

GEO_INTERSECT(wkt_polygon_or_column, wkt_polygon_or_column)

Example:

SELECT GEO_INTERSECT(
POLYGON((17.0779 48.1303, 17.0912 48.1303, 17 48, 17.0779 48.1303)),
geo_polygon)
FROM geo_table;

GEO UNION

Since version: 1.4 Calculates union of two polygons. Returning type is polygon.

GEO_UNION(wkt_polygon_or_column, wkt_polygon_or_column)

Typical usage:

SELECT GEO_UNION(
POLYGON((17.0779 48.1303, 17.0912 48.1303, 17 48, 17.0779 48.1303)),
geo_polygon)
FROM geo_table;

Numeric Functions

ABS

Since version: 1.4 Calculates the absolute value of the numeric expression or number.

ABS(numeric_expression)

ACOS

Since version: 1.4 Calculates arc cosine of the numeric expression or number. This numeric expression or number needs to be greater than -1 and less than 1.

ACOS(numeric_expression)

ASIN

Since version: 1.4 Calculates arc sine of the numeric expression or number. This numeric expression or number needs to be greater than -1 and less than 1.

ASIN(numeric_expression)

ATAN

Since version: 1.4 Calculates arc tangent of the numeric expression or number.

ATAN(numeric_expression)

ATN2

Since version: 1.4 Calculates arc tangent of two numeric expressions or numbers.

ATN2(numeric_expression_1, numeric_expression_2)

AVG

Since version: 1.4 Calculates average value of the numeric expression.

AVG(numeric_expression)

CEIL

Since version: 1.4 Calculates an integer value from the numeric expression or number, so the new value is the smallest integer value that is greater or equal to the input numeric expression or number.

CEIL(numeric_expression)

COUNT

Since version: 1.4 Counts the number of records.

COUNT(expression)

COS

Since version: 1.4 Calculates cosine of the numeric expression or number.

COS(numeric_expression)

COT

Since version: 1.4 Calculates cotangent of the numeric expression or number.

COT(numeric_expression)

EXP

Since version: 1.4 Calculates Euler's number raised to the power of the input numeric expression or number.

EXP(numeric_expression)

FLOOR

Since version: 1.4 Calculates an integer value from the numeric expression or number, so the new value is the greatest integer value that is less or equal to input numeric expression or number.

FLOOR(numeric_expression)

LOG

Since version: 1.4 Calculates the natural logarithm of the input numeric expression or number or if there are two input numeric expressions or numbers, calculates logarithm of the first numeric expression or number to the base defined by the second numeric expression or number. The input numeric expression or number needs to be greater than 0 and base numeric expression or number needs to be greater than 1.

LOG(numeric_expression[, base])

LOG10

Since version: 1.4 Calculates the logarithm of the input numeric expression or number to the base of 10. The input numeric expression or number needs to be greater than 0.

LOG10(numeric_expression)

MAX

Since version: 1.4 Returns the maximum value.

MAX(numeric_expression)

MIN

Since version: 1.4 Returns the minimum value.

MIN(numeric_expression)

PI

Since version: 1.4 Provides the value of π (3.141592F).

PI()

POW

Since version: 1.4 Calculates the first input numeric expression or number raised to the power of the second numeric expression or number.

POW(base, exponent)

ROUND

Since version: 1.4 Rounds the input numeric expression or number to the number of decimal input.

ROUND(numeric_expression, decimal)

ROOT

Since version: 1.4 Calculates the n-th root from the input numeric expression or number.

ROOT(numeric_expression, n)

SIGN

Since version: 1.4 Returns the sign of the numeric expression or number.

SIGN(numeric_expression)

SIN

Since version: 1.4 Calculates sine of the numeric expression or number.

SIN(numeric_expression)

SQRT

Since version: 1.4 Calculates square root of the input numeric expression or number.

SQRT(numeric_expression)

SQUARE

Since version: 1.4 Calculates square of the input numeric expression or number.

SQUARE(numeric_expression)

SUM

Since version: 1.4 Calculates the sum of the input numeric expression.

SUM(numeric_expression)

TAN

Since version: 1.4 Calculates tangent of the input numeric expression or number.

TAN(numeric_expression)

Advanced Functionality

CAST

Since version: 1.4 Converts a value to a specified data type. The possible casting combinations are:

  • Bool → Bool, Double, Float, Int, Long, String

  • Double → Bool, Double, Float, Int, Long, String

  • Float → Bool, Double, Float, Int, Long, String

  • Int→ Bool, Double, Float, Int, Long, String

  • Long → Bool, Double, Float, Int, Long, String

  • String → Double, Float, Geo_Point, Int, Long, String

  • Geo_Point → String

  • Geo_Polygon → String

CAST(some_value AS data_type)

Two more casting combinations will be added in the near future (string to bool, string to geo_polygon).

IS NOT NULL

Since version: 1.4 Allows you to get the not null values.

SELECT column_1 [, column_2, ...]
FROM table
WHERE column IS NOT NULL;

IS NULL

Since version: 1.4 Allows you to get the null values.

SELECT column_1 [, column_2, ...]
FROM table
WHERE column IS NULL;

Date Functions

FORMAT OF THE DATE TYPE: year-month-day hour:minute:second

DATE

Since version: 1.4 Returns the datetime in human readable format (year-month-day hour:minute:second) from Integer64 data type.

DATE(datetime_represented_as_integer64)

Also addition of two datetimes represented as Integer64 would work inside this function like this:

DATE(datetime1_represented_as_integer64 + datetime2_represented_as_integer64)

DAY

Since version: 1.4 Returns the day from the input date, which is in the range from 1 to 31.

DAY(date)

HOUR

Since version: 1.4 Returns the hour from the input date, which is in the range from 1 to 24.

HOUR(date)

MINUTE

Since version: 1.4 Returns the minute from the input date, which is in the range from 1 to 60.

MINUTE(date)

MONTH

Since version: 1.4 Returns the month from the input date, which is in the range from 1 to 12.

MONTH(date)

NOW

Since version: 1.4 Returns the current date and time.

NOW()

SECOND

Since version: 1.4 Returns the seconds from the input date, which is in the range from 1 to 60.

SECOND(date)

String Functions

Syntax consists from:

KEYWORDS required_input [optional input];

CONCAT

Since version: 1.4 Merges two strings together.

CONCAT(string_1, string_2)

LEFT

Since version: 1.4 Extracts specified number of chars from left side from the input string.

LEFT(string, charCount)

LEN

Since version: 1.4 Returns the length of the input string.

LEN(string)

LOWER

Since version: 1.4 Transforms the input string to lower case.

LOWER(string)

LTRIM

Since version: 1.4 Removes leading spaces from the input string.

LTRIM(string)

REVERSE

Since version: 1.4 Reverses the input string.

REVERSE(string)

Since version: 1.4 Extracts the specified number of characters from the right side from the input string.

RIGHT(string, characters_count)

RTRIM

Since version: 1.4 Removes trailing spaces from the input string.

RTRIM(string)

UPPER

Since version: 1.4 Transforms the input string to upper case.

UPPER(string)