GridDB Advanced Edition SQL reference

Revision: 4.3.2-9

1 SQL description format

This chapter shows the descriptive format of the SQL that can be used in the NewSQL interface.

1.1 Usable operations

Besides the SELECT command, DDL command (Data Definition Language) such as CREATE TABLE, and INSERT/DELETE are also supported. See SQL commands supported by GridDB AE for details.

　

1.2 Data types

1.2.1 Data types used in data storage

The data types used for data storage in the NewSQL interface are shown in Table 1. These data type names can be specified as a column data type when creating a table.

Data types Description
BOOL True/False
BYTE Integer value from -27 to 27-1 (8 bit)
SHORT Integer value from -215 to 215-1 (16 bit)
INTEGER Integer value from -231 to 231-1 (32 bit)
LONG Integer value from -263 to 263-1 (64 bit)
FLOAT Single-precision data type (32 bits), floating-point number defined in IEEE754
DOUBLE Double-precision data type (64 bits), floating-point number defined in IEEE754
TIMESTAMP Data type expressing the date and time.
STRING Text that is composed of an arbitrary number of characters using the unicode code point.
BLOB Data type for binary data such as images and voice, etc.
Large objects to be saved directly in the input format.
The character x or X can also be added to create a hexadecimal expression such as X'23AB'.

A NULL value can be registered to table. The results of operators that is related to NULL value such as "IS NULL" are SQL-compliant.

1.2.2 Expression that can be specified as a column data type when creating a table

In the NewSQL interface, for data type names that are described as column data types when the table was created, even if the name does not match the data type name given in Data types used in data storage, follow the rules to interpret and determine the data type to use for data storage.

Check the following rules in sequence starting from the top and determine the data type to use for data storage based on the applicable rule. The data type name described when checking the rules and the strings to check using the rules are not case sensitive. If multiple rules apply, the rule ranked higher will be prioritized. If no rules are applicable, an error will occur and table creation will fail.

Rule no. Data type names, that were described as column data types when the table was created Column type of the table to be created
1 Type names listed in Data types used in data storage Same as specified type
2 REAL DOUBLE
3 TINYINT BYTE
4 SMALLINT SHORT
5 BIGINT LONG
6 Type name including "INT" INTEGER
7 Type name including any of "CHAR", "CLOB", "TEXT" STRING
8 Type name including "BLOB" BLOB
9 Type name including any of "REAL", "DOUB" DOUBLE
10 Type name including "FLOA" FLOAT

An example to determine the data type using this rule is shown.

Describe the data type as follows in the NewSQL interface when using the data type equivalent to the one used in the clients of the NoSQL interface. Except for some data types which cannot be used since the equivalent type do not exist.

Data type in NoSQL interface in client Equivalent column data type descriptions in NewSQL interface
STRING (string data type) STRING or "Expression to be STRING"
BOOL (Boolean) BOOL
BYTE (8-bit integer) BYTE or "Expression to be BYTE"
SHORT (16-bit integer) SHORT or "Expression to be SHORT"
INTEGER (32-bit integer) INTEGER or "Expression to be INTEGER"
LONG (64-bit integer) LONG or "Expression to be LONG"
FLOAT (32 bitwise floating point number) FLOAT or "Expression to be FLOAT"
DOUBLE (64 bitwise floating point number) DOUBLE or "Expression to be DOUBLE"
TIMESTAMP (time data type) TIMESTAMP
GEOMETRY (spatial data type) Cannot be specified as a data type of the column when creating a table
BLOB BLOB or "Expression to be BLOB"
ARRAY Cannot be specified as a data type of the column when creating a table

　

1.2.3 Data type when accessing a container as a table and the treatment of the values

The container created with the NoSQL interface client is handled as follows using the container's column type and value when accessing it with the NewSQL interface:

Column type of container Data type mapped in NewSQL Value
STRING STRING Same as original value
BOOL BOOL Same as original value
BYTE BYTE Same as original value
SHORT SHORT Same as original value
INTEGER INTEGER Same as original value
LONG LONG Same as original value
FLOAT FLOAT Same as original value
DOUBLE DOUBLE Same as original value
TIMESTAMP TIMESTAMP Same as original value
GEOMETRY Same data type as NULL constant (Types.UNKNOWN) All the values are NULL
BLOB BLOB Same as original value
ARRAY Same data type as NULL constant (Types.UNKNOWN) All the values are NULL

1.2.4 Treatment of the data type not supported by SQL

The data types which are supported by the NoSQL interface, but not by the NewSQL interface are as follows.

This section explains how to handle the data of these data types when accessed using the NewSQL interface.

　

1.3 User and database

There are 2 types of GridDB user, an administrator user and a general user, which differ in terms of the functions which can be used. In addition, access can be separated on a user basis by creating a database. See "GridDB Features Reference" (GridDB_FeaturesReference.html) for the details of users and a database.

1.4 Naming rules

The naming rules are as follows:

See "GridDB Features Reference" (GridDB_FeaturesReference.html) for the details about the node affinity function, and the rules and the restrictions of naming.

[Notice]

2 SQL commands supported by GridDB AE

Supported SQL commands are in the table as follows.

Command Overview
CREATE DATABASE Create a database.
CREATE TABLE Create a table.
CREATE INDEX Create an index.
CREATE VIEW Create a view.
CREATE USER Create a general user.
DROP DATABASE Delete a database.
DROP TABLE Delete a table.
DROP INDEX Delete an index.
DROP VIEW Delete a view.
DROP USER Delete a general user.
ALTER TABLE Change the structure of a table.
GRANT Assign database access rights to a general user.
REVOKE Revoke database access rights from a general user.
SET PASSWORD Change the password of a general user.
SELECT Select data.
INSERT Insert rows into a table.
DELETE Delete rows from a table.
UPDATE Update rows in a table.
Comment Add a comment.
Hints Control an execution plan.

An explanation for each category of SQL command is given in this chapter.

2.1 Data definition language (DDL)

2.1.1 CREATE DATABASE

Create a database.

Syntax

CREATE DATABASE database_name;

Specifications

2.1.2 CREATE TABLE

2.1.2.1 Creating a table

Create a table.

Syntax

Specifications

Examples

2.1.2.2 Creating a partitioned table

Creating a partitioned table

See "GridDB Features Reference" (GridDB_FeaturesReference.html) for details of each partitioning function.

(1) Creating a hash partitioned table

Syntax

Specifications

Examples

(2) Creating an interval partitioned table

Syntax

Specifications

Examples

(3) Creating an interval hash partitioned table

Syntax

Specifications

Examples

2.1.3 CREATE INDEX

Create an index.

Syntax

CREATE INDEX [IF NOT EXISTS] index_name ON table_name ( column_name_to_be_indexed );

Specifications

2.1.4 CREATE VIEW

Create a view.

Syntax

CREATE [FORCE] VIEW view_name AS SELECT statement;

Specifications

2.1.5 CREATE USER

Create a general user.

Syntax

CREATE USER user_name IDENTIFIED BY 'password_string' ;

Specifications

2.1.6 DROP DATABASE

Delete a database.

Syntax

DROP DATABASE database_name;

Specifications

2.1.7 DROP TABLE

Delete a table.

Syntax

DROP TABLE [IF EXISTS] table_name;

Specifications

2.1.8 DROP INDEX

Delete the specified index.

Syntax

DROP INDEX [IF EXISTS] index_name ON table_name;

Specifications

2.1.9 DROP VIEW

Delete a view.

Syntax

DROP VIEW [IF EXISTS] view name ;

Specifications

2.1.10 DROP USER

Delete a general user.

Syntax

DROP USER user_name;

Specifications

2.1.11 ALTER TABLE

Change the structure of a table.

2.1.11.1 Adding columns to a table

Add columns to the end of the table.

Syntax

ALTER TABLE table_name ADD [COLUMN] column definition [,ADD [COLUMN] column definition ...];

Specifications

Examples

2.1.11.2 Deleting data partitions

Delete data partitions created by table partitioning.

Syntax

ALTER TABLE table_name DROP PARTITION FOR ( value_included_in_the_data_partition );

Specifications

See Metatables for the details on the metatable.

Examples

