创建异步物化视图sql中使用函数TIMESTAMPDIFF时报错

Viewed 25

版本:apache-doris-2.1.2-bin-x64
sql如下:
CREATE MATERIALIZED VIEW mv_dws_content_tag_summary2
REFRESH COMPLETE ON SCHEDULE EVERY 1 minute
DISTRIBUTED BY HASH(t.layer_type,t.tag) BUCKETS 5
PROPERTIES ('replication_num' = '1')
AS
SELECT t.layer_type,t.tag
,SUM(IF(t.layer_type='entity' AND TIMESTAMPDIFF(DAY,t.createtime,NOW())<7,1,0)) Last7daysTotalCount --近7天总数
,SUM(IF(t.layer_type='video',1,0)) AS videoTotalCount
FROM dwd_content_tag_detail t
group by t.layer_type,t.tag

执行sql时报错信息如下:
org.jkiss.dbeaver.model.sql.DBSQLException: SQL 错误 [1105] [HY000]: errCode = 2, detailMessage = Syntax error in line 2:
REFRESH COMPLETE ON SCHEDULE EVERY 1 minute

^
Encountered: REFRESH
Expected

at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)

at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:614)

at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$2(SQLQueryJob.java:505)

at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:190)

at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:524)

at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:976)

at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:4133)

at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123)

at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:190)

at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121)

at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5148)

at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:114)

at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)

Caused by: java.sql.SQLException: errCode = 2, detailMessage = Syntax error in line 2:
REFRESH COMPLETE ON SCHEDULE EVERY 1 minute

^
Encountered: REFRESH
Expected

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:130)

at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)

at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:767)

at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:652)

at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:330)

at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)

... 12 more
1 Answers

在创建物化视图的properties中加入:
enable_nondeterministic_function=true