xinghuayu007 opened a new issue #4273:
URL: https://github.com/apache/incubator-doris/issues/4273
**Describe the bug**
When a sql joins two tables and contains a 'where' clause and the clause
contains an unstable function like rand()，the current sql planner predicate
pushdown the unstable function. That is not corrent. It should not be
predicated pushdown.
**To Reproduce**
Steps to reproduce the behavior:
1. create table1, table2:
CREATE TABLE `table1` (
`siteid` int(11) NULL DEFAULT "10" COMMENT "",
`citycode` smallint(6) NULL COMMENT "",
`username` varchar(32) NULL DEFAULT "" COMMENT "",
`pv` bigint(20) SUM NULL DEFAULT "0" COMMENT ""
) ENGINE=OLAP
AGGREGATE KEY(`siteid`, `citycode`, `username`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`siteid`) BUCKETS 10
PROPERTIES (
"replication_num" = "1",
"in_memory" = "false",
"storage_format" = "DEFAULT"
);
CREATE TABLE `table2` (
`siteid` int(11) NULL DEFAULT "10" COMMENT "",
`citycode` smallint(6) NULL COMMENT "",
`adate` int(11) NULL DEFAULT "0" COMMENT "",
`pv` bigint(20) SUM NULL DEFAULT "0" COMMENT ""
) ENGINE=OLAP
AGGREGATE KEY(`siteid`, `citycode`, `adate`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`siteid`) BUCKETS 10
PROPERTIES (
"replication_num" = "1",
"in_memory" = "false",
"storage_format" = "DEFAULT"
);
2. Explain a sql:
explain select a.siteid, b.citycode from table2 as a join table1 as b on
a.siteid = b.siteid where a.citycode=123 and rand(b.siteid) = 2;
3. The result is:
4. The condition 'rand(b.siteid) = 2' is predicated pushdown.
**Expected behavior**
The condition 'rand(b.siteid) = 2' should be filted after the two tables
joining.
**Desktop (please complete the following information):**
- OS: [e.g. iOS]
- Browser [e.g. chrome, safari]
- Version [e.g. 22]
**Smartphone (please complete the following information):**
- Device: [e.g. iPhone6]
- OS: [e.g. iOS8.1]
- Browser [e.g. stock browser, safari]
- Version [e.g. 22]
**Additional context**
Add any other context about the problem here.
----------------------------------------------------------------
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:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
