qidaye opened a new issue #6392: URL: https://github.com/apache/incubator-doris/issues/6392
Current Doris can not fold constant in `InlineView` by BE.
1. Create a table
```sql
CREATE TABLE `t1` (
`dayDate` datetime NOT NULL COMMENT "",
`stationName` varchar(200) NULL COMMENT "",
`preSalesComplaints` int(11) NULL COMMENT "",
`productComplaints` int(11) NULL COMMENT "",
`serviceComplaints` int(11) NULL COMMENT "",
`partsComplaints` int(11) NULL COMMENT "",
`createdOn` datetime NULL COMMENT ""
) ENGINE=OLAP
UNIQUE KEY(`dayDate`, `stationName`)
DISTRIBUTED BY HASH(`dayDate`) BUCKETS 10
PROPERTIES (
"replication_num" = "1",
"in_memory" = "false",
"storage_format" = "V2"
);
```
2. Sql execution
```sql
SELECT
IFNULL(a.complaints,0) t_complaints, IFNULL(b.complaints,0)
l_complaints,
IFNULL(round((IFNULL(a.complaints,0)-IFNULL(b.complaints,0))*100/IFNULL(b.complaints,0),3),0)
tb
FROM
(
SELECT
SUM(IFNULL(partsComplaints,0)) + SUM(IFNULL(productComplaints,0)) +
SUM(IFNULL(serviceComplaints,0)) complaints
FROM
t1
WHERE
(stationName like '%山东%')
AND dayDate >= DATE_SUB( DATE_FORMAT( now(), '%Y-%m-%d 00:00:00' ),
INTERVAL IF(DAYOFWEEK(now()) = 1, 7, DAYOFWEEK(now()) - 1)-1 DAY )
)a,
(
SELECT
SUM(IFNULL(partsComplaints,0)) + SUM(IFNULL(productComplaints,0)) +
SUM(IFNULL(serviceComplaints,0)) complaints
FROM
t1
WHERE
(stationName like '%山东%')
AND dayDate >= DATE_SUB( DATE_FORMAT( now(), '%Y-%m-%d 00:00:00' ),
INTERVAL IF(DAYOFWEEK(now()) = 1, 7, DAYOFWEEK(now()) - 1)+6 DAY )
AND dayDate < DATE_SUB( DATE_FORMAT( now(), '%Y-%m-%d 00:00:00' ),
INTERVAL 7 DAY )
)b
limit 0, 5000;
```
3. Set fold constant by be
```sql
set enable_fold_constant_by_be=true;
```
4. Explain
```sql
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0
|
| OUTPUT EXPRS:ifnull(<slot 5> sum(ifnull(`partsComplaints`, 0)) + <slot 6>
sum(ifnull(`productComplaints`, 0)) + <slot 7> sum(ifnull(`serviceComplaints`,
0)), 0) | ifnull(<slot 14> sum(ifnull(`partsComplaints`, 0)) + <slot 15>
sum(ifnull(`productComplaints`, 0)) + <slot 16> sum(ifnull(`serviceComplaints`,
0)), 0) | ifnull(round((ifnull(<slot 5> sum(ifnull(`partsComplaints`, 0)) +
<slot 6> sum(ifnull(`productComplaints`, 0)) + <slot 7>
sum(ifnull(`serviceComplaints`, 0)), 0) - ifnull(<slot 14>
sum(ifnull(`partsComplaints`, 0)) + <slot 15> sum(ifnull(`productComplaints`,
0)) + <slot 16> sum(ifnull(`serviceComplaints`, 0)), 0)) * 100 / ifnull(<slot
14> sum(ifnull(`partsComplaints`, 0)) + <slot 15>
sum(ifnull(`productComplaints`, 0)) + <slot 16> sum(ifnull(`serviceComplaints`,
0)), 0), 3), 0.0) |
| PARTITION: UNPARTITIONED
|
|
|
| RESULT SINK
|
|
|
| 4:CROSS JOIN
|
| | cross join:
|
| | predicates is NULL. | cardinality=1
|
| | limit: 5000
|
| |
|
| |----9:EXCHANGE
|
| |
|
| 6:AGGREGATE (merge finalize)
|
| | output: sum(<slot 5> sum(ifnull(`partsComplaints`, 0))), sum(<slot 6>
sum(ifnull(`productComplaints`, 0))), sum(<slot 7>
sum(ifnull(`serviceComplaints`, 0)))
|
| | group by:
|
| | cardinality=-1
|
| |
|
| 5:EXCHANGE
|
|
|
| PLAN FRAGMENT 1
|
| OUTPUT EXPRS:
|
| PARTITION: UNPARTITIONED
|
|
|
| STREAM DATA SINK
|
| EXCHANGE ID: 09
|
| UNPARTITIONED
|
|
|
| 8:AGGREGATE (merge finalize)
|
| | output: sum(<slot 14> sum(ifnull(`partsComplaints`, 0))), sum(<slot
15> sum(ifnull(`productComplaints`, 0))), sum(<slot 16>
sum(ifnull(`serviceComplaints`, 0)))
|
| | group by:
|
| | cardinality=-1
|
| |
|
| 7:EXCHANGE
|
|
|
| PLAN FRAGMENT 2
|
| OUTPUT EXPRS:
|
| PARTITION: HASH_PARTITIONED: `default_cluster:example_db`.`t1`.`dayDate`
|
|
|
| STREAM DATA SINK
|
| EXCHANGE ID: 07
|
| UNPARTITIONED
|
|
|
| 3:AGGREGATE (update serialize)
|
| | output: sum(ifnull(`partsComplaints`, 0)),
sum(ifnull(`productComplaints`, 0)), sum(ifnull(`serviceComplaints`, 0))
|
| | group by:
|
| | cardinality=1
|
| |
|
| 2:OlapScanNode
|
| TABLE: t1
|
| PREAGGREGATION: OFF. Reason:
aggExpr.getChild(0)[(FunctionCallExpr{name=ifnull, isStar=false,
isDistinct=false, (SlotRef{slotDesc=SlotDescriptor{id=9, parent=3,
col=partsComplaints, type=INT, materialized=true, byteSize=0, byteOffset=-1,
nullIndicatorByte=0, nullIndicatorBit=0, slotIdx=0}, col=partsComplaints,
label=`partsComplaints`, tblName=null} )})] is not SlotRef or CastExpr|CaseExpr
|
| PREDICATES: (`stationName` LIKE '%山东%'), `dayDate` >=
date_sub(date_format(now(), '%Y-%m-%d 00:00:00'), INTERVAL if(dayofweek(now())
= 1, 7, dayofweek(now()) - 1) + 6 DAY), `dayDate` < date_sub(date_format(now(),
'%Y-%m-%d 00:00:00'), INTERVAL 7 DAY)
|
| partitions=1/1
|
| rollup: t1
|
| tabletRatio=10/10
|
|
tabletList=12009,12011,12013,12015,12017,12019,12021,12023,12025,12027
|
| cardinality=5
|
| avgRowSize=1149.4
|
| numNodes=1
|
|
|
| PLAN FRAGMENT 3
|
| OUTPUT EXPRS:
|
| PARTITION: HASH_PARTITIONED: `default_cluster:example_db`.`t1`.`dayDate`
|
|
|
| STREAM DATA SINK
|
| EXCHANGE ID: 05
|
| UNPARTITIONED
|
|
|
| 1:AGGREGATE (update serialize)
|
| | output: sum(ifnull(`partsComplaints`, 0)),
sum(ifnull(`productComplaints`, 0)), sum(ifnull(`serviceComplaints`, 0))
|
| | group by:
|
| | cardinality=1
|
| |
|
| 0:OlapScanNode
|
| TABLE: t1
|
| PREAGGREGATION: OFF. Reason:
aggExpr.getChild(0)[(FunctionCallExpr{name=ifnull, isStar=false,
isDistinct=false, (SlotRef{slotDesc=SlotDescriptor{id=0, parent=0,
col=partsComplaints, type=INT, materialized=true, byteSize=0, byteOffset=-1,
nullIndicatorByte=0, nullIndicatorBit=0, slotIdx=0}, col=partsComplaints,
label=`partsComplaints`, tblName=null} )})] is not SlotRef or CastExpr|CaseExpr
|
| PREDICATES: (`stationName` LIKE '%山东%'), `dayDate` >=
date_sub(date_format(now(), '%Y-%m-%d 00:00:00'), INTERVAL if(dayofweek(now())
= 1, 7, dayofweek(now()) - 1) - 1 DAY)
|
| partitions=1/1
|
| rollup: t1
|
| tabletRatio=10/10
|
|
tabletList=12009,12011,12013,12015,12017,12019,12021,12023,12025,12027
|
| cardinality=5
|
| avgRowSize=1149.4
|
| numNodes=1
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
```
As you can see in the explain string, the functionCallExpr `date_sub` in
`PREDICATES` is not folded.
When it is folded, the explain string looks like below:
```sql
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0
|
| OUTPUT EXPRS:ifnull(<slot 5> sum(ifnull(`partsComplaints`, 0)) + <slot 6>
sum(ifnull(`productComplaints`, 0)) + <slot 7> sum(ifnull(`serviceComplaints`,
0)), 0) | ifnull(<slot 14> sum(ifnull(`partsComplaints`, 0)) + <slot 15>
sum(ifnull(`productComplaints`, 0)) + <slot 16> sum(ifnull(`serviceComplaints`,
0)), 0) | ifnull(round((ifnull(<slot 5> sum(ifnull(`partsComplaints`, 0)) +
<slot 6> sum(ifnull(`productComplaints`, 0)) + <slot 7>
sum(ifnull(`serviceComplaints`, 0)), 0) - ifnull(<slot 14>
sum(ifnull(`partsComplaints`, 0)) + <slot 15> sum(ifnull(`productComplaints`,
0)) + <slot 16> sum(ifnull(`serviceComplaints`, 0)), 0)) * 100 / ifnull(<slot
14> sum(ifnull(`partsComplaints`, 0)) + <slot 15>
sum(ifnull(`productComplaints`, 0)) + <slot 16> sum(ifnull(`serviceComplaints`,
0)), 0), 3), 0.0) |
| PARTITION: UNPARTITIONED
|
|
|
| RESULT SINK
|
|
|
| 4:CROSS JOIN
|
| | cross join:
|
| | predicates is NULL. | cardinality=1
|
| | limit: 5000
|
| |
|
| |----9:EXCHANGE
|
| |
|
| 6:AGGREGATE (merge finalize)
|
| | output: sum(<slot 5> sum(ifnull(`partsComplaints`, 0))), sum(<slot 6>
sum(ifnull(`productComplaints`, 0))), sum(<slot 7>
sum(ifnull(`serviceComplaints`, 0)))
|
| | group by:
|
| | cardinality=-1
|
| |
|
| 5:EXCHANGE
|
|
|
| PLAN FRAGMENT 1
|
| OUTPUT EXPRS:
|
| PARTITION: UNPARTITIONED
|
|
|
| STREAM DATA SINK
|
| EXCHANGE ID: 09
|
| UNPARTITIONED
|
|
|
| 8:AGGREGATE (merge finalize)
|
| | output: sum(<slot 14> sum(ifnull(`partsComplaints`, 0))), sum(<slot
15> sum(ifnull(`productComplaints`, 0))), sum(<slot 16>
sum(ifnull(`serviceComplaints`, 0)))
|
| | group by:
|
| | cardinality=-1
|
| |
|
| 7:EXCHANGE
|
|
|
| PLAN FRAGMENT 2
|
| OUTPUT EXPRS:
|
| PARTITION: HASH_PARTITIONED: `default_cluster:example_db`.`t1`.`dayDate`
|
|
|
| STREAM DATA SINK
|
| EXCHANGE ID: 07
|
| UNPARTITIONED
|
|
|
| 3:AGGREGATE (update serialize)
|
| | output: sum(ifnull(`partsComplaints`, 0)),
sum(ifnull(`productComplaints`, 0)), sum(ifnull(`serviceComplaints`, 0))
|
| | group by:
|
| | cardinality=1
|
| |
|
| 2:OlapScanNode
|
| TABLE: t1
|
| PREAGGREGATION: OFF. Reason:
aggExpr.getChild(0)[(FunctionCallExpr{name=ifnull, isStar=false,
isDistinct=false, (SlotRef{slotDesc=SlotDescriptor{id=9, parent=3,
col=partsComplaints, type=INT, materialized=true, byteSize=0, byteOffset=-1,
nullIndicatorByte=0, nullIndicatorBit=0, slotIdx=0}, col=partsComplaints,
label=`partsComplaints`, tblName=null} )})] is not SlotRef or CastExpr|CaseExpr
|
| PREDICATES: (`stationName` LIKE '%山东%'), `dayDate` >= '2021-07-26
00:00:00', `dayDate` < '2021-07-30 00:00:00'
|
| partitions=1/1
|
| rollup: t1
|
| tabletRatio=10/10
|
|
tabletList=12009,12011,12013,12015,12017,12019,12021,12023,12025,12027
|
| cardinality=5
|
| avgRowSize=1149.4
|
| numNodes=1
|
|
|
| PLAN FRAGMENT 3
|
| OUTPUT EXPRS:
|
| PARTITION: HASH_PARTITIONED: `default_cluster:example_db`.`t1`.`dayDate`
|
|
|
| STREAM DATA SINK
|
| EXCHANGE ID: 05
|
| UNPARTITIONED
|
|
|
| 1:AGGREGATE (update serialize)
|
| | output: sum(ifnull(`partsComplaints`, 0)),
sum(ifnull(`productComplaints`, 0)), sum(ifnull(`serviceComplaints`, 0))
|
| | group by:
|
| | cardinality=1
|
| |
|
| 0:OlapScanNode
|
| TABLE: t1
|
| PREAGGREGATION: OFF. Reason:
aggExpr.getChild(0)[(FunctionCallExpr{name=ifnull, isStar=false,
isDistinct=false, (SlotRef{slotDesc=SlotDescriptor{id=0, parent=0,
col=partsComplaints, type=INT, materialized=true, byteSize=0, byteOffset=-1,
nullIndicatorByte=0, nullIndicatorBit=0, slotIdx=0}, col=partsComplaints,
label=`partsComplaints`, tblName=null} )})] is not SlotRef or CastExpr|CaseExpr
|
| PREDICATES: (`stationName` LIKE '%山东%'), `dayDate` >= '2021-08-02
00:00:00'
|
| partitions=1/1
|
| rollup: t1
|
| tabletRatio=10/10
|
|
tabletList=12009,12011,12013,12015,12017,12019,12021,12023,12025,12027
|
| cardinality=5
|
| avgRowSize=1149.4
|
| numNodes=1
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
```
--
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
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
