【详述】包含now()函数的雾化视图无法更新。
【StarRocks版本】例如：2.5.4
【雾化视图任务更新定义】insert overwrite t_compose_11e8012626104d3081193db080184bfb SELECT temp1.stu_name, temp1.stu_no, temp1.stu_major, now() AS 1687837358533
FROM (SELECT db_bi_74449.temp0.stu_name, db_bi_74449.temp0.stu_no, db_bi_74449.temp0.stu_major
FROM db_bi_74449.t_sql_info_1687315304181 AS temp0) temp1
你好，能提供更多的信息吗？base表的建表结构和MV的建表语句。还有一些报错的信息。报什么错。
我们也会尝试复现一下，看看是什么问题。
base表就是简单的三个字段：姓名、学号、入学时间戳
mv的建表语句：
DROP MATERIALIZED VIEW t_compose_11e8012626104d3081193db080184bfb
CREATE MATERIALIZED VIEW t_compose_11e8012626104d3081193db080184bfb
COMMENT “MATERIALIZED_VIEW”
DISTRIBUTED BY HASH(stu_name) BUCKETS 4
REFRESH ASYNC START(“2023-06-27 11:43:01”) EVERY(INTERVAL 3650 DAY)
PROPERTIES (
“replication_num” = “1”,
“storage_medium” = “HDD”
)
AS SELECT temp1.stu_name, temp1.stu_no, temp1.stu_major, now() AS 1687837358533
FROM (SELECT temp0.stu_name, temp0.stu_no, temp0.stu_major
FROM db_bi_74449.t_sql_info_1687315304181 AS temp0) temp1;
你这个我试了一下，应该没办法创建成功吧。
now() AS 1687837358533
这里字段名不能是纯数字吧。
我用now() as now模拟吧
mysql> CREATE TABLE t_sql_info_1687315304181 (
-> stu_name VARCHAR(256),
-> stu_no VARCHAR(256),
-> stu_major VARCHAR(256)
-> ) DISTRIBUTED BY HASH(stu_name) BUCKETS 4
-> PROPERTIES (
-> “replication_num” = “1”,
-> “storage_medium” = “HDD”
-> );
Query OK, 0 rows affected (0.28 sec)
mysql> drop MATERIALIZED VIEW t_compose_11e8012626104d3081193db080184bfb;
Query OK, 0 rows affected (0.11 sec)
mysql>
mysql> CREATE MATERIALIZED VIEW t_compose_11e8012626104d3081193db080184bfb
-> COMMENT “MATERIALIZED_VIEW”
-> DISTRIBUTED BY HASH(stu_name) BUCKETS 4
-> REFRESH ASYNC EVERY(INTERVAL 3650 DAY)
-> PROPERTIES (
-> “replication_num” = “1”,
-> “storage_medium” = “HDD”
-> )
-> AS SELECT temp1.stu_name, temp1.stu_no, temp1.stu_major, now() AS now
-> FROM (SELECT temp0.stu_name, temp0.stu_no, temp0.stu_major
-> FROM t_sql_info_1687315304181 AS temp0) temp1;
Query OK, 0 rows affected (0.16 sec)
mysql> INSERT INTO t_sql_info_1687315304181 (stu_name, stu_no, stu_major)
-> VALUES (‘张三’, ‘123456’, ‘计算机科学’);
Query OK, 1 row affected (2.37 sec)
{‘label’:‘insert_47fbae2e-165d-11ee-aaf7-0242676308ed’, ‘status’:‘VISIBLE’, ‘txnId’:‘2004’}
mysql> select * from t_compose_11e8012626104d3081193db080184bfb;
Empty set (0.67 sec)
mysql> refresh MATERIALIZED VIEW t_compose_11e8012626104d3081193db080184bfb;
±-------------------------------------+
| QUERY_ID |
±-------------------------------------+
| 65a3b0eb-165d-11ee-aaf7-0242676308ed |
±-------------------------------------+
1 row in set (0.05 sec)
mysql> select * from t_compose_11e8012626104d3081193db080184bfb;
±---------±-------±----------------±--------------------+
| stu_name | stu_no | stu_major | now |
±---------±-------±----------------±--------------------+
| 张三 | 123456 | 计算机科学 | 2023-06-29 17:14:49 |
±---------±-------±----------------±--------------------+
1 row in set (0.20 sec)
mysql> INSERT INTO t_sql_info_1687315304181 (stu_name, stu_no, stu_major)
-> VALUES (‘张三’, ‘1234567’, ‘计算机科学’);
Query OK, 1 row affected (0.24 sec)
{‘label’:‘insert_6e4e78c0-165d-11ee-aaf7-0242676308ed’, ‘status’:‘VISIBLE’, ‘txnId’:‘2009’}
mysql> refresh MATERIALIZED VIEW t_compose_11e8012626104d3081193db080184bfb;
±-------------------------------------+
| QUERY_ID |
±-------------------------------------+
| 750279a2-165d-11ee-aaf7-0242676308ed |
±-------------------------------------+
1 row in set (0.32 sec)
mysql> select * from t_compose_11e8012626104d3081193db080184bfb;
±---------±--------±----------------±--------------------+
| stu_name | stu_no | stu_major | now |
±---------±--------±----------------±--------------------+
| 张三 | 123456 | 计算机科学 | 2023-06-29 17:15:14 |
| 张三 | 1234567 | 计算机科学 | 2023-06-29 17:15:14 |
±---------±--------±----------------±--------------------+
2 rows in set (0.18 sec)
mysql>
我本地模拟测试了一下，没有复现。你能告诉我具体报了什么错误吗？
V3.2.11有类似的问题，基于外部表创建物化视图，视图中有时间函数，虽然视图数据可以正常访问，但是查询外部表是不能命中物化视图，每次都要重新读取外部表数据。
物化视图创建语句：
CREATE MATERIALIZED VIEW mv_async_fix7dt_a_fk_gj_rent_rate_dtl_tbl_test
PARTITION BY (dt)
DISTRIBUTED BY HASH(cust_id)
REFRESH ASYNC START(“2024-07-20 10:00:00”) EVERY(INTERVAL 1 DAY)
PROPERTIES (
“replicated_storage” = “true”,
“replication_num” = “1”,
“storage_medium” = “HDD”
)
AS SELECT * FROM test.adl.a_fk_gj_rent_rate_dtl_tbl_test
WHERE dt >= cast(date_format(date_add(now(), interval -1 year),’%Y%m%d’) as int) ；