GridDB SQL Reference

Revision: 5.1.0-666

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 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 the table below. These data type names can be specified as a column data type when creating a table.

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.

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

• Name of specified data type is "BIGINTEGER" -> INTEGER (Rule 6)
• Name of specified data type is "LONG" -> LONG (Rule 1)
• Name of specified data type is "TINYINT" -> BYTE (Rule 3)
• Name of specified data type is "FLOAT" -> FLOAT (Rule 1)
• Name of specified data type is "VARCHAR" -> STRING (Rule 7)
• Name of specified data type is "CHARINT" -> INTEGER (Rule 6)
• Name of specified data type is "BIGBLOB" -> BLOB (Rule 8)
• Name of specified data type is "FLOATDOUB" -> DOUBLE (Rule 9)
• Name of specified data type is "INTREAL" -> INTEGER (Rule 6)
• Name of specified data type is "FLOATINGPOINT" -> INTEGER (Rule 6)
• Name of specified data type is "DECIMAL" -> error

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. However, some data types may not be used as the equivalent type do not exist.

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:

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.

• GEOMETRY
• ARRAY

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

• Creating a table using CREATE TABLE

  • These data types cannot be specified as a data type of the column when creating a table. An error occurs.

• Deleting a table using DROP TABLE

  • The table, which has any columns of these data types, can be deleted.

• Registration/updating/deleting using INSERT/UPDATE/DELETE

  • For a table with the column of these data types, INSERT/UPDATE/DELETE causes an error.

  • Rows can not be registered or updated even by specifying only the column values of the supported data types, without specifying any column values of these data types.

    // The table created using the NoSQL interface
      name: sample1
      Column: id INTEGER
              value DOUBLE
              geometry GEOMETRY
    
    // Register rows by specifying only INTEGER and DOUBLE columns. -> An error occurs because the table has a GEOMETRY type column.
    INSERT INTO sample1 (id, value) VALUES (1, 192.3)
    

• Searching using SELECT

  • Whenever a table with the column of these data types are searched, NULL returns from these columns.

• Creating/deleting an index using CREATE INDEX/DROP INDEX

  • Creating/deleting an index on a GEOMETRY type column is possible.
  • Creating/deleting an index on an array type column is not allowed. An error occurs. (In the NoSQL interface, creating/deleting an index on an array type column is not allowed.)

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:

• A database name, table name, view name, column name, index name and general user name is a string composed of one or more ASCII alphanumeric characters, the underscore "_" , the hyphen "-" , the dot "." , the slash "/" and the equal "=".
• For table name, the "@" character can also be specified for the node affinity function.

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

[Notice]

• If the name of a table or a column contains characters other than ASCII alphanumeric characters and underscore, or if the first character of the name is a number in a SQL statement, enclose the name with double quotation marks.

  SELECT "column.a1" FROM "Table-5"
  

2 SQL commands supported by GridDB

Supported SQL commands are in the table as follows.

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

Specifications

• This command can only be executed by the administrative user.
• Databases with the same name as "public", "information_schema" cannot be created as these are reserved for internal use in GridDB.
• Nothing will be changed if a database with the same name already exists.
• See "GridDB Features Reference" (GridDB_FeaturesReference.html) for the rules of a database name.

2.1.2 CREATE TABLE

2.1.2.1 Creating a table

Create a table.

Syntax

• Table (collection)

  CREATE TABLE [IF NOT EXISTS] table name (column definition [, column definition ...] [, PRIMARY KEY (column name [, ...])]) [WITH (property key = property value)];

• Timeseries table (timeseries container)

  CREATE TABLE [IF NOT EXISTS] table_name ( column_name TIMESTAMP PRIMARY KEY [, column definition ...] ) USING TIMESERIES [WITH (property_key=property_value [, property_key=property_value ...])];

• column definition

  • column_name data_type [ column_constraint ]

• column_constraint

  • PRIMARY KEY (only the 1st column can be specified)
  • NULL
  • NOT NULL

Specifications

• See "GridDB Features Reference" (GridDB_FeaturesReference.html) for the rule of a table name and a column name.

• If "IF NOT EXISTS" is specified, the specified table can be created only if another table with the same name does not exist.

• The column name and data type name need to be specified in column definition. See Data types used in data storage for the data types that can be specified.

• Composite primary key can be set to a table (collection) by setting the primary key after describing the column definition. The composite primary key must be set to the columns which are continuous from the first column and can be set up to 16 columns. It cannot be set together with the PRIMARY KEY as a column constraint, cannot be set to a time series table (time series container).

• See "GridDB Features Reference" (GridDB_FeaturesReference.html) for details of time series table (Time series container).

• Options related to data affinity can be specified in the format "WITH (property key = property value, ...)".

  Function	Item	Property key	Property value type
  Data affinity	hint (Character string indicating similarity between containers)	data_affinity	STRING

• See "GridDB Features Reference" (GridDB_FeaturesReference.html) for the details of each item.

Examples

• Creating a table

  CREATE TABLE myTable (
    key INTEGER PRIMARY KEY,
    value1 DOUBLE NOT NULL,
    value2 DOUBLE NOT NULL
  );
  

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

• Table (collection)

  CREATE TABLE [IF NOT EXISTS] table_name ( column definition [, column definition ...] [, PRIMARY KEY(column name [, ...])] ) [WITH (property_key=property_value)] PARTITION BY HASH (column_name_of_partitioning_key) PARTITIONS division_count;

• Timeseries table (timeseries container)

  CREATE TABLE [IF NOT EXISTS] table_name ( column definition [, column definition ...] ) USING TIMESERIES [WITH (property_key=property_value, ...)]] PARTITION BY HASH (column_name_of_partitioning_key) PARTITIONS division_count ;

Specifications

• Create a hash partitioned table usng the column name of the partitioning key and the value of division count.
• Specify the value from 1 to 1024 for "division_count".
• The partitioning key requires the primary key. To set a key other than the primary key, the restriction in the configuration file need to be removed. For details, refer to the cluster definition file settings in GridDB Features Reference (GridDB_FeaturesReference.html).
• The column specified as partitioning key cannot be updated.
• Options related to data affinity can be specified in the format "WITH (property key = property value, ...)". The options that can be specified are same as normal table.

Examples

