Skip to content

Query with Count distinct matches the incorrect materialized view  #4381

@EmmyMiao87

Description

@EmmyMiao87

Describe the bug
The count distinct in query matches the incorrect materialized view which only has count() function.
It causes the query result is incorrect.

To Reproduce
Steps to reproduce the behavior:

  1. create base table
CREATE TABLE test_shoot_tb_dup ( k1 tinyint NULL, k2 smallint NULL, k3 int NULL, k4 bigint NULL, k5 decimal(9, 3) NULL, k6 char(5) NULL, k10 date NULL, k11 datetime NULL, k7 varchar(20) NULL, k8 double NULL, k9 float NULL )  DISTRIBUTED BY HASH(k1) BUCKETS 5
  1. create materialized view
CREATE MATERIALIZED VIEW mv6 AS select k2, k1, k3, k4, count(k6), count(k7) from test_shoot_tb_dup group by k2, k1, k3, k4
  1. load data

  2. query

select count(distinct k6) from test_shoot_tb_dup ;

query matches the mv

MySQL [test]> explain select count(distinct k6) from test_shoot_tb_dup ;
+------------------------------------------------------------------------------------------+
| Explain String                                                                           |
+------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0                                                                          |
|  OUTPUT EXPRS:<slot 2> sum(`default_cluster:test`.`test_shoot_tb_dup`.`mv_count_k6`)     |
|   PARTITION: UNPARTITIONED                                                               |
|                                                                                          |
|   RESULT SINK                                                                            |
|                                                                                          |
|   3:AGGREGATE (merge finalize)                                                           |
|   |  output: sum(<slot 2> sum(`default_cluster:test`.`test_shoot_tb_dup`.`mv_count_k6`)) |
|   |  group by:                                                                           |
|   |                                                                                      |
|   2:EXCHANGE                                                                             |
|                                                                                          |
| PLAN FRAGMENT 1                                                                          |
|  OUTPUT EXPRS:                                                                           |
|   PARTITION: RANDOM                                                                      |
|                                                                                          |
|   STREAM DATA SINK                                                                       |
|     EXCHANGE ID: 02                                                                      |
|     UNPARTITIONED                                                                        |
|                                                                                          |
|   1:AGGREGATE (update serialize)                                                         |
|   |  output: sum(`default_cluster:test`.`test_shoot_tb_dup`.`mv_count_k6`)               |
|   |  group by:                                                                           |
|   |                                                                                      |
|   0:OlapScanNode                                                                         |
|      TABLE: test_shoot_tb_dup                                                            |
|      PREAGGREGATION: ON                                                                  |
|      partitions=1/1                                                                      |
|      rollup: mv6                                                                         |
|      tabletRatio=5/5                                                                     |
|      tabletList=33048,33050,33052,33054,33056                                            |
|      cardinality=0                                                                       |
|      avgRowSize=0.0                                                                      |
|      numNodes=1                                                                          |
+------------------------------------------------------------------------------------------+
34 rows in set (0.009 sec)

Expected behavior

Query matches the base table and return correct result .

Metadata

Metadata

Assignees

No one assigned

    Labels

    area/materialized-viewIssues or PRs related to materialized viewkind/fixCategorizes issue or PR as related to a bug.

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions