Information Functions

CHARSET

CHARSET(expr)

This function returns the character set of expr.

Parameters:

expr – Target expression to get the character set.

Return type:

STRING

SELECT CHARSET('abc');

'iso88591'

SELECT CHARSET(_utf8'abc');

'utf8'

SET NAMES utf8;
SELECT CHARSET('abc');

'utf8'

COERCIBILITY

COERCIBILITY(expr)

This function returns the collation coercibility level of expr. The collation coercibility level determines which collation or charset should be used when each column(expression) has different collation or charset. For more details, please see Collation Coercibility.

Parameters:

expr – Target expression to get the collation coercibility level.

Return type:

INT

SELECT COERCIBILITY(USER());

7

SELECT COERCIBILITY(_utf8'abc');

10

COLLATION

COLLATION(expr)

This function returns the collation of expr.

Parameters:

expr – Target expression to get the collation.

Return type:

STRING

SELECT COLLATION('abc');

'iso88591_bin'

SELECT COLLATION(_utf8'abc');

'utf8_bin'

CURRENT_USER, USER

CURRENT_USER
USER

CURRENT_USER and USER are pseudo-columns and can be used interchangeably. They return the user name that is currently logged in to the database as a string.

Please note that SYSTEM_USER() and USER() functions return the user name with a host name.

Return type:

STRING

--selecting the current user on the session
SELECT USER;

   CURRENT_USER
======================
  'PUBLIC'

SELECT USER(), CURRENT_USER;

   user()                CURRENT_USER
============================================
  'PUBLIC@cdbs006.cora'  'PUBLIC'

--selecting all users of the current database from the system table
SELECT name, id, password FROM db_user;

  name                           id  password
=========================================================
  'DBA'                        NULL  NULL
  'PUBLIC'                     NULL  NULL
  'SELECT_ONLY_USER'           NULL  db_password
  'ALMOST_DBA_USER'            NULL  db_password
  'SELECT_ONLY_USER2'          NULL  NULL

DATABASE, SCHEMA

DATABASE()
SCHEMA()

The functions DATABASE and SCHEMA are used interchangeably. They return the name of currently-connected database as a VARCHAR type.

Return type:

STRING

SELECT DATABASE(), SCHEMA();

   database()            schema()
============================================
  'demodb'              'demodb'

DBTIMEZONE

DBTIMEZONE()

Prints out a timezone of database server (offset or region name) as a string. (e.g. ‘-05:00’, or ‘Europe/Vienna’).

SELECT DBTIMEZONE();

  dbtimezone
======================
  'Asia/Seoul'

See also

SESSIONTIMEZONE(), FROM_TZ(), NEW_TIME(), TZ_OFFSET()

DEFAULT

DEFAULT(column_name)
DEFAULT

The DEFAULT and the DEFAULT function returns a default value defined for a column. If a default value is not specified for the column, NULL or an error is output. DEFAULT has no parameter, however, the DEFAULT function uses the column name as the input parameter. DEFAULT can be used for the input data of the INSERT statement and the SET clause of the UPDATE statement and the DEFAULT function can be used anywhere.

If any of constraints is not defined or the UNIQUE constraint is defined for the column where a default value is not defined, NULL is returned. If NOT NULL or PRIMARY KEY constraint is defined, an error is returned.

CREATE TABLE info_tbl(id INT DEFAULT 0, name VARCHAR);
INSERT INTO info_tbl VALUES (1,'a'),(2,'b'),(NULL,'c');

SELECT id, DEFAULT(id) FROM info_tbl;

           id   default(id)
=============================
            1             0
            2             0
         NULL             0

UPDATE info_tbl SET id = DEFAULT WHERE id IS NULL;
DELETE FROM info_tbl WHERE id = DEFAULT(id);
INSERT INTO info_tbl VALUES (DEFAULT,'d');

Note

In version lower than CoraDB 9.0, the value at the time of CREATE TABLE has been saved when the value of the DATE, DATETIME, TIME, TIMESTAMP column has been specified as SYS_DATE, SYS_DATETIME, SYS_TIME, SYS_TIMESTAMP while creating a table. Therefore, to enter the value at the time of data INSERT in version lower than CoraDB 9.0, the function should be entered to the VALUES clause of the INSERT syntax.

