diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_01/union_all_01.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_01/union_all_01.1.ddl.sqlpp
new file mode 100644
index 0000000..83754e1
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_01/union_all_01.1.ddl.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+create dataset ds1(id int) open type primary key id;
+
+create dataset ds2(id int) open type primary key id;
+
+create dataset ds3(id int) open type primary key id;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_01/union_all_01.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_01/union_all_01.2.update.sqlpp
new file mode 100644
index 0000000..d23a7f3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_01/union_all_01.2.update.sqlpp
@@ -0,0 +1,36 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use test;
+
+insert into ds1 ({"id": 1, "a": 1, "b": 1 });
+insert into ds1 ({"id": 2, "a": 1, "b": 2 });
+insert into ds1 ({"id": 3, "a": 2, "b": 3 });
+insert into ds1 ({"id": 4, "a": 2, "b": 4 });
+insert into ds1 ({"id": 5, "a": 3, "b": 5 });
+insert into ds1 ({"id": 6, "a": 3, "b": 6 });
+insert into ds1 ({"id": 7, "a": 4, "b": 7 });
+
+insert into ds2 ({"id": 100, "c": 2, "d": 100 });
+insert into ds2 ({"id": 101, "c": 2, "d": 101 });
+insert into ds2 ({"id": 102, "c": 3, "d": 102 });
+
+insert into ds3 ({"id": 200, "e": 3, "f": 200 });
+insert into ds3 ({"id": 201, "e": 3, "f": 201 });
+insert into ds3 ({"id": 202, "e": 4, "f": 202 });
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_01/union_all_01.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_01/union_all_01.3.query.sqlpp
new file mode 100644
index 0000000..748dce4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_01/union_all_01.3.query.sqlpp
@@ -0,0 +1,34 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * UNION ALL -> SELECT list rewriting
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+use test;
+
+select id, a, b from ds1
+union all
+select id, c, d from ds2
+union all
+select id, e, f from ds3
+order by id desc;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_01/union_all_01.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_01/union_all_01.4.query.sqlpp
new file mode 100644
index 0000000..bc557bf
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_01/union_all_01.4.query.sqlpp
@@ -0,0 +1,34 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * UNION ALL -> SELECT list rewriting
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+use test;
+
+select a, b from ds1
+union all
+select c, d from ds2
+union all
+select e, f from ds3
+order by a desc, b desc;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_01/union_all_01.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_01/union_all_01.5.query.sqlpp
new file mode 100644
index 0000000..7682176
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_01/union_all_01.5.query.sqlpp
@@ -0,0 +1,34 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * SELECT VALUE inside UNION ALL -> no rewriting
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+use test;
+
+select value { id, a, b } from ds1
+union all
+select value { id, c, d } from ds2
+union all
+select value { id, e, f } from ds3
+order by id desc;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_01/union_all_01.6.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_01/union_all_01.6.query.sqlpp
new file mode 100644
index 0000000..4812fa3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_01/union_all_01.6.query.sqlpp
@@ -0,0 +1,38 @@
+/*
+ * 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.
+ */
+
+/*
+ * UNION ALL in subquery -> SELECT list rewriting and subquery coercion
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+use test;
+
+select x, y
+from range(2, 4) x, [3, 100, 200] y
+where (x, y) in (
+  select a, b from ds1
+  union all
+  select c, d from ds2
+  union all
+  select e, f from ds3
+)
+order by x, y;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_01/union_all_01.7.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_01/union_all_01.7.query.sqlpp
new file mode 100644
index 0000000..9524ba8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_01/union_all_01.7.query.sqlpp
@@ -0,0 +1,38 @@
+/*
+ * 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.
+ */
+
+/*
+ * UNION ALL in subquery -> SELECT list rewriting and subquery coercion
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+use test;
+
+select x
+from [0, 1, 101, 201, 301] x
+where x in (
+  select b from ds1
+  union all
+  select d from ds2
+  union all
+  select f from ds3
+)
+order by x;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_01/union_all_01.8.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_01/union_all_01.8.query.sqlpp
new file mode 100644
index 0000000..8fd5852
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_01/union_all_01.8.query.sqlpp
@@ -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.
+ */
+
+/*
+ * UNION ALL in subquery -> SELECT list rewriting and subquery coercion
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+use test;
+
+select x,
+  (
+    select a from ds1
+    where id = 101
+    union all
+    select c from ds2
+    where id = 101
+    union all
+    select e from ds3
+    where id = 101
+  ) y
+from range(0, 1) x
+order by x;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_02_negative/union_all_02_negative.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_02_negative/union_all_02_negative.1.ddl.sqlpp
new file mode 100644
index 0000000..83754e1
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_02_negative/union_all_02_negative.1.ddl.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+create dataset ds1(id int) open type primary key id;
+
+create dataset ds2(id int) open type primary key id;
+
+create dataset ds3(id int) open type primary key id;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_02_negative/union_all_02_negative.2.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_02_negative/union_all_02_negative.2.query.sqlpp
new file mode 100644
index 0000000..a78aea6
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_02_negative/union_all_02_negative.2.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.
+ */
+
+/*
+ * UNION ALL failure. Both SELECT and SELECT VALUE are present
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+use test;
+
+select id, a, b from ds1
+union all
+select value { id, c, d } from ds2;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_02_negative/union_all_02_negative.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_02_negative/union_all_02_negative.3.query.sqlpp
new file mode 100644
index 0000000..3ba52d8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_02_negative/union_all_02_negative.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.
+ */
+
+/*
+ * UNION ALL failure. Different number of projected fields.
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+use test;
+
+select id, a, b from ds1
+union all
+select id, c from ds2;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_02_negative/union_all_02_negative.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_02_negative/union_all_02_negative.4.query.sqlpp
new file mode 100644
index 0000000..f20bd55
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_02_negative/union_all_02_negative.4.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.
+ */
+
+/*
+ * UNION ALL failure. Unsupported projection kind (SELECT *)
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+use test;
+
+select * from ds1
+union all
+select * from ds2;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_02_negative/union_all_02_negative.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_02_negative/union_all_02_negative.5.query.sqlpp
new file mode 100644
index 0000000..15a44d1
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_02_negative/union_all_02_negative.5.query.sqlpp
@@ -0,0 +1,32 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * UNION ALL failure. Unsupported projection kind (SELECT v.*)
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+use test;
+
+
+select x.* from ds1 x
+union all
+select y.* from ds2 y;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_02_negative/union_all_02_negative.6.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_02_negative/union_all_02_negative.6.query.sqlpp
new file mode 100644
index 0000000..0941248
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/union_all_02_negative/union_all_02_negative.6.query.sqlpp
@@ -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.
+ */
+
+/*
+ * UNION ALL failure. Unsupported projection kind (SELECT *, v.* mixed)
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+use test;
+
+select id from ds1
+union all
+select * from ds2
+union all
+select v3.* from ds3 v3;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/union_all_01/union_all_01.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/union_all_01/union_all_01.3.adm
new file mode 100644
index 0000000..53680ee
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/union_all_01/union_all_01.3.adm
@@ -0,0 +1,13 @@
+{ "id": 202, "a": 4, "b": 202 }
+{ "id": 201, "a": 3, "b": 201 }
+{ "id": 200, "a": 3, "b": 200 }
+{ "id": 102, "a": 3, "b": 102 }
+{ "id": 101, "a": 2, "b": 101 }
+{ "id": 100, "a": 2, "b": 100 }
+{ "id": 7, "a": 4, "b": 7 }
+{ "id": 6, "a": 3, "b": 6 }
+{ "id": 5, "a": 3, "b": 5 }
+{ "id": 4, "a": 2, "b": 4 }
+{ "id": 3, "a": 2, "b": 3 }
+{ "id": 2, "a": 1, "b": 2 }
+{ "id": 1, "a": 1, "b": 1 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/union_all_01/union_all_01.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/union_all_01/union_all_01.4.adm
new file mode 100644
index 0000000..b1ed67c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/union_all_01/union_all_01.4.adm
@@ -0,0 +1,13 @@
+{ "a": 4, "b": 202 }
+{ "a": 4, "b": 7 }
+{ "a": 3, "b": 201 }
+{ "a": 3, "b": 200 }
+{ "a": 3, "b": 102 }
+{ "a": 3, "b": 6 }
+{ "a": 3, "b": 5 }
+{ "a": 2, "b": 101 }
+{ "a": 2, "b": 100 }
+{ "a": 2, "b": 4 }
+{ "a": 2, "b": 3 }
+{ "a": 1, "b": 2 }
+{ "a": 1, "b": 1 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/union_all_01/union_all_01.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/union_all_01/union_all_01.5.adm
new file mode 100644
index 0000000..60dc861
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/union_all_01/union_all_01.5.adm
@@ -0,0 +1,13 @@
+{ "id": 202, "e": 4, "f": 202 }
+{ "id": 201, "e": 3, "f": 201 }
+{ "id": 200, "e": 3, "f": 200 }
+{ "id": 102, "c": 3, "d": 102 }
+{ "id": 101, "c": 2, "d": 101 }
+{ "id": 100, "c": 2, "d": 100 }
+{ "id": 7, "a": 4, "b": 7 }
+{ "id": 6, "a": 3, "b": 6 }
+{ "id": 5, "a": 3, "b": 5 }
+{ "id": 4, "a": 2, "b": 4 }
+{ "id": 3, "a": 2, "b": 3 }
+{ "id": 2, "a": 1, "b": 2 }
+{ "id": 1, "a": 1, "b": 1 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/union_all_01/union_all_01.6.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/union_all_01/union_all_01.6.adm
new file mode 100644
index 0000000..6ce501e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/union_all_01/union_all_01.6.adm
@@ -0,0 +1,3 @@
+{ "x": 2, "y": 3 }
+{ "x": 2, "y": 100 }
+{ "x": 3, "y": 200 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/union_all_01/union_all_01.7.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/union_all_01/union_all_01.7.adm
new file mode 100644
index 0000000..68d0470
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/union_all_01/union_all_01.7.adm
@@ -0,0 +1,3 @@
+{ "x": 1 }
+{ "x": 101 }
+{ "x": 201 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/union_all_01/union_all_01.8.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/union_all_01/union_all_01.8.adm
new file mode 100644
index 0000000..ada7b8c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/union_all_01/union_all_01.8.adm
@@ -0,0 +1,2 @@
+{ "x": 0, "y": 2 }
+{ "x": 1, "y": 2 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
index fa7d501..d400c27 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -10257,6 +10257,21 @@
         <output-dir compare="Text">subquery_coercion_08_misc</output-dir>
       </compilation-unit>
     </test-case>
+    <test-case FilePath="sql-compat">
+      <compilation-unit name="union_all_01">
+        <output-dir compare="Text">union_all_01</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="sql-compat">
+      <compilation-unit name="union_all_02_negative">
+        <output-dir compare="Text">none</output-dir>
+        <expected-error>ASX1171: Unable to process UNION clause. Both SELECT and SELECT VALUE are present (in line 29, at column 1)</expected-error>
+        <expected-error>ASX1171: Unable to process UNION clause. Unequal number of input fields (in line 29, at column 1)</expected-error>
+        <expected-error>ASX1171: Unable to process UNION clause. Unsupported projection kind (in line 29, at column 8)</expected-error>
+        <expected-error>ASX1171: Unable to process UNION clause. Unsupported projection kind (in line 30, at column 8)</expected-error>
+        <expected-error>ASX1171: Unable to process UNION clause. Unsupported projection kind (in line 31, at column 8)</expected-error>
+      </compilation-unit>
+    </test-case>
   </test-group>
   <test-group name="statement-params">
     <test-case FilePath="statement-params">
diff --git a/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/exceptions/ErrorCode.java b/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/exceptions/ErrorCode.java
index 4bfe5e6..8939ce0 100644
--- a/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/exceptions/ErrorCode.java
+++ b/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/exceptions/ErrorCode.java
@@ -256,6 +256,7 @@
     AMBIGUOUS_PROJECTION(1168),
     COMPILATION_SUBQUERY_COERCION_ERROR(1169),
     S3_REGION_NOT_SUPPORTED(1170),
+    COMPILATION_SET_OPERATION_ERROR(1171),
 
     // Feed errors
     DATAFLOW_ILLEGAL_STATE(3001),
diff --git a/asterixdb/asterix-common/src/main/resources/asx_errormsg/en.properties b/asterixdb/asterix-common/src/main/resources/asx_errormsg/en.properties
index 4d95ae0..5120908f 100644
--- a/asterixdb/asterix-common/src/main/resources/asx_errormsg/en.properties
+++ b/asterixdb/asterix-common/src/main/resources/asx_errormsg/en.properties
@@ -258,6 +258,7 @@
 1168 = Ambiguous projection in SELECT clause
 1169 = Unable to do subquery coercion. %1$s
 1170 = Provided S3 region is not supported: '%1$s'
+1171 = Unable to process %1$s clause. %2$s
 
 # Feed Errors
 3001 = Illegal state.
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlCompatRewriteVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlCompatRewriteVisitor.java
index 9b36a4c..9b91bc0 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlCompatRewriteVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlCompatRewriteVisitor.java
@@ -36,14 +36,17 @@
 import org.apache.asterix.lang.common.clause.LimitClause;
 import org.apache.asterix.lang.common.expression.CallExpr;
 import org.apache.asterix.lang.common.expression.FieldAccessor;
+import org.apache.asterix.lang.common.expression.FieldBinding;
 import org.apache.asterix.lang.common.expression.IndexAccessor;
 import org.apache.asterix.lang.common.expression.ListConstructor;
 import org.apache.asterix.lang.common.expression.LiteralExpr;
 import org.apache.asterix.lang.common.expression.OperatorExpr;
 import org.apache.asterix.lang.common.expression.QuantifiedExpression;
+import org.apache.asterix.lang.common.expression.RecordConstructor;
 import org.apache.asterix.lang.common.expression.UnaryExpr;
 import org.apache.asterix.lang.common.expression.VariableExpr;
 import org.apache.asterix.lang.common.literal.IntegerLiteral;
+import org.apache.asterix.lang.common.literal.StringLiteral;
 import org.apache.asterix.lang.common.rewrites.LangRewritingContext;
 import org.apache.asterix.lang.common.struct.Identifier;
 import org.apache.asterix.lang.common.struct.OperatorType;
@@ -56,9 +59,11 @@
 import org.apache.asterix.lang.sqlpp.clause.SelectBlock;
 import org.apache.asterix.lang.sqlpp.clause.SelectClause;
 import org.apache.asterix.lang.sqlpp.clause.SelectElement;
+import org.apache.asterix.lang.sqlpp.clause.SelectRegular;
 import org.apache.asterix.lang.sqlpp.clause.SelectSetOperation;
 import org.apache.asterix.lang.sqlpp.clause.UnnestClause;
 import org.apache.asterix.lang.sqlpp.expression.SelectExpression;
+import org.apache.asterix.lang.sqlpp.optype.SetOpType;
 import org.apache.asterix.lang.sqlpp.struct.SetOperationInput;
 import org.apache.asterix.lang.sqlpp.struct.SetOperationRight;
 import org.apache.asterix.lang.sqlpp.util.FunctionMapUtil;
@@ -300,7 +305,7 @@
     private SqlCompatSelectExpressionCoercionAnnotation annotateSubquery(SelectExpression subqueryExpr,
             SqlCompatSelectCoercionKind cardinalityCoercion, SqlCompatSelectCoercionKind typeCoercion)
             throws CompilationException {
-        selectExprAnalyzer.analyze(subqueryExpr);
+        selectExprAnalyzer.analyze(subqueryExpr.getSelectSetOperation(), true);
         if (selectExprAnalyzer.subqueryExists) {
             throw new CompilationException(ErrorCode.COMPILATION_SUBQUERY_COERCION_ERROR,
                     subqueryExpr.getSourceLocation(), "");
@@ -321,6 +326,120 @@
         }
     }
 
+    @Override
+    public Expression visit(SelectSetOperation setOp, ILangExpression arg) throws CompilationException {
+        // let subquery coercion rewriting run first
+        super.visit(setOp, arg);
+
+        if (setOp.hasRightInputs()) {
+            // SetOp (UNION ALL) rewriting
+            selectExprAnalyzer.analyze(setOp, false);
+            if (selectExprAnalyzer.subqueryExists) {
+                throw new CompilationException(ErrorCode.COMPILATION_SET_OPERATION_ERROR, setOp.getSourceLocation(),
+                        setOp.getRightInputs().get(0).getSetOpType().toString(), "");
+            }
+            if (selectExprAnalyzer.selectRegularExists) {
+                if (selectExprAnalyzer.selectElementExists) {
+                    throw new CompilationException(ErrorCode.COMPILATION_SET_OPERATION_ERROR, setOp.getSourceLocation(),
+                            setOp.getRightInputs().get(0).getSetOpType().toString(),
+                            "Both SELECT and SELECT VALUE are present");
+                }
+                rewriteSelectSetOp(setOp);
+            }
+        }
+
+        return null;
+    }
+
+    private void rewriteSelectSetOp(SelectSetOperation setOp) throws CompilationException {
+        /*
+         * SELECT a, b, c FROM ...
+         * UNION ALL
+         * SELECT d, e, f FROM ....
+         * -->
+         * SELECT a, b, c
+         * UNION ALL
+         * SELECT v.d AS a, v.e AS b, v.f AS c FROM ( SELECT d, e, f FROM ... )
+         */
+        SelectBlock leftSelectBlock = setOp.getLeftInput().getSelectBlock();
+        boolean ok = leftSelectBlock != null && setOp.hasRightInputs();
+        if (!ok) {
+            throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_STATE, setOp.getSourceLocation(), "");
+        }
+        // collect all field names from the left input
+        List<String> leftFieldNames = collectFieldNames(leftSelectBlock, setOp.getRightInputs().get(0).getSetOpType());
+        for (SetOperationRight rightInput : setOp.getRightInputs()) {
+            // rewrite right inputs
+            rewriteSelectSetOpRightInput(rightInput, leftFieldNames, setOp.getSourceLocation());
+        }
+    }
+
+    private void rewriteSelectSetOpRightInput(SetOperationRight setOpRight, List<String> outputFieldNames,
+            SourceLocation sourceLoc) throws CompilationException {
+        SetOperationInput setOpRightInput = setOpRight.getSetOperationRightInput();
+        SelectBlock rightSelectBlock = setOpRightInput.getSelectBlock();
+        if (rightSelectBlock == null) {
+            throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_STATE, sourceLoc, "");
+        }
+        List<String> inputFieldNames = collectFieldNames(rightSelectBlock, setOpRight.getSetOpType());
+        int nFields = inputFieldNames.size();
+        if (nFields != outputFieldNames.size()) {
+            throw new CompilationException(ErrorCode.COMPILATION_SET_OPERATION_ERROR, sourceLoc,
+                    setOpRight.getSetOpType().toString(), "Unequal number of input fields");
+        }
+
+        SelectSetOperation setOp1 = new SelectSetOperation(new SetOperationInput(rightSelectBlock, null), null);
+        setOp1.setSourceLocation(sourceLoc);
+        SelectExpression selectExpr1 = new SelectExpression(null, setOp1, null, null, true);
+        selectExpr1.setSourceLocation(sourceLoc);
+
+        VarIdentifier v1 = context.newVariable();
+        VariableExpr v1Expr1 = new VariableExpr(v1);
+        v1Expr1.setSourceLocation(sourceLoc);
+
+        FromTerm fromTerm1 = new FromTerm(selectExpr1, v1Expr1, null, null);
+        fromTerm1.setSourceLocation(sourceLoc);
+        List<FromTerm> fromTermList1 = new ArrayList<>(1);
+        fromTermList1.add(fromTerm1);
+        FromClause fromClause1 = new FromClause(fromTermList1);
+        fromClause1.setSourceLocation(sourceLoc);
+
+        List<FieldBinding> fb1List = new ArrayList<>(nFields);
+        for (int i = 0; i < nFields; i++) {
+            VariableExpr v1Expr2 = new VariableExpr(v1);
+            v1Expr2.setSourceLocation(sourceLoc);
+            FieldAccessor fa1 = new FieldAccessor(v1Expr2, new Identifier(inputFieldNames.get(i)));
+            fa1.setSourceLocation(sourceLoc);
+            LiteralExpr lit1 = new LiteralExpr(new StringLiteral(outputFieldNames.get(i)));
+            lit1.setSourceLocation(sourceLoc);
+            fb1List.add(new FieldBinding(lit1, fa1));
+        }
+        RecordConstructor rc1 = new RecordConstructor(fb1List);
+        rc1.setSourceLocation(sourceLoc);
+        SelectClause selectClause1 = new SelectClause(new SelectElement(rc1), null, false);
+        selectClause1.setSourceLocation(sourceLoc);
+        SelectBlock newRightSelectBlock = new SelectBlock(selectClause1, fromClause1, null, null, null);
+        newRightSelectBlock.setSourceLocation(sourceLoc);
+        setOpRightInput.setSelectBlock(newRightSelectBlock);
+    }
+
+    private List<String> collectFieldNames(SelectBlock selectBlock, SetOpType setOpType) throws CompilationException {
+        SelectRegular selectRegular = selectBlock.getSelectClause().getSelectRegular();
+        if (selectRegular == null) {
+            throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_STATE, selectBlock.getSourceLocation(), "");
+        }
+        List<Projection> projectionList = selectRegular.getProjections();
+        List<String> fieldNames = new ArrayList<>(projectionList.size());
+        for (Projection projection : projectionList) {
+            if (projection.getKind() != Projection.Kind.NAMED_EXPR) {
+                throw new CompilationException(ErrorCode.COMPILATION_SET_OPERATION_ERROR,
+                        projection.getSourceLocation(), setOpType.toString(), "Unsupported projection kind");
+            }
+            fieldNames.add(projection.getName());
+        }
+        return fieldNames;
+    }
+
     private static CallExpr createCallExpr(FunctionIdentifier fid, Expression inExpr, SourceLocation sourceLoc) {
         List<Expression> argList = new ArrayList<>(1);
         argList.add(inExpr);
@@ -334,18 +453,20 @@
         private boolean subqueryExists;
         private boolean selectRegularExists;
         private boolean selectElementExists;
+        private boolean computeSelectRegularAllFields;
         private final Set<String> selectRegularAllFields = new HashSet<>();
 
-        private void reset() {
+        private void reset(boolean computeSelectRegularAllFields) {
             subqueryExists = false;
             selectRegularExists = false;
             selectElementExists = false;
             selectRegularAllFields.clear();
+            this.computeSelectRegularAllFields = computeSelectRegularAllFields;
         }
 
-        private void analyze(SelectExpression selectExpr) throws CompilationException {
-            reset();
-            SelectSetOperation setOp = selectExpr.getSelectSetOperation();
+        private void analyze(SelectSetOperation setOp, boolean computeSelectRegularAllFields)
+                throws CompilationException {
+            reset(computeSelectRegularAllFields);
             analyzeSelectSetOpInput(setOp.getLeftInput());
             if (setOp.hasRightInputs()) {
                 for (SetOperationRight rhs : setOp.getRightInputs()) {
@@ -360,9 +481,11 @@
                 SelectClause selectClause = selectBlock.getSelectClause();
                 if (selectClause.selectRegular()) {
                     selectRegularExists = true;
-                    for (Projection projection : selectClause.getSelectRegular().getProjections()) {
-                        if (projection.getKind() == Projection.Kind.NAMED_EXPR) {
-                            selectRegularAllFields.add(projection.getName());
+                    if (computeSelectRegularAllFields) {
+                        for (Projection projection : selectClause.getSelectRegular().getProjections()) {
+                            if (projection.getKind() == Projection.Kind.NAMED_EXPR) {
+                                selectRegularAllFields.add(projection.getName());
+                            }
                         }
                     }
                 } else if (selectClause.selectElement()) {
@@ -508,7 +631,7 @@
             /*
              * inExpr = SELECT ..., type_coercion_expr AS $new_unique_field
              * -->
-             * inExpr = SELECT VALUE v1.$new_unique_field FROM (SELECT ..., type_coercion_expr AS #x) v1
+             * inExpr = SELECT VALUE v1.$new_unique_field FROM (SELECT ..., type_coercion_expr AS $new_unique_field) v1
              */
             VarIdentifier v1 = context.newVariable();
             VariableExpr v1Ref1 = new VariableExpr(v1);