Failed to query on metric table due to column order mismatch compared to the greptime_physical_table

[0neSe7en]

What type of bug is this?

Unexpected error

What subsystems are affected?

Datanode, Other

Minimal reproduce step

in version 0.7.1 create a table with opposite column order:

CREATE TABLE IF NOT EXISTS "test" (
  "greptime_value" DOUBLE NULL,
  "greptime_timestamp" TIMESTAMP(3) NOT NULL,
  TIME INDEX ("greptime_timestamp")
)

ENGINE=metric
WITH(
  on_physical_table = 'greptime_physical_table',
  regions = 1
)

insert data into test

INSERT INTO "test" (greptime_timestamp, greptime_value)
VALUES (1708606800000, 10)

then upgrade greptime to 0.7.2, and run

select * from 'test'

It seems like the columns are sorted when creating a table from 0.7.2, but existing tables with mismatched order should also be able to be queried.

What did you expect to see?

{
  "output": [
    {
      "records": {
        "schema": {
          "column_schemas": [
            {
              "name": "greptime_value",
              "data_type": "Float64"
            },
            {
              "name": "greptime_timestamp",
              "data_type": "TimestampMillisecond"
            }
          ]
        },
        "rows": [
          [
            10.0,
            1708606800000
          ]
        ]
      }
    }
  ],
  "execution_time_ms": 9
}

What did you see instead?

{
  "code": 1003,
  "error": "Internal error: 1003",
  "execution_time_ms": 25
}

What operating system did you use?

Mac OS 14.2.1 (23C71)

What version of GreptimeDB did you use?

0.7.2

Relevant log output and stack trace

2024-04-11T07:51:36.649558Z ERROR sql{protocol="http" request_type="sql"}: servers::http::error_result: Failed to handle HTTP request err=0: , at src/common/recordbatch/src/adapter.rs:250:55
1: External(0: Fail to create datafusion record batch, at /greptimedb/src/common/recordbatch/src/recordbatch.rs:51:14
1: InvalidArgumentError("column types must match schema types, expected Float64 but found Timestamp(Millisecond, None) at column index 0"))
2024-04-11T07:51:36.650284Z ERROR tower_http::trace::on_failure: response failed classification=Status code: 500 Internal Server Error latency=13 ms

[killme2008]

Thank you for your report, good catch! We'll fix it ASAP.

[waynexia]

This is expected when upgrading from v0.7.1 to v0.7.2 with metric engine. Write and read data all in v0.7.2 should avoid this. We are also building a migration tool to help upgrade metric engine table to v0.7.2. Will update here once it's available. Sorry for the inconvenience.

[killme2008]

This is expected when upgrading from v0.7.1 to v0.7.2 with metric engine. Write and read data all in v0.7.2 should avoid this. We are also building a migration tool to help upgrade metric engine table to v0.7.2. Will update here once it's available. Sorry for the inconvenience.

Do we finish the tool? If so, I think we can provide the doc to help users.

[killme2008]

Upgrade guide https://docs.greptime.com/user-guide/upgrade