DISK_SIZE

DISK_SIZE(expr)

This function returns the size in bytes required to store the value of expr after evaluation. Main usage is to get necessary size for storing values in database heap file.

Parameters:

expr – Target expression to get the size.

Return type:

INTEGER

SELECT DISK_SIZE('abc'), DISK_SIZE(1);

   disk_size('abc')   disk_size(1)
==================================
                  7              4

The size depends on the actual content of value, string compression is also taken into account:

CREATE TABLE t1(s1 VARCHAR(10), s2 VARCHAR(300), c1 CHAR(10), c2 CHAR(300));
INSERT INTO t1 VALUES(REPEAT('a', 10), REPEAT('b', 300), REPEAT('c', 10), REPEAT('d', 300));
INSERT INTO t1 VALUES('a', 'b', 'c', 'd');
SELECT DISK_SIZE(s1), DISK_SIZE(s2), DISK_SIZE(c1), DISK_SIZE(c2) FROM t1;

   disk_size(s1)   disk_size(s2)   disk_size(c1)   disk_size(c2)
================================================================
              12              24              10             300
               4               4              10             300

INDEX_CARDINALITY

INDEX_CARDINALITY(table, index, key_pos)

The INDEX_CARDINALITY function returns the index cardinality in a table. The index cardinality is the number of unique values defining the index. The index cardinality can be applied even to the partial key of the multiple column index and displays the number of the unique value for the partial key by specifying the column location with the third parameter. Note that this value is an approximate value.

If you want the updated result from this function, you should run UPDATE STATISTICS statement.

Parameters:

  • table – Table name

  • index – Index name that exists in the table

  • key_pos –

    Partial key location. It key_pos starts from 0 and has a range that is smaller than the number of columns consisting of keys; that is, the key_pos of the first column is 0. For the single column index, it is 0. It can be one of the following types.

    • Character string that can be converted to a numeric type.

    • Numeric type that can be converted to an integer type. The FLOAT or the DOUBLE types will be the value converted by the ROUND function.

Return type:

INT

The return value is 0 or a positive integer and if any of the input parameters is NULL, NULL is returned. If tables or indexes that are input parameters are not found, or key_pos is out of range, NULL is returned.

CREATE TABLE t1( i1 INTEGER ,
i2 INTEGER not null,
i3 INTEGER unique,
s1 VARCHAR(10),
s2 VARCHAR(10),
s3 VARCHAR(10) UNIQUE);

CREATE INDEX i_t1_i1 ON t1(i1 DESC);
CREATE INDEX i_t1_s1 ON t1(s1(7));
CREATE INDEX i_t1_i1_s1 on t1(i1,s1);
CREATE UNIQUE INDEX i_t1_i2_s2 ON t1(i2,s2);

INSERT INTO t1 VALUES (1,1,1,'abc','abc','abc');
INSERT INTO t1 VALUES (2,2,2,'zabc','zabc','zabc');
INSERT INTO t1 VALUES (2,3,3,'+abc','+abc','+abc');

UPDATE STATISTICS ON t1;
SELECT INDEX_CARDINALITY('t1','i_t1_i1_s1',0);

   index_cardinality('t1', 'i_t1_i1_s1', 0)
===========================================
                                          2

SELECT INDEX_CARDINALITY('t1','i_t1_i1_s1',1);

   index_cardinality('t1', 'i_t1_i1_s1', 1)
===========================================
                                          3

SELECT INDEX_CARDINALITY('t1','i_t1_i1_s1',2);

   index_cardinality('t1', 'i_t1_i1_s1', 2)
===========================================
                                       NULL

SELECT INDEX_CARDINALITY('t123','i_t1_i1_s1',1);

  index_cardinality('t123', 'i_t1_i1_s1', 1)
============================================
                                       NULL

INET_ATON

INET_ATON(ip_string)

The INET_ATON function receives the string of an IPv4 address and returns a number. When an IP address string such as ‘a.b.c.d’ is entered, the function returns “a * 256 ^ 3 + b * 256 ^ 2 + c * 256 + d”. The return type is BIGINT.

Parameters:

ip_string – IPv4 address string

Return type:

BIGINT

In the following example, 192.168.0.10 is calculated as “192 * 256 ^ 3 + 168 * 256 ^ 2 + 0 * 256 + 10”.

