diff --git a/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1047.aql b/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1047.aql
new file mode 100644
index 0000000..4210fc3
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1047.aql
@@ -0,0 +1,40 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/**
+ * This query is to verify the fix of ASTERIXDB-1047.
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type DBLPType as open {
+  id: int64,
+  dblpid: string,
+  title: string,
+  authors: string,
+  misc: string
+}
+
+create dataset DBLP(DBLPType) primary key id;
+
+
+(for $d in dataset DBLP where $d.id = 1 return $d)
+  union
+(for $d in dataset DBLP where $d.authors = "Alfred V. Aho John E. Hopcroft Jeffrey D. Ullman" return $d);
diff --git a/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1205-2.aql b/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1205-2.aql
new file mode 100644
index 0000000..82d1430
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1205-2.aql
@@ -0,0 +1,28 @@
+/*
+ * 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.
+ */
+/**
+ * This query is to verify the fix of ASTERIXDB-1205.
+ */
+
+let $aa := [{"fa":1, "fb":1}, {"fa":2, "fb":1}]
+let $bb := [{"fa":1, "fb":0}, {"fa":1, "fb":1}, {"fa":3, "fb":1}]
+
+for $tt in ($aa union $bb)
+order by $tt.fa, $tt.fb
+return $tt;
diff --git a/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1205-3.aql b/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1205-3.aql
new file mode 100644
index 0000000..2ab92dc
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1205-3.aql
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/**
+ * This query is to verify the fix of ASTERIXDB-1205.
+ */
+
+let $aa := [{"fa":1, "fb":1}, {"fa":2, "fb":1}]
+let $bb := [{"fa":1, "fb":0}, {"fa":1, "fb":1}, {"fa":3, "fb":1}]
+return $aa union $bb;
\ No newline at end of file
diff --git a/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1205-4.aql b/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1205-4.aql
new file mode 100644
index 0000000..48f7c2a
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1205-4.aql
@@ -0,0 +1,30 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/**
+ * This query is to verify the fix of ASTERIXDB-1205.
+ */
+
+let $aa := [{"fa":1, "fb":1}, {"fa":2, "fb":1}]
+let $bb := [{"fa":1, "fb":0}, {"fa":1, "fb":1}, {"fa":3, "fb":1}]
+let $dd := [{"fa":4, "fb":5}, {"fa":6, "fb":7}]
+let $ccc := $aa union $bb union $dd
+
+for $tt in $ccc
+order by $tt.fa, $tt.fb
+return $tt;
\ No newline at end of file
diff --git a/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1205.aql b/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1205.aql
new file mode 100644
index 0000000..763aba1
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1205.aql
@@ -0,0 +1,29 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/**
+ * This query is to verify the fix of ASTERIXDB-1205.
+ */
+
+let $aa := [{"fa":1, "fb":1}, {"fa":2, "fb":1}]
+let $bb := [{"fa":1, "fb":0}, {"fa":1, "fb":1}, {"fa":3, "fb":1}]
+let $ccc := $aa union $bb
+
+for $tt in $ccc
+order by $tt.fa, $tt.fb
+return $tt;
diff --git a/asterix-app/src/test/resources/optimizerts/queries/union/union_dataset.aql b/asterix-app/src/test/resources/optimizerts/queries/union/union_dataset.aql
new file mode 100644
index 0000000..7a55ee5
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/union/union_dataset.aql
@@ -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.
+ */
+drop dataverse TinySocial if exists;
+create dataverse TinySocial;
+use dataverse TinySocial;
+
+create type FacebookUserType as open {
+        id: int
+}
+
+create dataset FacebookUsers(FacebookUserType)
+primary key id;
+
+create dataset FacebookUsers2(FacebookUserType)
+primary key id;
+
+
+let $c := dataset("FacebookUsers") union dataset("FacebookUsers2")
+for $res in $c order by $res.id return $res
diff --git a/asterix-app/src/test/resources/optimizerts/queries/union/union_dataset2.aql b/asterix-app/src/test/resources/optimizerts/queries/union/union_dataset2.aql
new file mode 100644
index 0000000..049652f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/union/union_dataset2.aql
@@ -0,0 +1,33 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+drop dataverse TinySocial if exists;
+create dataverse TinySocial;
+use dataverse TinySocial;
+
+create type FacebookUserType as open {
+        id: int
+}
+
+create dataset FacebookUsers(FacebookUserType)
+primary key id;
+
+create dataset FacebookUsers2(FacebookUserType)
+primary key id;
+
+dataset("FacebookUsers") union dataset("FacebookUsers2");
diff --git a/asterix-app/src/test/resources/optimizerts/queries/union/union_dataset3.aql b/asterix-app/src/test/resources/optimizerts/queries/union/union_dataset3.aql
new file mode 100644
index 0000000..edb429f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/union/union_dataset3.aql
@@ -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.
+ */
+drop dataverse TinySocial if exists;
+create dataverse TinySocial;
+use dataverse TinySocial;
+
+create type FacebookUserType as open {
+        id: int
+}
+
+create dataset FacebookUsers(FacebookUserType)
+primary key id;
+
+create dataset FacebookUsers2(FacebookUserType)
+primary key id;
+
+
+for $res in (dataset("FacebookUsers") union dataset("FacebookUsers2"))
+order by $res.id return $res
diff --git a/asterix-app/src/test/resources/optimizerts/queries/union/union_query.aql b/asterix-app/src/test/resources/optimizerts/queries/union/union_query.aql
new file mode 100644
index 0000000..3e6ebaf
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/union/union_query.aql
@@ -0,0 +1,41 @@
+/*
+ * 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 dataverse TinySocial;
+
+create type FacebookUserType as open {
+        id: int
+}
+
+create type FacebookMessageType as open {
+        message-id: int
+}
+
+create dataset FacebookUsers(FacebookUserType)
+primary key id;
+
+create dataset FacebookMessages(FacebookMessageType)
+primary key message-id;
+
+let $t1 := for $t in dataset FacebookUsers return $t.id
+let $t2 := for $s in dataset FacebookMessages return $s.message-id
+let $c := $t1 union $t2
+for $res in $c distinct by $res order by $res return $res
diff --git a/asterix-app/src/test/resources/optimizerts/results/q05_local_supplier_volume.plan b/asterix-app/src/test/resources/optimizerts/results/q05_local_supplier_volume.plan
index 1f99eee..9d17ca8 100644
--- a/asterix-app/src/test/resources/optimizerts/results/q05_local_supplier_volume.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/q05_local_supplier_volume.plan
@@ -2,8 +2,8 @@
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
-        -- SORT_MERGE_EXCHANGE [$$100(DESC) ]  |PARTITIONED|
-          -- STABLE_SORT [$$100(DESC)]  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$89(DESC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$89(DESC)]  |PARTITIONED|
             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
               -- EXTERNAL_GROUP_BY[$$119]  |PARTITIONED|
                       {
@@ -19,7 +19,7 @@
                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                       -- STREAM_PROJECT  |PARTITIONED|
                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                          -- HYBRID_HASH_JOIN [$$81, $$98][$$115, $$88]  |PARTITIONED|
+                          -- HYBRID_HASH_JOIN [$$94, $$81][$$88, $$115]  |PARTITIONED|
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                               -- STREAM_PROJECT  |PARTITIONED|
                                 -- ASSIGN  |PARTITIONED|
@@ -66,8 +66,8 @@
                                                     -- HASH_PARTITION_EXCHANGE [$$86]  |PARTITIONED|
                                                       -- STREAM_PROJECT  |PARTITIONED|
                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                          -- HYBRID_HASH_JOIN [$$89][$$87]  |PARTITIONED|
-                                                            -- HASH_PARTITION_EXCHANGE [$$89]  |PARTITIONED|
+                                                          -- HYBRID_HASH_JOIN [$$90][$$87]  |PARTITIONED|
+                                                            -- HASH_PARTITION_EXCHANGE [$$90]  |PARTITIONED|
                                                               -- STREAM_PROJECT  |PARTITIONED|
                                                                 -- ASSIGN  |PARTITIONED|
                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/q08_group_by.plan b/asterix-app/src/test/resources/optimizerts/results/q08_group_by.plan
index 601091e..1aca471 100644
--- a/asterix-app/src/test/resources/optimizerts/results/q08_group_by.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/q08_group_by.plan
@@ -31,8 +31,8 @@
                               -- HASH_PARTITION_EXCHANGE [$$81]  |PARTITIONED|
                                 -- STREAM_PROJECT  |PARTITIONED|
                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                    -- HYBRID_HASH_JOIN [$$92][$$82]  |PARTITIONED|
-                                      -- HASH_PARTITION_EXCHANGE [$$92]  |PARTITIONED|
+                                    -- HYBRID_HASH_JOIN [$$94][$$82]  |PARTITIONED|
+                                      -- HASH_PARTITION_EXCHANGE [$$94]  |PARTITIONED|
                                         -- STREAM_SELECT  |PARTITIONED|
                                           -- STREAM_PROJECT  |PARTITIONED|
                                             -- ASSIGN  |PARTITIONED|
@@ -43,8 +43,8 @@
                                       -- HASH_PARTITION_EXCHANGE [$$82]  |PARTITIONED|
                                         -- STREAM_PROJECT  |PARTITIONED|
                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                            -- HYBRID_HASH_JOIN [$$89][$$83]  |PARTITIONED|
-                                              -- HASH_PARTITION_EXCHANGE [$$89]  |PARTITIONED|
+                                            -- HYBRID_HASH_JOIN [$$91][$$83]  |PARTITIONED|
+                                              -- HASH_PARTITION_EXCHANGE [$$91]  |PARTITIONED|
                                                 -- STREAM_PROJECT  |PARTITIONED|
                                                   -- ASSIGN  |PARTITIONED|
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -54,8 +54,8 @@
                                               -- HASH_PARTITION_EXCHANGE [$$83]  |PARTITIONED|
                                                 -- STREAM_PROJECT  |PARTITIONED|
                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                    -- HYBRID_HASH_JOIN [$$87][$$84]  |PARTITIONED|
-                                                      -- HASH_PARTITION_EXCHANGE [$$87]  |PARTITIONED|
+                                                    -- HYBRID_HASH_JOIN [$$89][$$84]  |PARTITIONED|
+                                                      -- HASH_PARTITION_EXCHANGE [$$89]  |PARTITIONED|
                                                         -- STREAM_PROJECT  |PARTITIONED|
                                                           -- ASSIGN  |PARTITIONED|
                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/q09_group_by.plan b/asterix-app/src/test/resources/optimizerts/results/q09_group_by.plan
index fc2abd2..9f48723 100644
--- a/asterix-app/src/test/resources/optimizerts/results/q09_group_by.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/q09_group_by.plan
@@ -15,7 +15,7 @@
               -- HASH_PARTITION_EXCHANGE [$$84]  |PARTITIONED|
                 -- STREAM_PROJECT  |PARTITIONED|
                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                    -- HYBRID_HASH_JOIN [$$64, $$63][$$69, $$84]  |PARTITIONED|
+                    -- HYBRID_HASH_JOIN [$$63, $$64][$$84, $$69]  |PARTITIONED|
                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                         -- STREAM_PROJECT  |PARTITIONED|
                           -- ASSIGN  |PARTITIONED|
@@ -30,8 +30,8 @@
                               -- HASH_PARTITION_EXCHANGE [$$65]  |PARTITIONED|
                                 -- STREAM_PROJECT  |PARTITIONED|
                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                    -- HYBRID_HASH_JOIN [$$77][$$66]  |PARTITIONED|
-                                      -- HASH_PARTITION_EXCHANGE [$$77]  |PARTITIONED|
+                                    -- HYBRID_HASH_JOIN [$$72][$$66]  |PARTITIONED|
+                                      -- HASH_PARTITION_EXCHANGE [$$72]  |PARTITIONED|
                                         -- STREAM_PROJECT  |PARTITIONED|
                                           -- ASSIGN  |PARTITIONED|
                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/query-issue562.plan b/asterix-app/src/test/resources/optimizerts/results/query-issue562.plan
index 5062719..28625aa 100644
--- a/asterix-app/src/test/resources/optimizerts/results/query-issue562.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/query-issue562.plan
@@ -3,12 +3,12 @@
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
         -- SORT_MERGE_EXCHANGE [$$7(ASC) ]  |PARTITIONED|
-          -- PRE_CLUSTERED_GROUP_BY[$$89]  |PARTITIONED|
+          -- PRE_CLUSTERED_GROUP_BY[$$86]  |PARTITIONED|
                   {
                     -- AGGREGATE  |LOCAL|
                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                   }
-            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$89(ASC)] HASH:[$$89]  |PARTITIONED|
+            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$86(ASC)] HASH:[$$86]  |PARTITIONED|
               -- SORT_GROUP_BY[$$59]  |PARTITIONED|
                       {
                         -- AGGREGATE  |LOCAL|
@@ -21,46 +21,48 @@
                         -- STREAM_SELECT  |PARTITIONED|
                           -- STREAM_PROJECT  |PARTITIONED|
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              -- PRE_CLUSTERED_GROUP_BY[$$86]  |PARTITIONED|
+                              -- SORT_GROUP_BY[$$83]  |PARTITIONED|
                                       {
                                         -- AGGREGATE  |LOCAL|
                                           -- NESTED_TUPLE_SOURCE  |LOCAL|
                                       }
-                                -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$86(ASC)] HASH:[$$86]  |PARTITIONED|
-                                  -- PRE_CLUSTERED_GROUP_BY[$$62]  |PARTITIONED|
-                                          {
-                                            -- AGGREGATE  |LOCAL|
-                                              -- STREAM_SELECT  |LOCAL|
-                                                -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                          }
+                                -- HASH_PARTITION_EXCHANGE [$$83]  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                      -- STABLE_SORT [$$62(ASC)]  |PARTITIONED|
+                                      -- PRE_CLUSTERED_GROUP_BY[$$65, $$11, $$62]  |PARTITIONED|
+                                              {
+                                                -- AGGREGATE  |LOCAL|
+                                                  -- STREAM_SELECT  |LOCAL|
+                                                    -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                              }
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                          -- STREAM_PROJECT  |PARTITIONED|
+                                          -- STABLE_SORT [$$65(ASC), $$65(ASC), $$62(ASC)]  |PARTITIONED|
                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                              -- HYBRID_HASH_JOIN [$$67][$$68]  |PARTITIONED|
-                                                -- HASH_PARTITION_EXCHANGE [$$67]  |PARTITIONED|
-                                                  -- ASSIGN  |PARTITIONED|
-                                                    -- STREAM_PROJECT  |PARTITIONED|
-                                                      -- ASSIGN  |PARTITIONED|
-                                                        -- STREAM_PROJECT  |PARTITIONED|
-                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                            -- HYBRID_HASH_JOIN [$$83][$$82]  |PARTITIONED|
-                                                              -- HASH_PARTITION_EXCHANGE [$$83]  |PARTITIONED|
-                                                                -- UNNEST  |UNPARTITIONED|
-                                                                  -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
-                                                              -- HASH_PARTITION_EXCHANGE [$$82]  |PARTITIONED|
-                                                                -- ASSIGN  |PARTITIONED|
-                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                              -- STABLE_SORT [$$65(ASC), $$65(ASC), $$62(ASC)]  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- HYBRID_HASH_JOIN [$$68][$$69]  |PARTITIONED|
+                                                        -- HASH_PARTITION_EXCHANGE [$$68]  |PARTITIONED|
+                                                          -- ASSIGN  |PARTITIONED|
+                                                            -- ASSIGN  |PARTITIONED|
+                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                -- HYBRID_HASH_JOIN [$$65][$$11]  |PARTITIONED|
+                                                                  -- HASH_PARTITION_EXCHANGE [$$65]  |PARTITIONED|
+                                                                    -- UNNEST  |UNPARTITIONED|
+                                                                      -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+                                                                  -- HASH_PARTITION_EXCHANGE [$$11]  |PARTITIONED|
                                                                     -- ASSIGN  |PARTITIONED|
-                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                        -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                                        -- ASSIGN  |PARTITIONED|
                                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                -- HASH_PARTITION_EXCHANGE [$$68]  |PARTITIONED|
-                                                  -- ASSIGN  |PARTITIONED|
-                                                    -- STREAM_PROJECT  |PARTITIONED|
-                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                        -- DATASOURCE_SCAN  |PARTITIONED|
-                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                            -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                        -- HASH_PARTITION_EXCHANGE [$$69]  |PARTITIONED|
+                                                          -- ASSIGN  |PARTITIONED|
+                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/split-materialization-above-join.plan b/asterix-app/src/test/resources/optimizerts/results/split-materialization-above-join.plan
index 19eaf25..f22f71a 100644
--- a/asterix-app/src/test/resources/optimizerts/results/split-materialization-above-join.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/split-materialization-above-join.plan
@@ -8,151 +8,153 @@
               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                 -- STREAM_PROJECT  |PARTITIONED|
                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                    -- HYBRID_HASH_JOIN [$$94, $$93][$$26, $$25]  |PARTITIONED|
-                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                        -- NESTED_LOOP  |PARTITIONED|
-                          -- BROADCAST_EXCHANGE  |PARTITIONED|
-                            -- DATASOURCE_SCAN  |PARTITIONED|
-                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                            -- STREAM_PROJECT  |PARTITIONED|
-                              -- ASSIGN  |PARTITIONED|
-                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                  -- SPLIT  |PARTITIONED|
-                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                      -- DATASOURCE_SCAN  |PARTITIONED|
-                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                    -- HYBRID_HASH_JOIN [$$26][$$94]  |PARTITIONED|
                       -- HASH_PARTITION_EXCHANGE [$$26]  |PARTITIONED|
-                        -- PRE_CLUSTERED_GROUP_BY[$$122, $$124]  |PARTITIONED|
-                                {
-                                  -- AGGREGATE  |LOCAL|
-                                    -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                }
+                        -- STREAM_PROJECT  |PARTITIONED|
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                            -- STABLE_SORT [$$122(ASC), $$124(ASC)]  |PARTITIONED|
-                              -- HASH_PARTITION_EXCHANGE [$$122, $$124]  |PARTITIONED|
-                                -- STREAM_PROJECT  |PARTITIONED|
-                                  -- ASSIGN  |PARTITIONED|
-                                    -- STREAM_SELECT  |PARTITIONED|
-                                      -- STREAM_PROJECT  |PARTITIONED|
-                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                          -- HYBRID_HASH_JOIN [$$12][$$23]  |PARTITIONED|
-                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- HYBRID_HASH_JOIN [$$93][$$25]  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- DATASOURCE_SCAN  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                              -- HASH_PARTITION_EXCHANGE [$$25]  |PARTITIONED|
+                                -- PRE_CLUSTERED_GROUP_BY[$$122, $$124]  |PARTITIONED|
+                                        {
+                                          -- AGGREGATE  |LOCAL|
+                                            -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                        }
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- STABLE_SORT [$$122(ASC), $$124(ASC)]  |PARTITIONED|
+                                      -- HASH_PARTITION_EXCHANGE [$$122, $$124]  |PARTITIONED|
+                                        -- STREAM_PROJECT  |PARTITIONED|
+                                          -- ASSIGN  |PARTITIONED|
+                                            -- STREAM_SELECT  |PARTITIONED|
                                               -- STREAM_PROJECT  |PARTITIONED|
-                                                -- ASSIGN  |PARTITIONED|
-                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                    -- SPLIT  |PARTITIONED|
-                                                      -- HASH_PARTITION_EXCHANGE [$$23]  |PARTITIONED|
-                                                        -- UNNEST  |PARTITIONED|
-                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                            -- PRE_CLUSTERED_GROUP_BY[$$97]  |PARTITIONED|
-                                                                    {
-                                                                      -- AGGREGATE  |LOCAL|
-                                                                        -- STREAM_SELECT  |LOCAL|
-                                                                          -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                                                    }
-                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                -- STABLE_SORT [$$97(ASC), $$21(ASC)]  |PARTITIONED|
-                                                                  -- HASH_PARTITION_EXCHANGE [$$97]  |PARTITIONED|
-                                                                    -- STREAM_PROJECT  |PARTITIONED|
-                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                        -- HYBRID_HASH_JOIN [$$15][$$18]  |PARTITIONED|
-                                                                          -- HASH_PARTITION_EXCHANGE [$$15]  |PARTITIONED|
-                                                                            -- STREAM_PROJECT  |PARTITIONED|
-                                                                              -- UNNEST  |PARTITIONED|
-                                                                                -- ASSIGN  |PARTITIONED|
-                                                                                  -- STREAM_PROJECT  |PARTITIONED|
-                                                                                    -- ASSIGN  |PARTITIONED|
-                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                        -- DATASOURCE_SCAN  |PARTITIONED|
-                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                          -- HASH_PARTITION_EXCHANGE [$$18]  |PARTITIONED|
-                                                                            -- ASSIGN  |PARTITIONED|
-                                                                              -- RUNNING_AGGREGATE  |PARTITIONED|
-                                                                                -- STREAM_PROJECT  |PARTITIONED|
-                                                                                  -- SORT_MERGE_EXCHANGE [$$105(ASC), $$18(ASC) ]  |PARTITIONED|
-                                                                                    -- STABLE_SORT [$$105(ASC), $$18(ASC)]  |PARTITIONED|
-                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                        -- PRE_CLUSTERED_GROUP_BY[$$130]  |PARTITIONED|
-                                                                                                {
-                                                                                                  -- AGGREGATE  |LOCAL|
-                                                                                                    -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                                                                                }
-                                                                                          -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$130(ASC)] HASH:[$$130]  |PARTITIONED|
-                                                                                            -- SORT_GROUP_BY[$$17]  |PARTITIONED|
-                                                                                                    {
-                                                                                                      -- AGGREGATE  |LOCAL|
-                                                                                                        -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                                                                                    }
-                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                  -- UNNEST  |PARTITIONED|
-                                                                                                    -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                      -- ASSIGN  |PARTITIONED|
-                                                                                                        -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                            -- SPLIT  |PARTITIONED|
-                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                -- DATASOURCE_SCAN  |PARTITIONED|
-                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                              -- SPLIT  |PARTITIONED|
-                                                -- HASH_PARTITION_EXCHANGE [$$23]  |PARTITIONED|
-                                                  -- UNNEST  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- HYBRID_HASH_JOIN [$$12][$$23]  |PARTITIONED|
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                      -- PRE_CLUSTERED_GROUP_BY[$$97]  |PARTITIONED|
-                                                              {
-                                                                -- AGGREGATE  |LOCAL|
-                                                                  -- STREAM_SELECT  |LOCAL|
-                                                                    -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                                              }
-                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                          -- STABLE_SORT [$$97(ASC), $$21(ASC)]  |PARTITIONED|
-                                                            -- HASH_PARTITION_EXCHANGE [$$97]  |PARTITIONED|
-                                                              -- STREAM_PROJECT  |PARTITIONED|
-                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                  -- HYBRID_HASH_JOIN [$$15][$$18]  |PARTITIONED|
-                                                                    -- HASH_PARTITION_EXCHANGE [$$15]  |PARTITIONED|
-                                                                      -- STREAM_PROJECT  |PARTITIONED|
-                                                                        -- UNNEST  |PARTITIONED|
-                                                                          -- ASSIGN  |PARTITIONED|
+                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                        -- ASSIGN  |PARTITIONED|
+                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                            -- SPLIT  |PARTITIONED|
+                                                              -- HASH_PARTITION_EXCHANGE [$$23]  |PARTITIONED|
+                                                                -- UNNEST  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- PRE_CLUSTERED_GROUP_BY[$$97]  |PARTITIONED|
+                                                                            {
+                                                                              -- AGGREGATE  |LOCAL|
+                                                                                -- STREAM_SELECT  |LOCAL|
+                                                                                  -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                                            }
+                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                        -- STABLE_SORT [$$97(ASC), $$21(ASC)]  |PARTITIONED|
+                                                                          -- HASH_PARTITION_EXCHANGE [$$97]  |PARTITIONED|
                                                                             -- STREAM_PROJECT  |PARTITIONED|
-                                                                              -- ASSIGN  |PARTITIONED|
-                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                  -- DATASOURCE_SCAN  |PARTITIONED|
-                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                    -- HASH_PARTITION_EXCHANGE [$$18]  |PARTITIONED|
-                                                                      -- ASSIGN  |PARTITIONED|
-                                                                        -- RUNNING_AGGREGATE  |PARTITIONED|
-                                                                          -- STREAM_PROJECT  |PARTITIONED|
-                                                                            -- SORT_MERGE_EXCHANGE [$$105(ASC), $$18(ASC) ]  |PARTITIONED|
-                                                                              -- STABLE_SORT [$$105(ASC), $$18(ASC)]  |PARTITIONED|
-                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                  -- PRE_CLUSTERED_GROUP_BY[$$130]  |PARTITIONED|
-                                                                                          {
-                                                                                            -- AGGREGATE  |LOCAL|
-                                                                                              -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                                                                          }
-                                                                                    -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$130(ASC)] HASH:[$$130]  |PARTITIONED|
-                                                                                      -- SORT_GROUP_BY[$$17]  |PARTITIONED|
-                                                                                              {
-                                                                                                -- AGGREGATE  |LOCAL|
-                                                                                                  -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                                                                              }
-                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                -- HYBRID_HASH_JOIN [$$15][$$18]  |PARTITIONED|
+                                                                                  -- HASH_PARTITION_EXCHANGE [$$15]  |PARTITIONED|
+                                                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                                                      -- UNNEST  |PARTITIONED|
+                                                                                        -- ASSIGN  |PARTITIONED|
                                                                                           -- STREAM_PROJECT  |PARTITIONED|
-                                                                                            -- UNNEST  |PARTITIONED|
-                                                                                              -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                -- ASSIGN  |PARTITIONED|
+                                                                                            -- ASSIGN  |PARTITIONED|
+                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                  -- HASH_PARTITION_EXCHANGE [$$18]  |PARTITIONED|
+                                                                                    -- ASSIGN  |PARTITIONED|
+                                                                                      -- RUNNING_AGGREGATE  |PARTITIONED|
+                                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                                          -- SORT_MERGE_EXCHANGE [$$102(ASC), $$18(ASC) ]  |PARTITIONED|
+                                                                                            -- STABLE_SORT [$$102(ASC), $$18(ASC)]  |PARTITIONED|
+                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                -- PRE_CLUSTERED_GROUP_BY[$$130]  |PARTITIONED|
+                                                                                                        {
+                                                                                                          -- AGGREGATE  |LOCAL|
+                                                                                                            -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                                                                        }
+                                                                                                  -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$130(ASC)] HASH:[$$130]  |PARTITIONED|
+                                                                                                    -- SORT_GROUP_BY[$$17]  |PARTITIONED|
+                                                                                                            {
+                                                                                                              -- AGGREGATE  |LOCAL|
+                                                                                                                -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                                                                            }
+                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                          -- UNNEST  |PARTITIONED|
+                                                                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                              -- ASSIGN  |PARTITIONED|
+                                                                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                    -- SPLIT  |PARTITIONED|
+                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                        -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- SPLIT  |PARTITIONED|
+                                                        -- HASH_PARTITION_EXCHANGE [$$23]  |PARTITIONED|
+                                                          -- UNNEST  |PARTITIONED|
+                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                              -- PRE_CLUSTERED_GROUP_BY[$$97]  |PARTITIONED|
+                                                                      {
+                                                                        -- AGGREGATE  |LOCAL|
+                                                                          -- STREAM_SELECT  |LOCAL|
+                                                                            -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                                      }
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  -- STABLE_SORT [$$97(ASC), $$21(ASC)]  |PARTITIONED|
+                                                                    -- HASH_PARTITION_EXCHANGE [$$97]  |PARTITIONED|
+                                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                          -- HYBRID_HASH_JOIN [$$15][$$18]  |PARTITIONED|
+                                                                            -- HASH_PARTITION_EXCHANGE [$$15]  |PARTITIONED|
+                                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                                -- UNNEST  |PARTITIONED|
+                                                                                  -- ASSIGN  |PARTITIONED|
+                                                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                                                      -- ASSIGN  |PARTITIONED|
+                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                          -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                            -- HASH_PARTITION_EXCHANGE [$$18]  |PARTITIONED|
+                                                                              -- ASSIGN  |PARTITIONED|
+                                                                                -- RUNNING_AGGREGATE  |PARTITIONED|
+                                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                                    -- SORT_MERGE_EXCHANGE [$$102(ASC), $$18(ASC) ]  |PARTITIONED|
+                                                                                      -- STABLE_SORT [$$102(ASC), $$18(ASC)]  |PARTITIONED|
+                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                          -- PRE_CLUSTERED_GROUP_BY[$$130]  |PARTITIONED|
+                                                                                                  {
+                                                                                                    -- AGGREGATE  |LOCAL|
+                                                                                                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                                                                  }
+                                                                                            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$130(ASC)] HASH:[$$130]  |PARTITIONED|
+                                                                                              -- SORT_GROUP_BY[$$17]  |PARTITIONED|
+                                                                                                      {
+                                                                                                        -- AGGREGATE  |LOCAL|
+                                                                                                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                                                                      }
+                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                   -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                      -- SPLIT  |PARTITIONED|
-                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                          -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                                                    -- UNNEST  |PARTITIONED|
+                                                                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                        -- ASSIGN  |PARTITIONED|
+                                                                                                          -- STREAM_PROJECT  |PARTITIONED|
                                                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                              -- SPLIT  |PARTITIONED|
+                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                  -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ASSIGN  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- SPLIT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- DATASOURCE_SCAN  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1047.plan b/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1047.plan
new file mode 100644
index 0000000..59a50fb
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1047.plan
@@ -0,0 +1,19 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- AGGREGATE  |UNPARTITIONED|
+      -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+        -- UNION_ALL  |PARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+            -- STREAM_PROJECT  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- BTREE_SEARCH  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- ASSIGN  |PARTITIONED|
+                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+            -- STREAM_SELECT  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- DATASOURCE_SCAN  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1205-2.plan b/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1205-2.plan
new file mode 100644
index 0000000..1d21071
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1205-2.plan
@@ -0,0 +1,19 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_PROJECT  |UNPARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+        -- STABLE_SORT [$$19(ASC), $$20(ASC)]  |UNPARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+            -- UNION_ALL  |UNPARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+                -- ASSIGN  |UNPARTITIONED|
+                  -- STREAM_PROJECT  |UNPARTITIONED|
+                    -- UNNEST  |UNPARTITIONED|
+                      -- ASSIGN  |UNPARTITIONED|
+                        -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+                -- ASSIGN  |UNPARTITIONED|
+                  -- STREAM_PROJECT  |UNPARTITIONED|
+                    -- UNNEST  |UNPARTITIONED|
+                      -- ASSIGN  |UNPARTITIONED|
+                        -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1205-3.plan b/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1205-3.plan
new file mode 100644
index 0000000..a1f37e8
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1205-3.plan
@@ -0,0 +1,15 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- AGGREGATE  |UNPARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+        -- UNION_ALL  |UNPARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+            -- STREAM_PROJECT  |UNPARTITIONED|
+              -- UNNEST  |UNPARTITIONED|
+                -- ASSIGN  |UNPARTITIONED|
+                  -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+            -- STREAM_PROJECT  |UNPARTITIONED|
+              -- UNNEST  |UNPARTITIONED|
+                -- ASSIGN  |UNPARTITIONED|
+                  -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1205-4.plan b/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1205-4.plan
new file mode 100644
index 0000000..48950e8
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1205-4.plan
@@ -0,0 +1,27 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_PROJECT  |UNPARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+        -- STABLE_SORT [$$28(ASC), $$29(ASC)]  |UNPARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+            -- UNION_ALL  |UNPARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+                -- UNION_ALL  |UNPARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+                    -- ASSIGN  |UNPARTITIONED|
+                      -- STREAM_PROJECT  |UNPARTITIONED|
+                        -- UNNEST  |UNPARTITIONED|
+                          -- ASSIGN  |UNPARTITIONED|
+                            -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+                    -- ASSIGN  |UNPARTITIONED|
+                      -- STREAM_PROJECT  |UNPARTITIONED|
+                        -- UNNEST  |UNPARTITIONED|
+                          -- ASSIGN  |UNPARTITIONED|
+                            -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+                -- ASSIGN  |UNPARTITIONED|
+                  -- STREAM_PROJECT  |UNPARTITIONED|
+                    -- UNNEST  |UNPARTITIONED|
+                      -- ASSIGN  |UNPARTITIONED|
+                        -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1205.plan b/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1205.plan
new file mode 100644
index 0000000..115b5b6
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1205.plan
@@ -0,0 +1,19 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_PROJECT  |UNPARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+        -- STABLE_SORT [$$20(ASC), $$21(ASC)]  |UNPARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+            -- UNION_ALL  |UNPARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+                -- ASSIGN  |UNPARTITIONED|
+                  -- STREAM_PROJECT  |UNPARTITIONED|
+                    -- UNNEST  |UNPARTITIONED|
+                      -- ASSIGN  |UNPARTITIONED|
+                        -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+                -- ASSIGN  |UNPARTITIONED|
+                  -- STREAM_PROJECT  |UNPARTITIONED|
+                    -- UNNEST  |UNPARTITIONED|
+                      -- ASSIGN  |UNPARTITIONED|
+                        -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/union/union_dataset.plan b/asterix-app/src/test/resources/optimizerts/results/union/union_dataset.plan
new file mode 100644
index 0000000..fdc7d4c
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/union/union_dataset.plan
@@ -0,0 +1,21 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- SORT_MERGE_EXCHANGE [$$11(ASC) ]  |PARTITIONED|
+        -- STABLE_SORT [$$11(ASC)]  |PARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+            -- UNION_ALL  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- ASSIGN  |PARTITIONED|
+                  -- STREAM_PROJECT  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- DATASOURCE_SCAN  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- ASSIGN  |PARTITIONED|
+                  -- STREAM_PROJECT  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- DATASOURCE_SCAN  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/union/union_dataset2.plan b/asterix-app/src/test/resources/optimizerts/results/union/union_dataset2.plan
new file mode 100644
index 0000000..c1b97aa
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/union/union_dataset2.plan
@@ -0,0 +1,17 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- AGGREGATE  |UNPARTITIONED|
+      -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+        -- UNION_ALL  |PARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+            -- STREAM_PROJECT  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- DATASOURCE_SCAN  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+            -- STREAM_PROJECT  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- DATASOURCE_SCAN  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/union/union_dataset3.plan b/asterix-app/src/test/resources/optimizerts/results/union/union_dataset3.plan
new file mode 100644
index 0000000..9d51d1c
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/union/union_dataset3.plan
@@ -0,0 +1,21 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- SORT_MERGE_EXCHANGE [$$10(ASC) ]  |PARTITIONED|
+        -- STABLE_SORT [$$10(ASC)]  |PARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+            -- UNION_ALL  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- ASSIGN  |PARTITIONED|
+                  -- STREAM_PROJECT  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- DATASOURCE_SCAN  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- ASSIGN  |PARTITIONED|
+                  -- STREAM_PROJECT  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- DATASOURCE_SCAN  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/union/union_query.plan b/asterix-app/src/test/resources/optimizerts/results/union/union_query.plan
new file mode 100644
index 0000000..67de4c9
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/union/union_query.plan
@@ -0,0 +1,19 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$16(ASC) ]  |PARTITIONED|
+    -- PRE_SORTED_DISTINCT_BY  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STABLE_SORT [$$16(ASC)]  |PARTITIONED|
+          -- HASH_PARTITION_EXCHANGE [$$16]  |PARTITIONED|
+            -- UNION_ALL  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- STREAM_PROJECT  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- DATASOURCE_SCAN  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- STREAM_PROJECT  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- DATASOURCE_SCAN  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1047/query-ASTERIXDB-1047.1.ddl.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1047/query-ASTERIXDB-1047.1.ddl.aql
new file mode 100644
index 0000000..5586ad23c
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1047/query-ASTERIXDB-1047.1.ddl.aql
@@ -0,0 +1,33 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type DBLPType as open {
+  id: int64,
+  dblpid: string,
+  title: string,
+  authors: string,
+  misc: string
+}
+
+create dataset DBLP(DBLPType) primary key id;
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1047/query-ASTERIXDB-1047.2.update.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1047/query-ASTERIXDB-1047.2.update.aql
new file mode 100644
index 0000000..bbee2a1
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1047/query-ASTERIXDB-1047.2.update.aql
@@ -0,0 +1,24 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use dataverse test;
+
+load dataset DBLP
+using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="asterix_nc1://data/pub-small/dblp-small-id.txt"),("format"="delimited-text"),("delimiter"=":")) pre-sorted;
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1047/query-ASTERIXDB-1047.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1047/query-ASTERIXDB-1047.3.query.aql
new file mode 100644
index 0000000..f30a7c4
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1047/query-ASTERIXDB-1047.3.query.aql
@@ -0,0 +1,31 @@
+/*
+ * 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.
+ */
+/**
+ * This query is to verify the fix of ASTERIXDB-1047.
+ */
+
+use dataverse test;
+
+for $i in (
+  (for $d in dataset DBLP where $d.id = 1 return $d)
+  union
+  (for $d in dataset DBLP where $d.authors = "Alfred V. Aho John E. Hopcroft Jeffrey D. Ullman" return $d)
+)
+order by $i.id
+return $i;
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-2/query-ASTERIXDB-1205.1.ddl.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-2/query-ASTERIXDB-1205.1.ddl.aql
new file mode 100644
index 0000000..bd244d0
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-2/query-ASTERIXDB-1205.1.ddl.aql
@@ -0,0 +1,19 @@
+/*
+ * 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.
+ */
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-2/query-ASTERIXDB-1205.2.update.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-2/query-ASTERIXDB-1205.2.update.aql
new file mode 100644
index 0000000..042f3ce
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-2/query-ASTERIXDB-1205.2.update.aql
@@ -0,0 +1,18 @@
+/*
+ * 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.
+ */
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-2/query-ASTERIXDB-1205.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-2/query-ASTERIXDB-1205.3.query.aql
new file mode 100644
index 0000000..82d1430
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-2/query-ASTERIXDB-1205.3.query.aql
@@ -0,0 +1,28 @@
+/*
+ * 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.
+ */
+/**
+ * This query is to verify the fix of ASTERIXDB-1205.
+ */
+
+let $aa := [{"fa":1, "fb":1}, {"fa":2, "fb":1}]
+let $bb := [{"fa":1, "fb":0}, {"fa":1, "fb":1}, {"fa":3, "fb":1}]
+
+for $tt in ($aa union $bb)
+order by $tt.fa, $tt.fb
+return $tt;
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-3/query-ASTERIXDB-1205.1.ddl.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-3/query-ASTERIXDB-1205.1.ddl.aql
new file mode 100644
index 0000000..bd244d0
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-3/query-ASTERIXDB-1205.1.ddl.aql
@@ -0,0 +1,19 @@
+/*
+ * 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.
+ */
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-3/query-ASTERIXDB-1205.2.update.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-3/query-ASTERIXDB-1205.2.update.aql
new file mode 100644
index 0000000..042f3ce
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-3/query-ASTERIXDB-1205.2.update.aql
@@ -0,0 +1,18 @@
+/*
+ * 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.
+ */
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-3/query-ASTERIXDB-1205.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-3/query-ASTERIXDB-1205.3.query.aql
new file mode 100644
index 0000000..bee2076
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-3/query-ASTERIXDB-1205.3.query.aql
@@ -0,0 +1,30 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/**
+ * This query is to verify the fix of ASTERIXDB-1205.
+ */
+
+let $aa := [{"fa":1, "fb":1}, {"fa":2, "fb":1}]
+let $bb := [{"fa":1, "fb":0}, {"fa":1, "fb":1}, {"fa":3, "fb":1}]
+let $dd := [{"fa":4, "fb":5}, {"fa":6, "fb":7}]
+let $ccc := $aa union $bb union $dd
+
+for $tt in $ccc
+order by $tt.fa, $tt.fb
+return $tt;
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205/query-ASTERIXDB-1205.1.ddl.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205/query-ASTERIXDB-1205.1.ddl.aql
new file mode 100644
index 0000000..bd244d0
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205/query-ASTERIXDB-1205.1.ddl.aql
@@ -0,0 +1,19 @@
+/*
+ * 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.
+ */
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205/query-ASTERIXDB-1205.2.update.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205/query-ASTERIXDB-1205.2.update.aql
new file mode 100644
index 0000000..042f3ce
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205/query-ASTERIXDB-1205.2.update.aql
@@ -0,0 +1,18 @@
+/*
+ * 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.
+ */
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205/query-ASTERIXDB-1205.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205/query-ASTERIXDB-1205.3.query.aql
new file mode 100644
index 0000000..763aba1
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205/query-ASTERIXDB-1205.3.query.aql
@@ -0,0 +1,29 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/**
+ * This query is to verify the fix of ASTERIXDB-1205.
+ */
+
+let $aa := [{"fa":1, "fb":1}, {"fa":2, "fb":1}]
+let $bb := [{"fa":1, "fb":0}, {"fa":1, "fb":1}, {"fa":3, "fb":1}]
+let $ccc := $aa union $bb
+
+for $tt in $ccc
+order by $tt.fa, $tt.fb
+return $tt;
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-300/query-ASTERIXDB-300.1.ddl.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-300/query-ASTERIXDB-300.1.ddl.aql
new file mode 100644
index 0000000..bd244d0
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-300/query-ASTERIXDB-300.1.ddl.aql
@@ -0,0 +1,19 @@
+/*
+ * 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.
+ */
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-300/query-ASTERIXDB-300.2.update.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-300/query-ASTERIXDB-300.2.update.aql
new file mode 100644
index 0000000..042f3ce
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-300/query-ASTERIXDB-300.2.update.aql
@@ -0,0 +1,18 @@
+/*
+ * 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.
+ */
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-300/query-ASTERIXDB-300.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-300/query-ASTERIXDB-300.3.query.aql
new file mode 100644
index 0000000..95d9968
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-300/query-ASTERIXDB-300.3.query.aql
@@ -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.
+ */
+/**
+ * This query is to verify the fix of ASTERIXDB-300.
+ */
+
+let $a := [
+{"id":1234,"name":"John Doe","age":56,"salary":50000,"dept":"HR"}
+]
+let $b := [
+{"id":3424,"name":"Roger Sanders","age":46,"salary":60000,"dept":"Publishing"}
+]
+
+let $c := $a union $b
+return
+  for $i in $c
+  order by $i.id
+  return $i
+;
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/union/union.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/union/union/union.3.query.aql
index 4b8b4ab..d112458 100644
--- a/asterix-app/src/test/resources/runtimets/queries/union/union/union.3.query.aql
+++ b/asterix-app/src/test/resources/runtimets/queries/union/union/union.3.query.aql
@@ -21,4 +21,4 @@
 let $t1 := for $t in dataset FacebookUsers return $t.id
 let $t2 := for $s in dataset FacebookMessages return $s.message-id
 let $c := $t1 union $t2