• Creating a hash partitioned table

  CREATE TABLE myHashPartition (
    id INTEGER PRIMARY KEY,
    value STRING
  ) PARTITION BY HASH (id) PARTITIONS 128;
  

(2) Creating an interval partitioned table

Syntax

• Table (collection)

  CREATE TABLE [IF NOT EXISTS] table_name ( column definition [, column definition ...] [, PRIMARY KEY(column name [, ...])]) [WITH (property_key=property_value, ...)] PARTITION BY RANGE(column_name_of_partitioning_key) EVERY(interval_value [, interval_unit ]);

• Timeseries table (timeseries container)

  CREATE TABLE [IF NOT EXISTS] table_name ( column definition [, column definition ...] ) USING TIMESERIES [WITH (property_key=property_value, ...)] PARTITION BY RANGE(column_name_of_partitioning_key) EVERY(interval_value [, interval_unit ]) ;

Specifications

• Specify the column which type is BYTE, SHORT, INTEGER, LONG or TIMESTAMP for "column_name_of_partitioning_key".

• The partitioning key requires the primary key. To set a key other than the primary key, the restriction in the configuration file need to be removed. For details, refer to the cluster definition file settings in GridDB Features Reference (GridDB_FeaturesReference.html).

• The column specified as partitioning key cannot be updated.

• The following values can be specified as the "interval_value".

  Partitioning key type	Possible interval value
  BYTE	from 1 to 27-1
  SHORT	from 1 to 215-1
  INTEGER	from 1 to 231-1
  LONG	from 1000 to 263-1
  TIMESTAMP	1 or more

• If the column of TIMESTAMP is specified, it is also required to specify the interval unit. DAY is the only value that can be specified as the interval unit.

• The interval unit cannot be specified for any types other than TIMESTAMP.

• Options related to data affinity can be specified in the format "WITH (property key = property value, ...)". The options that can be specified are same as normal table.

• The options about expiry release can be specified by the format " WITH (property_key=property_value, ...)".

  Function	Item	Property key	Property value type	Required or optional when setting expiry release
  Expiry release function	Type	expiration_type	STRING (Only the following type can be specified. PARTITION: Partition expiry release)	Optional (default: PARTITION)
  	Expiration time	expiration_time	INTEGER	Required
  	Expiration time unit	expiration_time_unit	STRING (The following five types can be specified. DAY / HOUR / MINUTE / SECOND / MILLISECOND )	Optional (default: DAY)
  	Expiration division count	expiration_division_count	INTEGER	Optional (default: 8)

• The partition expiry release can only be specified for followings:

  • Timeseries table (timeseries container)
  • Table (collection) whose partitioning key is TIMESTAMP type.

• See "GridDB Features Reference" (GridDB_FeaturesReference.html) for the details of each item.

Examples

• Creating an interval partitioned table.

  CREATE TABLE myIntervalPartition (
    date TIMESTAMP PRIMARY KEY,
    value STRING
  ) PARTITION BY RANGE (date) EVERY (30, DAY);
  

• Creating an interval partitioned table (timeseries table) using the partition expiry release function.

  CREATE TABLE myIntervalPartition2 (
    date TIMESTAMP PRIMARY KEY,
    value STRING
  ) USING TIMESERIES WITH (
    expiration_type='PARTITION',
    expiration_time=90,
    expiration_time_unit='DAY'
  ) PARTITION BY RANGE (date) EVERY (30, DAY);
  

(3) Creating an interval hash partitioned table

Syntax

