http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-08_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-08_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-08_ps.sqlpp new file mode 100644 index 0000000..a82fe5b --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-08_ps.sqlpp @@ -0,0 +1,47 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Index join because there's a hint and the probe type is known (string). p_sort enabled. + * Expected Res : Success + * Date : 20 Jun 2017 + */ +drop dataverse test if exists; +create dataverse test; +use test; + +write output to asterix_nc1:"rttest/btree-equi-join-non-enforced_btree-equi-join-non-enforced-08.adm"; + +create type TestOpenType as open { + c_id: int64 +}; + +create dataset TestOpen1(TestOpenType) primary key c_id; +create dataset TestOpen2(TestOpenType) primary key c_id; +create index idx_t2_s on TestOpen2(c_s:string); +create index idx_t2_i64 on TestOpen2(c_i64:int64); +create index idx_t2_i8 on TestOpen2(c_i8:int8); +create index idx_t2_d on TestOpen2(c_d:double); + +set `compiler.sort.parallel` "true"; + + +select t1.c_x as c1, t2.c_x as c2 +from TestOpen1 as t1, TestOpen2 as t2 +where to_string(t1.c_s) /*+ indexnl */ = t2.c_s +order by t1.c_x, t2.c_x;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-09_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-09_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-09_ps.sqlpp new file mode 100644 index 0000000..759ce3a --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-09_ps.sqlpp @@ -0,0 +1,46 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Index join because there's a hint and the probe type is known (bigint). p_sort enabled. + * Expected Res : Success + * Date : 20 Jun 2017 + */ +drop dataverse test if exists; +create dataverse test; +use test; + +write output to asterix_nc1:"rttest/btree-equi-join-non-enforced_btree-equi-join-non-enforced-08.adm"; + +create type TestOpenType as open { + c_id: int64 +}; + +create dataset TestOpen1(TestOpenType) primary key c_id; +create dataset TestOpen2(TestOpenType) primary key c_id; +create index idx_t2_s on TestOpen2(c_s:string); +create index idx_t2_i64 on TestOpen2(c_i64:int64); +create index idx_t2_i8 on TestOpen2(c_i8:int8); +create index idx_t2_d on TestOpen2(c_d:double); + +set `compiler.sort.parallel` "true"; + +select t1.c_x as c1, t2.c_x as c2 +from TestOpen1 as t1, TestOpen2 as t2 +where to_bigint(t1.c_i64) /*+ indexnl */ = t2.c_i64 +order by t1.c_x, t2.c_x; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-04_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-04_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-04_ps.sqlpp new file mode 100644 index 0000000..1b585b6 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-04_ps.sqlpp @@ -0,0 +1,44 @@ +/* + * 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 : BTree non-enforced index verification test + * : Test that the secondary non-enforced BTree index is used. p_sort enabled. + * Expected Result : Success + * Date : 12th Jun 2017 + */ +drop dataverse test if exists; +create dataverse test; +use test; + +write output to asterix_nc1:"rttest/btree-index-non-enforced_btree-index-non-enforced-04.adm"; + +create type TestOpenType as open { + c_id: int64 +}; + +create dataset TestOpen(TestOpenType) primary key c_id; +create index idx_s on TestOpen(c_s:string); + + +set `compiler.sort.parallel` "true"; + +select t.c_x as res +from TestOpen t +where t.c_s = 'world' +order by t.c_x; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-05_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-05_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-05_ps.sqlpp new file mode 100644 index 0000000..b14b9d6 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-05_ps.sqlpp @@ -0,0 +1,44 @@ +/* + * 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 : BTree non-enforced index verification test + * : Test that the secondary non-enforced BTree index is used. p_sort enabled. + * Expected Result : Success + * Date : 12th Jun 2017 + */ + +drop dataverse test if exists; +create dataverse test; +use test; + +write output to asterix_nc1:"rttest/btree-index-non-enforced_btree-index-non-enforced-05.adm"; + +create type TestOpenType as open { + c_id: int64 +}; + +create dataset TestOpen(TestOpenType) primary key c_id; +create index idx_i64 on TestOpen(c_i64:int64); + +set `compiler.sort.parallel` "true"; + +select t.c_x as res +from TestOpen t +where t.c_i64 = 2 +order by t.c_x; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-06_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-06_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-06_ps.sqlpp new file mode 100644 index 0000000..570c37e --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-06_ps.sqlpp @@ -0,0 +1,44 @@ +/* + * 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 : BTree non-enforced index verification test + * : Test that the secondary non-enforced BTree index is used. p_sort enabled. + * Expected Result : Success + * Date : 12th Jun 2017 + */ + +drop dataverse test if exists; +create dataverse test; +use test; + +write output to asterix_nc1:"rttest/btree-index-non-enforced_btree-index-non-enforced-06.adm"; + +create type TestOpenType as open { + c_id: int64 +}; + +create dataset TestOpen(TestOpenType) primary key c_id; +create index idx_i64 on TestOpen(c_i64:int64); + +set `compiler.sort.parallel` "true"; + +select t.c_x as res +from TestOpen t +where t.c_i64 > 2 +order by t.c_x; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-07_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-07_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-07_ps.sqlpp new file mode 100644 index 0000000..ffb1cf8 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-07_ps.sqlpp @@ -0,0 +1,44 @@ +/* + * 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 : BTree non-enforced index verification test + * : Test that the secondary non-enforced BTree index is used. p_sort enabled. + * Expected Result : Success + * Date : 12th Jun 2017 + */ + +drop dataverse test if exists; +create dataverse test; +use test; + +write output to asterix_nc1:"rttest/btree-index-non-enforced_btree-index-non-enforced-07.adm"; + +create type TestOpenType as open { + c_id: int64 +}; + +create dataset TestOpen(TestOpenType) primary key c_id; +create index idx_i64 on TestOpen(c_i64:int64); + +set `compiler.sort.parallel` "true"; + +select t.c_x as res +from TestOpen t +where t.c_i64 > 2.0 +order by t.c_x; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-08_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-08_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-08_ps.sqlpp new file mode 100644 index 0000000..6de6121 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-08_ps.sqlpp @@ -0,0 +1,44 @@ +/* + * 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 : BTree non-enforced index verification test + * : Test that the secondary non-enforced BTree index is used. p_sort enabled. + * Expected Result : Success + * Date : 12th Jun 2017 + */ + +drop dataverse test if exists; +create dataverse test; +use test; + +write output to asterix_nc1:"rttest/btree-index-non-enforced_btree-index-non-enforced-08.adm"; + +create type TestOpenType as open { + c_id: int64 +}; + +create dataset TestOpen(TestOpenType) primary key c_id; +create index idx_i8 on TestOpen(c_i8:int8); + +set `compiler.sort.parallel` "true"; + +select t.c_x as res +from TestOpen t +where t.c_i8 > 2 +order by t.c_x; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-09_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-09_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-09_ps.sqlpp new file mode 100644 index 0000000..3f1a7847 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-09_ps.sqlpp @@ -0,0 +1,44 @@ +/* + * 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 : BTree non-enforced index verification test + * : Test that the secondary non-enforced BTree index is used. p_sort enabled. + * Expected Result : Success + * Date : 12th Jun 2017 + */ + +drop dataverse test if exists; +create dataverse test; +use test; + +write output to asterix_nc1:"rttest/btree-index-non-enforced_btree-index-non-enforced-09.adm"; + +create type TestOpenType as open { + c_id: int64 +}; + +create dataset TestOpen(TestOpenType) primary key c_id; +create index idx_i8 on TestOpen(c_i8:int8); + +set `compiler.sort.parallel` "true"; + +select t.c_x as res +from TestOpen t +where t.c_i8 > 2.5 +order by t.c_x; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-105_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-105_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-105_ps.sqlpp new file mode 100644 index 0000000..6049e5f --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-105_ps.sqlpp @@ -0,0 +1,46 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + /* + * Description : BTree non-enforced index verification test + * : Test that the secondary non-enforced BTree index is used. p_sort enabled. + * : when multiple indexes match + * Expected Result : Success + * Date : 29 Jun 2017 + */ + +drop dataverse test if exists; +create dataverse test; +use test; + +write output to asterix_nc1:"rttest/btree-index-non-enforced_btree-index-non-enforced-104.adm"; + +create type TestOpenType as open { + c_id: int64 +}; + +create dataset TestOpen(TestOpenType) primary key c_id; +create index idx_3 on TestOpen(c_i64:int64); +create index idx_4 on TestOpen(c_i8:int8); + +set `compiler.sort.parallel` "true"; + +select t.c_x as res +from TestOpen t +where t.c_i8 > 2 and t.c_i64 < 3 +order by t.c_x; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-10_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-10_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-10_ps.sqlpp new file mode 100644 index 0000000..afb08ca --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-10_ps.sqlpp @@ -0,0 +1,44 @@ +/* + * 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 : BTree non-enforced index verification test + * : Test that the secondary non-enforced BTree index is used. p_sort enabled. + * Expected Result : Success + * Date : 12th Jun 2017 + */ + +drop dataverse test if exists; +create dataverse test; +use test; + +write output to asterix_nc1:"rttest/btree-index-non-enforced_btree-index-non-enforced-10.adm"; + +create type TestOpenType as open { + c_id: int64 +}; + +create dataset TestOpen(TestOpenType) primary key c_id; +create index idx_d on TestOpen(c_d:double); + +set `compiler.sort.parallel` "true"; + +select t.c_x as res +from TestOpen t +where t.c_d >= 3.25 +order by t.c_x; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-11_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-11_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-11_ps.sqlpp new file mode 100644 index 0000000..b921fa3 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-index-non-enforced/btree-index-non-enforced-11_ps.sqlpp @@ -0,0 +1,44 @@ +/* + * 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 : BTree non-enforced index verification test + * : Test that the secondary non-enforced BTree index is used. p_sort enabled. + * Expected Result : Success + * Date : 12th Jun 2017 + */ + +drop dataverse test if exists; +create dataverse test; +use test; + +write output to asterix_nc1:"rttest/btree-index-non-enforced_btree-index-non-enforced-11.adm"; + +create type TestOpenType as open { + c_id: int64 +}; + +create dataset TestOpen(TestOpenType) primary key c_id; +create index idx_i8 on TestOpen(c_i8:int8); + +set `compiler.sort.parallel` "true"; + +select t.c_x as res +from TestOpen t +where t.c_i8 > 499 and t.c_i8 < 99999 +order by t.c_x; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orderby-desc-using-gby_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orderby-desc-using-gby_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orderby-desc-using-gby_ps.sqlpp new file mode 100644 index 0000000..254bd26 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orderby-desc-using-gby_ps.sqlpp @@ -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. + */ + +drop dataverse `gby-using-orderby-desc` if exists; +create dataverse `gby-using-orderby-desc`; + +use `gby-using-orderby-desc`; + + +create type `gby-using-orderby-desc`.AddressType as + closed { + number : integer, + street : string, + city : string +}; + +create type `gby-using-orderby-desc`.CustomerType as + closed { + cid : integer, + name : string, + age : integer?, + address : AddressType?, + lastorder : { + oid : integer, + total : float + } + +}; + +create nodegroup group1 if not exists on + asterix_nc1, + asterix_nc2 +; +create dataset Customers(CustomerType) primary key cid on group1; + +write output to asterix_nc1:"rttest/gby-using-orderby-desc.adm"; +set `compiler.sort.parallel` "true"; + +select element {'name':name,'age':age} +from Customers as c +group by c.name as name, c.age as age +order by name desc,age +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive-open_01_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive-open_01_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive-open_01_ps.sqlpp new file mode 100644 index 0000000..7764086 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive-open_01_ps.sqlpp @@ -0,0 +1,56 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +drop dataverse tpch if exists; +create dataverse tpch; + +use tpch; + + +create type tpch.OrderType as +{ + o_orderkey : integer, + o_custkey : integer, + o_orderstatus : string, + o_totalprice : double, + o_orderdate : string, + o_orderpriority : string, + o_clerk : string, + o_shippriority : integer, + o_comment : string +}; + +create nodegroup group1 if not exists on + asterix_nc1, + asterix_nc2 +; +create dataset Orders(OrderType) primary key o_orderkey on group1; + +load dataset Orders using localfs ((`path`=`asterix_nc1://data/tpch0.001/orders.tbl`),(`format`=`delimited-text`),(`delimiter`=`|`)) pre-sorted; + +create index idx_Orders_Custkey on Orders (o_custkey) type btree; + +write output to asterix_nc1:"/tmp/index_search-conjunctive.adm"; +set `compiler.sort.parallel` "true"; + +select element {'o_orderkey':o.o_orderkey,'o_custkey':o.o_custkey} +from Orders as o +where ((o.o_custkey = 40) and (o.o_totalprice > 150000.0)) +order by o.o_orderkey +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive-open_02_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive-open_02_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive-open_02_ps.sqlpp new file mode 100644 index 0000000..3814e1c --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive-open_02_ps.sqlpp @@ -0,0 +1,56 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +drop dataverse tpch if exists; +create dataverse tpch; + +use tpch; + + +create type tpch.OrderType as +{ + o_orderkey : integer, + o_custkey : integer, + o_orderstatus : string, + o_totalprice : double, + o_orderdate : string, + o_orderpriority : string, + o_clerk : string, + o_shippriority : integer, + o_comment : string +}; + +create nodegroup group1 if not exists on + asterix_nc1, + asterix_nc2 +; +create dataset Orders(OrderType) primary key o_orderkey on group1; + +load dataset Orders using localfs ((`path`=`asterix_nc1://data/tpch0.001/orders.tbl`),(`format`=`delimited-text`),(`delimiter`=`|`)) pre-sorted; + +create index idx_Orders_Custkey on Orders (o_custkey) type btree; + +write output to asterix_nc1:"/tmp/index_search-conjunctive.adm"; +set `compiler.sort.parallel` "true"; + +select element {'o_orderkey':o.o_orderkey,'o_custkey':o.o_custkey,'o_totalprice':o.o_totalprice} +from Orders as o +where ((o.o_custkey = 40) and (o.o_totalprice > 150000.0)) +order by o.o_orderkey +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_01_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_01_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_01_ps.sqlpp new file mode 100644 index 0000000..80cec87 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_01_ps.sqlpp @@ -0,0 +1,56 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +drop dataverse tpch if exists; +create dataverse tpch; + +use tpch; + + +create type tpch.OrderType as + closed { + o_orderkey : integer, + o_custkey : integer, + o_orderstatus : string, + o_totalprice : double, + o_orderdate : string, + o_orderpriority : string, + o_clerk : string, + o_shippriority : integer, + o_comment : string +}; + +create nodegroup group1 if not exists on + asterix_nc1, + asterix_nc2 +; +create dataset Orders(OrderType) primary key o_orderkey on group1; + +load dataset Orders using localfs ((`path`=`asterix_nc1://data/tpch0.001/orders.tbl`),(`format`=`delimited-text`),(`delimiter`=`|`)) pre-sorted; + +create index idx_Orders_Custkey on Orders (o_custkey) type btree; + +write output to asterix_nc1:"/tmp/index_search-conjunctive.adm"; +set `compiler.sort.parallel` "true"; + +select element {'o_orderkey':o.o_orderkey,'o_custkey':o.o_custkey} +from Orders as o +where ((o.o_custkey = 40) and (o.o_totalprice > 150000.0)) +order by o.o_orderkey +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_02_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_02_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_02_ps.sqlpp new file mode 100644 index 0000000..de4604e --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_02_ps.sqlpp @@ -0,0 +1,56 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +drop dataverse tpch if exists; +create dataverse tpch; + +use tpch; + + +create type tpch.OrderType as + closed { + o_orderkey : integer, + o_custkey : integer, + o_orderstatus : string, + o_totalprice : double, + o_orderdate : string, + o_orderpriority : string, + o_clerk : string, + o_shippriority : integer, + o_comment : string +}; + +create nodegroup group1 if not exists on + asterix_nc1, + asterix_nc2 +; +create dataset Orders(OrderType) primary key o_orderkey on group1; + +load dataset Orders using localfs ((`path`=`asterix_nc1://data/tpch0.001/orders.tbl`),(`format`=`delimited-text`),(`delimiter`=`|`)) pre-sorted; + +create index idx_Orders_Custkey on Orders (o_custkey) type btree; + +write output to asterix_nc1:"/tmp/index_search-conjunctive.adm"; +set `compiler.sort.parallel` "true"; + +select element {'o_orderkey':o.o_orderkey,'o_custkey':o.o_custkey,'o_totalprice':o.o_totalprice} +from Orders as o +where ((o.o_custkey = 40) and (o.o_totalprice > 150000.0)) +order by o.o_orderkey +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/parallel_sort_enabled_disabled/parallel_sort_enabled_disabled.1.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/parallel_sort_enabled_disabled/parallel_sort_enabled_disabled.1.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/parallel_sort_enabled_disabled/parallel_sort_enabled_disabled.1.sqlpp new file mode 100644 index 0000000..271565a --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/parallel_sort_enabled_disabled/parallel_sort_enabled_disabled.1.sqlpp @@ -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 enabling parallel sort by setting the property +* Expected Res : Success +*/ + +DROP DATAVERSE test IF EXISTS; +CREATE DATAVERSE test; +USE test; + +CREATE TYPE CustomerType AS { + c_custkey : integer, + c_1 : integer +}; + +CREATE DATASET Customers(CustomerType) PRIMARY KEY c_custkey; + +SET `compiler.sort.parallel` "true"; + +SELECT VALUE c +FROM Customers c +ORDER BY c.c_1; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/parallel_sort_enabled_disabled/parallel_sort_enabled_disabled.2.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/parallel_sort_enabled_disabled/parallel_sort_enabled_disabled.2.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/parallel_sort_enabled_disabled/parallel_sort_enabled_disabled.2.sqlpp new file mode 100644 index 0000000..e79e872 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/parallel_sort_enabled_disabled/parallel_sort_enabled_disabled.2.sqlpp @@ -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 disabling parallel sort by setting the property +* Expected Res : Success +*/ + +DROP DATAVERSE test IF EXISTS; +CREATE DATAVERSE test; +USE test; + +CREATE TYPE CustomerType AS { + c_custkey : integer, + c_1 : integer +}; + +CREATE DATASET Customers(CustomerType) PRIMARY KEY c_custkey; + +SET `compiler.sort.parallel` "false"; + +SELECT VALUE c +FROM Customers c +ORDER BY c.c_1; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/q01_pricing_summary_report_nt_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/q01_pricing_summary_report_nt_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/q01_pricing_summary_report_nt_ps.sqlpp new file mode 100644 index 0000000..3ee5774 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/q01_pricing_summary_report_nt_ps.sqlpp @@ -0,0 +1,80 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +drop dataverse tpch if exists; +create dataverse tpch; + +use tpch; + + +create type tpch.LineItemType as + closed { + l_orderkey : integer, + l_partkey : integer, + l_suppkey : integer, + l_linenumber : integer, + l_quantity : double, + l_extendedprice : double, + l_discount : double, + l_tax : double, + l_returnflag : string, + l_linestatus : string, + l_shipdate : string, + l_commitdate : string, + l_receiptdate : string, + l_shipinstruct : string, + l_shipmode : string, + l_comment : string +}; + +create dataset LineItem(LineItemType) primary key l_orderkey,l_linenumber; + +load dataset LineItem using localfs ((`path`=`asterix_nc1://data/tpch0.001/lineitem.tbl`),(`format`=`delimited-text`),(`delimiter`=`|`)) pre-sorted; + +write output to asterix_nc1:"rttest/tpch_q1_pricing_summary_report_nt.adm"; +set `compiler.sort.parallel` "true"; + +select element {'l_returnflag':l_returnflag,'l_linestatus':l_linestatus,'sum_qty':tpch.strict_sum(( + select element i.l.l_quantity + from g as i + )),'sum_base_price':tpch.strict_sum(( + select element i.l.l_extendedprice + from g as i + )),'sum_disc_price':tpch.strict_sum(( + select element (i.l.l_extendedprice * (1 - i.l.l_discount)) + from g as i + )),'sum_charge':tpch.strict_sum(( + select element (i.l.l_extendedprice * (1 - i.l.l_discount) * (1 + i.l.l_tax)) + from g as i + )),'ave_qty':tpch.strict_avg(( + select element i.l.l_quantity + from g as i + )),'ave_price':tpch.strict_avg(( + select element i.l.l_extendedprice + from g as i + )),'ave_disc':tpch.strict_avg(( + select element i.l.l_discount + from g as i + )),'count_order':tpch.strict_count(g)} +from LineItem as l +where (l.l_shipdate <= '1998-09-02') +/* +hash */ +group by l.l_returnflag as l_returnflag,l.l_linestatus as l_linestatus group as g +order by l_returnflag,l_linestatus +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-1806_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-1806_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-1806_ps.sqlpp new file mode 100644 index 0000000..28e60df --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-1806_ps.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 : This test case is to verify the fix for issue 1806. p_sort enabled. + * https://issues.apache.org/jira/browse/ASTERIXDB-1806 + * Expected Res : Non-index utilization Plan + */ +drop dataverse tpch if exists; +create dataverse tpch; +use tpch; + +create type LineItemType as closed { + l_orderkey : integer, + l_partkey : integer, + l_suppkey : integer, + l_linenumber : integer, + l_quantity : double, + l_extendedprice : double, + l_discount : double, + l_tax : double, + l_returnflag : string, + l_linestatus : string, + l_shipdate : string, + l_commitdate : string, + l_receiptdate : string, + l_shipinstruct : string, + l_shipmode : string, + l_comment : string +}; + +create dataset LineItem(LineItemType) primary key l_orderkey,l_linenumber; + +SET `import-private-functions` "true"; +SET `compiler.sort.parallel` "true"; + +SELECT l_returnflag, + l_linestatus, + sum(l_quantity) AS sum_qty, + sum(l_extendedprice) AS sum_base_price, + sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price, + sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge, + avg(l_quantity) AS ave_qty, + avg(l_extendedprice) AS ave_price, + avg(l_discount) AS ave_disc, + count(1) AS count_order +FROM LineItem l +WHERE inject_failure(l.l_shipdate <= '1998-09-02', l.l_orderkey=5988) +GROUP BY l_returnflag, l_linestatus +ORDER BY l_returnflag, l_linestatus; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-2354_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-2354_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-2354_ps.sqlpp new file mode 100644 index 0000000..722afae --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-2354_ps.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. + */ + +set `compiler.sort.parallel` "true"; + +SELECT ds.DatasetName as v1 +FROM Metadata.`Dataset` ds +WHERE ds.DatasetName LIKE "Orders%" +UNION ALL +SELECT ds.DatasetName v1, idx.DatasetName v2, idx.IndexName v3 +FROM Metadata.`Index` idx, Metadata.`Dataset` ds +WHERE ds.DatasetName LIKE "Orders%" and idx.DatasetName LIKE "Orders%" +ORDER BY v1, v2, v3; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-2408_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-2408_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-2408_ps.sqlpp new file mode 100644 index 0000000..b542ec7 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-2408_ps.sqlpp @@ -0,0 +1,45 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +/* + * Description : This test case is to verify the fix for issue ASTERIXDB-2408. p_sort enabled. + * https://issues.apache.org/jira/browse/ASTERIXDB-2408 + * Expected Res : + */ + +drop dataverse test if exists; +create dataverse test; +use test; + +create type testType as open { + id: int32 +}; + +create dataset customers(testType) primary key id; +create dataset orders(testType) primary key id; + +set `compiler.sort.parallel` "true"; + +SELECT c.name, ( + SELECT VALUE o.id + FROM orders o + WHERE o.customer_id = c.id +) AS orders +FROM customers c +ORDER BY array_count(orders); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-810-2_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-810-2_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-810-2_ps.sqlpp new file mode 100644 index 0000000..3b78d29 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-810-2_ps.sqlpp @@ -0,0 +1,70 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : This test case is to verify the fix for issue810. p_sort enabled. + * https://code.google.com/p/asterixdb/issues/detail?id=810 + * Expected Res : SUCCESS + * Date : 16th Nov. 2014 + */ +DROP DATAVERSE tpch IF EXISTS; +CREATE dataverse tpch; +USE tpch; + +CREATE TYPE LineItemType AS CLOSED { + l_orderkey : integer, + l_partkey : integer, + l_suppkey : integer, + l_linenumber : integer, + l_quantity : double, + l_extendedprice : double, + l_discount : double, + l_tax : double, + l_returnflag : string, + l_linestatus : string, + l_shipdate : string, + l_commitdate : string, + l_receiptdate : string, + l_shipinstruct : string, + l_shipmode : string, + l_comment : string +}; + +CREATE DATASET LineItem(LineItemType) PRIMARY KEY l_orderkey,l_linenumber; + +SET `compiler.sort.parallel` "true"; + +SELECT l_returnflag AS l_returnflag, + l_linestatus AS l_linestatus, + strict_count(cheap) AS count_cheaps, + strict_count(expensive) AS count_expensives +FROM LineItem AS l +/* +hash */ +GROUP BY l.l_returnflag AS l_returnflag,l.l_linestatus AS l_linestatus +GROUP AS g +LET cheap = ( + SELECT ELEMENT g.l + FROM g + WHERE g.l.l_discount > 0.05 +), +expensive = ( + SELECT ELEMENT g.l + FROM g + WHERE g.l.l_discount <= 0.05 +) +ORDER BY l_returnflag,l_linestatus; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-810-3_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-810-3_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-810-3_ps.sqlpp new file mode 100644 index 0000000..26974a6 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-810-3_ps.sqlpp @@ -0,0 +1,70 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : This test case is to verify the fix for issue810. p_sort enabled. + * https://code.google.com/p/asterixdb/issues/detail?id=810 + * Expected Res : SUCCESS + * Date : 16th Nov. 2014 + */ +DROP DATAVERSE tpch IF EXISTS; +CREATE DATAVERSE tpch; +USE tpch; + +CREATE TYPE LineItemType AS CLOSED { + l_orderkey : integer, + l_partkey : integer, + l_suppkey : integer, + l_linenumber : integer, + l_quantity : double, + l_extendedprice : double, + l_discount : double, + l_tax : double, + l_returnflag : string, + l_linestatus : string, + l_shipdate : string, + l_commitdate : string, + l_receiptdate : string, + l_shipinstruct : string, + l_shipmode : string, + l_comment : string +}; + +CREATE DATASET LineItem(LineItemType) PRIMARY KEY l_orderkey,l_linenumber; + +SET `compiler.sort.parallel` "true"; + +SELECT l_returnflag AS l_returnflag, + l_linestatus AS l_linestatus, + strict_count(cheap) AS count_cheaps, + strict_count(expensive) AS count_expensives +FROM LineItem AS l +/* +hash */ +GROUP BY l.l_returnflag AS l_returnflag,l.l_linestatus AS l_linestatus +GROUP AS g +LET cheap = ( + SELECT ELEMENT m + FROM (FROM g SELECT VALUE l) AS m + WHERE m.l_discount > 0.05 +), +expensive = ( + SELECT ELEMENT g.l + FROM g + WHERE g.l.l_discount <= 0.05 +) +ORDER BY l_returnflag,l_linestatus; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-810_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-810_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-810_ps.sqlpp new file mode 100644 index 0000000..dcddc12 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-810_ps.sqlpp @@ -0,0 +1,70 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : This test case is to verify the fix for issue810. p_sort enabled. + * https://code.google.com/p/asterixdb/issues/detail?id=810 + * Expected Res : SUCCESS + * Date : 16th Nov. 2014 + */ +DROP DATAVERSE tpch IF EXISTS; +CREATE dataverse tpch; +USE tpch; + +CREATE TYPE LineItemType AS CLOSED { + l_orderkey : integer, + l_partkey : integer, + l_suppkey : integer, + l_linenumber : integer, + l_quantity : double, + l_extendedprice : double, + l_discount : double, + l_tax : double, + l_returnflag : string, + l_linestatus : string, + l_shipdate : string, + l_commitdate : string, + l_receiptdate : string, + l_shipinstruct : string, + l_shipmode : string, + l_comment : string +}; + +CREATE DATASET LineItem(LineItemType) PRIMARY KEY l_orderkey,l_linenumber; + +SET `compiler.sort.parallel` "true"; + +SELECT l_returnflag AS l_returnflag, + l_linestatus AS l_linestatus, + strict_count(cheap) AS count_cheaps, + strict_count(expensive) AS count_expensives +FROM LineItem AS l +/* +hash */ +GROUP BY l.l_returnflag AS l_returnflag,l.l_linestatus AS l_linestatus +GROUP AS g +LET cheap = ( + SELECT ELEMENT m + FROM (FROM g SELECT VALUE l) AS m + WHERE m.l_discount > 0.05 +), +expensive = ( + SELECT ELEMENT m + FROM (FROM g SELECT VALUE l) AS m + WHERE m.l_discount <= 0.05 +) +ORDER BY l_returnflag,l_linestatus; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-issue562_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-issue562_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-issue562_ps.sqlpp new file mode 100644 index 0000000..53c79ef --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-issue562_ps.sqlpp @@ -0,0 +1,108 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : This test case is to verify the fix for issue562 + * https://code.google.com/p/asterixdb/issues/detail?id=562 + * Expected Res : SUCCESS + * Date : 15th Jan. 2015 + */ + +drop dataverse tpch if exists; +create dataverse tpch; + +use tpch; + + +create type tpch.LineItemType as + closed { + l_orderkey : integer, + l_partkey : integer, + l_suppkey : integer, + l_linenumber : integer, + l_quantity : integer, + l_extendedprice : double, + l_discount : double, + l_tax : double, + l_returnflag : string, + l_linestatus : string, + l_shipdate : string, + l_commitdate : string, + l_receiptdate : string, + l_shipinstruct : string, + l_shipmode : string, + l_comment : string +}; + +create type tpch.OrderType as + closed { + o_orderkey : integer, + o_custkey : integer, + o_orderstatus : string, + o_totalprice : double, + o_orderdate : string, + o_orderpriority : string, + o_clerk : string, + o_shippriority : integer, + o_comment : string +}; + +create type tpch.CustomerType as + closed { + c_custkey : integer, + c_name : string, + c_address : string, + c_nationkey : integer, + c_phone : string, + c_acctbal : double, + c_mktsegment : string, + c_comment : string +}; + +create dataset Orders(OrderType) primary key o_orderkey; + +create dataset Customer(CustomerType) primary key c_custkey; +set `compiler.sort.parallel` "true"; + +declare function q22_customer_tmp() { +( + select element {'c_acctbal':c.c_acctbal,'c_custkey':c.c_custkey,'cntrycode':phone_substr} + from Customer as c + with phone_substr as tpch.substring(c.c_phone,0,2) + where ((phone_substr = '13') or (phone_substr = '31') or (phone_substr = '23') or (phone_substr = '29') or (phone_substr = '30') or (phone_substr = '18') or (phone_substr = '17')) +) +}; +with avg as tpch.strict_avg(( + select element c.c_acctbal + from Customer as c + with phone_substr as tpch.substring(c.c_phone,0,2) + where ((c.c_acctbal > 0.0) and ((phone_substr = '13') or (phone_substr = '31') or (phone_substr = '23') or (phone_substr = '29') or (phone_substr = '30') or (phone_substr = '18') or (phone_substr = '17'))) + )) +select element {'cntrycode':cntrycode,'numcust':tpch.strict_count(g),'totacctbal':tpch.strict_sum(( + select element i.ct.c_acctbal + from g as i + ))} +from tpch.q22_customer_tmp() as ct +where (tpch.strict_count(( + select element o + from Orders as o + where (ct.c_custkey = o.o_custkey) +)) = 0) +group by ct.cntrycode as cntrycode group as g +order by cntrycode +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01_ps.sqlpp new file mode 100644 index 0000000..2a61fa5 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01_ps.sqlpp @@ -0,0 +1,77 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Test that left-outer-join may use two available indexes, one for primary index in prob subtree and another for secondary rtree index in index subtree. + * Issue : 730, 741 + * Expected Res : Success + * Date : 8th May 2014 + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.TwitterUserType as + closed { + `screen-name` : string, + lang : string, + `friends-count` : integer, + `statuses-count` : integer, + name : string, + `followers-count` : integer +}; + +create type test.TweetMessageType as + closed { + tweetid : bigint, + user : TwitterUserType, + `sender-location` : point, + `send-time` : datetime, + `referred-topics` : {{string}}, + `message-text` : string, + countA : integer, + countB : integer +}; + +create dataset TweetMessages(TweetMessageType) primary key tweetid; + +create index twmSndLocIx on TweetMessages (`sender-location`) type rtree; + +create index msgCountAIx on TweetMessages (countA) type btree; + +create index msgCountBIx on TweetMessages (countB) type btree; + +create index msgTextIx on TweetMessages (`message-text`) type keyword; + +write output to asterix_nc1:"rttest/rtree-index-join_leftouterjoin-probe-pidx-with-join-rtree-sidx_01.adm"; +set `compiler.sort.parallel` "true"; + +select element {'tweetid1':t1.tweetid,'loc1':t1.`sender-location`,'nearby-message':( + select element {'tweetid2':t2.tweetid,'loc2':t2.`sender-location`} + from TweetMessages as t2 + where test.`spatial-intersect`(t2.`sender-location`,n) + order by t2.tweetid + )} +from TweetMessages as t1 +with n as test.`create-circle`(t1.`sender-location`,0.5) +where (t1.tweetid < test.bigint('10')) +order by t1.tweetid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02_ps.sqlpp new file mode 100644 index 0000000..3e1c881 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02_ps.sqlpp @@ -0,0 +1,77 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Test that left-outer-join may use two available indexes, one for primary index in prob subtree and another for secondary rtree index in index subtree. + * Issue : 730, 741 + * Expected Res : Success + * Date : 8th May 2014 + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.TwitterUserType as + closed { + `screen-name` : string, + lang : string, + `friends-count` : integer, + `statuses-count` : integer, + name : string, + `followers-count` : integer +}; + +create type test.TweetMessageType as + closed { + tweetid : bigint, + user : TwitterUserType, + `sender-location` : point, + `send-time` : datetime, + `referred-topics` : {{string}}, + `message-text` : string, + countA : integer, + countB : integer +}; + +create dataset TweetMessages(TweetMessageType) primary key tweetid; + +create index twmSndLocIx on TweetMessages (`sender-location`) type rtree; + +create index msgCountAIx on TweetMessages (countA) type btree; + +create index msgCountBIx on TweetMessages (countB) type btree; + +create index msgTextIx on TweetMessages (`message-text`) type keyword; + +write output to asterix_nc1:"rttest/rtree-index-join_leftouterjoin-probe-pidx-with-join-rtree-sidx_02.adm"; +set `compiler.sort.parallel` "true"; + +select element {'tweetid1':t1.tweetid,'loc1':t1.`sender-location`,'nearby-message':( + select element {'tweetid2':t2.tweetid,'loc2':t2.`sender-location`} + from TweetMessages as t2 + where (test.`spatial-intersect`(t2.`sender-location`,n) and (t1.tweetid != t2.tweetid)) + order by t2.tweetid + )} +from TweetMessages as t1 +with n as test.`create-circle`(t1.`sender-location`,0.5) +where (t1.tweetid < test.bigint('10')) +order by t1.tweetid +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/skip-index/skip-ngram-index_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/skip-index/skip-ngram-index_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/skip-index/skip-ngram-index_ps.sqlpp new file mode 100644 index 0000000..c14a25e --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/skip-index/skip-ngram-index_ps.sqlpp @@ -0,0 +1,51 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +/* + * Description : Notice the query hint to avoid using any secondary index to evaluate the predicate in the where clause + * Expected Res : Success + * Date : 21th December 2013 + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + + +create type test.DBLPType as + closed { + id : integer, + dblpid : string, + title : string, + authors : string, + misc : string +}; + +create dataset DBLP(DBLPType) primary key id; + +create index ngram_index on DBLP (title) type ngram (3); + +write output to asterix_nc1:"rttest/inverted-index-basic_ngram-contains.adm"; +set `compiler.sort.parallel` "true"; + +select element o +from DBLP as o +where /*+ skip-index */ test.contains(o.title,'Multimedia') +order by o.id +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/split-materialization_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/split-materialization_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/split-materialization_ps.sqlpp new file mode 100644 index 0000000..71bcb4f --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/split-materialization_ps.sqlpp @@ -0,0 +1,61 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +drop dataverse TinySocial if exists; +create dataverse TinySocial; + +use TinySocial; + + +create type TinySocial.EmploymentType as +{ + `organization-name` : string, + `start-date` : date, + `end-date` : date? +}; + +create type TinySocial.FacebookUserType as + closed { + id : integer, + alias : string, + name : string, + `user-since` : datetime, + `friend-ids` : {{integer}}, + employment : [EmploymentType] +}; + +create dataset FacebookUsers(FacebookUserType) primary key id; +set `compiler.sort.parallel` "true"; + +with lonelyusers as ( + select element d + from FacebookUsers as d + where (TinySocial.strict_count(d.`friend-ids`) < 2) + ), + lonelyusers2 as ( + select element d + from FacebookUsers as d + where (TinySocial.strict_count(d.`friend-ids`) < 2) + ) +select element {'user1':{'id':l1.id,'name':l1.name},'user2':{'id':l2.id,'name':l2.name}} +from lonelyusers as l1, + lonelyusers2 as l2 +where (l1.id < l2.id) +order by l1.id,l2.id +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/exists_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/exists_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/exists_ps.sqlpp new file mode 100644 index 0000000..7fb60be --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/exists_ps.sqlpp @@ -0,0 +1,72 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +DROP DATAVERSE test IF EXISTS; +CREATE DATAVERSE test; +USE test; + +CREATE TYPE OrderType AS CLOSED { + o_orderkey: integer, + o_custkey: integer, + o_orderstatus: string, + o_totalprice: double, + o_orderdate: string, + o_orderpriority: string, + o_clerk: string, + o_shippriority: integer, + o_comment: string +}; + +CREATE TYPE CustomerType AS CLOSED { + c_custkey: integer, + c_name: string, + c_address: string, + c_nationkey: integer, + c_phone: string, + c_acctbal: double, + c_mktsegment: string, + c_comment: string +}; + +CREATE EXTERNAL DATASET Customer(CustomerType) USING `localfs` +((`path`=`asterix_nc1://data/tpch0.001/customer.tbl`), +(`input-format`=`text-input-format`),(`format`=`delimited-text`),(`delimiter`=`|`)); + +CREATE EXTERNAL DATASET Orders(OrderType) USING `localfs` +((`path`=`asterix_nc1://data/tpch0.001/orders.tbl`), +(`input-format`=`text-input-format`),(`format`=`delimited-text`),(`delimiter`=`|`)); + +SET `compiler.sort.parallel` "true"; + +WITH q22_customer_tmp AS +( + SELECT c_acctbal, c_custkey, substring(c_phone,0,2) AS cntrycode + FROM Customer +) +, +avg AS ( + SELECT ELEMENT AVG(c_acctbal) + FROM Customer + WHERE c_acctbal > 0.0 +)[0] +SELECT cntrycode, count(ct) AS numcust, SUM(c_acctbal) AS totacctbal +FROM q22_customer_tmp AS ct +WHERE c_acctbal > avg + AND EXISTS (SELECT * FROM Orders o WHERE o.o_custkey = ct.c_custkey) +GROUP BY cntrycode +ORDER BY cntrycode; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/in_as_or_1_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/in_as_or_1_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/in_as_or_1_ps.sqlpp new file mode 100644 index 0000000..308a618 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/in_as_or_1_ps.sqlpp @@ -0,0 +1,42 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +drop dataverse tpch if exists; +create dataverse tpch; +use tpch; + +create type tpch.CustomerType as closed { + c_custkey : bigint, + c_name : string, + c_address : string, + c_nationkey : bigint, + c_phone : string, + c_acctbal : double, + c_mktsegment : string, + c_comment : string +}; + +create dataset Customer(CustomerType) primary key c_custkey; + +set `compiler.sort.parallel` "true"; + +select value c.c_custkey +from Customer c +where c.c_name in ["Customer#000000001", "Customer#000000002", "Customer#000000003"] +order by c.c_custkey; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/in_as_or_2_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/in_as_or_2_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/in_as_or_2_ps.sqlpp new file mode 100644 index 0000000..297116a --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/in_as_or_2_ps.sqlpp @@ -0,0 +1,43 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +drop dataverse tpch if exists; +create dataverse tpch; +use tpch; + +create type tpch.CustomerType as closed { + c_custkey : bigint, + c_name : string, + c_address : string, + c_nationkey : bigint, + c_phone : string, + c_acctbal : double, + c_mktsegment : string, + c_comment : string +}; + +create dataset Customer(CustomerType) primary key c_custkey; + +set rewrite_in_as_or "false"; +set `compiler.sort.parallel` "true"; + +select value c.c_custkey +from Customer c +where c.c_name in ["Customer#000000001", "Customer#000000002", "Customer#000000003"] +order by c.c_custkey; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/in_as_or_3_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/in_as_or_3_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/in_as_or_3_ps.sqlpp new file mode 100644 index 0000000..d0d190d --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/in_as_or_3_ps.sqlpp @@ -0,0 +1,46 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +// requesttype=application/json + +// param $p_arr:json=["Customer#000000001", "Customer#000000002", "Customer#000000003"] + +drop dataverse tpch if exists; +create dataverse tpch; +use tpch; + +create type tpch.CustomerType as closed { + c_custkey : bigint, + c_name : string, + c_address : string, + c_nationkey : bigint, + c_phone : string, + c_acctbal : double, + c_mktsegment : string, + c_comment : string +}; + +create dataset Customer(CustomerType) primary key c_custkey; + +set `compiler.sort.parallel` "true"; + +select value c.c_custkey +from Customer c +where c.c_name in $p_arr +order by c.c_custkey; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/in_as_or_4_ps.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/in_as_or_4_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/in_as_or_4_ps.sqlpp new file mode 100644 index 0000000..2f40400 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/in_as_or_4_ps.sqlpp @@ -0,0 +1,47 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +// requesttype=application/json + +// param $p_arr:json=["Customer#000000001", "Customer#000000002", "Customer#000000003"] + +drop dataverse tpch if exists; +create dataverse tpch; +use tpch; + +create type tpch.CustomerType as closed { + c_custkey : bigint, + c_name : string, + c_address : string, + c_nationkey : bigint, + c_phone : string, + c_acctbal : double, + c_mktsegment : string, + c_comment : string +}; + +create dataset Customer(CustomerType) primary key c_custkey; + +set rewrite_in_as_or "false"; +set `compiler.sort.parallel` "true"; + +select value c.c_custkey +from Customer c +where c.c_name in $p_arr +order by c.c_custkey; \ No newline at end of file
