The logical table in the metric engine does not follow the defined column order when insert

[killme2008]

What type of bug is this?

Unexpected error

What subsystems are affected?

Storage Engine

Minimal reproduce step

For example, let's create a logical table and try to insert some data:

CREATE TABLE phy (ts timestamp time index, val double) engine=metric 
    with ("physical_metric_table" = "");

CREATE TABLE test_ttl (ts timestamp time index, val double, host string primary key)
    engine = metric with ("on_physical_table" = "phy");

INSERT INTO test_ttl VALUES
       (now(), 1, 'host1'),
       (now(), 2, 'host2'),
       (now(), 3, 'host3');

It fails right now:

Error: 3001(EngineExecuteQuery), Cast error: Cannot cast string ‘host1’ to value of Float64 type

I must specify the columns in insert statement:

INSERT INTO test_ttl(ts, val, host) VALUES
       (now(), 1, 'host1'),
       (now(), 2, 'host2'),
       (now(), 3, 'host3');

What did you expect to see?

Sucess to insert

What did you see instead?

Fails with

Error: 3001(EngineExecuteQuery), Cast error: Cannot cast string ‘host1’ to value of Float64 type

What operating system did you use?

All

What version of GreptimeDB did you use?

v0.10.2

Relevant log output and stack trace

No response

[NiwakaDev]

@killme2008

I'm interested in the issue.

This lines seems to result in the bug. In creating a logical table, this is executed.

greptimedb/src/common/meta/src/ddl/create_logical_tables/metadata.rs

Line 52 in ff4c153

task.sort_columns();

Once removed the line, the bug doesn't happen. But, I don't know whether it is fine to remove it.

[killme2008]

@killme2008

I'm interested in the issue.

This lines seems to result in the bug. In creating a logical table, this is executed.

greptimedb/src/common/meta/src/ddl/create_logical_tables/metadata.rs

Line 52 in ff4c153

task.sort_columns();

Once removed the line, the bug doesn't happen. But, I don't know whether it is fine to remove it.

Thank you for your investigation. @waynexia is the designer of the metric engine. What are your thoughts on @NiwakaDev's suggestion?

[evenyag]

The metric engine requires this to ensure the logical table and the physical table have the same column order and the datanode can create the same column id for the columns.

[waynexia]

This is intended behavior. Metric engine will send its updated schema to metasrv in the response, for metasrv to keep in sync with metric engine.

But this behavior might not be the most optimal option. As the user interface seems hard to use. We can change it if needed by adding a pre-create request round-trip between metasrv and metric engine to negotiate the proper schema and column id.