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.26k stars 3.2k forks source link

SQL execution error #5603

Open hf200012 opened 3 years ago

hf200012 commented 3 years ago

SQL：

select customer_name,dt,sum(daily_sales) from dws.test_crm_part_wudi_2 where dt>='2021-03-01' and dt<='2022-03-11' group by customer_name,dt

This SQL execution error It’s no problem to change the order of customer_name and dt.

SQL does not make any changes，This SQL has been executed more than a dozen times and I don’t know why there is no error

Create table SQL：

CREATE TABLE test_crm_part_wudi_2 ( dt date NULL COMMENT "天", city_name varchar(50) NULL COMMENT "城市名称", city_code varchar(50) NULL COMMENT "城市编码", customer_code varchar(50) NULL COMMENT "客户代码", customer_name varchar(150) NULL COMMENT "客户简称", sales_leader varchar(150) NULL COMMENT "销售组长", customer_delegate varchar(150) NULL COMMENT "客户代表", customer_strategy varchar(150) NULL COMMENT "客户策略", second_level_classify_name varchar(150) NULL COMMENT "二级分类名称", customer_category_name varchar(150) NULL COMMENT "客户类别名称", customer_classify_name varchar(150) NULL COMMENT "客户分类名称", daily_sales decimal(10, 2) NULL COMMENT "销售额", mt varchar(7) NULL COMMENT "月", create_time date NULL COMMENT "入库时间" ) ENGINE=OLAP UNIQUE KEY(dt, city_name, city_code, customer_code, customer_name) COMMENT "OLAP" PARTITION BY RANGE(dt) (PARTITION P_202101 VALUES [('2021-01-01'), ('2021-02-01')), PARTITION P_202102 VALUES [('2021-02-01'), ('2021-03-01')), PARTITION P_202103 VALUES [('2021-03-01'), ('2021-04-01')), PARTITION P_202104 VALUES [('2021-04-01'), ('2021-05-01')), PARTITION P_202105 VALUES [('2021-05-01'), ('2021-06-01')), PARTITION P_202106 VALUES [('2021-06-01'), ('2021-07-01'))) DISTRIBUTED BY HASH(customer_code) BUCKETS 1 PROPERTIES ( "replication_num" = "2", "dynamic_partition.enable" = "true", "dynamic_partition.time_unit" = "MONTH", "dynamic_partition.time_zone" = "Asia/Shanghai", "dynamic_partition.start" = "-2147483648", "dynamic_partition.end" = "2", "dynamicpartition.prefix" = "P", "dynamic_partition.replication_num" = "2", "dynamic_partition.buckets" = "1", "dynamic_partition.start_day_of_month" = "1", "in_memory" = "false", "storage_format" = "V2" );

FE log：

2021-04-06 14:56:51,082 INFO (thrift-server-pool-20|248) [QeProcessorImpl.reportExecStatus():109] ReportExecStatus(): fragment_instance_id=c66a3423f9d64013-87256a6609e67f9a, query id=c66a3423f9d64013-87256a6609e67f98, backend num: 4, ip: TNetworkAddress(hostname:172.22.197.250, port:35974)

2021-04-06 14:56:51,086 INFO (thrift-server-pool-13|229) [QeProcessorImpl.reportExecStatus():109] ReportExecStatus(): fragment_instance_id=c66a3423f9d64013-87256a6609e67f99, query id=c66a3423f9d64013-87256a6609e67f98, backend num: 3, ip: TNetworkAddress(hostname:172.22.197.249, port:42062)

2021-04-06 14:56:51,090 INFO (thrift-server-pool-19|245) [QeProcessorImpl.reportExecStatus():109] ReportExecStatus(): fragment_instance_id=c66a3423f9d64013-87256a6609e67f9b, query id=c66a3423f9d64013-87256a6609e67f98, backend num: 1, ip: TNetworkAddress(hostname:172.22.197.249, port:43396)

2021-04-06 14:56:51,090 INFO (thrift-server-pool-14|232) [QeProcessorImpl.reportExecStatus():109] ReportExecStatus(): fragment_instance_id=c66a3423f9d64013-87256a6609e67f9c, query id=c66a3423f9d64013-87256a6609e67f98, backend num: 2, ip: TNetworkAddress(hostname:172.22.197.250, port:60934)

