EmmyMiao87 opened a new issue #3174: The error rollup has been selected when there is having clause in query URL: https://github.com/apache/incubator-doris/issues/3174 **Describe the bug** If the aggregation function only appear in having clause such as following query, the mv selector will select a error rollup which only considers the columns that appear in the output. ··· Rollup only has column k1 will be selected in following query. select k1 from table group by k1 having sum(v1); ··· **To Reproduce** ``` CREATE TABLE `test_tb` ( `k1` int(11) NOT NULL COMMENT "", `k2` int(11) NOT NULL COMMENT "", `v1` varchar(4096) NOT NULL COMMENT "", `v2` float NOT NULL COMMENT "", `v3` decimal(20, 7) NOT NULL COMMENT "" ) ENGINE=OLAP DUPLICATE KEY(`k1`, `k2`) COMMENT "OLAP" DISTRIBUTED BY HASH(`k1`, `k2`) BUCKETS 10 PROPERTIES ( "storage_type" = "COLUMN", "replication_num" = "3", "in_memory" = "false" ); ``` create materialized view ``` create materialized view k1_k2 as select k1,k2 from test_tb group by k1,k2;\ ``` load data ``` LOAD LABEL test_db.label_19_17_28_06_736378_503886299 (DATA INFILE("hdfs://szwg-palo-be00.szwg01.baidu.com:54310/user/palo/test/data/sys/schema_change/a_raw") INTO TABLE `test_tb`) WITH BROKER "hdfs" ("username"="root", "password"="3trqDWfl"); ``` explain query ``` explain select k1 from test_tb group by k1 having max(v1) > 10; +-----------------------------------------------------------------------------+ | Explain String | +-----------------------------------------------------------------------------+ | PLAN FRAGMENT 0 | | OUTPUT EXPRS:<slot 2> | | PARTITION: UNPARTITIONED | | | | RESULT SINK | | | | 4:EXCHANGE | | tuple ids: 1 | | | | PLAN FRAGMENT 1 | | OUTPUT EXPRS: | | PARTITION: HASH_PARTITIONED: <slot 2> | | | | STREAM DATA SINK | | EXCHANGE ID: 04 | | UNPARTITIONED | | | | 3:AGGREGATE (merge finalize) | | | output: max(<slot 3>) | | | group by: <slot 2> | | | having: <slot 3> > 10.0 | | | tuple ids: 1 | | | | | 2:EXCHANGE | | tuple ids: 1 | | | | PLAN FRAGMENT 2 | | OUTPUT EXPRS: | | PARTITION: RANDOM | | | | STREAM DATA SINK | | EXCHANGE ID: 02 | | HASH_PARTITIONED: <slot 2> | | | | 1:AGGREGATE (update serialize) | | | STREAMING | | | output: max(`v1`) | | | group by: `k1` | | | tuple ids: 1 | | | | | 0:OlapScanNode | | TABLE: test_tb | | PREAGGREGATION: ON | | partitions=1/1 | | rollup: k1_k2 | | tabletRatio=10/10 | | tabletList=14006,14008,14010,14012,14014,14016,14018,14020,14022,14024 | | cardinality=1 | | avgRowSize=675.0 | | numNodes=1 | | tuple ids: 0 | +-----------------------------------------------------------------------------+ 51 rows in set (0.01 sec) ``` The rollup k1_k2 has been selected while column v1 is not in the k1_k2. **Expected behavior** The columns in having should be consider in mv selector. For the above query, only base table could be selected. ``` mysql> desc test_tb all; +-----------+-------+---------------+------+-------+---------+-------+ | IndexName | Field | Type | Null | Key | Default | Extra | +-----------+-------+---------------+------+-------+---------+-------+ | test_tb | k1 | INT | No | true | N/A | | | | k2 | INT | No | true | N/A | | | | v1 | VARCHAR(4096) | No | false | N/A | NONE | | | v2 | FLOAT | No | false | N/A | NONE | | | v3 | DECIMAL(20,7) | No | false | N/A | NONE | | | | | | | | | | k1_k2 | k1 | INT | No | true | N/A | | | | k2 | INT | No | true | N/A | | +-----------+-------+---------------+------+-------+---------+-------+ ```
---------------------------------------------------------------- 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. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
