The result of "show create table" cannot be used directly in mysql

[MichaelScofield]

What type of bug is this?

User Experience

What subsystems are affected?

Standalone mode

Minimal reproduce step

Just execute "show create table" against any table in the Dashboard. Copy, paste and run the result in the mysql cli.

What did you expect to see?

The result can be executed in mysql cli successfully.

What did you see instead?

Server returns an error:

mysql> create table bar ("ts" timestamp time index);
ERROR 1815 (HY000): sql parser error: Expected column name or constraint definition, found: "ts"

The result of "show create table" displays the column name as double quoted string, which is not valid in mysql.

For mysql, the only valid quotes here are backtick, or none. For postgresql, it's double quote or none. So maybe we can make the result not quoted at all?

What operating system did you use?

macos

What version of GreptimeDB did you use?

main

Relevant log output and stack trace

No response

[waynexia]

Just execute "show create table" against any table in the Dashboard. Copy, paste and run the result in the mysql cli.

This is an expected behavior. Different protocol has different quote escape schemes. The SQL you got can only be used at where you got it. You cannot use the SQL from PG in MySQL either

[MichaelScofield]

There's no "show create table" like statement in postgresql, it's mysql only. So it's a little counterintuitive that the output cannot be directly executed in mysql again.

[waynexia]

HTTP protocol is the third SQL interface. Its behavior is "closer" to PG and it supports "SHOW CREATE TABLE" clause.

[MichaelScofield]

If it's "third", it's supposed to be neutral. Actually in this case, it should be biased to mysql, because "show create table" is mysql only.

[waynexia]

Then you won't be able to execute the SQL you get right in where you get it. Result of SHOW CREATE TABLE in the dashboard cannot be run in the dashboard. This is more wired to me.

Given the SQL itself is a mess of substandard things, I won't recommend you mix different protocols in any case. We are compatible with many SQL clients doesn't mean we can (and it's not even possible) fill the gap between different protocols.

[tisonkun]

One more point is that quote is significant for case sensitive, so we cannot simply drop the quote.

This happens commonly when we accept "measurements" in influxdb line protocol or prometheus that are in camelCase.

[killme2008]

Agree with @waynexia , the HTTP endpoint in the dashboard is another SQL protocol, you can't expect the result to be executed directly in MySQL Client, but you should able to run it in the dashboard too.

[tisonkun]

Closed as not a bug.

[killme2008]

I have a new idea that makes dashboard support sql dialect selection, the user can choose their favorite dialect including:

• MySQL
• PG
• GreptimeDB(or generic, the default chosen)

[waynexia]

We can support this via another SET clause. I remember MySQL also provides an ORACLE-compatible mode by setting, which will change some behaviors.