SELECT INET_ATON('192.168.0.10');

   inet_aton('192.168.0.10')
============================
                  3232235530

INET_NTOA

INET_NTOA(expr)

The INET_NTOA function receives a number and returns an IPv4 address string. The return type is VARCHAR.

Parameters:

expr – Numeric expression

Return type:

STRING

SELECT INET_NTOA(3232235530);

   inet_ntoa(3232235530)
======================
  '192.168.0.10'

LAST_INSERT_ID

LAST_INSERT_ID()

The LAST_INSERT_ID function returns the value that has been most recently inserted to the AUTO_INCREMENT column by a single INSERT statement.

Return type:

BIGINT

The value returned by the LAST_INSERT_ID function has the following characteristics.

  • The latest LAST_INSERT_ID value which was INSERTed successfully will be maintained. If it fails to INSERT, there is no change for LAST_INSERT_ID() value, but AUTO_INCREMENT value is internally increased. Therefore, LAST_INSERT_ID() value after the next INSERT statement’s success reflects the internally increased AUTO_INCREMENT value.

    CREATE TABLE tbl(a INT PRIMARY KEY AUTO_INCREMENT, b INT UNIQUE);
INSERT INTO tbl VALUES (null, 1);
INSERT INTO tbl VALUES (null, 1);

    ERROR: Operation would have caused one or more unique constraint violations.

    INSERT INTO tbl VALUES (null, 1);

    ERROR: Operation would have caused one or more unique constraint violations.

    SELECT LAST_INSERT_ID();

    1

-- In 2008 R4.x or before, above value is 3.

    INSERT INTO tbl VALUES (null, 2);
SELECT LAST_INSERT_ID();

    4

  • In the Multiple-rows INSERT statement(INSERT INTO tbl VALUES (), (), …, ()), LAST_INSERT_ID() returns the firstly inserted AUTO_INCREMENT value. In other words, from the second row, there is no change on LAST_INSERT_ID() value even if the next rows are inserted.

    INSERT INTO tbl VALUES (null, 11), (null, 12), (null, 13);
SELECT LAST_INSERT_ID();

    5

    INSERT INTO tbl VALUES (null, 21);
SELECT LAST_INSERT_ID();

    8

  • If INSERT statement succeeds to execute, LAST_INSERT_ID () value is not recovered to its previous value even if the transaction is rolled back.

    -- corasql> ;autocommit off
CREATE TABLE tbl2(a INT PRIMARY KEY AUTO_INCREMENT, b INT UNIQUE);
INSERT INTO tbl2 VALUES (null, 1);
COMMIT;

SELECT LAST_INSERT_ID();

    1

    INSERT INTO tbl2 VALUES (null, 2);
INSERT INTO tbl2 VALUES (null, 3);

ROLLBACK;

SELECT LAST_INSERT_ID();

    3

  • LAST_INSERT_ID() value used from the inside of a trigger cannot be identified from the outside of the trigger.

  • LAST_INSERT_IDis independently kept by a session of each application.

CREATE TABLE ss (id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, text VARCHAR(32));
INSERT INTO ss VALUES (NULL, 'coradb');
SELECT LAST_INSERT_ID ();

     last_insert_id()
=======================
                     1

INSERT INTO ss VALUES (NULL, 'database'), (NULL, 'manager');
SELECT LAST_INSERT_ID ();

     last_insert_id()
=======================
                     2

CREATE TABLE tbl (id INT AUTO_INCREMENT);
INSERT INTO tbl values (500), (NULL), (NULL);
SELECT LAST_INSERT_ID();

     last_insert_id()
=======================
                     1

INSERT INTO tbl VALUES (500), (NULL), (NULL);
SELECT LAST_INSERT_ID();

     last_insert_id()
=======================
                     3

SELECT * FROM tbl;

                    id
=======================
                   500
                     1
                     2
                   500
                     3
                     4

LABEL

LABEL(graph_element)

The LABEL function returns the label (table name) of a graph element (vertex or edge) as a string. This function is commonly used in graph queries with MATCH clauses to identify the type of vertices or edges in query results.

Parameters:

graph_element – A vertex or edge reference from a graph query

Return type:

STRING

