http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/olist-edit-distance-check-let-panic.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/olist-edit-distance-check-let-panic.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/olist-edit-distance-check-let-panic.aql new file mode 100644 index 0000000..ea22e19 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/olist-edit-distance-check-let-panic.aql @@ -0,0 +1,61 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Tests whether an ngram_index is applied to optimize a selection query using the edit-distance-check function on lists. + * Tests that the optimizer rule correctly drills through the let clauses. + * The index should *not* be applied (see below). + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +create type AddressType as closed { + number: int32, + street: string, + city: string +} + +create type CustomerTypetmp as closed { + cid: int32, + name: string, + age: int32?, + address: AddressType?, + interests: [string], + children: [ { name: string, age: int32? } ] +} + +create type CustomerType as closed { + nested : CustomerTypetmp +} + +create dataset Customers(CustomerType) primary key nested.cid; + +create dataset Customers2(CustomerType) primary key nested.cid; + +create index interests_index on Customers(nested.interests) type keyword; + +write output to asterix_nc1:"rttest/inverted-index-complex_olist-edit-distance-check-let-panic.adm"; + +for $c in dataset('Customers') +let $ed := edit-distance-check($c.nested.interests, ["computers", "wine", "walking"], 3) +where $ed[0] +order by $c.nested.cid +return $c
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/olist-edit-distance-check-let.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/olist-edit-distance-check-let.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/olist-edit-distance-check-let.aql new file mode 100644 index 0000000..e88a673 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/olist-edit-distance-check-let.aql @@ -0,0 +1,63 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Tests whether an ngram_index is applied to optimize a selection query using the edit-distance-check function on lists. + * Tests that the optimizer rule correctly drills through the let clauses. + * The index should be applied. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +create type AddressType as closed { + number: int32, + street: string, + city: string +} + +create type CustomerTypetmp as closed { + cid: int32, + name: string, + age: int32?, + address: AddressType?, + interests: [string], + children: [ { name: string, age: int32? } ] +} + + + +create type CustomerType as closed { + nested : CustomerTypetmp +} + +create dataset Customers(CustomerType) primary key nested.cid; + +create dataset Customers2(CustomerType) primary key nested.cid; + +create index interests_index on Customers(nested.interests) type keyword; + +write output to asterix_nc1:"rttest/inverted-index-complex_olist-edit-distance-check-let.adm"; + +for $c in dataset('Customers') +let $ed := edit-distance-check($c.nested.interests, ["computers", "wine", "walking"], 1) +where $ed[0] +order by $c.nested.cid +return $c http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/olist-jaccard-check-let.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/olist-jaccard-check-let.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/olist-jaccard-check-let.aql new file mode 100644 index 0000000..bd7245f --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/olist-jaccard-check-let.aql @@ -0,0 +1,59 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Tests whether a keyword index is applied to optimize a selection query using the similarity-jaccard-check function on lists. + * Tests that the optimizer rule correctly drills through the let clauses. + * The index should be applied. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +create type AddressType as closed { + number: int32, + street: string, + city: string +} + +create type CustomerTypetmp as closed { + cid: int32, + name: string, + age: int32?, + address: AddressType?, + interests: [string], + children: [ { name: string, age: int32? } ] +} + +create type CustomerType as closed { + nested : CustomerTypetmp +} + +create dataset Customers(CustomerType) primary key nested.cid; + + +create index interests_index on Customers(nested.interests) type keyword; + +write output to asterix_nc1:"rttest/inverted-index-complex_olist-jaccard-check-let.adm"; + +for $c in dataset('Customers') +let $jacc := similarity-jaccard-check($c.nested.interests, ["databases", "computers", "wine"], 0.7f) +where $jacc[0] +return $c http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/ulist-jaccard-check-let.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/ulist-jaccard-check-let.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/ulist-jaccard-check-let.aql new file mode 100644 index 0000000..c4093dc --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/ulist-jaccard-check-let.aql @@ -0,0 +1,60 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Tests whether a keyword index is applied to optimize a selection query using the similarity-jaccard-check function on lists. + * Tests that the optimizer rule correctly drills through the let clauses. + * The index should be applied. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +create type AddressType as closed { + number: int32, + street: string, + city: string +} + +create type CustomerTypetmp as closed { + cid: int32, + name: string, + age: int32?, + address: AddressType?, + interests: {{string}}, + children: [ { name: string, age: int32? } ] +} + +create type CustomerType as closed { + nested : CustomerTypetmp +} + +create dataset Customers(CustomerType) primary key nested.cid; + +create dataset Customers2(CustomerType) primary key nested.cid; + +create index interests_index on Customers(nested.interests) type keyword; + +write output to asterix_nc1:"rttest/inverted-index-complex_ulist-jaccard-check-let.adm"; + +for $c in dataset('Customers') +let $jacc := similarity-jaccard-check($c.nested.interests, ["databases", "computers", "wine"], 0.7f) +where $jacc[0] +return $c http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/word-jaccard-check-let.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/word-jaccard-check-let.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/word-jaccard-check-let.aql new file mode 100644 index 0000000..4a416b8 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/word-jaccard-check-let.aql @@ -0,0 +1,52 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Tests whether a keyword index is applied to optimize a selection query using the similarity-jaccard-check function on word tokens. + * Tests that the optimizer rule correctly drills through the let clauses. + * The index should be applied. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +create type DBLPTypetmp as closed { + id: int32, + dblpid: string, + title: string, + authors: string, + misc: string +} + +create type DBLPType as closed { + nested : DBLPTypetmp +} + +create dataset DBLP(DBLPType) primary key nested.id; + +create index keyword_index on DBLP(nested.title) type keyword; + +write output to asterix_nc1:"rttest/inverted-index-complex_word-jaccard-check-let.adm"; + +for $o in dataset('DBLP') +let $jacc := similarity-jaccard-check(word-tokens($o.nested.title), word-tokens("Transactions for Cooperative Environments"), 0.5f) +where $jacc[0] +return $o + http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/word-jaccard-check-multi-let.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/word-jaccard-check-multi-let.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/word-jaccard-check-multi-let.aql new file mode 100644 index 0000000..7183115 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/word-jaccard-check-multi-let.aql @@ -0,0 +1,54 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Tests whether a keyword index is applied to optimize a selection query using the similarity-jaccard-check function on word tokens. + * Tests that the optimizer rule correctly drills through the let clauses. + * The index should be applied. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +create type DBLPTypetmp as closed { + id: int32, + dblpid: string, + title: string, + authors: string, + misc: string +} + +create type DBLPType as closed { + nested : DBLPTypetmp +} + +create dataset DBLP(DBLPType) primary key nested.id; + +create index keyword_index on DBLP(nested.title) type keyword; + +write output to asterix_nc1:"rttest/inverted-index-complex_word-jaccard-check-multi-let.adm"; + +// This test is complex because we have three assigns to drill into. +for $paper in dataset('DBLP') +let $paper_tokens := word-tokens($paper.nested.title) +let $query_tokens := word-tokens("Transactions for Cooperative Environments") +let $jacc := similarity-jaccard-check($paper_tokens, $query_tokens, 0.8f) +where $jacc[0] +return {"Paper": $paper_tokens, "Query": $query_tokens } http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01.aql new file mode 100644 index 0000000..90e7cb9 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01.aql @@ -0,0 +1,72 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Test that left-outer-join may use two available indexes, one for primary index in prob subtree and another for secondary rtree index in index subtree. + * Issue : 730, 741 + * Expected Res : Success + * Date : 8th May 2014 + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +create type TwitterUserType as closed { + screen-name: string, + lang: string, + friends-count: int32, + statuses-count: int32, + name: string, + followers-count: int32 +} + +create type TweetMessageNestedType as closed { + tweetid: int64, + user: TwitterUserType, + sender-location: point, + send-time: datetime, + referred-topics: {{ string }}, + message-text: string, + countA: int32, + countB: int32 +} + +create type TweetMessageType as closed { + nested: TweetMessageNestedType +} + +create dataset TweetMessages(TweetMessageType) +primary key nested.tweetid; + +create index msgNgramIx on TweetMessages(nested.message-text) type ngram(3); + +write output to asterix_nc1:"rttest/inverted-index-join_leftouterjoin-probe-pidx-with-join-edit-distance-check_idx_01.adm"; + +for $t1 in dataset('TweetMessages') +where $t1.nested.tweetid > int64("240") +order by $t1.nested.tweetid +return { + "tweet": {"id": $t1.nested.tweetid, "topics" : $t1.nested.message-text} , + "similar-tweets": for $t2 in dataset('TweetMessages') + let $sim := edit-distance-check($t1.nested.message-text, $t2.nested.message-text, 7) + where $sim[0] and + $t2.nested.tweetid != $t1.nested.tweetid + order by $t2.nested.tweetid + return {"id": $t2.nested.tweetid, "topics" : $t2.nested.message-text} +}; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-jaccard-check-idx_01.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-jaccard-check-idx_01.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-jaccard-check-idx_01.aql new file mode 100644 index 0000000..128b068 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-jaccard-check-idx_01.aql @@ -0,0 +1,72 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Test that left-outer-join may use two available indexes, one for primary index in prob subtree and another for secondary rtree index in index subtree. + * Issue : 730, 741 + * Expected Res : Success + * Date : 8th May 2014 + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +create type TwitterUserType as closed { + screen-name: string, + lang: string, + friends-count: int32, + statuses-count: int32, + name: string, + followers-count: int32 +} + +create type TweetMessageNestedType as closed { + tweetid: int64, + user: TwitterUserType, + sender-location: point, + send-time: datetime, + referred-topics: {{ string }}, + message-text: string, + countA: int32, + countB: int32 +} + +create type TweetMessageType as closed { + nested: TweetMessageNestedType +} + +create dataset TweetMessages(TweetMessageType) +primary key nested.tweetid; + +create index topicKeywordIx on TweetMessages(nested.referred-topics) type keyword; + +write output to asterix_nc1:"rttest/inverted-index-join_leftouterjoin-probe-pidx-with-join-jaccard-check_idx_01.adm"; + +for $t1 in dataset('TweetMessages') +where $t1.nested.tweetid > int64("240") +order by $t1.nested.tweetid +return { + "tweet": {"id": $t1.nested.tweetid, "topics" : $t1.nested.referred-topics} , + "similar-tweets": for $t2 in dataset('TweetMessages') + let $sim := similarity-jaccard-check($t1.nested.referred-topics, $t2.nested.referred-topics, 0.5f) + where $sim[0] and + $t2.nested.tweetid != $t1.nested.tweetid + order by $t2.nested.tweetid + return {"id": $t2.nested.tweetid, "topics" : $t2.nested.referred-topics} +}; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/ngram-edit-distance-inline.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/ngram-edit-distance-inline.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/ngram-edit-distance-inline.aql new file mode 100644 index 0000000..44a8430 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/ngram-edit-distance-inline.aql @@ -0,0 +1,53 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Fuzzy self joins a dataset, DBLP, based on the edit-distance function of its authors. + * DBLP has a 3-gram index on authors, and we expect the join to be transformed into an indexed nested-loop join. + * We test the inlining of variables that enable the select to be pushed into the join for subsequent optimization with an index. + * We expect the top-level equi join introduced because of surrogate optimization to be removed, since it is not necessary. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +create type DBLPTypetmp as closed { + id: int32, + dblpid: string, + title: string, + authors: string, + misc: string +} + +create type DBLPType as closed { + nested : DBLPTypetmp +} + +create dataset DBLP(DBLPType) primary key nested.id; + +create index ngram_index on DBLP(nested.authors) type ngram(3); + +write output to asterix_nc1:"rttest/inverted-index-join-noeqjoin_ngram-edit-distance-inline.adm"; + +for $a in dataset('DBLP') +for $b in dataset('DBLP') +let $ed := edit-distance($a.nested.authors, $b.nested.authors) +where $ed < 3 and $a.nested.id < $b.nested.id +return {"aauthors": $a.nested.authors, "bauthors": $b.nested.authors, "ed": $ed} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/ngram-fuzzyeq-edit-distance_01.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/ngram-fuzzyeq-edit-distance_01.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/ngram-fuzzyeq-edit-distance_01.aql new file mode 100644 index 0000000..e188ff1 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/ngram-fuzzyeq-edit-distance_01.aql @@ -0,0 +1,67 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Fuzzy joins two datasets, DBLP and CSX, based on ~= using edit distance of their authors. + * DBLP has a 3-gram index on authors, and we expect the join to be transformed into an indexed nested-loop join. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +create type DBLPTypetmp as closed { + id: int32, + dblpid: string, + title: string, + authors: string, + misc: string +} + +create type CSXTypetmp as closed { + id: int32, + csxid: string, + title: string, + authors: string, + misc: string +} + +create type DBLPType as closed { + nested : DBLPTypetmp +} + +create type CSXType as closed { + nested : CSXTypetmp +} + +create dataset DBLP(DBLPType) primary key nested.id; + +create dataset CSX(CSXType) primary key nested.id; + +create index ngram_index on CSX(nested.authors) type ngram(3); + +write output to asterix_nc1:"rttest/inverted-index-join_ngram-fuzzyeq-edit-distance_01.adm"; + +set simfunction 'edit-distance'; +set simthreshold '3'; + +for $a in dataset('DBLP') +for $b in dataset('CSX') +where $a.nested.authors ~= $b.nested.authors and $a.nested.id < $b.nested.id +return {"arec": $a, "brec": $b } http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/ngram-jaccard-inline.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/ngram-jaccard-inline.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/ngram-jaccard-inline.aql new file mode 100644 index 0000000..7448a06 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/ngram-jaccard-inline.aql @@ -0,0 +1,54 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Fuzzy self joins a dataset, DBLP, based on the similarity-jaccard function of its titles' 3-gram tokens. + * DBLP has a 3-gram index on title, and we expect the join to be transformed into an indexed nested-loop join. + * We test the inlining of variables that enable the select to be pushed into the join for subsequent optimization with an index. + * We expect the top-level equi join introduced because of surrogate optimization to be removed, since it is not necessary. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; +set import-private-functions 'true'; + +create type DBLPTypetmp as closed { + id: int32, + dblpid: string, + title: string, + authors: string, + misc: string +} + +create type DBLPType as closed { + nested : DBLPTypetmp +} + +create dataset DBLP(DBLPType) primary key nested.id; + +create index ngram_index on DBLP(nested.title) type ngram(3); + +write output to asterix_nc1:"rttest/inverted-index-join-noeqjoin_ngram-jaccard-inline.adm"; + +for $a in dataset('DBLP') +for $b in dataset('DBLP') +let $jacc := similarity-jaccard(gram-tokens($a.nested.title, 3, false), gram-tokens($b.nested.title, 3, false)) +where $jacc >= 0.5f and $a.nested.id < $b.nested.id +return {"atitle": $a.nested.title, "btitle": $b.nested.title, "jacc": $jacc} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/olist-edit-distance-inline.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/olist-edit-distance-inline.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/olist-edit-distance-inline.aql new file mode 100644 index 0000000..542361b --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/olist-edit-distance-inline.aql @@ -0,0 +1,64 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Fuzzy self joins a dataset, Customers, based on the edit-distance function of its interest lists. + * Customers has a keyword index on interests, and we expect the join to be transformed into an indexed nested-loop join. + * We test the inlining of variables that enable the select to be pushed into the join for subsequent optimization with an index. + * We expect the top-level equi join introduced because of surrogate optimization to be removed, since it is not necessary. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +create type AddressType as closed { + number: int32, + street: string, + city: string +} + +create type CustomerTypetmp as closed { + cid: int32, + name: string, + age: int32?, + address: AddressType?, + interests: [string], + children: [ { name: string, age: int32? } ] +} + + + +create type CustomerType as closed { + nested : CustomerTypetmp +} + +create dataset Customers(CustomerType) primary key nested.cid; + +create dataset Customers2(CustomerType) primary key nested.cid; + +create index interests_index on Customers(nested.interests) type keyword; + +write output to asterix_nc1:"rttest/inverted-index-join-noeqjoin_olist-edit-distance-inline.adm"; + +for $a in dataset('Customers') +for $b in dataset('Customers') +let $ed := edit-distance($a.nested.interests, $b.nested.interests) +where $ed <= 2 and $a.nested.cid < $b.nested.cid +return {"ainterests": $a.nested.interests, "binterests": $b.nested.interests, "ed": $ed} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/olist-jaccard-inline.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/olist-jaccard-inline.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/olist-jaccard-inline.aql new file mode 100644 index 0000000..adbe0e5 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/olist-jaccard-inline.aql @@ -0,0 +1,62 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Fuzzy self joins a dataset, Customers, based on the similarity-jaccard function of its interest lists. + * Customers has a keyword index on interests, and we expect the join to be transformed into an indexed nested-loop join. + * We test the inlining of variables that enable the select to be pushed into the join for subsequent optimization with an index. + * We expect the top-level equi join introduced because of surrogate optimization to be removed, since it is not necessary. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +create type AddressType as closed { + number: int32, + street: string, + city: string +} + +create type CustomerTypetmp as closed { + cid: int32, + name: string, + age: int32?, + address: AddressType?, + interests: [string], + children: [ { name: string, age: int32? } ] +} + +create type CustomerType as closed { + nested : CustomerTypetmp +} + +create dataset Customers(CustomerType) primary key nested.cid; + +create dataset Customers2(CustomerType) primary key nested.cid; + +create index interests_index on Customers(nested.interests) type keyword; + +write output to asterix_nc1:"rttest/inverted-index-join-noeqjoin_olist-jaccard-inline.adm"; + +for $a in dataset('Customers') +for $b in dataset('Customers') +let $jacc := /*+ indexnl */ similarity-jaccard($a.nested.interests, $b.nested.interests) +where $jacc >= 0.7f and $a.nested.cid < $b.nested.cid +return {"ainterests": $a.nested.interests, "binterests": $b.nested.interests, "jacc": $jacc } http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/ulist-jaccard-inline.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/ulist-jaccard-inline.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/ulist-jaccard-inline.aql new file mode 100644 index 0000000..5e3a563 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/ulist-jaccard-inline.aql @@ -0,0 +1,62 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Fuzzy self joins a dataset, Customers, based on the similarity-jaccard function of its interest sets. + * Customers has a keyword index on interests, and we expect the join to be transformed into an indexed nested-loop join. + * We test the inlining of variables that enable the select to be pushed into the join for subsequent optimization with an index. + * We expect the top-level equi join introduced because of surrogate optimization to be removed, since it is not necessary. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +create type AddressType as closed { + number: int32, + street: string, + city: string +} + +create type CustomerTypetmp as closed { + cid: int32, + name: string, + age: int32?, + address: AddressType?, + interests: {{string}}, + children: [ { name: string, age: int32? } ] +} + +create type CustomerType as closed { + nested : CustomerTypetmp +} + +create dataset Customers(CustomerType) primary key nested.cid; + +create dataset Customers2(CustomerType) primary key nested.cid; + +create index interests_index on Customers(nested.interests) type keyword; + +write output to asterix_nc1:"rttest/inverted-index-join-noeqjoin_ulist-jaccard-inline.adm"; + +for $a in dataset('Customers') +for $b in dataset('Customers') +let $jacc := /*+ indexnl */ similarity-jaccard($a.nested.interests, $b.nested.interests) +where $jacc >= 0.7f and $a.nested.cid < $b.nested.cid +return {"ainterests": $a.nested.interests, "binterests": $b.nested.interests, "jacc": $jacc} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/word-jaccard-check-after-btree-access.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/word-jaccard-check-after-btree-access.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/word-jaccard-check-after-btree-access.aql new file mode 100644 index 0000000..0fd522d --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/word-jaccard-check-after-btree-access.aql @@ -0,0 +1,73 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Fuzzy self joins a dataset, TweetMessages, based on the similarity-jaccard-check function of its text-messages' word tokens. + * TweetMessages has a keyword index on text-message and btree index on the primary key tweetid, and we expect the join to be + * transformed into btree and inverted indexed nested-loop joins. We test whether the join condition can be transformed into + * multiple indexed nested loop joins of various type of indexes. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +create type TwitterUserType as closed { + screen-name: string, + lang: string, + friends-count: int32, + statuses-count: int32, + name: string, + followers-count: int32 +} + +create type TweetMessageNestedType as closed { + tweetid: int64, + user: TwitterUserType, + sender-location: point, + send-time: datetime, + referred-topics: {{ string }}, + message-text: string, + countA: int32, + countB: int32 +} + +create type TweetMessageType as closed { + nested: TweetMessageNestedType +} + +create dataset TweetMessages(TweetMessageType) +primary key nested.tweetid; + +create index twmSndLocIx on TweetMessages(nested.sender-location) type rtree; +create index msgCountAIx on TweetMessages(nested.countA) type btree; +create index msgCountBIx on TweetMessages(nested.countB) type btree; +create index msgTextIx on TweetMessages(nested.message-text) type keyword; + +write output to asterix_nc1:"rttest/inverted-index-join_word-jaccard-check-after-btree-access.adm"; + +for $t1 in dataset('TweetMessages') +for $t2 in dataset('TweetMessages') +let $sim := similarity-jaccard-check(word-tokens($t1.nested.message-text), word-tokens($t2.nested.message-text), 0.6f) +where $sim[0] and $t1.nested.tweetid < int64("20") and $t2.nested.tweetid != $t1.nested.tweetid +return { + "t1": $t1.nested.tweetid, + "t2": $t2.nested.tweetid, + "sim": $sim[1] +} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/word-jaccard-inline.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/word-jaccard-inline.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/word-jaccard-inline.aql new file mode 100644 index 0000000..93bcc3e --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/word-jaccard-inline.aql @@ -0,0 +1,53 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Fuzzy self joins a dataset, DBLP, based on the similarity-jaccard function of its titles' word tokens. + * DBLP has a keyword index on title, and we expect the join to be transformed into an indexed nested-loop join. + * We test the inlining of variables that enable the select to be pushed into the join for subsequent optimization with an index. + * We expect the top-level equi join introduced because of surrogate optimization to be removed, since it is not necessary. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +create type DBLPTypetmp as closed { + id: int32, + dblpid: string, + title: string, + authors: string, + misc: string +} + +create type DBLPType as closed { + nested : DBLPTypetmp +} + +create dataset DBLP(DBLPType) primary key nested.id; + +create index keyword_index on DBLP(nested.title) type keyword; + +write output to asterix_nc1:"rttest/inverted-index-join-noeqjoin_word-jaccard-inline.adm"; + +for $a in dataset('DBLP') +for $b in dataset('DBLP') +let $jacc := similarity-jaccard(word-tokens($a.nested.title), word-tokens($b.nested.title)) +where $jacc >= 0.5f and $a.nested.id < $b.nested.id +return {"atitle": $a.nested.title, "btitle": $b.nested.title, "jacc": $jacc} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02.aql new file mode 100644 index 0000000..4fb3041 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02.aql @@ -0,0 +1,75 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Test that left-outer-join may use two available indexes, one for primary index in prob subtree and another for secondary rtree index in index subtree. + * Issue : 730, 741 + * Expected Res : Success + * Date : 8th May 2014 + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +create type TwitterUserType as closed { + screen-name: string, + lang: string, + friends-count: int32, + statuses-count: int32, + name: string, + followers-count: int32 +} + +create type TweetMessageNestedType as closed { + tweetid: int64, + user: TwitterUserType, + sender-location: point, + send-time: datetime, + referred-topics: {{ string }}, + message-text: string, + countA: int32, + countB: int32 +} + +create type TweetMessageType as open { + nested: TweetMessageNestedType +} + +create dataset TweetMessages(TweetMessageType) +primary key nested.tweetid; + +create index twmSndLocIx on TweetMessages(nested.sender-location) type rtree; +create index msgCountAIx on TweetMessages(nested.countA) type btree; +create index msgCountBIx on TweetMessages(nested.countB) type btree; +create index msgTextIx on TweetMessages(nested.message-text) type keyword; + +write output to asterix_nc1:"rttest/rtree-index-join_leftouterjoin-probe-pidx-with-join-rtree-sidx_02.adm"; + +for $t1 in dataset('TweetMessages') +let $n := create-circle($t1.nested.sender-location, 0.5) +where $t1.nested.tweetid < int64("10") +order by $t1.nested.tweetid +return { +"tweetid1": $t1.nested.tweetid, +"loc1":$t1.nested.sender-location, +"nearby-message": for $t2 in dataset('TweetMessages') + where spatial-intersect($t2.nested.sender-location, $n) and $t1.nested.tweetid != $t2.nested.tweetid + order by $t2.nested.tweetid + return {"tweetid2":$t2.nested.tweetid, "loc2":$t2.nested.sender-location} +}; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/rtree-index-join/spatial-intersect-point_02.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/rtree-index-join/spatial-intersect-point_02.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/rtree-index-join/spatial-intersect-point_02.aql new file mode 100644 index 0000000..bed6626 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/rtree-index-join/spatial-intersect-point_02.aql @@ -0,0 +1,55 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Joins two datasets on the intersection of their point attributes. + * The dataset 'MyData2' has an enforced open RTree index?, and we expect the + * join to be transformed into an indexed nested-loop join. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +create type MyRecord as closed { + id: int32, + point: point, + kwds: string, + line1: line, + line2: line, + poly1: polygon, + poly2: polygon, + rec: rectangle +} + +create type MyRecordNested as closed { + nested: MyRecord +} + +create dataset MyData1(MyRecordNested) primary key nested.id; +create dataset MyData2(MyRecord) primary key id; + +create index rtree_index on MyData2(point) type rtree; + +write output to asterix_nc1:"rttest/rtree-index-join_spatial-intersect-point_02.adm"; + +for $a in dataset('MyData1') +for $b in dataset('MyData2') +where spatial-intersect($a.nested.point, $b.point) +return {"a": $a, "b": $b} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/rtree-index-join/spatial-intersect-point_03.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/rtree-index-join/spatial-intersect-point_03.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/rtree-index-join/spatial-intersect-point_03.aql new file mode 100644 index 0000000..e991ee8 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/rtree-index-join/spatial-intersect-point_03.aql @@ -0,0 +1,53 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Self-joins a dataset on the intersection of its point attribute. + * The dataset has an enforced open RTree index?, and we expect the + * join to be transformed into an indexed nested-loop join. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +create type MyRecord as closed { + id: int32, + point: point, + kwds: string, + line1: line, + line2: line, + poly1: polygon, + poly2: polygon, + rec: rectangle +} + +create type MyRecordNested as closed { + nested: MyRecord +} +create dataset MyData(MyRecordNested) primary key nested.id; + +create index rtree_index on MyData(nested.point) type rtree; + +write output to asterix_nc1:"rttest/rtree-index-join_spatial-intersect-point_03.adm"; + +for $a in dataset('MyData') +for $b in dataset('MyData') +where spatial-intersect($a.nested.point, $b.nested.point) +return {"a": $a, "b": $b} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join-multiindex.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join-multiindex.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join-multiindex.aql new file mode 100644 index 0000000..4c2e45c --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join-multiindex.aql @@ -0,0 +1,85 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Equi joins two datasets, FacebookUsers and FacebookMessages, based on their user's id. + * We first expect FacebookUsers' primary index to be used + * to satisfy the range condition on it's primary key. + * FacebookMessages has a secondary btree index on author-id-copy, and given the 'indexnl' hint + * we expect the join to be transformed into an indexed nested-loop join. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +create type EmploymentType as closed { + organization-name: string, + start-date: date, + end-date: date? +} + +create type FacebookUserTypetmp as closed { + id: int32, + id-copy: int32, + alias: string, + name: string, + user-since: datetime, + user-since-copy: datetime, + friend-ids: {{ int32 }}, + employment: [EmploymentType] +} + +create type FacebookMessageTypetmp as open { + message-id: int32, + message-id-copy: int32, + author-id: int32, + in-response-to: int32?, + sender-location: point?, + message: string +} + +create type FacebookUserType as closed { + nested : FacebookUserTypetmp +} + +create type FacebookMessageType as closed { + nested : FacebookMessageTypetmp +} + +create dataset FacebookUsers(FacebookUserType) +primary key nested.id; + +create dataset FacebookMessages(FacebookMessageType) +primary key nested.message-id; + +create index fbmIdxAutId if not exists on FacebookMessages(nested.author-id-copy: int32?) enforced; + +write output to asterix_nc1:"rttest/btree-index-join_title-secondary-equi-join-multiindex.adm"; + +for $user in dataset('FacebookUsers') +for $message in dataset('FacebookMessages') +where $user.nested.id /*+ indexnl */ = $message.nested.author-id-copy +and $user.nested.id >= 11000 and $user.nested.id <= 12000 +return { + "fbu-ID": $user.nested.id, + "fbm-auth-ID": $message.nested.author-id, + "uname": $user.nested.name, + "message": $message.nested.message +} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_02.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_02.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_02.aql new file mode 100644 index 0000000..bf3fdb3 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_02.aql @@ -0,0 +1,64 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Equi joins two datasets, closed DBLP and open CSX, based on their title. + * DBLP has a secondary btree index on title, and given the 'indexnl' hint + * we expect the join to be transformed into an indexed nested-loop join. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +create type DBLPTypetmp as closed { + id: int32, + dblpid: string, + title: string, + authors: string, + misc: string +} + +create type CSXTypetmp as open { + id: int32, + csxid: string, + authors: string, + misc: string +} + +create type DBLPType as closed { + nested : DBLPTypetmp +} + +create type CSXType as closed { + nested : CSXTypetmp +} + +create dataset DBLP(DBLPType) primary key nested.id; + +create dataset CSX(CSXType) primary key nested.id; + +create index title_index on CSX(nested.title: string?) enforced; + +write output to asterix_nc1:"rttest/btree-index-join_title-secondary-equi-join_02.adm"; + +for $a in dataset('DBLP') +for $b in dataset('CSX') +where $a.nested.title /*+ indexnl */ = $b.nested.title +return {"arec": $a, "brec": $b} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_03.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_03.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_03.aql new file mode 100644 index 0000000..6447343 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_03.aql @@ -0,0 +1,65 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Equi joins two open datasets, DBLP and CSX, based on their title. + * DBLP has a secondary btree index on title, and given the 'indexnl' hint + * we expect the join to be transformed into an indexed nested-loop join. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +create type DBLPTypetmp as open { + id: int32, + dblpid: string, + authors: string, + misc: string +} + +create type CSXTypetmp as open { + id: int32, + csxid: string, + authors: string, + misc: string +} + +create type DBLPType as closed { + nested : DBLPTypetmp +} + +create type CSXType as closed { + nested : CSXTypetmp +} + +create dataset DBLP(DBLPType) primary key nested.id; + +create dataset CSX(CSXType) primary key nested.id; + +create index title_index_DBLP on DBLP(nested.title: string?) enforced; + +create index title_index_CSX on CSX(nested.title: string?) enforced; + +write output to asterix_nc1:"rttest/btree-index-join_title-secondary-equi-join_03.adm"; + +for $a in dataset('DBLP') +for $b in dataset('CSX') +where $a.nested.title /*+ indexnl */ = $b.nested.title +return {"arec": $a, "brec": $b} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_04.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_04.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_04.aql new file mode 100644 index 0000000..5b77bb4 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_04.aql @@ -0,0 +1,50 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Self-joins dataset DBLP, based on it's title. + * DBLP has a secondary btree index on title, and given the 'indexnl' hint + * we expect the join to be transformed into an indexed nested-loop join. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +create type DBLPTypetmp as open { + id: int32, + dblpid: string, + authors: string, + misc: string +} + +create type DBLPType as open { + nested : DBLPTypetmp +} + +create dataset DBLP(DBLPType) primary key nested.id; + +create index title_index on DBLP(nested.title: string?) enforced; + +write output to asterix_nc1:"rttest/btree-index-join_title-secondary-equi-join_04.adm"; + +for $a in dataset('DBLP') +for $a2 in dataset('DBLP') +where $a.nested.title /*+ indexnl */ = $a2.nested.title +return {"arec": $a, "arec2": $a2} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_05.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_05.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_05.aql new file mode 100644 index 0000000..83ed7b0 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_05.aql @@ -0,0 +1,63 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Equi joins two open datasets, open DBLP and closed CSX, based on their title. + * DBLP has a secondary btree index on title, and given the 'indexnl' hint + * we *do not* expect the join to be transformed into an indexed nested-loop join. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +create type DBLPTypetmp as open { + id: int32, + dblpid: string, + authors: string, + misc: string +} + +create type CSXTypetmp as open { + id: int32, + csxid: string, + authors: string, + misc: string +} + +create type DBLPType as closed { + nested : DBLPTypetmp +} + +create type CSXType as closed { + nested : CSXTypetmp +} + +create dataset DBLP(DBLPType) primary key nested.id; + +create dataset CSX(CSXType) primary key nested.id; + +create index title_index on DBLP(nested.title: string?) enforced; + +write output to asterix_nc1:"rttest/btree-index-join_title-secondary-equi-join_05.adm"; + +for $a in dataset('DBLP') +for $b in dataset('CSX') +where $a.nested.title /*+ indexnl */ = $b.nested.title +return {"arec": $a, "brec": $b} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-33.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-33.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-33.aql new file mode 100644 index 0000000..a73ec15 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-33.aql @@ -0,0 +1,49 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : BTree Index verification test + * : This test is intended to verify that the secondary BTree index is NOT used + * : in the optimized query plan. + * Expected Result : Success + * Date : 13th Aug 2012 + */ + +// Please note this is a Negative test and the BTree index should NOT be used in the plan. + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +write output to asterix_nc1:"rttest/btree-index_btree-primary-39.adm"; + +create type TestTypetmp as open { + id : int32 +} + +create type TestType as open { + nested : TestTypetmp +} + +create dataset testdst(TestType) primary key nested.id; + +create index sec_Idx on testdst(nested.fname: string?,nested.lname: string?) enforced; + +for $emp in dataset('testdst') +where $emp.nested.fname > "Roger" +return $emp http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-34.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-34.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-34.aql new file mode 100644 index 0000000..351b6f3 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-34.aql @@ -0,0 +1,49 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : BTree Index verification test + * : This test is intended to verify that the secondary BTree index is NOT used + * : in the optimized query plan. + * Expected Result : Success + * Date : 13th Aug 2012 + */ + +// This is a Negative test - prefix search, BTree index should not be used in the plan. + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +write output to asterix_nc1:"rttest/btree-index_btree-secondary-32.adm"; + +create type TestTypetmp as open { + id : int32 +} + +create type TestType as open { + nested : TestTypetmp +} + +create dataset testdst(TestType) primary key nested.id; + +create index sec_Idx on testdst(nested.fname:string?,nested.lname:string?) enforced; + +for $emp in dataset('testdst') +where $emp.nested.fname >= "Susan" +return $emp http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-35.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-35.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-35.aql new file mode 100644 index 0000000..d20add6 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-35.aql @@ -0,0 +1,49 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : BTree Index verification (usage) test + * : This test is intended to verify that the secondary BTree index is used + * : in the optimized query plan. + * Expected Result : Success + * Date : 13th Aug 2012 + */ + +// Negative test - prefix search, BTree index should not be used. + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +write output to asterix_nc1:"rttest/btree-index_btree-secondary-33.adm"; + +create type TestTypetmp as open { + id : int32 +} + +create type TestType as open { + nested : TestTypetmp +} + +create dataset testdst(TestType) primary key nested.id; + +create index sec_Idx on testdst(nested.fname:string?,nested.lname:string?) enforced; + +for $emp in dataset('testdst') +where $emp.nested.fname < "Isa" +return $emp http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-36.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-36.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-36.aql new file mode 100644 index 0000000..447d73a --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-36.aql @@ -0,0 +1,49 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : BTree Index verification test + * : This test is intended to verify that the secondary BTree index is NOT used + * : in the optimized query plan. + * Expected Result : Success + * Date : 13th Aug 2012 + */ + +// Negative test - prefix search, BTree index should not be used in query plan + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +write output to asterix_nc1:"rttest/btree-index_btree-secondary-34.adm"; + +create type TestTypetmp as open { + id : int32 +} + +create type TestType as open { + nested : TestTypetmp +} + +create dataset testdst(TestType) primary key nested.id; + +create index sec_Idx on testdst(nested.fname:string?,nested.lname:string?) enforced; + +for $emp in dataset('testdst') +where $emp.nested.fname <= "Vanpatten" +return $emp http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-40.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-40.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-40.aql new file mode 100644 index 0000000..78e672d --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-40.aql @@ -0,0 +1,46 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : BTree Index verification test + * : This test is intended to verify that the secondary BTree index is used in the optimized query plan + * Expected Result : Success + * Date : 13th Aug 2012 + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +write output to asterix_nc1:"rttest/btree-index_btree-secondary-38.adm"; + +create type TestTypetmp as open { + id : int32 +} + +create type TestType as open { + nested : TestTypetmp +} + +create dataset testdst(TestType) primary key nested.id; + +create index sec_Idx on testdst(nested.fname:string?,nested.lname:string?) enforced; + +for $emp in dataset('testdst') +where $emp.nested.fname = "Young Seok" and $emp.nested.lname = "Kim" +return $emp http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-42.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-42.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-42.aql new file mode 100644 index 0000000..47c86fd --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-42.aql @@ -0,0 +1,47 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : BTree Index verification test + * : This test is intended to verify that the secondary BTree index is used + * : in the optimized query plan. + * Expected Result : Success + * Date : 13th Aug 2012 + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +write output to asterix_nc1:"rttest/btree-index_btree-secondary-40.adm"; + +create type TestTypetmp as open { + id : int32 +} + +create type TestType as open { + nested : TestTypetmp +} + +create dataset testdst(TestType) primary key nested.id; + +create index sec_Idx on testdst(nested.fname:string?,nested.lname:string?) enforced; + +for $emp in dataset('testdst') +where $emp.nested.fname > "Alex" and $emp.nested.lname < "Zach" +return $emp http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-43.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-43.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-43.aql new file mode 100644 index 0000000..8b0d920 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-43.aql @@ -0,0 +1,47 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : BTree Index verification test + * : This test is intended to verify that the secondary BTree index is NOT used + * : in the optimized query plan. + * Expected Result : Success + * Date : 13th Aug 2012 + */ + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +write output to asterix_nc1:"rttest/btree-index_btree-secondary-41.adm"; + +create type TestTypetmp as open { + id : int32 +} + +create type TestType as open { + nested : TestTypetmp +} + +create dataset testdst(TestType) primary key nested.id; + +create index sec_Idx on testdst(nested.fname:string?,nested.lname:string?) enforced; + +for $emp in dataset('testdst') +where $emp.nested.fname > "Allan" and $emp.nested.lname < "Zubi" +return $emp http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c4dbb614/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-44.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-44.aql b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-44.aql new file mode 100644 index 0000000..c5a2ed6 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-44.aql @@ -0,0 +1,49 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : BTree Index verification test + * : This test is intended to verify that the secondary BTree index is NOT used + * : in the optimized query plan. + * Expected Result : Success + * Date : 13th Aug 2012 + */ + +// Negative test - prefix search + +drop dataverse test if exists; +create dataverse test; +use dataverse test; + +write output to asterix_nc1:"rttest/btree-index_btree-secondary-42.adm"; + +create type TestTypetmp as open { + id : int32 +} + +create type TestType as open { + nested : TestTypetmp +} + +create dataset testdst(TestType) primary key nested.id; + +create index sec_Idx on testdst(nested.fname:string?,nested.lname:string?) enforced; + +for $emp in dataset('testdst') +where $emp.nested.fname > "Allan" and $emp.nested.lname = "Xu" +return $emp