SQL commands

ALTER DATABASE

Changes the definition of a database.

Synopsis
ALTER DATABASE
name
RENAME
new_name
Description

ALTER DATABASE supports only the database renaming.

RENAME

Changes the name of an existing database. Renaming does not affect stored data. The new_name parameter must contain a valid name and a database with the same name must not exist.

Parameters
name

Name of the database that is going to be altered. Must be a valid name of an existing database.

new_name

New name of a database. Must be a valid database name and a database with the same name must not exist.

Example 1. Using ALTER DATABASE
List of SQL commands
DROP DATABASE if exists testdb1;
DROP DATABASE if exists testdb2;
CREATE DATABASE testdb1;

USE testdb1;
CREATE TABLE test(x INT, y VARCHAR);
INSERT INTO test (x, y) VALUES (1, 'test1');
INSERT INTO test (x, y) VALUES (2, 'test2');

ALTER DATABASE testdb1 RENAME testdb2;

USE testdb2;
SELECT x, y FROM test;
Result
{"x":1,"y":"test1"}
{"x":2,"y":"test2"}

ALTER TABLE

Changes the definition of a table.

Synopsis
ALTER TABLE [ database . ] name
    RENAME new_name
ALTER TABLE [ database . ]  name
    ATTACH PARTITION partition_name
ALTER TABLE [ database . ]  name
    DETACH PARTITION partition_name
ALTER TABLE [ database . ] name
    DROP PARTITION partition_name
ALTER TABLE [ database . ] name
    PARTITION BY dimensions
Description

ALTER TABLE changes the definition of an existing table.

RENAME

Change the name of an existing table. The database must not already contain a table or view with the same new name. The name you specify must meet the table/view naming criteria. This statement does not modify the table data.

Renaming a table works only within a single database. The destination database must be the same as the source.
ATTACH PARTITION

Attach a previously detached table partition. The partition’s data is moved from the _detached folder to the table’s root directory.

DETACH PARTITION

Detaches the specified table partition, taking the partition data offline. The partition data is moved to the _detached folder in the table’s data directory.

DROP PARTITION

Remove the specified partition and delete all partition data from the disk. You must detach a partition before you can delete it. This operation is irreversible.

PARTITION BY

Modify the table partition dimensions for new records inserted into the table. Existing partitions remain unaltered. Refer to Table partitioning for more information.

Parameters
database

Specifies the database which table should be altered. If this parameter is not specified, the current database is used.

name

Name of the table to be altered. Must be a valid name of the existing table.

new_name

New name of a table. Must be a valid table name and a table with the same name must be non-existent.

partition_name

Name of the partition the command will be executed on. Must be a valid name of the existing partition.

Example 2. Using ALTER TABLE
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE sample();
SHOW TABLES;
ALTER TABLE sample RENAME renamed;
SHOW TABLES;
Result
{"table":"sample","record_count":0,"encrypted":"no","partitioning_type":"none","size":27}
{"table":"renamed","record_count":0,"encrypted":"no","partitioning_type":"none","size":27}

ALTER USER

Change the password of a user.

Synopsis
ALTER USER username SET PASSWORD TO new_password
Description

ALTER USER modifies the password of an existing user.

Parameters
username

Name of an existing user. It must not exceed 20 characters. Only numbers, letters, dash (-), and underscore (_) are accepted.

new_password

The new password. It must be at least four characters long and contain one uppercase letter, one lowercase letter, one number, and a symbol. The maximum password length is 32 characters.

Example 3. Using ALTER USER
ALTER USER 'foo' SET PASSWORD TO '1234_Raijin';

ALTER VIEW

Changes the definition of a view.

Synopsis
ALTER VIEW [ database . ] name
    RENAME new_name
Description

ALTER VIEW changes the definition of an existing view. Currently, only the RENAME command is supported.

RENAME

Change the name of an existing view. The database must not already contain a table or view with the same new name. The name you specify must meet the table/view naming criteria.

Renaming a view works only within a single database. The destination database must be the same as the source.
Parameters
database

Optional parameter to specify the database where the view resides. It defaults to the current database.

name

Name of an existing view to alter.

new_name

A valid view name. A view with the same name cannot already exist.

Example 4. Using ALTER VIEW
List of commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE events(
    Hostname VARCHAR(20),
    EventTime TIMESTAMP,
    EventType VARCHAR(10),
    Message TEXT
);

CREATE VIEW error_logs AS
    SELECT * FROM events
    WHERE EventType = 'ERROR';

ALTER VIEW error_logs RENAME errors_view;

COPY

Copies data across files in various formats.

Synopsis
COPY [ database . ] table
    [ FROM | TO ] file
    [ WITH ( option = value ) ]
Description

To save data to file, the user must have write permissions. Data is recorded based on either the file format or the command options. By default, all columns are copied unless otherwise specified in the options.

To read data from a file, the user must have read permissions. Data is read and appended to a table in the given format based on either the command options or the file extension.

To parse CSV data, the column names must be explicitly specified in the options. If a column does not exist in the table schema, it must also be specified in the options.

While parsing JSON data, this command automatically takes column names from the file description.

Upon successful completion, COPY returns the number of rows copied.

If the copy command includes duplicate keys, the last value with the given key is copied into the table. Previous values with the same key are discarded.
Parameters
database

Database name to copy data. If not specified, the current database is used.

table

Table name to copy data. Must be the name of an existing table.

file

Name of the file for reading or writing. The user must have read and write permissions.

option and value

Key-value pairs of the options and their values as described below.

Options

The following options are common for both CSV and JSON formats:

format

File format. Can be either CSV or JSON.

partition

Partition of the table to copy data. This option is ignored when copying to a file.

onerror

Error handling strategy. Can be either warn (default) or abort. This option is ignored when copying to a file.

The following options are applicable only for processing data in CSV format:

header

Boolean flag. If set to TRUE, this option enables skipping the first line of a copied file. When copying to a file, it generates a header line. Applicable only for CSV format.

quote

The quote character for a string. Applicable only for CSV format.

escape

