diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.10.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.10.query.sqlpp
new file mode 100644
index 0000000..4b8d3be
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.10.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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's
+ * outside of IN/NOT IN, comparison operators,
+ * FROM/JOIN/UNNEST clauses
+ *
+ * SELECT subquery with ORDER BY and LIMIT
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1, (select r2 as r3 from range(0, r1) r2 order by r3 desc limit 1) r3
+from range(1,3) r1
+order by r1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.15.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.15.query.sqlpp
new file mode 100644
index 0000000..326ebd2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.15.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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when it's compared
+ * with an expression which is not an explicit array constructor
+ *
+ * lhs = expr, rhs = subquery with ORDER BY and LIMIT
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+select r1
+from range(1, 3) r1
+where r1 = (select r2 as r3 from range(0, r1) r2 order by r3 desc limit 1)
+order by r1;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.11.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.11.query.sqlpp
new file mode 100644
index 0000000..d061efe
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.11.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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when
+ * its the rhs argument of the IN operator
+ * and lhs expression is an explicit array constructor
+ * and subquery contains an ORDER BY clause
+ *
+ * lhs = expr, rhs = subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+select r1
+from range(1, 5) r1
+where r1 IN (select r2 as r3 from range(1, 5) r2 order by r3 desc limit 2)
+order by r1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.12.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.12.query.sqlpp
new file mode 100644
index 0000000..0940aa8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.12.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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when
+ * its the rhs argument of the IN operator
+ * and lhs expression is not an explicit array constructor
+ * and subquery contains ORDER BY and UNION ALL clauses
+ *
+ * lhs = expr, rhs = subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+select r1
+from range(1, 5) r1
+where r1 IN (
+  select r2 as r3 from [1, 5] r2
+  union all
+  select r4 as r3 from [2, 4] r4
+  order by r3 desc
+  limit 2
+)
+order by r1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.13.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.13.query.sqlpp
new file mode 100644
index 0000000..c6eabaf
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.13.query.sqlpp
@@ -0,0 +1,37 @@
+/*
+ * 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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when
+ * its the rhs argument of the IN operator
+ * and lhs expression is an explicit array constructor
+ * and subquery contains an ORDER BY clause
+ *
+ * lhs = expr, rhs = subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+select r1
+from range(1, 5) r1
+where (r1, r1+1) IN (
+  select r2 as r3, r2+1 from range(1, 5) r2 order by r3 desc limit 2
+  )
+order by r1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.14.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.14.query.sqlpp
new file mode 100644
index 0000000..82df0ec
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.14.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.
+ */
+
+/*
+ * SQL-compat mode.
+ * Test subquery coercion into a scalar value when
+ * its the rhs argument of the IN operator
+ * and lhs expression is an explicit array constructor
+ * and subquery contains ORDER BY and UNION ALL clauses
+ *
+ * lhs = expr, rhs = subquery
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+select r1
+from range(1, 5) r1
+where (r1, r1+1) IN (
+  select r2 as r3, r2+1 r5 from [1, 5] r2
+  union all
+  select r4 as r3, r4+1 r5 from [2, 4] r4
+  order by r3 desc
+  limit 2
+)
+order by r1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.10.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.10.adm
new file mode 100644
index 0000000..b56511d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_01_scalar/subquery_coercion_01_scalar.10.adm
@@ -0,0 +1,3 @@
+{ "r1": 1, "r3": 1 }
+{ "r1": 2, "r3": 2 }
+{ "r1": 3, "r3": 3 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.15.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.15.adm
new file mode 100644
index 0000000..572906d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_03_cmp/subquery_coercion_03_cmp.15.adm
@@ -0,0 +1,3 @@
+{ "r1": 1 }
+{ "r1": 2 }
+{ "r1": 3 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.11.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.11.adm
new file mode 100644
index 0000000..bfd12d4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.11.adm
@@ -0,0 +1,2 @@
+{ "r1": 4 }
+{ "r1": 5 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.12.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.12.adm
new file mode 100644
index 0000000..bfd12d4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.12.adm
@@ -0,0 +1,2 @@
+{ "r1": 4 }
+{ "r1": 5 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.13.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.13.adm
new file mode 100644
index 0000000..bfd12d4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.13.adm
@@ -0,0 +1,2 @@
+{ "r1": 4 }
+{ "r1": 5 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.14.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.14.adm
new file mode 100644
index 0000000..bfd12d4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/subquery_coercion_05_in/subquery_coercion_05_in.14.adm
@@ -0,0 +1,2 @@
+{ "r1": 4 }
+{ "r1": 5 }
\ No newline at end of file
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppQueryRewriter.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppQueryRewriter.java
index 7394c95..2f4fcc8 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppQueryRewriter.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppQueryRewriter.java
@@ -139,15 +139,17 @@
         // Sets up parameters.
         setup(context, topStatement, externalVars, allowNonStoredUdfCalls, inlineUdfsAndViews);
 
-        // Initial SQL-compat mode rewrites
-        rewriteSqlCompat();
-
         // Resolves function calls
         resolveFunctionCalls();
 
         // Generates column names.
         generateColumnNames();
 
+        // SQL-compat mode rewrites
+        // Must run after generateColumnNames() because it might need to generate new column names
+        // for the new projections that it introduces
+        rewriteSqlCompat();
+
         // Substitutes group-by key expressions.
         substituteGroupbyKeyExpression();
 
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 a0939c3..9b36a4c 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
@@ -20,7 +20,10 @@
 package org.apache.asterix.lang.sqlpp.rewrites.visitor;
 
 import java.util.ArrayList;
+import java.util.HashSet;
 import java.util.List;
+import java.util.Objects;
+import java.util.Set;
 
 import org.apache.asterix.common.exceptions.CompilationException;
 import org.apache.asterix.common.exceptions.ErrorCode;
@@ -32,6 +35,7 @@
 import org.apache.asterix.lang.common.clause.LetClause;
 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.IndexAccessor;
 import org.apache.asterix.lang.common.expression.ListConstructor;
 import org.apache.asterix.lang.common.expression.LiteralExpr;
@@ -41,6 +45,7 @@
 import org.apache.asterix.lang.common.expression.VariableExpr;
 import org.apache.asterix.lang.common.literal.IntegerLiteral;
 import org.apache.asterix.lang.common.rewrites.LangRewritingContext;
+import org.apache.asterix.lang.common.struct.Identifier;
 import org.apache.asterix.lang.common.struct.OperatorType;
 import org.apache.asterix.lang.common.struct.QuantifiedPair;
 import org.apache.asterix.lang.common.struct.VarIdentifier;
@@ -57,6 +62,8 @@
 import org.apache.asterix.lang.sqlpp.struct.SetOperationInput;
 import org.apache.asterix.lang.sqlpp.struct.SetOperationRight;
 import org.apache.asterix.lang.sqlpp.util.FunctionMapUtil;
+import org.apache.asterix.lang.sqlpp.util.SqlppRewriteUtil;
+import org.apache.asterix.lang.sqlpp.util.SqlppVariableUtil;
 import org.apache.asterix.lang.sqlpp.visitor.base.AbstractSqlppSimpleExpressionVisitor;
 import org.apache.asterix.om.functions.BuiltinFunctions;
 import org.apache.hyracks.algebricks.core.algebra.expressions.IExpressionAnnotation;
@@ -86,7 +93,7 @@
 
     private final LangRewritingContext context;
 
-    private final SelectSetOpInfo setOpInfo = new SelectSetOpInfo();
+    private final SelectExpressionAnalyzer selectExprAnalyzer = new SelectExpressionAnalyzer();
 
     public SqlCompatRewriteVisitor(LangRewritingContext context) {
         this.context = context;
@@ -230,10 +237,8 @@
             selectExprAnn = selectExpr.findHint(SqlCompatSelectExpressionCoercionAnnotation.class);
             if (selectExprAnn == null) {
                 // all other cases --> coerce the subquery into a scalar value
-                if (annotateSubquery(selectExpr, SqlCompatSelectExpressionCoercionAnnotation.SCALAR,
-                        SqlCompatSelectBlockCoercionAnnotation.SCALAR)) {
-                    selectExprAnn = SqlCompatSelectExpressionCoercionAnnotation.SCALAR;
-                }
+                selectExprAnn = annotateSubquery(selectExpr, SqlCompatSelectCoercionKind.SCALAR,
+                        SqlCompatSelectCoercionKind.SCALAR);
             }
         }
         Expression newExpr = super.visit(selectExpr, arg);
@@ -247,94 +252,72 @@
     public Expression visit(SelectBlock selectBlock, ILangExpression arg) throws CompilationException {
         super.visit(selectBlock, arg);
         SelectExpression selectExpr = (SelectExpression) arg;
-        SqlCompatSelectBlockCoercionAnnotation selectBlockAnn =
-                selectExpr.findHint(SqlCompatSelectBlockCoercionAnnotation.class);
-        if (selectBlockAnn != null) {
-            rewriteSelectBlock(selectBlock, selectBlockAnn);
+        SqlCompatSelectExpressionCoercionAnnotation selectExprAnn =
+                selectExpr.findHint(SqlCompatSelectExpressionCoercionAnnotation.class);
+        if (selectExprAnn != null) {
+            rewriteSelectBlock(selectBlock, selectExprAnn);
         }
         return null;
     }
 
     private void annotateSubqueryNoCoercion(SelectExpression subqueryExpr) {
         // FROM/JOIN/UNNEST/LET (subquery) -> do NOT coerce the subquery
-        subqueryExpr.addHint(SqlCompatSelectExpressionCoercionAnnotation.COLLECTION);
+        subqueryExpr.addHint(SqlCompatSelectExpressionCoercionAnnotation.NONE_NONE);
     }
 
     private void annotateComparisonOpSubquery(SelectExpression subqueryExpr, Expression otherArg)
             throws CompilationException {
         // (x,y) = (subquery) -> coerce the subquery into a single array
         // x = (subquery) -> coerce the subquery into a scalar value
-        annotateSubquery(subqueryExpr, SqlCompatSelectExpressionCoercionAnnotation.SCALAR,
+        annotateSubquery(subqueryExpr, SqlCompatSelectCoercionKind.SCALAR,
                 getSelectBlockAnnotationForOpSubquery(otherArg));
     }
 
     private void annotateInOpSubquery(SelectExpression subqueryExpr, Expression otherArg) throws CompilationException {
         // (x,y) in (subquery) -> coerce the subquery into a collection of arrays
         // x in (subquery) -> coerce the subquery into a collection of scalar values
-        annotateSubquery(subqueryExpr, SqlCompatSelectExpressionCoercionAnnotation.COLLECTION,
+        annotateSubquery(subqueryExpr, SqlCompatSelectCoercionKind.NONE,
                 getSelectBlockAnnotationForOpSubquery(otherArg));
     }
 
-    private static SqlCompatSelectBlockCoercionAnnotation getSelectBlockAnnotationForOpSubquery(Expression otherArg)
+    private static SqlCompatSelectCoercionKind getSelectBlockAnnotationForOpSubquery(Expression otherArg)
             throws CompilationException {
         if (otherArg.getKind() == Expression.Kind.LIST_CONSTRUCTOR_EXPRESSION) {
             ListConstructor lc = (ListConstructor) otherArg;
             switch (lc.getType()) {
                 case ORDERED_LIST_CONSTRUCTOR:
-                    return SqlCompatSelectBlockCoercionAnnotation.ARRAY;
+                    return SqlCompatSelectCoercionKind.ARRAY;
                 case UNORDERED_LIST_CONSTRUCTOR:
-                    return SqlCompatSelectBlockCoercionAnnotation.MULTISET;
+                    return SqlCompatSelectCoercionKind.MULTISET;
                 default:
                     throw new CompilationException(ErrorCode.ILLEGAL_STATE, otherArg.getSourceLocation(), "");
             }
         } else {
-            return SqlCompatSelectBlockCoercionAnnotation.SCALAR;
+            return SqlCompatSelectCoercionKind.SCALAR;
         }
     }
 
-    private boolean annotateSubquery(SelectExpression subqueryExpr,
-            SqlCompatSelectExpressionCoercionAnnotation selectExprAnnotation,
-            SqlCompatSelectBlockCoercionAnnotation selectBlockAnn) throws CompilationException {
-        setOpInfo.reset();
-        analyzeSelectSetOp(subqueryExpr.getSelectSetOperation(), setOpInfo);
-        if (setOpInfo.subqueryExists) {
+    private SqlCompatSelectExpressionCoercionAnnotation annotateSubquery(SelectExpression subqueryExpr,
+            SqlCompatSelectCoercionKind cardinalityCoercion, SqlCompatSelectCoercionKind typeCoercion)
+            throws CompilationException {
+        selectExprAnalyzer.analyze(subqueryExpr);
+        if (selectExprAnalyzer.subqueryExists) {
             throw new CompilationException(ErrorCode.COMPILATION_SUBQUERY_COERCION_ERROR,
                     subqueryExpr.getSourceLocation(), "");
         }
-        if (setOpInfo.selectRegularExists) {
-            if (setOpInfo.selectElementExists) {
+        if (selectExprAnalyzer.selectRegularExists) {
+            if (selectExprAnalyzer.selectElementExists) {
                 throw new CompilationException(ErrorCode.COMPILATION_SUBQUERY_COERCION_ERROR,
                         subqueryExpr.getSourceLocation(), "Both SELECT and SELECT VALUE are present");
             }
-            subqueryExpr.addHint(selectExprAnnotation);
-            subqueryExpr.addHint(selectBlockAnn);
-            return true;
+            String typeCoercionFieldName = typeCoercion == SqlCompatSelectCoercionKind.NONE ? null
+                    : selectExprAnalyzer.generateFieldName(context);
+            SqlCompatSelectExpressionCoercionAnnotation ann = new SqlCompatSelectExpressionCoercionAnnotation(
+                    cardinalityCoercion, typeCoercion, typeCoercionFieldName);
+            subqueryExpr.addHint(ann);
+            return ann;
         } else {
-            return false;
-        }
-    }
-
-    private static void analyzeSelectSetOp(SelectSetOperation setOp, SelectSetOpInfo outSelectExprInfo)
-            throws CompilationException {
-        analyzeSelectSetOpInput(setOp.getLeftInput(), outSelectExprInfo);
-        if (setOp.hasRightInputs()) {
-            for (SetOperationRight rhs : setOp.getRightInputs()) {
-                analyzeSelectSetOpInput(rhs.getSetOperationRightInput(), outSelectExprInfo);
-            }
-        }
-    }
-
-    private static void analyzeSelectSetOpInput(SetOperationInput setOpInput, SelectSetOpInfo outSelectSetOpInfo)
-            throws CompilationException {
-        if (setOpInput.selectBlock()) {
-            SelectBlock selectBlock = setOpInput.getSelectBlock();
-            SelectClause selectClause = selectBlock.getSelectClause();
-            outSelectSetOpInfo.selectRegularExists |= selectClause.selectRegular();
-            outSelectSetOpInfo.selectElementExists |= selectClause.selectElement();
-        } else if (setOpInput.subquery()) {
-            outSelectSetOpInfo.subqueryExists = true;
-        } else {
-            throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_STATE, "");
+            return null;
         }
     }
 
@@ -346,59 +329,128 @@
         return callExpr;
     }
 
-    private static final class SelectSetOpInfo {
+    private static final class SelectExpressionAnalyzer {
+
         private boolean subqueryExists;
         private boolean selectRegularExists;
         private boolean selectElementExists;
+        private final Set<String> selectRegularAllFields = new HashSet<>();
 
-        void reset() {
+        private void reset() {
             subqueryExists = false;
             selectRegularExists = false;
             selectElementExists = false;
+            selectRegularAllFields.clear();
+        }
+
+        private void analyze(SelectExpression selectExpr) throws CompilationException {
+            reset();
+            SelectSetOperation setOp = selectExpr.getSelectSetOperation();
+            analyzeSelectSetOpInput(setOp.getLeftInput());
+            if (setOp.hasRightInputs()) {
+                for (SetOperationRight rhs : setOp.getRightInputs()) {
+                    analyzeSelectSetOpInput(rhs.getSetOperationRightInput());
+                }
+            }
+        }
+
+        private void analyzeSelectSetOpInput(SetOperationInput setOpInput) throws CompilationException {
+            if (setOpInput.selectBlock()) {
+                SelectBlock selectBlock = setOpInput.getSelectBlock();
+                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());
+                        }
+                    }
+                } else if (selectClause.selectElement()) {
+                    selectElementExists = true;
+                }
+            } else if (setOpInput.subquery()) {
+                subqueryExists = true;
+            } else {
+                throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_STATE, "");
+            }
+        }
+
+        private String generateFieldName(LangRewritingContext ctx) {
+            String fieldName;
+            do {
+                fieldName = SqlppVariableUtil.variableNameToDisplayedFieldName(ctx.newVariable().getValue());
+            } while (selectRegularAllFields.contains(fieldName));
+            return fieldName;
         }
     }
 
-    private enum SqlCompatSelectExpressionCoercionAnnotation implements IExpressionAnnotation {
+    private static class SqlCompatSelectExpressionCoercionAnnotation implements IExpressionAnnotation {
+
+        static final SqlCompatSelectExpressionCoercionAnnotation NONE_NONE =
+                new SqlCompatSelectExpressionCoercionAnnotation(SqlCompatSelectCoercionKind.NONE,
+                        SqlCompatSelectCoercionKind.NONE, null);
+
+        final SqlCompatSelectCoercionKind cardinalityCoercion;
+
+        final SqlCompatSelectCoercionKind typeCoercion;
+
+        final String typeCoercionFieldName;
+
+        SqlCompatSelectExpressionCoercionAnnotation(SqlCompatSelectCoercionKind cardinalityCoercion,
+                SqlCompatSelectCoercionKind typeCoercion, String typeCoercionFieldName) {
+            this.cardinalityCoercion = Objects.requireNonNull(cardinalityCoercion);
+            this.typeCoercion = Objects.requireNonNull(typeCoercion);
+            this.typeCoercionFieldName = typeCoercionFieldName;
+        }
+    }
+
+    private enum SqlCompatSelectCoercionKind {
+
         /**
-         * Indicates that the result of the {@link SelectExpression}
+         * Indicates that no transformation is needed.
+         * Applicable to both type and cardinality coercion.
+         */
+        NONE,
+
+        /**
+         * Used to specify type and cardinality coercion.
+         * <p>
+         * When used for type coercion
+         * indicates that the output record of the {@link SelectBlock} must be transformed
+         * into a scalar value if that output record has 1 field, or transformed into MISSING value otherwise.
+         * <p>
+         * When used for cardinality coercion
+         * indicates that the result of the {@link SelectExpression}
          * must be coerced into a single item if its cardinality is 1 or to MISSING otherwise.
          */
         SCALAR,
 
         /**
-         * Indicates that no transformation is needed
-         */
-        COLLECTION
-    }
-
-    private enum SqlCompatSelectBlockCoercionAnnotation implements IExpressionAnnotation {
-        /**
-         * Indicates that the output record of the {@link SelectBlock} must be transformed
-         * into a scalar value if that output record has 1 field, or transformed into MISSING value otherwise
-         */
-        SCALAR,
-
-        /**
+         * Only used to specify type coercion.
+         * <p>
          * Indicates that the output record of the {@link SelectBlock} must be transformed
          * into an array
          */
         ARRAY,
 
         /**
+         * Only used to specify type coercion.
+         * <p>
          * Indicates that the output record of the {@link SelectBlock} must be transformed
          * into a multiset
          */
-        MULTISET
+        MULTISET,
     }
 