The following examples demonstrate the usage of LABEL with vertices and edges.

Setup Example Schema

CREATE VERTEX TABLE person (name STRING, age STRING);
INSERT VERTEX INTO Person VALUES ('Mary', '25');
INSERT VERTEX INTO Person VALUES ('John', '35');

DROP EDGE TABLE IF EXISTS likes;

CREATE EDGE TABLE likes;
INSERT EDGE FROM (SELECT Person FROM Person WHERE name = 'John') TO (SELECT Person FROM Person WHERE name = 'Mary') INTO likes VALUES ();

Example 1: Get labels of vertices and edges

SELECT a.Name, LABEL(a), label(c), b.Name
FROM MATCH (a:Person)-[c:Likes]->(b:Person)
WHERE a.name = 'John'
ORDER BY 1,2,3,4;

This query returns the labels for both the source vertex, the edge, and the destination vertex in the pattern.

Example 2: Filter on source vertex

SELECT a.Name, LABEL(a), b.Name, LABEL(c)
FROM MATCH (a:Person)-[c:Likes]->(b:Person)
WHERE a.name = 'Mary'
ORDER BY 1,2,3,4;

This query shows labels when filtering by different vertex properties.

Example 3: Using path length constraints

SELECT a.Name, LABEL(a), b.Name, LABEL(c)
FROM MATCH (a:Person)-[c:Likes{1,1}]->(b:Person)
ORDER BY 1,2,3,4;

This query demonstrates using LABEL with path length constraints.

Example 4: Multiple edges

INSERT VERTEX INTO Person VALUES ('Mike', '45');
INSERT EDGE FROM (SELECT Person FROM Person WHERE name = 'John') TO (SELECT Person FROM Person WHERE name = 'Mike') INTO likes VALUES ();

SELECT a.Name, LABEL(a), b.Name, LABEL(c)
FROM MATCH (a:Person)-[c:Likes]->(b:Person)
WHERE a.name = 'John'
ORDER BY 1,2,3,4;

This query shows LABEL function with multiple edges from the same source vertex.

Example 6: Error case with string literal

SELECT LABEL('NegativeTest');

This query demonstrates error handling when passing invalid arguments.

Example 7: Error case with Nested LABEL calls

SELECT LABEL(label(a))
FROM MATCH (a:Person)-[c:Likes]->(b:Person)
WHERE a.name = 'John'
ORDER BY 1;

This query demonstrates error handling when using LABEL with nested function calls.

LIST_DBS

LIST_DBS()

The LIST_DBS function outputs the list of all databases in the directory file($CoraDB_DATABASES/databases.txt), separated by blanks.

Return type:

STRING

SELECT LIST_DBS();

  list_dbs()
======================
  'testdb demodb'

PROPERTY_OF

property_of(object, property_name, class_name)

The property_of() function extracts a specific property value from a graph object (vertex or edge). It is primarily used in graph queries with MATCH clauses to access properties of nodes or relationships returned by functions like nodes() or relationships().

Parameters:

  • object – A graph object (vertex or edge reference)

  • property_name – The name of the property to extract

  • class_name – The table/class name that defines the property

Return type:

Type of the specified property

Note

The property_of() function is commonly used with graph functions like nodes(), relationships(), first(), and last() to extract specific properties from graph traversal results.

Test Data Setup

The following examples use this test schema:

CREATE VERTEX TABLE Person (id INT PRIMARY KEY, firstName STRING, lastName STRING, age INT, col1 SEQUENCE(integer));

INSERT VERTEX INTO Person VALUES (0, 'Jerry', 'Madonna', 10, {1, 2, 3});
INSERT VERTEX INTO Person VALUES (1, 'John', 'Doe', 20, {4, 5, 6});
INSERT VERTEX INTO Person VALUES (2, 'Alice', 'Johnson', 40, {7, 8, 9});
INSERT VERTEX INTO Person VALUES (3, NULL, NULL, NULL, {null, null, null});
INSERT VERTEX INTO Person VALUES (4, 'Herb', 'Johan', 43, {6, 3, 34});
INSERT VERTEX INTO Person VALUES (5, 'Sutter', 'Korg', 46, {68, 31, 3});

CREATE EDGE TABLE Knows (id INT NOT NULL AUTO_INCREMENT, source INT, destination INT, remark STRING);