The escape character for a string. Applicable only for CSV format.

delimiter

Column delimiter character in CSV data.

null

Value which represents NULL. If such a value is parsed, it will be replaced with NULL. Applicable only for CSV format.

force_not_null

Boolean flag. If set to TRUE, this option enables parsing a null or empty value as an empty string. Applicable only for CSV format.

force_quote

Boolean flag. If set to TRUE, this option enables quoting of all copied values. Applicable only for CSV format.

fields

List of fields which is used for mapping while reading or writing data to file. Applicable only for CSV format.

field_types

Field types. This option needs to be specified after the fields parameter. Applicable only for CSV format.

Example 5. Using COPY with JSON format
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;

CREATE TABLE IF NOT EXISTS testdb.events();

COPY testdb.events FROM 'data/logs.json';

USE testdb;
SELECT count(*) FROM events;
SELECT count("EventTime") FROM events;
Example 6. Using COPY with CSV format
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;

CREATE TABLE IF NOT EXISTS testdb.test(a INT, b FLOAT, c VARCHAR, d BOOL);

COPY testdb.test FROM 'data/data.csv' WITH(FORCE_NOT_NULL {c,b}, NULL='X', DELIMITER='#', ESCAPE='$', QUOTE='!', FORMAT=CSV);
SELECT a, b, c, d FROM testdb.test;

CREATE DATABASE

Creates a new database.

Synopsis
CREATE DATABASE [ IF NOT EXISTS ] name
Description

Creates a new database with the given name. The database name must be unique unless IF NOT EXISTS is specified. If a table with the same name exists, the new database will not be created.

Example 7. Using CREATE DATABASE
List of SQL commands
DROP DATABASE IF EXISTS testdb1;

CREATE DATABASE testdb1;
CREATE DATABASE IF NOT EXISTS testdb1;
USE testdb1;

DROP DATABASE testdb1;
DROP DATABASE IF EXISTS testdb1;

CREATE TABLE

Creates a new table with or without an associated schema.

Synopsis
CREATE [ ENCRYPTED ] TABLE [ IF NOT EXISTS ] [ database . ] name
    ( [ field_name field_typ ] [, ...] ) [ PARTITION BY dimensions ]
Description

Creates a new table using the name given. Unless the IF NOT EXISTS clause is specified, the created table must be non-existent.

ENCRYPTED

Encrypt the table data. Refer to the data encryption configuration settings for more information.

Parameters
database

Name of the database, in which this table will be created. If not specified, the current database is used.

name

Name of the table to be created. Table names beginning with numerals must be enclosed in double-quotes.

field_name, field_type
field_name

Name of a field to be created in this table. Each field name must be unique within a table.

field_type

Field type. See Data types.

dimensions

One or more fieds or expressions. Refer to Table partitioning for more information.

Example 8. Using CREATE TABLE
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;

USE testdb;
CREATE TABLE IF NOT EXISTS events_test(
    ID INT,
    Name VARCHAR(100),
    Birthday TIMESTAMP
);

CREATE TABLE IF NOT EXISTS events_none();

CREATE USER

Creates a user with the default privileges.

Synopsis
CREATE USER username IDENTIFIED WITH
[ PASSWORD | SSL_CERTIFICATE ]
[ user_password | CN certificate_cn ]
Description

Creates a new user with password or certificate-based authentication.

Parameters
username

It must not exceed 20 characters. Only numbers, letters, dash (-), and underscore (_) are accepted.

user_password

It must be at least four characters long and contain one uppercase letter, one lowercase letter, one number, and a symbol. The maximum password length is 32 characters.

ssl_certificate_cn

The TLS/SSL certificate Common Name.

Example 9. Using CREATE USER
List of SQL commands
CREATE USER 'foo' IDENTIFIED WITH PASSWORD '1234_Raijin';

CREATE USER 'foo' INDENTIFIED WITH SSL_CERTIFICATE CN 'ssl-certificate-common-name';

CREATE VIEW

Saves an SQL query as a view. The query runs every time you use the view in a query.

Synopsis
CREATE [ OR REPLACE ] VIEW [ database . ] name AS query
Description

Create a new view with the given name or replace an existing one with the same name. The database must not already contain a table or view with the same name.

OR REPLACE

Replace the view if it already exists.

Parameters
database

Optional parameter to specify the database where to create the view. It defaults to the current database.

name

Unique name of the new view.

query

The SQL query to build the view.

Example 10. Using CREATE VIEW
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE events(
    Hostname VARCHAR(20),
    EventTime TIMESTAMP,
    EventType VARCHAR(10),
    Message TEXT
);

INSERT INTO events VALUES
    ('SRV01', '2023-07-05 00:00:00', 'ERROR', 'This is an error');

CREATE VIEW error_logs AS
    SELECT * FROM events
    WHERE EventType = 'ERROR';

SELECT * FROM error_logs;
Result
{
  "error_logs._id": 1,
  "error_logs.hostname": "SRV01",
  "error_logs.eventtime": "2023-07-05T00:00:00.000000+02:00",
  "error_logs.eventtype": "ERROR",
  "error_logs.message": "This is an error"
}

DESCRIBE

Displays information about all columns in a table.

Synopsis
DESCRIBE name
Description

Displays all columns from the name table, even the ones not present in the original CREATE TABLE statement. Note that the similar SELECT FIELDS statement displays only the fields that contain non-null data.

Example 11. Using DESCRIBE name
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;

USE testdb;
CREATE TABLE tbl (
    ID int ,
    Name varchar(100) ,
    Birthday timestamp
);

INSERT into tbl(A) values(1);

DESCRIBE tbl;
Result
{"field":"id","type":"INT"}
{"field":"name","type":"STRING"}
{"field":"birthday","type":"TIMESTAMP"}
{"field":"a","type":"INT"}
See also

SELECT FIELDS

DROP DATABASE

Deletes a database.

Synopsis
DROP DATABASE [ IF EXISTS ] name
Description

Deletes the database with the given name. If the database doesn’t exist, it will return an error unless IF EXISTS is specified. If the table has dependent views in other databases, it will not delete it and return an error.