Interval partitioned table

Interval hash partitioned table

　

2.2 Data control language (DCL)

2.2.1 GRANT

Assign database access rights to a general user.

Syntax

GRANT {SELECT|ALL} ON database_name TO user_name;

Specifications

2.2.2 REVOKE

Revoke database access rights from a general user.

Syntax

REVOKE {SELECT|ALL} ON database_name FROM user_name;

Specifications

2.2.3 SET PASSWORD

Change the password of a general user.

Syntax

SET PASSWORD [FOR user_name ] = 'password_string';

Specifications

　　

2.3 Data management language (DML)

2.3.1 SELECT

Select data. Made up of a variety of Clauses such as FROM, WHERE, etc.

Syntax

SELECT [{ALL|DISTINCT}] * | column_name_1 [, column_name_2 ...]
[FROM clause]
[WHERE clause]
[GROUP BY clause [HAVING clause]]
[{UNION [ALL] |INTERSECT|EXCEPT} SELECT statement]
[ORDER BY clause]
[LIMIT clause [OFFSET clause]] ;

2.3.2 INSERT

Register rows in a table. INSERT only registers rows, while INSERT OR REPLACE and REPLACE overwrite the existing data, when the data with the same primary key as that of the existing data is given. REPLACE is an alias of INSERT OR REPLACE and they are the same in their functions.

Syntax

{INSERT|INSERT OR REPLACE|REPLACE} INTO table_name
{VALUES ( { number_1 | string_1 } [, { number_2 | string_2 } ...] )|SELECT statement} ;

Specifications

INSERT INTO myTable1 VALUES(1, 100);

REPLACE INTO myTable1 VALUES(1, 200);

INSERT INTO myTable1 SELECT * FROM myTable2;

2.3.3 DELETE

Delete rows from a table.

Syntax

DELETE FROM table_name [ WHERE clause ];

2.3.4 UPDATE

Update the rows existing in a table.

Syntax

UPDATE table_name SET column_name_1 = expression_1 [, column_name_2 = expression_2 ...] [ WHERE clause ];

Specifications

2.4 Clauses

2.4.1 FROM

Specify the table name, view name, and subquery on which to execute data operations.

Syntax

FROM table_name_1 [, table_name_2 ... ]
FROM (sub_query) [AS] Alias [, ...]

Specifications

Example:

SELECT a.ID, b.ID FROM mytable a, (SELECT ID FROM mytable2) b;

ID     ID
---+-----
 1    100
 1    200
 2    100
 2    200
   :

2.4.2 GROUP BY

Among the results of the clauses specified earlier, rows having the same value in the specified column will be grouped together.

Syntax

GROUP BY column_name_1 [, column_name_2 ...]

2.4.3 HAVING

Perform filtering using the search condition on data grouped by the GROUP BY clause. GROUP BY clause cannot be omitted.

Syntax

HAVING search_conditions

2.4.4 ORDER BY

Sort search results.

ORDER BY column_name_1 [{ASC|DESC}] [, column_name_2 [{ASC|DESC}] ...]

2.4.5 WHERE

Apply a search condition on the result of the preceding FROM clause.

Syntax

WHERE search_conditions

Specifications

2.4.6 LIMIT/OFFSET

Extract the specified number of data from the specified location.

Syntax

LIMIT value_1 [OFFSET value_2 ]

Specifications

　

2.4.7 JOIN

Join a table.

Syntax

Type of join Syntax
Inner join Table 1 [INNER] JOIN table 2 [ON condition | USING(Column name [, column name ...])]
Left outer join Table 1 LEFT [OUTER] JOIN table 2 [ON type | USING (column name [, column name ...])]
Cross join Table 1 CROSS JOIN table 2 [ON condition | USING (column name [, column name ...])]

Specify join conditions with ON or USING.

Example:

name: employees

 id   first_name   department_id
----+------------+----------------
  0   John         0
  1   William      1
  2   Richard      0
  3   Mary         4
  4   Lisa         3
  5   James        1

name: departments

 id   department   
----+------------
  0   Sales
  1   Development
  2   Research
  3   Marketing

○Inner join
SELECT * FROM employees e INNER JOIN departments d ON e.department_id=d.department_id;

 id    first_name  department_id  department_id  department
------+-----------+--------------+--------------+-----------
  0    John         0              0             Sales
  1    William      1              1             Development
  2    Richard      0              0             Sales
  4    Lisa         3              3             Marketing
  5    James        1              1             Development


○Left outer join
SELECT * FROM employees e LEFT JOIN departments d  ON e.department_id=d.department_id;

 id    first_name  department_id  department_id  department
------+-----------+--------------+--------------+-----------
  0    John         0              0             Sales
  1    William      1              1             Development
  2    Richard      0              0             Sales
  3    Mary         4              (NULL)        (NULL)
  4    Lisa         3              3             Marketing
  5    James        1              1             Development

　

Natural join (NATURAL JOIN) joins tables that have matching values in the rows under the same name.

Type of join Syntax
Inner join Table 1 NATURAL [INNER] JOIN table 2
Left outer join Table 1 NATURAL LEFT [OUTER] JOIN table 2
Cross join Table 1 NATURAL CROSS JOIN table 2 
SELECT * FROM employees NATURAL INNER JOIN departments;

 department_id   id    first_name     department
---------------+-----+--------------+--------------
  0              0     John           Sales
  1              1     William        Development
  0              2     Richard        Sales
  3              4     Lisa           Marketing
  1              5     James          Development

2.4.8 UNION/INTERSECT/EXCEPT

Calculate on a set of two query results.

Syntax

Inquiry 1 UNION inquiry 2 Returns all the results of two queries. (duplication is not included)
Query 1 UNION ALL query 2 Returns all the results of two queries. (duplication is included)
Query 1 INTERSECT query 2 Returns the results common to the results of two queries.
Query 1 EXCEPT query 2 Returns the difference of two queries (result included in the query 1, not in the query 2).

2.4.9 OVER

Split and sort query results. Use with a WINDOW function.

Syntax

Function OVER ([PARTITION BY expression 1] [ORDER BY expression 2])

Specifications

2.5 Operator

This section explains the operators used in SQL statements.

2.5.1 List of Operators

The list of operators is as follows.

Class Operator Description
Arithmetic + Add
- Subtract
* Multiply
/ Divide
% Modulo
Character || Connect the value of arbitrary types as a character string.
Compare =, == Compare whether both sides are equal.
!=, <> Compare whether both sides are not equal.
> Compare whether the left side is larger than the right side.
>= Compare whether the left side is larger than or equal to the right one.
< Compare whether the left side is smaller than the right side.
<= Compare whether the left side is smaller than or equal to the right side.
IS Compare whether both sides are equal.
Return true, when both sides are NULLs.
Return false, when either side is NULL.
IS NOT Compare whether both sides are not equal.
Return false, when both sides are NULLs.
Return true, when either side is NULL.
ISNULL Determine whether the left side is NULL.
NOTNULL Determine whether the left side is not NULL.
LIKE Search the character string on the right.
GLOB Search the character string on the right.
BETWEEN Extract values of the specified range.
IN Return whether the specified value is included in the set of values.
Bit & A & B : Bitwise AND of A and B
| A | B : Bitwise OR of A and B
~ ~A : Bitwise NOT of A
<< A << B : Shift A to the left by B bit.
>> A >> B : Shift A to the right by B bit.
Logic AND Return true, when both sides are true.
Return false, when either side is false.
Otherwise return NULL.
OR Return true, when the expression on either side is true.
Return false, when the expressions on both sides are false.
Otherwise return NULL.
NOT Return false, when the expression on the right is true.
Return true, when the expression on the right is false.
Otherwise return NULL.

2.5.2 LIKE

Search the character string on the right.

Syntax

str [NOT] LIKE pattern_str [ESCAPE escape_str ]

Specifications

2.5.3 GLOB

Syntax

Search the character string on the right.

str GLOB pattern_str

Specifications

2.5.4 BETWEEN

Extract values of the specified range.

Syntax

expression_1 [NOT] BETWEEN expression_2 AND expression_3

Specifications

2.5.5 IN

Return whether the specified value is included in the set of values.

Syntax

