[jira] [Updated] (ASTERIXDB-3083) Indexnl query rewrite results in gives different query semantics than default HHJ

["Ian Maxon (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/ASTERIXDB-3083?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Ian Maxon updated ASTERIXDB-3083:
---------------------------------
    Summary: Indexnl query rewrite results in gives different query semantics than default HHJ  (was: Indexnl gives different result than hash-join)

> Indexnl query rewrite results in gives different query semantics than default HHJ
> ---------------------------------------------------------------------------------
>
>                 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)