[
https://issues.apache.org/jira/browse/ASTERIXDB-3083?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ali Alsuliman updated ASTERIXDB-3083:
-------------------------------------
Description:
Currently, in order to use index-nested-loop-join algorithm via "indexnl" hint,
it is required that the outer collection be cast to the type that is compatible
with the index on the inner collection. This leads to a query that is
semantically different from the hash-join algorithm and that (rightly) produces
a different result. However, from a user perspective, using a hash-join or
nested-loop join should give the same result. Here is an example:
{code:java}
bucket b1 has:
{"f": 1,"o": "f=1","id": 1}
{"f": 8,"o": "f=8","id": 2}
{"f": "1","o": "f=\"1\"","id": 3}
bucket b2 has:
{"f": 1,"o": "f=1","id": 1}
{"f": "1","o": "f=\"1\"","id": 2}
{"f": 9,"o": "f=9","id": 3}
create dataset ds1 on b1;
create dataset ds2 on b2;
create index idx_ds2 on ds2(f: int);
// hash-join
from ds1, ds2 where ds1.f = ds2.f select ds1, ds2;
[ { "ds1": { "f": "1", "o": "f=\"1\"", "id": 3 },
"ds2": { "f": "1", "o": "f=\"1\"", "id": 2 } }, { "ds1":
{ "f": 1, "o": "f=1", "id": 1 }, "ds2": { "f": 1,
"o": "f=1", "id": 1 } }]
// indexnl join where ds1 is the outer collection and ds2 is the inner
from ds1, ds2 where to_bigint(ds1.f) /*+ indexnl */ = ds2.f select ds1, ds2;
[ { "ds1": { "f": 1, "o": "f=1", "id": 1 }, "ds2": {
"f": 1, "o": "f=1", "id": 1 } }, { "ds1": { "f": "1",
"o": "f=\"1\"", "id": 3 }, "ds2": { "f": 1, "o":
"f=1", "id": 1 } }]
// the plan for the indexnl join
distribute result [$$28]-- DISTRIBUTE_RESULT |PARTITIONED| exchange --
ONE_TO_ONE_EXCHANGE |PARTITIONED| project ([$$28]) -- STREAM_PROJECT
|PARTITIONED| assign [$$28] <- [{"ds1": $$ds1, "ds2": $$ds2}] --
ASSIGN |PARTITIONED| project ([$$ds1, $$ds2]) -- STREAM_PROJECT
|PARTITIONED| select (eq($$33, $$ds2.getField("f"))) --
STREAM_SELECT |PARTITIONED| project ([$$ds1, $$33, $$ds2])
-- STREAM_PROJECT |PARTITIONED| exchange --
ONE_TO_ONE_EXCHANGE |PARTITIONED| unnest-map [$$31, $$ds2,
$$32] <- index-search("ds2", 0, "Default", "ds2", true, false, 1, $$37, 1,
$$37, true, true, true) -- BTREE_SEARCH |PARTITIONED|
exchange -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
order (ASC, $$37) -- STABLE_SORT [$$37(ASC)]
|PARTITIONED| exchange --
ONE_TO_ONE_EXCHANGE |PARTITIONED| project ([$$ds1,
$$33, $$37]) -- STREAM_PROJECT |PARTITIONED|
exchange -- ONE_TO_ONE_EXCHANGE
|PARTITIONED| unnest-map [$$36, $$37] <-
index-search("idx_ds2", 0, "Default", "ds2", true, true, 1, $$33, 1, $$33,
true, true, true) -- BTREE_SEARCH |PARTITIONED|
exchange --
BROADCAST_EXCHANGE |PARTITIONED| assign [$$33]
<- [to-bigint($$ds1.getField("f"))] -- ASSIGN
|PARTITIONED| project ([$$ds1])
-- STREAM_PROJECT |PARTITIONED|
exchange -- ONE_TO_ONE_EXCHANGE
|PARTITIONED| data-scan []<-[$$29, $$ds1,
$$30] <- Default.ds1 -- DATASOURCE_SCAN
|PARTITIONED| exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED| {code}
was:Currently, in order to use index-nested-loop-join algorithm via "indexnl"
hint, it is required that the outer collection be cast to the type that is
compatible with the index on the inner collection. This leads to a query that
is semantically different from the hash-join algorithm and that (rightly)
produces a different result. However, from a user perspective, using a
hash-join or nested-loop join should give the same result. Here is an example:
> Indexnl gives different result than hash-join
> ---------------------------------------------
>
> Key: ASTERIXDB-3083
> URL: https://issues.apache.org/jira/browse/ASTERIXDB-3083
> Project: Apache AsterixDB
> Issue Type: Bug
> Components: IDX - Indexes
> Affects Versions: 0.9.3
> Reporter: Ali Alsuliman
> Assignee: Ali Alsuliman
> Priority: Major
> Fix For: 0.9.7
>
>
> Currently, in order to use index-nested-loop-join algorithm via "indexnl"
> hint, it is required that the outer collection be cast to the type that is
> compatible with the index on the inner collection. This leads to a query that
> is semantically different from the hash-join algorithm and that (rightly)
> produces a different result. However, from a user perspective, using a
> hash-join or nested-loop join should give the same result. Here is an example:
> {code:java}
> bucket b1 has:
> {"f": 1,"o": "f=1","id": 1}
> {"f": 8,"o": "f=8","id": 2}
> {"f": "1","o": "f=\"1\"","id": 3}
> bucket b2 has:
> {"f": 1,"o": "f=1","id": 1}
> {"f": "1","o": "f=\"1\"","id": 2}
> {"f": 9,"o": "f=9","id": 3}
> create dataset ds1 on b1;
> create dataset ds2 on b2;
> create index idx_ds2 on ds2(f: int);
> // hash-join
> from ds1, ds2 where ds1.f = ds2.f select ds1, ds2;
> [ { "ds1": { "f": "1", "o": "f=\"1\"", "id": 3 },
> "ds2": { "f": "1", "o": "f=\"1\"", "id": 2 } }, {
> "ds1": { "f": 1, "o": "f=1", "id": 1 }, "ds2": {
> "f": 1, "o": "f=1", "id": 1 } }]
> // indexnl join where ds1 is the outer collection and ds2 is the inner
> from ds1, ds2 where to_bigint(ds1.f) /*+ indexnl */ = ds2.f select ds1, ds2;
> [ { "ds1": { "f": 1, "o": "f=1", "id": 1 }, "ds2": {
> "f": 1, "o": "f=1", "id": 1 } }, { "ds1": { "f":
> "1", "o": "f=\"1\"", "id": 3 }, "ds2": { "f": 1,
> "o": "f=1", "id": 1 } }]
> // the plan for the indexnl join
> distribute result [$$28]-- DISTRIBUTE_RESULT |PARTITIONED| exchange --
> ONE_TO_ONE_EXCHANGE |PARTITIONED| project ([$$28]) -- STREAM_PROJECT
> |PARTITIONED| assign [$$28] <- [{"ds1": $$ds1, "ds2": $$ds2}] --
> ASSIGN |PARTITIONED| project ([$$ds1, $$ds2]) --
> STREAM_PROJECT |PARTITIONED| select (eq($$33, $$ds2.getField("f")))
> -- STREAM_SELECT |PARTITIONED| project ([$$ds1, $$33,
> $$ds2]) -- STREAM_PROJECT |PARTITIONED| exchange
> -- ONE_TO_ONE_EXCHANGE |PARTITIONED| unnest-map
> [$$31, $$ds2, $$32] <- index-search("ds2", 0, "Default", "ds2", true, false,
> 1, $$37, 1, $$37, true, true, true) -- BTREE_SEARCH
> |PARTITIONED| exchange --
> ONE_TO_ONE_EXCHANGE |PARTITIONED| order (ASC, $$37)
> -- STABLE_SORT [$$37(ASC)] |PARTITIONED|
> exchange -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
> project ([$$ds1, $$33, $$37]) --
> STREAM_PROJECT |PARTITIONED| exchange
> -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
> unnest-map [$$36, $$37] <- index-search("idx_ds2", 0, "Default", "ds2", true,
> true, 1, $$33, 1, $$33, true, true, true) --
> BTREE_SEARCH |PARTITIONED| exchange
> -- BROADCAST_EXCHANGE |PARTITIONED|
> assign [$$33] <- [to-bigint($$ds1.getField("f"))]
> -- ASSIGN |PARTITIONED| project
> ([$$ds1]) -- STREAM_PROJECT |PARTITIONED|
> exchange
> -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
> data-scan []<-[$$29, $$ds1, $$30] <- Default.ds1
> -- DATASOURCE_SCAN |PARTITIONED|
> exchange -- ONE_TO_ONE_EXCHANGE
> |PARTITIONED| empty-tuple-source
> -- EMPTY_TUPLE_SOURCE |PARTITIONED|
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
