Query by using materialized view query rewrite for iceberg table return partial result

[Fulton-x]

Steps to reproduce the behavior (Required)

1. Create iceberg table in AWS Athena,

   CREATE TABLE iceberg_test2 (
   prcdate date,
   price double,
   localcode string)
   PARTITIONED BY (month(prcdate))
   LOCATION 's3://bucket/directory/'
   TBLPROPERTIES (
   'table_type'='iceberg',
   'format'='PARQUET',
   'write_compression'='GZIP'
   );

2. Create external catalog in StarRocks and materialized view

   CREATE EXTERNAL CATALOG iceberg_catalog_glue_test
   PROPERTIES
   (
   "type" = "iceberg",
   "iceberg.catalog.type" = "glue",
   "aws.glue.use_instance_profile" = "true",
   "aws.glue.region" = "us-east-2",
   "aws.s3.use_instance_profile" = "true",
   "aws.s3.region" = "us-east-2"
   );

   CREATE MATERIALIZED VIEW test1view (prcdate, price, localcode)
   PARTITION BY (date_trunc('month', prcdate))
   DISTRIBUTED BY HASH(localcode)
   REFRESH MANUAL
   PROPERTIES (
   "replicated_storage" = "true",
   "force_external_table_query_rewrite" = "CHECKED",
   "query_rewrite_consistency" = "CHECKED",
   "mv_rewrite_staleness_second" = "120",
   "replication_num" = "1",
   "enable_query_rewrite" = "true"
   )
   AS
   SELECT prcdate, price, localcode FROM iceberg_catalog_glue_test.dev_db.iceberg_test2;

3. Query select DISTINCT prcdate from iceberg_catalog_glue_test.dev_db.iceberg_test2;

4. Insert some data to Athena table

   insert into iceberg_test2 values(date('2024-10-03'),10,'test1');
   insert into iceberg_test2 values(date('2024-10-05'),10,'test1');
   insert into iceberg_test2 values(date('2024-11-03'),10,'test1');

5. Refresh materilized view REFRESH MATERIALIZED VIEW test1view;

6. Query result is expected select DISTINCT prcdate from iceberg_catalog_glue_test.dev_db.iceberg_test2;

7. Further Insert some data to Athena table

   insert into iceberg_test2 values(date('2024-11-08'),10,'test1');

After a period of time, possible external table metadata had been refreshed
8. Query result is not expected select DISTINCT prcdate from iceberg_catalog_glue_test.dev_db.iceberg_test2;

Expected behavior (Required)

prcdate
2024-10-03
2024-10-05
2024-11-05
2024-11-08

Real behavior (Required)

prcdate
2024-10-05
2024-10-03

Query result of materialized view is expected SELECT distinct prcdate FROM test1view;

prcdate
2024-10-03
2024-10-05
2024-11-05

Here's the query explain,still materialized view was selected to use and looks one partition is not be queried explain select DISTINCT prcdate from iceberg_catalog_glue_test.dev_db.iceberg_test2

PLAN FRAGMENT 0
OUTPUT EXPRS:1: prcdate
PARTITION: UNPARTITIONED

RESULT SINK

5:EXCHANGE

PLAN FRAGMENT 1
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 1: prcdate

STREAM DATA SINK
EXCHANGE ID: 05
UNPARTITIONED

4:AGGREGATE (merge finalize)
| group by: 1: prcdate
|
3:EXCHANGE

PLAN FRAGMENT 2
OUTPUT EXPRS:
colocate exec groups: ExecGroup{groupId=1, nodeIds=[0, 1, 2]}
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 03
HASH_PARTITIONED: 1: prcdate

2:AGGREGATE (update serialize)
| STREAMING
| group by: 1: prcdate
|
1:Project
| <slot 1> : 4: prcdate
|
0:OlapScanNode
TABLE: test1view
PREAGGREGATION: ON
partitions=1/2
rollup: test1view
tabletRatio=6/6
tabletList=40459,40461,40463,40465,40467,40469
cardinality=2
avgRowSize=8.0
MaterializedView: true

StarRocks version (Required)

• 3.3.7

[LiShuMing]

Thanks for your questions. Let me check it.