• Table (collection)

  CREATE TABLE [IF NOT EXISTS] table_name ( column definition [, column definition ...] [, PRIMARY KEY((column_name [, ...])] ) [WITH (property_key=property_value, ...) ] PARTITION BY RANGE(column_name_of_interval_partitioning_key) EVERY(interval_value [, interval_unit ]) SUBPARTITION BY HASH(column_name_of_hash_partitioning_key) SUBPARTITIONS division_count;

• Timeseries table (timeseries container)

  CREATE TABLE [IF NOT EXISTS] table_name ( column definition [, column definition ...] ) USING TIMESERIES [WITH (property_key=property_value, ...)] PARTITION BY RANGE(column_name_of_partitioning_key) EVERY(interval_value [, interval_unit ]) SUBPARTITION BY HASH(column_name_of_hashpartitioning_key) SUBPARTITIONS division_count ;

Specifications

• Specify the column which type is BYTE, SHORT, INTEGER, LONG or TIMESTAMP for "column_name_of_interval_partitioning_key".

• The following values can be specified as the "interval_value".

  Partitioning key type	Possible interval value
  BYTE	from 1 to 27-1
  SHORT	from 1 to 215-1
  INTEGER	from 1 to 231-1
  LONG	from 1000 * division_count to -263-1
  TIMESTAMP	1 or more

  • If the column of TIMESTAMP is specified, it is also required to specify the interval unit. DAY is the only value that can be specified as the interval unit.
  • The interval unit cannot be specified for any types other than TIMESTAMP.

• Specify the value from 1 to 1024 for "division_count".

• The partitioning key requires the primary key. To set a key other than the primary key, the restriction in the configuration file need to be removed. For details, refer to the cluster definition file settings in GridDB Features Reference (GridDB_FeaturesReference.html).

• The column specified as partitioning key cannot be updated.

• Options related to data affinity can be specified in the format "WITH (property key = property value, ...)". The options that can be specified are same as normal table.

• The options about expiry release can be specified by the format " WITH (property_key=property_value, ...)".

  Function	Item	Property key	Property value type	Required or optional when setting expiry release
  Expiry release function	Type	expiration_type	STRING (Any of the followings. If omitted, PARTITION. PARTITION: Partition expiry release ROW: Row expiry release)	Optional
  	Expiration time	expiration_time	INTEGER	Required
  	Expiration time unit	expiration_time_unit	STRING (The following five types can be specified. DAY / HOUR / MINUTE / SECOND / MILLISECOND )	Optional (default: DAY)
  	Expiration division count	expiration_division_count	INTEGER	Optional (default: 8)

Examples

• Creating an interval-hash partitioned table

  CREATE TABLE myIntervalHashPartition (
    date TIMESTAMP,
    value STRING,
    PRIMARY KEY (date, value)
  ) PARTITION BY RANGE (date) EVERY (60, DAY)
  SUBPARTITION BY HASH (value) SUBPARTITIONS 64;
  

• Creating an interval-hash partitioned table (timeseries table) using the partition expiry release function.

  CREATE TABLE myIntervalHashPartition2 (
    date TIMESTAMP PRIMARY KEY,
    value STRING
  ) USING TIMESERIES WITH (
    expiration_type='PARTITION',
    expiration_time=90,
    expiration_time_unit='DAY'
  ) PARTITION BY RANGE (date) EVERY (60, DAY)
  SUBPARTITION BY HASH (date) SUBPARTITIONS 64;
  

2.1.3 CREATE INDEX

Create an index.

Syntax

Specifications

• See "GridDB Features Reference" (GridDB_FeaturesReference.html) for the rules of an index name.
• For a table, an index with the same name as an existing index in the table cannot be created.
• If a transaction under execution exists in a table subject to processing, the system will wait for these to be completed before creating the data.
• An index cannot be created on a column of BLOB type and ARRAY type.
• An index with multiple columns can be created. This is called a composite index.
• The maximum number of columns that can be specified in one composite index is 16, and the same column cannot be specified more than one time.
• Time series table does not allow a primary key in a composite index.

2.1.4 CREATE VIEW

Create a view.

Syntax

Specifications

• See "GridDB Features Reference" (GridDB_FeaturesReference.html) for the rules of a view name.
• Whether the result from the SELECT statement is available or not is checked. If the result is not available, a view cannot be created.
• When FORCE is specified, the result from the SELECT statement is not checked, while a syntactic check is done.
• The SELECT statement can include other view names. If other view names in the SELECT statement cause circular reference, the view cannot be created even if FORCE is specified.

2.1.5 CREATE USER

Create a general user.

Syntax

Specifications

• See "GridDB Features Reference" (GridDB_FeaturesReference.html) for the rules of a user name.
• Can be executed by an administrator user only.
• A user with the same name as an administrator user (admin and system) registered during installation cannot be created.
• The password must consist of ASCII characters. It is case-sensitive.

2.1.6 CREATE ROLE

Create a role required for LDAP authentication.

Syntax

Specifications

• For the argument role name, specify either an LDAP user name or an LDAP group name. For details, see the GridDB Features Reference (GridDB_FeaturesReference.html).
• This command can only be executed by the administrative user.
• A user with the same name as an administrator user (admin and system) registered during installation cannot be created.
• The password must consist of ASCII characters. It is case-sensitive.

2.1.7 DROP DATABASE

Delete a database.

Syntax

Specifications

• This command can only be executed by the administrative user.
• The database with the following names are reserved for internal use and thus cannot be deleted: "public", "information_schema", and any names starting with "gs#".
• A database containing tables created by a user cannot be deleted.

2.1.8 DROP TABLE

Delete a table.

Syntax

Specifications

• If "IF EXISTS" is specified, nothing will change if no table with the specified name exists.
• If there is an active transaction involving the table, the table will be deleted only after the transaction is completed.

2.1.9 DROP INDEX

Delete the specified index.

Syntax

Specifications

• If "IF EXISTS" is specified, nothing will change if no index with the specified name exists.
• If there is an active transaction involving the table, the table will be deleted only after the transaction is completed.
• The unnamed index creating through NoSQL I/F can not be deleted by "DROP INDEX".

2.1.10 DROP VIEW

Delete a view.

Syntax

Specifications

• If "IF EXISTS" is specified, nothing will be changed if a view with the specified name does not exist.

2.1.11 DROP USER

Delete a general user.

Syntax

Specifications

• This command can only be executed by the administrative user.

2.1.12 DROP ROLE

Delete a role required for LDAP authentication.

Syntax

Specifications

• This command can only be executed by the administrative user.

2.1.13 ALTER TABLE

Change the structure of a table.

2.1.13.1 Adding columns to a table

Add columns to the end of the table.

Syntax

• column definition

  • column_name data_type [ column_constraint ]

• column_constraint

  • NULL
  • NOT NULL

Specifications

• The added column is located in the end of the table. If multiple columns are specified, they are located in their order.
• PRIMARY KEY can not be specified to the column constraint.
• If the same name column exists, an error occurs.

Examples

• Adding multiple columns to the table

  ALTER TABLE myTable1
  ADD COLUMN col111 STRING NOT NULL,
  ADD COLUMN col112 INTEGER;
  

2.1.13.2 Deleting data partitions

Delete data partitions created by table partitioning.

Syntax

Specifications

• Data partitions can be deleted only for interval and interval-hash partitioning.
• Specify the value included in the data partition to be deleted.
• Data in the range of the once deleted data partition (from the lower limit value to the upper limit value of the data partition) cannot be registered.
• The lower limit value of a data partition can be checked by metatable. In many cases, the upper limit of a data partition is the lower limit value plus division width value.
• For interval-hash partitioned tables, there are multiple data partitions which have the same lower limit value, and the maximum number of those partitions is equal to the hash division count. Those data partitions are deleted simultaneously. Deleted partitions are checked by metatable.

See Metatables for the details on the metatable.

Examples

Interval partitioned table

• Check the lower limit value of the interval partitioned table "myIntervalPartition1" (partitioning key type: TIMESTAMP, interval: 30 DAY)

  SELECT PARTITION_BOUNDARY_VALUE FROM "#table_partitions"
  WHERE TABLE_NAME='myIntervalPartition1' ORDER BY PARTITION_BOUNDARY_VALUE;
  
  PARTITION_BOUNDARY_VALUE
  -----------------------------------
   2017-01-10T13:00:00.000Z
   2017-02-09T13:00:00.000Z
   2017-03-11T13:00:00.000Z
         :
  

• Delete unnecessary data partitions

  ALTER TABLE myIntervalPartition1 DROP PARTITION FOR ('2017-01-10T13:00:00Z');
  

Interval hash partitioned table

• Check the lower limit value of each data partitions on the interval hash partitioned table "myIntervalHashPartition" (partitioning key type: TIMESTAMP, interval value: 90 DAY, division count 3)

  SELECT PARTITION_BOUNDARY_VALUE FROM "#table_partitions"
  WHERE TABLE_NAME='myIntervalHashPartition' ORDER BY PARTITION_BOUNDARY_VALUE;
  
  PARTITION_BOUNDARY_VALUE
  -----------------------------------
  2016-08-01T10:00:00.000Z    The data of the same lower limit is hashed and
  2016-08-01T10:00:00.000Z    is divided into three data partitions.
  2016-08-01T10:00:00.000Z
  2016-10-30T10:00:00.000Z
  2016-10-30T10:00:00.000Z
  2016-10-30T10:00:00.000Z
  2017-01-29T10:00:00.000Z
         :
  

• Delete unnecessary data partitions

  ALTER TABLE myIntervalHashPartition DROP PARTITION FOR ('2016-09-15T10:00:00Z');
  

• Data partitions that have same boundary value will be deleted

  SELECT PARTITION_BOUNDARY_VALUE FROM "#table_partitions"
  WHERE TABLE_NAME='myIntervalHashPartition' ORDER BY PARTITION_BOUNDARY_VALUE;
  
  PARTITION_BOUNDARY_VALUE
  -----------------------------------
  2016-10-30T10:00:00.000Z    For the section (lower limit '2016-08-01T10: 00: 00Z') including '2016-09-15T10: 00: 00Z'
  2016-10-30T10:00:00.000Z    three data partitions are deleted.
  2016-10-30T10:00:00.000Z
  2017-01-29T10:00:00.000Z
         :
  

2.1.13.3 RENAME COLUMN

Change an existing specified column.

Syntax

Specifications

• An error will occur if the column name before renaming is not in the specified table.
• An error will occur if the column name after renaming is already in the specified table.

Examples

• Renaming a column

  ALTER TABLE myTable1 RENAME COLUMN col112 TO col121;
  

2.2 Data control language (DCL)

2.2.1 GRANT

Assign database access rights to a general user or a role.

Syntax

Specifications

• This command can only be executed by the administrative user.
• SELECT indicates reference authority and ALL indicates reference authority and update authority.

2.2.2 REVOKE

Revoke database access rights from a general user or a role.

Syntax

Specifications

• This command can only be executed by the administrative user.
• SELECT indicates reference authority and ALL indicates reference authority and update authority.

2.2.3 SET PASSWORD

Change the password of a general user.

Syntax

Specifications

• An administrator user can change the passwords of all general users.
• A general user can change its own password only.

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

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

Specifications

• If a SELECT statement is specified instead of VALUES, the execution result will be registered. Except that the inquiry result including UNION/INTERSECT/EXCEPT cannot be registered.

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

2.3.4 UPDATE

Update the rows existing in a table.

Syntax

Specifications

• The value of the PRIMARY KEY column can not be updated.

• For a partitioned table, a column , set as a partitioning key, can not be updated to a different value using the UPDATE statement. In such a case, INSERT after DELETE.

  • Example:

    CREATE TABLE tab (a INTEGER, b STRING) PARTITION BY HASH a PARTITIONS 5;
    
    -- NG
    UPDATE tab SET a = a * 2;
    [240016:SQL_COMPILE_PARTITIONING_KEY_NOT_UPDATABLE] Partitioning column='a' is not updatable
    
    -- OK
    UPDATE tab SET b = 'XXX';
    

• A column name specified with SET cannot be qualified with a table name.

  • Example:

    CREATE TABLE myTable1 (key INTEGER, value INTEGER);
    
    -- NG
    UPDATE myTable1 SET myTable1.value = 999 WHERE myTable1.key = 8;
    
    -- OK
    UPDATE myTable1 SET value = 999 WHERE myTable1.key = 8;
    

• Subqueries cannot be used for update values, while it can be used for conditional statements such as WHERE.

  • Example:

    CREATE TABLE myTable1 (key INTEGER, value INTEGER);
    
    -- NG
    UPDATE myTable1 SET value = (SELECT 999) WHERE key = 8;
    
    -- OK
    UPDATE myTable1 SET value = 999 WHERE key = (SELECT 8);
    

2.4 Clauses

2.4.1 FROM

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

Syntax

Specifications

• A sub query must be enclosed with () and requires an alias.

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

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

2.4.4 ORDER BY

Sort search results.

2.4.5 WHERE

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

Syntax

Specifications

• Search conditions can be described using an expression, a function, a subquery, etc.

2.4.6 LIMIT/OFFSET

Extract the specified number of data from the specified location.

Syntax

Specifications

• Value_1 represents the number of data to extract while value_2 represents the position of the data to extract.

2.4.7 JOIN

Join a table.

Syntax

• Inner join returns records that have matching values in both tables in the specified row.
• Left outer join returns records that have matching values in both tables in the specified row, as well as the records only exist in the table 1.
• A cross join is equivalent to an inner join (INNER JOIN).

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

 department_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.

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

2.4.9 OVER

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

Syntax

Specifications

• Can be used in the SELECT clause.
• The supported functions are as follows:
  • ROW_NUMBER()
  • LAG()
  • LEAD()
  • AVG()
  • COUNT()
  • MAX()
  • MIN()
  • SUM()/TOTAL()
  • STDDEV_SAMP()
  • STDDEV()/STDDEV0()
  • STDDEV_POP()
  • VAR_SAMP()
  • VARIANCE()/VARIANCE0()
  • VAR_POP()
• DISTINCT cannot be specified as the first argument of the function.
• Split the query result with PARTITION BY clause. Sort rows by ORDER BY clause.
• Multiple use of the WINDOW function/OVER clause in the same SELECT clause, and simultaneous use of the WINDOW function/OVER clause and the MEDIAN function are not allowed.
• The following expressions cannot be specified in the PARTITION BY clause.
  • Expression containing OVER clause
  • Expression containing aggregate function
  • Expression containing column aliases
  • Subquery
• The following expressions cannot be specified in the ORDER BY clause.
  • Expression containing OVER clause
  • Expression containing aggregate function
  • Expression containing column aliases
  • Subquery

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.

2.5.2 LIKE

Search the character string on the right.

Syntax

Specifications

• See LIKE function.

2.5.3 GLOB

Syntax

Search the character string on the right.

Specifications

• See GLOB function.

2.5.4 BETWEEN

Extract values of the specified range.

Syntax

Specifications

• Return true if the following conditions are met

  expression_2 <= expression_1 <= expression_3
  

• Return true if the following conditions are not met when NOT is specified.

2.5.5 IN

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

Syntax

Specifications

• Return true when the value of expression_1 is included in the result of expression_N.
• IN can be used in a sub query.

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.

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

table: travelexpenses

 id    date         empId   amount
-----+------------+-------+--------
  101  2020/02/01   0       200
  102  2020/02/03   2       2500
  103  2020/02/03   3       60
  104  2020/02/04   0       200
  105  2020/02/05   0       150
  106  2020/02/06   3       80

[Notice]

• NULL value is expressed as (NULL).

2.6.2 Aggregate functions

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

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

[Notice]

• An aggregate function can be used only for a SELECT phrase.
• If there are no rows to be calculated, the result of COUNT is 0. Other aggregate functions result in NULL.

Aggregate functions are also available with the OVER clause as analytic functions. See OVER clause for details.

Example) Example using aggregate function SUM and OVER clause