INSERT EDGE FROM (SELECT Person FROM Person WHERE id = 0) TO (SELECT Person FROM Person WHERE id = 1) INTO Knows VALUES (1, 0, 1, 'best friend');
INSERT EDGE FROM (SELECT Person FROM Person WHERE id = 1) TO (SELECT Person FROM Person WHERE id = 2) INTO Knows VALUES (2, 1, 2, 'worst friend');
INSERT EDGE FROM (SELECT Person FROM Person WHERE id = 2) TO (SELECT Person FROM Person WHERE id = 0) INTO Knows VALUES (3, 2, 0, 'most hated');
INSERT EDGE FROM (SELECT Person FROM Person WHERE id = 2) TO (SELECT Person FROM Person WHERE id = 1) INTO Knows VALUES (4, 2, 1, 'close friend');
INSERT EDGE FROM (SELECT Person FROM Person WHERE id = 0) TO (SELECT Person FROM Person WHERE id = 3) INTO Knows VALUES (5, 0, 3, 'distant friend');
INSERT EDGE FROM (SELECT Person FROM Person WHERE id = 3) TO (SELECT Person FROM Person WHERE id = 4) INTO Knows VALUES (6, 3, 4, 'seldom friend');
INSERT EDGE FROM (SELECT Person FROM Person WHERE id = 4) TO (SELECT Person FROM Person WHERE id = 5) INTO Knows VALUES (7, 4, 5, 'somewhat friend');

Examples with Vertex Properties

Example 1: Extract property from first node in path

SELECT property_of(first(nodes(pth)), lastName, Person) AS first_node
FROM MATCH pth=(p1:Person)-[:Knows]->(p2:Person);

This query extracts the lastName property from the first node in each path.

Example 2: Extract multiple properties from path nodes

SELECT property_of(first(nodes(pth)), lastName, Person) AS first_node,
       property_of(last(nodes(pth)), lastName, Person) AS last_node
FROM MATCH pth=(p1:Person)-[k:Knows {2,3}]->(p2:Person)
ORDER BY first_node, last_node;

This query extracts lastName from both the first and last nodes in paths of length 2 or 3.

Example 3: Extract property for filtering

SELECT property_of(first(nodes(pth)), lastName, Person) AS first_node
FROM MATCH pth=(p1:Person)-[k:Knows {1,3}]->(p2:Person)
WHERE 3 IN list_comp(n IN nodes(pth) | property_of(n, id, Person))
ORDER BY first_node;

This query filters paths that contain a person with ID 3, using property_of() in the WHERE clause.

Example 4: Extract sequence property

SELECT property_of(p1, col1, Person) AS sequence_prop
FROM MATCH (p1:Person)-[:Knows]->(p2:Person)
WHERE property_of(p1, id, Person) = 0;

This query extracts a sequence property (col1) from vertices.

Examples with Edge Properties

Example 5: Extract edge property from relationships

SELECT list_comp(e IN relationships(pth) | property_of(e, id, Knows)) AS edge_ids
FROM MATCH pth=(p1:Person)-[k:Knows {2,3}]->(p2:Person)
ORDER BY edge_ids;

This query collects edge IDs from all edges in paths of length 2-3.

Example 6: Extract edge remark property

SELECT property_of(first(relationships(pth)), remark, Knows) AS remark
FROM MATCH pth=(p1:Person)-[k:Knows]->(p2:Person);

This query extracts the remark property from the first edge in each path.

Example 7: Filter by edge property

SELECT property_of(first(relationships(pth)), id, Knows) AS edge_id
FROM MATCH pth=(p1:Person)-[k:Knows {1,3}]->(p2:Person)
WHERE 2 IN list_comp(e IN relationships(pth) | property_of(e, id, Knows))
ORDER BY edge_id;

This query filters paths where edge ID 2 is present.

Examples with NULL Values

Example 8: Handle NULL properties

SELECT property_of(p1, firstName, Person) AS first_name,
       property_of(p1, age, Person) AS age
FROM MATCH (p1:Person)-[:Knows]->(p2:Person)
WHERE property_of(p1, firstName, Person) IS NULL;

This query demonstrates handling NULL values in vertex properties.

Example 9: NULL in edge properties

