asterixdb-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From wangs...@apache.org
Subject [08/16] asterixdb git commit: [ASTERIXDB-1972][COMP][RT][TX] index-only plan
Date Fri, 16 Feb 2018 19:04:14 GMT
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.1.ddl.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.1.ddl.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.1.ddl.aql
new file mode 100644
index 0000000..9555bb1
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.1.ddl.aql
@@ -0,0 +1,46 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * Description    : Joins two datasets on the intersection of their point attributes.
+ *                  The inner dataset 'MyData2' has an RTree index, and we expect the
+ *                  join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use dataverse test;
+
+create type MyRecord as closed {
+  id: int64,
+  point: point,
+  kwds: string,
+  line1: line,
+  line2: line,
+  poly1: polygon,
+  poly2: polygon,
+  rec: rectangle,
+  circle: circle
+}
+
+create dataset MyData1(MyRecord) primary key id;
+create dataset MyData2(MyRecord) primary key id;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.2.update.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.2.update.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.2.update.aql
new file mode 100644
index 0000000..56687c4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.2.update.aql
@@ -0,0 +1,29 @@
+/*
+ * 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.
+ */
+
+use dataverse test;
+
+load dataset MyData1
+using localfs
+(("path"="asterix_nc1://data/spatial/spatialData.json"),("format"="adm")) pre-sorted;
+
+load dataset MyData2
+using localfs
+(("path"="asterix_nc1://data/spatial/spatialData.json"),("format"="adm")) pre-sorted;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.3.ddl.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.3.ddl.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.3.ddl.aql
new file mode 100644
index 0000000..bc5de3c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.3.ddl.aql
@@ -0,0 +1,30 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * Description    : Joins two datasets on the intersection of their point attributes.
+ *                  The inner dataset 'MyData2' has an RTree index, and we expect the
+ *                  join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+use dataverse test;
+
+create index rtree_index on MyData2(point) type rtree;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.4.query.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.4.query.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.4.query.aql
new file mode 100644
index 0000000..833111c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.4.query.aql
@@ -0,0 +1,33 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * Description    : Joins two datasets on the intersection of their point attributes.
+ *                  The inner dataset 'MyData2' has an RTree index, and we expect the
+ *                  join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+use dataverse test;
+
+for $a in dataset('MyData1')
+for $b in dataset('MyData2')
+where spatial-intersect($a.point, $b.point)
+order by $a.id, $b.id
+return {"aid": $a.id, "bid": $b.id, "apoint": $a.point, "bpoint": $b.point}

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.1.ddl.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.1.ddl.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.1.ddl.aql
new file mode 100644
index 0000000..33e7c9d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.1.ddl.aql
@@ -0,0 +1,47 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+ /*
+ * Description    : Joins two datasets on the intersection of their point attributes.
+ *                  Both inner and outer dataset have an RTree index, and we expect the
+ *                  join to be transformed into an indexed nested-loop join
+ *                  by using an RTree index from the inner dataset.
+ * Success        : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use dataverse test;
+
+create type MyRecord as closed {
+  id: int64,
+  point: point,
+  kwds: string,
+  line1: line,
+  line2: line,
+  poly1: polygon,
+  poly2: polygon,
+  rec: rectangle,
+  circle: circle
+}
+
+create dataset MyData1(MyRecord) primary key id;
+create dataset MyData2(MyRecord) primary key id;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.2.update.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.2.update.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.2.update.aql
new file mode 100644
index 0000000..56687c4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.2.update.aql
@@ -0,0 +1,29 @@
+/*
+ * 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.
+ */
+
+use dataverse test;
+
+load dataset MyData1
+using localfs
+(("path"="asterix_nc1://data/spatial/spatialData.json"),("format"="adm")) pre-sorted;
+
+load dataset MyData2
+using localfs
+(("path"="asterix_nc1://data/spatial/spatialData.json"),("format"="adm")) pre-sorted;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.3.ddl.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.3.ddl.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.3.ddl.aql
new file mode 100644
index 0000000..ee1eb35
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.3.ddl.aql
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+use dataverse test;
+
+create index rtree_index1 on MyData1(point) type rtree;
+create index rtree_index2 on MyData2(point) type rtree;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.4.query.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.4.query.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.4.query.aql
new file mode 100644
index 0000000..a5bb5c0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.4.query.aql
@@ -0,0 +1,26 @@
+/*
+ * 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.
+ */
+
+use dataverse test;
+
+for $a in dataset('MyData1')
+for $b in dataset('MyData2')
+where spatial-intersect($a.point, $b.point)
+order by $a.id, $b.id
+return {"aid": $a.id, "bid": $b.id, "apoint": $a.point, "bpoint": $b.point}

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.1.ddl.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.1.ddl.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.1.ddl.aql
new file mode 100644
index 0000000..b3e62ef
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.1.ddl.aql
@@ -0,0 +1,47 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * Description    : Joins two datasets on the intersection of their point attributes.
+ *                  Both inner and outer dataset have an RTree index, and we expect the
+ *                  join to be transformed into an indexed nested-loop join
+ *                  by using an RTree index from the inner dataset.
+ * Success        : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use dataverse test;
+
+create type MyRecord as closed {
+  id: int64,
+  point: point,
+  kwds: string,
+  line1: line,
+  line2: line,
+  poly1: polygon,
+  poly2: polygon,
+  rec: rectangle,
+  circle: circle
+}
+
+create dataset MyData1(MyRecord) primary key id;
+create dataset MyData2(MyRecord) primary key id;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.2.update.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.2.update.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.2.update.aql
new file mode 100644
index 0000000..56687c4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.2.update.aql
@@ -0,0 +1,29 @@
+/*
+ * 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.
+ */
+
+use dataverse test;
+
+load dataset MyData1
+using localfs
+(("path"="asterix_nc1://data/spatial/spatialData.json"),("format"="adm")) pre-sorted;
+
+load dataset MyData2
+using localfs
+(("path"="asterix_nc1://data/spatial/spatialData.json"),("format"="adm")) pre-sorted;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.3.ddl.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.3.ddl.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.3.ddl.aql
new file mode 100644
index 0000000..249601a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.3.ddl.aql
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+use dataverse test;
+
+create index rtree_index1 on MyData1(poly1) type rtree;
+create index rtree_index2 on MyData2(point) type rtree;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.4.query.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.4.query.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.4.query.aql
new file mode 100644
index 0000000..4d00d76
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.4.query.aql
@@ -0,0 +1,26 @@
+/*
+ * 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.
+ */
+
+use dataverse test;
+
+for $a in dataset('MyData1')
+for $b in dataset('MyData2')
+where spatial-intersect($a.poly1, $b.point)
+order by $a.id, $b.id
+return {"aid": $a.id, "bid": $b.id, "apoly1": $a.poly1, "bpoint": $b.point}

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.1.ddl.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.1.ddl.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.1.ddl.aql
new file mode 100644
index 0000000..b3e62ef
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.1.ddl.aql
@@ -0,0 +1,47 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * Description    : Joins two datasets on the intersection of their point attributes.
+ *                  Both inner and outer dataset have an RTree index, and we expect the
+ *                  join to be transformed into an indexed nested-loop join
+ *                  by using an RTree index from the inner dataset.
+ * Success        : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use dataverse test;
+
+create type MyRecord as closed {
+  id: int64,
+  point: point,
+  kwds: string,
+  line1: line,
+  line2: line,
+  poly1: polygon,
+  poly2: polygon,
+  rec: rectangle,
+  circle: circle
+}
+
+create dataset MyData1(MyRecord) primary key id;
+create dataset MyData2(MyRecord) primary key id;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.2.update.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.2.update.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.2.update.aql
new file mode 100644
index 0000000..56687c4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.2.update.aql
@@ -0,0 +1,29 @@
+/*
+ * 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.
+ */
+
+use dataverse test;
+
+load dataset MyData1
+using localfs
+(("path"="asterix_nc1://data/spatial/spatialData.json"),("format"="adm")) pre-sorted;
+
+load dataset MyData2
+using localfs
+(("path"="asterix_nc1://data/spatial/spatialData.json"),("format"="adm")) pre-sorted;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.3.ddl.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.3.ddl.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.3.ddl.aql
new file mode 100644
index 0000000..bcaa0bd
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.3.ddl.aql
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+use dataverse test;
+
+create index rtree_index1 on MyData1(poly1) type rtree;
+create index rtree_index2 on MyData2(poly2) type rtree;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.4.query.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.4.query.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.4.query.aql
new file mode 100644
index 0000000..ac8a03d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.4.query.aql
@@ -0,0 +1,26 @@
+/*
+ * 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.
+ */
+
+use dataverse test;
+
+for $a in dataset('MyData1')
+for $b in dataset('MyData2')
+where spatial-intersect($a.poly1, $b.poly2)
+order by $a.id, $b.id
+return {"aid": $a.id, "bid": $b.id, "apoly1": $a.poly1, "bpoly2": $b.poly2}

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-pidx1/probe-pidx-with-join-btree-pidx1.1.ddl.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-pidx1/probe-pidx-with-join-btree-pidx1.1.ddl.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-pidx1/probe-pidx-with-join-btree-pidx1.1.ddl.aql
new file mode 100644
index 0000000..0dfc827
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-pidx1/probe-pidx-with-join-btree-pidx1.1.ddl.aql
@@ -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  : Test that left-outer-join may use two available indexes, one for primary index in prob subtree and
+ *                another for primary index in index subtree.
+ * Expected Res : Success
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type TwitterUserType as closed {
+    screen-name: string,
+    lang: string,
+    friends-count: int64,
+    statuses-count: int64,
+    name: string,
+    followers-count: int64
+}
+
+create type TweetMessageType as closed {
+    tweetid: int64,
+        user: TwitterUserType,
+        sender-location: point,
+    send-time: datetime,
+        referred-topics: {{ string }},
+    message-text: string,
+    countA: int64,
+    countB: int64
+}
+
+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 msgTextIx on TweetMessages(message-text) type keyword;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-pidx1/probe-pidx-with-join-btree-pidx1.2.update.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-pidx1/probe-pidx-with-join-btree-pidx1.2.update.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-pidx1/probe-pidx-with-join-btree-pidx1.2.update.aql
new file mode 100644
index 0000000..fd0458a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-pidx1/probe-pidx-with-join-btree-pidx1.2.update.aql
@@ -0,0 +1,30 @@
+/*
+ * 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 primary index in index subtree.
+ * Expected Res : Success
+ */
+
+use dataverse test;
+
+load dataset TweetMessages
+using localfs
+(("path"="asterix_nc1://data/twitter/tw_for_indexleftouterjoin.adm"),("format"="adm"));

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-pidx1/probe-pidx-with-join-btree-pidx1.3.query.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-pidx1/probe-pidx-with-join-btree-pidx1.3.query.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-pidx1/probe-pidx-with-join-btree-pidx1.3.query.aql
new file mode 100644
index 0000000..0512daa
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-pidx1/probe-pidx-with-join-btree-pidx1.3.query.aql
@@ -0,0 +1,40 @@
+/*
+ * 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 primary index in index subtree.
+ * Expected Res : Success
+ */
+
+
+use dataverse test;
+
+count(
+for $t1 in dataset('TweetMessages')
+where $t1.tweetid < int64("10")
+return {
+"tweetid1": $t1.tweetid,
+"count1":$t1.countA,
+"t2info": for $t2 in dataset('TweetMessages')
+                        where $t1.tweetid /* +indexnl */= $t2.tweetid
+                        return {"tweetid2": $t2.tweetid,
+                                "count2": $t2.countB}
+}
+);

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-sidx2/probe-pidx-with-join-btree-sidx2.2.update.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-sidx2/probe-pidx-with-join-btree-sidx2.2.update.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-sidx2/probe-pidx-with-join-btree-sidx2.2.update.aql
index 36deb61..dc4fcdc 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-sidx2/probe-pidx-with-join-btree-sidx2.2.update.aql
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-sidx2/probe-pidx-with-join-btree-sidx2.2.update.aql
@@ -18,13 +18,13 @@
  */
 /*
  * 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                 
+ * Issue        : 730, 741
  * Expected Res : Success
  * Date         : 8th May 2014
  */
 
 use dataverse test;
 