-    private void rewriteSelectBlock(SelectBlock selectBlock, SqlCompatSelectBlockCoercionAnnotation ann)
+    private void rewriteSelectBlock(SelectBlock selectBlock, SqlCompatSelectExpressionCoercionAnnotation ann)
             throws CompilationException {
         SelectClause selectClause = selectBlock.getSelectClause();
         List<Projection> projectList = selectClause.getSelectRegular().getProjections();
-        switch (ann) {
+        SqlCompatSelectCoercionKind typeCoercion = ann.typeCoercion;
+        switch (typeCoercion) {
             case SCALAR:
                 /*
-                 * SELECT x -> SELECT VALUE x
+                 * SELECT x -> SELECT x, x AS $new_unique_field
                  * SELECT x, y -> ERROR
                  * SELECT * -> ERROR
                  */
@@ -411,15 +463,17 @@
                     throw new CompilationException(ErrorCode.COMPILATION_SUBQUERY_COERCION_ERROR,
                             projection.getSourceLocation(), "Unsupported projection kind");
                 }
-                SelectElement selectElement = new SelectElement(projection.getExpression());
-                selectElement.setSourceLocation(selectClause.getSourceLocation());
-                selectClause.setSelectElement(selectElement);
+                Projection typeCoercionProj = new Projection(Projection.Kind.NAMED_EXPR,
+                        (Expression) SqlppRewriteUtil.deepCopy(projection.getExpression()), ann.typeCoercionFieldName);
+                projectList.add(typeCoercionProj);
                 break;
             case ARRAY:
             case MULTISET:
                 /*
-                 * SELECT x -> SELECT VALUE [x]  (or SELECT VALUE {{x}})
-                 * SELECT x, y -> SELECT VALUE [x, y] (or SELECT VALUE {{x, y}})
+                 * SELECT x -> SELECT x, [x] AS $new_unique_field -- for ARRAY case
+                 *         (or SELECT VALUE x, {{x}} AS $new_unique_field) -- for MULTISET case
+                 * SELECT x, y -> SELECT x, y, [x, y] AS $new_unique_field -- for ARRAY case
+                 *            (or SELECT x, y, {{x, y}} AS $new_unique_field) -- for MULTISET case
                  * SELECT * -> ERROR
                  */
                 List<Expression> exprList = new ArrayList<>(projectList.size());
@@ -428,16 +482,17 @@
                         throw new CompilationException(ErrorCode.COMPILATION_SUBQUERY_COERCION_ERROR,
                                 p.getSourceLocation(), "Unsupported projection kind");
                     }
-                    exprList.add(p.getExpression());
+                    exprList.add((Expression) SqlppRewriteUtil.deepCopy(p.getExpression()));
                 }
