search

apache / doris

Apache Doris is an easy-to-use, high performance and unified analytics database.
https://doris.apache.org
Apache License 2.0
12.39k stars 3.22k forks source link

[Bug] Illegal column UInt64 of first argument of function toDateTime on INSERT prepared statement with datetime column #30112

Open geoffreytran opened 8 months ago

geoffreytran commented 8 months ago

Search before asking

Version

2.0.3

What's Wrong?

When inserting a record using a prepared statement with parameters for a datetime column using the MySQL datetime DB type, Doris throws the following exception. This does not occur on UPDATE statements.

Inserting with the value as a string works correctly, but some connection drivers automatically bind datetime datatypes as datetime over the MySQL protocol. This behavior is failing for insert statements that bind the MYSQL_TIME structure. https://dev.mysql.com/doc/c-api/5.7/en/c-api-prepared-statement-date-handling.html

2024-01-18 03:47:55,333 WARN (thrift-server-pool-3|172) [Coordinator.updateFragmentExecStatus():2275] one instance report fail, query_id=6fa06d4a60c543f8-b9db8baaae6e6ac7 instance_id=6fa06d4a60c543f8-b9db8baaae6e6ac8, error message: (172.16.81.5)[RUNTIME_ERROR]Illegal column UInt64 of first argument of function toDateTime
2024-01-18 03:47:55,334 WARN (thrift-server-pool-3|172) [Coordinator.updateStatus():1129] one instance report fail throw updateStatus(), need cancel. job id: -1, query id: 6fa06d4a60c543f8-b9db8baaae6e6ac7, instance id: 6fa06d4a60c543f8-b9db8baaae6e6ac8, error message: (172.16.81.5)[RUNTIME_ERROR]Illegal column UInt64 of first argument of function toDateTime
2024-01-18 03:47:55,334 WARN (mysql-nio-pool-20|3459) [StmtExecutor.handleInsertStmt():1805] insert failed: (172.16.81.5)[RUNTIME_ERROR]Illegal column UInt64 of first argument of function toDateTime
2024-01-18 03:47:55,335 WARN (mysql-nio-pool-20|3459) [StmtExecutor.handleInsertStmt():1846] handle insert stmt fail: insert_6fa06d4a60c543f8_b9db8baaae6e6ac7
org.apache.doris.common.DdlException: errCode = 2, detailMessage = (172.16.81.5)[RUNTIME_ERROR]Illegal column UInt64 of first argument of function toDateTime
    at org.apache.doris.common.ErrorReport.reportDdlException(ErrorReport.java:68) ~[doris-fe.jar:1.2-SNAPSHOT]
    at org.apache.doris.qe.StmtExecutor.handleInsertStmt(StmtExecutor.java:1806) ~[doris-fe.jar:1.2-SNAPSHOT]
    at org.apache.doris.qe.StmtExecutor.executeByLegacy(StmtExecutor.java:748) ~[doris-fe.jar:1.2-SNAPSHOT]
    at org.apache.doris.qe.StmtExecutor.execute(StmtExecutor.java:459) ~[doris-fe.jar:1.2-SNAPSHOT]
    at org.apache.doris.qe.StmtExecutor.execute(StmtExecutor.java:429) ~[doris-fe.jar:1.2-SNAPSHOT]
    at org.apache.doris.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:435) ~[doris-fe.jar:1.2-SNAPSHOT]
    at org.apache.doris.qe.ConnectProcessor.dispatch(ConnectProcessor.java:583) ~[doris-fe.jar:1.2-SNAPSHOT]
    at org.apache.doris.qe.ConnectProcessor.processOnce(ConnectProcessor.java:834) ~[doris-fe.jar:1.2-SNAPSHOT]
    at org.apache.doris.mysql.ReadListener.lambda$handleEvent$0(ReadListener.java:52) ~[doris-fe.jar:1.2-SNAPSHOT]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_342]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_342]
    at java.lang.Thread.run(Thread.java:750) ~[?:1.8.0_342]
2024-01-18 03:47:55,336 WARN (mysql-nio-pool-20|3459) [StmtExecutor.executeByLegacy():818] errors when abort txn. stmt[584, 6fa06d4a60c543f8-b9db8baaae6e6ac7]
org.apache.doris.transaction.TransactionNotFoundException: errCode = 2, detailMessage = transaction not found
    at org.apache.doris.transaction.DatabaseTransactionMgr.abortTransaction(DatabaseTransactionMgr.java:1369) ~[doris-fe.jar:1.2-SNAPSHOT]
    at org.apache.doris.transaction.GlobalTransactionMgr.abortTransaction(GlobalTransactionMgr.java:334) ~[doris-fe.jar:1.2-SNAPSHOT]
    at org.apache.doris.transaction.GlobalTransactionMgr.abortTransaction(GlobalTransactionMgr.java:318) ~[doris-fe.jar:1.2-SNAPSHOT]
    at org.apache.doris.qe.StmtExecutor.executeByLegacy(StmtExecutor.java:814) ~[doris-fe.jar:1.2-SNAPSHOT]
    at org.apache.doris.qe.StmtExecutor.execute(StmtExecutor.java:459) ~[doris-fe.jar:1.2-SNAPSHOT]
    at org.apache.doris.qe.StmtExecutor.execute(StmtExecutor.java:429) ~[doris-fe.jar:1.2-SNAPSHOT]
    at org.apache.doris.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:435) ~[doris-fe.jar:1.2-SNAPSHOT]
    at org.apache.doris.qe.ConnectProcessor.dispatch(ConnectProcessor.java:583) ~[doris-fe.jar:1.2-SNAPSHOT]
    at org.apache.doris.qe.ConnectProcessor.processOnce(ConnectProcessor.java:834) ~[doris-fe.jar:1.2-SNAPSHOT]
    at org.apache.doris.mysql.ReadListener.lambda$handleEvent$0(ReadListener.java:52) ~[doris-fe.jar:1.2-SNAPSHOT]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_342]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_342]
    at java.lang.Thread.run(Thread.java:750) ~[?:1.8.0_342]

What You Expected?

INSERT statements using parameters with MYSQL_TIME data structures should correctly be processed.

How to Reproduce?

create table if not exists insert_test (
    id int not null comment "id",
    created_at datetime not null default current_timestamp(0) comment "Created at"
)
engine=olap
unique key (id)
distributed by hash(id)
properties (
    "replication_allocation" = "tag.location.default: 3",
    -- "enable_unique_key_merge_on_write" = "true",
    "store_row_column" = "true",
    "light_schema_change" = "true"
);

SQL bind query parameters

The created_at column bind parameter should be sent as a MYSQL_TIME data structure not a string. https://dev.mysql.com/doc/c-api/5.7/en/c-api-prepared-statement-date-handling.html

[
  Parameters=[
    @p0='018d1d6a-92f2-72b3-854e-711e872c8167' (Nullable = false) (Size = 255), 
    @p1='2024-01-18T16:32:20.7256480Z' (DbType = DateTime)
  ], 
  CommandType='"Text"', 
  CommandTimeout='30'
]
SET AUTOCOMMIT = 1;
INSERT INTO `insert_test` (`id`, `created_at`)
VALUES (@p0, @p1);

Anything Else?

No response

Are you willing to submit PR?

Code of Conduct

Muzi999 commented 6 months ago

I'm also having this problem , I don't know how to fix it at the moment Version 2.0.4