[sync materialized view]result error when show materialized view use where

[colorfulu]

Steps to reproduce the behavior (Required)

CREATE TABLE `t1` (
                                    `k1` date,
                                    `k2` int,
                                    `k3` int
                                ) ENGINE=OLAP
                                DUPLICATE KEY(`k1`)
                                COMMENT "OLAP"
                                PARTITION BY RANGE (k1) (
                                START ("2020-10-01") END ("2022-03-04") EVERY (INTERVAL 15 day)
                                )
                                DISTRIBUTED BY HASH(`k1`) BUCKETS 3;
CREATE MATERIALIZED VIEW mv1
                             PARTITION BY k1
                             DISTRIBUTED BY HASH(k1) BUCKETS 10
                             REFRESH ASYNC
                             AS SELECT k1, k2
                             FROM t1;
show materialized view where name = "mv1";

Expected behavior (Required)

+-------+------+-----------------------+-------------------------------------------------------------------------------+------+
| id    | name | database_name         | text                                                                          | rows |
+-------+------+-----------------------+-------------------------------------------------------------------------------+------+
| 62700 | mv1  | default_cluster:yn_db | SELECT `yn_db`.`t1`.`k1` AS `k1`, `yn_db`.`t1`.`k2` AS `k2` FROM `yn_db`.`t1` | 0    |
+-------+------+-----------------------+-------------------------------------------------------------------------------+------+
1 row in set (0.00 sec)

Real behavior (Required)

+-------+------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+
| id    | name | database_name | text                                                                                                                                                                                                                                                                                  | rows |
+-------+------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+
| 78582 | mv1  | yn_db         | CREATE MATERIALIZED VIEW `mv1`
COMMENT "MATERIALIZED_VIEW"
PARTITION BY (`k1`)
DISTRIBUTED BY HASH(`k1`) BUCKETS 10
REFRESH ASYNC
PROPERTIES (
"replication_num" = "3",
"storage_medium" = "HDD"
)
AS SELECT `yn_db`.`t1`.`k1` AS `k1`, `yn_db`.`t1`.`k2` AS `k2` FROM `yn_db`.`t1`; | 0    |
+-------+------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+
1 row in set (0.04 sec)

StarRocks version (Required)

• You can get the StarRocks version by executing SQL select current_version()

mysql> select current_version();
+----------------------------+
| current_version()          |
+----------------------------+
| BRANCH-2.4-RELEASE e0e5c98 |
+----------------------------+

[banmoy]

I'd like to take a look, and @Astralidea could you please assign this issue to me?

[banmoy]

The behavior for show materialized view where name = "mv1" is as expected, which follows the original behavior when a materialized view is created without REFRESH ASYNC. The problem is that show materialized view will return a wrong sql text different from that with a where clause for ASYNC mv, and should be corrected.