You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "xiong duan (Jira)" <ji...@apache.org> on 2022/01/22 06:43:00 UTC
[jira] [Updated] (CALCITE-4993) Simplify EQUALS or NOT-EQUALS with other number comparison
[ https://issues.apache.org/jira/browse/CALCITE-4993?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
xiong duan updated CALCITE-4993:
--------------------------------
Description:
For example(SELECT expression FROM table), The Plan is EnumerableCalc description:
1:
{code:java}
"cust_id"<>5 and "cust_id">3 and "cust_id"< 10{code}
Before:
{noformat}
expr#0..1=[{inputs}], expr#2=[CAST($t0):INTEGER NOT NULL], expr#3=[5], expr#4=[<>($t2, $t3)], expr#5=[3], expr#6=[>($t0, $t5)], expr#7=[10], expr#8=[<($t0, $t7)], expr#9=[AND($t4, $t6, $t8)], EXPR$0=[$t9]{noformat}
After:
{noformat}
(expr#0..1=[{inputs}], expr#2=[Sarg[(3..5), (5..10)]], expr#3=[SEARCH($t0, $t2)], EXPR$0=[$t3]){noformat}
2:
{code:java}
"cust_id"=5 and "cust_id">3 and "cust_id"< 10{code}
Before:
{noformat}
expr#0..1=[{inputs}], expr#2=[CAST($t0):INTEGER NOT NULL], expr#3=[5], expr#4=[=($t2, $t3)], expr#5=[3], expr#6=[>($t0, $t5)], expr#7=[10], expr#8=[<($t0, $t7)], expr#9=[AND($t4, $t6, $t8)], EXPR$0=[$t9]{noformat}
After:
{noformat}
expr#0..1=[{inputs}], expr#2=[5], expr#3=[=($t0, $t2)], EXPR$0=[$t3]{noformat}
3:
{code:java}
"cust_id"=5 and "cust_id">6 and "cust_id"< 10{code}
Before:
{noformat}
expr#0..1=[{inputs}], expr#2=[CAST($t0):INTEGER NOT NULL], expr#3=[5], expr#4=[=($t2, $t3)], expr#5=[6], expr#6=[>($t0, $t5)], expr#7=[10], expr#8=[<($t0, $t7)], expr#9=[AND($t4, $t6, $t8)], EXPR$0=[$t9]{noformat}
After:
{noformat}
expr#0..1=[{inputs}], expr#2=[false], EXPR$0=[$t2]{noformat}
was:
For example(SELECT expression FROM table), The Plan is EnumerableCalc description:
1:
{code:java}
"cust_id"<>5 and "cust_id">3 and "cust_id"< 10{code}
Before:
{noformat}
expr#0..1=[{inputs}], expr#2=[CAST($t0):INTEGER NOT NULL], expr#3=[5], expr#4=[<>($t2, $t3)], expr#5=[3], expr#6=[>($t0, $t5)], expr#7=[10], expr#8=[<($t0, $t7)], expr#9=[AND($t4, $t6, $t8)], EXPR$0=[$t9]{noformat}
After:
{noformat}
(expr#0..1=[{inputs}], expr#2=[Sarg[(3..5), (5..10)]], expr#3=[SEARCH($t0, $t2)], EXPR$0=[$t3]){noformat}
2:
{code:java}
"cust_id"=5 and "cust_id">3 and "cust_id"< 10{code}
Before:
{noformat}
expr#0..1=[{inputs}], expr#2=[CAST($t0):INTEGER NOT NULL], expr#3=[5], expr#4=[=($t2, $t3)], expr#5=[3], expr#6=[>($t0, $t5)], expr#7=[10], expr#8=[<($t0, $t7)], expr#9=[AND($t4, $t6, $t8)], EXPR$0=[$t9]{noformat}
After:
{noformat}
expr#0..1=[{inputs}], expr#2=[5], expr#3=[=($t0, $t2)], EXPR$0=[$t3]{noformat}
3:
{code:java}
"cust_id"=5 and "cust_id">6 and "cust_id"< 10{code}
Before:
{noformat}
expr#0..1=[{inputs}], expr#2=[CAST($t0):INTEGER NOT NULL], expr#3=[5], expr#4=[=($t2, $t3)], expr#5=[6], expr#6=[>($t0, $t5)], expr#7=[10], expr#8=[<($t0, $t7)], expr#9=[AND($t4, $t6, $t8)], EXPR$0=[$t9]{noformat}
After:
{noformat}
expr#0..1=[{inputs}], expr#2=[false], EXPR$0=[$t2]{noformat}
> Simplify EQUALS or NOT-EQUALS with other number comparison
> ----------------------------------------------------------
>
> Key: CALCITE-4993
> URL: https://issues.apache.org/jira/browse/CALCITE-4993
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.29.0
> Reporter: xiong duan
> Priority: Major
>
> For example(SELECT expression FROM table), The Plan is EnumerableCalc description:
> 1:
> {code:java}
> "cust_id"<>5 and "cust_id">3 and "cust_id"< 10{code}
> Before:
> {noformat}
> expr#0..1=[{inputs}], expr#2=[CAST($t0):INTEGER NOT NULL], expr#3=[5], expr#4=[<>($t2, $t3)], expr#5=[3], expr#6=[>($t0, $t5)], expr#7=[10], expr#8=[<($t0, $t7)], expr#9=[AND($t4, $t6, $t8)], EXPR$0=[$t9]{noformat}
> After:
> {noformat}
> (expr#0..1=[{inputs}], expr#2=[Sarg[(3..5), (5..10)]], expr#3=[SEARCH($t0, $t2)], EXPR$0=[$t3]){noformat}
> 2:
> {code:java}
> "cust_id"=5 and "cust_id">3 and "cust_id"< 10{code}
> Before:
> {noformat}
> expr#0..1=[{inputs}], expr#2=[CAST($t0):INTEGER NOT NULL], expr#3=[5], expr#4=[=($t2, $t3)], expr#5=[3], expr#6=[>($t0, $t5)], expr#7=[10], expr#8=[<($t0, $t7)], expr#9=[AND($t4, $t6, $t8)], EXPR$0=[$t9]{noformat}
> After:
> {noformat}
> expr#0..1=[{inputs}], expr#2=[5], expr#3=[=($t0, $t2)], EXPR$0=[$t3]{noformat}
> 3:
> {code:java}
> "cust_id"=5 and "cust_id">6 and "cust_id"< 10{code}
> Before:
> {noformat}
> expr#0..1=[{inputs}], expr#2=[CAST($t0):INTEGER NOT NULL], expr#3=[5], expr#4=[=($t2, $t3)], expr#5=[6], expr#6=[>($t0, $t5)], expr#7=[10], expr#8=[<($t0, $t7)], expr#9=[AND($t4, $t6, $t8)], EXPR$0=[$t9]{noformat}
> After:
> {noformat}
> expr#0..1=[{inputs}], expr#2=[false], EXPR$0=[$t2]{noformat}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)