[GitHub] [doris] morningman opened a new issue, #13560: [Bug] wrong materialized view select cause query result incorrect.

[GitBox]

morningman opened a new issue, #13560:
URL: https://github.com/apache/doris/issues/13560

   ### Search before asking
   
   - [X] I had searched in the 
[issues](https://github.com/apache/incubator-doris/issues?q=is%3Aissue) and 
found no similar issues.
   
   
   ### Version
   
   master
   
   ### What's Wrong?
   
   result incorrect
   
   ### What You Expected?
   
   ```
   CREATE TABLE test_join_left_table (
     `dt` BIGINT NULL COMMENT "日期",
     `key1` BIGINT NULL COMMENT "key1",
     `key2` BIGINT NULL COMMENT "key2",
     `value1` INT NULL COMMENT "value1"
   ) ENGINE = OLAP
   DUPLICATE KEY(`dt`, `key1`, `key2`)
   COMMENT "测试join左表"
   PARTITION BY RANGE(`dt`)
   (
     PARTITION p20221022 VALUES  [("20221021"),("20221022"))
   )
   DISTRIBUTED BY HASH(`key1`) BUCKETS 3
   PROPERTIES (
   "storage_format" = "v2");
   
   
   insert into test_join_left_table
   values
   (20221021, 123, 1, 10),
   (20221021, 123, 2, 15),
   (20221021, 456, 1, 20); 
   
   CREATE TABLE test_join_right_table_non_mv (
     `dt` INT NULL COMMENT "dt",
     `key1` BIGINT NULL COMMENT "key1",
     `key3` BIGINT NULL COMMENT "key3",
     `value2` INT NULL COMMENT "value2"
   ) ENGINE = OLAP
   DUPLICATE KEY(`dt`, `key1`, `key3`)
   COMMENT "测试join右表无物化"
   PARTITION BY RANGE(`dt`)
   (
     PARTITION p20221022 VALUES  [("20221021"),("20221022"))
   )
   DISTRIBUTED BY HASH(`key3`) BUCKETS 3
   PROPERTIES (
   "storage_format" = "v2");
   
   
   insert into test_join_right_table_non_mv
   values
   (20221021, 123, 1, 5),
   (20221021, 123, 2, 6),
   (20221021, 123, 3, 10),
   (20221021, 456, 1, 8); 
   
   CREATE TABLE test_join_right_table_has_mv (
     `dt` INT NULL COMMENT "dt",
     `key1` BIGINT NULL COMMENT "key1",
     `key3` BIGINT NULL COMMENT "key3",
     `value2` INT NULL COMMENT "value2"
   ) ENGINE = OLAP
   DUPLICATE KEY(`dt`, `key1`, `key3`)
   COMMENT "测试join右表存在物化"
   PARTITION BY RANGE(`dt`)
   (
     PARTITION p20221022 VALUES  [("20221021"),("20221022"))
   )
   DISTRIBUTED BY HASH(`key3`) BUCKETS 3
   PROPERTIES (
   "storage_format" = "v2");
   
   
   insert into test_join_right_table_has_mv
   values
   (20221021, 123, 1, 5),
   (20221021, 123, 2, 6),
   (20221021, 123, 3, 10),
   (20221021, 456, 1, 8);
   
   
   create materialized view test_join_right_table_has_mv_dt_key1
   as select dt, key1, sum(value2)
   from test_join_right_table_has_mv
   group by dt, key1; 
   ```
   
   ```
   select t1.dt,
          t1.key1,
          t1.key2,
          sum(t1.value1) as value1,
          sum(t2.value2) as value2
     from test_join_left_table t1
     left join test_join_right_table_non_mv t2
       on t1.dt = t2.dt
      and t1.key1 = t2.key1
    group by t1.dt,
             t1.key1,
             t1.key2
    order by t1.dt,
             t1.key1,
             t1.key2;
   
   select t1.dt,
          t1.key1,
          t1.key2,
          sum(t1.value1) as value1,
          sum(t2.value2) as value2
     from test_join_left_table t1
     left join test_join_right_table_has_mv t2
       on t1.dt = t2.dt
      and t1.key1 = t2.key1
    group by t1.dt,
             t1.key1,
             t1.key2
    order by t1.dt,
             t1.key1,
             t1.key2; 
   ```
   
   ### How to Reproduce?
   
   _No response_
   
   ### Anything Else?
   
   _No response_
   
   ### Are you willing to submit PR?
   
   - [ ] Yes I am willing to submit a PR!
   
   ### Code of Conduct
   
   - [X] I agree to follow this project's [Code of 
Conduct](https://www.apache.org/foundation/policies/conduct)
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org