-                ListConstructor.Type listType = ann == SqlCompatSelectBlockCoercionAnnotation.ARRAY
+                ListConstructor.Type listType = typeCoercion == SqlCompatSelectCoercionKind.ARRAY
                         ? ListConstructor.Type.ORDERED_LIST_CONSTRUCTOR
                         : ListConstructor.Type.UNORDERED_LIST_CONSTRUCTOR;
                 ListConstructor listExpr = new ListConstructor(listType, exprList);
                 listExpr.setSourceLocation(selectClause.getSourceLocation());
-                selectElement = new SelectElement(listExpr);
-                selectElement.setSourceLocation(selectClause.getSourceLocation());
-                selectClause.setSelectElement(selectElement);
+                typeCoercionProj = new Projection(Projection.Kind.NAMED_EXPR, listExpr, ann.typeCoercionFieldName);
+                projectList.add(typeCoercionProj);
+                break;
+            case NONE:
                 break;
             default:
                 throw new CompilationException(ErrorCode.ILLEGAL_STATE, selectBlock.getSourceLocation(),
@@ -447,21 +502,56 @@
 
     private Expression rewriteSelectExpression(Expression inExpr, SqlCompatSelectExpressionCoercionAnnotation ann)
             throws CompilationException {
-        switch (ann) {
+        SourceLocation sourceLoc = inExpr.getSourceLocation();
+
+        if (ann.typeCoercion != SqlCompatSelectCoercionKind.NONE) {
+            /*
+             * inExpr = SELECT ..., type_coercion_expr AS $new_unique_field
+             * -->
+             * inExpr = SELECT VALUE v1.$new_unique_field FROM (SELECT ..., type_coercion_expr AS #x) v1
+             */
+            VarIdentifier v1 = context.newVariable();
+            VariableExpr v1Ref1 = new VariableExpr(v1);
+            v1Ref1.setSourceLocation(sourceLoc);
+            FromTerm ft1 = new FromTerm(inExpr, v1Ref1, null, null);
+            ft1.setSourceLocation(sourceLoc);
+            List<FromTerm> fc1Terms = new ArrayList<>(1);
+            fc1Terms.add(ft1);
+            FromClause fc1 = new FromClause(fc1Terms);
+            fc1.setSourceLocation(sourceLoc);
+            VariableExpr v1Ref2 = new VariableExpr(v1);
+            v1Ref2.setSourceLocation(sourceLoc);
+            FieldAccessor fa = new FieldAccessor(v1Ref2, new Identifier(ann.typeCoercionFieldName));
+            fa.setSourceLocation(sourceLoc);
+            SelectElement sv1 = new SelectElement(fa);
+            sv1.setSourceLocation(sourceLoc);
+            SelectClause sc1 = new SelectClause(sv1, null, false);
+            sc1.setSourceLocation(sourceLoc);
+            SelectBlock sb1 = new SelectBlock(sc1, fc1, null, null, null);
+            sv1.setSourceLocation(sourceLoc);
+            SelectSetOperation sop1 = new SelectSetOperation(new SetOperationInput(sb1, null), null);
+            sop1.setSourceLocation(sourceLoc);
+            SelectExpression se1 = new SelectExpression(null, sop1, null, null, true);
+            se1.setSourceLocation(sourceLoc);
+
+            inExpr = se1;
+        }
+
+        SqlCompatSelectCoercionKind cardinalityCoercion = ann.cardinalityCoercion;
+        switch (cardinalityCoercion) {
             case SCALAR:
                 /*
-                 * (SELECT ...)
+                 * inExpr = (SELECT ...)
                  * ->
                  * STRICT_FIRST_ELEMENT
                  * (
                  *  SELECT VALUE v2[(LEN(v2)-1)*2]
-                 *  LET v2 = (SELECT VALUE v1 FROM (SELECT ...) v1 LIMIT 2)
+                 *  LET v2 = (SELECT VALUE v1 FROM (inExpr) v1 LIMIT 2)
                  * )
                  */
-                SourceLocation sourceLoc = inExpr.getSourceLocation();
 
                 /*
-                 * E1: SELECT VALUE v1 FROM (SELECT ...) v1 LIMIT 2
+                 * E1: SELECT VALUE v1 FROM (inExpr) v1 LIMIT 2
                  */
                 VarIdentifier v1 = context.newVariable();
                 VariableExpr v1Ref1 = new VariableExpr(v1);
@@ -549,7 +639,7 @@
                 CallExpr firstElemExpr = new CallExpr(new FunctionSignature(firstElemFun), firstElemArgs);
                 firstElemExpr.setSourceLocation(sourceLoc);
                 return firstElemExpr;
-            case COLLECTION:
+            case NONE:
                 // indicates that no transformation is necessary
                 return inExpr;
             default: