support for informational schema in sql requests.

The MySQL wire protocol is widely used nowadays in various software. It's great that Manticore supports it too.

But some "smart" products perform an additional query before doing anything to get information about tables and fields.

Among such products are development tools (DataGrip) and DBMS such as Clickhouse.

Clickhouse allows you to connect to other databases via the MySQL protocol, both for inserting and selecting data. It translate own SQL query to the remote DBMS dialect. However, it makes an additional request to informational schema of remote DBMS.

select * from mysql('127.0.0.1:9306','','wiki','qq','qq') where match('qq') ;

/* Fri Feb 11 09:56:37.139 2022 conn 3 */ SELECT TABLE_NAME AS table_name, COLUMN_NAME AS column_name, COLUMN_TYPE AS column_type, IS_NULLABLE = 'YES' AS is_nullable, COLUMN_TYPE LIKE '%unsigned' AS is_unsigned, CHARACTER_MAXIMUM_LENGTH AS length, NUMERIC_PRECISION AS numeric_precision, IF(ISNULL(NUMERIC_SCALE), DATETIME_PRECISION, NUMERIC_SCALE) AS scale, COLUMN_COMMENT AS column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN ('wiki') ORDER BY ORDINAL_POSITION # error=sphinxql: syntax error, unexpected string, expecting FROM or ',' near ''%unsigned' AS is_unsigned, CHARACTER_MAXIMUM_LENGTH AS length, NUMERIC_PRECISION AS numeric_precision, IF(ISNULL(NUMERIC_SCALE), DATETIME_PRECISION, NUMERIC_SCALE) AS scale, COLUMN_COMMENT AS column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN ('wiki') ORDER BY ORDINAL_POSITION'

It would be convenient to resolve that issue to simplify the process of integration of Manticore and Clickhouse (and possibly other DBMS). It could be done according to the following scheme:

• The data coming to the Clickhouse is processed by batches through the common pipelines with Materialized View and inserted into the remote table with the engine mysql.
• The data is proxied towards the Manticore, and indexed there.
• When processing a search from a user, Clickhouse can join or subrequest data from both local and remote tables.

select title, content from wikipedia
where id in (select id from mysql('manticore.host:9306','database','wikipedia','user','pass') where match('search string') )

Review the MySQL wire-protocol handling and the INFORMATION_SCHEMA.COLUMNS query shown in the issue, including the ClickHouse integration example. Determine the expected response for table and column metadata, then verify that the displayed query and the described remote-table search workflow work correctly.