[LiShuMing]

1. can you execute refresh external table iceberg_catalog_glue_test.dev_db.iceberg_test2 to refresh the external table's metadata and retry to query again?
2. can you set enable_materialized_view_rewrite=false; (disable mv rewrite and directly query from the external table) and check the result is fine?

[Fulton-x]

Thanks ,

1. As it passed long time, I now checked the query results it return expeceted. and then I try insert one new row insert into iceberg_test2 values(date('2024-11-03'),10,'test1');, and then refresh external table, then run the query, it return partial result

   "prcdate"
   2024-11-05
   2024-11-03
   2024-11-08

2. After set enable_materialized_view_rewrite=false; the query return expected results

   "prcdate"
   2024-10-03
   2024-10-05
   2024-10-06
   2024-11-03
   2024-11-05
   2024-11-08

[LiShuMing]

Thanks for your replies. This should be something wrong in mv's timeliness check.

Can you execute trace logs mv select DISTINCT prcdate from iceberg_catalog_glue_test.dev_db.iceberg_test2 to get some trace info?

[LiShuMing]

And I doubt it's about mv_rewrite_staleness_second property for external table.

Can you execute alter materialized view test1view set('mv_rewrite_staleness_second'='-1'); to reset it and check the result?

[Fulton-x]

Here's the trace log

