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 2020/03/23 09:38:42 UTC
[GitHub] [incubator-doris] EmmyMiao87 opened a new issue #3174: The error
rollup has been selected when there is having clause in query
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:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] EmmyMiao87 closed issue #3174: The error rollup
has been selected when there is having clause in query
Posted by GitBox <gi...@apache.org>.
EmmyMiao87 closed issue #3174: The error rollup has been selected when there is having clause in query
URL: https://github.com/apache/incubator-doris/issues/3174
----------------------------------------------------------------
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:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org