SELECT id, date, empId, amount, SUM(amount) OVER(PARTITION BY empID ORDER BY id) as accumulated FROM travelexpenses;
Result:
 id    date         empId   amount   accumulated
-----+------------+-------+--------+-------------
  101  2020/02/01   0       200      200
  104  2020/02/04   0       200      400
  105  2020/02/05   0       150      550
  102  2020/02/03   2       2500     2500
  103  2020/02/03   3       60       60
  106  2020/02/06   3       80       140

[Points to note]

• GROUP_CONCAT and MEDIAN cannot be used with the OVER clause.

2.6.2.1 AVG

Return the average value of n.

• Specify a numeric value as the argument n.
• Rows with n of NULL value are excluded from the calculation.
• The result is of a DOUBLE type.

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

Return the number of rows.

• Rows with x of NULL value are excluded from the calculation. They are not included in the number of rows.
• The result is of a LONG type.

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

Return the maximum.

• Specify the value of arbitrary types as the argument x.
  • For the argument of character string type, the character string started with the largest character code is returned.
  • For the argument of TIMESTAMP type, return the newest time.
• Rows with x of NULL value are excluded from the calculation.
• The type of the result is the same as that of the argument x.

Example:

SELECT MAX(age) FROM employees;
Result: 59

SELECT MAX(first_name) FROM employees;
Result: William