expression_1 [NOT] IN ( expression_2 [, expression_3 ...] )

Specifications

2.6 Functions

This section explains the functions used in SQL statements.

2.6.1 List of Functions

The following functions are available for SQL statements.

Class Function name Description
Aggregation AVG Return the average value.
COUNT Return the number of rows.
MAX Return the maximum.
MIN Return the minimum.
SUM Return a sum of values.
TOTAL Return a sum of values.
GROUP_CONCAT Connect values.
STDDEV_SAMP Returns the sample standard deviation
STDDEV Returns the sample standard deviation
STDDEV0 Returns the sample standard deviation
STDDEV_POP Returns the population standard deviation
VAR_SAMP Returns the sample variance
VARIANCE Returns the sample variance
VARIANCE0 Returns the sample variance
VAR_POP Returns the population variance
MEDIAN Returns the median
Arithmetic ABS Return an absolute value.
ROUND Round off.
RANDOM Return a random number.
MAX Return the maximum.
MIN Return the minimum.
LOG Return the logarithm.
SQRT Return the square root.
TRUNC Round down numbers.
HEX_TO_DEC Converts a hexadecimal string to a decimal number
Character LENGTH Return the length of a character string.
LOWER Convert a character string to a lowercase.
UPPER Convert a character string to an uppercase.
SUBSTR Cut out part of a character string.
REPLACE Replace a character string.
INSTR Return the position of a specified character string in a character string.
LIKE Search a character string.
GLOB Search a character string.
TRIM Remove a specified character(s) from the both ends of a character string.
LTRIM Remove a specified character(s) from the left end of a character string.
RTRIM Remove a specified character(s) from the right end of a character string.
QUOTE Enclose a character string with single quotes.
UNICODE Return the Unicode code point of a character.
CHAR A Unicode code point is converted to characters and connected.
PRINTF Return the converted character string
TRANSLATE Replace a character string.
Time NOW Return the present time.
TIMESTAMP Convert the string representation of time to TIMESTAMP type.
TIMESTAMP_ADD Add a duration to a time.
TIMESTAMP_DIFF Return the difference of times.
TO_TIMESTAMP_MS Add lapsed time to the time point '1970-01-01T00:00:00.000Z'.
TO_EPOCH_MS Return the lapsed time from the time point '1970-01-01T00:00:00.000Z'.
EXTRACT Take out the value of the specific field from time.
STRFTIME Return a character string with the time converted.
MAKE_TIMESTAMP Generate time.
TIMESTAMP_TRUNC Truncate time.
WINDOW ROW_NUMBER Assign a unique sequential value to the resulting Row
Other COALESCE Return the first argument that is not NULL.
IFNULL Return the first argument that is not NULL.
NULLIF Return NULL when two arguments are the same, return the first argument when the arguments are different.
RANDOMBLOB Return a BLOB type value (random number).
ZEROBLOB Return a BLOB type value (0x00).
HEX Convert a BLOB type value to a hexadecimal type.
TYPEOF Return the data type of a value.

These functions are described using the data in the following table as an example.

table: employees

 id   first_name   last_name   age     department    enrollment_period
----+------------+-----------+-------+-------------+-------------------
  0   John         Smith       43      Sales         15.5
  1   William      Jones       59      Development   23.2
  2   Richard      Brown       (NULL)  Sales          7.0
  3   Mary         Taylor      31      Research      (NULL)
  4   Lisa         (NULL)      29      (NULL)         4.9
  5   James        Smith       43      Development   10.3

table: departments

 id   department   
----+------------
  0   Sales
  1   Development
  2   Research

[Notice]

2.6.2 Aggregate functions

Functions to aggregate values DISTINCT or ALL can be specified as the argument of an aggregate function.

Format function( [DISTINCT | ALL] argument)
Point Meaning
DISTINCT Rows of duplicate values are excluded and aggregated
ALL All the rows including the duplicate values are aggregated.

When no argument is specified, the resut will be the same as ALL is specified.

[Notice]

2.6.2.1 AVG

Format AVG( [DISTINCT | ALL] n)

Return the average value of n.

Example:

SELECT AVG(age) FROM employees;
Result: 41.0

SELECT AVG(DISTINCT age) FROM employees;
Result: 40.5

SELECT department, AVG(age) avg FROM employees GROUP BY department;
Result: 
  department   avg
  ------------+-----
  Development  51.0
  Research     31.0
  Sales        43.0
  (NULL)       29.0

2.6.2.2 COUNT

Format COUNT(* | [DISTINCT | ALL] x)

Return the number of rows.

Example:

SELECT COUNT(*) FROM employees;
Result: 6

// Count the rows ignoring the ones with NULL value.
SELECT COUNT(department) FROM employees;
Result: 5

SELECT COUNT(DISTINCT department) FROM employees;
Result: 3

2.6.2.3 MAX

Format MAX( [DISTINCT | ALL] x)

Return the maximum.

Example:

SELECT MAX(age) FROM employees;
Result: 59

SELECT MAX(first_name) FROM employees;
Result: William

2.6.2.4 MIN

Format MIN( [DISTINCT | ALL] x)

Return the minimum.

Example:

SELECT MIN(age) FROM employees;
Result: 29

SELECT MIN(first_name) FROM employees;
Result: James

2.6.2.5 SUM/TOTAL

Format SUM( [DISTINCT | ALL] n)
Format TOTAL( [DISTINCT | ALL] n)

Return a sum of values.

Example:

SELECT SUM(age) FROM employees;
Result: 205

SELECT TOTAL(age) FROM employees;
Result: 205.0

SELECT department, SUM(age) sum FROM employees GROUP BY department;
Result: 
  department   sum
  ------------+-----
  Development  102
  Research      31
  Sales         43
  (NULL)        29

2.6.2.6 GROUP_CONCAT

Format GROUP_CONCAT( [DISTINCT | ALL] x [, separator] )

Return the character string in which the values of x are concatenated. Specify the separator to be concatenated as "separator". When not specified, ", " is used.

Example:

// Concatenate the name last_name with '/'
SELECT GROUP_CONCAT(last_name, '/') from employees;
Result:  Smith/Jones/Brown/Taylor/Smith

// Concatenate the name "first_name" for each department "department"
SELECT department, GROUP_CONCAT(first_name) group_concat from employees GROUP BY(department);
Result: 
   department    group_concat
  -------------+--------------
   Development  William,James
   Research     Mary
   Sales        John,Richard
   (NULL)       Lisa

SELECT GROUP_CONCAT(age, ' + ') FROM employees;
Result: 43 + 59 + 31 + 29 + 43

2.6.2.7 STDDEV_SAMP

Format STDDEV_SAMP( [DISTINCT | ALL] x)

Returns the sample standard deviation.

Example:

SELECT department, STDDEV_SAMP(enrollment_period) enrollment_period_stddev from employees GROUP BY department;
Result:
   department    enrollment_period_stddev
  -------------+--------------------------
   Development  9.121677477306465
   Research     (NULL)
   Sales        6.010407640085654
   (NULL)       (NULL)

2.6.2.8 STDDEV/STDDEV0

Format STDDEV( [DISTINCT | ALL] x)
Format STDDEV0( [DISTINCT | ALL] x)

Returns the sample standard deviation. STDDEV is an alias of the STDDEV_SAMP function.

Example:

SELECT department, STDDEV(enrollment_period) enrollment_period_stddev from employees GROUP BY department;
Result:
   department    enrollment_period_stddev
  -------------+--------------------------
   Development  9.121677477306465
   Research     (NULL)
   Sales        6.010407640085654
   (NULL)       (NULL)

SELECT department, STDDEV0(enrollment_period) enrollment_period_stddev from employees GROUP BY department;
Result:
   department    enrollment_period_stddev
  -------------+--------------------------
   Development  9.121677477306465
   Research     (NULL)
   Sales        6.010407640085654
   (NULL)       0.0

SELECT STDDEV(enrollment_period) enrollment_period_stddev from employees WHERE age >= 55;
Result:
   enrollment_period_stddev
  --------------------------
   (NULL)

SELECT STDDEV0(enrollment_period) enrollment_period_stddev from employees WHERE age >= 55;
Result:
   enrollment_period_stddev
  --------------------------
   0.0

