[
https://issues.apache.org/jira/browse/CALCITE-4910?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17451070#comment-17451070
]
Zoltan Haindrich commented on CALCITE-4910:
-------------------------------------------
[~Ziwei Liu]: if you use simple equals operations and such; the simplification
happens:
{code}
explain plan for select * from emps where (empno = 0 or deptno = 1) and (
empno=2 or deptno=2) and deptno=2 and empno=0;
{"resultset":[
{"PLAN":"EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t2):INTEGER],
expr#4=[2], expr#5=[=($t3, $t4)], expr#6=[CAST($t0):INTEGER], expr#7=[0],
expr#8=[=($t6, $t7)], expr#9=[AND($t5, $t8)], proj#0..2=[{exprs}],
$condition=[$t9])\n EnumerableTableScan(table=[[SALES, EMPS]])\n"}
]}
{code}
I was thinking that this will not be an issue with simplification - but it
seems like it is; if I use similar IS NULL checks as in the above query:
{code}
explain plan for select * from emps where (replace(name,'a','b') is not null or
deptno = 1) and ( empno=2 or replace(name,'b','x') is not null) and
replace(name,'a','b') is not null and replace(name,'b','x') is not null ;
{"resultset":[
{"PLAN":"EnumerableCalc(expr#0..2=[{inputs}], expr#3=['a'], expr#4=['b'],
expr#5=[REPLACE($t1, $t3, $t4)], expr#6=[IS NOT NULL($t5)],
expr#7=[CAST($t2):INTEGER], expr#8=[1], expr#9=[=($t7, $t8)], expr#10=[OR($t6,
$t9)], expr#11=[CAST($t0):INTEGER], expr#12=[2], expr#13=[=($t11, $t12)],
expr#14=['x'], expr#15=[REPLACE($t1, $t4, $t14)], expr#16=[IS NOT NULL($t15)],
expr#17=[OR($t13, $t16)], expr#18=[AND($t10, $t17, $t6, $t16)],
proj#0..2=[{exprs}], $condition=[$t18])\n EnumerableTableScan(table=[[SALES,
EMPS]])\n"}
]}
{code}
I think this might be connected to some of the following:
* REPLACE is registered as a OTHER_FUNCTION
* the issue seems to be around `IS NULL` handling of predicates
* oddly it seems at first glance that simplifyUsingPredicates is not invoked
for non-comparisions...do we really miss that?
* I think a good point to start debugging this issue is around
RexSimplify#simplifyUsingPredicates
I have to jump into something else now
> Enhance simplify to reduce ((a or d) and (a or c) and a and b) to (a and b)
> ---------------------------------------------------------------------------
>
> Key: CALCITE-4910
> URL: https://issues.apache.org/jira/browse/CALCITE-4910
> Project: Calcite
> Issue Type: Improvement
> Reporter: Ziwei Liu
> Assignee: Ziwei Liu
> Priority: Major
>
> Considering this condition, (a or d) and (a or c) and a and b. This condition
> can be simplified to a and b, but now simplify can not do this.
> This case is found out by materialized view test:
> {code}
> materialized view: select 'a',\"empid\",\"deptno\",\"salary\" as s from
> \"emps\" where (replace(\"name\",'\\2','a') is not null or
> replace(\"name\",'a[[:alpha:]]{5}','c') is not null) and
> (replace(\"name\",'[[:blank:]][[:alnum:]]*','b') is not null or
> replace(\"name\",'(.*)(.)$','d') is not null)
> query: select \"salary\" +1 as s,\"deptno\" from \"emps\" where
> replace(\"name\",'[[:blank:]][[:alnum:]]*','b' ) is not null and
> replace(\"name\",'a[[:alpha:]]{5}','c') is not null and \"salary\">10
> {code}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
