You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@asterixdb.apache.org by bu...@apache.org on 2015/10/31 00:16:46 UTC
[38/51] [partial] incubator-asterixdb git commit: SQL++ parser: 1.
refactored asterix-aql to become asterix-lang-common and asterix-lang-aql,
where the former is the common part for different languages;
2. added asterix-lang-sqlpp on top of asterix-lang-
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/ulist-jaccard_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/ulist-jaccard_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/ulist-jaccard_03.sqlpp
new file mode 100644
index 0000000..0a0e384
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/ulist-jaccard_03.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 : Fuzzy self joins a dataset, Customers, based on the similarity-jaccard function of its interest sets.
+ * Customers has a keyword index on interests, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.AddressType as
+ closed {
+ number : int32,
+ street : string,
+ city : string
+}
+
+create type test.CustomerType as
+ closed {
+ cid : int32,
+ name : string,
+ age : int32?,
+ address : AddressType?,
+ interests : {{string}},
+ children : [{
+ name : string,
+ age : int32?
+ }
+]
+}
+
+create table Customers(CustomerType) primary key cid;
+
+create index interests_index on Customers (interests) type keyword;
+
+write output to nc1:"rttest/inverted-index-join_ulist-jaccard_03.adm"
+select element {'arec':a,'brec':b}
+from Customers as a,
+ Customers as b
+where (( /*+ indexnl */ test."similarity-jaccard"(a.interests,b.interests) >= 0.700000f) and (a.cid < b.cid))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/ulist-jaccard_04.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/ulist-jaccard_04.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/ulist-jaccard_04.sqlpp
new file mode 100644
index 0000000..60a47fe
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/ulist-jaccard_04.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 : Fuzzy self joins a dataset, Customers, based on the similarity-jaccard function of its interest sets.
+ * Customers has a keyword index on interests, and we expect the join to be transformed into an indexed nested-loop join.
+ * We test the inlining of variables that enable the select to be pushed into the join for subsequent optimization with an index.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.AddressType as
+ closed {
+ number : int32,
+ street : string,
+ city : string
+}
+
+create type test.CustomerType as
+ closed {
+ cid : int32,
+ name : string,
+ age : int32?,
+ address : AddressType?,
+ interests : {{string}},
+ children : [{
+ name : string,
+ age : int32?
+ }
+]
+}
+
+create table Customers(CustomerType) primary key cid;
+
+create index interests_index on Customers (interests) type keyword;
+
+write output to nc1:"rttest/inverted-index-join_ulist-jaccard_04.adm"
+select element {'arec':a,'brec':b,'jacc':jacc}
+from Customers as a,
+ Customers as b
+with jacc as /*+ indexnl */ test."similarity-jaccard"(a.interests,b.interests)
+where ((jacc >= 0.700000f) and (a.cid < b.cid))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-fuzzyeq-jaccard_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-fuzzyeq-jaccard_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-fuzzyeq-jaccard_01.sqlpp
new file mode 100644
index 0000000..07eafcc
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-fuzzyeq-jaccard_01.sqlpp
@@ -0,0 +1,64 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : Fuzzy joins two datasets, DBLP and CSX, based on ~= using Jaccard of their titles' word tokens.
+ * DBLP has a keyword index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.DBLPType as
+ closed {
+ id : int32,
+ dblpid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.CSXType as
+ closed {
+ id : int32,
+ csxid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create table DBLP(DBLPType) primary key id;
+
+create table CSX(CSXType) primary key id;
+
+create index keyword_index on DBLP (title) type keyword;
+
+write output to nc1:"rttest/inverted-index-join_word-fuzzyeq-jaccard_01.adm"
+set "simfunction" "jaccard";
+
+set "simthreshold" "0.5f";
+
+select element {'arec':a,'brec':b}
+from DBLP as a,
+ CSX as b
+where ((test."word-tokens"(a.title) ~= test."word-tokens"(b.title)) and (a.id < b.id))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-fuzzyeq-jaccard_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-fuzzyeq-jaccard_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-fuzzyeq-jaccard_02.sqlpp
new file mode 100644
index 0000000..278e67b
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-fuzzyeq-jaccard_02.sqlpp
@@ -0,0 +1,64 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : Fuzzy joins two datasets, DBLP and CSX, based on ~= using Jaccard of their titles' word tokens.
+ * CSX has a keyword index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.DBLPType as
+ closed {
+ id : int32,
+ dblpid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.CSXType as
+ closed {
+ id : int32,
+ csxid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create table DBLP(DBLPType) primary key id;
+
+create table CSX(CSXType) primary key id;
+
+create index keyword_index on CSX (title) type keyword;
+
+write output to nc1:"rttest/inverted-index-join_word-fuzzyeq-jaccard_02.adm"
+set "simfunction" "jaccard";
+
+set "simthreshold" "0.5f";
+
+select element {'arec':a,'brec':b}
+from DBLP as a,
+ CSX as b
+where ((test."word-tokens"(a.title) ~= test."word-tokens"(b.title)) and (a.id < b.id))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-fuzzyeq-jaccard_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-fuzzyeq-jaccard_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-fuzzyeq-jaccard_03.sqlpp
new file mode 100644
index 0000000..7059c4a
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-fuzzyeq-jaccard_03.sqlpp
@@ -0,0 +1,53 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : Fuzzy self joins a dataset, DBLP, based on ~= using Jaccard of its titles' word tokens.
+ * DBLP has a keyword index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.DBLPType as
+ closed {
+ id : int32,
+ dblpid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create table DBLP(DBLPType) primary key id;
+
+create index keyword_index on DBLP (title) type keyword;
+
+write output to nc1:"rttest/inverted-index-join_word-fuzzyeq-jaccard_03.adm"
+set "simfunction" "jaccard";
+
+set "simthreshold" "0.5f";
+
+select element {'arec':a,'brec':b}
+from DBLP as a,
+ DBLP as b
+where ((test."word-tokens"(a.title) ~= test."word-tokens"(b.title)) and (a.id < b.id))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard-check-after-btree-access.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard-check-after-btree-access.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard-check-after-btree-access.sqlpp
new file mode 100644
index 0000000..a8af07c
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard-check-after-btree-access.sqlpp
@@ -0,0 +1,71 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : Fuzzy self joins a dataset, TweetMessages, based on the similarity-jaccard-check function of its text-messages' word tokens.
+ * TweetMessages has a keyword index on text-message and btree index on the primary key tweetid, and we expect the join to be
+ * transformed into btree and inverted indexed nested-loop joins. We test whether the join condition can be transformed into
+ * multiple indexed nested loop joins of various type of indexes.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.TwitterUserType as
+ closed {
+ "screen-name" : string,
+ lang : string,
+ "friends-count" : int32,
+ "statuses-count" : int32,
+ name : string,
+ "followers-count" : int32
+}
+
+create type test.TweetMessageType as
+ closed {
+ tweetid : int64,
+ user : TwitterUserType,
+ "sender-location" : point,
+ "send-time" : datetime,
+ "referred-topics" : {{string}},
+ "message-text" : string,
+ countA : int32,
+ countB : int32
+}
+
+create table 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 msgTextIx on TweetMessages ("message-text") type keyword;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard-check-after-btree-access.adm"
+select element {'t1':t1.tweetid,'t2':t2.tweetid,'sim':sim[1]}
+from TweetMessages as t1,
+ TweetMessages as t2
+with sim as test."similarity-jaccard-check"(test."word-tokens"(t1."message-text"),test."word-tokens"(t2."message-text"),0.600000f)
+where (sim[0] and (t1.tweetid < test.int64('20')) and (t2.tweetid != t1.tweetid))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard-check_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard-check_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard-check_01.sqlpp
new file mode 100644
index 0000000..d35694a
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard-check_01.sqlpp
@@ -0,0 +1,60 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : Fuzzy joins two datasets, DBLP and CSX, based on the similarity-jaccard-check function of their titles' word tokens.
+ * DBLP has a keyword index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.DBLPType as
+ closed {
+ id : int32,
+ dblpid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.CSXType as
+ closed {
+ id : int32,
+ csxid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create table DBLP(DBLPType) primary key id;
+
+create table CSX(CSXType) primary key id;
+
+create index keyword_index on DBLP (title) type keyword;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard-check_01.adm"
+select element {'arec':a,'brec':b}
+from DBLP as a,
+ CSX as b
+where (test."similarity-jaccard-check"(test."word-tokens"(a.title),test."word-tokens"(b.title),0.500000f)[0] and (a.id < b.id))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard-check_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard-check_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard-check_02.sqlpp
new file mode 100644
index 0000000..963dee2
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard-check_02.sqlpp
@@ -0,0 +1,60 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : Fuzzy joins two datasets, DBLP and CSX, based on the similarity-jaccard-check function of their titles' word tokens.
+ * CSX has a keyword index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.DBLPType as
+ closed {
+ id : int32,
+ dblpid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.CSXType as
+ closed {
+ id : int32,
+ csxid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create table DBLP(DBLPType) primary key id;
+
+create table CSX(CSXType) primary key id;
+
+create index keyword_index on CSX (title) type keyword;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard-check_02.adm"
+select element {'arec':a,'brec':b}
+from DBLP as a,
+ CSX as b
+where (test."similarity-jaccard-check"(test."word-tokens"(a.title),test."word-tokens"(b.title),0.500000f)[0] and (a.id < b.id))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard-check_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard-check_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard-check_03.sqlpp
new file mode 100644
index 0000000..9b7ffb2
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard-check_03.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : Fuzzy self joins a dataset, DBLP, based on the similarity-jaccard-check function of its titles' word tokens.
+ * DBLP has a keyword index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.DBLPType as
+ closed {
+ id : int32,
+ dblpid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create table DBLP(DBLPType) primary key id;
+
+create index keyword_index on DBLP (title) type keyword;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard-check_03.adm"
+select element {'arec':a,'brec':b}
+from DBLP as a,
+ DBLP as b
+where (test."similarity-jaccard-check"(test."word-tokens"(a.title),test."word-tokens"(b.title),0.500000f)[0] and (a.id < b.id))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard-check_04.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard-check_04.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard-check_04.sqlpp
new file mode 100644
index 0000000..96fa6de
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard-check_04.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 : Fuzzy self joins a dataset, DBLP, based on the similarity-jaccard-check function of its titles' word tokens.
+ * DBLP has a keyword index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * We test the inlining of variables that enable the select to be pushed into the join for subsequent optimization with an index.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.DBLPType as
+ closed {
+ id : int32,
+ dblpid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create table DBLP(DBLPType) primary key id;
+
+create index keyword_index on DBLP (title) type keyword;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard-check_04.adm"
+select element {'arec':a,'brec':b,'jacc':jacc[1]}
+from DBLP as a,
+ DBLP as b
+with jacc as test."similarity-jaccard-check"(test."word-tokens"(a.title),test."word-tokens"(b.title),0.500000f)
+where (jacc[0] and (a.id < b.id))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard_01.sqlpp
new file mode 100644
index 0000000..8ba66ca
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard_01.sqlpp
@@ -0,0 +1,60 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : Fuzzy joins two datasets, DBLP and CSX, based on the similarity-jaccard function of their titles' word tokens.
+ * DBLP has a keyword index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.DBLPType as
+ closed {
+ id : int32,
+ dblpid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.CSXType as
+ closed {
+ id : int32,
+ csxid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create table DBLP(DBLPType) primary key id;
+
+create table CSX(CSXType) primary key id;
+
+create index keyword_index on DBLP (title) type keyword;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard_01.adm"
+select element {'arec':a,'brec':b}
+from DBLP as a,
+ CSX as b
+where ((test."similarity-jaccard"(test."word-tokens"(a.title),test."word-tokens"(b.title)) >= 0.500000f) and (a.id < b.id))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard_02.sqlpp
new file mode 100644
index 0000000..e525469
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard_02.sqlpp
@@ -0,0 +1,60 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : Fuzzy joins two datasets, DBLP and CSX, based on the similarity-jaccard function of their titles' word tokens.
+ * CSX has a keyword index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.DBLPType as
+ closed {
+ id : int32,
+ dblpid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.CSXType as
+ closed {
+ id : int32,
+ csxid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create table DBLP(DBLPType) primary key id;
+
+create table CSX(CSXType) primary key id;
+
+create index keyword_index on CSX (title) type keyword;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard_02.adm"
+select element {'arec':a,'brec':b}
+from DBLP as a,
+ CSX as b
+where ((test."similarity-jaccard"(test."word-tokens"(a.title),test."word-tokens"(b.title)) >= 0.500000f) and (a.id < b.id))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard_03.sqlpp
new file mode 100644
index 0000000..e9d670d
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard_03.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : Fuzzy self joins a dataset, DBLP, based on the similarity-jaccard function of its titles' word tokens.
+ * DBLP has a keyword index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.DBLPType as
+ closed {
+ id : int32,
+ dblpid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create table DBLP(DBLPType) primary key id;
+
+create index keyword_index on DBLP (title) type keyword;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard_03.adm"
+select element {'arec':a,'brec':b}
+from DBLP as a,
+ DBLP as b
+where ((test."similarity-jaccard"(test."word-tokens"(a.title),test."word-tokens"(b.title)) >= 0.500000f) and (a.id < b.id))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard_04.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard_04.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard_04.sqlpp
new file mode 100644
index 0000000..2782951
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/inverted-index-join/word-jaccard_04.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 : Fuzzy self joins a dataset, DBLP, based on the similarity-jaccard function of its titles' word tokens.
+ * DBLP has a keyword index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * We test the inlining of variables that enable the select to be pushed into the join for subsequent optimization with an index.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.DBLPType as
+ closed {
+ id : int32,
+ dblpid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create table DBLP(DBLPType) primary key id;
+
+create index keyword_index on DBLP (title) type keyword;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard_04.adm"
+select element {'arec':a,'brec':b,'jacc':jacc}
+from DBLP as a,
+ DBLP as b
+with jacc as test."similarity-jaccard"(test."word-tokens"(a.title),test."word-tokens"(b.title))
+where ((jacc >= 0.500000f) and (a.id < b.id))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/join-super-key_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/join-super-key_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/join-super-key_01.sqlpp
new file mode 100644
index 0000000..0a4bd2b
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/join-super-key_01.sqlpp
@@ -0,0 +1,100 @@
+/*
+ * 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 database "join-super-key_1" if exists;
+create database "join-super-key_1";
+
+use "join-super-key_1";
+
+
+create type "join-super-key_1".SupplierType as
+ closed {
+ s_suppkey : int32,
+ s_name : string,
+ s_address : string,
+ s_nationkey : int32,
+ s_phone : string,
+ s_acctbal : double,
+ s_comment : string
+}
+
+create type "join-super-key_1".NationType as
+ closed {
+ n_nationkey : int32,
+ n_name : string,
+ n_regionkey : int32,
+ n_comment : string
+}
+
+create type "join-super-key_1".LineItemType as
+ closed {
+ l_orderkey : int32,
+ l_partkey : int32,
+ l_suppkey : int32,
+ l_linenumber : int32,
+ l_quantity : double,
+ l_extendedprice : double,
+ l_discount : double,
+ l_tax : double,
+ l_returnflag : string,
+ l_linestatus : string,
+ l_shipdate : string,
+ l_commitdate : string,
+ l_receiptdate : string,
+ l_shipinstruct : string,
+ l_shipmode : string,
+ l_comment : string
+}
+
+create type "join-super-key_1".PartType as
+ closed {
+ p_partkey : int32,
+ p_name : string,
+ p_mfgr : string,
+ p_brand : string,
+ p_type : string,
+ p_size : int32,
+ p_container : string,
+ p_retailprice : double,
+ p_comment : string
+}
+
+create type "join-super-key_1".PartSuppType as
+ closed {
+ ps_partkey : int32,
+ ps_suppkey : int32,
+ ps_availqty : int32,
+ ps_supplycost : double,
+ ps_comment : string
+}
+
+create nodegroup group1 if not exists on
+ nc1,
+ nc2
+;
+write output to nc1:"/tmp/join-super-key_01.adm"
+create table LineItems(LineItemType) primary key l_partkey,l_linenumber on group1;
+
+create table PartSupp(PartSuppType) primary key ps_partkey,ps_suppkey on group1;
+
+select element {'l_partkey':li.l_partkey}
+from LineItems as li,
+ PartSupp as ps
+where ((li.l_partkey = ps.ps_partkey) and (li.l_suppkey = ps.ps_suppkey) and (li.l_extendedprice = ps.ps_supplycost))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/join-super-key_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/join-super-key_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/join-super-key_02.sqlpp
new file mode 100644
index 0000000..7aab555
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/join-super-key_02.sqlpp
@@ -0,0 +1,100 @@
+/*
+ * 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 database "join-super-key_01" if exists;
+create database "join-super-key_01";
+
+use "join-super-key_01";
+
+
+create type "join-super-key_01".SupplierType as
+ closed {
+ s_suppkey : int32,
+ s_name : string,
+ s_address : string,
+ s_nationkey : int32,
+ s_phone : string,
+ s_acctbal : double,
+ s_comment : string
+}
+
+create type "join-super-key_01".NationType as
+ closed {
+ n_nationkey : int32,
+ n_name : string,
+ n_regionkey : int32,
+ n_comment : string
+}
+
+create type "join-super-key_01".LineItemType as
+ closed {
+ l_orderkey : int32,
+ l_partkey : int32,
+ l_suppkey : int32,
+ l_linenumber : int32,
+ l_quantity : double,
+ l_extendedprice : double,
+ l_discount : double,
+ l_tax : double,
+ l_returnflag : string,
+ l_linestatus : string,
+ l_shipdate : string,
+ l_commitdate : string,
+ l_receiptdate : string,
+ l_shipinstruct : string,
+ l_shipmode : string,
+ l_comment : string
+}
+
+create type "join-super-key_01".PartType as
+ closed {
+ p_partkey : int32,
+ p_name : string,
+ p_mfgr : string,
+ p_brand : string,
+ p_type : string,
+ p_size : int32,
+ p_container : string,
+ p_retailprice : double,
+ p_comment : string
+}
+
+create type "join-super-key_01".PartSuppType as
+ closed {
+ ps_partkey : int32,
+ ps_suppkey : int32,
+ ps_availqty : int32,
+ ps_supplycost : double,
+ ps_comment : string
+}
+
+create nodegroup group1 if not exists on
+ nc1,
+ nc2
+;
+write output to nc1:"/tmp/join-super-key_01.adm"
+create table LineItems(LineItemType) primary key l_partkey,l_linenumber on group1;
+
+create table PartSupp(PartSuppType) primary key ps_partkey,ps_suppkey on group1;
+
+select element {'l_partkey':li.l_partkey}
+from PartSupp as ps,
+ LineItems as li
+where ((li.l_partkey = ps.ps_partkey) and (li.l_suppkey = ps.ps_suppkey) and (li.l_extendedprice = ps.ps_supplycost))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/limit-issue353.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/limit-issue353.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/limit-issue353.sqlpp
new file mode 100644
index 0000000..e0b1b89
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/limit-issue353.sqlpp
@@ -0,0 +1,54 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : This test case is to verify the fix for issue353
+
+drop database tpch if exists;
+create database tpch;
+
+use tpch;
+
+
+create type tpch.LineItemType as
+ closed {
+ l_orderkey : int32,
+ l_partkey : int32,
+ l_suppkey : int32,
+ l_linenumber : int32,
+ l_quantity : double,
+ l_extendedprice : double,
+ l_discount : double,
+ l_tax : double,
+ l_returnflag : string,
+ l_linestatus : string,
+ l_shipdate : string,
+ l_commitdate : string,
+ l_receiptdate : string,
+ l_shipinstruct : string,
+ l_shipmode : string,
+ l_comment : string
+}
+
+create table LineItem(LineItemType) primary key l_orderkey,l_linenumber;
+
+write output to nc1:"/tmp/push_limit.adm"
+select element {'l_returnflag':l.l_returnflag,'l_linestatus':l.l_linestatus,'l_shipmode':l.l_shipmode}
+from LineItem as l
+limit 2
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/loj-super-key_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/loj-super-key_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/loj-super-key_01.sqlpp
new file mode 100644
index 0000000..9215f6d
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/loj-super-key_01.sqlpp
@@ -0,0 +1,103 @@
+/*
+ * 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 database "loj-super-key_01" if exists;
+create database "loj-super-key_01";
+
+use "loj-super-key_01";
+
+
+create type "loj-super-key_01".SupplierType as
+ closed {
+ s_suppkey : int32,
+ s_name : string,
+ s_address : string,
+ s_nationkey : int32,
+ s_phone : string,
+ s_acctbal : double,
+ s_comment : string
+}
+
+create type "loj-super-key_01".NationType as
+ closed {
+ n_nationkey : int32,
+ n_name : string,
+ n_regionkey : int32,
+ n_comment : string
+}
+
+create type "loj-super-key_01".LineItemType as
+ closed {
+ l_orderkey : int32,
+ l_partkey : int32,
+ l_suppkey : int32,
+ l_linenumber : int32,
+ l_quantity : double,
+ l_extendedprice : double,
+ l_discount : double,
+ l_tax : double,
+ l_returnflag : string,
+ l_linestatus : string,
+ l_shipdate : string,
+ l_commitdate : string,
+ l_receiptdate : string,
+ l_shipinstruct : string,
+ l_shipmode : string,
+ l_comment : string
+}
+
+create type "loj-super-key_01".PartType as
+ closed {
+ p_partkey : int32,
+ p_name : string,
+ p_mfgr : string,
+ p_brand : string,
+ p_type : string,
+ p_size : int32,
+ p_container : string,
+ p_retailprice : double,
+ p_comment : string
+}
+
+create type "loj-super-key_01".PartSuppType as
+ closed {
+ ps_partkey : int32,
+ ps_suppkey : int32,
+ ps_availqty : int32,
+ ps_supplycost : double,
+ ps_comment : string
+}
+
+create nodegroup group1 if not exists on
+ nc1,
+ nc2
+;
+write output to nc1:"/tmp/loj-super-key_01.adm"
+create table LineItems(LineItemType) primary key l_partkey,l_linenumber on group1;
+
+create table PartSupp(PartSuppType) primary key ps_partkey,ps_suppkey on group1;
+
+select element {'li':li,'partsupp':partsupp}
+from LineItems as li
+with partsupp as (
+ select element ps
+ from PartSupp as ps
+ where ((li.l_partkey = ps.ps_partkey) and (li.l_suppkey = ps.ps_suppkey) and (li.l_extendedprice = ps.ps_supplycost))
+ )
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/loj-super-key_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/loj-super-key_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/loj-super-key_02.sqlpp
new file mode 100644
index 0000000..a80fa61
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/loj-super-key_02.sqlpp
@@ -0,0 +1,103 @@
+/*
+ * 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 database "loj-super-key_02" if exists;
+create database "loj-super-key_02";
+
+use "loj-super-key_02";
+
+
+create type "loj-super-key_02".SupplierType as
+ closed {
+ s_suppkey : int32,
+ s_name : string,
+ s_address : string,
+ s_nationkey : int32,
+ s_phone : string,
+ s_acctbal : double,
+ s_comment : string
+}
+
+create type "loj-super-key_02".NationType as
+ closed {
+ n_nationkey : int32,
+ n_name : string,
+ n_regionkey : int32,
+ n_comment : string
+}
+
+create type "loj-super-key_02".LineItemType as
+ closed {
+ l_orderkey : int32,
+ l_partkey : int32,
+ l_suppkey : int32,
+ l_linenumber : int32,
+ l_quantity : double,
+ l_extendedprice : double,
+ l_discount : double,
+ l_tax : double,
+ l_returnflag : string,
+ l_linestatus : string,
+ l_shipdate : string,
+ l_commitdate : string,
+ l_receiptdate : string,
+ l_shipinstruct : string,
+ l_shipmode : string,
+ l_comment : string
+}
+
+create type "loj-super-key_02".PartType as
+ closed {
+ p_partkey : int32,
+ p_name : string,
+ p_mfgr : string,
+ p_brand : string,
+ p_type : string,
+ p_size : int32,
+ p_container : string,
+ p_retailprice : double,
+ p_comment : string
+}
+
+create type "loj-super-key_02".PartSuppType as
+ closed {
+ ps_partkey : int32,
+ ps_suppkey : int32,
+ ps_availqty : int32,
+ ps_supplycost : double,
+ ps_comment : string
+}
+
+create nodegroup group1 if not exists on
+ nc1,
+ nc2
+;
+write output to nc1:"/tmp/loj-super-key_01.adm"
+create table LineItems(LineItemType) primary key l_partkey,l_linenumber on group1;
+
+create table PartSupp(PartSuppType) primary key ps_partkey,ps_suppkey on group1;
+
+select element {'partsupp':ps,'li':items}
+from PartSupp as ps
+with items as (
+ select element li
+ from LineItems as li
+ where ((li.l_partkey = ps.ps_partkey) and (li.l_suppkey = ps.ps_suppkey) and (li.l_extendedprice = ps.ps_supplycost))
+ )
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nest_aggregate.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nest_aggregate.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nest_aggregate.sqlpp
new file mode 100644
index 0000000..2b67f09
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nest_aggregate.sqlpp
@@ -0,0 +1,137 @@
+/*
+ * 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 : This test case is to verify the fix for issue782
+ * https://code.google.com/p/asterixdb/issues/detail?id=782
+ * Expected Res : SUCCESS
+ * Date : 2nd Jun 2014
+ */
+
+drop database tpch if exists;
+create database tpch;
+
+use tpch;
+
+
+create type tpch.OrderType as
+ closed {
+ o_orderkey : int32,
+ o_custkey : int32,
+ o_orderstatus : string,
+ o_totalprice : double,
+ o_orderdate : string,
+ o_orderpriority : string,
+ o_clerk : string,
+ o_shippriority : int32,
+ o_comment : string
+}
+
+create type tpch.CustomerType as
+ closed {
+ c_custkey : int32,
+ c_name : string,
+ c_address : string,
+ c_nationkey : int32,
+ c_phone : string,
+ c_acctbal : double,
+ c_mktsegment : string,
+ c_comment : string
+}
+
+create type tpch.SupplierType as
+ closed {
+ s_suppkey : int32,
+ s_name : string,
+ s_address : string,
+ s_nationkey : int32,
+ s_phone : string,
+ s_acctbal : double,
+ s_comment : string
+}
+
+create type tpch.NationType as
+ closed {
+ n_nationkey : int32,
+ n_name : string,
+ n_regionkey : int32,
+ n_comment : string
+}
+
+create type tpch.RegionType as
+ closed {
+ r_regionkey : int32,
+ r_name : string,
+ r_comment : string
+}
+
+create type tpch.PartType as
+ closed {
+ p_partkey : int32,
+ p_name : string,
+ p_mfgr : string,
+ p_brand : string,
+ p_type : string,
+ p_size : int32,
+ p_container : string,
+ p_retailprice : double,
+ p_comment : string
+}
+
+create type tpch.PartSuppType as
+ closed {
+ ps_partkey : int32,
+ ps_suppkey : int32,
+ ps_availqty : int32,
+ ps_supplycost : double,
+ ps_comment : string
+}
+
+create table Orders(OrderType) primary key o_orderkey;
+
+create table Supplier(SupplierType) primary key s_suppkey;
+
+create table Region(RegionType) primary key r_regionkey;
+
+create table Nation(NationType) primary key n_nationkey;
+
+create table Part(PartType) primary key p_partkey;
+
+create table Partsupp(PartSuppType) primary key ps_partkey,ps_suppkey;
+
+create table Customer(CustomerType) primary key c_custkey;
+
+create table SelectedNation(NationType) primary key n_nationkey;
+
+select element {'nation_key':nation.n_nationkey,'name':nation.n_name,'aggregates':(
+ select element {'order_date':orderdate,'sum_price':sum}
+ from Orders as orders,
+ Customer as customer
+ where ((orders.o_custkey = customer.c_custkey) and (customer.c_nationkey = nation.n_nationkey))
+ group by orders.o_orderdate as orderdate
+ with sum as tpch.sum((
+ select element o.o_totalprice
+ from orders as o
+ ))
+ order by sum
+ limit 3
+ )}
+from Nation as nation,
+ SelectedNation as sn
+where (nation.n_nationkey = sn.n_nationkey)
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/disjunction-to-join.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/disjunction-to-join.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/disjunction-to-join.sqlpp
new file mode 100644
index 0000000..d5acf89
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/disjunction-to-join.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 : Disjunctive predicate should be transformed into collection scan.
+ * Secondary index should be used to probe the values retrieved from collection.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.NestedTestType as
+{
+ idx : string
+}
+
+create type test.TestType as
+{
+ id : string,
+ "no-idx" : string,
+ nested : NestedTestType
+}
+
+create table TestSet(TestType) primary key id;
+
+create index TestSetIndex on TestSet (nested.idx) type btree;
+
+select element x
+from TestSet as x
+where ((x.nested.idx = 'one') or (x.nested.idx = 'two'))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01.sqlpp
new file mode 100644
index 0000000..36e4c79
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01.sqlpp
@@ -0,0 +1,73 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : 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 database test if exists;
+create database test;
+
+use test;
+
+
+create type test.TwitterUserType as
+ closed {
+ "screen-name" : string,
+ lang : string,
+ "friends-count" : int32,
+ "statuses-count" : int32,
+ name : string,
+ "followers-count" : int32
+}
+
+create type test.TweetMessageNestedType as
+ closed {
+ tweetid : int64,
+ user : TwitterUserType,
+ "sender-location" : point,
+ "send-time" : datetime,
+ "referred-topics" : {{string}},
+ "message-text" : string,
+ countA : int32,
+ countB : int32
+}
+
+create type test.TweetMessageType as
+ closed {
+ nested : TweetMessageNestedType
+}
+
+create table TweetMessages(TweetMessageType) primary key nested.tweetid;
+
+create index msgCountBIx on TweetMessages (nested.countB) type btree;
+
+write output to nc1:"rttest/btree-index-join_leftouterjoin-probe-pidx-with-join-btree-sidx_01.adm"
+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.int64('10'))
+order by t1.nested.tweetid
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02.sqlpp
new file mode 100644
index 0000000..bf0a60f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02.sqlpp
@@ -0,0 +1,73 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : 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 database test if exists;
+create database test;
+
+use test;
+
+
+create type test.TwitterUserType as
+ closed {
+ "screen-name" : string,
+ lang : string,
+ "friends-count" : int32,
+ "statuses-count" : int32,
+ name : string,
+ "followers-count" : int32
+}
+
+create type test.TweetMessageNestedType as
+ closed {
+ tweetid : int64,
+ user : TwitterUserType,
+ "sender-location" : point,
+ "send-time" : datetime,
+ "referred-topics" : {{string}},
+ "message-text" : string,
+ countA : int32,
+ countB : int32
+}
+
+create type test.TweetMessageType as
+ closed {
+ nested : TweetMessageNestedType
+}
+
+create table TweetMessages(TweetMessageType) primary key nested.tweetid;
+
+create index msgCountBIx on TweetMessages (nested.countB) type btree;
+
+write output to nc1:"rttest/btree-index-join_leftouterjoin-probe-pidx-with-join-btree-sidx_02.adm"
+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.int64('10'))
+order by t1.nested.tweetid
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-join_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-join_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-join_01.sqlpp
new file mode 100644
index 0000000..b5db202
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-join_01.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 : Notice the query hint to use an indexed nested-loops join plan in both predicates.
+ * : We expect a plan to have a self-join, which probes dataset Names’s primary index.
+ * Expected Res : Success
+ * Date : 11th November 2014
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.Nametmp as
+{
+ fname : string,
+ lname : string
+}
+
+create type test.Name as
+{
+ nested : Nametmp
+}
+
+create table Names(Name) primary key nested.fname,nested.lname;
+
+write output to nc1:"rttest/btree-index-join_primary-composite-key-prefix-join_01.adm"
+select element {'emp1':emp1,'emp2':emp2}
+from Names as emp1,
+ Names as emp2
+where ((emp1.nested.fname /*+ indexnl */ > emp2.nested.fname) and (emp1.nested.lname /*+ indexnl */ > emp2.nested.lname))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-join_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-join_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-join_02.sqlpp
new file mode 100644
index 0000000..f6b191a
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-join_02.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 : Notice the query hint to use an indexed nested-loops join plan in both predicates.
+ * : We expect a plan to have a self-join, which probes dataset Names’s primary index.
+ * Expected Res : Success
+ * Date : 11th November 2014
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.Nametmp as
+{
+ fname : string,
+ lname : string
+}
+
+create type test.Name as
+{
+ nested : Nametmp
+}
+
+create table Names(Name) primary key nested.fname,nested.lname;
+
+write output to nc1:"rttest/btree-index-join_primary-composite-key-prefix-join_02.adm"
+select element {'emp1':emp1,'emp2':emp2}
+from Names as emp1,
+ Names as emp2
+where ((emp1.nested.fname /*+ indexnl */ < emp2.nested.fname) and (emp1.nested.lname /*+ indexnl */ < emp2.nested.lname))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-join_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-join_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-join_03.sqlpp
new file mode 100644
index 0000000..141fd42
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-join_03.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 : Notice the query hint to use an indexed nested-loops join plan in both predicates.
+ * : We expect a plan to have a self-join, which probes dataset Names’s primary index.
+ * Expected Res : Success
+ * Date : 11th November 2014
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.Nametmp as
+{
+ fname : string,
+ lname : string
+}
+
+create type test.Name as
+{
+ nested : Nametmp
+}
+
+create table Names(Name) primary key nested.fname,nested.lname;
+
+write output to nc1:"rttest/btree-index-join_primary-composite-key-prefix-join_03.adm"
+select element {'emp1':emp1,'emp2':emp2}
+from Names as emp1,
+ Names as emp2
+where ((emp1.nested.fname /*+ indexnl */ = emp2.nested.fname) and (emp1.nested.lname /*+ indexnl */ = emp2.nested.lname))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-prefix-join_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-prefix-join_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-prefix-join_01.sqlpp
new file mode 100644
index 0000000..c8aee57
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-prefix-join_01.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 : Notice the query hint to use an indexed nested-loops join plan in both predicates.
+ * : We expect a plan to have a self-join, which probes dataset Names’s with a prefix of its primary index.
+ * Expected Res : Success
+ * Date : 11th November 2014
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.Nametmp as
+{
+ fname : string,
+ lname : string
+}
+
+create type test.Name as
+{
+ nested : Nametmp
+}
+
+create table Names(Name) primary key nested.fname,nested.lname;
+
+write output to nc1:"rttest/btree-index-join_primary-composite-key-prefix-prefix-join_01.adm"
+select element {'emp1':emp1,'emp2':emp2}
+from Names as emp1,
+ Names as emp2
+where ((emp1.nested.fname /*+ indexnl */ < emp2.nested.fname) and (emp1.nested.lname /*+ indexnl */ > emp2.nested.lname))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-prefix-join_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-prefix-join_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-prefix-join_02.sqlpp
new file mode 100644
index 0000000..c38f606
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-prefix-join_02.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 : Notice the query hint to use an indexed nested-loops join plan in both predicates.
+ * : We expect a plan to have a self-join, which probes dataset Names’s with a prefix of its primary index.
+ * Expected Res : Success
+ * Date : 11th November 2014
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.Nametmp as
+{
+ fname : string,
+ lname : string
+}
+
+create type test.Name as
+{
+ nested : Nametmp
+}
+
+create table Names(Name) primary key nested.fname,nested.lname;
+
+write output to nc1:"rttest/btree-index-join_primary-composite-key-prefix-prefix-join_02.adm"
+select element {'emp1':emp1,'emp2':emp2}
+from Names as emp1,
+ Names as emp2
+where ((emp1.nested.fname /*+ indexnl */ > emp2.nested.fname) and (emp1.nested.lname /*+ indexnl */ < emp2.nested.lname))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-prefix-join_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-prefix-join_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-prefix-join_03.sqlpp
new file mode 100644
index 0000000..05f5d9f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-prefix-join_03.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 : Notice the query hint to use an indexed nested-loops join plan in both predicates.
+ * : We expect a plan to have a self-join, which probes dataset Names’s with a prefix of its primary index.
+ * Expected Res : Success
+ * Date : 11th November 2014
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.Nametmp as
+{
+ fname : string,
+ lname : string
+}
+
+create type test.Name as
+{
+ nested : Nametmp
+}
+
+create table Names(Name) primary key nested.fname,nested.lname;
+
+write output to nc1:"rttest/btree-index-join_primary-composite-key-prefix-prefix-join_03.adm"
+select element {'emp1':emp1,'emp2':emp2}
+from Names as emp1,
+ Names as emp2
+where ((emp1.nested.fname /*+ indexnl */ > emp2.nested.fname) and (emp1.nested.lname /*+ indexnl */ = emp2.nested.lname))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-prefix-join_04.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-prefix-join_04.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-prefix-join_04.sqlpp
new file mode 100644
index 0000000..cd462d3
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-prefix-join_04.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 : Notice the query hint to use an indexed nested-loops join plan in both predicates.
+ * : We expect a plan to have a self-join, which probes dataset Names’s with a prefix of its primary index.
+ * Expected Res : Success
+ * Date : 11th November 2014
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.Nametmp as
+{
+ fname : string,
+ lname : string
+}
+
+create type test.Name as
+{
+ nested : Nametmp
+}
+
+create table Names(Name) primary key nested.fname,nested.lname;
+
+write output to nc1:"rttest/btree-index-join_primary-composite-key-prefix-prefix-join_04.adm"
+select element {'emp1':emp1,'emp2':emp2}
+from Names as emp1,
+ Names as emp2
+where ((emp1.nested.fname /*+ indexnl */ < emp2.nested.fname) and (emp1.nested.lname /*+ indexnl */ = emp2.nested.lname))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-prefix-join_05.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-prefix-join_05.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-prefix-join_05.sqlpp
new file mode 100644
index 0000000..2ef6a91
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-prefix-join_05.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 : Notice the query hint to use an indexed nested-loops join plan in both predicates.
+ * : We expect a plan to have a self-join, which probes dataset Names’s with a prefix of its primary index.
+ * Expected Res : Success
+ * Date : 11th November 2014
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.Nametmp as
+{
+ fname : string,
+ lname : string
+}
+
+create type test.Name as
+{
+ nested : Nametmp
+}
+
+create table Names(Name) primary key nested.fname,nested.lname;
+
+write output to nc1:"rttest/btree-index-join_primary-composite-key-prefix-prefix-join_05.adm"
+select element {'emp1':emp1,'emp2':emp2}
+from Names as emp1,
+ Names as emp2
+where ((emp1.nested.fname /*+ indexnl */ = emp2.nested.fname) and (emp1.nested.lname /*+ indexnl */ > emp2.nested.lname))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-prefix-join_06.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-prefix-join_06.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-prefix-join_06.sqlpp
new file mode 100644
index 0000000..d95fe11
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-composite-key-prefix-join_06.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 : Notice the query hint to use an indexed nested-loops join plan in both predicates.
+ * : We expect a plan to have a self-join, which probes dataset Names’s with a prefix of its primary index.
+ * Expected Res : Success
+ * Date : 11th November 2014
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.Nametmp as
+{
+ fname : string,
+ lname : string
+}
+
+create type test.Name as
+{
+ nested : Nametmp
+}
+
+create table Names(Name) primary key nested.fname,nested.lname;
+
+write output to nc1:"rttest/btree-index-join_primary-composite-key-prefix-prefix-join_06.adm"
+select element {'emp1':emp1,'emp2':emp2}
+from Names as emp1,
+ Names as emp2
+where ((emp1.nested.fname /*+ indexnl */ = emp2.nested.fname) and (emp1.nested.lname /*+ indexnl */ < emp2.nested.lname))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join-multipred.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join-multipred.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join-multipred.sqlpp
new file mode 100644
index 0000000..ded8580
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join-multipred.sqlpp
@@ -0,0 +1,83 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : Equi joins two datasets, Customers and Orders, based on the customer id.
+ * Given the 'indexnl' hint we expect the join to be transformed
+ * into an indexed nested-loop join using Customers' primary index.
+ * We expect the additional predicates to be put into a select above the
+ * primary index search.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.AddressType as
+ closed {
+ number : int32,
+ street : string,
+ city : string
+}
+
+create type test.CustomerTypetmp as
+ closed {
+ cid : int32,
+ name : string,
+ age : int32?,
+ address : AddressType?,
+ lastorder : {
+ oid : int32,
+ total : float
+ }
+
+}
+
+create type test.OrderTypetmp as
+ closed {
+ oid : int32,
+ cid : int32,
+ orderstatus : string,
+ orderpriority : string,
+ clerk : string,
+ total : float
+}
+
+create type test.CustomerType as
+ closed {
+ nested : CustomerTypetmp
+}
+
+create type test.OrderType as
+ closed {
+ nested : OrderTypetmp
+}
+
+create table Customers(CustomerType) primary key nested.cid;
+
+create table Orders(OrderType) primary key nested.oid;
+
+write output to nc1:"rttest/btree-index-join_primary-equi-join-multipred.adm"
+select element {'customer':c.nested,'order':o.nested}
+from Customers as c,
+ Orders as o
+where ((c.nested.cid /*+ indexnl */ = o.nested.cid) and (c.nested.name < o.nested.orderstatus) and (c.nested.age < o.nested.cid))
+;