asterixdb-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ti...@apache.org
Subject [2/4] asterixdb git commit: ASTERIXDB-1581: fix subquery decorrelation.
Date Wed, 07 Sep 2016 04:39:23 GMT
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-3/query-ASTERIXDB-1581-3.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-3/query-ASTERIXDB-1581-3.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-3/query-ASTERIXDB-1581-3.1.ddl.sqlpp
new file mode 100644
index 0000000..2e4844e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-3/query-ASTERIXDB-1581-3.1.ddl.sqlpp
@@ -0,0 +1,82 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+create type tpcds.store_sales_type as closed {
+    ss_sold_date_sk:           int64?,
+    ss_sold_time_sk:           int64?,
+    ss_item_sk:                int64,
+    ss_customer_sk:            int64?,
+    ss_cdemo_sk:               int64?,
+    ss_hdemo_sk:               int64?,
+    ss_addr_sk:                int64?,
+    ss_store_sk:               int64?,
+    ss_promo_sk:               int64?,
+    ss_ticket_number:          int64,
+    ss_quantity:               int64?,
+    ss_wholesale_cost:         double?,
+    ss_list_price:             double?,
+    ss_sales_price:            double?,
+    ss_ext_discount_amt:       double?,
+    ss_ext_sales_price:        double?,
+    ss_ext_wholesale_cost:     double?,
+    ss_ext_list_price:         double?,
+    ss_ext_tax:                double?,
+    ss_coupon_amt:             double?,
+    ss_net_paid:               double?,
+    ss_net_paid_inc_tax:       double?,
+    ss_net_profit:             double?
+}
+
+
+create type tpcds.item_type as closed {
+    i_item_sk:                 int64,
+    i_item_id:                 string,
+    i_rec_start_date:          string?,
+    i_rec_end_date:            string?,
+    i_item_desc:               string?,
+    i_current_price:           double?,
+    i_wholesale_cost:          double?,
+    i_brand_id:                int64? ,
+    i_brand:                   string?,
+    i_class_id:                int64? ,
+    i_class:                   string?,
+    i_category_id:             int64? ,
+    i_category:                string?,
+    i_manufact_id:             int64? ,
+    i_manufact:                string?,
+    i_size:                    string?,
+    i_formulation:             string?,
+    i_color:                   string?,
+    i_units:                   string?,
+    i_container:               string?,
+    i_manager_id:              int64?,
+    i_product_name:            string?
+}
+
+create dataset store_sales (store_sales_type)
+primary key ss_item_sk, ss_ticket_number;
+
+create dataset item (item_type)
+primary key i_item_sk;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-3/query-ASTERIXDB-1581-3.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-3/query-ASTERIXDB-1581-3.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-3/query-ASTERIXDB-1581-3.2.update.sqlpp
new file mode 100644
index 0000000..bf0a6f7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-3/query-ASTERIXDB-1581-3.2.update.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 tpcds;
+
+load  dataset store_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_sales.csv`),
+(`format`=`delimited-text`), (`delimiter`=`|`));
+
+load  dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),
+(`format`=`delimited-text`), (`delimiter`=`|`));

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-3/query-ASTERIXDB-1581-3.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-3/query-ASTERIXDB-1581-3.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-3/query-ASTERIXDB-1581-3.3.query.sqlpp
new file mode 100644
index 0000000..cee2a7c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-3/query-ASTERIXDB-1581-3.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.
+ */
+
+use tpcds;
+
+// Returns a singleton list of records in the case expression.
+select case when i_brand_id > 1
+            then (select avg(ss_ext_discount_amt)
+                  from store_sales
+                  where ss_quantity >= 1 and ss_quantity <= 20)
+            else (select avg(ss_net_profit)
+                  from store_sales
+                  where ss_quantity >= 1 and ss_quantity <= 20)
+            end bucket1
+from item
+where i_item_sk = 1;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-4/query-ASTERIXDB-1581-4.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-4/query-ASTERIXDB-1581-4.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-4/query-ASTERIXDB-1581-4.1.ddl.sqlpp
new file mode 100644
index 0000000..2e4844e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-4/query-ASTERIXDB-1581-4.1.ddl.sqlpp
@@ -0,0 +1,82 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+create type tpcds.store_sales_type as closed {
+    ss_sold_date_sk:           int64?,
+    ss_sold_time_sk:           int64?,
+    ss_item_sk:                int64,
+    ss_customer_sk:            int64?,
+    ss_cdemo_sk:               int64?,
+    ss_hdemo_sk:               int64?,
+    ss_addr_sk:                int64?,
+    ss_store_sk:               int64?,
+    ss_promo_sk:               int64?,
+    ss_ticket_number:          int64,
+    ss_quantity:               int64?,
+    ss_wholesale_cost:         double?,
+    ss_list_price:             double?,
+    ss_sales_price:            double?,
+    ss_ext_discount_amt:       double?,
+    ss_ext_sales_price:        double?,
+    ss_ext_wholesale_cost:     double?,
+    ss_ext_list_price:         double?,
+    ss_ext_tax:                double?,
+    ss_coupon_amt:             double?,
+    ss_net_paid:               double?,
+    ss_net_paid_inc_tax:       double?,
+    ss_net_profit:             double?
+}
+
+
+create type tpcds.item_type as closed {
+    i_item_sk:                 int64,
+    i_item_id:                 string,
+    i_rec_start_date:          string?,
+    i_rec_end_date:            string?,
+    i_item_desc:               string?,
+    i_current_price:           double?,
+    i_wholesale_cost:          double?,
+    i_brand_id:                int64? ,
+    i_brand:                   string?,
+    i_class_id:                int64? ,
+    i_class:                   string?,
+    i_category_id:             int64? ,
+    i_category:                string?,
+    i_manufact_id:             int64? ,
+    i_manufact:                string?,
+    i_size:                    string?,
+    i_formulation:             string?,
+    i_color:                   string?,
+    i_units:                   string?,
+    i_container:               string?,
+    i_manager_id:              int64?,
+    i_product_name:            string?
+}
+
+create dataset store_sales (store_sales_type)
+primary key ss_item_sk, ss_ticket_number;
+
+create dataset item (item_type)
+primary key i_item_sk;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-4/query-ASTERIXDB-1581-4.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-4/query-ASTERIXDB-1581-4.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-4/query-ASTERIXDB-1581-4.2.update.sqlpp
new file mode 100644
index 0000000..bf0a6f7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-4/query-ASTERIXDB-1581-4.2.update.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 tpcds;
+
+load  dataset store_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_sales.csv`),
+(`format`=`delimited-text`), (`delimiter`=`|`));
+
+load  dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),
+(`format`=`delimited-text`), (`delimiter`=`|`));

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-4/query-ASTERIXDB-1581-4.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-4/query-ASTERIXDB-1581-4.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-4/query-ASTERIXDB-1581-4.3.query.sqlpp
new file mode 100644
index 0000000..fd0682d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-4/query-ASTERIXDB-1581-4.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.
+ */
+
+use tpcds;
+
+// Returns heterogeneous scalar expressions (string and double) in the case expression.
+select case when (select value count(ss)
+                  from store_sales ss
+                  where ss_quantity >= 1 and ss_quantity <= 20)[0] > 25437
+            then "1.0"
+            else (select value avg(ss_ext_discount_amt)
+                  from store_sales
+                  where ss_quantity >= 1 and ss_quantity <= 20)[0]
+            end bucket1
+from item
+where i_item_sk = 1;
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-5/query-ASTERIXDB-1581-5.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-5/query-ASTERIXDB-1581-5.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-5/query-ASTERIXDB-1581-5.1.ddl.sqlpp
new file mode 100644
index 0000000..2e4844e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-5/query-ASTERIXDB-1581-5.1.ddl.sqlpp
@@ -0,0 +1,82 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+create type tpcds.store_sales_type as closed {
+    ss_sold_date_sk:           int64?,
+    ss_sold_time_sk:           int64?,
+    ss_item_sk:                int64,
+    ss_customer_sk:            int64?,
+    ss_cdemo_sk:               int64?,
+    ss_hdemo_sk:               int64?,
+    ss_addr_sk:                int64?,
+    ss_store_sk:               int64?,
+    ss_promo_sk:               int64?,
+    ss_ticket_number:          int64,
+    ss_quantity:               int64?,
+    ss_wholesale_cost:         double?,
+    ss_list_price:             double?,
+    ss_sales_price:            double?,
+    ss_ext_discount_amt:       double?,
+    ss_ext_sales_price:        double?,
+    ss_ext_wholesale_cost:     double?,
+    ss_ext_list_price:         double?,
+    ss_ext_tax:                double?,
+    ss_coupon_amt:             double?,
+    ss_net_paid:               double?,
+    ss_net_paid_inc_tax:       double?,
+    ss_net_profit:             double?
+}
+
+
+create type tpcds.item_type as closed {
+    i_item_sk:                 int64,
+    i_item_id:                 string,
+    i_rec_start_date:          string?,
+    i_rec_end_date:            string?,
+    i_item_desc:               string?,
+    i_current_price:           double?,
+    i_wholesale_cost:          double?,
+    i_brand_id:                int64? ,
+    i_brand:                   string?,
+    i_class_id:                int64? ,
+    i_class:                   string?,
+    i_category_id:             int64? ,
+    i_category:                string?,
+    i_manufact_id:             int64? ,
+    i_manufact:                string?,
+    i_size:                    string?,
+    i_formulation:             string?,
+    i_color:                   string?,
+    i_units:                   string?,
+    i_container:               string?,
+    i_manager_id:              int64?,
+    i_product_name:            string?
+}
+
+create dataset store_sales (store_sales_type)
+primary key ss_item_sk, ss_ticket_number;
+
+create dataset item (item_type)
+primary key i_item_sk;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-5/query-ASTERIXDB-1581-5.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-5/query-ASTERIXDB-1581-5.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-5/query-ASTERIXDB-1581-5.2.update.sqlpp
new file mode 100644
index 0000000..bf0a6f7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-5/query-ASTERIXDB-1581-5.2.update.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 tpcds;
+
+load  dataset store_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_sales.csv`),
+(`format`=`delimited-text`), (`delimiter`=`|`));
+
+load  dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),
+(`format`=`delimited-text`), (`delimiter`=`|`));

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-5/query-ASTERIXDB-1581-5.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-5/query-ASTERIXDB-1581-5.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-5/query-ASTERIXDB-1581-5.3.query.sqlpp
new file mode 100644
index 0000000..c39f703
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-5/query-ASTERIXDB-1581-5.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.
+ */
+
+use tpcds;
+
+// Returns heterogeneous expressions (string and list) in the case expression.
+select case when (select value count(ss)
+                  from store_sales ss
+                  where ss_quantity >= 1 and ss_quantity <= 20)[0] > 25437
+            then "1.0"
+            else (select avg(ss_ext_discount_amt)
+                  from store_sales
+                  where ss_quantity >= 1 and ss_quantity <= 20)
+            end bucket1
+from item
+where i_item_sk = 1;
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-6/query-ASTERIXDB-1581-6.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-6/query-ASTERIXDB-1581-6.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-6/query-ASTERIXDB-1581-6.1.ddl.sqlpp
new file mode 100644
index 0000000..2e4844e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-6/query-ASTERIXDB-1581-6.1.ddl.sqlpp
@@ -0,0 +1,82 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+create type tpcds.store_sales_type as closed {
+    ss_sold_date_sk:           int64?,
+    ss_sold_time_sk:           int64?,
+    ss_item_sk:                int64,
+    ss_customer_sk:            int64?,
+    ss_cdemo_sk:               int64?,
+    ss_hdemo_sk:               int64?,
+    ss_addr_sk:                int64?,
+    ss_store_sk:               int64?,
+    ss_promo_sk:               int64?,
+    ss_ticket_number:          int64,
+    ss_quantity:               int64?,
+    ss_wholesale_cost:         double?,
+    ss_list_price:             double?,
+    ss_sales_price:            double?,
+    ss_ext_discount_amt:       double?,
+    ss_ext_sales_price:        double?,
+    ss_ext_wholesale_cost:     double?,
+    ss_ext_list_price:         double?,
+    ss_ext_tax:                double?,
+    ss_coupon_amt:             double?,
+    ss_net_paid:               double?,
+    ss_net_paid_inc_tax:       double?,
+    ss_net_profit:             double?
+}
+
+
+create type tpcds.item_type as closed {
+    i_item_sk:                 int64,
+    i_item_id:                 string,
+    i_rec_start_date:          string?,
+    i_rec_end_date:            string?,
+    i_item_desc:               string?,
+    i_current_price:           double?,
+    i_wholesale_cost:          double?,
+    i_brand_id:                int64? ,
+    i_brand:                   string?,
+    i_class_id:                int64? ,
+    i_class:                   string?,
+    i_category_id:             int64? ,
+    i_category:                string?,
+    i_manufact_id:             int64? ,
+    i_manufact:                string?,
+    i_size:                    string?,
+    i_formulation:             string?,
+    i_color:                   string?,
+    i_units:                   string?,
+    i_container:               string?,
+    i_manager_id:              int64?,
+    i_product_name:            string?
+}
+
+create dataset store_sales (store_sales_type)
+primary key ss_item_sk, ss_ticket_number;
+
+create dataset item (item_type)
+primary key i_item_sk;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-6/query-ASTERIXDB-1581-6.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-6/query-ASTERIXDB-1581-6.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-6/query-ASTERIXDB-1581-6.2.update.sqlpp
new file mode 100644
index 0000000..bf0a6f7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-6/query-ASTERIXDB-1581-6.2.update.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 tpcds;
+
+load  dataset store_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_sales.csv`),
+(`format`=`delimited-text`), (`delimiter`=`|`));
+
+load  dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),
+(`format`=`delimited-text`), (`delimiter`=`|`));

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-6/query-ASTERIXDB-1581-6.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-6/query-ASTERIXDB-1581-6.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-6/query-ASTERIXDB-1581-6.3.query.sqlpp
new file mode 100644
index 0000000..62c1d49
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-6/query-ASTERIXDB-1581-6.3.query.sqlpp
@@ -0,0 +1,35 @@
+/*
+ * 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 tpcds;
+
+// Note: the condition always returns null, hence the case expression
+// should always return the "else" expression.
+select case when (select value count(ss)
+                  from store_sales ss
+                  where ss_quantity >= 1 and ss_quantity <= 20) > 25437
+            then (select avg(ss_ext_discount_amt)
+                  from store_sales
+                  where ss_quantity >= 1 and ss_quantity <= 20)
+            else (select avg(ss_net_profit)
+                  from store_sales
+                  where ss_quantity >= 1 and ss_quantity <= 20)
+            end bucket1
+from item
+where i_item_sk = 1;
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-correlated-2/query-ASTERIXDB-1581-correlated-2.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-correlated-2/query-ASTERIXDB-1581-correlated-2.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-correlated-2/query-ASTERIXDB-1581-correlated-2.1.ddl.sqlpp
new file mode 100644
index 0000000..2e4844e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-correlated-2/query-ASTERIXDB-1581-correlated-2.1.ddl.sqlpp
@@ -0,0 +1,82 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+create type tpcds.store_sales_type as closed {
+    ss_sold_date_sk:           int64?,
+    ss_sold_time_sk:           int64?,
+    ss_item_sk:                int64,
+    ss_customer_sk:            int64?,
+    ss_cdemo_sk:               int64?,
+    ss_hdemo_sk:               int64?,
+    ss_addr_sk:                int64?,
+    ss_store_sk:               int64?,
+    ss_promo_sk:               int64?,
+    ss_ticket_number:          int64,
+    ss_quantity:               int64?,
+    ss_wholesale_cost:         double?,
+    ss_list_price:             double?,
+    ss_sales_price:            double?,
+    ss_ext_discount_amt:       double?,
+    ss_ext_sales_price:        double?,
+    ss_ext_wholesale_cost:     double?,
+    ss_ext_list_price:         double?,
+    ss_ext_tax:                double?,
+    ss_coupon_amt:             double?,
+    ss_net_paid:               double?,
+    ss_net_paid_inc_tax:       double?,
+    ss_net_profit:             double?
+}
+
+
+create type tpcds.item_type as closed {
+    i_item_sk:                 int64,
+    i_item_id:                 string,
+    i_rec_start_date:          string?,
+    i_rec_end_date:            string?,
+    i_item_desc:               string?,
+    i_current_price:           double?,
+    i_wholesale_cost:          double?,
+    i_brand_id:                int64? ,
+    i_brand:                   string?,
+    i_class_id:                int64? ,
+    i_class:                   string?,
+    i_category_id:             int64? ,
+    i_category:                string?,
+    i_manufact_id:             int64? ,
+    i_manufact:                string?,
+    i_size:                    string?,
+    i_formulation:             string?,
+    i_color:                   string?,
+    i_units:                   string?,
+    i_container:               string?,
+    i_manager_id:              int64?,
+    i_product_name:            string?
+}
+
+create dataset store_sales (store_sales_type)
+primary key ss_item_sk, ss_ticket_number;
+
+create dataset item (item_type)
+primary key i_item_sk;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-correlated-2/query-ASTERIXDB-1581-correlated-2.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-correlated-2/query-ASTERIXDB-1581-correlated-2.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-correlated-2/query-ASTERIXDB-1581-correlated-2.2.update.sqlpp
new file mode 100644
index 0000000..bf0a6f7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-correlated-2/query-ASTERIXDB-1581-correlated-2.2.update.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 tpcds;
+
+load  dataset store_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_sales.csv`),
+(`format`=`delimited-text`), (`delimiter`=`|`));
+
+load  dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),
+(`format`=`delimited-text`), (`delimiter`=`|`));

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-correlated-2/query-ASTERIXDB-1581-correlated-2.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-correlated-2/query-ASTERIXDB-1581-correlated-2.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-correlated-2/query-ASTERIXDB-1581-correlated-2.3.query.sqlpp
new file mode 100644
index 0000000..d2cc049
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-correlated-2/query-ASTERIXDB-1581-correlated-2.3.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.
+ */
+
+use tpcds;
+
+// The case expression contains correlated, non-equality condition subqueries.
+select case when (select value count(ss)
+                  from store_sales ss
+                  where ss_quantity >= item.i_item_sk and ss_quantity <= 20)[0] < 25437
+            then (select avg(ss_ext_discount_amt)
+                  from store_sales
+                  where ss_quantity >= item.i_item_sk and ss_quantity <= 20)
+            else (select avg(ss_net_profit)
+                  from store_sales
+                  where ss_quantity >= item.i_item_sk and ss_quantity <= 20)
+            end bucket1
+from item
+where i_item_sk = 1;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-correlated/query-ASTERIXDB-1581-correlated.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-correlated/query-ASTERIXDB-1581-correlated.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-correlated/query-ASTERIXDB-1581-correlated.1.ddl.sqlpp
new file mode 100644
index 0000000..2e4844e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-correlated/query-ASTERIXDB-1581-correlated.1.ddl.sqlpp
@@ -0,0 +1,82 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+create type tpcds.store_sales_type as closed {
+    ss_sold_date_sk:           int64?,
+    ss_sold_time_sk:           int64?,
+    ss_item_sk:                int64,
+    ss_customer_sk:            int64?,
+    ss_cdemo_sk:               int64?,
+    ss_hdemo_sk:               int64?,
+    ss_addr_sk:                int64?,
+    ss_store_sk:               int64?,
+    ss_promo_sk:               int64?,
+    ss_ticket_number:          int64,
+    ss_quantity:               int64?,
+    ss_wholesale_cost:         double?,
+    ss_list_price:             double?,
+    ss_sales_price:            double?,
+    ss_ext_discount_amt:       double?,
+    ss_ext_sales_price:        double?,
+    ss_ext_wholesale_cost:     double?,
+    ss_ext_list_price:         double?,
+    ss_ext_tax:                double?,
+    ss_coupon_amt:             double?,
+    ss_net_paid:               double?,
+    ss_net_paid_inc_tax:       double?,
+    ss_net_profit:             double?
+}
+
+
+create type tpcds.item_type as closed {
+    i_item_sk:                 int64,
+    i_item_id:                 string,
+    i_rec_start_date:          string?,
+    i_rec_end_date:            string?,
+    i_item_desc:               string?,
+    i_current_price:           double?,
+    i_wholesale_cost:          double?,
+    i_brand_id:                int64? ,
+    i_brand:                   string?,
+    i_class_id:                int64? ,
+    i_class:                   string?,
+    i_category_id:             int64? ,
+    i_category:                string?,
+    i_manufact_id:             int64? ,
+    i_manufact:                string?,
+    i_size:                    string?,
+    i_formulation:             string?,
+    i_color:                   string?,
+    i_units:                   string?,
+    i_container:               string?,
+    i_manager_id:              int64?,
+    i_product_name:            string?
+}
+
+create dataset store_sales (store_sales_type)
+primary key ss_item_sk, ss_ticket_number;
+
+create dataset item (item_type)
+primary key i_item_sk;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-correlated/query-ASTERIXDB-1581-correlated.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-correlated/query-ASTERIXDB-1581-correlated.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-correlated/query-ASTERIXDB-1581-correlated.2.update.sqlpp
new file mode 100644
index 0000000..bf0a6f7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-correlated/query-ASTERIXDB-1581-correlated.2.update.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 tpcds;
+
+load  dataset store_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_sales.csv`),
+(`format`=`delimited-text`), (`delimiter`=`|`));
+
+load  dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),
+(`format`=`delimited-text`), (`delimiter`=`|`));

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-correlated/query-ASTERIXDB-1581-correlated.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-correlated/query-ASTERIXDB-1581-correlated.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-correlated/query-ASTERIXDB-1581-correlated.3.query.sqlpp
new file mode 100644
index 0000000..a46ffe0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581-correlated/query-ASTERIXDB-1581-correlated.3.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.
+ */
+
+use tpcds;
+
+// The case expression contains correlated subqueries.
+select case when (select value count(ss)
+                  from store_sales ss
+                  where ss_quantity = item.i_item_sk)[0] < 25437
+            then (select avg(ss_ext_discount_amt)
+                  from store_sales
+                  where ss_quantity = item.i_item_sk)
+            else (select avg(ss_net_profit)
+                  from store_sales
+                  where ss_quantity = item.i_item_sk)
+            end bucket1
+from item
+where i_item_sk = 2;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581/query-ASTERIXDB-1581.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581/query-ASTERIXDB-1581.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581/query-ASTERIXDB-1581.1.ddl.sqlpp
new file mode 100644
index 0000000..2e4844e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581/query-ASTERIXDB-1581.1.ddl.sqlpp
@@ -0,0 +1,82 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+create type tpcds.store_sales_type as closed {
+    ss_sold_date_sk:           int64?,
+    ss_sold_time_sk:           int64?,
+    ss_item_sk:                int64,
+    ss_customer_sk:            int64?,
+    ss_cdemo_sk:               int64?,
+    ss_hdemo_sk:               int64?,
+    ss_addr_sk:                int64?,
+    ss_store_sk:               int64?,
+    ss_promo_sk:               int64?,
+    ss_ticket_number:          int64,
+    ss_quantity:               int64?,
+    ss_wholesale_cost:         double?,
+    ss_list_price:             double?,
+    ss_sales_price:            double?,
+    ss_ext_discount_amt:       double?,
+    ss_ext_sales_price:        double?,
+    ss_ext_wholesale_cost:     double?,
+    ss_ext_list_price:         double?,
+    ss_ext_tax:                double?,
+    ss_coupon_amt:             double?,
+    ss_net_paid:               double?,
+    ss_net_paid_inc_tax:       double?,
+    ss_net_profit:             double?
+}
+
+
+create type tpcds.item_type as closed {
+    i_item_sk:                 int64,
+    i_item_id:                 string,
+    i_rec_start_date:          string?,
+    i_rec_end_date:            string?,
+    i_item_desc:               string?,
+    i_current_price:           double?,
+    i_wholesale_cost:          double?,
+    i_brand_id:                int64? ,
+    i_brand:                   string?,
+    i_class_id:                int64? ,
+    i_class:                   string?,
+    i_category_id:             int64? ,
+    i_category:                string?,
+    i_manufact_id:             int64? ,
+    i_manufact:                string?,
+    i_size:                    string?,
+    i_formulation:             string?,
+    i_color:                   string?,
+    i_units:                   string?,
+    i_container:               string?,
+    i_manager_id:              int64?,
+    i_product_name:            string?
+}
+
+create dataset store_sales (store_sales_type)
+primary key ss_item_sk, ss_ticket_number;
+
+create dataset item (item_type)
+primary key i_item_sk;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581/query-ASTERIXDB-1581.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581/query-ASTERIXDB-1581.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581/query-ASTERIXDB-1581.2.update.sqlpp
new file mode 100644
index 0000000..bf0a6f7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581/query-ASTERIXDB-1581.2.update.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 tpcds;
+
+load  dataset store_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_sales.csv`),
+(`format`=`delimited-text`), (`delimiter`=`|`));
+
+load  dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),
+(`format`=`delimited-text`), (`delimiter`=`|`));

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581/query-ASTERIXDB-1581.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581/query-ASTERIXDB-1581.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581/query-ASTERIXDB-1581.3.query.sqlpp
new file mode 100644
index 0000000..cb9a030
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1581/query-ASTERIXDB-1581.3.query.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 tpcds;
+
+select case when (select value count(ss)
+                  from store_sales ss
+                  where ss_quantity >= 1 and ss_quantity <= 20)[0] > 25437
+            then 1.0
+            else 2.0
+                   end bucket1
+from item
+where i_item_sk = 1;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/aggregate_join/aggregate_join.1.adm
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/aggregate_join/aggregate_join.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/aggregate_join/aggregate_join.1.adm
new file mode 100644
index 0000000..f03669f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/aggregate_join/aggregate_join.1.adm
@@ -0,0 +1 @@
+{ "min": 0, "s": { "id": 1 } }

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/aggregate_join_external/aggregate_join_external.1.adm
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/aggregate_join_external/aggregate_join_external.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/aggregate_join_external/aggregate_join_external.1.adm
new file mode 100644
index 0000000..9653dbe
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/aggregate_join_external/aggregate_join_external.1.adm
@@ -0,0 +1,5 @@
+{ "min": 1, "c": { "c_custkey": 2, "c_name": "Customer#000000002", "c_address": "XSTf4,NCwDVaWNe6tEgvwfmRchLXak", "c_nationkey": 13, "c_phone": "23-768-687-3665", "c_acctbal": 121.65, "c_mktsegment": "AUTOMOBILE", "c_comment": "l accounts. blithely ironic theodolites integrate boldly: caref" } }
+{ "min": 1, "c": { "c_custkey": 3, "c_name": "Customer#000000003", "c_address": "MG9kdTD2WBHm", "c_nationkey": 1, "c_phone": "11-719-748-3364", "c_acctbal": 7498.12, "c_mktsegment": "AUTOMOBILE", "c_comment": " deposits eat slyly ironic, even instructions. express foxes detect slyly. blithely even accounts abov" } }
+{ "min": 1, "c": { "c_custkey": 4, "c_name": "Customer#000000004", "c_address": "XxVSJsLAGtn", "c_nationkey": 4, "c_phone": "14-128-190-5944", "c_acctbal": 2866.83, "c_mktsegment": "MACHINERY", "c_comment": " requests. final, regular ideas sleep final accou" } }
+{ "min": 1, "c": { "c_custkey": 5, "c_name": "Customer#000000005", "c_address": "KvpyuHCplrB84WgAiGV6sYpZq7Tj", "c_nationkey": 3, "c_phone": "13-750-942-6364", "c_acctbal": 794.47, "c_mktsegment": "HOUSEHOLD", "c_comment": "n accounts will have to unwind. foxes cajole accor" } }
+{ "min": 1, "c": { "c_custkey": 6, "c_name": "Customer#000000006", "c_address": "sKZz0CsnMD7mp4Xd0YrBvx,LREYKUWAh yVn", "c_nationkey": 20, "c_phone": "30-114-968-4951", "c_acctbal": 7638.57, "c_mktsegment": "AUTOMOBILE", "c_comment": "tions. even deposits boost according to the slyly bold packages. final accounts cajole requests. furious" } }

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1581-2/query-ASTERIXDB-1581-2.1.adm
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1581-2/query-ASTERIXDB-1581-2.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1581-2/query-ASTERIXDB-1581-2.1.adm
new file mode 100644
index 0000000..c7a4707
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1581-2/query-ASTERIXDB-1581-2.1.adm
@@ -0,0 +1 @@
+{ "bucket1": [ { "$1": 2.16 } ] }

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1581-4/query-ASTERIXDB-1581-4.1.adm
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1581-4/query-ASTERIXDB-1581-4.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1581-4/query-ASTERIXDB-1581-4.1.adm
new file mode 100644
index 0000000..92de03a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1581-4/query-ASTERIXDB-1581-4.1.adm
@@ -0,0 +1 @@
+{ "bucket1": 2.16 }

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1581-6/query-ASTERIXDB-1581-6.1.adm
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1581-6/query-ASTERIXDB-1581-6.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1581-6/query-ASTERIXDB-1581-6.1.adm
new file mode 100644
index 0000000..0db3ede
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1581-6/query-ASTERIXDB-1581-6.1.adm
@@ -0,0 +1 @@
+{ "bucket1": [ { "$2": -377.0216666666667 } ] }

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1581-correlated/query-ASTERIXDB-1581-correlated.1.adm
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1581-correlated/query-ASTERIXDB-1581-correlated.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1581-correlated/query-ASTERIXDB-1581-correlated.1.adm
new file mode 100644
index 0000000..aad62bb
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1581-correlated/query-ASTERIXDB-1581-correlated.1.adm
@@ -0,0 +1 @@
+{ "bucket1": [ { "$1": 0.0 } ] }

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1581/query-ASTERIXDB-1581.1.adm
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1581/query-ASTERIXDB-1581.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1581/query-ASTERIXDB-1581.1.adm
new file mode 100644
index 0000000..faed854
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1581/query-ASTERIXDB-1581.1.adm
@@ -0,0 +1 @@
+{ "bucket1": 2.0 }

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
index a89808e..7fe7c40 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -5671,6 +5671,16 @@
   </test-group>
   <test-group name="subquery">
     <test-case FilePath="subquery">