2.6.2.4 MIN

Return the minimum.

• Specify the value of arbitrary types as the argument x.
  • For the argument of character string type, the character string started with the smallest character code is returned.
  • For the argument of TIMESTAMP type, return the oldest time.
• Rows with x of NULL value are excluded from the calculation.
• The type of the result is the same as that of the argument x.

Example:

SELECT MIN(age) FROM employees;
Result: 29

SELECT MIN(first_name) FROM employees;
Result: James

2.6.2.5 SUM/TOTAL

Return a sum of values.

• Specify a numeric value as the argument n.

• Rows with n of NULL value are excluded from the calculation.

• The difference between SUM and TOTAL is as follows.

  • When n includes integer type values only, SUM returns a value of integer (LONG) type, while TOTAL returns a value of floating point number (DOUBLE).
  • When n includes a floating point number type value, both of them return a value of floating point number (DOUBLE).
  • When n includes NULL only, SUM returns NULL, while TOTAL returns 0. while TOTAL returns 0.

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

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.

• Specify the value of arbitrary types as the argument x.
  • A TIMESTAMP type argument is converted to the string representation of time 'YYYY-MM-DDThh:mm:ss.SSS(Z|±hh:mm)' (see TIMESTAMP function) and connected.
• Rows with x of NULL value are excluded from the calculation.
• The result is of a STRING type.

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

Returns the sample standard deviation.

• Specify a numeric value for the argument x.
  • Expressions cannot contain aggregate functions or WINDOW functions/OVER clauses.
• Rows with x of NULL value are excluded from the calculation.
• If x is one, returns NULL.
• The result is of a DOUBLE type.

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

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

• Specify a numeric value for the argument x.
  • Expressions cannot contain aggregate functions or WINDOW functions/OVER clauses.
• Rows with x of NULL value are excluded from the calculation.
• The result is of a DOUBLE type.
• The differences between STDDEV and STDDEV0 are as follows:
  • STDDEV returns NULL if x is 1,
  • while STDDEV0 returns 0 when x is 1.

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

Returns the population standard deviation.

• Specify a numeric value for the argument x.
  • Expressions cannot contain aggregate functions or WINDOW functions/OVER clauses.
• Rows with x of NULL value are excluded from the calculation.
• The result is of a DOUBLE type.

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

Returns the sample variance.

• Specify a numeric value for the argument x.
  • Expressions cannot contain aggregate functions or WINDOW functions/OVER clauses.
• Rows with x of NULL value are excluded from the calculation.
• If x is one, returns NULL.
• The result is of a DOUBLE type.

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

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

• Specify a numeric value for the argument x.
  • Expressions cannot contain aggregate functions or WINDOW functions/OVER clauses.
• Rows with x of NULL value are excluded from the calculation.
• The result is of a DOUBLE type.
• The differences between VARIANCE and VARIANCE0 are as follows:
  • VARIANCE returns NULL if x is 1,
  • while VARIANCE0 returns 0 if x is 1.

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

Returns the population variance.

• Specify a numeric value for the argument x.
  • Expressions cannot contain aggregate functions or WINDOW functions/OVER clauses.
• Rows with x of NULL value are excluded from the calculation.
• The result is of a DOUBLE type.

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

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.

• Specify a numeric value as the argument n.
  • Subqueries cannot be specified.
• Rows with n of NULL value are excluded from the calculation.
• The type of the result is a LONG type when n includes only integers, a DOUBLE type when n includes a floating point number.
• Multiple use of the WINDOW function/OVER clause in the same SELECT clause, and simultaneous use of the WINDOW function/OVER clause and the MEDIAN function are not allowed.

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

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.

• Specify a numeric value as the argument n.
• Return NULL, when the result value is NULL.
• Cause an overflow error, when the value is an integer of -2⁶³.
• The type of the result is a LONG type when n includes only integers, a DOUBLE type when n includes a floating point number.

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

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

• Specify a row of a numeric type as the argument n.
• Specify an integer greater than or equal to 0 as the argument m. When no value is specified for m, the default value 0 is specified.
• Return NULL, when the result value is NULL.
• The type of the result is a LONG type when n includes only integers, a DOUBLE type when n includes a floating point number.

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

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

• The result is of a LONG type.

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

Return the greatest value among the values xN.

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

Returns the logarithm of m with base n.

• For the argument n, specify a numeric value greater than 0 and other than 1.
• For the argument m, specify a numeric value greater than 0.
• Return NULL, when the result value is NULL.
• The result is of a DOUBLE type.

