# [jira] [Updated] (CALCITE-1449) Support EXCEPT DISTINCT

```     [
https://issues.apache.org/jira/browse/CALCITE-1449?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]```
```
Pengcheng Xiong updated CALCITE-1449:
-------------------------------------
Description:
This will rewrite except distinct as below
{code}
Rewrite: (GB-Union All-GB)-GB-UDTF (on all attributes)
Example: R1 Except All R2
R1 introduce VCol ‘2’, R2 introduce VCol ‘1’
R3 = GB(R1 on all attributes + VCol + count(VCol) as c) union all GB(R2 on all
attributes + VCol + count(VCol) as c)
R4 = GB(R3 on all attributes + sum(c) as a + sum(VCol*c) as b)
Note, now we have m+n=a, 2m+n=b where m is the #row in R1 and n is the #row in
R2 then
m=b-a, n=2a-b, m-n=2b-3a

R5 = Fil (b-a>0 && 2a-b=0)
R6 = select only attributes from R5
{code}

was:
This will rewrite except distinct as below
{code}
Rewrite: (GB-Union All-GB)-GB-UDTF (on all attributes)
Example: R1 Except All R2
R1 introduce VCol ‘2’, R2 introduce VCol ‘1’
R3 = GB(R1 on all attributes + VCol + count(VCol) as c) union all GB(R2 on all
attributes + VCol + count(VCol) as c)
R4 = GB(R3 on all attributes + sum(c) as a + sum(VCol*c) as b)
Note, now we have m+n=a, 2m+n=b where m is the #row in R1 and n is the #row in
R2 then
m=b-a, n=2a-b, m-n=2b-3a

R5 = Fil (b-a>0 && 2a-b=0) R6 = select only attributes from R5
{code}

> Support EXCEPT DISTINCT
> -----------------------
>
>                 Key: CALCITE-1449
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1449
>             Project: Calcite
>            Reporter: Pengcheng Xiong
>            Assignee: Pengcheng Xiong
>
> This will rewrite except distinct as below
> {code}
> Rewrite: (GB-Union All-GB)-GB-UDTF (on all attributes)
> Example: R1 Except All R2
> R1 introduce VCol ‘2’, R2 introduce VCol ‘1’
> R3 = GB(R1 on all attributes + VCol + count(VCol) as c) union all GB(R2 on
> all attributes + VCol + count(VCol) as c)
> R4 = GB(R3 on all attributes + sum(c) as a + sum(VCol*c) as b)
> Note, now we have m+n=a, 2m+n=b where m is the #row in R1 and n is the #row
> in R2 then
> m=b-a, n=2a-b, m-n=2b-3a
> R5 = Fil (b-a>0 && 2a-b=0)
> R6 = select only attributes from R5
> {code}

--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
```