2.6.2.9 STDDEV_POP

Format STDDEV_POP( [DISTINCT | ALL] x)

Returns the population standard deviation.

Example:

SELECT department, STDDEV_POP(enrollment_period) enrollment_period_stddev from employees GROUP BY department;
Result:
   department    enrollment_period_stddev
  -------------+--------------------------
   Development  6.450000000000002
   Research     (NULL)
   Sales        4.25
   (NULL)       0.0

2.6.2.10 VAR_SAMP

Format VAR_SAMP( [DISTINCT | ALL] x)

Returns the sample variance.

Example:

SELECT department, VAR_SAMP(enrollment_period) enrollment_period_variance from employees GROUP BY department;
Result:
   department    enrollment_period_variance
  -------------+----------------------------
   Development  83.20500000000004
   Research     (NULL)
   Sales        36.125
   (NULL)       (NULL)

2.6.2.11 VARIANCE/VARIANCE0

Format VARIANCE( [DISTINCT | ALL] x)
Format VARIANCE0( [DISTINCT | ALL] x)

Returns the sample variance. VARIANCE is an alias of the VAR_SAMP function.

Example:

SELECT department, VARIANCE(enrollment_period) enrollment_period_variance from employees GROUP BY department;
Result:
   department    enrollment_period_variance
  -------------+----------------------------
   Development  83.20500000000004
   Research     (NULL)
   Sales        36.125
   (NULL)       (NULL)

SELECT department, VARIANCE0(enrollment_period) enrollment_period_variance from employees GROUP BY department;
Result:
   department    enrollment_period_variance
  -------------+----------------------------
   Development  83.20500000000004
   Research     (NULL)
   Sales        36.125
   (NULL)       0.0

SELECT VARIANCE(enrollment_period) enrollment_period_variance from employees WHERE age >= 55;
Result:
   enrollment_period_variance
  ----------------------------
   (NULL)

SELECT VARIANCE0(enrollment_period) enrollment_period_variance from employees WHERE age >= 55;
Result:
   enrollment_period_variance
  ----------------------------
   0.0

2.6.2.12 VAR_POP

Format VAR_POP( [DISTINCT | ALL] x)

Returns the population variance.

Example:

SELECT department, VAR_POP(enrollment_period) enrollment_period_variance from employees GROUP BY department;
Result:
   department    enrollment_period_variance
  -------------+----------------------------
   Development  41.60250000000002
   Research     (NULL)
   Sales        18.0625
   (NULL)       0.0

2.6.2.13 MEDIAN

Format MEDIAN(n)

Returns the median of n. If the number of rows to be calculated is even, returns the average value of the two rows near the center.

Example:

SELECT MEDIAN(age) FROM employees;
Result: 43

SELECT department, MEDIAN(age) mn FROM employees GROUP BY department ORDER BY mn DESC;
Result:
  department   mn
  ------------+-----
  Development  51
  Sales        43
  Research     31
  (NULL)       29

2.6.3 Mathematical functions

2.6.3.1 ABS

Format ABS(n)

Return the absolute value of n. For a positive number, the value as it is is returned and for a negative number, the value multiplied by -1 is returned.

Example:

SELECT first_name, ABS(age) abs FROM employees;
Result：
  first_name    abs
  ------------+-------
  John          43
  William       59
  Richard       (NULL)
  Mary          31
  Lisa          29
  James         43

2.6.3.2 ROUND

Format ROUND(n [, m])

Round off. Returns the value of n rounded to m decimal places.

Example:

SELECT first_name, ROUND(enrollment_period, 0) round FROM employees;
Result：
  first_name    round
  ------------+-------
  John          16.0
  William       23.0
  Richard        7.0
  Mary          (NULL)
  Lisa           5.0
  James         10.0

2.6.3.3 RANDOM

Format RANDOM()

Return a random number. A random number is an integer of the range from -263 to 263-1.

Example:

SELECT first_name, RANDOM() random FROM employees;
Result：
  first_name    random
  ------------+----------------------
  John          -3382931580741820003
  William       -7362300487836647182
  Richard        8834368641333737477
  Mary          -8544493602797564288
  Lisa          -7727163797274657674
  James          6751560427268247384

2.6.3.4 MAX/MIN

Format MAX(x1, x2 [,...])

Return the greatest value among the values xN.

Format MIN(x1, x2 [,...])

Return the smallest value among the values xN.

Example:

SELECT first_name, age, enrollment_period, MAX(age, enrollment_period) max FROM employees;
Result：
  first_name    age    enrollment_period   max
  ------------+-------+------------------+--------
  John          43      15.5               43.0
  William       59      23.2               59.0
  Richard       (NULL)   7.0               (NULL)
  Mary          31      (NULL)             (NULL)
  Lisa          29       4.9               29.0
  James         43      10.3               43.0

2.6.3.5 LOG

Format LOG(n, m)

Returns the logarithm of m with base n.

Example:

SELECT LOG(2, 8);
Result: 3.0

SELECT LOG(0.5, 2.0);
Result: -1.0

2.6.3.6 SQRT

Format SQRT(n)

Returns the positive square root of n.

Example:

SELECT SQRT(4);
Result：2.0

SELECT SQRT(16.0);
Result：4.0

2.6.3.7 TRUNC

Format TRUNC(n [,m])

In the case of m>=0, return the value of n, rounded down to the nearest m digits.

In the case of m< 0, return the value of n, rounded down to the nearest -m digits.

Example:

SELECT TRUNC(123.4567);
Result: 123.0

SELECT TRUNC(123.4567, 2);
Result: 123.45

SELECT TRUNC(123.4567, -1);
Result: 120.0

SELECT TRUNC(123.4567, -3);
Result: 0.0

SELECT TRUNC(1234567, -2);
Result: 1234500

2.6.3.8 HEX_TO_DEC

Format HEX_TO_DEC(str)

Converts hexadecimal string str to decimal number type.

Example:

SELECT HEX_TO_DEC('FF');
Result: 255

SELECT HEX_TO_DEC('10');
Result: 16

2.6.4 Character functions

2.6.4.1 LENGTH

Format LENGTH(str)

Return the length of the character string str.

Example:

SELECT last_name, LENGTH(last_name) length FROM employees;
Result：
  last_name     length
  ------------+----------------------
  Smith         5
  Jones         5
  Brown         5
  Taylor        6
  (NULL)        (NULL)
  Smith         5

2.6.4.2 LOWER

Format LOWER(str)

Convert all the alphabet of the character string str to lowercases.

Example:

SELECT last_name, LOWER(last_name) lower FROM employees;
Result：
  last_name     lower
  ------------+----------------------
  Smith         smith
  Jones         jones
  Brown         brown
  Taylor        taylor
  (NULL)        (NULL)
  Smith         smith

2.6.4.3 UPPER

Format UPPER(str)

Convert all the alphabet of the character string str to uppercases.

Example:

SELECT last_name, UPPER(last_name) upper FROM employees;
Result：
  last_name     upper
  ------------+----------------------
  Smith         SMITH
  Jones         JONES
  Brown         BROWN
  Taylor        TAYLOR
  (NULL)        (NULL)
  Smith         SMITH

2.6.4.4 SUBSTR

Format SUBSTR(str, index [, length])

Cut out a part of a character string. from the character on the starting position, indicated by "index" up to the length specified by "length".

Example:

SELECT SUBSTR('abcdefg', 3);
Result：cdefg

SELECT SUBSTR('abcdefg', 3, 2);
Result：cd

2.6.4.5 REPLACE

Format REPLACE(str, search_str, replacement_str)

Replace a character string. In the character string str, replace all the parts matching the character string search_str with replacement_str.

Example:

SELECT REPLACE('abcdefabc', 'abc', '123');
Result：123def123

2.6.4.6 INSTR

Format INSTR(str, search_str [, offset] [, occurrence])

Search for character string search_str in the character string str, and return its starting position. Return 0, when not found. Return 0, when not found.

Example:

SELECT INSTR('abcdef', 'cd');
Result：3

SELECT INSTR('abcdef', 'gh');
Result：0