Example 12. Using DROP DATABASE
List of SQL commands
DROP DATABASE IF EXISTS testdb1;

CREATE DATABASE testdb1;
CREATE DATABASE IF NOT EXISTS testdb1;
USE testdb1;

DROP DATABASE testdb1;
DROP DATABASE IF EXISTS testdb1;

DROP TABLE

Deletes a table from the database.

Synopsis
DROP TABLE [ IF EXISTS ] [ database . ] name
Description

Deletes the table with the given name. If the table doesn’t exist, it will return an error unless IF EXISTS is specified. If the table has dependent views, it will not delete it and return an error.

Example 13. Using DROP TABLE
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE tbl_one ();
CREATE TABLE tbl_two ();

SHOW TABLES;
DROP TABLE tbl_one;
SHOW TABLES;
Result
{"table":"tbl_one","record_count":0,"encrypted":"no","partitioning_type":"none","size":27}
{"table":"tbl_two","record_count":0,"encrypted":"no","partitioning_type":"none","size":27}
{}
{"table":"tbl_two","record_count":0,"encrypted":"no","partitioning_type":"none","size":27}
Example 14. Deleting a table with dependent views
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE tbl();
CREATE VIEW sample_view as SELECT * FROM tbl;
DROP TABLE tbl; -- Will result in an error because sample_view depends on tbl

DROP USER

Delete an existing user.

Synopsis
DROP USER username
Description

Deletes the user with the given username. This command applies to users with password or certificate-based authentication. If the user doesn’t exist, it will return an error.

Example 15. Using DROP USER
The SQL command to drop USER foo
DROP USER 'foo';

DROP VIEW

Deletes a view from the database.

Synopsis
DROP VIEW [ IF EXISTS ] [ database . ] name
Description

Deletes the view with the given name. If the view doesn’t exist, it will return an error unless IF EXISTS is specified. If the view has dependent views, it will not delete it and return an error.

Example 16. Using DROP VIEW
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE tbl ();
CREATE VIEW sample_view AS SELECT * FROM tbl;
DROP VIEW sample_view;

EXPLAIN

Explains operations in the given query.

Synopsis
EXPLAIN query
Description

Returns the execution plan for the given query.

Parameters
query

Query to be explained. Must be a valid query.

Example 17. Using EXPLAIN
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE events(
    Hostname VARCHAR(20),
    EventTime TIMESTAMP,
    EventType VARCHAR(10),
    Message TEXT
);

EXPLAIN SELECT Message FROM events WHERE date(EventTime + 2) = '2016-10-25';
EXPLAIN SELECT is_even(EventTime) FROM events;