+      <compilation-unit name="aggregate_join">
+        <output-dir compare="Text">aggregate_join</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="subquery">
+      <compilation-unit name="aggregate_join_external">
+        <output-dir compare="Text">aggregate_join_external</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="subquery">
       <compilation-unit name="gby_inline">
         <output-dir compare="Text">gby_inline</output-dir>
       </compilation-unit>
@@ -5871,6 +5881,46 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="tpcds">
+      <compilation-unit name="query-ASTERIXDB-1581">
+        <output-dir compare="Text">query-ASTERIXDB-1581</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="tpcds">
+      <compilation-unit name="query-ASTERIXDB-1581-2">
+        <output-dir compare="Text">query-ASTERIXDB-1581-2</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="tpcds">
+      <compilation-unit name="query-ASTERIXDB-1581-3">
+        <output-dir compare="Text">query-ASTERIXDB-1581-2</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="tpcds">
+      <compilation-unit name="query-ASTERIXDB-1581-4">
+        <output-dir compare="Text">query-ASTERIXDB-1581-4</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="tpcds">
+      <compilation-unit name="query-ASTERIXDB-1581-5">
+        <output-dir compare="Text">query-ASTERIXDB-1581-2</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="tpcds">
+      <compilation-unit name="query-ASTERIXDB-1581-6">
+        <output-dir compare="Text">query-ASTERIXDB-1581-6</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="tpcds">
+      <compilation-unit name="query-ASTERIXDB-1581-correlated">
+          <output-dir compare="Text">query-ASTERIXDB-1581-correlated</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="tpcds">
+      <compilation-unit name="query-ASTERIXDB-1581-correlated-2">
+        <output-dir compare="Text">query-ASTERIXDB-1581-2</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="tpcds">
       <compilation-unit name="query-ASTERIXDB-1596">
         <output-dir compare="Text">query-ASTERIXDB-1596</output-dir>
       </compilation-unit>

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/AqlDataSource.java
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/AqlDataSource.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/AqlDataSource.java
index d406108..6db28c5 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/AqlDataSource.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/AqlDataSource.java
@@ -77,6 +77,7 @@ public abstract class AqlDataSource implements IDataSource<AqlSourceId> {
         return schemaTypes;
     }
 