SELECT property_of(k, source, Knows) AS source_id,
       property_of(k, destination, Knows) AS dest_id
FROM MATCH (p1:Person)-[k:Knows]->(p2:Person)
WHERE property_of(k, remark, Knows) LIKE '%friend%';

This query filters edges based on the remark property value.

Advanced Examples

Example 10: Combine with aggregation

SELECT property_of(p1, lastName, Person) AS person,
       COUNT(*) AS connection_count
FROM MATCH (p1:Person)-[:Knows]->(p2:Person)
GROUP BY property_of(p1, lastName, Person)
ORDER BY connection_count DESC;

This query aggregates results grouped by a property value.

Example 11: Use in subquery

SELECT first_name
FROM (
    SELECT property_of(p1, firstName, Person) AS first_name,
           property_of(p1, age, Person) AS age
    FROM MATCH (p1:Person)-[:Knows]->(p2:Person)
) AS subquery
WHERE age > 20;

This query uses property_of() in a subquery to extract and filter properties.

Example 12: Multiple class names (alternative class reference)

CREATE VERTEX TABLE Primate (id INT NOT NULL PRIMARY KEY, firstName STRING, age DOUBLE);

-- When a property exists in multiple vertex tables, specify the correct class
SELECT property_of(p1, firstName, Person) AS person_name,
       property_of(p1, age, Person) AS person_age
FROM MATCH (p1:Person)-[:Knows]->(p2:Person);

This example shows using the class_name parameter to disambiguate properties when multiple vertex types share property names.

See Also

  • nodes() - Returns a list of vertices in a path (for graph queries)

  • relationships() - Returns a list of edges in a path (for graph queries)

  • first() - Returns the first element of a sequence

  • last() - Returns the last element of a sequence

  • list_comp() - Performs list comprehension operations

  • MATCH clause - For graph pattern matching queries

ROW_COUNT

ROW_COUNT()

The ROW_COUNT function returns the number of rows updated (UPDATE, INSERT, DELETE, REPLACE) by the previous statement.

ROW_COUNT returns 1 for each inserted row and 2 for each updated row for INSERT ON DUPLICATE KEY UPDATE statement. It returns the sum of number of deleted and inserted rows for REPLACE statement.

Statements triggered by trigger will not affect the ROW_COUNT for the statement.

Return type:

INT

CREATE TABLE rc (i int);
INSERT INTO rc VALUES (1),(2),(3),(4),(5),(6),(7);
SELECT ROW_COUNT();

   row_count()
===============
              7

UPDATE rc SET i = 0 WHERE i >  3;
SELECT ROW_COUNT();

   row_count()
===============
              4

DELETE FROM rc WHERE i = 0;
SELECT ROW_COUNT();

   row_count()
===============
              4

SESSIONTIMEZONE

SESSIONTIMEZONE()

Prints out a timezone of session (offset or region name) as a string. (e.g. ‘-05:00’, or ‘Europe/Vienna’).

SELECT SESSIONTIMEZONE();

  sessiontimezone
======================
  'Asia/Seoul'

See also

DBTIMEZONE(), FROM_TZ(), NEW_TIME(), TZ_OFFSET()

USER, SYSTEM_USER

USER()
SYSTEM_USER()

The functions USER and SYSTEM_USER are identical and they return the user name together with the host name.

The USER and CURRENT_USER pseudo-columns return the user names who has logged on to the current database as character strings.

Return type:

STRING

--selecting the current user on the session
SELECT SYSTEM_USER ();

   user()
======================
  'PUBLIC@coradb_host'

SELECT USER(), CURRENT_USER;

   user()                CURRENT_USER
============================================
  'PUBLIC@coradb_host'  'PUBLIC'

--selecting all users of the current database from the system table
SELECT name, id, password FROM db_user;

  name                           id  password
=========================================================
  'DBA'                        NULL  NULL
  'PUBLIC'                     NULL  NULL
  'SELECT_ONLY_USER'           NULL  db_password
  'ALMOST_DBA_USER'            NULL  db_password
  'SELECT_ONLY_USER2'          NULL  NULL

VERSION

VERSION()

The VERSION function returns the version character string representing the CoraDB server version.

Return type:

STRING

SELECT VERSION();

   version()
=====================
  '9.1.0.0203'