-- should not able to create information_schema
create database information_schema;

Error: 1004(InvalidArguments), Schema information_schema already exists

-- scripts table has different table ids in different modes
select *
from information_schema.tables
where table_name != 'scripts'
order by table_schema, table_name;

+---------------+--------------------+-------------------+-----------------+----------+-------------+
| table_catalog | table_schema       | table_name        | table_type      | table_id | engine      |
+---------------+--------------------+-------------------+-----------------+----------+-------------+
| greptime      | information_schema | build_info        | LOCAL TEMPORARY | 8        |             |
| greptime      | information_schema | column_privileges | LOCAL TEMPORARY | 6        |             |
| greptime      | information_schema | column_statistics | LOCAL TEMPORARY | 7        |             |
| greptime      | information_schema | columns           | LOCAL TEMPORARY | 4        |             |
| greptime      | information_schema | engines           | LOCAL TEMPORARY | 5        |             |
| greptime      | information_schema | tables            | LOCAL TEMPORARY | 3        |             |
| greptime      | public             | numbers           | LOCAL TEMPORARY | 2        | test_engine |
+---------------+--------------------+-------------------+-----------------+----------+-------------+

select * from information_schema.columns order by table_schema, table_name;

+---------------+--------------------+-------------------+------------------+-----------+---------------+
| table_catalog | table_schema       | table_name        | column_name      | data_type | semantic_type |
+---------------+--------------------+-------------------+------------------+-----------+---------------+
| greptime      | information_schema | build_info        | pkg_version      | String    | FIELD         |
| greptime      | information_schema | build_info        | git_dirty        | String    | FIELD         |
| greptime      | information_schema | build_info        | git_commit_short | String    | FIELD         |
| greptime      | information_schema | build_info        | git_commit       | String    | FIELD         |
| greptime      | information_schema | build_info        | git_branch       | String    | FIELD         |
| greptime      | information_schema | column_privileges | grantee          | String    | FIELD         |
| greptime      | information_schema | column_privileges | is_grantable     | String    | FIELD         |
| greptime      | information_schema | column_privileges | privilege_type   | String    | FIELD         |
| greptime      | information_schema | column_privileges | column_name      | String    | FIELD         |
| greptime      | information_schema | column_privileges | table_name       | String    | FIELD         |
| greptime      | information_schema | column_privileges | table_schema     | String    | FIELD         |
| greptime      | information_schema | column_privileges | table_catalog    | String    | FIELD         |
| greptime      | information_schema | column_statistics | histogram        | String    | FIELD         |
| greptime      | information_schema | column_statistics | column_name      | String    | FIELD         |
| greptime      | information_schema | column_statistics | table_name       | String    | FIELD         |
| greptime      | information_schema | column_statistics | schema_name      | String    | FIELD         |
| greptime      | information_schema | columns           | table_name       | String    | FIELD         |
| greptime      | information_schema | columns           | semantic_type    | String    | FIELD         |
| greptime      | information_schema | columns           | data_type        | String    | FIELD         |
| greptime      | information_schema | columns           | column_name      | String    | FIELD         |
| greptime      | information_schema | columns           | table_schema     | String    | FIELD         |
| greptime      | information_schema | columns           | table_catalog    | String    | FIELD         |
| greptime      | information_schema | engines           | savepoints       | String    | FIELD         |
| greptime      | information_schema | engines           | xa               | String    | FIELD         |
| greptime      | information_schema | engines           | transactions     | String    | FIELD         |
| greptime      | information_schema | engines           | comment          | String    | FIELD         |
| greptime      | information_schema | engines           | support          | String    | FIELD         |
| greptime      | information_schema | engines           | engine           | String    | FIELD         |
| greptime      | information_schema | tables            | table_schema     | String    | FIELD         |
| greptime      | information_schema | tables            | table_catalog    | String    | FIELD         |
| greptime      | information_schema | tables            | engine           | String    | FIELD         |
| greptime      | information_schema | tables            | table_id         | UInt32    | FIELD         |
| greptime      | information_schema | tables            | table_type       | String    | FIELD         |
| greptime      | information_schema | tables            | table_name       | String    | FIELD         |
| greptime      | public             | numbers           | number           | UInt32    | TAG           |
+---------------+--------------------+-------------------+------------------+-----------+---------------+

create
database my_db;

Affected Rows: 1

use my_db;

Affected Rows: 0

