You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@asterixdb.apache.org by "Wenhai Li (Jira)" <ji...@apache.org> on 2019/11/13 19:29:00 UTC
[jira] [Created] (ASTERIXDB-2675) Fulltext search predicate
-ftcontains- issues an wrong query plan based on another index-based
selection.
Wenhai Li created ASTERIXDB-2675:
------------------------------------
Summary: Fulltext search predicate -ftcontains- issues an wrong query plan based on another index-based selection.
Key: ASTERIXDB-2675
URL: https://issues.apache.org/jira/browse/ASTERIXDB-2675
Project: Apache AsterixDB
Issue Type: Bug
Components: *DB - AsterixDB
Affects Versions: 0.9.4.1
Reporter: Wenhai Li
When ftcontains is triggered on top of index-based selection results, the query plan is wrong.
Schema:
DROP dataverse test IF EXISTS;
CREATE dataverse test;
USE test;
DROP DATASET lefttable IF EXISTS;
DROP TYPE lefttype IF EXISTS;
CREATE TYPE lefttype AS OPEN {
leftid: string,
leftname: string
};
CREATE DATASET lefttable(lefttype) PRIMARY KEY leftid;
CREATE INDEX leftnameidx on lefttable(leftname) TYPE btree;
USE test;
DROP DATASET righttable IF EXISTS;
DROP TYPE righttype IF EXISTS;
CREATE TYPE righttype AS OPEN {
rightid: string,
leftids: string?
};
CREATE DATASET righttable(righttype) PRIMARY KEY rightid;
CREATE INDEX rightleftidsidx on righttable(leftids) TYPE fulltext;
Query:
USE test;
SELECT LT.leftid, RT.rightid
FROM lefttable LT, righttable RT
WHERE LT.leftname = 'aaa'
AND ftcontains(RT.leftids, `word-tokens`(LT.leftid), \{"mode":"any"})
ORDER BY LT.leftid, RT.rightid;
Plan (which generated less results than what we expected):
distribute result [$$32]
-- DISTRIBUTE_RESULT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
project ([$$32])
-- STREAM_PROJECT |PARTITIONED|
assign [$$32] <- [\{"leftid": $$36, "rightid": $$37}]
-- ASSIGN |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
order (ASC, $$36) (ASC, $$37)
-- STABLE_SORT [$$36(ASC), $$37(ASC)] |PARTITIONED|
exchange
-- RANGE_PARTITION_EXCHANGE [$$36(ASC), $$37(ASC)] |PARTITIONED|
forward: range-map = $$57
-- FORWARD |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
replicate
-- REPLICATE |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
project ([$$36, $$37])
-- STREAM_PROJECT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
join (ftcontains($$39, word-tokens($$36), "mode", "any"))
-- NESTED_LOOP |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
union ($$51, $$46, $$36)
-- UNION_ALL |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
project ([$$51])
-- STREAM_PROJECT |PARTITIONED|
select (eq($$52.getField(1), "aaa"))
-- STREAM_SELECT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
unnest-map [$$51, $$52] <- index-search("lefttable", 0, "test", "lefttable", FALSE, FALSE, 1, $$46, 1, $$46, TRUE, TRUE, TRUE)
-- BTREE_SEARCH |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
split ($$47)
-- SPLIT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
project ([$$46, $$47])
-- STREAM_PROJECT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
unnest-map [$$45, $$46, $$47] <- index-search("leftnameidx", 0, "test", "lefttable", FALSE, FALSE, 1, $$43, 1, $$44, TRUE, TRUE, TRUE)
-- BTREE_SEARCH |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
assign [$$43, $$44] <- ["aaa", "aaa"]
-- ASSIGN |PARTITIONED|
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
project ([$$46])
-- STREAM_PROJECT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
split ($$47)
-- SPLIT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
project ([$$46, $$47])
-- STREAM_PROJECT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
unnest-map [$$45, $$46, $$47] <- index-search("leftnameidx", 0, "test", "lefttable", FALSE, FALSE, 1, $$43, 1, $$44, TRUE, TRUE, TRUE)
-- BTREE_SEARCH |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
assign [$$43, $$44] <- ["aaa", "aaa"]
-- ASSIGN |PARTITIONED|
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
exchange
-- BROADCAST_EXCHANGE |PARTITIONED|
project ([$$37, $$39])
-- STREAM_PROJECT |PARTITIONED|
assign [$$39] <- [$$RT.getField(1)]
-- ASSIGN |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
data-scan []<-[$$37, $$RT] <- test.righttable
-- DATASOURCE_SCAN |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
exchange
-- BROADCAST_EXCHANGE |PARTITIONED|
aggregate [$$57] <- [agg-range-map($$54, $$55, $$56)]
-- AGGREGATE |UNPARTITIONED|
exchange
-- RANDOM_MERGE_EXCHANGE |PARTITIONED|
aggregate [$$54, $$55, $$56] <- [agg-local-sampling($$36, $$37), agg-null-writer($$36), agg-null-writer($$37)]
-- AGGREGATE |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
replicate
-- REPLICATE |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
project ([$$36, $$37])
-- STREAM_PROJECT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
join (ftcontains($$39, word-tokens($$36), "mode", "any"))
-- NESTED_LOOP |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
union ($$51, $$46, $$36)
-- UNION_ALL |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
project ([$$51])
-- STREAM_PROJECT |PARTITIONED|
select (eq($$52.getField(1), "aaa"))
-- STREAM_SELECT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
unnest-map [$$51, $$52] <- index-search("lefttable", 0, "test", "lefttable", FALSE, FALSE, 1, $$46, 1, $$46, TRUE, TRUE, TRUE)
-- BTREE_SEARCH |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
split ($$47)
-- SPLIT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
project ([$$46, $$47])
-- STREAM_PROJECT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
unnest-map [$$45, $$46, $$47] <- index-search("leftnameidx", 0, "test", "lefttable", FALSE, FALSE, 1, $$43, 1, $$44, TRUE, TRUE, TRUE)
-- BTREE_SEARCH |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
assign [$$43, $$44] <- ["aaa", "aaa"]
-- ASSIGN |PARTITIONED|
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
project ([$$46])
-- STREAM_PROJECT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
split ($$47)
-- SPLIT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
project ([$$46, $$47])
-- STREAM_PROJECT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
unnest-map [$$45, $$46, $$47] <- index-search("leftnameidx", 0, "test", "lefttable", FALSE, FALSE, 1, $$43, 1, $$44, TRUE, TRUE, TRUE)
-- BTREE_SEARCH |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
assign [$$43, $$44] <- ["aaa", "aaa"]
-- ASSIGN |PARTITIONED|
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
exchange
-- BROADCAST_EXCHANGE |PARTITIONED|
project ([$$37, $$39])
-- STREAM_PROJECT |PARTITIONED|
assign [$$39] <- [$$RT.getField(1)]
-- ASSIGN |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
data-scan []<-[$$37, $$RT] <- test.righttable
-- DATASOURCE_SCAN |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
--
This message was sent by Atlassian Jira
(v8.3.4#803005)