AggregatingMergeTree Materialized view could not query data sometimes

# Which ClickHouse server version to use
23.8.9.54
# Tables
## My_Table_One
```sql
-- local table
CREATE TABLE amazong_pacvue_test.My_Table_One on cluster cluster1
(

`vendorGroupId` Int64,

`asin` String,

`productTitle` Nullable(String),

`country_code` String,

`clientId` Int64,

`version` Int64
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{layer}-{shard}/{uuid}',
'{replica}',
version)
ORDER BY
(vendorGroupId,
asin,
country_code,
clientId)
SETTINGS index_granularity = 8192;

-- distribute table
CREATE OR REPLACE TABLE amazong_pacvue_test.My_Table_One_all ON CLUSTER cluster1 AS amazong_pacvue_test.My_Table_One ENGINE =
Distributed(cluster1,amazong_pacvue_test, My_Table_One,
murmurHash2_32(clientId) );
```

## My_Table_Two
```sql
-- local table
CREATE TABLE amazong_pacvue_test.My_Table_Two on cluster cluster1
(

`clientId` Int64,

`countryCode` String,

`asin` String,

`valueOfTypeOne` Nullable(String),
`valueOfTypeTwo` Nullable(String),
`valueOfTypeThree` Nullable(String),

`type` Int64,

`version` Int64
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{layer}-{shard}/{uuid}',
'{replica}',
version)
ORDER BY (clientId,
countryCode,
asin,
type)
SETTINGS index_granularity = 8192;

-- distribute table
CREATE OR REPLACE TABLE amazong_pacvue_test.My_Table_Two_all ON CLUSTER cluster1 AS amazong_pacvue_test.My_Table_Two ENGINE =
Distributed(cluster1,amazong_pacvue_test, My_Table_Two,
murmurHash2_32(clientId) );

```
# Datas
## My_Table_One
```sql
insert into My_Table_One_all
select
100 as vendorGroupId,
'asin1' as asin,
'test-product-title' as productTitle,
'US' as country_code,
1 as clientId,
toUnixTimestamp64Milli(toDateTime64(now(), 6 ) ) AS version
```
which means:
| vendorGroupId | asin | productTitle | country_code | clientId |
|------------------|------|--------------|-----------------|---------|
| 100 | asin1| test-product-title| US | 1 |

## My_Table_Two
```sql
insert into My_Table_Two_all values
(1, 'US', 'asin1', 'value of type one', null, null, 1, toUnixTimestamp64Milli(toDateTime64(now(), 6 ) )),
(1, 'US', 'asin1', null, 'value of type two', null, 2, toUnixTimestamp64Milli(toDateTime64(now(), 6 ) )),
(1, 'US', 'asin1', null, null, 'value of type three', 3, toUnixTimestamp64Milli(toDateTime64(now(), 6 ) ));
```
which means:
| clientId | countryCode | asin | valueOfTypeOne | valueOfTypeTwo | valueOfTypeThree | type |
|--------|----------------|-----|--------------------|--------------------|----------------------|-------|
| 1 | US | asin1 | value of type one | null | null | 1 |
| 1 | US | asin1 | null | value of type two| null | 2 |
| 1 | US | asin1 | null | null | value of type three| 3 |

# What do i want to do
I want to get data like sql:
```sql

select
one.clientId,
one.vendorGroupId,
one.country_code,
one.asin,
one.productTitle,
two.valueOfTypeOne,
two.valueOfTypeTwo,
two.valueOfTypeThree
from amazong_pacvue_test.My_Table_One one final
global left join (
select
clientId,
countryCode,
asin,
argMax(valueOfTypeOne, if(type == 1, version, null)) as valueOfTypeOne,
argMax(valueOfTypeTwo, if(type == 2, version, null)) as valueOfTypeTwo,
argMax(valueOfTypeThree, if(type == 3, version, null)) as valueOfTypeThree
from amazong_pacvue_test.My_Table_Two final
group by clientId, countryCode, asin
) two on one.clientId = two.clientId
and one.country_code = two.countryCode
and one.asin = two.asin

```
which got:
| clientId | vendorGroupId | country_code | asin | productTitle| valueOfTypeOne| valueOfTypeTwo | valueOfTypeThree |
| ---------|-----------------|-----------------|------|--------------|-------------------|-------------------|----------------------|
| 1 | 100 | US | asin1 | test-product-title | value of type one | value of type two | value of type three |

