You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@asterixdb.apache.org by "Chen Luo (Jira)" <ji...@apache.org> on 2020/07/17 01:04:00 UTC
[jira] [Created] (ASTERIXDB-2761) Join keys not sorted in index
nested loop join
Chen Luo created ASTERIXDB-2761:
-----------------------------------
Summary: Join keys not sorted in index nested loop join
Key: ASTERIXDB-2761
URL: https://issues.apache.org/jira/browse/ASTERIXDB-2761
Project: Apache AsterixDB
Issue Type: Bug
Components: COMP - Compiler
Reporter: Chen Luo
Attachments: tpch_ddl.sql
When testing a query (TPC-H Q3), I found out that sometimes the join keys are not sorted if the index-nested loop join was choosen. The DDLs are attached below.
The example query:
```
use tpch;
SELECT l_orderkey AS l_orderkey,
sum(l.l_extendedprice * (1 - l.l_discount)) AS revenue,
o_orderdate AS o_orderdate,
o_shippriority AS o_shippriority
FROM Customer AS c,
Orders AS o,
LineItem AS l
where c.c_mktsegment = 'BUILDING' AND c.c_custkey = o.o_custkey
AND {color:red}o.o_orderkey /*+ indexnl */= l.l_orderkey {color}
AND o.o_orderdate < '1995-03-15' AND l.l_shipdate > '1995-03-15'
/* +hash */
GROUP BY l.l_orderkey AS l_orderkey,
o.o_orderdate AS o_orderdate,
o.o_shippriority AS o_shippriority
ORDER BY revenue DESC,o_orderdate
LIMIT 10;
```
The optimized query plan is attached below. The LineItem table was searched directly using o_orderkeys without sorting them first.
```
distribute result [$$105]
-- DISTRIBUTE_RESULT |UNPARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
limit 10
-- STREAM_LIMIT |UNPARTITIONED|
project ([$$105])
-- STREAM_PROJECT |PARTITIONED|
assign [$$105] <- [{"l_orderkey": $$l_orderkey, "revenue": $$117, "o_orderdate": $$o_orderdate, "o_shippriority": $$o_shippriority}]
-- ASSIGN |PARTITIONED|
exchange
-- SORT_MERGE_EXCHANGE [$$117(DESC), $$o_orderdate(ASC) ] |PARTITIONED|
limit 10
-- STREAM_LIMIT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
order (topK: 10) (DESC, $$117) (ASC, $$o_orderdate)
-- STABLE_SORT [topK: 10] [$$117(DESC), $$o_orderdate(ASC)] |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
group by ([$$l_orderkey := $$125; $$o_orderdate := $$126; $$o_shippriority := $$127]) decor ([]) {
aggregate [$$117] <- [global-sql-sum-serial($$124)]
-- AGGREGATE |LOCAL|
nested tuple source
-- NESTED_TUPLE_SOURCE |LOCAL|
}
-- EXTERNAL_GROUP_BY[$$125, $$126, $$127] |PARTITIONED|
exchange
-- HASH_PARTITION_EXCHANGE [$$125, $$126, $$127] |PARTITIONED|
group by ([$$125 := $$113; $$126 := $$110; $$127 := $$108]) decor ([]) {
aggregate [$$124] <- [local-sql-sum-serial(numeric-multiply($$122, numeric-subtract(1, $$123)))]
-- AGGREGATE |LOCAL|
nested tuple source
-- NESTED_TUPLE_SOURCE |LOCAL|
}
-- EXTERNAL_GROUP_BY[$$113, $$110, $$108] |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
project ([$$122, $$123, $$113, $$110, $$108])
-- STREAM_PROJECT |PARTITIONED|
select (gt($$l.getField(10), "1995-03-15"))
-- STREAM_SELECT |PARTITIONED|
assign [$$123, $$122] <- [$$l.getField(6), $$l.getField(5)]
-- ASSIGN |PARTITIONED|
project ([$$108, $$110, $$113, $$l])
-- STREAM_PROJECT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
unnest-map [$$113, $$114, $$l] <- index-search("LineItem", 0, "tpch", "LineItem", TRUE, TRUE, 1, $$112, 1, $$112, TRUE, TRUE, TRUE)
-- BTREE_SEARCH |PARTITIONED|
exchange
-- BROADCAST_EXCHANGE |PARTITIONED|
project ([$$112, $$108, $$110])
-- STREAM_PROJECT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
join (eq($$111, $$119))
-- HYBRID_HASH_JOIN [$$111][$$119] |PARTITIONED|
exchange
-- HASH_PARTITION_EXCHANGE [$$111] |PARTITIONED|
project ([$$111])
-- STREAM_PROJECT |PARTITIONED|
select (eq($$c.getField(6), "BUILDING"))
-- STREAM_SELECT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
data-scan []<-[$$111, $$c] <- tpch.Customer
-- DATASOURCE_SCAN |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
exchange
-- HASH_PARTITION_EXCHANGE [$$119] |PARTITIONED|
union ($$141, $$133, $$112) ($$143, $$131, $$108) ($$144, $$129, $$110) ($$145, $$130, $$119)
-- UNION_ALL |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
project ([$$141, $$143, $$144, $$145])
-- STREAM_PROJECT |PARTITIONED|
exchange
-- RANDOM_PARTITION_EXCHANGE |PARTITIONED|
select (lt($$144, "1995-03-15"))
-- STREAM_SELECT |PARTITIONED|
project ([$$141, $$143, $$145, $$144])
-- STREAM_PROJECT |PARTITIONED|
assign [$$143, $$145, $$144] <- [$$142.getField(7), $$142.getField(1), $$142.getField(4)]
-- ASSIGN |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
unnest-map [$$141, $$142] <- index-search("Orders", 0, "tpch", "Orders", FALSE, FALSE, 1, $$133, 1, $$133, TRUE, TRUE, TRUE)
-- BTREE_SEARCH |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
split ($$134)
-- SPLIT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
project ([$$129, $$130, $$131, $$133, $$134])
-- STREAM_PROJECT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
unnest-map [$$129, $$130, $$131, $$132, $$133, $$134] <- index-search("orderDateIdx", 0, "tpch", "Orders", FALSE, FALSE, 0, 1, $$128, TRUE, FALSE, FALSE)
-- BTREE_SEARCH |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
assign [$$128] <- ["1995-03-15"]
-- ASSIGN |PARTITIONED|
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
project ([$$133, $$131, $$129, $$130])
-- STREAM_PROJECT |PARTITIONED|
exchange
-- RANDOM_PARTITION_EXCHANGE |PARTITIONED|
project ([$$129, $$130, $$131, $$133])
-- STREAM_PROJECT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
split ($$134)
-- SPLIT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
project ([$$129, $$130, $$131, $$133, $$134])
-- STREAM_PROJECT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
unnest-map [$$129, $$130, $$131, $$132, $$133, $$134] <- index-search("orderDateIdx", 0, "tpch", "Orders", FALSE, FALSE, 0, 1, $$128, TRUE, FALSE, FALSE)
-- BTREE_SEARCH |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
assign [$$128] <- ["1995-03-15"]
-- ASSIGN |PARTITIONED|
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
```
--
This message was sent by Atlassian Jira
(v8.3.4#803005)