-for $res in $c distinct by $res return $res
+for $res in $c distinct by $res order by $res return $res
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/union2/union.1.ddl.aql b/asterix-app/src/test/resources/runtimets/queries/union/union2/union.1.ddl.aql
new file mode 100644
index 0000000..1936ef8
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/union2/union.1.ddl.aql
@@ -0,0 +1,29 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+drop dataverse TinySocial if exists;
+create dataverse TinySocial;
+use dataverse TinySocial;
+
+create type FacebookUserType as open {
+        id: int
+}
+
+create dataset FacebookUsers(FacebookUserType)
+primary key id;
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/union2/union.2.update.aql b/asterix-app/src/test/resources/runtimets/queries/union/union2/union.2.update.aql
new file mode 100644
index 0000000..a6b70a3
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/union2/union.2.update.aql
@@ -0,0 +1,22 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+use dataverse TinySocial;
+
+load dataset FacebookUsers using localfs
+(("path"="asterix_nc1://data/tinysocial/fbu.adm"),("format"="adm"));
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/union2/union.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/union/union2/union.3.query.aql
new file mode 100644
index 0000000..4b52c77
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/union2/union.3.query.aql
@@ -0,0 +1,22 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+use dataverse TinySocial;
+
+let $c := dataset("FacebookUsers") union dataset("FacebookUsers")
+for $res in $c order by $res.id return $res
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_04/join_q_04.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_04/join_q_04.1.ddl.sqlpp
index a0795af..7dffaf6 100644
--- a/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_04/join_q_04.1.ddl.sqlpp
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_04/join_q_04.1.ddl.sqlpp
@@ -18,6 +18,7 @@
  */
 /*
  * Description  : This test case is to verify the fix for issue51
+ */
 
 drop  database test if exists;
 create  database test;
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_05/join_q_05.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_05/join_q_05.1.ddl.sqlpp
new file mode 100644
index 0000000..0a583a4
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_05/join_q_05.1.ddl.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  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.AddressType as
+{
+  number : int64,
+  street : string,
+  city : string
+}
+
+create type test.CustomerType as
+ closed {
+  cid : int64,
+  name : string,
+  cashBack : int64,
+  age : int64?,
+  address : AddressType?,
+  lastorder : {
+      oid : int64,
+      total : float
+  }
+
+}
+
+create type test.OrderType as
+{
+  oid : int64,
+  cid : int64,
+  orderstatus : string,
+  orderpriority : string,
+  clerk : string,
+  total : float,
+  items : [int64]
+}
+
+create external table Customers(CustomerType) using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter"(("path"="asterix_nc1://data/nontagged/customerData.json"),("format"="adm"));
+
+create external table Orders(OrderType) using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter"(("path"="asterix_nc1://data/nontagged/orderData.json"),("format"="adm"));
+
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_05/join_q_05.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_05/join_q_05.2.update.sqlpp
new file mode 100644
index 0000000..6c98c1e
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_05/join_q_05.2.update.sqlpp
@@ -0,0 +1,20 @@
+/*
+ * 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.
+ */
+
+
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_05/join_q_05.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_05/join_q_05.3.query.sqlpp
new file mode 100644
index 0000000..c7533ae
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_05/join_q_05.3.query.sqlpp
@@ -0,0 +1,31 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+
+WITH customer AS (SELECT ELEMENT c FROM Customers c),
+     "orders" AS (SELECT ELEMENT o FROM Orders o)
+SELECT c.name AS cust_name,
+       c.age AS cust_age,
+       o.total AS order_total,
+       [o.oid,o.cid] AS orderList
+FROM customer c JOIN orders o ON c.cid = o.cid
+ORDER BY c.name,o.total
+;
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_06/join_q_06.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_06/join_q_06.1.ddl.sqlpp
new file mode 100644
index 0000000..821b565
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_06/join_q_06.1.ddl.sqlpp
@@ -0,0 +1,47 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.AddressType as
+{
+  number : int64,
+  street : string,
+  city : string
+}
+
+create type test.CustomerType as
+ closed {
+  cid : int64,
+  name : string,
+  cashBack : int64,
+  age : int64?,
+  address : AddressType?,
+  lastorder : {
+      oid : int64,
+      total : float
+  }
+
+}
+
+create external table Customers(CustomerType) using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter"(("path"="asterix_nc1://data/nontagged/customerData.json"),("format"="adm"));
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_06/join_q_06.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_06/join_q_06.2.update.sqlpp
new file mode 100644
index 0000000..6c98c1e
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_06/join_q_06.2.update.sqlpp
@@ -0,0 +1,20 @@
+/*
+ * 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.
+ */
+
+
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_06/join_q_06.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_06/join_q_06.3.query.sqlpp
new file mode 100644
index 0000000..c9e9876
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_06/join_q_06.3.query.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+
+WITH customer AS (SELECT ELEMENT c FROM Customers c)
+
+SELECT c.name AS cust_name,
+       c.cashBack AS cust_cashBack
+FROM customer c JOIN [min((SELECT ELEMENT c.cashBack FROM customer c))] as min_cashBack
+     ON c.cashBack = min_cashBack
+ORDER BY c.cid, c.name
+;
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_07/join_q_07.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_07/join_q_07.1.ddl.sqlpp
new file mode 100644
index 0000000..821b565
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_07/join_q_07.1.ddl.sqlpp
@@ -0,0 +1,47 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.AddressType as
+{
+  number : int64,
+  street : string,
+  city : string
+}
+
+create type test.CustomerType as
+ closed {
+  cid : int64,
+  name : string,
+  cashBack : int64,
+  age : int64?,
+  address : AddressType?,
+  lastorder : {
+      oid : int64,
+      total : float
+  }
+
+}
+
+create external table Customers(CustomerType) using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter"(("path"="asterix_nc1://data/nontagged/customerData.json"),("format"="adm"));
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_07/join_q_07.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_07/join_q_07.2.update.sqlpp
new file mode 100644
index 0000000..6c98c1e
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_07/join_q_07.2.update.sqlpp
@@ -0,0 +1,20 @@
+/*
+ * 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.
+ */
+
+
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_07/join_q_07.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_07/join_q_07.3.query.sqlpp
new file mode 100644
index 0000000..05c71c2
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_07/join_q_07.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.
+ */
+
+/*
+ * This query is expected to fail because variable c
+ * is undefined for subquery "(SELECT ELEMENT c.cashBack FROM c c)".
+ */
+
+USE test;
+
+
+WITH customer AS (SELECT ELEMENT c FROM Customers c)
+
+SELECT c.name AS cust_name,
+       c.cashBack AS cust_cashBack
+FROM customer c JOIN [min((SELECT ELEMENT c.cashBack FROM c c))] as min_cashBack
+     ON c.cashBack = min_cashBack
+ORDER BY c.cid, c.name
+;
diff --git a/asterix-app/src/test/resources/runtimets/results/custord/join_q_06/join_q_06.1.adm b/asterix-app/src/test/resources/runtimets/results/custord/join_q_06/join_q_06.1.adm
new file mode 100644
index 0000000..bcbe1fb
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/custord/join_q_06/join_q_06.1.adm
@@ -0,0 +1 @@
+{ "cust_name": "Jodi Rotruck", "cust_cashBack": 100 }
diff --git a/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-1047/query-ASTERIXDB-1047.1.adm b/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-1047/query-ASTERIXDB-1047.1.adm
new file mode 100644
index 0000000..2a14594
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-1047/query-ASTERIXDB-1047.1.adm
@@ -0,0 +1,3 @@
+{ "id": 1, "dblpid": "books/acm/kim95/AnnevelinkACFHK95", "title": "Object SQL - A Language for the Design and Implementation of Object Databases.", "authors": "Jurgen Annevelink Rafiul Ahad Amelia Carlson Daniel H. Fishman Michael L. Heytens William Kent", "misc": "2002-01-03 42-68 1995 Modern Database Systems db/books/collections/kim95.html#AnnevelinkACFHK95" }
+{ "id": 73, "dblpid": "books/aw/AhoHU74", "title": "The Design and Analysis of Computer Algorithms.", "authors": "Alfred V. Aho John E. Hopcroft Jeffrey D. Ullman", "misc": "2002-01-03 Addison-Wesley 1974 0-201-00029-6" }
+{ "id": 75, "dblpid": "books/aw/AhoHU83", "title": "Data Structures and Algorithms.", "authors": "Alfred V. Aho John E. Hopcroft Jeffrey D. Ullman", "misc": "2002-01-03 Addison-Wesley 1983 0-201-00023-7" }
diff --git a/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-1205-2/query-ASTERIXDB-1205-2.1.adm b/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-1205-2/query-ASTERIXDB-1205-2.1.adm
new file mode 100644
index 0000000..2ddaa6e
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-1205-2/query-ASTERIXDB-1205-2.1.adm
@@ -0,0 +1,5 @@
+{ "fa": 1, "fb": 0 }
+{ "fa": 1, "fb": 1 }
+{ "fa": 1, "fb": 1 }
+{ "fa": 2, "fb": 1 }
+{ "fa": 3, "fb": 1 }
diff --git a/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-1205-3/query-ASTERIXDB-1205-3.1.adm b/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-1205-3/query-ASTERIXDB-1205-3.1.adm
new file mode 100644
index 0000000..12a9fe6
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-1205-3/query-ASTERIXDB-1205-3.1.adm
@@ -0,0 +1,7 @@
+{ "fa": 1, "fb": 0 }
+{ "fa": 1, "fb": 1 }
+{ "fa": 1, "fb": 1 }
+{ "fa": 2, "fb": 1 }
+{ "fa": 3, "fb": 1 }
+{ "fa": 4, "fb": 5 }
+{ "fa": 6, "fb": 7 }
\ No newline at end of file
diff --git a/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-1205/query-ASTERIXDB-1205.1.adm b/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-1205/query-ASTERIXDB-1205.1.adm
new file mode 100644
index 0000000..2ddaa6e
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-1205/query-ASTERIXDB-1205.1.adm
@@ -0,0 +1,5 @@
+{ "fa": 1, "fb": 0 }
+{ "fa": 1, "fb": 1 }
+{ "fa": 1, "fb": 1 }
+{ "fa": 2, "fb": 1 }
+{ "fa": 3, "fb": 1 }
diff --git a/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-300/query-ASTERIXDB-300.1.adm b/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-300/query-ASTERIXDB-300.1.adm
new file mode 100644
index 0000000..d580e02
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-300/query-ASTERIXDB-300.1.adm
@@ -0,0 +1 @@
+[ { "id": 1234, "name": "John Doe", "age": 56, "salary": 50000, "dept": "HR" }, { "id": 3424, "name": "Roger Sanders", "age": 46, "salary": 60000, "dept": "Publishing" } ]
diff --git a/asterix-app/src/test/resources/runtimets/results/union/union/union.1.adm b/asterix-app/src/test/resources/runtimets/results/union/union/union.1.adm
index 69a9eb0..97b3d1a 100644
--- a/asterix-app/src/test/resources/runtimets/results/union/union/union.1.adm
+++ b/asterix-app/src/test/resources/runtimets/results/union/union/union.1.adm
@@ -1,15 +1,15 @@
-6
-11
-12
-14
 1
 2
