Failed to support insert data without specify the `ts` column when using `?` placeholder

[zyy17]

What type of bug is this?

Unexpected error

What subsystems are affected?

Query Engine

Minimal reproduce step

Test code

I'm using the Go and github.com/go-sql-driver/mysql to connect greptimedb:

package main

import (
    "context"
    "database/sql"

    "github.com/go-sql-driver/mysql"
)

func main() {
    if err := testDB(); err != nil {
        panic(err)
    }
}

func testDB() error {
    cfg := mysql.Config{
        Net:                  "tcp",
        Addr:                 "localhost:4002",
        AllowNativePasswords: true,
    }
    ctx := context.Background()

    db, err := sql.Open("mysql", cfg.FormatDSN())
    if err != nil {
        return err
    }

    conn, err := db.Conn(ctx)
    if err != nil {
        return err
    }

    createStandaloneTableSQL := `CREATE TABLE test_table(
                            ts TIMESTAMP DEFAULT current_timestamp(),
                            n INT,
                            row_id INT,
                            PRIMARY KEY(n),
                            TIME INDEX (ts)
                          )
                          engine=mito`

    _, err = db.Exec(createStandaloneTableSQL)
    if err != nil {
        return err
    }

    insertDataSQL := `INSERT INTO test_table(n, row_id) VALUES (?, ?);`
    for i := 0; i < 10; i++ {
        _, err = conn.ExecContext(ctx, insertDataSQL, i, i)
        if err != nil {
            return err
        }
    }

    return nil
}

Test steps

1. Run the greptimedb

docker run -p 127.0.0.1:4000-4003:4000-4003 \
-v "$(pwd)/greptimedb:/tmp/greptimedb" \
--name greptime --rm \
greptime-registry.cn-hangzhou.cr.aliyuncs.com/greptime/greptimedb:v0.8.2 standalone start \
--http-addr 0.0.0.0:4000 \
--rpc-addr 0.0.0.0:4001 \
--mysql-addr 0.0.0.0:4002 \
--postgres-addr 0.0.0.0:4003

2. Run the test code

go run test-placeholder.go
panic: Error 1815: Invalid request to region 4398046511104(1024, 0), reason: column ts is not null but input has null
...

After multiple tests, the above test failed when the db release version > v0.7.2. It works well in v0.7.2.

3. Specify the ts column can work

package main

import (
    "context"
    "database/sql"
    "time"

    "github.com/go-sql-driver/mysql"
)

func main() {
    if err := testDB(); err != nil {
        panic(err)
    }
}

func testDB() error {
    cfg := mysql.Config{
        Net:                  "tcp",
        Addr:                 "localhost:4002",
        AllowNativePasswords: true,
    }
    ctx := context.Background()

    db, err := sql.Open("mysql", cfg.FormatDSN())
    if err != nil {
        return err
    }

    conn, err := db.Conn(ctx)
    if err != nil {
        return err
    }

    createStandaloneTableSQL := `CREATE TABLE test_table(
                            ts TIMESTAMP DEFAULT current_timestamp(),
                            n INT,
                            row_id INT,
                            PRIMARY KEY(n),
                            TIME INDEX (ts)
                          )
                          engine=mito`

    _, err = db.Exec(createStandaloneTableSQL)
    if err != nil {
        return err
    }

    insertDataSQL := `INSERT INTO test_table(n, row_id, ts) VALUES (?, ?, ?);`
    for i := 0; i < 10; i++ {
        now := time.Now()
        timestampInMillisecond := now.Unix()*1000 + int64(now.Nanosecond())/1e6
        _, err = conn.ExecContext(ctx, insertDataSQL, i, i, timestampInMillisecond)
        if err != nil {
            return err
        }
    }

    return nil
}

4. If I don't use the ? placeholder, it works

package main

import (
    "context"
    "database/sql"
    "fmt"

    "github.com/go-sql-driver/mysql"
)

func main() {
    if err := testDB(); err != nil {
        panic(err)
    }
}

func testDB() error {
    cfg := mysql.Config{
        Net:                  "tcp",
        Addr:                 "localhost:4002",
        AllowNativePasswords: true,
    }
    ctx := context.Background()

    db, err := sql.Open("mysql", cfg.FormatDSN())
    if err != nil {
        return err
    }

    conn, err := db.Conn(ctx)
    if err != nil {
        return err
    }

    createStandaloneTableSQL := `CREATE TABLE test_table(
                            ts TIMESTAMP DEFAULT current_timestamp(),
                            n INT,
                            row_id INT,
                            PRIMARY KEY(n),
                            TIME INDEX (ts)
                          )
                          engine=mito`

    _, err = db.Exec(createStandaloneTableSQL)
    if err != nil {
        return err
    }

    for i := 0; i < 10; i++ {
        insertDataSQL := fmt.Sprintf("INSERT INTO test_table(n, row_id) VALUES (%d, %d)", i, i)
        _, err = conn.ExecContext(ctx, insertDataSQL)
        if err != nil {
            return err
        }
    }

    return nil
}

What did you expect to see?

The test code should work.

What did you see instead?

The test code failed.

What operating system did you use?

macOS

What version of GreptimeDB did you use?

v0.8.2

Relevant log output and stack trace

No response

[zyy17]

@waynexia @evenyag

[killme2008]

Let me figure out.

[killme2008]

Java is good:

Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:4002/public", "root", "password");

        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO test_table(n, row_id) VALUES (?, ?);");

        long start = System.currentTimeMillis();
        for (int i = 0; i < 100000; i++) {
            pstmt.setInt(1, i);
            pstmt.setInt(2, i);
            pstmt.addBatch();

            if (i % 100 == 0 ){
                pstmt.executeBatch();
            }
        }

        pstmt.executeBatch();

Looks like it's related to https://github.com/GreptimeTeam/greptimedb/pull/4125

[killme2008]

Confirmed:

mysql> prepare stmt1 from 'INSERT INTO test_table(n, row_id) VALUES (?, ?)';
Query OK, 0 rows affected (0.01 sec)

mysql> EXECUTE stmt1 using 1, 1;
ERROR 1815 (HY000): Invalid request to region 4398046511104(1024, 0), reason: column ts is not null but input has null

Let me fix it.