SELECT INSTR('abcabcabcde', 'ab', 2, 2);
Result: 7

SELECT INSTR('abcabcabcde', 'ab', -1, 2);
Result: 4

2.6.4.7 LIKE

Format LIKE(pattern_str, str [, escape_str])

Search the character string on the right. Return true, when the character string str matches the match pattern pattern_str. Return false, when no match was found. The following two wild cards are available for a match pattern.

Wild card Meaning
_ Any one character
% Any character with zero or more character strings

Specify the escape character escape_str when searching for the character _ or % in str containing the wildcard character _ or %. If a escape character is specified before the wild card character, it will no longer be interpreted as a wild card.

Example:

SELECT last_name, LIKE('%mi%', last_name) like_name FROM employees;
Result：
  last_name     like_name
  ------------+----------------------
  Smith         true
  Jones         false
  Brown         false
  Taylor        false
  (NULL)        (NULL)
  Smith         true


SELECT LIKE('%C%E%',  'ABC%DEF');
Result：true

SELECT LIKE('%C@%E%', 'ABC%DEF', '@');
Result：false

SELECT LIKE('%C@%D%', 'ABC%DEF', '@');
Result：true

2.6.4.8 GLOB

Format GLOB(pattern_str, str)

Search the character string on the right. Return true, when the character string str matches the match pattern pattern_str. Return false, when no match was found. The following wild cards are available for a match pattern.

Wild card Meaning
? Any one character
* Any character with zero or more character strings
[abc] Match any of the letters a, b or c
[a-e] Match any of the letters from a to e

Example:

SELECT GLOB('*[BA]AB?D', 'AABCD');
Result：true

2.6.4.9 TRIM

Format TRIM(str [, trim_str])

Delete all the characters of character string trim_str from both ends of the character string str.

Example:

SELECT TRIM(' ABC ');
Result: ABC (no space at both ends)

SELECT TRIM('ABCAA', 'BA');
Result: C

2.6.4.10 LTRIM

Format LTRIM(str [, trim_str])

Delete all the characters of character string trim_str from the left end of the character string str.

Example:

SELECT TRIM(' ABC ');
Result: ABC (no space at the left end)

SELECT TRIM('ABCAA', 'BA');
Result: BCAA

2.6.4.11 RTRIM

Format RTRIM(str [, trim_str])

Delete all the characters of character string trim_str from the right end of the character string str.

Example:

SELECT RTRIM(' ABC ');
Result: ABC (no space at the right end)

SELECT RTRIM('ABCAA', 'A');
Result: ABC

2.6.4.12 QUOTE

Format QUOTE(x)

Returns a character string containing the value of x enclosed in single quotes.

Example:

SELECT QUOTE(last_name) last_name, QUOTE(age) age FROM employees;
Result:
  last_name     age
  ------------+-------
  'Smith'       43
  'Jones'       59
  'Brown'       (NULL)
  'Taylor'      31
  (NULL)        29
  'Smith'       43

SELECT QUOTE(RANDOMBLOB(4));
Result: X'A45EA28D'

// The value of column "value" is a character string "Today's news."
SELECT value, QUOTE(value) FROM testcontainer;
Result:
   value            QUOTE(value)
  ---------------+-------------------
   Today's news     'Today''s news'

2.6.4.13 UNICODE

Format UNICODE(str)

Returns the UNICODE code point of the first character of the string str.

Example:

SELECT last_name, UNICODE(last_name) unicode FROM employees;
Result:
  last_name     unicode
  ------------+----------------------
  Smith         83
  Jones         74
  Brown         66
  Taylor        84
  (NULL)        (NULL)
  Smith         83

2.6.4.14 CHAR

Format CHAR(x1 [, x2, ... , xn])

Returns a concatenated character string of characters with Unicode code point value xn.

Example:

SELECT CHAR(83, 84, 85);
Result: STU

2.6.4.15 PRINTF

Format PRINTF(format [, x1, x2, ..., xn])

Return the converted character string according to the specified format "format". A format equivalent to the printf function of the standard C libraries can be used. There are two other formats as below.

Format Description
%q A single quote in a character string is escaped to two single quotes ''.
%Q A single quote in a character string is escaped to two single quotes ''.
Enclose both ends of the character string by single quotes.

Example:

SELECT enrollment_period, PRINTF('%.2f', enrollment_period) printf FROM employees;
Result:
  enrollment_period   printf
  ------------------+-----------
  15.5                15.50
  23.2                23.20
   7.0                 7.00
  (NULL)               0.00
   4.9                 4.90
  10.3                10.30

2.6.4.16 TRANSLATE

Format TRANSLATE(str, search_str, replacement_str)

Replace a character string. Among the character string str, the characters matched the character string search_str is replaced by the characters of character string replacement_str in the same position as search_str. When replacement_str is shorter than search_str, thus having no characters to substitute in the part longer than replacement_str, the characters to be replaced will be deleted.

Example:

SELECT TRANSLATE('abcde', 'ace', '123');
Result:1b2d3

SELECT TRANSLATE('abcdeca', 'ace', '123');
Result: 1b2d321

SELECT TRANSLATE('abcde', 'ac', '123');
Result: 1b2de

SELECT TRANSLATE('abcde', 'ace', '12');
Result: 1b2d

SELECT TRANSLATE('abcde', 'AB', '123');
Result: abcde

SELECT TRANSLATE('abcde', 'abc', '');
Result: de

2.6.5 Time functions

2.6.5.1 NOW

Format NOW()

Returns the current time value.

Example:

SELECT NOW();
Result: 2019-09-17T04:07:31.825Z

SELECT NOW();
Result: 2019-09-17T13:09:20.918+09:00

2.6.5.2 TIMESTAMP

Format TIMESTAMP(timestamp_string [, timezone])

Convert the string representation of time to TIMESTAMP type.

Example:

// Search for a row with the value of column date (TIMESTAMP type) newer than time '2018-12-01T10: 30: 00Z'
SELECT * FROM timeseries WHERE date > TIMESTAMP('2018-12-01T10:30:00Z');

2.6.5.3 TIMESTAMP_ADD

Format TIMESTAMP_ADD(time_unit, timestamp, duration [, timezone])

The value obtained by adding the period "duration" (unit: time_umit) to time period "timestamp" is returned.

Example:

Add ten days to time period '2018-12-01T11:22:33.444Z'.
SELECT TIMESTAMP_ADD(DAY, TIMESTAMP('2018-12-01T11:22:33.444Z'), 10);
Result: 2018-12-11T11:22:33.444Z

SELECT TIMESTAMP_ADD(MONTH, TIMESTAMP('2019-05-31T01:23:45.678Z'), 1);
Result: 2019-06-30T01:23:45.678Z

SELECT TIMESTAMP_ADD(MONTH, TIMESTAMP('2019-05-31T01:23:45.678Z'), 1, '-02:00');
Result: 2019-07-01T01:23:45.678Z

2.6.5.4 TIMESTAMP_DIFF

Format TIMESTAMP_DIFF(time_unit, timestamp1, timestamp2 [, timezone])

Returns the difference of timestamp1 and timestamp2 (timestamp1-timestamp2) as a value expressed in the time unit "time_unit". When a time difference is represented in time units, the decimal places are rounded off.

Example:


// Time unit: Month
SELECT TIMESTAMPDIFF(MONTH, TIMESTAMP('2018-12-11T10:30:15.555Z'), TIMESTAMP('2018-12-01T10:00:00.000Z'));
Result: 0

Time unit: Day
SELECT TIMESTAMPDIFF(DAY,   TIMESTAMP('2018-12-11T10:30:15.555Z'), TIMESTAMP('2018-12-01T10:00:00.000Z'));
Result: 10

// Time unit: Time point
SELECT TIMESTAMPDIFF(HOUR,  TIMESTAMP('2018-12-11T10:30:15.555Z'), TIMESTAMP('2018-12-01T10:00:00.000Z'));
Result: 240

// Time unit: Minute
SELECT TIMESTAMPDIFF(MINUTE, TIMESTAMP('2018-12-11T10:30:15.555Z'), TIMESTAMP('2018-12-01T10:00:00.000Z'));
Result: 14430

