Supported functionality related to Data Manipulation Language of SQL.

Syntax is case insensitive following this template:

KEYWORDS required_input [optional_input];

*Since version: 1.4.0*
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;

*Since version: 1.4.0*
Shows loaded databases in memory.

SHOW DATABASES;

*Since version: 1.4.0*
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.0*
Also this syntax is valid:

SHOW TABLES IN my_database;

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

SHOW COLUMN FROM my_table;

*Since version: 1.4.0*
Also this syntax is valid:

SHOW COLUMNS IN my_table;

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

SHOW CONSTRAINTS FROM table_name;

*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;

*Since version: 1.4.0*

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]];

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

*Since version: 1.4.0*
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.

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

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

*Since version: 1.4.0*
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.

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

*Since version: 1.4.0*
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 type of JOIN was designed for Star Schema (data mart schema). That means, it was optimized for joining tables with 1:N realtion. It can be used for joining tables with N:M relation as well, but it was not designed for this purpose, and therefore, the performance is weak.

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

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

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

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

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

*Since version: 1.4.0*
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)));

*Since version: 1.4.0*

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 |

*Since version: 1.4.0*
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.

*Since version: 1.4.0*
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_tableWHERE GEO_CONTAINS(POLYGON((17.0779 48.1303, 17.0912 48.1303, 17 48, 17.0779 48.1303)),geo_point);

*Since version: 1.4.0*
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;

*Since version: 1.4.0*
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;

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

ABS(numeric_expression)

*Since version: 1.4.0*
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)

*Since version: 1.4.0*
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)

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

ATAN(numeric_expression)

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

ATN2(numeric_expression_1, numeric_expression_2)

*Since version: 1.4.0*
Calculates average value of the numeric expression.

AVG(numeric_expression)

*Since version: 1.4.0*
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)

*Since version: 1.4.0*
Counts the number of records.

COUNT(expression)

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

COS(numeric_expression)

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

COT(numeric_expression)

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

EXP(numeric_expression)

*Since version: 1.4.0*
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)

*Since version: 1.4.0*
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])

*Since version: 1.4.0*
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)

*Since version: 1.4.0*
Returns the maximum value.

MAX(numeric_expression)

*Since version: 1.4.0*
Returns the minimum value.

MIN(numeric_expression)

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

PI()

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

POW(base, exponent)

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

ROUND(numeric_expression, decimal)

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

ROOT(numeric_expression, n)

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

SIGN(numeric_expression)

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

SIN(numeric_expression)

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

SQRT(numeric_expression)

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

SQUARE(numeric_expression)

*Since version: 1.4.0*
Calculates the sum of the input numeric expression.

SUM(numeric_expression)

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

TAN(numeric_expression)

*Since version: 1.4.0*
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 → Bool (any string is casted to false except for string "true" - case insensitive), Double, Float, Geo_Point, Int, Long, String

Geo_Point → String

Geo_Polygon → String

**One more casting combinations will be added in the near future (string to geo_polygon).**

CAST(some_value AS data_type)

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

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

*Since version: 1.4.0*
Allows you to get the null values.

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

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

*Since version: 1.4.0*
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)

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

DAY(date)

*Since version: 1.5.0
*Returns integer 1-7 according to daytime (NOTE: returned values are different from function WEEKDAY).

Day | Value |

Monday | 2 |

Tuesday | 3 |

Wednesday | 4 |

Thursday | 5 |

Friday | 6 |

Saturday | 7 |

Sunday | 1 |

It works with both long or string, e.g.:

SELECT DAYOFWEEK(4564545);SELECT DAYOFWEEK("2020-04-26");

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

HOUR(date)

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

MINUTE(date)

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

MONTH(date)

*Since version: 1.4.0*
Returns the current date and time.

NOW()

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

SECOND(date)

*Since version: 1.5.0
*Returns integer 0-6 according to daytime (NOTE: returned values are different from function DAYOFWEEK).

Day | Value |

Monday | 0 |

Tuesday | 1 |

Wednesday | 2 |

Thursday | 3 |

Friday | 4 |

Saturday | 5 |

Sunday | 6 |

It works with both long or string, e.g.:

SELECT WEEKDAY(4564545);SELECT WEEKDAY("2020-04-26");

Syntax consists from:

KEYWORDS required_input [optional input];

*Since version: 1.4.0*
Merges two strings together.

CONCAT(string_1, string_2)

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

LEFT(string, charCount)

*Since version: 1.4.0*
Returns the length of the input string.

LEN(string)

*Since version: 1.4.0*
Transforms the input string to lower case.

LOWER(string)

*Since version: 1.4.0*
Removes leading spaces from the input string.

LTRIM(string)

*Since version: 1.4.0*
Reverses the input string.

REVERSE(string)

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

RIGHT(string, characters_count)

*Since version: 1.4.0*
Removes trailing spaces from the input string.

RTRIM(string)

*Since version: 1.4.0*
Transforms the input string to upper case.

UPPER(string)