+    @Override
     public INodeDomain getDomain() {
         return domain;
     }

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/metadata/IDataSource.java
----------------------------------------------------------------------
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/metadata/IDataSource.java b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/metadata/IDataSource.java
index 63ce5fa..e15b699 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/metadata/IDataSource.java
+++ b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/metadata/IDataSource.java
@@ -22,6 +22,7 @@ import java.util.List;
 
 import org.apache.hyracks.algebricks.core.algebra.base.LogicalVariable;
 import org.apache.hyracks.algebricks.core.algebra.properties.FunctionalDependency;
+import org.apache.hyracks.algebricks.core.algebra.properties.INodeDomain;
 
 public interface IDataSource<T> {
     public T getId();
@@ -34,4 +35,6 @@ public interface IDataSource<T> {
 
     // https://issues.apache.org/jira/browse/ASTERIXDB-1619
     public boolean isScanAccessPathALeaf();
+
+    public INodeDomain getDomain();
 }

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/CardinalityInferenceVisitor.java
----------------------------------------------------------------------
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/CardinalityInferenceVisitor.java b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/CardinalityInferenceVisitor.java
index 8d74de3..ae98d05 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/CardinalityInferenceVisitor.java
+++ b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/CardinalityInferenceVisitor.java
@@ -18,9 +18,16 @@
  */
 package org.apache.hyracks.algebricks.core.algebra.operators.logical.visitors;
 
+import java.util.HashSet;
+import java.util.List;
+import java.util.Set;
+
 import org.apache.commons.lang3.mutable.Mutable;
 import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
+import org.apache.hyracks.algebricks.core.algebra.base.ILogicalExpression;
 import org.apache.hyracks.algebricks.core.algebra.base.ILogicalOperator;
+import org.apache.hyracks.algebricks.core.algebra.base.LogicalVariable;
+import org.apache.hyracks.algebricks.core.algebra.expressions.ConstantExpression;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.AggregateOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.AssignOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.DataSourceScanOperator;
@@ -36,11 +43,11 @@ import org.apache.hyracks.algebricks.core.algebra.operators.logical.InsertDelete
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.IntersectOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.LeftOuterJoinOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.LeftOuterUnnestMapOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.LeftOuterUnnestOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.LimitOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.MaterializeOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.NestedTupleSourceOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.OrderOperator;
-import org.apache.hyracks.algebricks.core.algebra.operators.logical.LeftOuterUnnestOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.PartitioningSplitOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.ProjectOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.ReplicateOperator;
@@ -58,13 +65,29 @@ import org.apache.hyracks.algebricks.core.algebra.operators.logical.WriteResultO
 import org.apache.hyracks.algebricks.core.algebra.visitors.ILogicalOperatorVisitor;
 
 /**
- * A visitor that provides the basic inference of tuple cardinalities of an
- * operator's output. There are only two cases: 1. the cardinality is one in the
- * worst case; 2. the cardinality is some unknown value.
+ * A visitor that provides the basic, static inference of tuple cardinalities of an
+ * operator's output. There are only three cases:
+ * <p/>
+ * 1. ZERO_OR_ONE: the cardinality is either zero or one.
+ * <p/>
+ * 2. ONE: the cardinality is exactly one in any case;
+ * <p/>
+ * 3. ZERO_OR_ONE_GROUP: if we group output tuples of the operator by any variable in <code>keyVariables</code>, it will
+ * result in zero or one group;
+ * <p/>
+ * 4. ONE_GROUP: if we group output tuples of the operator by any variable in <code>keyVariables</code>, it will
+ * result in exact one group;
+ * <p/>
+ * 5. the cardinality is some unknown value.
  */
 public class CardinalityInferenceVisitor implements ILogicalOperatorVisitor<Long, Void> {
-    private static final Long ONE = 1L;
-    private static final Long UNKNOWN = 1000L;
+    private static final long ZERO_OR_ONE = 0L;
+    private static final long ONE = 1L;
+    private static final long ZERO_OR_ONE_GROUP = 2L;
+    private static final long ONE_GROUP = 3L;
+    private static final long UNKNOWN = 1000L;
+
+    private final Set<LogicalVariable> keyVariables = new HashSet<>();
 
     @Override
     public Long visitAggregateOperator(AggregateOperator op, Void arg) throws AlgebricksException {
@@ -78,29 +101,43 @@ public class CardinalityInferenceVisitor implements ILogicalOperatorVisitor<Long
 
     @Override
     public Long visitEmptyTupleSourceOperator(EmptyTupleSourceOperator op, Void arg) throws AlgebricksException {
-        // Empty tuple source sends one empty tuple to kick off the pipeline.
+        // Empty tuple source operator sends an empty tuple to downstream operators.
         return ONE;
     }
 
     @Override
     public Long visitGroupByOperator(GroupByOperator op, Void arg) throws AlgebricksException {
-        return op.getInputs().get(0).getValue().accept(this, arg);
+        ILogicalOperator inputOp = op.getInputs().get(0).getValue();
+        long inputCardinality = inputOp.accept(this, arg);
+        List<LogicalVariable> gbyVar = op.getGbyVarList();
+        if (inputCardinality == ONE_GROUP && keyVariables.containsAll(gbyVar)) {
+            keyVariables.clear();
+            return ONE;
+        }
+        if (inputCardinality == ZERO_OR_ONE_GROUP && keyVariables.containsAll(gbyVar)) {
+            keyVariables.clear();
+            return ZERO_OR_ONE;
+        }
+        // ZERO_OR_ONE, ONE, ZERO_OR_ONE_GROUP, ONE_GROUP, OR UNKNOWN
+        return inputCardinality;
     }
 
     @Override
     public Long visitLimitOperator(LimitOperator op, Void arg) throws AlgebricksException {
-        // This is only a worst-case estimate
-        return op.getInputs().get(0).getValue().accept(this, arg);
+        return adjustCardinalityForTupleReductionOperator(op.getInputs().get(0).getValue().accept(this, arg));
     }
 
     @Override
     public Long visitInnerJoinOperator(InnerJoinOperator op, Void arg) throws AlgebricksException {
-        return visitJoin(op, arg);
+        // Inner join can have 0 to M * N tuples, where M is the cardinality of the left input branch
+        // and N is the cardinality of the right input branch.
+        // We only provide inference for the case the JOIN condition is TRUE.
+        return visitInnerJoin(op, arg);
     }
 
     @Override
     public Long visitLeftOuterJoinOperator(LeftOuterJoinOperator op, Void arg) throws AlgebricksException {
-        return visitJoin(op, arg);
+        return visitLeftOuterUnnest(op, arg);
     }
 
     @Override
@@ -120,8 +157,7 @@ public class CardinalityInferenceVisitor implements ILogicalOperatorVisitor<Long
 
     @Override
     public Long visitSelectOperator(SelectOperator op, Void arg) throws AlgebricksException {
-        // This is only a worst-case inference.
-        return op.getInputs().get(0).getValue().accept(this, arg);
+        return adjustCardinalityForTupleReductionOperator(op.getInputs().get(0).getValue().accept(this, arg));
     }
 
     @Override
@@ -131,6 +167,7 @@ public class CardinalityInferenceVisitor implements ILogicalOperatorVisitor<Long
 
     @Override
     public Long visitProjectOperator(ProjectOperator op, Void arg) throws AlgebricksException {
+        keyVariables.retainAll(op.getVariables()); // Only returns live variables.
         return op.getInputs().get(0).getValue().accept(this, arg);
     }
 
@@ -176,7 +213,7 @@ public class CardinalityInferenceVisitor implements ILogicalOperatorVisitor<Long
 
     @Override
     public Long visitLeftOuterUnnestOperator(LeftOuterUnnestOperator op, Void arg) throws AlgebricksException {
-        return UNKNOWN;
+        return visitLeftOuterUnnest(op, arg);
     }
 
     @Override
@@ -186,7 +223,7 @@ public class CardinalityInferenceVisitor implements ILogicalOperatorVisitor<Long
 
     @Override
     public Long visitLeftOuterUnnestMapOperator(LeftOuterUnnestMapOperator op, Void arg) throws AlgebricksException {
-        return UNKNOWN;
+        return visitLeftOuterUnnest(op, arg);
     }
 
     @Override
@@ -239,23 +276,96 @@ public class CardinalityInferenceVisitor implements ILogicalOperatorVisitor<Long
     public Long visitIntersectOperator(IntersectOperator op, Void arg) throws AlgebricksException {
         long cardinality = UNKNOWN;
         for (Mutable<ILogicalOperator> inputOpRef : op.getInputs()) {
-            Long branchCardinality = inputOpRef.getValue().accept(this, arg);
-            if (branchCardinality < cardinality) {
-                cardinality = branchCardinality;
+            long inputCardinality = inputOpRef.getValue().accept(this, arg);
+            if (inputCardinality <= ONE) {
+                return ZERO_OR_ONE;
+            }
+            if (inputCardinality == ZERO_OR_ONE_GROUP || inputCardinality == ONE_GROUP) {
+                cardinality = ZERO_OR_ONE_GROUP;
             }
         }
         return cardinality;
     }
 
-    private long visitJoin(ILogicalOperator op, Void arg) throws AlgebricksException {
-        long cardinality = 1L;
-        for (Mutable<ILogicalOperator> inputOpRef : op.getInputs()) {
-            cardinality *= inputOpRef.getValue().accept(this, arg);
+    // Visits an operator that has the left outer semantics, e.g.,
+    // left outer join, left outer unnest, left outer unnest map.
+    private long visitLeftOuterUnnest(ILogicalOperator operator, Void arg) throws AlgebricksException {
+        ILogicalOperator left = operator.getInputs().get(0).getValue();
+        long leftCardinality = left.accept(this, arg);
+        if (leftCardinality == ONE) {
+            keyVariables.clear();
+            VariableUtilities.getLiveVariables(left, keyVariables);
+            return ONE_GROUP;
         }
-        if (cardinality > ONE) {
-            cardinality = UNKNOWN;
+        if (leftCardinality == ZERO_OR_ONE) {
+            keyVariables.clear();
+            VariableUtilities.getLiveVariables(left, keyVariables);
+            return ZERO_OR_ONE_GROUP;
         }
-        return cardinality;
+        // ZERO_OR_ONE_GROUP, ONE_GROUP (maintained from the left branch) or UNKNOWN.
+        return leftCardinality;
+    }
+
+    // Visits an inner join operator, particularly, deals with the case the join is a cartesian product.
+    private long visitInnerJoin(InnerJoinOperator joinOperator, Void arg) throws AlgebricksException {
+        ILogicalExpression conditionExpr = joinOperator.getCondition().getValue();
+        if (!conditionExpr.equals(ConstantExpression.TRUE)) {
+            // Currently we are not able to estimate for more general join conditions.
+            return UNKNOWN;
+        }
+        Set<LogicalVariable> newKeyVars = new HashSet<>();
+
+        // Visits the left branch and adds left key variables (if the left branch delivers one group).
+        ILogicalOperator left = joinOperator.getInputs().get(0).getValue();
+        long leftCardinality = left.accept(this, arg);
+        newKeyVars.addAll(keyVariables);
+
+        // Visits the right branch and adds right key variables (if the right branch delivers one group).
+        ILogicalOperator right = joinOperator.getInputs().get(1).getValue();
+        long rightCardinality = right.accept(this, arg);
+        newKeyVars.addAll(keyVariables);
+
+        // If any branch has carinality zero or one, the result will have cardinality zero or one.
+        if (leftCardinality == ZERO_OR_ONE && rightCardinality == ZERO_OR_ONE) {
+            return ZERO_OR_ONE;
+        }
+
+        // If both branches has cardinality one, the result for sure has cardinality one.
+        if (leftCardinality == ONE && rightCardinality == ONE) {
+            return ONE;
+        }
+
+        keyVariables.clear();
+        // If one branch has cardinality zero_or_one, the result for sure has cardinality one.
+        if (leftCardinality == ZERO_OR_ONE || rightCardinality == ZERO_OR_ONE) {
+            VariableUtilities.getLiveVariables(leftCardinality == ONE ? left : right, keyVariables);
+            return ZERO_OR_ONE_GROUP;
+        }
+
+        // If one branch has cardinality one, the result has one group.
+        if (leftCardinality == ONE || rightCardinality == ONE) {
+            VariableUtilities.getLiveVariables(leftCardinality == ONE ? left : right, keyVariables);
+            return ONE_GROUP;
+        }
+
+        // If one branch has zero or one group, the result has one zero or one group.
+        if (leftCardinality == ONE_GROUP || rightCardinality == ONE_GROUP || leftCardinality == ZERO_OR_ONE_GROUP
+                || rightCardinality == ZERO_OR_ONE_GROUP) {
+            keyVariables.addAll(newKeyVars);
+            return Math.min(leftCardinality, rightCardinality);
+        }
+        return UNKNOWN;
+    }
+
+    // For operators including SELECT and LIMIT.
+    private long adjustCardinalityForTupleReductionOperator(long inputCardinality) {
+        if (inputCardinality == ONE) {
+            return ZERO_OR_ONE;
+        }
+        if (inputCardinality == ONE_GROUP) {
+            return ZERO_OR_ONE_GROUP;
+        }
+        return inputCardinality;
     }
 
 }

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c89d668f/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/IsomorphismVariableMappingVisitor.java
----------------------------------------------------------------------
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/IsomorphismVariableMappingVisitor.java b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/IsomorphismVariableMappingVisitor.java
index 7909499..965008c 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/IsomorphismVariableMappingVisitor.java
+++ b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/IsomorphismVariableMappingVisitor.java
@@ -22,8 +22,8 @@ import java.util.ArrayList;
 import java.util.HashSet;
 import java.util.List;
 import java.util.Map;
-import java.util.Map.Entry;
 import java.util.Set;
+import java.util.Map.Entry;
 
 import org.apache.commons.lang3.mutable.Mutable;
 import org.apache.commons.lang3.mutable.MutableObject;
@@ -293,10 +293,13 @@ public class IsomorphismVariableMappingVisitor implements ILogicalOperatorVisito
     }
 
     private void mapChildren(ILogicalOperator op, ILogicalOperator opArg) throws AlgebricksException {
+        if (op.getOperatorTag() != opArg.getOperatorTag()) {
+            return;
+        }
         List<Mutable<ILogicalOperator>> inputs = op.getInputs();
         List<Mutable<ILogicalOperator>> inputsArg = opArg.getInputs();
         if (inputs.size() != inputsArg.size()) {
-            throw new AlgebricksException("children are not isomoprhic");
+            return;
         }
         for (int i = 0; i < inputs.size(); i++) {
             ILogicalOperator input = inputs.get(i).getValue();
@@ -306,8 +309,11 @@ public class IsomorphismVariableMappingVisitor implements ILogicalOperatorVisito
     }
 
     private void mapVariables(ILogicalOperator left, ILogicalOperator right) throws AlgebricksException {
-        List<LogicalVariable> producedVarLeft = new ArrayList<LogicalVariable>();
-        List<LogicalVariable> producedVarRight = new ArrayList<LogicalVariable>();
+        if (left.getOperatorTag() != right.getOperatorTag()) {
+            return;
+        }
+        List<LogicalVariable> producedVarLeft = new ArrayList<>();
+        List<LogicalVariable> producedVarRight = new ArrayList<>();
         VariableUtilities.getProducedVariables(left, producedVarLeft);
         VariableUtilities.getProducedVariables(right, producedVarRight);
         mapVariables(producedVarLeft, producedVarRight);
@@ -327,6 +333,9 @@ public class IsomorphismVariableMappingVisitor implements ILogicalOperatorVisito
 
     private void mapVariablesForAbstractAssign(ILogicalOperator left, ILogicalOperator right)
             throws AlgebricksException {
+        if (left.getOperatorTag() != right.getOperatorTag()) {
+            return;
+        }
         AbstractAssignOperator leftOp = (AbstractAssignOperator) left;
         AbstractAssignOperator rightOp = (AbstractAssignOperator) right;
         List<LogicalVariable> producedVarLeft = new ArrayList<LogicalVariable>();
@@ -338,6 +347,9 @@ public class IsomorphismVariableMappingVisitor implements ILogicalOperatorVisito
     }
 
     private void mapVariablesForGroupBy(ILogicalOperator left, ILogicalOperator right) throws AlgebricksException {
+        if (left.getOperatorTag() != right.getOperatorTag()) {
+            return;
+        }
         GroupByOperator leftOp = (GroupByOperator) left;
         GroupByOperator rightOp = (GroupByOperator) right;
         List<Pair<LogicalVariable, Mutable<ILogicalExpression>>> leftPairs = leftOp.getGroupByList();
@@ -416,6 +428,9 @@ public class IsomorphismVariableMappingVisitor implements ILogicalOperatorVisito
     }
 
     private void mapVariablesStandard(ILogicalOperator op, ILogicalOperator arg) throws AlgebricksException {
+        if (op.getOperatorTag() != arg.getOperatorTag()) {
+            return;
+        }
         mapChildren(op, arg);
         mapVariables(op, arg);
     }
@@ -429,6 +444,9 @@ public class IsomorphismVariableMappingVisitor implements ILogicalOperatorVisito
     }
 
     private void mapVariablesForUnion(ILogicalOperator op, ILogicalOperator arg) {
+        if (op.getOperatorTag() != arg.getOperatorTag()) {
+            return;
+        }
         UnionAllOperator union = (UnionAllOperator) op;
         UnionAllOperator unionArg = (UnionAllOperator) arg;
         mapVarTripleList(union.getVariableMappings(), unionArg.getVariableMappings());
@@ -456,6 +474,9 @@ public class IsomorphismVariableMappingVisitor implements ILogicalOperatorVisito
     }
 
     private void mapVariablesForIntersect(IntersectOperator op, ILogicalOperator arg) {
+        if (op.getOperatorTag() != arg.getOperatorTag()) {
+            return;
+        }
         IntersectOperator opArg = (IntersectOperator) arg;
         if (op.getNumInput() != opArg.getNumInput()) {
             return;


Mime
View raw message