Example:

SELECT LOG(2, 8);
Result: 3.0

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

2.6.3.6 SQRT

Returns the positive square root of n.

• Specify a numeric value of 0 or greater as the argument n.
• Return NULL, when the result value is NULL.
• The result is of a DOUBLE type.

Example:

SELECT SQRT(4);
Result: 2.0

SELECT SQRT(16.0);
Result: 4.0

2.6.3.7 TRUNC

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.

• Specify a numeric value as the argument n.
• Specify an integer as the argument m. When no value is specified for m, the default value 0 is specified. A value greater than 309 or less than -308 cannot be specified.
• Return NULL, when the result value is NULL.
• The result type is LONG if an integer is specified for the argument n and DOUBLE if a decimal is specified.

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

Converts hexadecimal string str to decimal number type.

• Specify a character string type value (0-9, a-f, A-F) that can be converted to hexadecimal for the argument str.
• Return NULL, when the result value is NULL.
• The result is of a LONG 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

Return the length of the character string str.

• Specify character string type values for the argument str.
  • Unicode code point of a character string is used.
• Return NULL, when the result value is NULL.
• The result is of a LONG type.
• A BLOB type can also be specified for an argument.

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

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

• Specify character string type values for the argument str.
• Return NULL, when the result value is NULL.
• The result is of a character string type.
• Unicode characters other than ASCII alphabetic characters are not converted.

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

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

• Specify character string type values for the argument str.
• Return NULL, when the result value is NULL.
• The result is of a character string type.
• Unicode characters, such as Cyrille characters, other than ASCII alphabetic characters are not converted.

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

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".

• Specify character string type values for the argument str.
• Specify an integer, 1 or larger, as the argument index. The starting position at the beginning of a character string is 1.
• When the argument length is not specified, the character strings up to the end of str is cut out.
• Return NULL, when the str value is NULL.
• The result is of a character string type.
• A BLOB type can also be specified for an argument.

Example:

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

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

2.6.4.5 REPLACE

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

• Specify character string type values for the argument search_str, replacement_str.
• Return NULL, when the str value is NULL.
• The result is of a character string type.

Example:

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

2.6.4.6 INSTR

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.

• Specify a string type or BLOB type value for the arguments str and search_str. The values of the same data type must be specified for str and search_str. For the offset and occurrence arguments, specify a LONG value.
• For string type, it is calculated in Unicode code point unit, and for BLOB type, it is calculated in byte unit.
• offset indicates the position where the search starts: for a positive value, the search starts from the front; for a negative value, the search starts from the rear end; when 0 is specified, 0 is returned meaning no match.
• occurrence indicates the number of matches: the search is repeated the specified number of times and the last matched position is returned. when 0 is specified, 0 is returned meaning no match.
• Return NULL, when either of the value of the arguments is NULL.
• The result is of a LONG type.

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

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.

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.

• Specify character string type values for the argument str, pattern_str ,escape_str.
• Return NULL, when either of the value of the arguments is NULL.
• Uppercase and lowercase characters are not distinguished.
• The result is of a BOOL type.

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

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.

• Specify character string type values for the argument str, pattern_str.
• Return NULL, when either of the value of the arguments is NULL.
• Uppercase and lowercase characters are distinguished.
• The result is of a BOOL type.

Example:

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

2.6.4.9 TRIM

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

• Specify character string type values for the argument str and trim_str.
• Delete all the characters contained in the argument trim_str. When no value is specified, spaces are deleted from both ends of str.
• The result is of a character string type.

Example:

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

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

2.6.4.10 LTRIM

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

• Specify character string type values for the argument str and trim_str.
• Delete all the characters contained in the argument trim_str. When no value is specified, spaces are deleted from the left end of str.
• The result is of a character string type.

Example:

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

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

2.6.4.11 RTRIM

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

• Specify character string type values for the argument str and trim_str.
• Delete all the characters contained in the argument trim_str. When no value is specified, spaces are deleted from the right end of str.
• The result is of a character string type.

Example:

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

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

2.6.4.12 QUOTE

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

• For the argument x, specify a value of a character string type, a numeric type, a TIMESTAMP type, and a BLOB type value.
  • For the string type, single quotes contained in the string are escaped into two single quotes ''.
  • For the numeric type, a numeric value is returns as it is. It is not enclosed in single quotes.
  • A TIMESTAMP type argument is converted to the string representation of time 'YYYY-MM-DDThh:mm:ss.SSS(Z|±hh:mm)' (see TIMESTAMP function). It is not enclosed in single quotes.
  • For the BLOB type, return the character string X'BLOB type value'.
• The result is of a character string type.

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

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

• Specify character string type values for the argument str.
• The result is of a LONG type.

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

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

• Specify a Unicode code point value for an argument xn.
• The result is of a STRING type.

Example:

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

2.6.4.15 PRINTF

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.

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

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.

• Specify character string type values for the argument search_str, replacement_str.
• Return NULL, when the result value is NULL.
• The result is of a character string type.

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

Returns the current time value.

• If the time zone is specified at the time of connection, the offset calculated value is returned.
• The result is of a TIMESTAMP type.

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

Convert the string representation of time to TIMESTAMP type.

• In the argument timestamp_string, specify a character string in the following format as a character string representation of time.

  • YYYY-MM-DDThh:mm:ssZ
  • YYYY-MM-DDThh:mm:ss.SSSZ
  • YYYY-MM-DD
  • hh:mm:ss

  Notation	Item	The range of value
  YYYY	Year (A.D.)	1970-
  MM	Month	1 to 12
  DD	Day	1 to 31
  hh	Time (24-hour notation)	0 to 23
  mm	Minute	0 to 59
  ss	Second	0 to 59
  SSS	Millisecond	0 to 999
  Z	Time zone	Z

• For the timezone argument, specify the time zone (Z|±hh:mm|±hhmm), not required when time zone information is included in timestamp_string. An error is returned if the specified values are inconsistent.

• If the time zone is specified at the time of connection, the offset calculated value is returned.

• The result is of a TIMESTAMP type.

• Use CAST for the inverse conversion of TIMESTAMP function (conversion from TIMESTAMP type to string type).

  • CAST(timestamp AS STRING)

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

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