// Here is an example where the result changes depending on the time zone. 
SELECT TIMESTAMP_DIFF(MONTH, MAKE_TIMESTAMP(2019, 8, 1), MAKE_TIMESTAMP(2019, 6, 30), 'Z');
Result: 2

SELECT TIMESTAMP_DIFF(MONTH, MAKE_TIMESTAMP(2019, 8, 1), MAKE_TIMESTAMP(2019, 6, 30), '-01:00');
Result: 1

2.6.5.5 TO_TIMESTAMP_MS

Format TO_TIMESTAMP_MS(milliseconds)

Return the time point obtained by adding the value of argument "milliseconds" as millisecond, to the time point'1970-01-01T00:00:00.000Z'.

This function is an inverse conversion of TO_EPOCH_MS function.

Example:

SELECT TO_TIMESTAMP_MS(1609459199999);
Result: 2020-12-31T23:59:59.999Z

2.6.5.6 TO_EPOCH_MS

Format TO_EPOCH_MS(timestamp)

Return the lapsed time (in milliseconds) from the time '1970-01-01T00:00:00.000Z' to the time "timestamp".

This function is an inverse conversion of TO_EPOCH_MS function.

Example:

SELECT TO_EPOCH_MS(TIMESTAMP('2020-12-31T23:59:59.999Z'));
Result: 1609459199999

SELECT TO_EPOCH_MS(TIMESTAMP('2020-12-31T23:59:59.999+09:00'));
Result: 1609426799999

2.6.5.7 EXTRACT

Format EXTRACT(time_field, timestamp [, timezone])

Retrieve the value of time field "time_field" from the time "timestamp". The time will be the value of UTC.

Example:

// Calculate the value of the year, the day, and the millisecond of time point '2018-12-01T10:30:02.392Z'.

// The value of the year
SELECT EXTRACT(YEAR, TIMESTAMP('2018-12-01T10:30:02.392Z'));
Result: 2018

SELECT EXTRACT(DAY, TIMESTAMP('2018-12-01T10:30:02.392Z'));
// The value of the day
Result: 1

// The value of the millisecond
SELECT EXTRACT(MILLISECOND, TIMESTAMP('2018-12-01T10:30:02.392Z'));
Result: 392

// Consider the time zone. 
SELECT EXTRACT(HOUR, TIMESTAMP('2018-12-01T10:30:02.392Z'), '+09:00');
Result: 19

2.6.5.8 STRFTIME

Format STRFTIME(format, timestamp [, modifier,...])

Return a time converted to a string according to the specified format.

Format Description
%Y Extract the year in YYYY format.
%m Extract the month in MM format.
%d Extract the day in DD format.
%H Extract the time in hh format.
%M Extract the minute in mm format.
%S Extract the second in ss format.
%3f Extract millisecond in SSS format.
%z Extract the time zone in ± hh:mm format.
%w Extracts the day of the week in D format (0 to 6): from Sunday, as 0, to Saturday, as 6.
%W Extracts the number of the week of the year in DD format (from 00 to 53). The first Monday is considered to be in the first week, and days before that are considered to be in the 0th week.
%j Extract the number of days from January first in DDD format (001 to 366).
%c Extract the time in the format YYYY-MM-DDThh:mm:ss[.SSS](Z|±hh:mm|±hhmm).
%% Output % as a character.

Example:


SELECT STRFTIME('%c', TIMESTAMP('2019-06-19T14:15:01.123Z'));
Result: 2019-06-19T14:15:01.123Z

SELECT STRFTIME('%H:%M:%S%z', TIMESTAMP('2019-06-19T14:15:01.123Z'), '+09:00');
Result: 23:15:01+09:00

SELECT STRFTIME('%W', TIMESTAMP('2019-01-19T14:15:01.123Z'));
Result: 02

2.6.5.9 MAKE_TIMESTAMP

Format MAKE_TIMESTAMP(year, month, day [, timezone])
MAKE_TIMESTAMP(year, month, day, hour, min, sec [, timezone])

Generate and return a TIMESTAMP type value.

Example:


SELECT MAKE_TIMESTAMP(2019, 9, 19);
Result: 2019-09-19T00:00:00.000Z

SELECT MAKE_TIMESTAMP(2019, 9, 19, 10, 30, 15.123, '+09:00');
Result: 2019-09-19T01:30:15.123Z

2.6.5.10 TIMESTAMP_TRUNC

Format TIMESTAMP_TRUNC(field, timestamp [, timezone])

Truncates the time information.

Example:


SELECT TIMESTAMP_TRUNC(HOUR, MAKE_TIMESTAMP(2019, 9, 19, 10, 30, 15.123));
Result: 2019-09-19T10:00:00.000Z

SELECT TIMESTAMP_TRUNC(DAY, MAKE_TIMESTAMP(2019, 5, 15), '-01:00');
Result: 2019-05-14T01:00:00.000Z

2.6.6 WINDOW function

2.6.6.1 ROW_NUMBER

Format ROW_NUMBER() OVER ( [PARTITION BY expression_1] [ORDER BY expression_2 ] )

Assign a unique serial number to the resulting rows.

Example:

SELECT ROW_NUMBER() OVER(PARTITION BY department ORDER BY age) no, first_name, age, department FROM employees;
Result:
  no   first_name   age      department
  ----+------------+--------+-------------
  1    James        43       Development
  2    William      59       Development
  1    Mary         31       Research
  1    John         43       Sales
  2    Richard      (NULL)   Sales
  1    Lisa         29       (NULL)

2.6.7 Other functions

2.6.7.1 COALESCE

Format COALESCE(x1, x2 [,..., xn])

Return the value of the first argument that is not NULL in the specified argument xn.

Example:

SELECT last_name, COALESCE(last_name, 'XXX') coalesce FROM employees;
Result:
  last_name     coalesce
  ------------+----------------------
  Smith         Smith
  Jones         Jones
  Brown         Brown
  Taylor        Taylor
  (NULL)        XXX
  Smith         Smith

SELECT age, COALESCE(age, -1) coalesce FROM employees;
Result:
  age       coalesce
  --------+-----------
  43         43
  59         59
  (NULL)     -1
  31         31
  29         29
  43         43

2.6.7.2 IFNULL

Format IFNULL(x, y)

Return the value of the first argument that is not NULL among the specified arguments x and y. The IFNULL function is equivalent to the COALESCE function with two arguments.

Example:

SELECT last_name, IFNULL(last_name, 'XXX') ifnull FROM employees;
Result：
  last_name     ifnull
  ------------+----------------------
  Smith         Smith
  Jones         Jones
  Brown         Brown
  Taylor        Taylor
  (NULL)        XXX
  Smith         Smith

SELECT age, IFNULL(age, -1) ifnull FROM employees;
Result：
  age       coalesce
  --------+-----------
  43         43
  59         59
  (NULL)     -1
  31         31
  29         29
  43         43

2.6.7.3 NULLIF

Format NULLIF(x, y)

Return NULL when two arguments are the same, return the first argument when the arguments are different.

Example:

// Execute NULLIF with the value of value1 and value2.
SELECT value1, value2, NULLIF(value1, value2) nullif FROM container_sample;
Result: 
   value1   value2   nullif
  --------+--------+--------
      10       10    (NULL)
       5        0      5
   (NULL)       4    (NULL)
       3    (NULL)     3
   (NULL)   (NULL)   (NULL)


// Convert 0 to NULL to prevent division by zero errors in the calculation of value1 / value2
SELECT value1, value2, value1/NULLIF(value2, 0) division FROM container_sample;
Result: 
   value1   value2   division
  --------+--------+--------
      10       10      1
       5        0    (NULL)
   (NULL)       4    (NULL)
       3    (NULL)   (NULL)
   (NULL)   (NULL)   (NULL)

2.6.7.4 RANDOMBLOB

Format RANDOMBLOB(size)

Return a BLOB type value (random number).

Example:　

// Generate a 10-byte blob value (random number)
SELECT HEX(RANDOMBLOB(10));
Result: 7C8C893C8087F07883AF

2.6.7.5 ZEROBLOB

Format ZEROBLOB(size)

Return a BLOB type value (0x00).

Example:　