1ms| [MV TRACE] [PREPARE GLOBAL] Query input tables: [Table [id=100000153, name=iceberg_test2, type=ICEBERG]]
2ms| [MV TRACE] [PREPARE GLOBAL] ---------------------------------
2ms| [MV TRACE] [PREPARE GLOBAL] Materialized View Enable/Disable Params:
2ms| [MV TRACE] [PREPARE GLOBAL] enable_experimental_mv: true
2ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_rewrite: true
2ms| [MV TRACE] [PREPARE GLOBAL] enable_view_based_mv_rewrite: true
2ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_union_rewrite: true
2ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_view_delta_rewrite: true
2ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_single_table_view_delta_rewrite: false
2ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_plan_cache: true
2ms| [MV TRACE] [PREPARE GLOBAL] mv_auto_analyze_async: true
2ms| [MV TRACE] [PREPARE GLOBAL] enable_mv_automatic_active_check: true
2ms| [MV TRACE] [PREPARE GLOBAL] enable_sync_materialized_view_rewrite: true
2ms| [MV TRACE] [PREPARE GLOBAL] enable_view_based_mv_rewrite: true
2ms| [MV TRACE] [PREPARE GLOBAL] enable_materialized_view_text_match_rewrite: true
2ms| [MV TRACE] [PREPARE GLOBAL] ---------------------------------
3ms| [MV TRACE] [PREPARE GLOBAL] Materialized View Limit Params:
3ms| [MV TRACE] [PREPARE GLOBAL] optimizer_materialized_view_timelimit: 1000
3ms| [MV TRACE] [PREPARE GLOBAL] materialized_view_join_same_table_permutation_limit: 5
3ms| [MV TRACE] [PREPARE GLOBAL] skip_whole_phase_lock_mv_limit: 5
3ms| [MV TRACE] [PREPARE GLOBAL] ---------------------------------
3ms| [MV TRACE] [PREPARE GLOBAL] Materialized View Config Params:
3ms| [MV TRACE] [PREPARE GLOBAL] analyze_mv: sample
3ms| [MV TRACE] [PREPARE GLOBAL] query_excluding_mv_names:
3ms| [MV TRACE] [PREPARE GLOBAL] query_including_mv_names:
3ms| [MV TRACE] [PREPARE GLOBAL] cbo_materialized_view_rewrite_rule_output_limit: 3
3ms| [MV TRACE] [PREPARE GLOBAL] cbo_materialized_view_rewrite_candidate_limit: 12
3ms| [MV TRACE] [PREPARE GLOBAL] cbo_materialized_view_rewrite_related_mvs_limit: 64
3ms| [MV TRACE] [PREPARE GLOBAL] materialized_view_rewrite_mode: DEFAULT
3ms| [MV TRACE] [PREPARE GLOBAL] ---------------------------------
3ms| [MV TRACE] [PREPARE GLOBAL] Table/MaterializedView iceberg_test2 has related materialized views: [MvId{dbId=10337, id=40338}]
3ms| [MV TRACE] [PREPARE GLOBAL] Choose 1/1 mvs after user config
3ms| [MV TRACE] [PREPARE GLOBAL] Choose 1/1 valid mvs after checking valid
3ms| [MV TRACE] [PREPARE test1view] MV refresh arbiter start to get partition names to refresh, query rewrite mode: CHECKED
4ms| [MV TRACE] [PREPARE test1view] MV refresh based on non-ref base table:false
29ms| [MV TRACE] [PREPARE test1view] MV' partitions to refresh: [p202410_202411]
30ms| [MV TRACE] [PREPARE test1view] MV compensate partition predicate: 1: prcdate >= 2024-11-01 AND 1: prcdate < 2024-12-01
34ms| [MV TRACE] [PREPARE test1view] Prepare MV test1view success
34ms| [MV TRACE] [PREPARE GLOBAL] RelatedMVs: [test1view], CandidateMVs: [test1view]
34ms| [MV TRACE] [PREPARE GLOBAL] MV rewrite strategy: MvRewriteStrategy{enableMaterializedViewRewrite=true, enableForceRBORewrite=true, enableViewBasedRewrite=false, enableSingleTableRewrite=true, enableMultiTableRewrite=false, mvStrategy=DEFAULT}
43ms| [MV TRACE] [REWRITE TF_MV_TEXT_MATCH_REWRITE_RULE] [InMemo:false] TEXT_BASED_REWRITE matched mvs: []
43ms| [MV TRACE] [REWRITE TF_MV_TEXT_MATCH_REWRITE_RULE] [InMemo:false] OptToAstMap is empty, no try to rewrite sub-query again
46ms| [MV TRACE] [REWRITE test1view] [InMemo:false] MV compensation:MvCompensation{state=NO_COMPENSATE, compensations=null}
49ms| [MV TRACE] [REWRITE test1view] Init mv compensation: MvCompensation{state=NO_COMPENSATE, compensations=null}
49ms| [MV TRACE] [REWRITE test1view] Query Compensate partition predicate:true
50ms| [MV TRACE] [REWRITE TF_MV_ONLY_SCAN_RULE test1view] [InMemo:false] Try to rewrite query with mv:test1view, match mode:COMPLETE
50ms| [MV TRACE] [REWRITE TF_MV_ONLY_SCAN_RULE test1view] [InMemo:false] MV predicate split:equalPredicates=, rangePredicates=, residualPredicates=
50ms| [MV TRACE] [REWRITE TF_MV_ONLY_SCAN_RULE test1view] [InMemo:false] Query predicate split:equalPredicates=, rangePredicates=, residualPredicates=
50ms| [MV TRACE] [REWRITE TF_MV_ONLY_SCAN_RULE test1view] [InMemo:false] Construct 1 relation id mappings from query to mv
50ms| [MV TRACE] [REWRITE TF_MV_ONLY_SCAN_RULE test1view] [InMemo:false] Get compensation predicates:equalPredicates=true, rangePredicates=true, residualPredicates=true, isTransparentRewrite: false
50ms| [MV TRACE] [REWRITE TF_MV_ONLY_SCAN_RULE test1view] [InMemo:false] Rewrite Succeed
52ms| Query has already been successfully rewritten by: test1view.
Tracer Cost: 18084us

Tried alter materialized view test1view set('mv_rewrite_staleness_second'='-1') does not help

[LiShuMing]

There maybe a bug with iceberg's partition transform in old version, let me check it.

CREATE TABLE iceberg_test2 (
prcdate date,
price double,
localcode string)
PARTITIONED BY (month(prcdate))
LOCATION 's3://bucket/directory/'
TBLPROPERTIES (
'table_type'='iceberg',
'format'='PARQUET',
'write_compression'='GZIP'
);

[LiShuMing]

Yes, it's a bug for mv's partition compensation with iceberg partition transform case. I will fix it.

[LiShuMing]

This should be fixed by PR(#55151), I will check it into v3.3 version.

There is bug to handle iceberg's partition transforms in old policy.