• Specify a TIMESTAMP type value for the argument timestamp.
• Specify an integer for an argument duration. Subtract from the time point, when a negative number is specified.
• Specify one of the following identifiers for the argument time_unit:
  • YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | MILLISECOND
• For the timezone argument, specify the time zone (Z|±hh:mm|±hhmm),
• If the calculated day of the month does not exist as a result of adding a year or a month, the day is rounded to the last day of the month. For example, if one month is added to May 31, the result will be rounded to June 30 because June 31 does not exist.
• If the time zone is specified at the time of connection, the offset calculated value is returned.
• The result is of a TIMESTAMP type.
• TIMESTAMPADD can also be used as a function alias.

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

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.

• Specify a TIMESTAMP type value for the argument timestamp1 and timestamp2.
• Specify one of the following identifiers for the argument time_unit: Instead of calculating the difference only in the unit specified by the identifier, the unit less than the identifier is also used in the calculation. For example, if MONTH is specified and 2019/09/30 is compared with 2019/10/02, the output will be 0 instead of 1 because 2 days of 0 months will be the difference.
  • YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | MILLISECOND
• For the timezone argument, specify the time zone (Z|±hh:mm|±hhmm),
• If the time zone is specified at the time of connection, the offset calculated value is used for the calculation of the difference.
• The result is of a LONG type.
• TIMESTAMPDIFF can also be used as a function alias.

Example:

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

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

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

// Time unit: Minute
SELECT TIMESTAMP_DIFF(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

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.

• Specify an integer for the argument "milliseconds".
• If the time zone is specified at the time of connection, the offset calculated value is returned.
• The result is of a TIMESTAMP type.

Example:

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

2.6.5.6 TO_EPOCH_MS

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.

• Specify a TIMESTAMP type value for the argument timestamp.
• The result is of a LONG type.

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

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

• Specify a TIMESTAMP type value for the argument timestamp.
• Specify one of the following identifiers for the argument time_field:
  • YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | MILLISECOND | DAY_OF_WEEK | DAY_OF_YEAR
    • DAY_OF_WEEK is from Sunday, as 0, to Saturday, as 6.
    • DAY_OF_YEAR is from January first, as 1, to December 31th, as 365 or 366.
• For the timezone argument, specify the time zone (Z|±hh:mm|±hhmm),
• If the time zone is specified at the time of connection, the offset calculated value is returned. If it is also specified in the argument timezone, the one specified in the argument will be used.
• The result is of a LONG type.

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

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

• Specify the following in the format argument to extract time information.

• Specify a TIMESTAMP type value for the argument timestamp.
• For the timezone argument, specify the time zone (Z|±hh:mm|±hhmm),
• The result is of a STRING type.

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

Generate and return a TIMESTAMP type value.

• If the hour, min, and sec arguments are not specified, it is assumed that all 0 have been specified.
• The argument sec can be specified in milliseconds. The value less than a millisecond is rounded, possibly causing a floating point calculation error.
• For the timezone argument, specify the time zone (Z|±hh:mm|±hhmm),
• The result is of a TIMESTAMP type.

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

Truncates the time information.

• Specify one of the following identifiers for the argument field:
  • YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | MILLISECOND
• Specify a TIMESTAMP type value for the argument timestamp.
• For the timezone argument, specify the time zone (Z|±hh:mm|±hhmm),

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

• Use the WINDOW function with the OVER clause. See OVER clause for details.

2.6.6.1 ROW_NUMBER

Assign a unique serial number to the resulting rows.

• Use the function ROW_NUMBER with the clause OVER. For details, see OVER clause.

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.6.2 LAG

Returns x ahead of the current row by offset rows.

• Specify the value of arbitrary types as the argument x.
• For the argument offset, specify a LONG type value:
  0 for the current position, a positive number for the forward position, a negative number for the backward position.
  When no value is specified, offset will be 1.
• Specify the value of arbitrary types as the argument x.
  If the expected row does not exist, default will be returned.
  When no value is specified, default will be NULL.
• The type of the result is the same as that of the argument x.
• Specify the value of the same type for the arguments x and default. There are some different types that can be specified. Refer to CASE for the allowed combination of types.

Example:

SELECT id, date, empId, amount, LAG(amount) OVER(PARTITION BY empID ORDER BY id) as lag_amount FROM travelexpenses;
Result：
 id    date         empId   amount   lag_amount   
-----+------------+-------+--------+------------
  101  2020/02/01   0       200      (NULL)     
  104  2020/02/04   0       200      200        
  105  2020/02/05   0       150      200        
  102  2020/02/03   2       2500     (NULL)     
  103  2020/02/03   3       60       (NULL)     
  106  2020/02/06   3       80       60         

2.6.6.3 LEAD

Returns x behind the current row by offset rows.

• Specify the value of arbitrary types as the argument x.
• For the argument offset, specify a LONG type value:
  0 for the current position, a positive number for the forward position, a negative number for the backward position.
  When no value is specified, offset will be 1.
• Specify the value of arbitrary types as the argument x.
  If the expected row does not exist, default will be returned.
  When no value is specified, default will be NULL.
• The type of the result is the same as that of the argument x.
• Specify the value of the same type for the arguments x and default. There are some different types that can be specified. Refer to CASE for the allowed combination of types.

Example:

SELECT id, date, empId, amount, LEAD(amount) OVER(PARTITION BY empID ORDER BY id) as lead_amount FROM travelexpenses;
Result：
 id    date         empId   amount   lead_amount 
-----+------------+-------+--------+-------------
  101  2020/02/01   0       200      200         
  104  2020/02/04   0       200      150         
  105  2020/02/05   0       150      (NULL)      
  102  2020/02/03   2       2500     (NULL)      
  103  2020/02/03   3       60       80          
  106  2020/02/06   3       80       (NULL)      

2.6.7 Other functions

2.6.7.1 COALESCE

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

• Specify the same type value for the argument xn. There are some different types that can be specified. Refer to CASE for the allowed combination of types.

• Return NULL, when all argument values are NULL.

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

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.

• Specify the value of the same type for the arguments x and y. There are some different types that can be specified. Refer to CASE for the allowed combination of types.
• Return NULL, when all argument values are NULL.

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

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

• Specify the value of the same type for the arguments x and y. There are some different types that can be specified. Refer to CASE for the allowed combination of types.

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

Return a BLOB type value (random number).

• Specify the size (number of bytes) of a BLOB type value as an integer for the argument size.
• The result is of a BLOB type.

Example:

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

2.6.7.5 ZEROBLOB

Return a BLOB type value (0x00).

• Specify the size (number of bytes) of a BLOB type value as an integer for the argument size.
• The result is of a BLOB type.

Example:

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

2.6.7.6 HEX

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.

• Specify a BLOB type and a character string type for the argument x.
  • For a character string type argument, return the character string in which the Unicode code point of all the characters converted to hexadecimal.
• The result is of a character string type.

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

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

• The correspondence between the data type and the string returned by the TYPEOF function is shown below.

  Data types	Character string which TYPEOF function returns
  BOOL	BOOL
  STRING	STRING
  BYTE	BYTE
  SHORT	SHORT
  INTEGER	INTEGER
  LONG	LONG
  FLOAT	FLOAT
  DOUBLE	DOUBLE
  TIMESTAMP	TIMESTAMP
  GEOMETRY	NULL
  BLOB	BLOB
  ARRAY	NULL

• The result is of a character string type.

• When a NULL value is specified, 'NULL' is returned.

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

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

• Specify the following values for argument "data_type" according to the converted data type.

  Converted data type	Value for data_type
  BOOL	BOOL
  STRING	STRING
  BYTE	BYTE
  SHORT	SHORT
  INTEGER	INTEGER
  LONG	LONG
  FLOAT	FLOAT
  DOUBLE	DOUBLE
  TIMESTAMP	TIMESTAMP
  BLOB	BLOB

2.7.1.1 Convert to string type

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.

2.7.1.2 Convert to numeric type

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.

• An error will occur if the converted number exceeds the range of numeric values specified in data_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

• When converted from floating-point type (FLOAT, DOUBLE) to integer type (BYTE, SHORT, INTEGER, LONG), the number of significant digits in the result may be reduced.

SELECT CAST(10.5 AS INTEGER);
Result: 10

• The following character strings can be specified in the conversion from a character string type to a numeric type (case insensitive). An error will occur when character strings other than these are specified.
  • The character string containing a number, a sign (". "," - ", "+"), or "E"
  • "Inf" (signed data acceptable)
  • "Infinity" (signed data acceptable)
  • "NaN"

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

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.

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

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.

2.7.1.5 Convert to BLOB type

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.

2.7.2 CASE

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.

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.

• If the arguments are of different types, only the combination of the following types can be calculated. Any other combinations will result in an error.

  Type of argument	Type of argument	Type of argument when calculating the two arguments
  SHORT	BYTE	LONG
  INTEGER	BYTE, SHORT	LONG
  LONG	BYTE, SHORT, INTEGER	LONG
  FLOAT	BYTE, SHORT, INTEGER, LONG	DOUBLE
  DOUBLE	BYTE, SHORT, INTEGER, LONG, FLOAT	DOUBLE

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

Specifications

• Return true when the value of expression_1 is included in the result of the sub query.
• The result of a sub query must be data of one row.

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

Specifications

• Check whether the execution result of the sub query exists. Return true if the number of execution result is 1 or more, false if it is 0.

• The result is of a BOOL type.

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
);
結果：
  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.

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, 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 SQL tuning guide" (GridDB_SQL_TuningGuide.html) to tune with hint clauses.