// Generate a 10-byte blob value (0x00).
SELECT HEX(ZEROBLOB(10));
Result: 00000000000000000000

2.6.7.6 HEX

Format HEX(x)

Convert a BLOB type value to a hexadecimal type. Interpret the argument x as a BLOB type value, and return the character string (uppercase) converted into the hexadecimal.

Example:

SELECT HEX(RANDOMBLOB(2));
Result: E18D

SELECT first_name, HEX(first_name) hex FROM employees;
Result:
  first_name    hex
  ------------+----------------------
  John          4A6F686E
  William       57696C6C69616D
  Richard       52696368617264
  Mary          4D617279
  Lisa          4C697361
  James         4A616D6573

2.6.7.7 TYPEOF

Format TYPEOF(x)

Return the character string indicating the data type of the value of x.

Example:

SELECT TYPEOF(ABS(-10)) abs, TYPEOF(RANDOMBLOB(10)) randomblob,
    TYPEOF(TIMESTAMP('2018-12-01T10:30:02.392Z')) timestamp;
Result：
   abs    randomblob   timestamp
  ------+------------+-----------
   LONG   BLOB         TIMESTAMP

　

2.7 Other syntaxes

2.7.1 CAST

Format CAST(x AS data_type)

Convert the value x into the data type "data_type".

2.7.1.1 Convert to string type

Format CAST(x AS STRING)

Convert the argument x to a character string type.

The data types of the value which can be specified for x, and the converted values are as follows.

Data type of x Value converted to character string type
BOOL 'true' if true, 'false' if false
STRING Original value
BYTE
SHORT
INTEGER
LONG
FLOAT
DOUBLE		 Value converted from a number to a character string
TIMESTAMP Character string notation of time point 'YYYY-MM-DDThh:mm:ss.SSS(Z| ±hh:mm)'
The time zone setting at the time of connection is used.
BLOB A character string equivalent to the converted character string using HEX function

2.7.1.2 Convert to numeric type

Format CAST(x AS BYTE|SHORT|INTEGER|LONG|FLOAT|DOUBLE)

Convert the argument x into a numeric type.

The data types of the value which can be specified for x, and the converted values are as follows.

Data type of x Value converted to numeric type
BOOL 1 if true, 0 if false
STRING The value converted from the character string to numerical value
BYTE
SHORT
INTEGER
LONG
FLOAT
DOUBLE		 The numerical value converted to the specified numeric type 
// An error occurs if exceeding BYTE type range (-128 to 127)
SELECT CAST(128 AS BYTE);
Result: error

// An error occurs if exceeding INTEGER type range (-2147483648-2147483647).
SELECT CAST('2147483648' AS INTEGER);
Result: error

SELECT CAST(10.5 AS INTEGER);
Result: 10

SELECT CAST('abc' AS INTEGER);
Result: error

SELECT CAST('-1.09E+10' AS DOUBLE);
Result: -1.09E10

2.7.1.3 Convert to time type

Format CAST(x AS TIMESTAMP)

Convert the argument x to a time type. If the time zone is specified at the time of connection, that value is used for offset calculation.

The data types of the value which can be specified for x, and the converted values are as follows.

Data type of x Value converted to time type
STRING (character string notation of time 'YYYY-MM-DDThh:mm:ss.SSSZ') Equivalent to the value converted using the TIMESTAMP function 
SELECT CAST('2018-12-01T10:30:00Z' AS TIMESTAMP);
Result: 2018-12-01T10:30:00.000Z

SELECT CAST('2018-12-01T10:30:00+09:00' AS TIMESTAMP);
Result: 2018-12-01T01:30:00.000Z

2.7.1.4 Convert to BOOL type

Format CAST(x AS BOOL)

Convert the argument x to a BOOL type.

The data types of the value which can be specified for x, and the converted values are as follows.

Data type of x Value converted to time type
STRING True if 'true', false if 'false' (case insensitive)
BYTE
SHORT
INTEGER
LONG		 False if 0, otherwise true

2.7.1.5 Convert to BLOB type

Format CAST(x AS BLOB)

Convert the argument x to a BLOB type.

The data types of the value which can be specified for x, and the converted values are as follows.

Data type of x Value converted to BLOB type
STRING The value converted from character string as hexadecimal data to BLOB type

　

2.7.2 CASE

Format CASE
WHEN condition1 THEN result1
[WHEN condition2 THEN result2]
...
[ELSE resultElse]
END

When the conditional expression conditionN is true, the value of corresponding resultN is returned. When all the conditional expressions are false or NULL, and if ELSE is specified, the value of resultElse will be returned. When ELSE is not specified, NULL is returned.

　

Format CASE x
WHEN value1 THEN result1
[WHEN value2 THEN result2]
...
[ELSE resultElse]
END

When the value of x is valueN, the value of corresponding resultN is returned. When the value of x is not equal to all values, and if ELSE is specified, the value of resultElse will be returned. When ELSE is not specified, NULL is returned.

　

Specify the same type value for resultN. There are some different types that can be specified.

Example:

// Display the employee's age (30's, 40's, 50's, other than these)
SELECT id, first_name, age,
  CASE
    WHEN age > 50 THEN '50s'
    WHEN age > 40 THEN '40s'
    WHEN age > 30 THEN '30s'
    ELSE 'other'
  END AS period
FROM employees;

Result: 
　id   first_name   age     period
　----+------------+-------+--------
　0    John          43      40s
　1    William       59      50s
　2    Richard      (NULL)   other
　3    Mary          31      30s
　4    Lisa          29      other
　5    James         43      40s


// Display a location according to their departments.
SELECT id, first_name, department,
  CASE department
    WHEN 'Sales' THEN 'Tokyo'
    WHEN 'Development' THEN 'Osaka'
    ELSE 'Nagoya'
  END AS location
FROM employees;

Result: 
　id   first_name   department    location
　----+------------+-------------+---------
　0    John         Sales         Tokyo
　1    William      Development   Osaka
　2    Richard      Sales         Tokyo
　3    Mary         Research      Nagoya
　4    Lisa         (NULL)        Nagoya
　5    James        Development   Osaka

2.7.3 Subquery

Subqueries can be specified in various parts of an SQL statemnt other than FROM and WHERE clauses. Some operation types for subqueries are also provided, which are explained in this section.

2.7.3.1 IN

Return whether the specified value is included in the sub query execution result.

Syntax

Expression 1 [NOT] IN ( sub_query )

Specifications

Example:

// Display the information of the employee who belongs to the department of id=1 in the departments table from the employees table.
SELECT * FROM employees
WHERE department IN(
  SELECT department FROM departments
  WHERE id = 1
);
Result: 
  id   first_name   last_name   age     department    enrollment_period
  ----+------------+-----------+-------+-------------+-------------------
   1   William      Jones       59      Development   23.2
   5   James        Smith       43      Development   10.3

2.7.3.2 EXISTS

Return whether the execution result of the sub query exists.

Syntax

[NOT] EXISTS( sub_query )

Specifications

Example:

// Display the information of the employee who belongs to the department of id=1 in the departments table from the employees table.
SELECT * FROM employees
WHERE EXISTS(
   SELECT * FROM departments
   WHERE employees.department=departments.department AND departments.id=1
);
Result: 
  id   first_name   last_name   age     department    enrollment_period
  ----+------------+-----------+-------+-------------+-------------------
   1   William      Jones       59      Development   23.2
   5   James        Smith       43      Development   10.3

2.7.3.3 Scalar sub query

Subquery which returns one result, which can be used for the result of a SELECT statement or for an expression.

Example:

SELECT id, first_name,
       (SELECT department FROM departments WHERE department_id=employees.department_id)
FROM employees;

Result: 
  id  first_name  department
  ---+-----------+-------------
   0  John        Sales
   1  William     Development
   2  Richard     Sales
   3  Mary        (NULL)
   4  Lisa        Marketing
   5  James       Development

2.7.4 Placeholder

A prepared statement can describe a placeholder in SQL statements. A placeholder indicates the position of the parameter to be substituted when the statement is executed. The parameter number starts from 1.

The placeholder can use several forms for compatibility with other databases. However, the parameter number will be the already assigned parameter number + 1, regardless of which format is specified.

