http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1_ps.sqlpp new file mode 100644 index 0000000..5b88828 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1_ps.sqlpp @@ -0,0 +1,74 @@ +/* + * 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 btree index in index subtree. + * Issue : 730, 741 + * Expected Res : Success + * Date : 8th May 2014 + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.TwitterUserType as + closed { + `screen-name` : string, + lang : string, + `friends-count` : integer, + `statuses-count` : integer, + name : string, + `followers-count` : integer +}; + +create type test.TweetMessageNestedType as +{ + tweetid : bigint, + user : TwitterUserType, + `sender-location` : point, + `send-time` : datetime, + `referred-topics` : {{string}}, + `message-text` : string, + countA : integer +}; + +create type test.TweetMessageType as +{ + nested : TweetMessageNestedType +}; + +create dataset TweetMessages(TweetMessageType) primary key nested.tweetid; + +create index msgCountBIx on TweetMessages (nested.countB:integer?) type btree enforced; + +write output to asterix_nc1:"rttest/btree-index-join_leftouterjoin-probe-pidx-with-join-btree-sidx_01.adm"; +set `compiler.sort.parallel` "true"; + +select element {'tweetid1':t1.nested.tweetid,'count1':t1.nested.countA,'t2info':( + select element {'tweetid2':t2.nested.tweetid,'count2':t2.nested.countB} + from TweetMessages as t2 + where (t1.nested.countA /*+ indexnl */ = t2.nested.countB) + order by t2.nested.tweetid + )} +from TweetMessages as t1 +where (t1.nested.tweetid < test.bigint('10')) +order by t1.nested.tweetid +;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2_ps.sqlpp new file mode 100644 index 0000000..bb50b07 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2_ps.sqlpp @@ -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 btree index in index subtree. + * Issue : 730, 741 + * Expected Res : Success + * Date : 8th May 2014 + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.TwitterUserType as + closed { + `screen-name` : string, + lang : string, + `friends-count` : integer, + `statuses-count` : integer, + name : string, + `followers-count` : integer +}; + +create type test.TweetMessageNestedType as +{ + tweetid : bigint, + user : TwitterUserType, + `sender-location` : point, + `send-time` : datetime, + `referred-topics` : {{string}}, + `message-text` : string +}; + +create type test.TweetMessageType as +{ + nested : TweetMessageNestedType +}; + +create dataset TweetMessages(TweetMessageType) primary key nested.tweetid; + +create index msgCountAIx on TweetMessages (nested.countA:integer?) type btree enforced; + +create index msgCountBIx on TweetMessages (nested.countB:integer?) type btree enforced; + +write output to asterix_nc1:"rttest/btree-index-join_leftouterjoin-probe-pidx-with-join-btree-sidx_01.adm"; +set `compiler.sort.parallel` "true"; + +select element {'tweetid1':t1.nested.tweetid,'count1':t1.nested.countA,'t2info':( + select element {'tweetid2':t2.nested.tweetid,'count2':t2.nested.countB} + from TweetMessages as t2 + where (t1.nested.countA /*+ indexnl */ = t2.nested.countB) + order by t2.nested.tweetid + )} +from TweetMessages as t1 +where (t1.nested.tweetid < test.bigint('10')) +order by t1.nested.tweetid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1_ps.sqlpp new file mode 100644 index 0000000..dbf7c40 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1_ps.sqlpp @@ -0,0 +1,74 @@ +/* + * 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 btree index in index subtree. + * Issue : 730, 741 + * Expected Res : Success + * Date : 8th May 2014 + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.TwitterUserType as + closed { + `screen-name` : string, + lang : string, + `friends-count` : integer, + `statuses-count` : integer, + name : string, + `followers-count` : integer +}; + +create type test.TweetMessageNestedType as +{ + tweetid : bigint, + user : TwitterUserType, + `sender-location` : point, + `send-time` : datetime, + `referred-topics` : {{string}}, + `message-text` : string, + countA : integer +}; + +create type test.TweetMessageType as +{ + nested : TweetMessageNestedType +}; + +create dataset TweetMessages(TweetMessageType) primary key nested.tweetid; + +create index msgCountBIx on TweetMessages (nested.countB:integer?) type btree enforced; + +write output to asterix_nc1:"rttest/btree-index-join_leftouterjoin-probe-pidx-with-join-btree-sidx_02.adm"; +set `compiler.sort.parallel` "true"; + +select element {'tweetid1':t1.nested.tweetid,'count1':t1.nested.countA,'t2info':( + select element {'tweetid2':t2.nested.tweetid,'count2':t2.nested.countB} + from TweetMessages as t2 + where ((t1.nested.countA /*+ indexnl */ = t2.nested.countB) and (t1.nested.tweetid != t2.nested.tweetid)) + order by t2.nested.tweetid + )} +from TweetMessages as t1 +where (t1.nested.tweetid < test.bigint('10')) +order by t1.nested.tweetid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2_ps.sqlpp new file mode 100644 index 0000000..df41b65 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2_ps.sqlpp @@ -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 btree index in index subtree. + * Issue : 730, 741 + * Expected Res : Success + * Date : 8th May 2014 + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.TwitterUserType as + closed { + `screen-name` : string, + lang : string, + `friends-count` : integer, + `statuses-count` : integer, + name : string, + `followers-count` : integer +}; + +create type test.TweetMessageNestedType as +{ + tweetid : bigint, + user : TwitterUserType, + `sender-location` : point, + `send-time` : datetime, + `referred-topics` : {{string}}, + `message-text` : string +}; + +create type test.TweetMessageType as +{ + nested : TweetMessageNestedType +}; + +create dataset TweetMessages(TweetMessageType) primary key nested.tweetid; + +create index msgCountAIx on TweetMessages (nested.countA:integer?) type btree enforced; + +create index msgCountBIx on TweetMessages (nested.countB:integer?) type btree enforced; + +write output to asterix_nc1:"rttest/btree-index-join_leftouterjoin-probe-pidx-with-join-btree-sidx_02.adm"; +set `compiler.sort.parallel` "true"; + +select element {'tweetid1':t1.nested.tweetid,'count1':t1.nested.countA,'t2info':( + select element {'tweetid2':t2.nested.tweetid,'count2':t2.nested.countB} + from TweetMessages as t2 + where ((t1.nested.countA /*+ indexnl */ = t2.nested.countB) and (t1.nested.tweetid != t2.nested.tweetid)) + order by t2.nested.tweetid + )} +from TweetMessages as t1 +where (t1.nested.tweetid < test.bigint('10')) +order by t1.nested.tweetid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/ngram-contains-panic_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/ngram-contains-panic_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/ngram-contains-panic_ps.sqlpp new file mode 100644 index 0000000..1d22a19 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/ngram-contains-panic_ps.sqlpp @@ -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 : Tests whether an ngram_index is applied to optimize a selection query using the contains function. + * The index should *not* be applied (see below). + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.DBLPTypetmp as +{ + id : integer, + dblpid : string, + authors : string, + misc : string +}; + +create type test.DBLPType as + closed { + nested : DBLPTypetmp +}; + +create dataset DBLP(DBLPType) primary key nested.id; + +create index ngram_index on DBLP (nested.title:string?) type ngram (3) enforced; + +write output to asterix_nc1:"rttest/inverted-index-basic_ngram-contains-panic.adm"; +set `compiler.sort.parallel` "true"; + +select element o +from DBLP as o +where test.contains(o.nested.title,'Mu') +order by o.nested.id +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/ngram-contains_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/ngram-contains_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/ngram-contains_ps.sqlpp new file mode 100644 index 0000000..7e98edf --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/ngram-contains_ps.sqlpp @@ -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 : Tests whether an ngram_index is applied to optimize a selection query using the contains function. + * The index should be applied. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.DBLPTypetmp as +{ + id : integer, + dblpid : string, + authors : string, + misc : string +}; + +create type test.DBLPType as + closed { + nested : DBLPTypetmp +}; + +create dataset DBLP(DBLPType) primary key nested.id; + +create index ngram_index on DBLP (nested.title:string?) type ngram (3) enforced; + +write output to asterix_nc1:"rttest/inverted-index-basic_ngram-contains.adm"; +set `compiler.sort.parallel` "true"; + +select element o +from DBLP as o +where test.contains(o.nested.title,'Multimedia') +order by o.nested.id +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/word-contains_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/word-contains_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/word-contains_ps.sqlpp new file mode 100644 index 0000000..b6407d5 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/word-contains_ps.sqlpp @@ -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 : Tests whether a keyword index is applied to optimize a selection query using the contains function. + * The index should *not* be applied (see below). + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.DBLPTypetmp as +{ + id : integer, + dblpid : string, + authors : string, + misc : string +}; + +create type test.DBLPType as + closed { + nested : DBLPTypetmp +}; + +create dataset DBLP(DBLPType) primary key nested.id; + +create index keyword_index on DBLP (nested.title:string?) type keyword enforced; + +write output to asterix_nc1:"rttest/inverted-index-basic_word-contains.adm"; +set `compiler.sort.parallel` "true"; + +select element o +from DBLP as o +where test.contains(o.nested.title,'Multimedia') +order by o.nested.id +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01_ps.sqlpp new file mode 100644 index 0000000..0a55178 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01_ps.sqlpp @@ -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 test; + + +create type test.TwitterUserType as + closed { + `screen-name` : string, + lang : string, + `friends-count` : integer, + `statuses-count` : integer, + name : string, + `followers-count` : integer +}; + +create type test.TweetMessageNestedType as +{ + tweetid : bigint, + user : TwitterUserType, + `sender-location` : point, + `send-time` : datetime, + `referred-topics` : {{string}}, + countA : integer, + countB : integer +}; + +create type test.TweetMessageType as +{ + nested : TweetMessageNestedType +}; + +create dataset TweetMessages(TweetMessageType) primary key nested.tweetid; + +create index msgNgramIx on TweetMessages (nested.`message-text`:string?) type ngram (3) enforced; + +write output to asterix_nc1:"rttest/inverted-index-join_leftouterjoin-probe-pidx-with-join-edit-distance-check_idx_01.adm"; +set `compiler.sort.parallel` "true"; + +select element {'tweet':{'id':t1.nested.tweetid,'topics':t1.nested.`message-text`},'similar-tweets':( + select element {'id':t2.nested.tweetid,'topics':t2.nested.`message-text`} + from TweetMessages as t2 + with sim as test.`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 + )} +from TweetMessages as t1 +where (t1.nested.tweetid > test.bigint('240')) +order by t1.nested.tweetid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_01_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_01_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_01_ps.sqlpp new file mode 100644 index 0000000..fe6c65d --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_01_ps.sqlpp @@ -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 join query using the contains function. + * The index should be applied. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.DBLPType as +{ + id : integer, + dblpid : string, + misc : string +}; + +create type test.CSXType as + closed { + id : integer, + csxid : string, + title : string, + authors : string, + misc : string +}; + +create dataset DBLP(DBLPType) primary key id; + +create dataset CSX(CSXType) primary key id; + +create index ngram_index on DBLP (title:string?) type ngram (3) enforced; + +write output to asterix_nc1:"rttest/inverted-index-join_ngram-contains-01.adm"; +set `compiler.sort.parallel` "true"; + +select element {'title1':o1.title,'title2':o2.title} +from DBLP as o1, + CSX as o2 +where (test.contains(o1.title,o2.title) and (o1.id < o2.id)) +order by o1.id,o2.id +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_02_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_02_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_02_ps.sqlpp new file mode 100644 index 0000000..955f38c --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_02_ps.sqlpp @@ -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 join query using the contains function. + * The index should be applied. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.DBLPType as + closed { + id : integer, + dblpid : string, + title : string, + misc : string +}; + +create type test.CSXType as +{ + id : integer, + csxid : string, + authors : string, + misc : string +}; + +create dataset DBLP(DBLPType) primary key id; + +create dataset CSX(CSXType) primary key id; + +create index ngram_index on CSX (title:string?) type ngram (3) enforced; + +write output to asterix_nc1:"rttest/inverted-index-join_ngram-contains-02.adm"; +set `compiler.sort.parallel` "true"; + +select element {'title1':o1.title,'title2':o2.title} +from CSX as o1, + DBLP as o2 +where (test.contains(o1.title,o2.title) and (o1.id < o2.id)) +order by o1.id,o2.id +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_03_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_03_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_03_ps.sqlpp new file mode 100644 index 0000000..eb6fe2c --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_03_ps.sqlpp @@ -0,0 +1,51 @@ +/* + * 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 join query using the contains function. + * The index should be applied. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.DBLPType as +{ + id : integer, + dblpid : string, + authors : string, + misc : string +}; + +create dataset DBLP(DBLPType) primary key id; + +create index ngram_index on DBLP (title:string?) type ngram (3) enforced; + +write output to asterix_nc1:"rttest/inverted-index-join_ngram-contains-03.adm"; +set `compiler.sort.parallel` "true"; + +select element {'title1':o1.title,'title2':o2.title} +from DBLP as o1, + DBLP as o2 +where (test.contains(o1.title,o2.title) and (o1.id < o2.id)) +order by o1.id,o2.id +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_04_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_04_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_04_ps.sqlpp new file mode 100644 index 0000000..a924e19 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_04_ps.sqlpp @@ -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 : Tests whether an ngram_index is applied to optimize a join query using the contains function. + * The index should be applied. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.DBLPType as +{ + id : integer, + dblpid : string, + misc : string +}; + +create type test.CSXType as +{ + id : integer, + csxid : string, + authors : string, + misc : string +}; + +create dataset DBLP(DBLPType) primary key id; + +create dataset CSX(CSXType) primary key id; + +create index ngram_index_DBLP on DBLP (title:string?) type ngram (3) enforced; + +create index ngram_index_CSX on CSX (title:string?) type ngram (3) enforced; + +write output to asterix_nc1:"rttest/inverted-index-join_ngram-contains-04.adm"; +set `compiler.sort.parallel` "true"; + +select element {'title1':o1.title,'title2':o2.title} +from DBLP as o1, + CSX as o2 +where (test.contains(o1.title,o2.title) and (o1.id < o2.id)) +order by o1.id,o2.id +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01_ps.sqlpp new file mode 100644 index 0000000..19143d3 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01_ps.sqlpp @@ -0,0 +1,81 @@ +/* + * 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 test; + + +create type test.TwitterUserType as + closed { + `screen-name` : string, + lang : string, + `friends-count` : integer, + `statuses-count` : integer, + name : string, + `followers-count` : integer +}; + +create type test.TweetMessageNestedType as +{ + tweetid : bigint, + user : TwitterUserType, + `send-time` : datetime, + `referred-topics` : {{string}}, + `message-text` : string, + countA : integer, + countB : integer +}; + +create type test.TweetMessageType as +{ + nested : TweetMessageNestedType +}; + +create dataset TweetMessages(TweetMessageType) primary key nested.tweetid; + +create index twmSndLocIx on TweetMessages (nested.`sender-location`:point?) type rtree enforced; + +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_01.adm"; +set `compiler.sort.parallel` "true"; + +select element {'tweetid1':t1.nested.tweetid,'loc1':t1.nested.`sender-location`,'nearby-message':( + select element {'tweetid2':t2.nested.tweetid,'loc2':t2.nested.`sender-location`} + from TweetMessages as t2 + where test.`spatial-intersect`(t2.nested.`sender-location`,n) + order by t2.tweetid + )} +from TweetMessages as t1 +with n as test.`create-circle`(t1.nested.`sender-location`,0.5) +where (t1.nested.tweetid < test.bigint('10')) +order by t1.nested.tweetid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02_ps.sqlpp new file mode 100644 index 0000000..0471760 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02_ps.sqlpp @@ -0,0 +1,81 @@ +/* + * 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 test; + + +create type test.TwitterUserType as + closed { + `screen-name` : string, + lang : string, + `friends-count` : integer, + `statuses-count` : integer, + name : string, + `followers-count` : integer +}; + +create type test.TweetMessageNestedType as +{ + tweetid : bigint, + user : TwitterUserType, + `send-time` : datetime, + `referred-topics` : {{string}}, + `message-text` : string, + countA : integer, + countB : integer +}; + +create type test.TweetMessageType as +{ + nested : TweetMessageNestedType +}; + +create dataset TweetMessages(TweetMessageType) primary key nested.tweetid; + +create index twmSndLocIx on TweetMessages (nested.`sender-location`:point?) type rtree enforced; + +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"; +set `compiler.sort.parallel` "true"; + +select element {'tweetid1':t1.nested.tweetid,'loc1':t1.nested.`sender-location`,'nearby-message':( + select element {'tweetid2':t2.nested.tweetid,'loc2':t2.nested.`sender-location`} + from TweetMessages as t2 + where (test.`spatial-intersect`(t2.nested.`sender-location`,n) and (t1.nested.tweetid != t2.nested.tweetid)) + order by t2.nested.tweetid + )} +from TweetMessages as t1 +with n as test.`create-circle`(t1.nested.`sender-location`,0.5) +where (t1.nested.tweetid < test.bigint('10')) +order by t1.nested.tweetid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1_ps.sqlpp new file mode 100644 index 0000000..2514206 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1_ps.sqlpp @@ -0,0 +1,69 @@ +/* + * 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 btree index in index subtree. + * Issue : 730, 741 + * Expected Res : Success + * Date : 8th May 2014 + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.TwitterUserType as + closed { + `screen-name` : string, + lang : string, + `friends-count` : integer, + `statuses-count` : integer, + name : string, + `followers-count` : integer +}; + +create type test.TweetMessageType as +{ + tweetid : bigint, + user : TwitterUserType, + `sender-location` : point, + `send-time` : datetime, + `referred-topics` : {{string}}, + `message-text` : string, + countA : integer +}; + +create dataset TweetMessages(TweetMessageType) primary key tweetid; + +create index msgCountBIx on TweetMessages (countB:integer?) type btree enforced; + +write output to asterix_nc1:"rttest/btree-index-join_leftouterjoin-probe-pidx-with-join-btree-sidx_01.adm"; +set `compiler.sort.parallel` "true"; + +select element {'tweetid1':t1.tweetid,'count1':t1.countA,'t2info':( + select element {'tweetid2':t2.tweetid,'count2':t2.countB} + from TweetMessages as t2 + where (t1.countA /*+ indexnl */ = t2.countB) + order by t2.tweetid + )} +from TweetMessages as t1 +where (t1.tweetid < test.bigint('10')) +order by t1.tweetid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2_ps.sqlpp new file mode 100644 index 0000000..abe7d59 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2_ps.sqlpp @@ -0,0 +1,70 @@ +/* + * 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 btree index in index subtree. + * Issue : 730, 741 + * Expected Res : Success + * Date : 8th May 2014 + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.TwitterUserType as + closed { + `screen-name` : string, + lang : string, + `friends-count` : integer, + `statuses-count` : integer, + name : string, + `followers-count` : integer +}; + +create type test.TweetMessageType as +{ + tweetid : bigint, + user : TwitterUserType, + `sender-location` : point, + `send-time` : datetime, + `referred-topics` : {{string}}, + `message-text` : string +}; + +create dataset TweetMessages(TweetMessageType) primary key tweetid; + +create index msgCountAIx on TweetMessages (countA:integer?) type btree enforced; + +create index msgCountBIx on TweetMessages (countB:integer?) type btree enforced; + +write output to asterix_nc1:"rttest/btree-index-join_leftouterjoin-probe-pidx-with-join-btree-sidx_01.adm"; +set `compiler.sort.parallel` "true"; + +select element {'tweetid1':t1.tweetid,'count1':t1.countA,'t2info':( + select element {'tweetid2':t2.tweetid,'count2':t2.countB} + from TweetMessages as t2 + where (t1.countA /*+ indexnl */ = t2.countB) + order by t2.tweetid + )} +from TweetMessages as t1 +where (t1.tweetid < test.bigint('10')) +order by t1.tweetid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1_ps.sqlpp new file mode 100644 index 0000000..1f0c190 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1_ps.sqlpp @@ -0,0 +1,69 @@ +/* + * 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 btree index in index subtree. + * Issue : 730, 741 + * Expected Res : Success + * Date : 8th May 2014 + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.TwitterUserType as + closed { + `screen-name` : string, + lang : string, + `friends-count` : integer, + `statuses-count` : integer, + name : string, + `followers-count` : integer +}; + +create type test.TweetMessageType as +{ + tweetid : bigint, + user : TwitterUserType, + `sender-location` : point, + `send-time` : datetime, + `referred-topics` : {{string}}, + `message-text` : string, + countA : integer +}; + +create dataset TweetMessages(TweetMessageType) primary key tweetid; + +create index msgCountBIx on TweetMessages (countB:integer?) type btree enforced; + +write output to asterix_nc1:"rttest/btree-index-join_leftouterjoin-probe-pidx-with-join-btree-sidx_02.adm"; +set `compiler.sort.parallel` "true"; + +select element {'tweetid1':t1.tweetid,'count1':t1.countA,'t2info':( + select element {'tweetid2':t2.tweetid,'count2':t2.countB} + from TweetMessages as t2 + where ((t1.countA /*+ indexnl */ = t2.countB) and (t1.tweetid != t2.tweetid)) + order by t2.tweetid + )} +from TweetMessages as t1 +where (t1.tweetid < test.bigint('10')) +order by t1.tweetid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2_ps.sqlpp new file mode 100644 index 0000000..fd29e2e --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2_ps.sqlpp @@ -0,0 +1,70 @@ +/* + * 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 btree index in index subtree. + * Issue : 730, 741 + * Expected Res : Success + * Date : 8th May 2014 + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.TwitterUserType as + closed { + `screen-name` : string, + lang : string, + `friends-count` : integer, + `statuses-count` : integer, + name : string, + `followers-count` : integer +}; + +create type test.TweetMessageType as +{ + tweetid : bigint, + user : TwitterUserType, + `sender-location` : point, + `send-time` : datetime, + `referred-topics` : {{string}}, + `message-text` : string +}; + +create dataset TweetMessages(TweetMessageType) primary key tweetid; + +create index msgCountAIx on TweetMessages (countA:integer?) type btree enforced; + +create index msgCountBIx on TweetMessages (countB:integer?) type btree enforced; + +write output to asterix_nc1:"rttest/btree-index-join_leftouterjoin-probe-pidx-with-join-btree-sidx_02.adm"; +set `compiler.sort.parallel` "true"; + +select element {'tweetid1':t1.tweetid,'count1':t1.countA,'t2info':( + select element {'tweetid2':t2.tweetid,'count2':t2.countB} + from TweetMessages as t2 + where ((t1.countA /*+ indexnl */ = t2.countB) and (t1.tweetid != t2.tweetid)) + order by t2.tweetid + )} +from TweetMessages as t1 +where (t1.tweetid < test.bigint('10')) +order by t1.tweetid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-basic/ngram-contains-panic_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-basic/ngram-contains-panic_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-basic/ngram-contains-panic_ps.sqlpp new file mode 100644 index 0000000..dbb430a --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-basic/ngram-contains-panic_ps.sqlpp @@ -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 : Tests whether an ngram_index is applied to optimize a selection query using the contains function. + * The index should *not* be applied (see below). + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.DBLPType as +{ + id : integer, + dblpid : string, + authors : string, + misc : string +}; + +create dataset DBLP(DBLPType) primary key id; + +create index ngram_index on DBLP (title:string?) type ngram (3) enforced; + +write output to asterix_nc1:"rttest/inverted-index-basic_ngram-contains-panic.adm"; +set `compiler.sort.parallel` "true"; + +select element o +from DBLP as o +where test.contains(o.title,'Mu') +order by o.id +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-basic/ngram-contains_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-basic/ngram-contains_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-basic/ngram-contains_ps.sqlpp new file mode 100644 index 0000000..060465d --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-basic/ngram-contains_ps.sqlpp @@ -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 : Tests whether an ngram_index is applied to optimize a selection query using the contains function. + * The index should be applied. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.DBLPType as +{ + id : integer, + dblpid : string, + authors : string, + misc : string +}; + +create dataset DBLP(DBLPType) primary key id; + +create index ngram_index on DBLP (title:string?) type ngram (3) enforced; + +write output to asterix_nc1:"rttest/inverted-index-basic_ngram-contains.adm"; +set `compiler.sort.parallel` "true"; + +select element o +from DBLP as o +where test.contains(o.title,'Multimedia') +order by o.id +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-basic/word-contains_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-basic/word-contains_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-basic/word-contains_ps.sqlpp new file mode 100644 index 0000000..9caf6bd --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-basic/word-contains_ps.sqlpp @@ -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 : Tests whether a keyword index is applied to optimize a selection query using the contains function. + * The index should *not* be applied (see below). + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.DBLPType as +{ + id : integer, + dblpid : string, + authors : string, + misc : string +}; + +create dataset DBLP(DBLPType) primary key id; + +create index keyword_index on DBLP (title:string?) type keyword enforced; + +write output to asterix_nc1:"rttest/inverted-index-basic_word-contains.adm"; +set `compiler.sort.parallel` "true"; + +select element o +from DBLP as o +where test.contains(o.title,'Multimedia') +order by o.id +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01_ps.sqlpp new file mode 100644 index 0000000..2d9b15b --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01_ps.sqlpp @@ -0,0 +1,70 @@ +/* + * 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 test; + + +create type test.TwitterUserType as + closed { + `screen-name` : string, + lang : string, + `friends-count` : integer, + `statuses-count` : integer, + name : string, + `followers-count` : integer +}; + +create type test.TweetMessageType as +{ + tweetid : bigint, + user : TwitterUserType, + `sender-location` : point, + `send-time` : datetime, + `referred-topics` : {{string}}, + countA : integer, + countB : integer +}; + +create dataset TweetMessages(TweetMessageType) primary key tweetid; + +create index msgNgramIx on TweetMessages (`message-text`:string?) type ngram (3) enforced; + +write output to asterix_nc1:"rttest/inverted-index-join_leftouterjoin-probe-pidx-with-join-edit-distance-check_idx_01.adm"; +set `compiler.sort.parallel` "true"; + +select element {'tweet':{'id':t1.tweetid,'topics':t1.`message-text`},'similar-tweets':( + select element {'id':t2.tweetid,'topics':t2.`message-text`} + from TweetMessages as t2 + with sim as test.`edit-distance-check`(t1.`message-text`,t2.`message-text`,7) + where (sim[0] and (t2.tweetid != t1.tweetid)) + order by t2.tweetid + )} +from TweetMessages as t1 +where (t1.tweetid > test.bigint('240')) +order by t1.tweetid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_01_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_01_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_01_ps.sqlpp new file mode 100644 index 0000000..fe6c65d --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_01_ps.sqlpp @@ -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 join query using the contains function. + * The index should be applied. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.DBLPType as +{ + id : integer, + dblpid : string, + misc : string +}; + +create type test.CSXType as + closed { + id : integer, + csxid : string, + title : string, + authors : string, + misc : string +}; + +create dataset DBLP(DBLPType) primary key id; + +create dataset CSX(CSXType) primary key id; + +create index ngram_index on DBLP (title:string?) type ngram (3) enforced; + +write output to asterix_nc1:"rttest/inverted-index-join_ngram-contains-01.adm"; +set `compiler.sort.parallel` "true"; + +select element {'title1':o1.title,'title2':o2.title} +from DBLP as o1, + CSX as o2 +where (test.contains(o1.title,o2.title) and (o1.id < o2.id)) +order by o1.id,o2.id +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_02_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_02_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_02_ps.sqlpp new file mode 100644 index 0000000..1cb8237 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_02_ps.sqlpp @@ -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 join query using the contains function. + * The index should be applied. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.DBLPType as + closed { + id : integer, + dblpid : string, + title : string, + misc : string +}; + +create type test.CSXType as +{ + id : integer, + csxid : string, + authors : string, + misc : string +}; + +create dataset DBLP(DBLPType) primary key id; + +create dataset CSX(CSXType) primary key id; + +create index ngram_index on CSX (title:string?) type ngram (3) enforced; + +write output to asterix_nc1:"rttest/inverted-index-join_ngram-contains-02.adm"; +set `compiler.sort.parallel` "true"; + +select element {'title1':o1.title,'title2':o2.title} +from DBLP as o1, + CSX as o2 +where (test.contains(o1.title,o2.title) and (o1.id < o2.id)) +order by o1.id,o2.id +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_03_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_03_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_03_ps.sqlpp new file mode 100644 index 0000000..eb6fe2c --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_03_ps.sqlpp @@ -0,0 +1,51 @@ +/* + * 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 join query using the contains function. + * The index should be applied. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.DBLPType as +{ + id : integer, + dblpid : string, + authors : string, + misc : string +}; + +create dataset DBLP(DBLPType) primary key id; + +create index ngram_index on DBLP (title:string?) type ngram (3) enforced; + +write output to asterix_nc1:"rttest/inverted-index-join_ngram-contains-03.adm"; +set `compiler.sort.parallel` "true"; + +select element {'title1':o1.title,'title2':o2.title} +from DBLP as o1, + DBLP as o2 +where (test.contains(o1.title,o2.title) and (o1.id < o2.id)) +order by o1.id,o2.id +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_04_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_04_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_04_ps.sqlpp new file mode 100644 index 0000000..a924e19 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_04_ps.sqlpp @@ -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 : Tests whether an ngram_index is applied to optimize a join query using the contains function. + * The index should be applied. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.DBLPType as +{ + id : integer, + dblpid : string, + misc : string +}; + +create type test.CSXType as +{ + id : integer, + csxid : string, + authors : string, + misc : string +}; + +create dataset DBLP(DBLPType) primary key id; + +create dataset CSX(CSXType) primary key id; + +create index ngram_index_DBLP on DBLP (title:string?) type ngram (3) enforced; + +create index ngram_index_CSX on CSX (title:string?) type ngram (3) enforced; + +write output to asterix_nc1:"rttest/inverted-index-join_ngram-contains-04.adm"; +set `compiler.sort.parallel` "true"; + +select element {'title1':o1.title,'title2':o2.title} +from DBLP as o1, + CSX as o2 +where (test.contains(o1.title,o2.title) and (o1.id < o2.id)) +order by o1.id,o2.id +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01_ps.sqlpp new file mode 100644 index 0000000..3fd898e --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01_ps.sqlpp @@ -0,0 +1,76 @@ +/* + * 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 test; + + +create type test.TwitterUserType as + closed { + `screen-name` : string, + lang : string, + `friends-count` : integer, + `statuses-count` : integer, + name : string, + `followers-count` : integer +}; + +create type test.TweetMessageType as +{ + tweetid : bigint, + user : TwitterUserType, + `send-time` : datetime, + `referred-topics` : {{string}}, + `message-text` : string, + countA : integer, + countB : integer +}; + +create dataset TweetMessages(TweetMessageType) primary key tweetid; + +create index twmSndLocIx on TweetMessages (`sender-location`:point?) type rtree enforced; + +create index msgCountAIx on TweetMessages (countA) type btree; + +create index msgCountBIx on TweetMessages (countB) type btree; + +create index msgTextIx on TweetMessages (`message-text`) type keyword; + +write output to asterix_nc1:"rttest/rtree-index-join_leftouterjoin-probe-pidx-with-join-rtree-sidx_01.adm"; +set `compiler.sort.parallel` "true"; + +select element {'tweetid1':t1.tweetid,'loc1':t1.`sender-location`,'nearby-message':( + select element {'tweetid2':t2.tweetid,'loc2':t2.`sender-location`} + from TweetMessages as t2 + where test.`spatial-intersect`(t2.`sender-location`,n) + order by t2.tweetid + )} +from TweetMessages as t1 +with n as test.`create-circle`(t1.`sender-location`,0.5) +where (t1.tweetid < test.bigint('10')) +order by t1.tweetid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02_ps.sqlpp new file mode 100644 index 0000000..c59dba4 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02_ps.sqlpp @@ -0,0 +1,76 @@ +/* + * 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 test; + + +create type test.TwitterUserType as + closed { + `screen-name` : string, + lang : string, + `friends-count` : integer, + `statuses-count` : integer, + name : string, + `followers-count` : integer +}; + +create type test.TweetMessageType as +{ + tweetid : bigint, + user : TwitterUserType, + `send-time` : datetime, + `referred-topics` : {{string}}, + `message-text` : string, + countA : integer, + countB : integer +}; + +create dataset TweetMessages(TweetMessageType) primary key tweetid; + +create index twmSndLocIx on TweetMessages (`sender-location`:point?) type rtree enforced; + +create index msgCountAIx on TweetMessages (countA) type btree; + +create index msgCountBIx on TweetMessages (countB) type btree; + +create index msgTextIx on TweetMessages (`message-text`) type keyword; + +write output to asterix_nc1:"rttest/rtree-index-join_leftouterjoin-probe-pidx-with-join-rtree-sidx_02.adm"; +set `compiler.sort.parallel` "true"; + +select element {'tweetid1':t1.tweetid,'loc1':t1.`sender-location`,'nearby-message':( + select element {'tweetid2':t2.tweetid,'loc2':t2.`sender-location`} + from TweetMessages as t2 + where (test.`spatial-intersect`(t2.`sender-location`,n) and (t1.tweetid != t2.tweetid)) + order by t2.tweetid + )} +from TweetMessages as t1 +with n as test.`create-circle`(t1.`sender-location`,0.5) +where (t1.tweetid < test.bigint('10')) +order by t1.tweetid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-05_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-05_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-05_ps.sqlpp new file mode 100644 index 0000000..8c3f6dd --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-05_ps.sqlpp @@ -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 : No index join because there's no hint and the probe type is unknown. p_sort enabled. + * Expected Res : Success + * Date : 20 Jun 2017 + */ +drop dataverse test if exists; +create dataverse test; +use test; + +write output to asterix_nc1:"rttest/btree-equi-join-non-enforced_btree-equi-join-non-enforced-05.adm"; + +create type TestOpenType as open { + c_id: int64 +}; + +create dataset TestOpen1(TestOpenType) primary key c_id; +create dataset TestOpen2(TestOpenType) primary key c_id; +create index idx_t2_s on TestOpen2(c_s:string); +create index idx_t2_i64 on TestOpen2(c_i64:int64); +create index idx_t2_i8 on TestOpen2(c_i8:int8); +create index idx_t2_d on TestOpen2(c_d:double); + +set `compiler.sort.parallel` "true"; + +select t1.c_x as c1, t2.c_x as c2 +from TestOpen1 as t1, TestOpen2 as t2 +where t1.c_s = t2.c_s +order by t1.c_x, t2.c_x; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-06_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-06_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-06_ps.sqlpp new file mode 100644 index 0000000..7ba14c9 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-06_ps.sqlpp @@ -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 : No index join because the probe type is unknown. p_sort enabled. + * Expected Res : Success + * Date : 20 Jun 2017 + */ +drop dataverse test if exists; +create dataverse test; +use test; + +write output to asterix_nc1:"rttest/btree-equi-join-non-enforced_btree-equi-join-non-enforced-06.adm"; + +create type TestOpenType as open { + c_id: int64 +}; + +create dataset TestOpen1(TestOpenType) primary key c_id; +create dataset TestOpen2(TestOpenType) primary key c_id; +create index idx_t2_s on TestOpen2(c_s:string); +create index idx_t2_i64 on TestOpen2(c_i64:int64); +create index idx_t2_i8 on TestOpen2(c_i8:int8); +create index idx_t2_d on TestOpen2(c_d:double); + +set `compiler.sort.parallel` "true"; + +select t1.c_x as c1, t2.c_x as c2 +from TestOpen1 as t1, TestOpen2 as t2 +where t1.c_s /*+ indexnl */ = t2.c_s +order by t1.c_x, t2.c_x; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-07_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-07_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-07_ps.sqlpp new file mode 100644 index 0000000..403a75e --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-07_ps.sqlpp @@ -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 : No index join because there's no hint. p_sort enabled. + * Expected Res : Success + * Date : 20 Jun 2017 + */ +drop dataverse test if exists; +create dataverse test; +use test; + +write output to asterix_nc1:"rttest/btree-equi-join-non-enforced_btree-equi-join-non-enforced-07.adm"; + +create type TestOpenType as open { + c_id: int64 +}; + +create dataset TestOpen1(TestOpenType) primary key c_id; +create dataset TestOpen2(TestOpenType) primary key c_id; +create index idx_t2_s on TestOpen2(c_s:string); +create index idx_t2_i64 on TestOpen2(c_i64:int64); +create index idx_t2_i8 on TestOpen2(c_i8:int8); +create index idx_t2_d on TestOpen2(c_d:double); + +set `compiler.sort.parallel` "true"; + + +select t1.c_x as c1, t2.c_x as c2 +from TestOpen1 as t1, TestOpen2 as t2 +where to_string(t1.c_s) = t2.c_s +order by t1.c_x, t2.c_x; \ No newline at end of file