2021-04-06 14:56:51,092 INFO (thrift-server-pool-15|234) [QeProcessorImpl.reportExecStatus():109] ReportExecStatus(): fragment_instance_id=c66a3423f9d64013-87256a6609e67f9d, query id=c66a3423f9d64013-87256a6609e67f98, backend num: 0, ip: TNetworkAddress(hostname:172.22.197.241, port:39112)

2021-04-06 14:56:51,092 INFO (doris-mysql-nio-pool-64|1907) [RowBatchBuilder.buildPartitionUpdateRequest():137] cant find partition key 20210313

2021-04-06 14:56:51,092 INFO (doris-mysql-nio-pool-64|1907) [RowBatchBuilder.buildPartitionUpdateRequest():137] cant find partition key 20210404

2021-04-06 14:56:51,092 INFO (doris-mysql-nio-pool-64|1907) [QeProcessorImpl.unregisterQuery():78] deregister query id c66a3423f9d64013-87256a6609e67f98

2021-04-06 14:56:51,092 WARN (doris-mysql-nio-pool-64|1907) [StmtExecutor.execute():364] execute Exception

java.lang.IllegalArgumentException: null

at java.nio.Buffer.position(Buffer.java:244) ~[?:1.8.0_261]

at org.apache.doris.qe.cache.RowBatchBuilder.getKeyFromRow(RowBatchBuilder.java:113) ~[palo-fe.jar:3.4.0]

at org.apache.doris.qe.cache.RowBatchBuilder.buildPartitionUpdateRequest(RowBatchBuilder.java:135) ~[palo-fe.jar:3.4.0]

at org.apache.doris.qe.cache.PartitionCache.updateCache(PartitionCache.java:128) ~[palo-fe.jar:3.4.0]

at org.apache.doris.qe.cache.CacheAnalyzer.updateCache(CacheAnalyzer.java:454) ~[palo-fe.jar:3.4.0]

at org.apache.doris.qe.StmtExecutor.handleCacheStmt(StmtExecutor.java:732) ~[palo-fe.jar:3.4.0]

at org.apache.doris.qe.StmtExecutor.handleQueryStmt(StmtExecutor.java:772) ~[palo-fe.jar:3.4.0]

at org.apache.doris.qe.StmtExecutor.execute(StmtExecutor.java:300) ~[palo-fe.jar:3.4.0]

at org.apache.doris.qe.StmtExecutor.execute(StmtExecutor.java:245) ~[palo-fe.jar:3.4.0]

at org.apache.doris.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:202) ~[palo-fe.jar:3.4.0]

at org.apache.doris.qe.ConnectProcessor.dispatch(ConnectProcessor.java:334) ~[palo-fe.jar:3.4.0]

at org.apache.doris.qe.ConnectProcessor.processOnce(ConnectProcessor.java:536) ~[palo-fe.jar:3.4.0]

at org.apache.doris.mysql.nio.ReadListener.lambda$handleEvent$0(ReadListener.java:50) ~[palo-fe.jar:3.4.0]

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_261]

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_261]

at java.lang.Thread.run(Thread.java:748) [?:1.8.0_261]

2021-04-06 14:56:51,179 INFO (doris-mysql-nio-pool-64|1907) [StmtExecutor.analyze():426] begin to analyze stmt: 9730, forwarded stmt id: 0

2021-04-06 14:56:51,189 INFO (replayer|70) [Catalog.replayJournal():2489] replayed journal id is 49295149, replay to journal id is 49295150

2021-04-06 14:56:51,206 INFO (doris-mysql-nio-pool-64|1907) [StmtExecutor.analyze():426] begin to analyze stmt: 9731, forwarded stmt id: 0

2021-04-06 14:56:51,206 WARN (doris-mysql-nio-pool-64|1907) [StmtExecutor.execute():360] execute Exception. {}

org.apache.doris.common.AnalysisException: errCode = 2, detailMessage = Unknown table 'PROFILING'

at org.apache.doris.common.ErrorReport.reportAnalysisException(ErrorReport.java:52) ~[palo-fe.jar:3.4.0]

at org.apache.doris.common.ErrorReport.reportAnalysisException(ErrorReport.java:47) ~[palo-fe.jar:3.4.0]

at org.apache.doris.analysis.Analyzer.resolveTableRef(Analyzer.java:504) ~[palo-fe.jar:3.4.0]

at org.apache.doris.analysis.FromClause.analyze(FromClause.java:148) ~[palo-fe.jar:3.4.0]

at org.apache.doris.analysis.SelectStmt.analyze(SelectStmt.java:366) ~[palo-fe.jar:3.4.0]