create table foo
(
    ts TIMESTAMP TIME INDEX
);

Affected Rows: 0

select table_name
from information_schema.tables
where table_schema = 'my_db'
order by table_name;

+------------+
| table_name |
+------------+
| foo        |
+------------+

select table_catalog, table_schema, table_name, table_type, engine
from information_schema.tables
where table_catalog = 'greptime'
  and table_schema != 'public'
  and table_schema != 'information_schema'
order by table_schema, table_name;

+---------------+--------------+------------+------------+--------+
| table_catalog | table_schema | table_name | table_type | engine |
+---------------+--------------+------------+------------+--------+
| greptime      | my_db        | foo        | BASE TABLE | mito   |
+---------------+--------------+------------+------------+--------+

select table_catalog, table_schema, table_name, column_name, data_type, semantic_type
from information_schema.columns
where table_catalog = 'greptime'
  and table_schema != 'public'
  and table_schema != 'information_schema'
order by table_schema, table_name, column_name;

+---------------+--------------+------------+-------------+----------------------+---------------+
| table_catalog | table_schema | table_name | column_name | data_type            | semantic_type |
+---------------+--------------+------------+-------------+----------------------+---------------+
| greptime      | my_db        | foo        | ts          | TimestampMillisecond | TIMESTAMP     |
+---------------+--------------+------------+-------------+----------------------+---------------+

use public;

Affected Rows: 0

drop schema my_db;

Error: 1001(Unsupported), SQL statement is not supported: drop schema my_db;, keyword: schema

use information_schema;

Affected Rows: 0

-- test engines
select * from engines;

+--------+---------+-------------------------------------+--------------+----+------------+
| engine | support | comment                             | transactions | xa | savepoints |
+--------+---------+-------------------------------------+--------------+----+------------+
| mito   | DEFAULT | Storage engine for time-series data | NO           | NO | NO         |
+--------+---------+-------------------------------------+--------------+----+------------+

desc table build_info;

+------------------+--------+-----+------+---------+---------------+
| Column           | Type   | Key | Null | Default | Semantic Type |
+------------------+--------+-----+------+---------+---------------+
| git_branch       | String |     | NO   |         | FIELD         |
| git_commit       | String |     | NO   |         | FIELD         |
| git_commit_short | String |     | NO   |         | FIELD         |
| git_dirty        | String |     | NO   |         | FIELD         |
| pkg_version      | String |     | NO   |         | FIELD         |
+------------------+--------+-----+------+---------+---------------+

select count(*) from build_info;

+----------+
| COUNT(*) |
+----------+
| 1        |
+----------+

-- tables not implemented
desc table COLUMN_PRIVILEGES;

+----------------+--------+-----+------+---------+---------------+
| Column         | Type   | Key | Null | Default | Semantic Type |
+----------------+--------+-----+------+---------+---------------+
| grantee        | String |     | NO   |         | FIELD         |
| table_catalog  | String |     | NO   |         | FIELD         |
| table_schema   | String |     | NO   |         | FIELD         |
| table_name     | String |     | NO   |         | FIELD         |
| column_name    | String |     | NO   |         | FIELD         |
| privilege_type | String |     | NO   |         | FIELD         |
| is_grantable   | String |     | NO   |         | FIELD         |
+----------------+--------+-----+------+---------+---------------+

select * from COLUMN_PRIVILEGES;

+---------+---------------+--------------+------------+-------------+----------------+--------------+
| grantee | table_catalog | table_schema | table_name | column_name | privilege_type | is_grantable |
+---------+---------------+--------------+------------+-------------+----------------+--------------+
+---------+---------------+--------------+------------+-------------+----------------+--------------+

desc table COLUMN_STATISTICS;

+-------------+--------+-----+------+---------+---------------+
| Column      | Type   | Key | Null | Default | Semantic Type |
+-------------+--------+-----+------+---------+---------------+
| schema_name | String |     | NO   |         | FIELD         |
| table_name  | String |     | NO   |         | FIELD         |
| column_name | String |     | NO   |         | FIELD         |
| histogram   | String |     | NO   |         | FIELD         |
+-------------+--------+-----+------+---------+---------------+

select * from COLUMN_STATISTICS;

+-------------+------------+-------------+-----------+
| schema_name | table_name | column_name | histogram |
+-------------+------------+-------------+-----------+
+-------------+------------+-------------+-----------+

use public;

Affected Rows: 0