show create table doesn't work for numbers

[sunng87]

What type of bug is this?

Incorrect result

What subsystems are affected?

Table Engine

Minimal reproduce step

just run sql show create table numbers

What did you expect to see?

sql statement for creating this table

What did you see instead?

"Failed to find table route: 2"

What operating system did you use?

any

What version of GreptimeDB did you use?

HEAD

Relevant log output and stack trace

No response

[tisonkun]

This is somehow not a bug. Because the numbers table is an internal immutable table and there is no way to create from user space.

However, we'd think of #1827 again.

[sunng87]

Actually using sql show create table is not for creating table but to see its definition and metadata. I think any table, or table-like object, that shows up in show tables and information_schema.tables should provide this ability.

Imagine that when you show table it says there is a table, but when you run show create table it says "failed to find table route", which sounds like an equivalent to "table not found".

[killme2008]

We can check the table type, it must be the Base type to show the table definition.

greptimedb/src/table/src/metadata.rs

Line 76 in 85a2318

Base,

Checking the table type in show_create_table and return an error if it's not Base type.

greptimedb/src/operator/src/statement/show.rs

Line 83 in 85a2318

) -> Result<Output> {

It's a good first issue. Let me label it.

[sunng87]

I think we still need a solution to return show create table content for non-Base type.

Because the table type is never exposed to user. The user won't have chance to know if a table is capable for show create table

[killme2008]

We'll have other statements to support other table types, such as show create view or show create flow.

We already have #3939 to track show create flow.

[killme2008]

If you want to know the table metadata, you can use desc table [table].
The show create table is mainly used to get the sql definition of the table and can be used to execute it directly by users.