2.9.1 Error handling

In the following cases, a syntax error occurs.

• Multiple block comments for hints are described
• The hint is described in the wrong position
• There is a syntax error in the description of the hint phrase
• Duplicate hint of the same class are specified for the same table

In the following case, a table specification error occurs:

• The table specification of the hint phrase is incorrect

[Memo]

• When a table specification error occurs, ignore the error hint phrase and execute the query using the others.
• When a syntax error and a table specification error occur at the same time, a syntax error occurs.

3 Metatables

3.1 About metatables

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

[Memo]

• Metatables can only be referred. It is not allowed to register or delete data in the metatables.
• When SELECT data from the metatables, it is necessary to enclose the table name with double quotation marks.

[Points to note]

• The schema of metatables may be changed in future version.

3.2 Table information

Table information can be obtained.

Table name

#tables

Schema

3.3 Index information

Index information can be obtained.

Table name

#index_info

Schema

3.4 Partitioning information

Data about partitioned tables can be obtained from this metatable.

Table name

#table_partitions

Schema

Specifications

• Each row represents the information of a data partition.
  • For example, when searching rows of a hash partitioned table in which the division count is 128, the number of rows displayed will be 128.
• In the metatable "#table_partitions", the other columns may be displayed besides the above columns.
• It is required to cast the lower limit value to the partitioning key type for sorting by the lower limit value.

Examples

• Check the number of data partitions

  SELECT COUNT(*) FROM "#table_partitions" WHERE TABLE_NAME='myIntervalPartition';
  
  COUNT(*)
  -----------------------------------
   8703
  

• Check the lower limit value of each data partition

  SELECT PARTITION_BOUNDARY_VALUE FROM "#table_partitions" WHERE TABLE_NAME='myIntervalPartition'
  ORDER BY PARTITION_BOUNDARY_VALUE;
  
  PARTITION_BOUNDARY_VALUE
  -----------------------------------
  2016-10-30T10:00:00Z
  2017-01-29T10:00:00Z
          :
  

• Check the lower limit value of each data partitions on the interval partitioned table "myIntervalPartition2" (partitioning key type: INTEGER, interval value: 20000)

  SELECT CAST(PARTITION_BOUNDARY_VALUE AS INTEGER) V FROM "#table_partitions"
  WHERE TABLE_NAME='myIntervalPartition2' ORDER BY V;
  
  PARTITION_BOUNDARY_VALUE
  -----------------------------------
  -5000
  15000
  35000
  55000
    :
  

3.5 View information

View information can be obtained.

Table name

#views

Schema

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

3.7 Information about an event in execution

The information about the event under execution can be obtained.

Table name

#events

Schema

3.8 Connection information

The information about the connected connection can be obtained.

Table name

#sockets

Schema

The following three are available for SOCKET_TYPE (socket type) above:

CREATION_TIME (connection time) in the table above is defined as follows according to each case:

• If SERVICE_TYPE is SQL, CREATION_TIME refers to the time a JDBC connection has started.
• If SERVICE_TYPE is TRANSACTION, CREATION_TIME refers to the time a NoSQL connection has started.

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.

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