EXPLAIN INSERT INTO events VALUES('Host', '2019-07-24 00:00:00', 'Log in', 'test');
Result
{"plan":"
Result
[message]
  SequentialScan on events
  . Filter: date(eventtime + INT32(2)) = STRING(2016-10-25)
  [message]"}
{"plan":"
Result
[is_even: is_even(eventtime)]
  SequentialScan on events
  [eventtime]"}
{"plan":"
Insert on 'events'"}

FLUSH TABLE

Writes tables from memory to disk.

Synopsis
FLUSH { TABLE | TABLES } [ [ database . ] name ] [, ... ]
FLUSH TABLES WITH READ LOCK
Description

Writes either all or specified tables from memory to disk. If a named table does not exist, no error occurs. The FLUSH TABLE command is a synonym for FLUSH TABLES.

WITH READ LOCK

Prevents any further write transactions on all tables in all databases. If you execute this command from a client, e.g., the Raijin user interface, the client will wait for a response to any write transaction until the tables are unlocked with UNLOCK TABLES. You can unlock the tables from another client instance, e.g., by opening the Raijin user interface in another tab.

Example 18. Using FLUSH TABLE
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE tbl1(a INTEGER);
CREATE TABLE tbl2(b INTEGER);
CREATE TABLE tbl3(c INTEGER);

INSERT INTO tbl1(a) VALUES (1);
INSERT INTO tbl2(b) VALUES (2);
FLUSH TABLES tbl1, sampledb.tbl2;

INSERT INTO tbl3(c) VALUES (3);
FLUSH TABLES;

GRANT

Grant privileges to a user.

Synopsis
GRANT [ ALL PRIVILEGES | ( privilege [, ...] ) ] ON scope TO username
Description

Grant an existing user privileges to a database or table.

Parameters
privilege

One or more privileges from CREATE, SELECT, INSERT, DROP, ALTER, MAINTENANCE, and METADATA. See Granting privileges for more information.

scope

The database or table the privilege applies to. It supports the * wildcard for any database or table.

Scope syntax Explanation

*

All tables in the current database. You must set the database with the USE command before using this syntax.

*.*

All tables in all databases.

db.*

All tables in database db.

db.tbl

Table tbl in database db.

username

The username you want to assign the privilege to.

Example 19. Using GRANT
GRANT CREATE, SELECT ON db.tbl TO 'foo';
GRANT ALL PRIVILEGES ON *.* TO 'bar';

INSERT

Inserts data into a table.

Synopsis
INSERT INTO table [ ( field [, ...] ) ]
    [ VALUES (expression [, ...] ) [, ...] |
    SET field = expression [, ...] |
    JSON |
    query ]
    [ WITH ( option = value ) ]
Description

Inserts data into the given table. Multiple syntaxes are supported for definition of the inserted data.

In all of these syntaxes, the data type of expression/value must be compatible with the data type of the column if being defined. Otherwise, any data type is accepted.

If the insert command includes duplicate keys, the last value with the given key is inserted into the table. Previous values with the same key are discarded.
The column name "_id" is reserved for Raijin primary key column. Insertions into "_id" column are prohibited and lead to the error generation: 'Column name "_id" is reserved for internal Raijin primary key field'.
VALUES

While using the VALUES syntax, a list of fields must be provided. Each expression corresponds to a field in the field list. The number of values in each expression list is fixed to the number of fields in the field list.

SET

While using the SET syntax, there is no need to predefine the fields used, since every expression has its own field definition. This also means that the number and order of fields may vary.

JSON

A valid JSON or comma-separated list of valid JSON objects or arrays of objects. JSON5’s Infinity, -Infinity, and NaN are allowed. Nested arrays that have no keys will be flattened, for example:

[{"row1":1}, [{"row2":2}, [{"row3":3}]]];

is equal to:

{"row1":1}, {"row2":2}, {"row3":3}

Nested arrays with keys will be stored as strings in the column specified as a key.

Parameters
table

Must be a name of an existing table in the current database.

field

Name of the field that is being inserted. Inserting into undefined columns is supported.

expression

The evaluated expression.

option and value

Key-value pairs of the options and their values are described below.

Options
partition

The partition name to insert data into. If the partition does not exist, it will be automatically created. This option is only available for tables with manual partitioning.

Example 20. Using INSERT
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE events(
    hostname STRING,
    eventtime TIMESTAMP,
    eventtype STRING,
    message STRING
);

-- Using VALUES syntax
INSERT INTO events VALUES
    ('srv-04', '2016-08-03 17:08:24Z', 'INFO', 'Server initialized successfully.');

-- Using SET syntax
INSERT INTO events SET
    hostname = 'srv-04',
    eventtime = '2016-08-03 11:37:23Z',
    eventtype = 'WARNING',
    message = 'Invalid login credentials from user "john".';

-- Using JSON syntax
INSERT INTO events
    -- JSON object
    {"hostname":"srv-04", "eventtime":"2015-06-24 12:20:12Z", "eventtype":"ERROR", "message": "Database timeout!"},
    -- JSON array
    [{"eventtime":"2015-06-24 12:20:13Z", "message": "Test"},
     {"eventtime":"2015-06-24 12:20:14Z", "message": "Test 2"}];


SELECT hostname, FORMAT_UTC_USEC(EventTime) as EventTime, eventtype, message FROM events;
Result
{"hostname":"srv-04","eventtime":"2016-08-03T17:08:24.000000Z","eventtype":"INFO","message":"Server initialized successfully."}
{"hostname":"srv-04","eventtime":"2016-08-03T11:37:23.000000Z","eventtype":"WARNING","message":"Invalid login credentials from user \"john\"."}
{"hostname":"srv-04","eventtime":"2015-06-24T12:20:12.000000Z","eventtype":"ERROR","message":"Database timeout!"}
{"hostname":null,"eventtime":"2015-06-24T12:20:13.000000Z","eventtype":null,"message":"Test"}
{"hostname":null,"eventtime":"2015-06-24T12:20:14.000000Z","eventtype":null,"message":"Test 2"}

SELECT

Retrieves records from tables or views.

Synopsis
[ DISTINCT [ ON ( expression [, ...] ) ] | ALL ]
[ * | expression [ AS name ] ] [, ...]
[ FROM table | view [ AS alias [, ...] ] ]
[ JOIN table [ ON expression | USING(expression) ] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition ]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ LIMIT expression | FETCH FIRST [ ( number ) ] [ ROW | ROWS ] ONLY ]
[ OFFSET expression [ ROW | ROWS ] ]
Description

Retrieves rows from a single table, view, or a combination of both. You can also use SELECT to project columns and values.

Select list

Using the asterisk character (*) will select all fields from the table or view.

All expressions in the select list will be evaluated against each table/view record specified in the FROM clause.

Example 21. Using SELECT to retrieve data from a table
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE events(Hostname STRING, Message STRING);
INSERT INTO events {"Hostname":"localhost"};

SELECT
    "Hostname",
    Hostname,
    2+5*2 expression1,
    2/4+1 AS expression2
FROM events;
Result
{
  "Hostname": "localhost",
  "hostname": null,
  "expression1": 12,
  "expression2": 1.5
}
Example 22. Using SELECT to project values
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;


CREATE TABLE tbl ();
INSERT INTO tbl {"a":1, "b":2};

SELECT 1 AS found HAVING
    (SELECT a FROM tbl  WHERE a = 1) > 0;
Result
{
  "found": 1
}
DISTINCT clause

With the DISTINCT clause specified, the records or columns with duplicate values will be filtered out.

If either ALL or no clause are specified, the records will be entirely checked for duplicates.

In case ON is specified, only the given columns will be checked for duplicates.

Example 23. Using SELECT with DISTINCT
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE testtbl();
INSERT INTO testtbl {"data":1};
INSERT INTO testtbl {"data":1};
INSERT INTO testtbl {"data":1};
INSERT INTO testtbl {"data":2};

SELECT DISTINCT data FROM testtbl;
Result
{"data":1}
{"data":2}
FROM clause

Specifies the tables and/or views to retrieve data from. If multiple tables or views are specified, they will be cross-joined.

WHERE clause

A condition that all retrieved records must satisfy. Records that do not satisfy the condition will be filtered out.

Example 24. Using SELECT with WHERE clause
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE events(LogLevel INTEGER, Message STRING);
INSERT INTO events (LogLevel, Message) VALUES (1, 'It''s OK');
INSERT INTO events (LogLevel, Message) VALUES (2, 'Beware');
INSERT INTO events (LogLevel, Message) VALUES (3, 'Something went wrong');

SELECT e.LogLevel, e.message FROM events e WHERE LogLevel > 1
Result
{"loglevel":2,"message":"Beware"}
{"loglevel":3,"message":"Something went wrong"}
JOIN clause

Retrieves records from multiple tables linked by a common field. Supported JOIN types are INNER JOIN, LEFT JOIN, RIGHT JOIN, and CROSS JOIN. The first three require one of the following join conditions:

  • ON to match one or more fields or arbitrary conditions. The field names can differ from one table to another.

  • USING to match a single field. The field names must be identical in both tables.

Example 25. Using SELECT with JOIN clause
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE logs(LogLevel INTEGER, Message STRING);
INSERT INTO logs (LogLevel, Message) VALUES (6, 'It''s OK');
INSERT INTO logs (LogLevel, Message) VALUES (4, 'Beware');
INSERT INTO logs (LogLevel, Message) VALUES (2, 'This is critical');

CREATE TABLE loglevels(Num INTEGER, LevelName VARCHAR(10));
INSERT INTO loglevels (Num, LevelName) VALUES (7, 'Debug');
INSERT INTO loglevels (Num, LevelName) VALUES (6, 'Info');
INSERT INTO loglevels (Num, LevelName) VALUES (4, 'Warning');
INSERT INTO loglevels (Num, LevelName) VALUES (3, 'Error');

CREATE TABLE facilities(Num INTEGER, Facility VARCHAR(10));
INSERT INTO facilities (Num, Facility) VALUES (0, 'kernel');

SELECT 'INNER JOIN' _;
SELECT logs.Message, loglevels.LevelName FROM logs INNER JOIN loglevels ON logs.LogLevel = loglevels.num;
SELECT 'LEFT JOIN' _;
SELECT logs.Message, loglevels.LevelName FROM logs LEFT JOIN loglevels ON logs.LogLevel = loglevels.num;
SELECT 'RIGHT JOIN' _;
SELECT logs.Message, loglevels.LevelName FROM logs RIGHT JOIN loglevels ON logs.LogLevel = loglevels.num;
SELECT 'CROSS JOIN' _;
SELECT facilities.num, facilities.facility, loglevels.num, loglevels.levelname FROM facilities CROSS JOIN loglevels;
Result
{"_":"INNER JOIN"}
{"logs.message":"It's OK","loglevels.levelname":"Info"}
{"logs.message":"Beware","loglevels.levelname":"Warning"}
{"_":"LEFT JOIN"}
{"logs.message":"It's OK","loglevels.levelname":"Info"}
{"logs.message":"Beware","loglevels.levelname":"Warning"}
{"logs.message":"This is critical","loglevels.levelname":null}
{"_":"RIGHT JOIN"}
{"logs.message":null,"loglevels.levelname":"Debug"}
{"logs.message":"It's OK","loglevels.levelname":"Info"}
{"logs.message":"Beware","loglevels.levelname":"Warning"}
{"logs.message":null,"loglevels.levelname":"Error"}
{"_":"CROSS JOIN"}
{"facilities.num":0,"facilities.facility":"kernel","loglevels.num":7,"loglevels.levelname":"Debug"}
{"facilities.num":0,"facilities.facility":"kernel","loglevels.num":6,"loglevels.levelname":"Info"}
{"facilities.num":0,"facilities.facility":"kernel","loglevels.num":4,"loglevels.levelname":"Warning"}
{"facilities.num":0,"facilities.facility":"kernel","loglevels.num":3,"loglevels.levelname":"Error"}
GROUP BY clause

Merges records into groups by one or more expressions. Expressions that appear in the select list must be either aggregate functions or appear in the GROUP BY clause. Expressions which do not satisfy that requirement will be implicitly wrapped into the FIRST aggregate function.

Although it is possible to use column aliases in the GROUP BY clause:

SELECT a, mod(b, 7) AS res FROM tbl GROUP BY res;

But such usage is restricted and may give unexpected results when the alias is used as part of an expression. For this reason, it is better to use select-list ordinal position:

SELECT a, mod(b, 7) FROM tbl GROUP BY 3;
Example 26. Using SELECT with the GROUP BY clause
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE events(LogLevel INTEGER, Message STRING);
INSERT INTO events VALUES (1, 'It''s OK');
INSERT INTO events VALUES (2, 'Beware');
INSERT INTO events VALUES (2, 'Warning');
INSERT INTO events VALUES (3, 'Something went wrong');

SELECT GROUP_CONCAT(e.message) messages FROM events e WHERE LogLevel > 1 GROUP
BY LogLevel;
Result
{"messages":"Beware,Warning"}
{"messages":"Something went wrong"}
HAVING clause

This clause is similar to WHERE, but applied to each group, i.e. is used to filter out the groups which do not satisfy it.

Example 27. Using SELECT with the HAVING clause
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE events(LogLevel INTEGER, Message STRING);
INSERT INTO events VALUES (1, 'It''s OK');
INSERT INTO events VALUES (2, 'Beware');
INSERT INTO events VALUES (2, 'Warning');
INSERT INTO events VALUES (2, 'It''s not good');
INSERT INTO events VALUES (3, 'Ooops');
INSERT INTO events VALUES (3, 'Something went wrong');

SELECT GROUP_CONCAT(message) messages FROM events GROUP BY LogLevel HAVING COUNT(*) > 1;
Result
{"messages":"Beware,Warning,It's not good"}
{"messages":"Ooops,Something went wrong"}
ORDER BY clause

Sorts the resulting rows by the given columns in ascending (ASC) or descending (DESC) order.

Example 28. Using SELECT with the ORDER BY clause
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE numbers(value INTEGER);

INSERT INTO numbers (value) VALUES (63);
INSERT INTO numbers (value) VALUES (100);
INSERT INTO numbers (value) VALUES (86);
INSERT INTO numbers (value) VALUES (3);

SELECT value FROM numbers ORDER BY value DESC;
Result
{"value":100}
{"value":86}
{"value":63}
{"value":3}
LIMIT clause

Limits the number of records that can be retrieved.

If ALL is given as a clause, there will be no limit on the number of records that can be retrieved.

Example 29. Using SELECT with the LIMIT clause
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE numbers(value INTEGER);

INSERT INTO numbers (value) VALUES (1);
INSERT INTO numbers (value) VALUES (2);
INSERT INTO numbers (value) VALUES (3);
INSERT INTO numbers (value) VALUES (4);
INSERT INTO numbers (value) VALUES (5);
INSERT INTO numbers (value) VALUES (6);

SELECT 'LIMIT 1:' _;
SELECT value FROM numbers LIMIT 1;
SELECT 'FETCH FIRST 2 ROWS:' _;
SELECT value FROM numbers FETCH FIRST (1+1) ROWS ONLY;
SELECT 'FETCH FIRST ROW:' _;
SELECT value FROM numbers FETCH FIRST ROW ONLY;
SELECT 'SKIPPING FIRST 4:' _;
SELECT value FROM numbers OFFSET 4;
SELECT 'MIXED LIMITING:' _;
SELECT value FROM numbers OFFSET 3 LIMIT 2;
Result
{"_":"LIMIT 1:"}
{"value":1}
{"_":"FETCH FIRST 2 ROWS:"}
{"value":1}
{"value":2}
{"_":"FETCH FIRST ROW:"}
{"value":1}
{"_":"SKIPPING FIRST 4:"}
{"value":5}
{"value":6}
{"_":"MIXED LIMITING:"}
{"value":4}
{"value":5}
FETCH FIRST number [ROW | ROWS] ONLY

This is an alternative syntax for the LIMIT clause. If the number is concluded inside parentheses, the expression will be evaluated; otherwise, it must be a constant. See the SELECT command with the LIMIT clause example.

OFFSET clause

Skips the number of records that the given expression is evaluated to. See the SELECT command with the LIMIT clause example.

SELECT FIELDS

Displays information about the columns which contain non-null data.

Synopsis
SELECT FIELDS FROM [ database . ] name [ WHERE condition ]
Description

Displays the names and types of columns that contain non-null data. It looks at either the whole table, or the selected rows only. Note that the similar DESCRIBE statement displays the names and types of all columns in a table.

Example 30. Using SELECT FIELDS
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;

USE testdb;
CREATE TABLE tbl (
    ID int ,
    Name varchar(100) ,
    Birthday timestamp
);

INSERT into tbl(ID, A) values(1, 2);
SELECT 'Whole table (two columns inserted)................................1' _;
SELECT FIELDS FROM tbl;

INSERT into tbl(Name, B) values('abc', 3);
FLUSH TABLE;
INSERT into tbl(Name, B) values('d', 3.4);
SELECT 'Whole table (all columns inserted)................................2' _;
SELECT FIELDS FROM testdb.tbl;

SELECT 'Using WHERE.......................................................3' _;
SELECT FIELDS FROM tbl WHERE A = ID + 1;

SELECT 'Using WHERE (B is INT32)..........................................4' _;
SELECT FIELDS FROM testdb.tbl WHERE Name = 'abc';

SELECT 'Using WHERE (B is DOUBLE).........................................5' _;
SELECT FIELDS FROM testdb.tbl WHERE Name = 'd';

SELECT 'Using WHERE (B is VARIANT)........................................6' _;
SELECT FIELDS FROM testdb.tbl WHERE B > 2 and B < 4;
Result
{"_":"Whole table (two columns inserted)................................1"}
{"field":"id","type":"INT32"}
{"field":"a","type":"INT32"}
{"_":"Whole table (all columns inserted)................................2"}
{"field":"id","type":"INT32"}
{"field":"name","type":"STRING"}
{"field":"a","type":"INT32"}
{"field":"b","type":"VARIANT"}
{"_":"Using WHERE.......................................................3"}
{"field":"id","type":"INT32"}
{"field":"a","type":"INT32"}
{"_":"Using WHERE (B is INT32)..........................................4"}
{"field":"name","type":"STRING"}
{"field":"b","type":"INT32"}
{"_":"Using WHERE (B is DOUBLE).........................................5"}
{"field":"name","type":"STRING"}
{"field":"b","type":"DOUBLE"}
{"_":"Using WHERE (B is VARIANT)........................................6"}
{"field":"name","type":"STRING"}
{"field":"b","type":"VARIANT"}
See also

The DESCRIBE command.

SET

Sets configuration parameters.

Synopsis
SET name TO value
Description

Sets configuration parameters with the given name and value. Changes are applied only to the current session, and neither affect other users nor survive after the session ends. See the supported parameters below.

Example 31. Using SET
List of SQL commands
CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;
DROP TABLE IF EXISTS test;

CREATE TABLE IF NOT EXISTS test();
INSERT INTO test {"pi":3.14159265};

SELECT "pi" from test;
SET FloatPrecision TO 3;
SELECT "pi" from test;
Result
{"pi":3.141593}
{"pi":3.142}

SHOW

Shows configuration parameters.

Synopsis
SHOW [ name | ALL ]
Description

Shows configuration parameters and their values for all given names only for the current user session. For more details, see the supported parameters.

Example 32. Using SHOW with config
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
USE testdb;

SELECT 'Show one option:' _;
SHOW HeapTypeConversion;
SET HeapTypeConversion TO "No";

SELECT 'Show ALL:' _;
SHOW ALL;
Result
{"_":"Show one option:"}
{"Name":"HeapTypeConversion","Value":"yes","Description":"Force data type conversion for heap columns (if schema is known in advance)."}
{"_":"Show ALL:"}
{"Name":"HeapTypeConversion","Value":"no","Description":"Force data type conversion for heap columns (if schema is known in advance)."}
{"Name":"HeapTypeConversionRaiseError","Value":"no","Description":"If the given value can not be converted to the expected data type, then throw an error when true or ignore when false."}
{"Name":"JsonAutoConvertType","Value":"yes","Description":"If set to true, Json values are being converted to the primitives."}
{"Name":"TransformNullEquals","Value":"no","Description":"If set to true, equivalency checks (=, !=, <>) work on NULL values as well."}
{"Name":"FloatPrecision","Value":"6","Description":"Sets the number of digits printed after the right of decimal point for float/double values. The default value is 6.
Maximum value for DOUBLE is 17, for FLOAT - 9 digits. At server start the value is checked for DOUBLE maximum only,
but using values greater than 9 for FLOATs will abort the query execution with run-time error."}
{"Name":"PushdownPredicates","Value":"yes","Description":"For testing and debugging purposes. Enables optimization where predicates of SQL queries can be pushed down to the datasource."}
{"Name":"PushdownAggregates","Value":"yes","Description":"Evaluates aggregates at the storage layer (as with predicates pushdown)."}
{"Name":"FilterPartitions","Value":"yes","Description":"Enables partitions skipping by expression and column predicates."}
{"Name":"OperatorAddPolicy","Value":"","Description":"Addition operator (a + b) policy.
Acceptable values: DEFAULT, QUIET, NULLING, SIGNALING"}
{"Name":"OperatorMultiplyPolicy","Value":"","Description":"Multiply operator (a * b) policy.
\nAcceptable values: DEFAULT, QUIET, NULLING, SIGNALING"}
{"Name":"OperatorSubtractPolicy","Value":"","Description":"Subtract operator (a - b) policy.
\nAcceptable values: DEFAULT, QUIET, NULLING, SIGNALING"}
{"Name":"OperatorDividePolicy","Value":"","Description":"Division operator (a / b) policy.
Acceptable values: DEFAULT, QUIET, NULLING, SIGNALING"}
{"Name":"OperatorModulusPolicy","Value":"","Description":"Modulus operator (a % b; mod(a, b)) policy.
Acceptable values: DEFAULT, QUIET, NULLING, SIGNALING"}
{"Name":"OperatorLnPolicy","Value":"","Description":"Ln operator (Ln(a); Log(a)) policy.
Acceptable values: DEFAULT, QUIET, NULLING, SIGNALING"}
{"Name":"OperatorLogPolicy","Value":"","Description":"Log operator (Log(a, b)) policy.
Acceptable values: DEFAULT, QUIET, NULLING, SIGNALING"}
{"Name":"OperatorLog10Policy","Value":"","Description":"Log10 operator (Log10(a)) policy.
Acceptable values: DEFAULT, QUIET, NULLING, SIGNALING"}
{"Name":"OperatorLog2Policy","Value":"","Description":"Log2 operator (Log2(a)) policy.
Acceptable values: DEFAULT, QUIET, NULLING, SIGNALING"}
{"Name":"OperatorSqrtPolicy","Value":"","Description":"Sqrt operator (Sqrt(a)) policy.
Acceptable values: DEFAULT, QUIET, NULLING, SIGNALING"}
{"Name":"OperatorPowPolicy","Value":"","Description":"Power operator(pow(a, b); power(a, b)) policy.
Acceptable values: DEFAULT, QUIET, NULLING, SIGNALING"}
{"Name":"OrderbyWithLimitOptimization","Value":"yes","Description":"Enables/disables optimization of queries with ORDER BY..LIMIT statements, statements like this:
SELECT * FROM t1 ORDER BY <field list> LIMIT n [OFFSET m]
The default is TRUE.
While default algorithm performs sorting of the whole dataset first, involving using on-disc cache for data parts,
an optimized algorithm is doing the in-memory operations which involve usage of memory enough for storing LIMIT+OFFSET
rows only (no on-disc cache). This allows SIGNIFICALLY improve the speed of algorithm, even in comparance to default
algorithm in cases when no disc cache used.
If the memory limit is reached (see SortMemoryLimit parameter), falls back to the default algorithm."}
{"Name":"CrashTestConnectionId","Value":"1","Description":"For testing purposes only"}
{"Name":"ExpandCommonColumnsWithPrefix","Value":"no","Description":"Enables prefixes for common columns."}
{"Name":"CalculateNullSelectivity","Value":"yes","Description":"Enables selectivity stat estimation of NullTest Nodes"}

SHOW DATABASES

Returns a list of databases.

Synopsis
SHOW DATABASES
Description

Returns a list of databases that exist on the server.

Example 33. Using SHOW DATABASES
List of SQL commands
DROP DATABASE IF EXISTS serverlogs;
DROP DATABASE IF EXISTS userlogs;
CREATE DATABASE serverlogs;
CREATE DATABASE userlogs;

SHOW DATABASES;
Result
{"database":"serverlogs","record_count":0,"size":0}
{"database":"userlogs","record_count":0,"size":0}

SHOW PARTITIONS

Returns a list of table partitions.

Synopsis
SHOW PARTITIONS [ IN | FROM ] table
Description

Returns a list of partitions in the given table. It includes the partition name, state (attached or detached), row count, and the partition data path on the disk. The path is relative to the Raijin root data folder.

Example 34. Using SHOW PARTITIONS
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
USE testdb;

CREATE TABLE tp1(a INTEGER, b INTEGER);

SELECT 'Empty table.......................................................1' _;
SHOW PARTITIONS FROM tp1;
SHOW PARTITIONS IN tp1;

INSERT INTO tp1 VALUES (1, 0), (2, -1); -- root partition
INSERT INTO tp1 VALUES (3, -2), (4, -3) WITH (partition = 'p1');
INSERT INTO tp1 VALUES (5, -4), (6, -5) WITH (partition = 'p2');
INSERT INTO tp1 VALUES (7, -6); -- root partition

SELECT 'All attached [FROM]...............................................2' _;
SHOW PARTITIONS FROM tp1;
SELECT 'All attached [IN].................................................3' _;
SHOW PARTITIONS IN tp1;
SELECT 'One attached one detached.........................................4' _;
ALTER TABLE tp1 DETACH PARTITION p1;
SHOW PARTITIONS FROM tp1;
SELECT 'All detached......................................................5' _;
ALTER TABLE tp1 DETACH PARTITION p2;
SHOW PARTITIONS IN tp1;
SELECT 'All dropped.......................................................6' _;
ALTER TABLE tp1 DROP PARTITION p1;
ALTER TABLE tp1 DROP PARTITION p2;
SHOW PARTITIONS IN tp1;
Result
{"_":"Empty table.......................................................1"}
{"_":"All attached [FROM]...............................................2"}
{"name":"","state":"attached","record_count":3,"path":"testdb/tp1/_default","size":78}
{"name":"p1","state":"attached","record_count":2,"path":"testdb/tp1/p1","size":82}
{"name":"p2","state":"attached","record_count":2,"path":"testdb/tp1/p2","size":82}
{"_":"All attached [IN].................................................3"}
{"name":"","state":"attached","record_count":3,"path":"testdb/tp1/_default","size":78}
{"name":"p1","state":"attached","record_count":2,"path":"testdb/tp1/p1","size":82}
{"name":"p2","state":"attached","record_count":2,"path":"testdb/tp1/p2","size":82}
{"_":"One attached one detached.........................................4"}
{"name":"","state":"attached","record_count":3,"path":"testdb/tp1/_default","size":78}
{"name":"p2","state":"attached","record_count":2,"path":"testdb/tp1/p2","size":82}
{"name":"p1","state":"detached","record_count":2,"path":"testdb/tp1/_detached/p1","size":705}
{"_":"All detached......................................................5"}
{"name":"","state":"attached","record_count":3,"path":"testdb/tp1/_default","size":78}
{"name":"p1","state":"detached","record_count":2,"path":"testdb/tp1/_detached/p1","size":705}
{"name":"p2","state":"detached","record_count":2,"path":"testdb/tp1/_detached/p2","size":705}
{"_":"All dropped.......................................................6"}
{"name":"","state":"attached","record_count":3,"path":"testdb/tp1/_default","size":78}

SHOW TABLES

Returns a list of tables in the database.

Synopsis
SHOW TABLES
Description

Returns a list of tables in the current database.

Example 35. Using SHOW TABLES
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE eventlogs();
CREATE TABLE eventlogs_partitioned() partition by DATE_TRUNC('day',event_date);
CREATE TABLE eventlogs_partitioned_byname();

CREATE ENCRYPTED TABLE userlogs();
CREATE ENCRYPTED TABLE userlogs_partitioned() partition by DATE_TRUNC('day',log_date);
CREATE ENCRYPTED TABLE userlogs_partitioned_byname();

INSERT INTO eventlogs_partitioned(event, event_date) VALUES
 ('event1', '2020-07-11 21:46:08'),
 ('event1', '2020-07-12 22:46:08'),
 ('event2', null);

INSERT INTO eventlogs_partitioned_byname (event, event_date) VALUES('logon', '2020-07-11 21:46:08') WITH (partition = 'p1');
INSERT INTO userlogs_partitioned_byname (user, log_date) VALUES('root', '2020-07-11 21:46:08') WITH (partition = 'p_root');
FLUSH TABLES;

SHOW TABLES;
Result
{"table":"eventlogs","record_count":0,"encrypted":"no","partitioning_type":"none","size":27}
{"table":"eventlogs_partitioned","record_count":3,"encrypted":"no","partitioning_type":"multidimensional","partitioning_expression":"DATE_TRUNC('day',event_date)","size":3073}
{"table":"eventlogs_partitioned_byname","record_count":1,"encrypted":"no","partitioning_type":"manual","size":1038}
{"table":"userlogs","record_count":0,"encrypted":"yes","partitioning_type":"none","size":59}
{"table":"userlogs_partitioned","record_count":0,"encrypted":"yes","partitioning_type":"multidimensional","partitioning_expression":"DATE_TRUNC('day',log_date)","size":138}
{"table":"userlogs_partitioned_byname","record_count":1,"encrypted":"yes","partitioning_type":"manual","size":1202}

SHOW USERS

Returns the list of users in the current database.

Synopsis
SHOW USERS
Description

Returns the list of users in the current database, also including information on their authentication method.

Example 36. Using SHOW USERS
List of SQL commands
SHOW USERS;
Result
{"name":"clarissa", "identified_with":"SSL_CERTIFICATE"}
{"name":"melissa", "identified_with":"PASSWORD"}

SHOW VERSION

Returns information on the version of Raijin Database Engine currently installed.

Synopsis
SHOW VERSION
Description

Returns the following information on the version of Raijin Database Engine currently installed:

  • Version: Raijin Database Engine version, a string formatted as MAJOR.MINOR.PATCH.

  • Commit: Identifier of the latest commit to the Raijin Database Engine software code branch.

  • Branch: Identifier of the code branch containing this version of the Raijin Database Engine software.

  • Configuration date: Timestamp of this Raijin Database Engine configuration, relevant for traceability analysis.

  • Platform: Operating system and kernel details.

  • Architecture: computing hardware architecture: X86, ARM, etc.

Example 37. Using SHOW VERSION
List of SQL commands
SHOW VERSION;
Result
"Version": "1.3.7",
"Commit": "6769ff43c",
"Branch": "master",
"Configuration date": "2023-11-09T03:05:32Z",
"Platform": "Linux 6.2.0-35-generic x86_64",
"Architecture": "x86_64"

SHOW VIEWS

Returns the list of views in the database.

Synopsis
SHOW VIEWS
Description

Returns the list of views in the current database, allowing users to identify and understand the views in their schema. The returned information includes the view name and the SQL query that defines the view.

Example 38. Using SHOW VIEWS
List of SQL commands
SHOW VIEWS;
Result
{"view":"user_dashboard","definition":"SELECT id, name FROM users"}
{"view":"product_inventory","definition":"SELECT product_id, quantity FROM inventories"}

UNLOCK TABLES

Synopsis
UNLOCK TABLES
Description

Releases the lock created with FLUSH TABLES WITH READ LOCK.

Example 39. Using UNLOCK TABLES
List of SQL commands
UNLOCK TABLES;

USE

Sets the current database.

Synopsis
USE name
Description

Before any data operation can be executed, you need to select an existing database with the USE command.

Parameters
name

Name of the existing database for using.

Example 40. Using USE
List of SQL commands
DROP DATABASE IF EXISTS "testdb1";
DROP DATABASE IF EXISTS "testdb2";
CREATE DATABASE "testdb1";
CREATE DATABASE "testdb2";

USE "testdb1";
CREATE TABLE tbl(value int);
INSERT INTO tbl(a) VALUES (1);

USE "testdb2";
CREATE TABLE tbl(value int);
INSERT INTO tbl(a) VALUES (2);

USE "testdb1";
SELECT a FROM tbl;

USE "testdb2";
SELECT a FROM tbl;

DROP DATABASE "testdb1";
DROP DATABASE "testdb2";
Result
{"a":1}
{"a":2}