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;

{"x":1,"y":"test1"}
{"x":2,"y":"test2"}

DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE sample();
SHOW TABLES;
ALTER TABLE sample RENAME renamed;
SHOW TABLES;

{"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 'foo' SET PASSWORD TO '1234_Raijin';

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;

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;

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;

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 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 'foo' IDENTIFIED WITH PASSWORD '1234_Raijin';

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

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;

{
  "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"
}

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;

{"field":"id","type":"INT"}
{"field":"name","type":"STRING"}
{"field":"birthday","type":"TIMESTAMP"}
{"field":"a","type":"INT"}

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

{"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}

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 'foo';

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;

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');

{"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'"}

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 CREATE, SELECT ON db.tbl TO 'foo';
GRANT ALL PRIVILEGES ON *.* TO 'bar';

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;

{"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"}

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;

{
  "Hostname": "localhost",
  "hostname": null,
  "expression1": 12,
  "expression2": 1.5
}

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;

{
  "found": 1
}

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;

{"data":1}
{"data":2}

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

{"loglevel":2,"message":"Beware"}
{"loglevel":3,"message":"Something went wrong"}

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;

{"_":"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"}

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;

{"messages":"Beware,Warning"}
{"messages":"Something went wrong"}

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;

{"messages":"Beware,Warning,It's not good"}
{"messages":"Ooops,Something went wrong"}

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;

{"value":100}
{"value":86}
{"value":63}
{"value":3}

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;

{"_":"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}

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;

{"_":"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"}

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;

{"pi":3.141593}
{"pi":3.142}

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;

{"_":"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"}

DROP DATABASE IF EXISTS serverlogs;
DROP DATABASE IF EXISTS userlogs;
CREATE DATABASE serverlogs;
CREATE DATABASE userlogs;

SHOW DATABASES;

{"database":"serverlogs","record_count":0,"size":0}
{"database":"userlogs","record_count":0,"size":0}

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;

{"_":"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}

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;

{"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;

{"name":"clarissa", "identified_with":"SSL_CERTIFICATE"}
{"name":"melissa", "identified_with":"PASSWORD"}

SHOW VERSION;

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

{"view":"user_dashboard","definition":"SELECT id, name FROM users"}
{"view":"product_inventory","definition":"SELECT product_id, quantity FROM inventories"}

UNLOCK TABLES;

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

{"a":1}
{"a":2}