You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by GitBox <gi...@apache.org> on 2020/08/21 07:46:21 UTC
[GitHub] [incubator-doris] chaoyli opened a new issue #4418: LEFT SEMI/ANTI JOIN wrong answer
chaoyli opened a new issue #4418:
URL: https://github.com/apache/incubator-doris/issues/4418
TPCDS qualification query16 and query94 use LEFT SEMI/ANTI JOIN, but the answer is wrong.
----------------------------------------------------------------
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:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] chaoyli edited a comment on issue #4418: LEFT SEMI/ANTI JOIN wrong answer
Posted by GitBox <gi...@apache.org>.
chaoyli edited a comment on issue #4418:
URL: https://github.com/apache/incubator-doris/issues/4418#issuecomment-678099699
The right answer of query16 is
```
+-------------+---------------------+------------------+
| order count | total shipping cost | total net profit |
+-------------+---------------------+------------------+
| 236 | 1062963.89 | -214910.61 |
+-------------+---------------------+------------------+
```
But now the answer is
```
+-------------+---------------------+------------------+
| order count | total shipping cost | total net profit |
+-------------+---------------------+------------------+
| 213 | 767460.44 | -187341.31 |
+-------------+---------------------+------------------+
```
----------------------------------------------------------------
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:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] chaoyli edited a comment on issue #4418: LEFT SEMI/ANTI JOIN wrong answer
Posted by GitBox <gi...@apache.org>.
chaoyli edited a comment on issue #4418:
URL: https://github.com/apache/incubator-doris/issues/4418#issuecomment-678099783
The right answer of query94 is
```
+-------------+---------------------+------------------+
| order count | total shipping cost | total net profit |
+-------------+---------------------+------------------+
| 33 | 64554.35 | -3979.35 |
+-------------+---------------------+------------------+
```
But now the answer is
```
+-------------+---------------------+------------------+
| order count | total shipping cost | total net profit |
+-------------+---------------------+------------------+
| 23 | 53594.6 | -10049.75 |
+-------------+---------------------+------------------+
```
----------------------------------------------------------------
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:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] chaoyli edited a comment on issue #4418: LEFT SEMI/ANTI JOIN wrong answer
Posted by GitBox <gi...@apache.org>.
chaoyli edited a comment on issue #4418:
URL: https://github.com/apache/incubator-doris/issues/4418#issuecomment-678099699
```
-- query 16
select
count(distinct cs_order_number) as "order count"
,sum(cs_ext_ship_cost) as "total shipping cost"
,sum(cs_net_profit) as "total net profit"
from
catalog_sales cs1
,date_dim
,customer_address
,call_center
where
d_date between '2002-2-01' and
date_add(cast('2002-2-01' as date), 60)
and cs1.cs_ship_date_sk = d_date_sk
and cs1.cs_ship_addr_sk = ca_address_sk
and ca_state = 'GA'
and cs1.cs_call_center_sk = cc_call_center_sk
and cc_county in ('Williamson County', 'Williamson County',
'Williamson County', 'Williamson County',
'Williamson County')
and exists (select *
from catalog_sales cs2
where cs1.cs_order_number = cs2.cs_order_number
and cs1.cs_warehouse_sk <> cs2.cs_warehouse_sk)
and not exists(select *
from catalog_returns cr1
where cs1.cs_order_number = cr1.cr_order_number)
order by count(distinct cs_order_number)
limit 100;
```
The right answer of query16 is
```
+-------------+---------------------+------------------+
| order count | total shipping cost | total net profit |
+-------------+---------------------+------------------+
| 236 | 1062963.89 | -214910.61 |
+-------------+---------------------+------------------+
```
But now the answer is
```
+-------------+---------------------+------------------+
| order count | total shipping cost | total net profit |
+-------------+---------------------+------------------+
| 213 | 767460.44 | -187341.31 |
+-------------+---------------------+------------------+
```
----------------------------------------------------------------
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:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] chaoyli closed issue #4418: LEFT SEMI/ANTI JOIN wrong answer
Posted by GitBox <gi...@apache.org>.
chaoyli closed issue #4418:
URL: https://github.com/apache/incubator-doris/issues/4418
----------------------------------------------------------------
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:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] chaoyli edited a comment on issue #4418: LEFT SEMI/ANTI JOIN wrong answer
Posted by GitBox <gi...@apache.org>.
chaoyli edited a comment on issue #4418:
URL: https://github.com/apache/incubator-doris/issues/4418#issuecomment-678099783
-- query 94
```
select
count(distinct ws_order_number) as "order count"
,sum(ws_ext_ship_cost) as "total shipping cost"
,sum(ws_net_profit) as "total net profit"
from
web_sales ws1
,date_dim
,customer_address
,web_site
where
d_date between '1999-2-01' and
date_add(cast('1999-2-01' as date), 60)
and ws1.ws_ship_date_sk = d_date_sk
and ws1.ws_ship_addr_sk = ca_address_sk
and ca_state = 'IL'
and ws1.ws_web_site_sk = web_site_sk
and web_company_name = 'pri'
and exists (select *
from web_sales ws2
where ws1.ws_order_number = ws2.ws_order_number
and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk)
and not exists(select *
from web_returns wr1
where ws1.ws_order_number = wr1.wr_order_number)
order by count(distinct ws_order_number)
limit 100;
```
The right answer of query94 is
```
+-------------+---------------------+------------------+
| order count | total shipping cost | total net profit |
+-------------+---------------------+------------------+
| 33 | 64554.35 | -3979.35 |
+-------------+---------------------+------------------+
```
But now the answer is
```
+-------------+---------------------+------------------+
| order count | total shipping cost | total net profit |
+-------------+---------------------+------------------+
| 23 | 53594.6 | -10049.75 |
+-------------+---------------------+------------------+
```
----------------------------------------------------------------
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:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] chaoyli edited a comment on issue #4418: LEFT SEMI/ANTI JOIN wrong answer
Posted by GitBox <gi...@apache.org>.
chaoyli edited a comment on issue #4418:
URL: https://github.com/apache/incubator-doris/issues/4418#issuecomment-678099783
```
-- query 94
select
count(distinct ws_order_number) as "order count"
,sum(ws_ext_ship_cost) as "total shipping cost"
,sum(ws_net_profit) as "total net profit"
from
web_sales ws1
,date_dim
,customer_address
,web_site
where
d_date between '1999-2-01' and
date_add(cast('1999-2-01' as date), 60)
and ws1.ws_ship_date_sk = d_date_sk
and ws1.ws_ship_addr_sk = ca_address_sk
and ca_state = 'IL'
and ws1.ws_web_site_sk = web_site_sk
and web_company_name = 'pri'
and exists (select *
from web_sales ws2
where ws1.ws_order_number = ws2.ws_order_number
and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk)
and not exists(select *
from web_returns wr1
where ws1.ws_order_number = wr1.wr_order_number)
order by count(distinct ws_order_number)
limit 100;
```
The right answer of query94 is
```
+-------------+---------------------+------------------+
| order count | total shipping cost | total net profit |
+-------------+---------------------+------------------+
| 33 | 64554.35 | -3979.35 |
+-------------+---------------------+------------------+
```
But now the answer is
```
+-------------+---------------------+------------------+
| order count | total shipping cost | total net profit |
+-------------+---------------------+------------------+
| 23 | 53594.6 | -10049.75 |
+-------------+---------------------+------------------+
```
----------------------------------------------------------------
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:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] chaoyli commented on issue #4418: LEFT SEMI/ANTI JOIN wrong answer
Posted by GitBox <gi...@apache.org>.
chaoyli commented on issue #4418:
URL: https://github.com/apache/incubator-doris/issues/4418#issuecomment-678099699
The right answer of query16 is
```
order count total shipping cost total net profit
----------- ------------------- ----------------
236 1062963.89 -214910.61
```
But now the answer is
----------------------------------------------------------------
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:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] chaoyli commented on issue #4418: LEFT SEMI/ANTI JOIN wrong answer
Posted by GitBox <gi...@apache.org>.
chaoyli commented on issue #4418:
URL: https://github.com/apache/incubator-doris/issues/4418#issuecomment-678099783
```
order count total shipping cost total net profit
----------- ------------------- ----------------
33 64554.35 -3979.35
```
----------------------------------------------------------------
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:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org