-load dataset TweetMessages 
+load dataset TweetMessages
 using localfs
 (("path"="asterix_nc1://data/twitter/tw_for_indexleftouterjoin.adm"),("format"="adm"));

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-sidx2/probe-pidx-with-join-btree-sidx2.3.query.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-sidx2/probe-pidx-with-join-btree-sidx2.3.query.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-sidx2/probe-pidx-with-join-btree-sidx2.3.query.aql
index c8076df..d27955d 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-sidx2/probe-pidx-with-join-btree-sidx2.3.query.aql
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-sidx2/probe-pidx-with-join-btree-sidx2.3.query.aql
@@ -18,7 +18,7 @@
  */
 /*
  * 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                 
+ * Issue        : 730, 741
  * Expected Res : Success
  * Date         : 8th May 2014
  */
@@ -31,10 +31,10 @@ order by $t1.tweetid
 return {
 "tweetid1": $t1.tweetid,
 "count1":$t1.countA,
-"t2info": for $t2 in dataset('TweetMessages') 
+"t2info": for $t2 in dataset('TweetMessages')
                         where $t1.countA /* +indexnl */= $t2.countB and
                         $t1.tweetid != $t2.tweetid
-                        order by $t2.tweetid 
+                        order by $t2.tweetid
                         return {"tweetid2": $t2.tweetid,
                                        "count2":$t2.countB}
 };

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-with-join-btree-sidx3-indexonly-plan.1.ddl.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-with-join-btree-sidx3-indexonly-plan.1.ddl.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-with-join-btree-sidx3-indexonly-plan.1.ddl.aql
new file mode 100644
index 0000000..a8a8ce6
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-with-join-btree-sidx3-indexonly-plan.1.ddl.aql
@@ -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  : Test that left-outer-join may use two available indexes, one for primary index in prob subtree and
+ *                another for secondary index in index subtree. For inner branch, this is an index-only plan.
+ * Expected Res : Success
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type TwitterUserType as closed {
+    screen-name: string,
+    lang: string,
+    friends-count: int64,
+    statuses-count: int64,
+    name: string,
+    followers-count: int64
+}
+
+create type TweetMessageType as closed {
+    tweetid: int64,
+        user: TwitterUserType,
+        sender-location: point,
+    send-time: datetime,
+        referred-topics: {{ string }},
+    message-text: string,
+    countA: int64,
+    countB: int64
+}
+
+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 msgTextIx on TweetMessages(message-text) type keyword;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-with-join-btree-sidx3-indexonly-plan.2.update.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-with-join-btree-sidx3-indexonly-plan.2.update.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-with-join-btree-sidx3-indexonly-plan.2.update.aql
new file mode 100644
index 0000000..a045bf9
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-with-join-btree-sidx3-indexonly-plan.2.update.aql
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+use dataverse test;
+
+load dataset TweetMessages
+using localfs
+(("path"="asterix_nc1://data/twitter/tw_for_indexleftouterjoin.adm"),("format"="adm"));

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-with-join-btree-sidx3-indexonly-plan.3.query.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-with-join-btree-sidx3-indexonly-plan.3.query.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-with-join-btree-sidx3-indexonly-plan.3.query.aql
new file mode 100644
index 0000000..54ff9c6
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-with-join-btree-sidx3-indexonly-plan.3.query.aql
@@ -0,0 +1,32 @@
+/*
+ * 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.
+ */
+
+use dataverse test;
+
+count(
+for $t1 in dataset('TweetMessages')
+where $t1.tweetid < 10
+return {
+"tweetid1": $t1.tweetid,
+"count1":$t1.countA,
+"t2info": for $t2 in dataset('TweetMessages')
+                        where $t1.countA /* +indexnl */= $t2.countB
+                        return {"tweetid2": $t2.tweetid,
+                                "count2": $t2.countB}
+});

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1.1.ddl.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1.1.ddl.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1.1.ddl.aql
new file mode 100644
index 0000000..8c56ff4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1.1.ddl.aql
@@ -0,0 +1,57 @@
+/*
+ * 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 an available primary index in the index subtree.
+ *                In the probe side, this is an index-only plan since a secondary index-search
+ *                will fetch all fields that are required for the plan.
+ * Expected Res : Success
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type TwitterUserType as closed {
+    screen-name: string,
+    lang: string,
+    friends-count: int64,
+    statuses-count: int64,
+    name: string,
+    followers-count: int64
+}
+
+create type TweetMessageType as closed {
+    tweetid: int64,
+    user: TwitterUserType,
+    sender-location: point,
+    send-time: datetime,
+    referred-topics: {{ string }},
+    message-text: string,
+    countA: int64,
+    countB: int64
+}
+
+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 msgTextIx on TweetMessages(message-text) type keyword;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1.2.update.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1.2.update.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1.2.update.aql
new file mode 100644
index 0000000..a045bf9
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1.2.update.aql
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+use dataverse test;
+
+load dataset TweetMessages
+using localfs
+(("path"="asterix_nc1://data/twitter/tw_for_indexleftouterjoin.adm"),("format"="adm"));

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1.3.query.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1.3.query.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1.3.query.aql
new file mode 100644
index 0000000..87d68f6
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1.3.query.aql
@@ -0,0 +1,32 @@
+/*
+ * 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.
+ */
+
+use dataverse test;
+
+count(
+for $t1 in dataset('TweetMessages')
+where $t1.countA > 0
+return {
+"tweetid1": $t1.tweetid,
+"count1":$t1.countA,
+"t2info": for $t2 in dataset('TweetMessages')
+                        where $t1.countA /* +indexnl */= $t2.tweetid
+                        return {"tweetid2": $t2.tweetid,
+                                "count2": $t2.countB}
+});

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan.1.ddl.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan.1.ddl.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan.1.ddl.aql
new file mode 100644
index 0000000..c6f8bb4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan.1.ddl.aql
@@ -0,0 +1,60 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * Description  : Test that left-outer-join may use an available secondary index in the index subtree.
+ *                In the probe side, this is an index-only plan since a secondary index-search will
+ *                fetch all fields that are required for the plan.
+ *                In the inner branch, this is also an index-only plan since a secondary index-search will
+ *                fetch all fields that are required for the branch.
+ *                All other variables will be propagated from the outer branch.
+ * Expected Res : Success
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type TwitterUserType as closed {
+    screen-name: string,
+    lang: string,
+    friends-count: int64,
+    statuses-count: int64,
+    name: string,
+    followers-count: int64
+}
+
+create type TweetMessageType as closed {
+    tweetid: int64,
+    user: TwitterUserType,
+    sender-location: point,
+    send-time: datetime,
+    referred-topics: {{ string }},
+    message-text: string,
+    countA: int64,
+    countB: int64
+}
+
+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 msgTextIx on TweetMessages(message-text) type keyword;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan.2.update.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan.2.update.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan.2.update.aql
new file mode 100644
index 0000000..a045bf9
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan.2.update.aql
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+use dataverse test;
+
+load dataset TweetMessages
+using localfs
+(("path"="asterix_nc1://data/twitter/tw_for_indexleftouterjoin.adm"),("format"="adm"));

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan.3.query.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan.3.query.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan.3.query.aql
new file mode 100644
index 0000000..0d3f099
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan.3.query.aql
@@ -0,0 +1,32 @@
+/*
+ * 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.
+ */
+
+use dataverse test;
+
+count(
+for $t1 in dataset('TweetMessages')
+where $t1.countA > 0
+return {
+"tweetid1": $t1.tweetid,
+"count1":$t1.countA,
+"t2info": for $t2 in dataset('TweetMessages')
+                        where $t1.countA /* +indexnl */= $t2.countB
+                        return {"tweetid2": $t2.tweetid,
+                                "count2": $t2.countB}
+});

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1.1.ddl.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1.1.ddl.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1.1.ddl.aql
new file mode 100644
index 0000000..6241240
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1.1.ddl.aql
@@ -0,0 +1,57 @@
+/*
+ * 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 an available primary index in the index subtree.
+ *                In the probe side, this is a non index-only plan since a secondary index-search can't cover
+ *                all fields that are required for the plan.
+ * Expected Res : Success
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type TwitterUserType as closed {
+    screen-name: string,
+    lang: string,
+    friends-count: int64,
+    statuses-count: int64,
+    name: string,
+    followers-count: int64
+}
+
+create type TweetMessageType as closed {
+    tweetid: int64,
+    user: TwitterUserType,
+    sender-location: point,
+    send-time: datetime,
+    referred-topics: {{ string }},
+    message-text: string,
+    countA: int64,
+    countB: int64
+}
+
+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 msgTextIx on TweetMessages(message-text) type keyword;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1.2.update.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1.2.update.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1.2.update.aql
new file mode 100644
index 0000000..a045bf9
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1.2.update.aql
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+use dataverse test;
+
+load dataset TweetMessages
+using localfs
+(("path"="asterix_nc1://data/twitter/tw_for_indexleftouterjoin.adm"),("format"="adm"));

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1.3.query.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1.3.query.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1.3.query.aql
new file mode 100644
index 0000000..d1fc6d5
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1.3.query.aql
@@ -0,0 +1,32 @@
+/*
+ * 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.
+ */
+
+use dataverse test;
+
+count(
+for $t1 in dataset('TweetMessages')
+where $t1.countA > 0 and $t1.countB < 10000
+return {
+"tweetid1": $t1.tweetid,
+"count1":$t1.countA,
+"t2info": for $t2 in dataset('TweetMessages')
+                        where $t1.countA /* +indexnl */= $t2.tweetid
+                        return {"tweetid2": $t2.tweetid,
+                                "count2": $t2.countB}
+});

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan.1.ddl.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan.1.ddl.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan.1.ddl.aql
new file mode 100644
index 0000000..dc393a0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan.1.ddl.aql
@@ -0,0 +1,59 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * Description  : Test that left-outer-join may use an available secondary index in the index subtree.
+ *                In the probe side, this is a non index-only plan since a secondary index-search can't cover
+ *                all fields that are required for the plan.
+ *                In the inner branch, this is a index-only plan since a secondary index-search can cover
+ *                all fields that are required for that branch.
+ * Expected Res : Success
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type TwitterUserType as closed {
+    screen-name: string,
+    lang: string,
+    friends-count: int64,
+    statuses-count: int64,
+    name: string,
+    followers-count: int64
+}
+
+create type TweetMessageType as closed {
+    tweetid: int64,
+    user: TwitterUserType,
+    sender-location: point,
+    send-time: datetime,
+    referred-topics: {{ string }},
+    message-text: string,
+    countA: int64,
+    countB: int64
+}
+
+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 msgTextIx on TweetMessages(message-text) type keyword;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan.2.update.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan.2.update.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan.2.update.aql
new file mode 100644
index 0000000..a045bf9
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan.2.update.aql
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+use dataverse test;
+
+load dataset TweetMessages
+using localfs
+(("path"="asterix_nc1://data/twitter/tw_for_indexleftouterjoin.adm"),("format"="adm"));

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan.3.query.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan.3.query.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan.3.query.aql
new file mode 100644
index 0000000..28e2423
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan.3.query.aql
@@ -0,0 +1,32 @@
+/*
+ * 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.
+ */
+
+use dataverse test;
+
+count(
+for $t1 in dataset('TweetMessages')
+where $t1.countA > 0 and $t1.countB < 10000
+return {
+"tweetid1": $t1.tweetid,
+"count1":$t1.countA,
+"t2info": for $t2 in dataset('TweetMessages')
+                        where $t1.countA /* +indexnl */= $t2.countB
+                        return {"tweetid2": $t2.tweetid,
+                                "count2": $t2.countB}
+});

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-selection/btree-secondary-composite-index-indexonly-plan-01/btree-secondary-composite-index-indexonly-plan-01.1.ddl.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-selection/btree-secondary-composite-index-indexonly-plan-01/btree-secondary-composite-index-indexonly-plan-01.1.ddl.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-selection/btree-secondary-composite-index-indexonly-plan-01/btree-secondary-composite-index-indexonly-plan-01.1.ddl.aql
new file mode 100644
index 0000000..11a3605
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-selection/btree-secondary-composite-index-indexonly-plan-01/btree-secondary-composite-index-indexonly-plan-01.1.ddl.aql
@@ -0,0 +1,62 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ *  Description     : Secondary BTree Index index-only selection plan verification test
+ *                  : This test is intended to verify that the secondary BTree index is
+ *                  : used in the optimized query plan.
+ *                  : In this plan, we fetch PK and SK based on a select condition that utilizes a secondary index.
+ *                  : The plan should have two paths after the secondary index-lookup.
+ *                  : The left path:
+ *                      ... -> unnest-map (sidx) -> split -> unnest-map (pidx) -> select -> union -> ...
+ *                  : The right path:
+ *                      ... -> unnest-map (sidx) -> split ->                                union -> ...
+ *  Expected Result : Success
+ *
+*/
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type MyRecord as closed {
+  id: int64,
+  docid: int64,
+  val1: int64,
+  title: string,
+  point: point,
+  kwds: string,
+  line1: line,
+  line2: line,
+  poly1: polygon,
+  poly2: polygon,
+  rec: rectangle,
+  circle: circle
+}
+
+create dataset MyData(MyRecord)
+  primary key id;
+
+//create index btree_index_docid on MyData(docid) type btree;
+//create index btree_index_val1 on MyData(val1) type btree;
+create index btree_index_docid_val1 on MyData(docid,val1) type btree;
+create index rtree_index_point on MyData(point) type rtree;
+create index rtree_index_rec on MyData(rec) type rtree;
+create index ngram_index_title on MyData(title) type ngram(3);
+create index keyword_index_title on MyData(title) type keyword;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-selection/btree-secondary-composite-index-indexonly-plan-01/btree-secondary-composite-index-indexonly-plan-01.2.update.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-selection/btree-secondary-composite-index-indexonly-plan-01/btree-secondary-composite-index-indexonly-plan-01.2.update.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-selection/btree-secondary-composite-index-indexonly-plan-01/btree-secondary-composite-index-indexonly-plan-01.2.update.aql
new file mode 100644
index 0000000..9a7b2b7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-selection/btree-secondary-composite-index-indexonly-plan-01/btree-secondary-composite-index-indexonly-plan-01.2.update.aql
@@ -0,0 +1,25 @@
+/*
+ * 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.
+ */
+
+ use dataverse test;
+
+load dataset MyData
+using localfs
+(("path"="asterix_nc1://data/spatial/spatialData2.json"),("format"="adm"));
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-selection/btree-secondary-composite-index-indexonly-plan-01/btree-secondary-composite-index-indexonly-plan-01.3.query.aql
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-selection/btree-secondary-composite-index-indexonly-plan-01/btree-secondary-composite-index-indexonly-plan-01.3.query.aql b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-selection/btree-secondary-composite-index-indexonly-plan-01/btree-secondary-composite-index-indexonly-plan-01.3.query.aql
new file mode 100644
index 0000000..e9c6f51
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries/index-selection/btree-secondary-composite-index-indexonly-plan-01/btree-secondary-composite-index-indexonly-plan-01.3.query.aql
@@ -0,0 +1,25 @@
+/*
+ * 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.
+ */
+
+use dataverse test;
+
+for $o in dataset('MyData')
+where $o.docid < 3
+order by $o.id
+return {"pk":$o.id, "sk":$o.val1}


Mime
View raw message