http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/ngram-contains-panic_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/ngram-contains-panic_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/ngram-contains-panic_ps.sqlpp new file mode 100644 index 0000000..0384667 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/ngram-contains-panic_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 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 + closed { + id : integer, + dblpid : string, + title : string, + authors : string, + misc : string +}; + +create dataset DBLP(DBLPType) primary key id; + +create index ngram_index on DBLP (title) type ngram (3); + +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/inverted-index-basic/ngram-contains_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/ngram-contains_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/ngram-contains_ps.sqlpp new file mode 100644 index 0000000..578ef74 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/ngram-contains_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 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 + closed { + id : integer, + dblpid : string, + title : string, + authors : string, + misc : string +}; + +create dataset DBLP(DBLPType) primary key id; + +create index ngram_index on DBLP (title) type ngram (3); + +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/inverted-index-basic/olist-edit-distance-check-panic_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/olist-edit-distance-check-panic_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/olist-edit-distance-check-panic_ps.sqlpp new file mode 100644 index 0000000..eb17ed4 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/olist-edit-distance-check-panic_ps.sqlpp @@ -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 a keyword index is applied to optimize a selection query using the edit-distance-check function on lists. + * The index should *not* be applied (see below). + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.AddressType as + closed { + number : integer, + street : string, + city : string +}; + +create type test.CustomerType as + closed { + cid : integer, + name : string, + age : integer?, + address : AddressType?, + interests : [string], + children : [{ + name : string, + age : integer? + } +] +}; + +create dataset Customers(CustomerType) primary key cid; + +create index interests_index on Customers (interests) type keyword; + +write output to asterix_nc1:"rttest/inverted-index-basic_olist-edit-distance-check-panic.adm"; +set `compiler.sort.parallel` "true"; + +select element c +from Customers as c +where test.`edit-distance-check`(c.interests,['computers','wine','walking'],3)[0] +order by c.cid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/olist-edit-distance-check_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/olist-edit-distance-check_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/olist-edit-distance-check_ps.sqlpp new file mode 100644 index 0000000..412ffdb --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/olist-edit-distance-check_ps.sqlpp @@ -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 a keyword index is applied to optimize a selection query using the edit-distance-check function on lists. + * The index should be applied. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.AddressType as + closed { + number : integer, + street : string, + city : string +}; + +create type test.CustomerType as + closed { + cid : integer, + name : string, + age : integer?, + address : AddressType?, + interests : [string], + children : [{ + name : string, + age : integer? + } +] +}; + +create dataset Customers(CustomerType) primary key cid; + +create index interests_index on Customers (interests) type keyword; + +write output to asterix_nc1:"rttest/inverted-index-basic_olist-edit-distance-check.adm"; +set `compiler.sort.parallel` "true"; + +select element c +from Customers as c +where test.`edit-distance-check`(c.interests,['computers','wine','walking'],1)[0] +order by c.cid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/olist-edit-distance-panic_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/olist-edit-distance-panic_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/olist-edit-distance-panic_ps.sqlpp new file mode 100644 index 0000000..44f6164 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/olist-edit-distance-panic_ps.sqlpp @@ -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 a keyword index is applied to optimize a selection query using the edit-distance function on lists. + * The index should *not* be applied (see below). + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.AddressType as + closed { + number : integer, + street : string, + city : string +}; + +create type test.CustomerType as + closed { + cid : integer, + name : string, + age : integer?, + address : AddressType?, + interests : [string], + children : [{ + name : string, + age : integer? + } +] +}; + +create dataset Customers(CustomerType) primary key cid; + +create index interests_index on Customers (interests) type keyword; + +write output to asterix_nc1:"rttest/inverted-index-basic_olist-edit-distance-panic.adm"; +set `compiler.sort.parallel` "true"; + +select element c +from Customers as c +where (test.`edit-distance`(c.interests,['computers','wine','walking']) <= 3) +order by c.cid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/olist-edit-distance_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/olist-edit-distance_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/olist-edit-distance_ps.sqlpp new file mode 100644 index 0000000..cf8b4a7 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/olist-edit-distance_ps.sqlpp @@ -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 a keyword index is applied to optimize a selection query using the edit-distance function on lists. + * The index should be applied. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.AddressType as + closed { + number : integer, + street : string, + city : string +}; + +create type test.CustomerType as + closed { + cid : integer, + name : string, + age : integer?, + address : AddressType?, + interests : [string], + children : [{ + name : string, + age : integer? + } +] +}; + +create dataset Customers(CustomerType) primary key cid; + +create index interests_index on Customers (interests) type keyword; + +write output to asterix_nc1:"rttest/inverted-index-basic_olist-edit-distance.adm"; +set `compiler.sort.parallel` "true"; + +select element c +from Customers as c +where (test.`edit-distance`(c.interests,['computers','wine','walking']) <= 1) +order by c.cid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/olist-fuzzyeq-edit-distance_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/olist-fuzzyeq-edit-distance_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/olist-fuzzyeq-edit-distance_ps.sqlpp new file mode 100644 index 0000000..0b8ec46 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/olist-fuzzyeq-edit-distance_ps.sqlpp @@ -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 : Tests whether a keyword index is applied to optimize a selection query with ~= using edit-distance on lists. + * The index should be applied. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.AddressType as + closed { + number : integer, + street : string, + city : string +}; + +create type test.CustomerType as + closed { + cid : integer, + name : string, + age : integer?, + address : AddressType?, + interests : [string], + children : [{ + name : string, + age : integer? + } +] +}; + +create dataset Customers(CustomerType) primary key cid; + +create index interests_index on Customers (interests) type keyword; + +write output to asterix_nc1:"rttest/inverted-index-basic_olist-fuzzyeq-edit-distance.adm"; +set `simfunction` `edit-distance`; +set `simthreshold` `1`; +set `compiler.sort.parallel` "true"; + +select element c +from Customers as c +where (c.interests ~= ['computers','wine','walking']) +order by c.cid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/word-contains_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/word-contains_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/word-contains_ps.sqlpp new file mode 100644 index 0000000..27157ff --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-basic/word-contains_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 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 + closed { + id : integer, + dblpid : string, + title : string, + authors : string, + misc : string +}; + +create dataset DBLP(DBLPType) primary key id; + +create index keyword_index on DBLP (title) type keyword; + +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/inverted-index-complex/olist-edit-distance-check-let-panic_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-complex/olist-edit-distance-check-let-panic_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-complex/olist-edit-distance-check-let-panic_ps.sqlpp new file mode 100644 index 0000000..3b861b1 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-complex/olist-edit-distance-check-let-panic_ps.sqlpp @@ -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 : 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 test; + + +create type test.AddressType as + closed { + number : integer, + street : string, + city : string +}; + +create type test.CustomerType as + closed { + cid : integer, + name : string, + age : integer?, + address : AddressType?, + interests : [string], + children : [{ + name : string, + age : integer? + } +] +}; + +create dataset Customers(CustomerType) primary key cid; + +create index interests_index on Customers (interests) type keyword; + +write output to asterix_nc1:"rttest/inverted-index-complex_olist-edit-distance-check-let-panic.adm"; +set `compiler.sort.parallel` "true"; + +select element c +from Customers as c +with ed as test.`edit-distance-check`(c.interests,['computers','wine','walking'],3) +where ed[0] +order by c.cid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-complex/olist-edit-distance-check-let_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-complex/olist-edit-distance-check-let_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-complex/olist-edit-distance-check-let_ps.sqlpp new file mode 100644 index 0000000..f5d9888 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-complex/olist-edit-distance-check-let_ps.sqlpp @@ -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 : 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 test; + + +create type test.AddressType as + closed { + number : integer, + street : string, + city : string +}; + +create type test.CustomerType as + closed { + cid : integer, + name : string, + age : integer?, + address : AddressType?, + interests : [string], + children : [{ + name : string, + age : integer? + } +] +}; + +create dataset Customers(CustomerType) primary key cid; + +create index interests_index on Customers (interests) type keyword; + +write output to asterix_nc1:"rttest/inverted-index-complex_olist-edit-distance-check-let.adm"; +set `compiler.sort.parallel` "true"; + +select element c +from Customers as c +with ed as test.`edit-distance-check`(c.interests,['computers','wine','walking'],1) +where ed[0] +order by c.cid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/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/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01_ps.sqlpp new file mode 100644 index 0000000..2c5ac71 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_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.TweetMessageType as + closed { + tweetid : bigint, + user : TwitterUserType, + `sender-location` : point, + `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`) type rtree; + +create index msgCountAIx on TweetMessages (countA) type btree; + +create index msgCountBIx on TweetMessages (countB) type btree; + +create index msgKeywordIx on TweetMessages (`message-text`) type keyword; + +create index msgNgramIx on TweetMessages (`message-text`) type ngram (3); + +create index topicKeywordIx on TweetMessages (`referred-topics`) type keyword; + +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/inverted-index-join/leftouterjoin-probe-pidx-with-join-jaccard-check-idx_01_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-join/leftouterjoin-probe-pidx-with-join-jaccard-check-idx_01_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-join/leftouterjoin-probe-pidx-with-join-jaccard-check-idx_01_ps.sqlpp new file mode 100644 index 0000000..2348dd9 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-join/leftouterjoin-probe-pidx-with-join-jaccard-check-idx_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.TweetMessageType as + closed { + tweetid : bigint, + user : TwitterUserType, + `sender-location` : point, + `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`) type rtree; + +create index msgCountAIx on TweetMessages (countA) type btree; + +create index msgCountBIx on TweetMessages (countB) type btree; + +create index msgKeywordIx on TweetMessages (`message-text`) type keyword; + +create index msgNgramIx on TweetMessages (`message-text`) type ngram (3); + +create index topicKeywordIx on TweetMessages (`referred-topics`) type keyword; + +write output to asterix_nc1:"rttest/inverted-index-join_leftouterjoin-probe-pidx-with-join-jaccard-check_idx_01.adm"; +set `compiler.sort.parallel` "true"; + +select element {'tweet':{'id':t1.tweetid,'topics':t1.`referred-topics`},'similar-tweets':( + select element {'id':t2.tweetid,'topics':t2.`referred-topics`} + from TweetMessages as t2 + with sim as test.`similarity-jaccard-check`(t1.`referred-topics`,t2.`referred-topics`,0.500000f) + 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/inverted-index-join/ngram-contains_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-join/ngram-contains_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-join/ngram-contains_ps.sqlpp new file mode 100644 index 0000000..5d49319 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/inverted-index-join/ngram-contains_ps.sqlpp @@ -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 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, + authors : string, + misc : string +}; + +create dataset DBLP(DBLPType) primary key id; + +create index ngram_index on DBLP (title) type ngram (3); + +write output to asterix_nc1:"rttest/inverted-index-join_ngram-contains.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/loj-core_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/loj-core_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/loj-core_ps.sqlpp new file mode 100644 index 0000000..d8a4d6c --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/loj-core_ps.sqlpp @@ -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. + */ +drop dataverse test if exists; +create dataverse test; +use test; + +create type test.DBLPType as closed { + id : bigint, + dblpid : string, + title : string, + authors : string, + misc : string +}; + +create type test.CSXType as closed { + id : bigint, + csxid : string, + title : string, + authors : string, + misc : string +}; + +create dataset DBLP(DBLPType) primary key id; +create dataset CSX(CSXType) primary key id; + +set `compiler.sort.parallel` "true"; + +SELECT a.id AS aid, b.id AS bid +FROM DBLP AS a +LEFT OUTER UNNEST +( + SELECT VALUE b + FROM CSX AS b + WHERE a.authors = b.authors +) AS b +ORDER BY a.id; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/loj-sugar_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/loj-sugar_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/loj-sugar_ps.sqlpp new file mode 100644 index 0000000..ff38e33 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/loj-sugar_ps.sqlpp @@ -0,0 +1,48 @@ +/* + * 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. + */ +drop dataverse test if exists; +create dataverse test; +use test; + +create type test.DBLPType as closed { + id : bigint, + dblpid : string, + title : string, + authors : string, + misc : string +}; + +create type test.CSXType as closed { + id : bigint, + csxid : string, + title : string, + authors : string, + misc : string +}; + +create dataset DBLP(DBLPType) primary key id; +create dataset CSX(CSXType) primary key id; + +set `compiler.sort.parallel` "true"; + +SELECT a.id AS aid, b.id AS bid +FROM DBLP AS a +LEFT OUTER JOIN CSX AS b +ON a.authors = b.authors +ORDER BY a.id; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_ps.sqlpp new file mode 100644 index 0000000..bc0f28c --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_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 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 + closed { + tweetid : bigint, + user : TwitterUserType, + `sender-location` : point, + `send-time` : datetime, + `referred-topics` : {{string}}, + `message-text` : string, + countA : integer, + countB : integer +}; + +create type test.TweetMessageType as + closed { + nested : TweetMessageNestedType +}; + +create dataset TweetMessages(TweetMessageType) primary key nested.tweetid; + +create index msgCountBIx on TweetMessages (nested.countB) type btree; + +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-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_ps.sqlpp new file mode 100644 index 0000000..3deb837 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_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 + closed { + tweetid : bigint, + user : TwitterUserType, + `sender-location` : point, + `send-time` : datetime, + `referred-topics` : {{string}}, + `message-text` : string, + countA : integer, + countB : integer +}; + +create type test.TweetMessageType as + closed { + nested : TweetMessageNestedType +}; + +create dataset TweetMessages(TweetMessageType) primary key nested.tweetid; + +create index msgCountBIx on TweetMessages (nested.countB) type btree; + +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-index/inverted-index-basic/ngram-contains-panic_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/ngram-contains-panic_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/ngram-contains-panic_ps.sqlpp new file mode 100644 index 0000000..99a3497 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/ngram-contains-panic_ps.sqlpp @@ -0,0 +1,56 @@ +/* + * 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 + closed { + id : integer, + dblpid : string, + title : 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) type ngram (3); + +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-index/inverted-index-basic/ngram-contains_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/ngram-contains_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/ngram-contains_ps.sqlpp new file mode 100644 index 0000000..4f07d69 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/ngram-contains_ps.sqlpp @@ -0,0 +1,56 @@ +/* + * 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 + closed { + id : integer, + dblpid : string, + title : 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) type ngram (3); + +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-index/inverted-index-basic/olist-edit-distance-check-panic_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/olist-edit-distance-check-panic_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/olist-edit-distance-check-panic_ps.sqlpp new file mode 100644 index 0000000..a11fec7 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/olist-edit-distance-check-panic_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 : Tests whether a keyword index is applied to optimize a selection query using the edit-distance-check function on lists. + * The index should *not* be applied (see below). + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.AddressType as + closed { + number : integer, + street : string, + city : string +}; + +create type test.CustomerTypetmp as + closed { + cid : integer, + name : string, + age : integer?, + address : AddressType?, + interests : [string], + children : [{ + name : string, + age : integer? + } +] +}; + +create type test.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-basic_olist-edit-distance-check-panic.adm"; +set `compiler.sort.parallel` "true"; + +select element c +from Customers as c +where test.`edit-distance-check`(c.nested.interests,['computers','wine','walking'],3)[0] +order by c.nested.cid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/olist-edit-distance-check_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/olist-edit-distance-check_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/olist-edit-distance-check_ps.sqlpp new file mode 100644 index 0000000..c9b6d13 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/olist-edit-distance-check_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 : Tests whether a keyword index is applied to optimize a selection query using the edit-distance-check function on lists. + * The index should be applied. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.AddressType as + closed { + number : integer, + street : string, + city : string +}; + +create type test.CustomerTypetmp as + closed { + cid : integer, + name : string, + age : integer?, + address : AddressType?, + interests : [string], + children : [{ + name : string, + age : integer? + } +] +}; + +create type test.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-basic_olist-edit-distance-check.adm"; +set `compiler.sort.parallel` "true"; + +select element c +from Customers as c +where test.`edit-distance-check`(c.nested.interests,['computers','wine','walking'],1)[0] +order by c.nested.cid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/olist-edit-distance-panic_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/olist-edit-distance-panic_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/olist-edit-distance-panic_ps.sqlpp new file mode 100644 index 0000000..db3a364 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/olist-edit-distance-panic_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 : Tests whether a keyword index is applied to optimize a selection query using the edit-distance function on lists. + * The index should *not* be applied (see below). + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.AddressType as + closed { + number : integer, + street : string, + city : string +}; + +create type test.CustomerTypetmp as + closed { + cid : integer, + name : string, + age : integer?, + address : AddressType?, + interests : [string], + children : [{ + name : string, + age : integer? + } +] +}; + +create type test.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-basic_olist-edit-distance-panic.adm"; +set `compiler.sort.parallel` "true"; + +select element c +from Customers as c +where (test.`edit-distance`(c.nested.interests,['computers','wine','walking']) <= 3) +order by c.nested.cid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/olist-edit-distance_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/olist-edit-distance_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/olist-edit-distance_ps.sqlpp new file mode 100644 index 0000000..05a8936 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/olist-edit-distance_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 : Tests whether a keyword index is applied to optimize a selection query using the edit-distance function on lists. + * The index should be applied. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.AddressType as + closed { + number : integer, + street : string, + city : string +}; + +create type test.CustomerTypetmp as + closed { + cid : integer, + name : string, + age : integer?, + address : AddressType?, + interests : [string], + children : [{ + name : string, + age : integer? + } +] +}; + +create type test.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-basic_olist-edit-distance.adm"; +set `compiler.sort.parallel` "true"; + +select element c +from Customers as c +where (test.`edit-distance`(c.nested.interests,['computers','wine','walking']) <= 1) +order by c.nested.cid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/olist-fuzzyeq-edit-distance_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/olist-fuzzyeq-edit-distance_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/olist-fuzzyeq-edit-distance_ps.sqlpp new file mode 100644 index 0000000..b53584e --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/olist-fuzzyeq-edit-distance_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 : Tests whether a keyword index is applied to optimize a selection query with ~= using edit-distance on lists. + * The index should be applied. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.AddressType as + closed { + number : integer, + street : string, + city : string +}; + +create type test.CustomerTypetmp as + closed { + cid : integer, + name : string, + age : integer?, + address : AddressType?, + interests : [string], + children : [{ + name : string, + age : integer? + } +] +}; + +create type test.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-basic_olist-fuzzyeq-edit-distance.adm"; +set `simfunction` `edit-distance`; +set `simthreshold` `1`; +set `compiler.sort.parallel` "true"; + +select element c +from Customers as c +where (c.nested.interests ~= ['computers','wine','walking']) +order by c.nested.cid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/word-contains_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/word-contains_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/word-contains_ps.sqlpp new file mode 100644 index 0000000..e682634 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-basic/word-contains_ps.sqlpp @@ -0,0 +1,56 @@ +/* + * 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 + closed { + id : integer, + dblpid : string, + title : 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) type keyword; + +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-index/inverted-index-complex/olist-edit-distance-check-let-panic_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/olist-edit-distance-check-let-panic_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/olist-edit-distance-check-let-panic_ps.sqlpp new file mode 100644 index 0000000..5d57ef6 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/olist-edit-distance-check-let-panic_ps.sqlpp @@ -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 : 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 test; + + +create type test.AddressType as + closed { + number : integer, + street : string, + city : string +}; + +create type test.CustomerTypetmp as + closed { + cid : integer, + name : string, + age : integer?, + address : AddressType?, + interests : [string], + children : [{ + name : string, + age : integer? + } +] +}; + +create type test.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"; +set `compiler.sort.parallel` "true"; + +select element c +from Customers as c +with ed as test.`edit-distance-check`(c.nested.interests,['computers','wine','walking'],3) +where ed[0] +order by c.nested.cid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/olist-edit-distance-check-let_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/olist-edit-distance-check-let_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/olist-edit-distance-check-let_ps.sqlpp new file mode 100644 index 0000000..acafb14 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-complex/olist-edit-distance-check-let_ps.sqlpp @@ -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 : 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 test; + + +create type test.AddressType as + closed { + number : integer, + street : string, + city : string +}; + +create type test.CustomerTypetmp as + closed { + cid : integer, + name : string, + age : integer?, + address : AddressType?, + interests : [string], + children : [{ + name : string, + age : integer? + } +] +}; + +create type test.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"; +set `compiler.sort.parallel` "true"; + +select element c +from Customers as c +with ed as test.`edit-distance-check`(c.nested.interests,['computers','wine','walking'],1) +where ed[0] +order by c.nested.cid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-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-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-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01_ps.sqlpp new file mode 100644 index 0000000..bfd2bf4 --- /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_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.TweetMessageNestedType as + closed { + tweetid : bigint, + user : TwitterUserType, + `sender-location` : point, + `send-time` : datetime, + `referred-topics` : {{string}}, + `message-text` : string, + countA : integer, + countB : integer +}; + +create type test.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"; +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-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-jaccard-check-idx_01_ps.sqlpp ---------------------------------------------------------------------- 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_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-jaccard-check-idx_01_ps.sqlpp new file mode 100644 index 0000000..292925a --- /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_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.TweetMessageNestedType as + closed { + tweetid : bigint, + user : TwitterUserType, + `sender-location` : point, + `send-time` : datetime, + `referred-topics` : {{string}}, + `message-text` : string, + countA : integer, + countB : integer +}; + +create type test.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"; +set `compiler.sort.parallel` "true"; + +select element {'tweet':{'id':t1.nested.tweetid,'topics':t1.nested.`referred-topics`},'similar-tweets':( + select element {'id':t2.nested.tweetid,'topics':t2.nested.`referred-topics`} + from TweetMessages as t2 + with sim as test.`similarity-jaccard-check`(t1.nested.`referred-topics`,t2.nested.`referred-topics`,0.500000f) + 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-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-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01_ps.sqlpp new file mode 100644 index 0000000..245e3ac --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01_ps.sqlpp @@ -0,0 +1,82 @@ +/* + * 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}}, + `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`) 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_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-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-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02_ps.sqlpp new file mode 100644 index 0000000..2481d9e --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02_ps.sqlpp @@ -0,0 +1,82 @@ +/* + * 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 + closed { + tweetid : bigint, + user : TwitterUserType, + `sender-location` : point, + `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`) 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"; +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 +;
