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

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

INSERT into tbl(A) values(1);

DESCRIBE tbl;

{"field":"id","type":"INT32"}
{"field":"name","type":"STRING"}
{"field":"birthday","type":"DATETIME"}
{"field":"a","type":"INT32"}

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":"66 bytes"}
{"table":"tbl_two","record_count":"0","encrypted":"no","partitioning_type":"none","size":"66 bytes"}
{"table":"tbl_two","record_count":"0","encrypted":"no","partitioning_type":"none","size":"66 bytes"}

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 VARCHAR(20),
    eventtime TIMESTAMP,
    eventtype VARCHAR(10),
    message TEXT
);

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

-- 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 {
    "hostname":"srv-04",
    "eventtime":"2015-06-24 12:20:12Z",
    "eventtype":"ERROR",
    "message": "Database timeout!"
};

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 successfuly."}
{"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!"}

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

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 {"a":"test"};
SELECT * from test;
SET IdColumnName TO "_another_id";
SELECT * from test;

{"_id":1,"a":"test"}
{"_another_id":1,"a":"test"}

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":"IdColumnName","Value":"_id","Description":"Name of internal row id column in Raijin's output. Session parameter."}
{"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":"DetectPartitionFilters","Value":"yes","Description":"Enables partitions skipping by expression 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."}
{"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":"<root>","state":"attached","rows":3,"path":"testdb/tp1/_root"}
{"name":"p1","state":"attached","rows":2,"path":"testdb/tp1/c985b5170824680e8b779935b16ce90f"}
{"name":"p2","state":"attached","rows":2,"path":"testdb/tp1/aaeae4b66f076c5d858a878fadd236e3"}
{"_":"All attached [IN].................................................3"}
{"name":"<root>","state":"attached","rows":3,"path":"testdb/tp1/_root"}
{"name":"p1","state":"attached","rows":2,"path":"testdb/tp1/c985b5170824680e8b779935b16ce90f"}
{"name":"p2","state":"attached","rows":2,"path":"testdb/tp1/aaeae4b66f076c5d858a878fadd236e3"}
{"_":"One attached one detached.........................................4"}
{"name":"<root>","state":"attached","rows":3,"path":"testdb/tp1/_root"}
{"name":"p2","state":"attached","rows":2,"path":"testdb/tp1/c985b5170824680e8b779935b16ce90f"}
{"name":"p1","state":"detached","rows":2,"path":"testdb/tp1/aaeae4b66f076c5d858a878fadd236e3"}
{"_":"All detached......................................................5"}
{"name":"<root>","state":"attached","rows":3,"path":"testdb/tp1/_root"}
{"name":"p1","state":"detached","rows":2,"path":"testdb/tp1/c985b5170824680e8b779935b16ce90f"}
{"name":"p2","state":"detached","rows":2,"path":"testdb/tp1/aaeae4b66f076c5d858a878fadd236e3"}
{"_":"All dropped.......................................................6"}
{"name":"<root>","state":"attached","rows":3,"path":"testdb/tp1/_root"}

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

CREATE TABLE eventlogs();
CREATE TABLE eventlogs_partitioned() partition by DATE(event_date);
CREATE TABLE eventlogs_partitioned_byname();

CREATE ENCRYPTED TABLE userlogs();
CREATE ENCRYPTED TABLE userlogs_partitioned() partition by DATE(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;

ALTER TABLE eventlogs_partitioned DETACH PARTITION "2020-07-12";

SHOW TABLES;

{"table":"eventlogs","record_count":0,"encrypted":"no","partitioning_type":"none","size":27}
{"table":"eventlogs_partitioned","record_count":3,"encrypted":"no","partitioning_type":"expression","partitioning_expression":"DATE(event_date)","size":3024}
{"table":"eventlogs_partitioned_byname","record_count":1,"encrypted":"no","partitioning_type":"manual","size":1075}
{"table":"userlogs","record_count":0,"encrypted":"yes","partitioning_type":"none","size":59}
{"table":"userlogs_partitioned","record_count":0,"encrypted":"yes","partitioning_type":"expression","partitioning_expression":"DATE(log_date)","size":147}
{"table":"userlogs_partitioned_byname","record_count":1,"encrypted":"yes","partitioning_type":"manual","size":1239}
{}
{"table":"eventlogs","record_count":0,"encrypted":"no","partitioning_type":"none","size":27}
{"table":"eventlogs_partitioned","record_count":2,"encrypted":"no","partitioning_type":"expression","partitioning_expression":"DATE(event_date)","size":1906}
{"table":"eventlogs_partitioned_byname","record_count":1,"encrypted":"no","partitioning_type":"manual","size":1075}
{"table":"userlogs","record_count":0,"encrypted":"yes","partitioning_type":"none","size":59}
{"table":"userlogs_partitioned","record_count":0,"encrypted":"yes","partitioning_type":"expression","partitioning_expression":"DATE(log_date)","size":147}
{"table":"userlogs_partitioned_byname","record_count":1,"encrypted":"yes","partitioning_type":"manual","size":1239}

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}