problem in query result # table_name header string when schema name contains a dot character

[mvdvm]

Describe the bug
the query result # table_name header string contains both the schema name and table name of each result column.
The names are separated by the dot character, so '.'.
However when the schema name contains a dot character, the # table_name header string will contain multiple dot characters.
This makes it impossible to extract the correct schema and table names metadata information.
This is a problem for the ODBC and JDBC drivers which need to return separate schema and table name metadata information.

To Reproduce

CREATE SCHEMA "sch.";
SET SCHEMA "sch.";
SELECT CURRENT_SCHEMA;

CREATE TABLE "sch." . ".tabl" (".col" int) ;
INSERT INTO "sch." . ".tabl" VALUES (63);
SELECT * FROM "sch." . ".tabl" WHERE ".col" IS NOT NULL;

This last query returns incorrect resultset schema and table name metadata in ODBC and JDBC.
They return 'sch' as the schema name and '..tabl' as the table name which is incorrect.

In mclient you can inspect this # table_name header information by using the \X mode before sending the last query:

Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.49.6 (hg id: 31772b44da50+), 'demo'
Type \q to quit, \? for a list of available commands
auto commit mode: on
sql>\X
sql>SELECT * FROM "sch." . ".tabl" WHERE ".col" IS NOT NULL;
mapi_query_part:57:SELECT * FROM "sch." . ".tabl" WHERE ".col" IS NOT NULL;

fetch next block: start at:948
got next block: length:101
text:&1 5 1 1 1 216 1975 653 195
% sch...tabl # table_name
% .col # name
% int # type
% 2 # length
[ 63	]

You can see line:
% sch...tabl # table_name
contains the fully qualified table_name. It is a concatenation of sch., a dot character and .tabl, all without the double quotes.

This header line is what is send by the server to the client.
The client libraries have to figure out from the received header line, what the schema name part is and what the table name part is.
In ODBC and JDBC driver this is done by searching for the first dot character. If it is found it splits the string into two substrings, the first is the schema name, the second the table name.
This goes wrong in the case the schema name contains a dot character.
In ODBC and JDBC it will return 'sch' as the schema name and '..tabl' as the table name which is incorrect.

Expected behavior
This problem cannot be fixed in the ODBC or JDBC driver code, as it is not possible to correctly decide which of the dot characters in 'sch...tabl' is the real separator. Is it the first, second or third dot character?

This problem should be fixed in the mapi protocol.
Suggestion: extend the header information with an additional # schema_name line, so:

% sch. # schema_name
% .tabl # table_name

This makes parsing the header lines for schema and table name possible without problems.
Also it is adaptive for new ODBC and JDBC driver as the client can inform the server to enable sending the # schema_name header line dynamically (like is done for the # typesizes header info) when the server and mapi supports it.

I also considered to add double quotes to the schema and table name parts when it contains a dot character.
So the # table_name header would become:
`% "sch.".".tabl" # table_name
Technically this would allow new versions of the ODBC and JDBC drivers to detect that the string between the double quotes need to be treated specially.
However old ODBC and JDBC drivers would then return wrong information: '"sch' as the schema name and '".".tabl"' as the table name. So I do not recommend this option.

When the mapi header changes also the client libraries (ODBC and JDBC) need to be adapted for this new behavior. Note that the ODBC and JDBC drivers also need to keep supporting older server versions, so be dynamic.

Software versions

• MonetDB version number Dec2023
• OS and version: all