Format Description Example of description
? Format of a standard placeholder ?
?NNN NNN indicates a number. ?56
:AAAA AAAA indicates a character string. :name
@AAAA AAAA indicates a character string. @name

The placeholder must not start with $.

Example:

String sql = "SELECT * FROM users WHERE id > ? AND id != :exclude_id;";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setInt(1, 100);  // 1: ?
pstmt.setInt(2, 253);  // 2: :exclude_id
ResultSet rs = pstmt.executeQuery();

2.8 Comment

Comments can be written in a SQL command. Format: Description at the back of -- (2 hyphens) or enclose with /* */. A new line needs to be returned at the end of the comment.

SELECT * -- comment
FROM employees;

SELECT *
/*
  comment
*/
FROM employees;

2.9 Hints

In GridDB AE, specifying the hints indicating the execution plan in the query makes it possible to control the execution plan without changing the SQL statement.

See "GridDB Advanced Edition SQL tuning guide" (GridDB_AE_SQL_TuningGuide.html) to tune with hint clauses.

2.9.1 Error handling

In the following cases, a syntax error occurs.

In the following case, a table specification error occurs:

[Memo]

3 Metatables

3.1 About metatables

The metatables are tables that are used for checking metadata of data management in GridDB.

[Memo]

[Points to note]

3.2 Table information

Table information can be obtained.

Table name

#tables

Schema

Column name Item Type
DATABASE_NAME Database name STRING
TABLE_NAME Table name STRING
TABLE_OPTIONAL_TYPE Table type
COLLECTION / TIMESERIES		 STRING
DATA_AFFINITY Data affinity STRING
EXPIRATION_TIME Expiry release elapsed time INTEGER
EXPIRATION_TIME_UNIT Expiry release elapsed time unit STRING
EXPIRATION_DIVISION_COUNT Expiry release division count STRING
COMPRESSION_METHOD Time series compression method STRING
COMPRESSION_WINDOW_SIZE Time series compression max period of thinning INTEGER
COMPRESSION_WINDOW_SIZE_UNIT Time series compression max period unit of thinning STRING
PARTITION_TYPE Partitioning type STRING
PARTITION_COLUMN Partitioning key STRING
PARTITION_INTERVAL_VALUE Interval value (For interval or interval hash) INTEGER
PARTITION_INTERVAL_UNIT Interval unit (For interval of interval hash) STRING
PARTITION_DIVISION_COUNT Division count (For hash) INTEGER
SUBPARTITION_TYPE Partitioning type
("Hash" for interval hash)		 STRING
SUBPARTITION_COLUMN Partitioning key
(for interval hash)		 STRING
SUBPARTITION_INTERVAL_VALUE Interval value INTEGER
SUBPARTITION_INTERVAL_UNIT Interval unit STRING
SUBPARTITION_DIVISION_COUNT Division count
(For interval hash)		 INTEGER
EXPIRATION_TYPE Expiration type
ROW / PARTITION		 STRING

3.3 Index information

Index information can be obtained.

Table name

#index_info

Schema

Column name Item Type
DATABASE_NAME Database name STRING
TABLE_NAME Table name STRING
INDEX_NAME Index name STRING
INDEX_TYPE Index type
TREE / HASH / SPATIAL		 STRING
ORDINAL_POSITION Column order in index (sequential number from 1) SHORT
COLUMN_NAME Column name STRING

3.4 Partitioning information

Data about partitioned tables can be obtained from this metatable.

Table name

#table_partitions

Schema

Column name Item Type
DATABASE_NAME Database name STRING
TABLE_NAME Partitioned table name STRING
PARTITION_BOUNDARY_VALUE The lower limit value of each data partition STRING

Specifications

Examples

3.5 View information

View information can be obtained.

Table name

#views

Schema

Column name Item Type
DATABASE_NAME Database name STRING
VIEW_NAME View name STRING
VIEW_DEFINITION View defining character string STRING

3.6 Information about an SQL in execution

The information about SQL , a query or a job, under execution can be obtained.

Table name

#sqls

Schema

Column name Item Type
DATABASE_NAME Database name STRING
NODE_ADDRESS address of the node being processed (system) STRING
NODE_PORT The port of the node being processed (system) INTEGER
START_TIME Processing start time TIMESTAMP
APPLICATION_NAME Application name STRING
SQL Query character string STRING
QUERY_ID Query ID STRING
JOB_ID Job ID STRING

3.7 Information about an event in execution

The information about the event under execution can be obtained.

Table name

#events

Schema

Column name Item Type
NODE_ADDRESS address of the node being processed (system) STRING
NODE_PORT The port of the node being processed (system) INTEGER
START_TIME Processing start time TIMESTAMP
APPLICATION_NAME Application name STRING
SERVICE_TYPE Service type (SQL/TRANSACTION/CHECKPOINT/SYNC) STRING
EVENT_TYPE Event types (PUT/CP_START/SYNC_START etc.) STRING
WORKER_INDEX Thread number of a worker INTEGER
CLUSTER_PARTITION_INDEX Cluster partition number INTEGER

3.8 Connection information

The information about the connected connection can be obtained.

Table name

#sockets

Schema

Column name Item Type
SERVICE_TYPE Service type (SQL/TRANSACTION) STRING
SOCKET_TYPE Socket type STRING
NODE_ADDRESS Connection source node address (viewed from a node) STRING
NODE_PORT Connection source node port (viewed from a node) INTEGER
REMOTE_ADDRESS Connection destination node address (viewed from a node) STRING
REMOTE_PORT Connection destination node port (viewed from a node) INTEGER
APPLICATION_NAME Application name STRING
CREATION_TIME Socket generation time TIMESTAMP
DISPATCHING_EVENT_COUNT Total number of times to start request for event handling LONG
SENDING_EVENT_COUNT Total number of times to start event transmission LONG

the socket types are as follows.

Value Description
SERVER TCP connection between servers
CLIENT TCP connection with a client
MULTICAST Multicasting socket
NULL In case currently unidentified during the cases such as connection attempt

Examples

Only in case of TCP connection with a client (socket type: CLIENT), it can be determined whether the connection is waiting for execution.

Specifically, if DISPATCHING_EVENT_COUNT is larger than SENDING_EVENT_COUNT, it can be determine that the possibility is relatively high that the time waiting for execution existed.

SELECT CREATION_TIME, NODE_ADDRESS, NODE_PORT, APPLICATION_NAME FROM "#sockets"
WHERE SOCKET_TYPE='CLIENT' AND DISPATCHING_EVENT_COUNT > SENDING_EVENT_COUNT;

CREATION_TIME             NODE_ADDRESS   NODE_PORT  APPLICATION_NAME
--------------------------------------------------------------------
2019-03-27T11:30:57.147Z  192.168.56.71  20001      myapp
2019-03-27T11:36:37.352Z  192.168.56.71  20001      myapp
          :

　

4 Reserved words

The following terms are defined as keywords in the SQL of GridDB AE.

ABORT ACTION AFTER ALL ANALYZE AND AS ASC BEGIN BETWEEN BY CASE CAST COLLATE COLUMN COMMIT CONFLICT CREATE CROSS DATABASE DAY DELETE DESC DISTINCT DROP ELSE END ESCAPE EXCEPT EXCLUSIVE EXISTS EXPLAIN EXTRACT FALSE FOR FROM GLOB GRANT GROUP HASH HAVING HOUR IDENTIFIED IF IN INDEX INITIALLY INNER INSERT INSTEAD INTERSECT INTO IS ISNULL JOIN KEY LEFT LIKE LIMIT MATCH MILLISECOND MINUTE MONTH NATURAL NO NOT NOTNULL NULL OF OFFSET ON OR ORDER OUTER PARTITION PARTITIONS PASSWORD PLAN PRAGMA PRIMARY QUERY RAISE REGEXP RELEASE REPLACE RESTRICT REVOKE RIGHT ROLLBACK ROW SECOND SELECT SET TABLE THEN TIMESTAMPADD TIMESTAMPDIFF TO TRANSACTION TRUE UNION UPDATE USER USING VALUES VIEW VIRTUAL WHEN WHERE WITHOUT XOR YEAR