# How do i do
I create a aggregatingMergeTree table and two materialized view on it:
## AggregatingMergeTree Table
```sql
-- local table
create table amazong_pacvue_test.My_Agg_Table on cluster cluster1 (
`clientId` Int64,
`vendorGroupId` Int64,
`country_code` String,
`asin` String,

`productTitle` AggregateFunction(argMax, Nullable(String), Nullable(Int64)),
`valueOfTypeOne` AggregateFunction(argMax, Nullable(String), Nullable(Int64)),
`valueOfTypeTwo` AggregateFunction(argMax, Nullable(String), Nullable(Int64)),
`valueOfTypeThree` AggregateFunction(argMax, Nullable(String), Nullable(Int64))
) ENGINE = AggregatingMergeTree()
ORDER BY (clientId,
vendorGroupId,
country_code,
asin)
SETTINGS index_granularity = 8192;

-- distribute table
CREATE OR REPLACE TABLE amazong_pacvue_test.My_Agg_Table_all ON CLUSTER cluster1 AS amazong_pacvue_test.My_Agg_Table ENGINE =
Distributed(cluster1,amazong_pacvue_test, My_Agg_Table,
murmurHash2_32(clientId) );
```
## My_Table_One left join My_Table_Two materialized view
```sql
create MATERIALIZED VIEW amazong_pacvue_test.mat_view_my_table_one_left_join_my_table_two on
cluster cluster1
to amazong_pacvue_test.My_Agg_Table_all
as
select
catalog_info.clientId as clientId,
catalog_info.vendorGroupId as vendorGroupId,
catalog_info.country_code as country_code,
catalog_info.asin as asin,
catalog_info.productTitle as productTitle,
brand_category_info.valueOfTypeOne as valueOfTypeOne,
brand_category_info.valueOfTypeTwo as valueOfTypeTwo,
brand_category_info.valueOfTypeThree as valueOfTypeThree

from (
select
clientId,
vendorGroupId,
country_code,
asin,
argMaxState(productTitle, if(1 == 1, version, null)) as productTitle
from amazong_pacvue_test.My_Table_One
group by
clientId,
vendorGroupId ,
country_code,
asin
) catalog_info global
left join (
select
clientId,
countryCode,
asin,
argMaxState(valueOfTypeOne, if(type == 1, version, null)) as valueOfTypeOne,
argMaxState(valueOfTypeTwo, if(type == 2, version, null)) as valueOfTypeTwo,
argMaxState(valueOfTypeThree, if(type == 3, version, null)) as valueOfTypeThree
from amazong_pacvue_test.My_Table_Two_all
group by
clientId,
countryCode,
asin
) brand_category_info on
catalog_info.clientId = brand_category_info.clientId
and catalog_info.country_code = brand_category_info.countryCode
and catalog_info.asin = brand_category_info.asin

```

## My_Table_Two join My_Table_One materialized view
```sql
create MATERIALIZED VIEW amazong_pacvue_test.mat_view_my_table_two_inner_join_my_table_one on
cluster cluster1
to amazong_pacvue_test.My_Agg_Table_all
as
select
catalog_info.clientId as clientId,
catalog_info.vendorGroupId as vendorGroupId,
catalog_info.country_code as country_code,
catalog_info.asin as asin,
catalog_info.productTitle as productTitle,
brand_category_info.valueOfTypeOne as valueOfTypeOne,
brand_category_info.valueOfTypeTwo as valueOfTypeTwo,
brand_category_info.valueOfTypeThree as valueOfTypeThree

from (
select
clientId,
countryCode,
asin,
argMaxState(valueOfTypeOne, if(type == 1, version, null)) as valueOfTypeOne,
argMaxState(valueOfTypeTwo, if(type == 2, version, null)) as valueOfTypeTwo,
argMaxState(valueOfTypeThree, if(type == 3, version, null)) as valueOfTypeThree
from amazong_pacvue_test.My_Table_Two
group by
clientId,
countryCode,
asin
) brand_category_info global
inner join (
select
clientId,
vendorGroupId,
country_code,
asin,
argMaxState(productTitle, if(1 == 1, version, null)) as productTitle
from amazong_pacvue_test.My_Table_One_all
group by
clientId,
vendorGroupId ,
country_code,
asin
) catalog_info on
brand_category_info.clientId = catalog_info.clientId
and brand_category_info.countryCode = catalog_info.country_code
and brand_category_info.asin = catalog_info.asin
```
So that when i insert data to My_Table_One or My_Table_Two, I can get the latest data through materialized view
# Test
## Prepare data
```sql
insert into My_Table_One_all values
(100, 'asin2', 'xxx', 'US', 1, toUnixTimestamp64Milli(toDateTime64(now(), 6 ) )),
(100, 'asin3', 'xxx', 'US', 1, toUnixTimestamp64Milli(toDateTime64(now(), 6 ) ));



insert into My_Table_Two_all values
(1, 'US', 'asin2', 'value of type one', null, null, 1, toUnixTimestamp64Milli(toDateTime64(now(), 6 ) )),
(1, 'US', 'asin2', null, 'value of type two', null, 2, toUnixTimestamp64Milli(toDateTime64(now(), 6 ) ));

```
## Query Through Materialized View
```sql
select
clientId,
vendorGroupId,
country_code,
asin,
argMaxMerge(productTitle) as productTitle,
argMaxMerge(valueOfTypeOne) as valueOfTypeOne,
argMaxMerge(valueOfTypeTwo) as valueOfTypeTwo,
argMaxMerge(valueOfTypeThree) as valueOfTypeThree
from
amazong_pacvue_test.My_Agg_Table_all final
group by
clientId,
vendorGroupId,
country_code,
asin
```
I got the correct data which is :
| clientId|vendorGroupId|country_code|asin |productTitle|valueOfTypeOne |valueOfTypeTwo |valueOfTypeThree|
| -------- | -------------|------------|-----|------------|-----------------|-----------------|----------------|
| 1| 100|US |asin2|xxx |value of type one|value of type two| |
| 1| 100|US |asin3|xxx | | | |

# Problems
I execute this query again and again:
```sql
select
clientId,
vendorGroupId,
country_code,
asin,
argMaxMerge(productTitle) as productTitle,
argMaxMerge(valueOfTypeOne) as valueOfTypeOne,
argMaxMerge(valueOfTypeTwo) as valueOfTypeTwo,
argMaxMerge(valueOfTypeThree) as valueOfTypeThree
from
amazong_pacvue_test.My_Agg_Table_all final
group by
clientId,
vendorGroupId,
country_code,
asin
```
most times i got the correct data , but some times i could not get any data(no error , just a empty data set returns.)

I want to know if i using materialized view to fulfill my purpost correctly?(If not ,would you please tell me the best solution?)
And why I can not get data through `My_Agg_Table_all ` some times?
PS: Both My_Table_One and My_Table_Two will have lot`s of datas.(Let`s say 10,000,000)