Extensions to SHOW Statements for information_schema tables

[killme2008]

What type of enhancement is this?

API improvement, User experience

What does the enhancement do?

MySQL supports SHOW XXXX from [table] or SHOW FULL XXX from [table] to query information_schema, for example:

SHOW FULL TABLES WHERE Table_type != 'VIEW'

SHOW TABLE STATUS

SHOW PROCEDURE STATUS where DB='public';

SHOW INDEX FROM `monitor`;

SHOW FULL COLUMNS from `monitor`;

We have to support these syntax sugars if we want to improve the compatibility of MySQL protocol.

Tasks:

• [x] Refactor show tables and show databases executor, make them querying from information_schema and support where clause such as SHOW FULL TABLES WHERE Table_type != 'VIEW'. https://github.com/GreptimeTeam/greptimedb/pull/3423
• [x] show columns https://github.com/GreptimeTeam/greptimedb/pull/3577
• [x] show table status https://github.com/GreptimeTeam/greptimedb/pull/4303
• [x] show index https://github.com/GreptimeTeam/greptimedb/pull/3577
• [x] SHOW CHARACTER SET https://github.com/GreptimeTeam/greptimedb/pull/3753
• [x] SHOW COLLATION https://github.com/GreptimeTeam/greptimedb/pull/3753
• [ ] Improve show variables.

Implementation challenges

No response

[killme2008]

The related MySQL documents:

• https://dev.mysql.com/doc/refman/8.0/en/show.html
• https://dev.mysql.com/doc/refman/8.0/en/extended-show.html

I'll implement the extensions to the show statement for information_schema in 0.7

[killme2008]

I'll implement the below show statements:

SHOW CHARACTER SET
SHOW COLLATION
SHOW COLUMNS
SHOW DATABASES
SHOW FUNCTION STATUS
SHOW INDEX
SHOW OPEN TABLES
SHOW PROCEDURE STATUS
SHOW STATUS
SHOW TABLE STATUS
SHOW TABLES
SHOW TRIGGERS
SHOW VARIABLES

[MichaelScofield]

Navicat will submit "show status" and "show table status" upon connection.

[killme2008]

Navicat will submit "show status" and "show table status" upon connection.

show status looks working but returns empty:

mysql> show status;
Empty set (0.03 sec)

I'll implement show table status based on #4218 .