+3
 4
-13
-15
+5
+6
+7
 8
 9
 10
-3
-5
-7
+11
+12
+13
+14
+15
diff --git a/asterix-app/src/test/resources/runtimets/results/union/union2/union2.1.adm b/asterix-app/src/test/resources/runtimets/results/union/union2/union2.1.adm
new file mode 100644
index 0000000..70b4777
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/union/union2/union2.1.adm
@@ -0,0 +1,20 @@
+{ "id": 1, "alias": "Margarita", "name": "MargaritaStoddard", "user-since": datetime("2012-08-20T10:10:00.000Z"), "friend-ids": {{ 2, 3, 6, 10 }}, "employment": [ { "organization-name": "Codetechno", "start-date": date("2006-08-06") } ] }
+{ "id": 1, "alias": "Margarita", "name": "MargaritaStoddard", "user-since": datetime("2012-08-20T10:10:00.000Z"), "friend-ids": {{ 2, 3, 6, 10 }}, "employment": [ { "organization-name": "Codetechno", "start-date": date("2006-08-06") } ] }
+{ "id": 2, "alias": "Isbel", "name": "IsbelDull", "user-since": datetime("2011-01-22T10:10:00.000Z"), "friend-ids": {{ 1, 4 }}, "employment": [ { "organization-name": "Hexviafind", "start-date": date("2010-04-27") } ] }
+{ "id": 2, "alias": "Isbel", "name": "IsbelDull", "user-since": datetime("2011-01-22T10:10:00.000Z"), "friend-ids": {{ 1, 4 }}, "employment": [ { "organization-name": "Hexviafind", "start-date": date("2010-04-27") } ] }
+{ "id": 3, "alias": "Emory", "name": "EmoryUnk", "user-since": datetime("2012-07-10T10:10:00.000Z"), "friend-ids": {{ 1, 5, 8, 9 }}, "employment": [ { "organization-name": "geomedia", "start-date": date("2010-06-17"), "end-date": date("2010-01-26") } ] }
+{ "id": 3, "alias": "Emory", "name": "EmoryUnk", "user-since": datetime("2012-07-10T10:10:00.000Z"), "friend-ids": {{ 1, 5, 8, 9 }}, "employment": [ { "organization-name": "geomedia", "start-date": date("2010-06-17"), "end-date": date("2010-01-26") } ] }
+{ "id": 4, "alias": "Nicholas", "name": "NicholasStroh", "user-since": datetime("2010-12-27T10:10:00.000Z"), "friend-ids": {{ 2 }}, "employment": [ { "organization-name": "Zamcorporation", "start-date": date("2010-06-08") } ] }
+{ "id": 4, "alias": "Nicholas", "name": "NicholasStroh", "user-since": datetime("2010-12-27T10:10:00.000Z"), "friend-ids": {{ 2 }}, "employment": [ { "organization-name": "Zamcorporation", "start-date": date("2010-06-08") } ] }
+{ "id": 5, "alias": "Von", "name": "VonKemble", "user-since": datetime("2010-01-05T10:10:00.000Z"), "friend-ids": {{ 3, 6, 10 }}, "employment": [ { "organization-name": "Kongreen", "start-date": date("2010-11-27") } ] }
+{ "id": 5, "alias": "Von", "name": "VonKemble", "user-since": datetime("2010-01-05T10:10:00.000Z"), "friend-ids": {{ 3, 6, 10 }}, "employment": [ { "organization-name": "Kongreen", "start-date": date("2010-11-27") } ] }
+{ "id": 6, "alias": "Willis", "name": "WillisWynne", "user-since": datetime("2005-01-17T10:10:00.000Z"), "friend-ids": {{ 1, 3, 7 }}, "employment": [ { "organization-name": "jaydax", "start-date": date("2009-05-15") } ] }
+{ "id": 6, "alias": "Willis", "name": "WillisWynne", "user-since": datetime("2005-01-17T10:10:00.000Z"), "friend-ids": {{ 1, 3, 7 }}, "employment": [ { "organization-name": "jaydax", "start-date": date("2009-05-15") } ] }
+{ "id": 7, "alias": "Suzanna", "name": "SuzannaTillson", "user-since": datetime("2012-08-07T10:10:00.000Z"), "friend-ids": {{ 6 }}, "employment": [ { "organization-name": "Labzatron", "start-date": date("2011-04-19") } ] }
+{ "id": 7, "alias": "Suzanna", "name": "SuzannaTillson", "user-since": datetime("2012-08-07T10:10:00.000Z"), "friend-ids": {{ 6 }}, "employment": [ { "organization-name": "Labzatron", "start-date": date("2011-04-19") } ] }
+{ "id": 8, "alias": "Nila", "name": "NilaMilliron", "user-since": datetime("2008-01-01T10:10:00.000Z"), "friend-ids": {{ 3 }}, "employment": [ { "organization-name": "Plexlane", "start-date": date("2010-02-28") } ] }
+{ "id": 8, "alias": "Nila", "name": "NilaMilliron", "user-since": datetime("2008-01-01T10:10:00.000Z"), "friend-ids": {{ 3 }}, "employment": [ { "organization-name": "Plexlane", "start-date": date("2010-02-28") } ] }
+{ "id": 9, "alias": "Woodrow", "name": "WoodrowNehling", "user-since": datetime("2005-09-20T10:10:00.000Z"), "friend-ids": {{ 3, 10 }}, "employment": [ { "organization-name": "Zuncan", "start-date": date("2003-04-22"), "end-date": date("2009-12-13") } ] }
+{ "id": 9, "alias": "Woodrow", "name": "WoodrowNehling", "user-since": datetime("2005-09-20T10:10:00.000Z"), "friend-ids": {{ 3, 10 }}, "employment": [ { "organization-name": "Zuncan", "start-date": date("2003-04-22"), "end-date": date("2009-12-13") } ] }
+{ "id": 10, "alias": "Bram", "name": "BramHatch", "user-since": datetime("2010-10-16T10:10:00.000Z"), "friend-ids": {{ 1, 5, 9 }}, "employment": [ { "organization-name": "physcane", "start-date": date("2007-06-05"), "end-date": date("2011-11-05") } ] }
+{ "id": 10, "alias": "Bram", "name": "BramHatch", "user-since": datetime("2010-10-16T10:10:00.000Z"), "friend-ids": {{ 1, 5, 9 }}, "employment": [ { "organization-name": "physcane", "start-date": date("2007-06-05"), "end-date": date("2011-11-05") } ] }
diff --git a/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/custord/join_q_04/join_q_04.1.ast b/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/custord/join_q_04/join_q_04.1.ast
index e69de29..7509a02 100644
--- a/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/custord/join_q_04/join_q_04.1.ast
+++ b/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/custord/join_q_04/join_q_04.1.ast
@@ -0,0 +1,36 @@
+DataverseUse test
+TypeDecl AddressType [
+  open RecordType {
+    number : int64,
+    street : string,
+    city : string
+  }
+]
+TypeDecl CustomerType [
+  closed RecordType {
+    cid : int64,
+    name : string,
+    cashBack : int64,
+    age : int64?,
+    address : AddressType?,
+    lastorder :       open RecordType {
+        oid : int64,
+        total : float
+      }
+
+  }
+]
+TypeDecl OrderType [
+  open RecordType {
+    oid : int64,
+    cid : int64,
+    orderstatus : string,
+    orderpriority : string,
+    clerk : string,
+    total : float,
+    items : OrderedList [int64]
+
+  }
+]
+DatasetDecl Customers(CustomerType)is an external dataset
+DatasetDecl Orders(OrderType)is an external dataset
diff --git a/asterix-app/src/test/resources/runtimets/testsuite.xml b/asterix-app/src/test/resources/runtimets/testsuite.xml
index a506bd3..a3a1fba 100644
--- a/asterix-app/src/test/resources/runtimets/testsuite.xml
+++ b/asterix-app/src/test/resources/runtimets/testsuite.xml
@@ -177,6 +177,36 @@
                 <output-dir compare="Text">union</output-dir>
             </compilation-unit>
         </test-case>
