http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-primary-to-secondary-indexonly-plan-equi-join_01/btree-primary-to-secondary-indexonly-plan-equi-join_01.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-primary-to-secondary-indexonly-plan-equi-join_01/btree-primary-to-secondary-indexonly-plan-equi-join_01.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-primary-to-secondary-indexonly-plan-equi-join_01/btree-primary-to-secondary-indexonly-plan-equi-join_01.1.ddl.sqlpp new file mode 100644 index 0000000..e4e4497 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-primary-to-secondary-indexonly-plan-equi-join_01/btree-primary-to-secondary-indexonly-plan-equi-join_01.1.ddl.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 : 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 Orders' secondary index. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + +create type AddressType as open { + number: int64, + street: string, + city: string +}; + +create type CustomerType as closed { + cid: int64, + name: string, + cashBack: int64, + age: int64?, + address: AddressType?, + lastorder: { + oid: int64, + total: float + } +}; + +create type OrderType as open { + oid: int64, + cid: int64, + orderstatus: string, + orderpriority: string, + clerk: string, + total: float, + items: [int64] +}; + +create dataset Customers(CustomerType) primary key cid; +create dataset Orders(OrderType) primary key oid; + +create index CustomerID_idx on Orders(cid); +
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-primary-to-secondary-indexonly-plan-equi-join_01/btree-primary-to-secondary-indexonly-plan-equi-join_01.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-primary-to-secondary-indexonly-plan-equi-join_01/btree-primary-to-secondary-indexonly-plan-equi-join_01.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-primary-to-secondary-indexonly-plan-equi-join_01/btree-primary-to-secondary-indexonly-plan-equi-join_01.2.update.sqlpp new file mode 100644 index 0000000..ddfd26b --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-primary-to-secondary-indexonly-plan-equi-join_01/btree-primary-to-secondary-indexonly-plan-equi-join_01.2.update.sqlpp @@ -0,0 +1,36 @@ +/* + * 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 Orders' secondary index. + * Success : Yes + */ + +use test; + +load dataset Customers +using localfs +(("path"="asterix_nc1://data/nontagged/customerData.json"),("format"="adm")); + +load dataset Orders +using localfs +(("path"="asterix_nc1://data/nontagged/orderData.json"),("format"="adm")); + http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-primary-to-secondary-indexonly-plan-equi-join_01/btree-primary-to-secondary-indexonly-plan-equi-join_01.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-primary-to-secondary-indexonly-plan-equi-join_01/btree-primary-to-secondary-indexonly-plan-equi-join_01.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-primary-to-secondary-indexonly-plan-equi-join_01/btree-primary-to-secondary-indexonly-plan-equi-join_01.3.query.sqlpp new file mode 100644 index 0000000..4bf06f0 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-primary-to-secondary-indexonly-plan-equi-join_01/btree-primary-to-secondary-indexonly-plan-equi-join_01.3.query.sqlpp @@ -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. + */ + +/* + * 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 Orders' secondary index. + * Success : Yes + */ + +use test; + +select value count(*) from ( +select o.oid, c.cid +from Customers c, Orders o +where c.cid /*+ indexnl */ = o.cid) a; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.1.ddl.sqlpp new file mode 100644 index 0000000..bb700a1 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.1.ddl.sqlpp @@ -0,0 +1,52 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +/* + * Description : Equi joins two datasets, DBLP and CSX, based on their authors. + * DBLP has a secondary btree index on authors, and given the 'indexnl' hint + * we expect the join to be transformed into an indexed nested-loop join. + * This is an index-only plan since we are using the + * secondary key field condition and returning only PK and SK fields. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + +create type DBLPType as closed { + id: int64, + dblpid: string, + title: string, + authors: string, + misc: string +}; + +create type CSXType as closed { + id: int64, + csxid: string, + title: string, + authors: string, + misc: string +}; + +create dataset DBLP(DBLPType) primary key id; +create dataset CSX(CSXType) primary key id; + http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.2.update.sqlpp new file mode 100644 index 0000000..e082c6e --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.2.update.sqlpp @@ -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 test; + +load dataset DBLP +using localfs +(("path"="asterix_nc1://data/pub-small/dblp-small-id.txt"),("format"="delimited-text"),("delimiter"=":")); + +load dataset CSX +using localfs +(("path"="asterix_nc1://data/pub-small/csx-small-id.txt"),("format"="delimited-text"),("delimiter"=":"),("quote"="\u0000")); + http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.3.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.3.ddl.sqlpp new file mode 100644 index 0000000..30aa05e --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.3.ddl.sqlpp @@ -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 test; + +create index authors_index on DBLP(authors); +create index authors_index on CSX(authors); + http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.4.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.4.query.sqlpp new file mode 100644 index 0000000..f1b2162 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.4.query.sqlpp @@ -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 test; + +select a.id as aid, b.id as bid, b.title +from DBLP a, CSX b +where a.authors /*+ indexnl */ = b.authors +order by aid, bid; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-primary-equi-join_01/btree-secondary-indexonly-plan-to-primary-equi-join_01.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-primary-equi-join_01/btree-secondary-indexonly-plan-to-primary-equi-join_01.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-primary-equi-join_01/btree-secondary-indexonly-plan-to-primary-equi-join_01.1.ddl.sqlpp new file mode 100644 index 0000000..c53da0e --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-primary-equi-join_01/btree-secondary-indexonly-plan-to-primary-equi-join_01.1.ddl.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 : 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. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + +create type AddressType as open { + number: int64, + street: string, + city: string +}; + +create type CustomerType as closed { + cid: int64, + name: string, + cashBack: int64, + age: int64?, + address: AddressType?, + lastorder: { + oid: int64, + total: float + } +}; + +create type OrderType as open { + oid: int64, + cid: int64, + orderstatus: string, + orderpriority: string, + clerk: string, + total: float, + items: [int64] +}; + +create dataset Customers(CustomerType) primary key cid; +create dataset Orders(OrderType) primary key oid; + +create index CustomerID_idx on Orders(cid); + http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-primary-equi-join_01/btree-secondary-indexonly-plan-to-primary-equi-join_01.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-primary-equi-join_01/btree-secondary-indexonly-plan-to-primary-equi-join_01.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-primary-equi-join_01/btree-secondary-indexonly-plan-to-primary-equi-join_01.2.update.sqlpp new file mode 100644 index 0000000..89bc827 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-primary-equi-join_01/btree-secondary-indexonly-plan-to-primary-equi-join_01.2.update.sqlpp @@ -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 test; + +load dataset Customers +using localfs +(("path"="asterix_nc1://data/nontagged/customerData.json"),("format"="adm")); + +load dataset Orders +using localfs +(("path"="asterix_nc1://data/nontagged/orderData.json"),("format"="adm")); + http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-primary-equi-join_01/btree-secondary-indexonly-plan-to-primary-equi-join_01.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-primary-equi-join_01/btree-secondary-indexonly-plan-to-primary-equi-join_01.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-primary-equi-join_01/btree-secondary-indexonly-plan-to-primary-equi-join_01.3.query.sqlpp new file mode 100644 index 0000000..494434a --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-primary-equi-join_01/btree-secondary-indexonly-plan-to-primary-equi-join_01.3.query.sqlpp @@ -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 test; + +select value count(*) from ( +select o.oid as oid, c.cid as cid +from Orders o, Customers c +where o.cid < 800 and o.cid /*+ indexnl */ = c.cid +) a; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.1.ddl.sqlpp new file mode 100644 index 0000000..9042f33 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.1.ddl.sqlpp @@ -0,0 +1,65 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +/* + * Description : 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 Orders' secondary index. + * Each branch (outer and inner) will be transformed as an index-only plan. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + +create type AddressType as open { + number: int64, + street: string, + city: string +}; + +create type CustomerType as closed { + cid: int64, + name: string, + cashBack: int64, + age: int64?, + address: AddressType?, + lastorder: { + oid: int64, + total: float + } +}; + +create type OrderType as open { + oid: int64, + cid: int64, + orderstatus: string, + orderpriority: string, + clerk: string, + total: float, + items: [int64] +}; + +create dataset Customers(CustomerType) primary key cid; +create dataset Orders(OrderType) primary key oid; + +create index CustomerID_idx on Orders(cid); +create index Cashback_idx on Customers(cashBack); http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.2.update.sqlpp new file mode 100644 index 0000000..dc5951f --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.2.update.sqlpp @@ -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 test; + +load dataset Customers +using localfs +(("path"="asterix_nc1://data/nontagged/customerData2.json"),("format"="adm")); + +load dataset Orders +using localfs +(("path"="asterix_nc1://data/nontagged/orderData.json"),("format"="adm")); + http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.3.query.sqlpp new file mode 100644 index 0000000..8e9417a --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.3.query.sqlpp @@ -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 test; + +select value count(*) from ( +select o.oid as oid, c.cid as cid +from Orders o, Customers c +where o.cid < 100000 and o.cid /*+ indexnl */ = c.cashBack +) a; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-secondary-non-indexonly-plan-to-primary-equi-join_01.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-secondary-non-indexonly-plan-to-primary-equi-join_01.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-secondary-non-indexonly-plan-to-primary-equi-join_01.1.ddl.sqlpp new file mode 100644 index 0000000..fdc6a6c --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-secondary-non-indexonly-plan-to-primary-equi-join_01.1.ddl.sqlpp @@ -0,0 +1,66 @@ +/* + * 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. + * This is not an index-only plan since a single secondary index can't + * cover the whole search predicate for the outer relation even when we exclude the join condition. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + +create type AddressType as open { + number: int64, + street: string, + city: string +}; + +create type CustomerType as closed { + cid: int64, + name: string, + cashBack: int64, + age: int64?, + address: AddressType?, + lastorder: { + oid: int64, + total: float + } +}; + +create type OrderType as open { + oid: int64, + cid: int64, + orderstatus: string, + orderpriority: string, + clerk: string, + total: float, + items: [int64] +}; + +create dataset Customers(CustomerType) primary key cid; +create dataset Orders(OrderType) primary key oid; + +create index CustomerID_idx on Orders(cid); + http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-secondary-non-indexonly-plan-to-primary-equi-join_01.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-secondary-non-indexonly-plan-to-primary-equi-join_01.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-secondary-non-indexonly-plan-to-primary-equi-join_01.2.update.sqlpp new file mode 100644 index 0000000..89bc827 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-secondary-non-indexonly-plan-to-primary-equi-join_01.2.update.sqlpp @@ -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 test; + +load dataset Customers +using localfs +(("path"="asterix_nc1://data/nontagged/customerData.json"),("format"="adm")); + +load dataset Orders +using localfs +(("path"="asterix_nc1://data/nontagged/orderData.json"),("format"="adm")); + http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-secondary-non-indexonly-plan-to-primary-equi-join_01.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-secondary-non-indexonly-plan-to-primary-equi-join_01.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-secondary-non-indexonly-plan-to-primary-equi-join_01.3.query.sqlpp new file mode 100644 index 0000000..b1160bb --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-secondary-non-indexonly-plan-to-primary-equi-join_01.3.query.sqlpp @@ -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 test; + +select value count(*) from ( +select o.oid as oid, c.cid as cid +from Orders o, Customers c +where o.cid < 800 and o.total < 10000 and o.cid /*+ indexnl */ = c.cid +) a; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.1.ddl.sqlpp new file mode 100644 index 0000000..3e1d2cb --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.1.ddl.sqlpp @@ -0,0 +1,67 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +/* + * Description : 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 Orders' secondary index. + * Inner branch will be transformed as an index-only plan. + * Outer branch cannot be transformed as an index-only plan as an index can't cover + * all search predicates even excluding the join condition. + * Success : Yes + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + +create type AddressType as open { + number: int64, + street: string, + city: string +}; + +create type CustomerType as closed { + cid: int64, + name: string, + cashBack: int64, + age: int64?, + address: AddressType?, + lastorder: { + oid: int64, + total: float + } +}; + +create type OrderType as open { + oid: int64, + cid: int64, + orderstatus: string, + orderpriority: string, + clerk: string, + total: float, + items: [int64] +}; + +create dataset Customers(CustomerType) primary key cid; +create dataset Orders(OrderType) primary key oid; + +create index CustomerID_idx on Orders(cid); +create index Cashback_idx on Customers(cashBack); http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.2.update.sqlpp new file mode 100644 index 0000000..dc5951f --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.2.update.sqlpp @@ -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 test; + +load dataset Customers +using localfs +(("path"="asterix_nc1://data/nontagged/customerData2.json"),("format"="adm")); + +load dataset Orders +using localfs +(("path"="asterix_nc1://data/nontagged/orderData.json"),("format"="adm")); + http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.3.query.sqlpp new file mode 100644 index 0000000..63c94d1 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.3.query.sqlpp @@ -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 test; + +select value count(*) from ( +select o.oid as oid, c.cid as cid +from Orders o, Customers c +where o.cid < 100000 and o.total >= 0 and o.cid /*+ indexnl */ = c.cashBack +) a; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.1.ddl.sqlpp deleted file mode 100644 index bde7f36..0000000 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.1.ddl.sqlpp +++ /dev/null @@ -1,48 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -/* - * Description : Joins two datasets on the intersection of their point attributes. - * The dataset 'MyData1' 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 test; - - -create type test.MyRecord as - closed { - id : bigint, - 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_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.2.update.sqlpp deleted file mode 100644 index 21b9b96..0000000 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.2.update.sqlpp +++ /dev/null @@ -1,32 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -/* - * Description : Joins two datasets on the intersection of their point attributes. - * The dataset 'MyData1' has an RTree index, and we expect the - * join to be transformed into an indexed nested-loop join. - * Success : Yes - */ - -use 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_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.3.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.3.ddl.sqlpp deleted file mode 100644 index 01943c7..0000000 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.3.ddl.sqlpp +++ /dev/null @@ -1,30 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -/* - * Description : Joins two datasets on the intersection of their point attributes. - * The dataset 'MyData1' has an RTree index, and we expect the - * join to be transformed into an indexed nested-loop join. - * Success : Yes - */ - -use test; - - -create index rtree_index on MyData1 (point) type rtree; - http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.4.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.4.query.sqlpp deleted file mode 100644 index 0cf19a3..0000000 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.4.query.sqlpp +++ /dev/null @@ -1,34 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -/* - * Description : Joins two datasets on the intersection of their point attributes. - * The dataset 'MyData1' has an RTree index, and we expect the - * join to be transformed into an indexed nested-loop join. - * Success : Yes - */ - -use test; - - -select element {'aid':a.id,'bid':b.id,'apt':a.point,'bp':b.point} -from MyData1 as a, - MyData2 as b -where (test.`spatial-intersect`(a.point,b.point) and (a.id != b.id)) -order by a.id,b.id -; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.1.ddl.sqlpp new file mode 100644 index 0000000..bde7f36 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.1.ddl.sqlpp @@ -0,0 +1,48 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Joins two datasets on the intersection of their point attributes. + * The dataset 'MyData1' 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 test; + + +create type test.MyRecord as + closed { + id : bigint, + 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_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.2.update.sqlpp new file mode 100644 index 0000000..21b9b96 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.2.update.sqlpp @@ -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. + */ +/* + * Description : Joins two datasets on the intersection of their point attributes. + * The dataset 'MyData1' has an RTree index, and we expect the + * join to be transformed into an indexed nested-loop join. + * Success : Yes + */ + +use 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_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.3.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.3.ddl.sqlpp new file mode 100644 index 0000000..01943c7 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.3.ddl.sqlpp @@ -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 dataset 'MyData1' has an RTree index, and we expect the + * join to be transformed into an indexed nested-loop join. + * Success : Yes + */ + +use test; + + +create index rtree_index on MyData1 (point) type rtree; + http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.4.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.4.query.sqlpp new file mode 100644 index 0000000..0cf19a3 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.4.query.sqlpp @@ -0,0 +1,34 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Joins two datasets on the intersection of their point attributes. + * The dataset 'MyData1' has an RTree index, and we expect the + * join to be transformed into an indexed nested-loop join. + * Success : Yes + */ + +use test; + + +select element {'aid':a.id,'bid':b.id,'apt':a.point,'bp':b.point} +from MyData1 as a, + MyData2 as b +where (test.`spatial-intersect`(a.point,b.point) and (a.id != b.id)) +order by a.id,b.id +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.1.ddl.sqlpp new file mode 100644 index 0000000..65cd32e --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.1.ddl.sqlpp @@ -0,0 +1,46 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +/* + * Description : 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 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_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.2.update.sqlpp new file mode 100644 index 0000000..9116663 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.2.update.sqlpp @@ -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 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_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.3.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.3.ddl.sqlpp new file mode 100644 index 0000000..a9f8edc --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.3.ddl.sqlpp @@ -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 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_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.4.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.4.query.sqlpp new file mode 100644 index 0000000..e43f3d2 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.4.query.sqlpp @@ -0,0 +1,34 @@ +/* + * 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 test; + +select element {'aid':a.id,'bid':b.id,'apoint':a.point,'bpoint':b.point} +from MyData1 as a, + MyData2 as b +where test.`spatial-intersect`(a.point,b.point) +order by a.id,b.id +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.1.ddl.sqlpp new file mode 100644 index 0000000..e004040 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.1.ddl.sqlpp @@ -0,0 +1,47 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + + /* + * Description : 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 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_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.2.update.sqlpp new file mode 100644 index 0000000..9116663 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.2.update.sqlpp @@ -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 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_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.3.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.3.ddl.sqlpp new file mode 100644 index 0000000..0595f2d --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.3.ddl.sqlpp @@ -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 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_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.4.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.4.query.sqlpp new file mode 100644 index 0000000..a241f6c --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.4.query.sqlpp @@ -0,0 +1,27 @@ +/* + * 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 test; + +select element {'aid':a.id,'bid':b.id,'apoint':a.point,'bpoint':b.point} +from MyData1 as a, + MyData2 as b +where test.`spatial-intersect`(a.point,b.point) +order by a.id,b.id +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.1.ddl.sqlpp new file mode 100644 index 0000000..94d2c2d --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.1.ddl.sqlpp @@ -0,0 +1,47 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +/* + * Description : 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 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_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.2.update.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.2.update.sqlpp new file mode 100644 index 0000000..9116663 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.2.update.sqlpp @@ -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 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_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.3.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.3.ddl.sqlpp new file mode 100644 index 0000000..9506862 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.3.ddl.sqlpp @@ -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 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_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.4.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.4.query.sqlpp new file mode 100644 index 0000000..b3d9221 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.4.query.sqlpp @@ -0,0 +1,27 @@ +/* + * 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 test; + +select element {'aid':a.id,'bid':b.id,'apoly1':a.poly1,'bpoint':b.point} +from MyData1 as a, + MyData2 as b +where test.`spatial-intersect`(a.poly1,b.point) +order by a.id,b.id +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.1.ddl.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.1.ddl.sqlpp new file mode 100644 index 0000000..94d2c2d --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.1.ddl.sqlpp @@ -0,0 +1,47 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +/* + * Description : 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 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; +