at org.apache.doris.qe.StmtExecutor.analyzeAndGenerateQueryPlan(StmtExecutor.java:525) ~[palo-fe.jar:3.4.0]

at org.apache.doris.qe.StmtExecutor.analyze(StmtExecutor.java:495) ~[palo-fe.jar:3.4.0]

at org.apache.doris.qe.StmtExecutor.execute(StmtExecutor.java:274) ~[palo-fe.jar:3.4.0]

at org.apache.doris.qe.StmtExecutor.execute(StmtExecutor.java:245) ~[palo-fe.jar:3.4.0]

at org.apache.doris.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:202) ~[palo-fe.jar:3.4.0]

at org.apache.doris.qe.ConnectProcessor.dispatch(ConnectProcessor.java:334) ~[palo-fe.jar:3.4.0]

at org.apache.doris.qe.ConnectProcessor.processOnce(ConnectProcessor.java:536) ~[palo-fe.jar:3.4.0]

at org.apache.doris.mysql.nio.ReadListener.lambda$handleEvent$0(ReadListener.java:50) ~[palo-fe.jar:3.4.0]

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_261]

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_261]

at java.lang.Thread.run(Thread.java:748) [?:1.8.0_261]

2021-04-06 14:56:51,275 INFO (replayer|70) [Catalog.replayJournal():2489] replayed journal id is 49295150, replay to journal id is 49295151

2021-04-06 14:56:51,355 INFO (replayer|70) [Catalog.replayJournal():2489] replayed journal id is 49295151, replay to journal id is 49295152

2021-04-06 14:56:51,555 INFO (replayer|70) [Catalog.replayJournal():2489] replayed journal id is 49295152, replay to journal id is 49295153

2021-04-06 14:56:51,690 INFO (replayer|70) [Catalog.replayJournal():2489] replayed journal id is 49295153, replay to journal id is 49295154

2021-04-06 14:56:51,717 INFO (replayer|70) [DatabaseTransactionMgr.replayUpsertTransactionState():1384] replay a committed transaction TransactionState. transaction id: 17138414, label: 76a5fd3dc9064670-ac1c2207a107f4f8, db id: 11001, table id list: 15399244, callback id: 15445991, coordinator: FE: 172.22.197.240, transaction status: COMMITTED, error replicas num: 0, replica ids: , prepare time: 1617692151111, commit time: 1617692211666, finish time: -1, reason: attactment: RLTaskTxnCommitAttachment [filteredRows=0, loadedRows=3831, unselectedRows=0, receivedBytes=5336776, taskExecutionTimeMs=60549, taskId=null, jobId=0, progress=KafkaProgress [partitionIdToOffset=3_377730427]]

2021-04-06 14:56:51,755 INFO (replayer|70) [Catalog.replayJournal():2489] replayed journal id is 49295154, replay to journal id is 49295155

2021-04-06 14:56:51,780 INFO (replayer|70) [DatabaseTransactionMgr.replayUpsertTransactionState():1387] replay a visible transaction TransactionState. transaction id: 17138414, label: 76a5fd3dc9064670-ac1c2207a107f4f8, db id: 11001, table id list: 15399244, callback id: 15445991, coordinator: FE: 172.22.197.240, transaction status: VISIBLE, error replicas num: 0, replica ids: , prepare time: 1617692151111, commit time: 1617692211666, finish time: 1617692211728, reason: attactment: RLTaskTxnCommitAttachment [filteredRows=0, loadedRows=3831, unselectedRows=0, receivedBytes=5336776, taskExecutionTimeMs=60549, taskId=null, jobId=0, progress=KafkaProgress [partitionIdToOffset=3_377730427]]

2021-04-06 14:56:51,807 INFO (replayer|70) [Catalog.replayJournal():2489] replayed journal id is 49295155, replay to journal id is 49295156

2021-04-06 14:56:52,337 INFO (replayer|70) [Catalog.replayJournal():2489] replayed journal id is 49295156, replay to journal id is 49295157

2021-04-06 14:56:52,783 INFO (replayer|70) [Catalog.replayJournal():2489] replayed journal id is 49295157, replay to journal id is 49295158

HappenLee commented 3 years ago

Hi，please give the info of doris version you are using now.

hf200012 commented 3 years ago

Hi，please give the info of doris version you are using now.

It should be caused by partition cache, set set SESSION enable_partition_cache=false; sql can be executed successfully

doris-0.13.15 doris-0.14.7