+        <test-case FilePath="union">
+            <compilation-unit name="union2">
+                <output-dir compare="Text">union2</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="union">
+            <compilation-unit name="query-ASTERIXDB-300">
+                <output-dir compare="Text">query-ASTERIXDB-300</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="union">
+            <compilation-unit name="query-ASTERIXDB-1205">
+                <output-dir compare="Text">query-ASTERIXDB-1205</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="union">
+            <compilation-unit name="query-ASTERIXDB-1205-2">
+                <output-dir compare="Text">query-ASTERIXDB-1205-2</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="union">
+            <compilation-unit name="query-ASTERIXDB-1205-3">
+                <output-dir compare="Text">query-ASTERIXDB-1205-3</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="union">
+            <compilation-unit name="query-ASTERIXDB-1047">
+                <output-dir compare="Text">query-ASTERIXDB-1047</output-dir>
+            </compilation-unit>
+        </test-case>
     </test-group>
     <test-case FilePath="flwor">
         <compilation-unit name="let33">
diff --git a/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml b/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
index 2965883..d289400 100644
--- a/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -1298,6 +1298,22 @@
             </compilation-unit>
         </test-case>
         <test-case FilePath="custord">
+            <compilation-unit name="join_q_05">
+                <output-dir compare="Text">join_q_01</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="custord">
+            <compilation-unit name="join_q_06">
+                <output-dir compare="Text">join_q_06</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="custord">
+            <compilation-unit name="join_q_07">
+                <output-dir compare="Text">join_q_06</output-dir>
+                 <expected-error>org.apache.hyracks.algebricks.common.exceptions.AlgebricksException: Could not resolve type for function-call: asterix:scan-collection, Args:[%0->$$8],please check whether the used variables has been defined!</expected-error>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="custord">
             <compilation-unit name="load-test">
                 <output-dir compare="Text">load-test</output-dir>
             </compilation-unit>