You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by GitBox <gi...@apache.org> on 2022/10/21 12:08:51 UTC
[GitHub] [doris] morningman opened a new issue, #13560: [Bug] wrong materialized view select cause query result incorrect.
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-unsubscribe@doris.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] wangshuo128 commented on issue #13560: [Bug] wrong materialized view select cause query result incorrect.
Posted by GitBox <gi...@apache.org>.
wangshuo128 commented on issue #13560:
URL: https://github.com/apache/doris/issues/13560#issuecomment-1296846019
> The following query will hit the rollup and cause wrong result
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;
Hi, @morningman, I tested the SQL with the latest master code. The query wouldn't use materialized view index, please see the test code: https://github.com/wangshuo128/doris/blob/doris-13560/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/mv/MvBugTest.java.
Did I miss anything?
--
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-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org