diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java
index c77774c..8eaf52a 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java
@@ -27,6 +27,7 @@
 import java.util.List;
 import java.util.Map;
 import java.util.Set;
+import java.util.function.Predicate;
 
 import org.apache.asterix.algebra.base.ILangExpressionToPlanTranslator;
 import org.apache.asterix.common.exceptions.CompilationException;
@@ -58,6 +59,7 @@
 import org.apache.asterix.lang.common.struct.QuantifiedPair;
 import org.apache.asterix.lang.common.struct.VarIdentifier;
 import org.apache.asterix.lang.common.util.FunctionUtil;
+import org.apache.asterix.lang.sqlpp.annotation.ExcludeFromSelectStarAnnotation;
 import org.apache.asterix.lang.sqlpp.clause.AbstractBinaryCorrelateClause;
 import org.apache.asterix.lang.sqlpp.clause.FromClause;
 import org.apache.asterix.lang.sqlpp.clause.FromTerm;
@@ -76,6 +78,7 @@
 import org.apache.asterix.lang.sqlpp.expression.WindowExpression;
 import org.apache.asterix.lang.sqlpp.optype.JoinType;
 import org.apache.asterix.lang.sqlpp.optype.SetOpType;
+import org.apache.asterix.lang.sqlpp.optype.UnnestType;
 import org.apache.asterix.lang.sqlpp.struct.SetOperationInput;
 import org.apache.asterix.lang.sqlpp.struct.SetOperationRight;
 import org.apache.asterix.lang.sqlpp.util.SqlppRewriteUtil;
@@ -494,6 +497,9 @@
                 context.setVar(joinClause.getRightVariable(), outerUnnestVar);
             }
             return new Pair<>(currentTopOp, null);
+        } else if (joinClause.getJoinType() == JoinType.RIGHTOUTER) {
+            // Fail if RIGHT OUTER JOIN was not rewritten into LEFT OUTER JOIN
+            throw new CompilationException(ErrorCode.ILLEGAL_RIGHT_OUTER_JOIN, joinClause.getSourceLocation());
         } else {
             throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_STATE, joinClause.getSourceLocation(),
                     String.valueOf(joinClause.getJoinType().toString()));
@@ -511,7 +517,7 @@
     public Pair<ILogicalOperator, LogicalVariable> visit(UnnestClause unnestClause,
             Mutable<ILogicalOperator> inputOpRef) throws CompilationException {
         return generateUnnestForBinaryCorrelateRightBranch(unnestClause, inputOpRef,
-                unnestClause.getJoinType() == JoinType.INNER);
+                unnestClause.getUnnestType() == UnnestType.INNER);
     }
 
     @Override
@@ -790,17 +796,22 @@
                 recordExprs.add(ifMissingOrNullExpr);
             } else if (projection.star()) {
                 if (selectBlock.hasGroupbyClause()) {
-                    getGroupBindings(selectBlock.getGroupbyClause(), fieldBindings, fieldNames);
+                    getGroupBindings(selectBlock.getGroupbyClause(), fieldBindings, fieldNames,
+                            SqlppExpressionToPlanTranslator::includeInSelectStar);
                     if (selectBlock.hasLetHavingClausesAfterGroupby()) {
-                        getLetBindings(selectBlock.getLetHavingListAfterGroupby(), fieldBindings, fieldNames);
+                        getLetBindings(selectBlock.getLetHavingListAfterGroupby(), fieldBindings, fieldNames,
+                                SqlppExpressionToPlanTranslator::includeInSelectStar);
                     }
                 } else if (selectBlock.hasFromClause()) {
-                    getFromBindings(selectBlock.getFromClause(), fieldBindings, fieldNames);
+                    getFromBindings(selectBlock.getFromClause(), fieldBindings, fieldNames,
+                            SqlppExpressionToPlanTranslator::includeInSelectStar);
                     if (selectBlock.hasLetWhereClauses()) {
-                        getLetBindings(selectBlock.getLetWhereList(), fieldBindings, fieldNames);
+                        getLetBindings(selectBlock.getLetWhereList(), fieldBindings, fieldNames,
+                                SqlppExpressionToPlanTranslator::includeInSelectStar);
                     }
                 } else if (selectBlock.hasLetWhereClauses()) {
-                    getLetBindings(selectBlock.getLetWhereList(), fieldBindings, fieldNames);
+                    getLetBindings(selectBlock.getLetWhereList(), fieldBindings, fieldNames,
+                            SqlppExpressionToPlanTranslator::includeInSelectStar);
                 }
             } else if (projection.hasName()) {
                 fieldBindings.add(getFieldBinding(projection, fieldNames));
@@ -824,21 +835,39 @@
         }
     }
 
+    private static boolean includeInSelectStar(VariableExpr varExpr) {
+        boolean excludeFromSelectStar =
+                varExpr.hasHints() && varExpr.getHints().contains(ExcludeFromSelectStarAnnotation.INSTANCE);
+        return !excludeFromSelectStar;
+    }
+
     // Generates all field bindings according to the from clause.
-    private void getFromBindings(FromClause fromClause, List<FieldBinding> outFieldBindings, Set<String> outFieldNames)
-            throws CompilationException {
+    private void getFromBindings(FromClause fromClause, List<FieldBinding> outFieldBindings, Set<String> outFieldNames,
+            Predicate<VariableExpr> varTest) throws CompilationException {
         for (FromTerm fromTerm : fromClause.getFromTerms()) {
-            outFieldBindings.add(getFieldBinding(fromTerm.getLeftVariable(), outFieldNames));
+            VariableExpr leftVar = fromTerm.getLeftVariable();
+            if (varTest == null || varTest.test(leftVar)) {
+                outFieldBindings.add(getFieldBinding(leftVar, outFieldNames));
+            }
             if (fromTerm.hasPositionalVariable()) {
-                outFieldBindings.add(getFieldBinding(fromTerm.getPositionalVariable(), outFieldNames));
+                VariableExpr leftPosVar = fromTerm.getPositionalVariable();
+                if (varTest == null || varTest.test(leftPosVar)) {
+                    outFieldBindings.add(getFieldBinding(leftPosVar, outFieldNames));
+                }
             }
             if (!fromTerm.hasCorrelateClauses()) {
                 continue;
             }
             for (AbstractBinaryCorrelateClause correlateClause : fromTerm.getCorrelateClauses()) {
-                outFieldBindings.add(getFieldBinding(correlateClause.getRightVariable(), outFieldNames));
+                VariableExpr rightVar = correlateClause.getRightVariable();
+                if (varTest == null || varTest.test(rightVar)) {
+                    outFieldBindings.add(getFieldBinding(rightVar, outFieldNames));
+                }
                 if (correlateClause.hasPositionalVariable()) {
-                    outFieldBindings.add(getFieldBinding(correlateClause.getPositionalVariable(), outFieldNames));
+                    VariableExpr rightPosVar = correlateClause.getPositionalVariable();
+                    if (varTest == null || varTest.test(rightPosVar)) {
+                        outFieldBindings.add(getFieldBinding(rightPosVar, outFieldNames));
+                    }
                 }
             }
         }
@@ -846,25 +875,32 @@
 
     // Generates all field bindings according to the from clause.
     private void getGroupBindings(GroupbyClause groupbyClause, List<FieldBinding> outFieldBindings,
-            Set<String> outFieldNames) throws CompilationException {
+            Set<String> outFieldNames, Predicate<VariableExpr> varTest) throws CompilationException {
         Set<VariableExpr> gbyKeyVars = new HashSet<>();
         List<GbyVariableExpressionPair> groupingSet = getSingleGroupingSet(groupbyClause);
         for (GbyVariableExpressionPair pair : groupingSet) {
             VariableExpr var = pair.getVar();
-            if (gbyKeyVars.add(var)) {
-                outFieldBindings.add(getFieldBinding(var, outFieldNames));
-            }
-        }
-        if (groupbyClause.hasDecorList()) {
-            for (GbyVariableExpressionPair pair : groupbyClause.getDecorPairList()) {
-                VariableExpr var = pair.getVar();
+            if (varTest == null || varTest.test(var)) {
                 if (gbyKeyVars.add(var)) {
                     outFieldBindings.add(getFieldBinding(var, outFieldNames));
                 }
             }
         }
+        if (groupbyClause.hasDecorList()) {
+            for (GbyVariableExpressionPair pair : groupbyClause.getDecorPairList()) {
+                VariableExpr var = pair.getVar();
+                if (varTest == null || varTest.test(var)) {
+                    if (gbyKeyVars.add(var)) {
+                        outFieldBindings.add(getFieldBinding(var, outFieldNames));
+                    }
+                }
+            }
+        }
         if (groupbyClause.hasGroupVar()) {
-            outFieldBindings.add(getFieldBinding(groupbyClause.getGroupVar(), outFieldNames));
+            VariableExpr var = groupbyClause.getGroupVar();
+            if (varTest == null || varTest.test(var)) {
+                outFieldBindings.add(getFieldBinding(var, outFieldNames));
+            }
         }
         if (groupbyClause.hasWithMap()) {
             // no WITH in SQLPP
@@ -875,11 +911,14 @@
 
     // Generates all field bindings according to the let clause.
     private void getLetBindings(List<AbstractClause> clauses, List<FieldBinding> outFieldBindings,
-            Set<String> outFieldNames) throws CompilationException {
+            Set<String> outFieldNames, Predicate<VariableExpr> varTest) throws CompilationException {
         for (AbstractClause clause : clauses) {
             if (clause.getClauseType() == ClauseType.LET_CLAUSE) {
                 LetClause letClause = (LetClause) clause;
-                outFieldBindings.add(getFieldBinding(letClause.getVarExpr(), outFieldNames));
+                VariableExpr letVar = letClause.getVarExpr();
+                if (varTest == null || varTest.test(letVar)) {
+                    outFieldBindings.add(getFieldBinding(letVar, outFieldNames));
+                }
             }
         }
     }
diff --git a/asterixdb/asterix-app/src/test/java/org/apache/asterix/test/runtime/SqlppRQGGroupingSetsIT.java b/asterixdb/asterix-app/src/test/java/org/apache/asterix/test/runtime/SqlppRQGGroupingSetsIT.java
index b1125a5..2ccab70 100644
--- a/asterixdb/asterix-app/src/test/java/org/apache/asterix/test/runtime/SqlppRQGGroupingSetsIT.java
+++ b/asterixdb/asterix-app/src/test/java/org/apache/asterix/test/runtime/SqlppRQGGroupingSetsIT.java
@@ -19,130 +19,43 @@
 
 package org.apache.asterix.test.runtime;
 
-import java.io.BufferedReader;
-import java.io.File;
-import java.io.IOException;
-import java.io.InputStream;
-import java.io.InputStreamReader;
-import java.io.PrintWriter;
-import java.nio.charset.StandardCharsets;
-import java.nio.file.Files;
-import java.nio.file.Path;
-import java.nio.file.Paths;
-import java.sql.Connection;
-import java.sql.DriverManager;
-import java.sql.JDBCType;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.ResultSetMetaData;
-import java.sql.SQLException;
-import java.sql.Statement;
-import java.sql.Types;
 import java.util.ArrayList;
 import java.util.Arrays;
 import java.util.Collection;
 import java.util.Collections;
-import java.util.LinkedHashMap;
 import java.util.LinkedHashSet;
 import java.util.List;
 import java.util.Random;
 import java.util.Set;
 import java.util.stream.Collectors;
 
-import org.apache.asterix.common.utils.Servlets;
-import org.apache.asterix.om.types.BuiltinType;
-import org.apache.asterix.om.types.IAType;
-import org.apache.asterix.test.common.ExtractedResult;
-import org.apache.asterix.test.common.ResultExtractor;
-import org.apache.asterix.test.common.TestExecutor;
-import org.apache.asterix.test.common.TestHelper;
-import org.apache.asterix.testframework.context.TestCaseContext;
-import org.apache.commons.io.FileUtils;
-import org.apache.commons.lang3.StringUtils;
 import org.apache.logging.log4j.LogManager;
 import org.apache.logging.log4j.Logger;
 import org.junit.AfterClass;
-import org.junit.Assert;
 import org.junit.BeforeClass;
 import org.junit.Test;
 import org.junit.runner.RunWith;
 import org.junit.runners.Parameterized;
 import org.junit.runners.Parameterized.Parameters;
-import org.testcontainers.containers.PostgreSQLContainer;
-
-import com.fasterxml.jackson.core.JsonProcessingException;
-import com.fasterxml.jackson.databind.JsonNode;
-import com.fasterxml.jackson.databind.ObjectMapper;
-import com.fasterxml.jackson.databind.ObjectReader;
-import com.fasterxml.jackson.databind.node.ArrayNode;
-import com.fasterxml.jackson.databind.node.ObjectNode;
-
-// Prerequisite:
-// setenv TESTCONTAINERS_RYUK_DISABLED true
 
 @RunWith(Parameterized.class)
-public class SqlppRQGGroupingSetsIT {
+public final class SqlppRQGGroupingSetsIT extends SqlppRQGTestBase {
 
-    private static final String CONF_PROPERTY_SEED = getConfigurationPropertyName("seed");
+    private static final Logger LOGGER = LogManager.getLogger(SqlppRQGGroupingSetsIT.class);
 
+    private static final String CONF_PROPERTY_SEED = getConfigurationPropertyName(SqlppRQGTestBase.class, "seed");
     private static final long CONF_PROPERTY_SEED_DEFAULT = System.currentTimeMillis();
 
-    private static final String CONF_PROPERTY_LIMIT = getConfigurationPropertyName("limit");
-
+    private static final String CONF_PROPERTY_LIMIT = getConfigurationPropertyName(SqlppRQGTestBase.class, "limit");
     private static final int CONF_PROPERTY_LIMIT_DEFAULT = 100;
 
-    private static final String TESTCONTAINERS_RYUK_DISABLED = "TESTCONTAINERS_RYUK_DISABLED";
-
-    private static final String TEST_CONFIG_FILE_NAME = "src/main/resources/cc.conf";
-
-    private static final String POSTGRES_IMAGE = "postgres:12.2";
-
-    private static final String TABLE_NAME = "tenk";
-
-    private static final Path TABLE_FILE = Paths.get("data", "tenk.tbl");
-
-    private static final char TABLE_FILE_COLUMN_SEPARATOR = '|';
-
-    private static final Path RESULT_OUTPUT_DIR = Paths.get("target", SqlppRQGGroupingSetsIT.class.getSimpleName());
-
     private static final int ROLLUP_ELEMENT_LIMIT = 2;
     private static final int CUBE_ELEMENT_LIMIT = 1;
     private static final int GROUPING_SETS_ELEMENT_LIMIT = 2;
     private static final int MULTI_ELEMENT_LIMIT = 2;
 
-    private static final String UNIQUE_1 = "unique1";
-    private static final String UNIQUE_2 = "unique2";
-    private static final String TWO = "two";
-    private static final String FOUR = "four";
-    private static final String TEN = "ten";
-    private static final String TWENTY = "twenty";
-    private static final String HUNDRED = "hundred";
-    private static final String THOUSAND = "thousand";
-    private static final String TWOTHOUSAND = "twothousand";
-    private static final String FIVETHOUS = "fivethous";
-    private static final String TENTHOUS = "tenthous";
-    private static final String ODD100 = "odd100";
-    private static final String EVEN100 = "even100";
-    private static final String STRINGU1 = "stringu1";
-    private static final String STRINGU2 = "stringu2";
-    private static final String STRING4 = "string4";
-
     private static final List<String> GROUPBY_COLUMNS = Arrays.asList(TWO, FOUR, TEN, TWENTY, HUNDRED, ODD100, EVEN100);
 
-    private static final LinkedHashMap<String, JDBCType> TABLE_SCHEMA = createTableSchema();
-
-    private static final ObjectReader JSON_NODE_READER = new ObjectMapper().readerFor(JsonNode.class);
-
-    private static final Logger LOGGER = LogManager.getLogger(SqlppRQGGroupingSetsIT.class);
-
-    private static TestExecutor testExecutor;
-
-    private static PostgreSQLContainer<?> postgres;
-
-    private static Connection conn;
-
-    private static Statement stmt;
-
     private final int testcaseId;
 
     private final String sqlQuery;
@@ -177,35 +90,19 @@
         this.groupByClause = groupByClause;
     }
 
+    @BeforeClass
+    public static void setUp() throws Exception {
+        setUpBeforeClass();
+    }
+
+    @AfterClass
+    public static void tearDown() throws Exception {
+        tearDownAfterClass();
+    }
+
     @Test
     public void test() throws Exception {
-        LOGGER.info(String.format("Starting testcase #%d: %s", testcaseId, groupByClause));
-
-        LOGGER.info("Running SQL");
-        LOGGER.info(sqlQuery);
-        stmt.execute(sqlQuery);
-        ArrayNode sqlResult;
-        try (ResultSet rs = stmt.getResultSet()) {
-            sqlResult = asJson(rs);
-        }
-
-        LOGGER.info("Running SQL++");
-        LOGGER.info(sqlppQuery);
-        ArrayNode sqlppResult;
-        try (InputStream resultStream = testExecutor.executeQueryService(sqlppQuery,
-                testExecutor.getEndpoint(Servlets.QUERY_SERVICE), TestCaseContext.OutputFormat.ADM)) {
-            sqlppResult = asJson(
-                    ResultExtractor.extract(resultStream, StandardCharsets.UTF_8, TestCaseContext.OutputFormat.ADM));
-        }
-
-        boolean eq = TestHelper.equalJson(sqlResult, sqlppResult, false);
-        if (!eq) {
-            File sqlResultFile = writeResult(sqlResult, "sql");
-            File sqlppResultFile = writeResult(sqlppResult, "sqlpp");
-
-            Assert.fail(String.format("Results do not match.\n%s\n%s", sqlResultFile.getCanonicalPath(),
-                    sqlppResultFile.getCanonicalPath()));
-        }
+        runTestCase(testcaseId, groupByClause, sqlQuery, sqlppQuery);
     }
 
     private static TestQuery generateQuery(int testcaseId, Random random) {
@@ -343,267 +240,6 @@
         sublist.add(s);
     }
 
-    private ArrayNode asJson(ExtractedResult aresult) throws IOException {
-        ArrayNode result = (ArrayNode) JSON_NODE_READER.createArrayNode();
-        try (BufferedReader reader =
-                new BufferedReader(new InputStreamReader(aresult.getResult(), StandardCharsets.UTF_8))) {
-            reader.lines().forEachOrdered(l -> {
-                try {
-                    result.add(JSON_NODE_READER.readTree(l));
-                } catch (JsonProcessingException e) {
-                    throw new RuntimeException(e);
-                }
-            });
-        }
-        return result;
-    }
-
-    private ArrayNode asJson(ResultSet rs) throws SQLException {
-        ResultSetMetaData rsmd = rs.getMetaData();
-        int rsColumnCount = rsmd.getColumnCount();
-        ArrayNode result = (ArrayNode) JSON_NODE_READER.createArrayNode();
-        while (rs.next()) {
-            ObjectNode row = (ObjectNode) JSON_NODE_READER.createObjectNode();
-            for (int i = 0; i < rsColumnCount; i++) {
-                int jdbcColumnIdx = i + 1;
-                String columnName = rsmd.getColumnName(jdbcColumnIdx);
-                switch (rsmd.getColumnType(jdbcColumnIdx)) {
-                    case Types.INTEGER:
-                        int intValue = rs.getInt(jdbcColumnIdx);
-                        if (rs.wasNull()) {
-                            row.putNull(columnName);
-                        } else {
-                            row.put(columnName, intValue);
-                        }
-                        break;
-                    case Types.BIGINT:
-                        long longValue = rs.getLong(jdbcColumnIdx);
-                        if (rs.wasNull()) {
-                            row.putNull(columnName);
-                        } else {
-                            row.put(columnName, longValue);
-                        }
-                        break;
-                    case Types.VARCHAR:
-                        String stringValue = rs.getString(jdbcColumnIdx);
-                        if (rs.wasNull()) {
-                            row.putNull(columnName);
-                        } else {
-                            row.put(columnName, stringValue);
-                        }
-                        break;
-                    default:
-                        throw new UnsupportedOperationException();
-                }
-            }
-            result.add(row);
-        }
-        return result;
-    }
-
-    private static void loadAsterixData() throws Exception {
-        String tableTypeName = TABLE_NAME + "Type";
-        String createTypeStmtText =
-                String.format("CREATE TYPE %s AS CLOSED { %s }", tableTypeName,
-                        TABLE_SCHEMA.entrySet().stream()
-                                .map(e -> e.getKey() + ':' + getAsterixType(e.getValue()).getTypeName())
-                                .collect(Collectors.joining(",")));
-
-        LOGGER.debug(createTypeStmtText);
-        testExecutor.executeSqlppUpdateOrDdl(createTypeStmtText, TestCaseContext.OutputFormat.ADM);
-
-        String createDatasetStmtText =
-                String.format("CREATE DATASET %s(%s) PRIMARY KEY %s", TABLE_NAME, tableTypeName, UNIQUE_2);
-        LOGGER.debug(createDatasetStmtText);
-        testExecutor.executeSqlppUpdateOrDdl(createDatasetStmtText, TestCaseContext.OutputFormat.ADM);
-
-        String loadStmtText =
-                String.format("LOAD DATASET %s USING localfs ((`path`=`%s`),(`format`=`%s`),(`delimiter`=`%s`))",
-                        TABLE_NAME, "asterix_nc1://" + TABLE_FILE, "delimited-text", "|");
-        LOGGER.debug(loadStmtText);
-        testExecutor.executeSqlppUpdateOrDdl(loadStmtText, TestCaseContext.OutputFormat.ADM);
-    }
-
-    private static void loadSQLData() throws SQLException, IOException {
-        String createTableStmtText = String.format("CREATE TEMPORARY TABLE %s (%s)", TABLE_NAME, TABLE_SCHEMA.entrySet()
-                .stream().map(e -> e.getKey() + ' ' + getSQLType(e.getValue())).collect(Collectors.joining(",")));
-
-        stmt.execute(createTableStmtText);
-
-        String insertStmtText = String.format("INSERT INTO %s VALUES (%s)", TABLE_NAME,
-                StringUtils.repeat("?", ",", TABLE_SCHEMA.size()));
-
-        try (PreparedStatement insertStmt = conn.prepareStatement(insertStmtText)) {
-            Files.lines(TABLE_FILE).forEachOrdered(line -> {
-                String[] values = StringUtils.split(line, TABLE_FILE_COLUMN_SEPARATOR);
-                try {
-                    insertStmt.clearParameters();
-                    int i = 0;
-                    for (JDBCType type : TABLE_SCHEMA.values()) {
-                        setColumnValue(insertStmt, i + 1, type, values[i]);
-                        i++;
-                    }
-                    insertStmt.addBatch();
-                } catch (SQLException e) {
-                    throw new RuntimeException(e);
-                }
-            });
-            insertStmt.executeBatch();
-        }
-    }
-
-    private static LinkedHashMap<String, JDBCType> createTableSchema() {
-        LinkedHashMap<String, JDBCType> schema = new LinkedHashMap<>();
-        schema.put(UNIQUE_1, JDBCType.INTEGER);
-        schema.put(UNIQUE_2, JDBCType.INTEGER);
-        schema.put(TWO, JDBCType.INTEGER);
-        schema.put(FOUR, JDBCType.INTEGER);
-        schema.put(TEN, JDBCType.INTEGER);
-        schema.put(TWENTY, JDBCType.INTEGER);
-        schema.put(HUNDRED, JDBCType.INTEGER);
-        schema.put(THOUSAND, JDBCType.INTEGER);
-        schema.put(TWOTHOUSAND, JDBCType.INTEGER);
-        schema.put(FIVETHOUS, JDBCType.INTEGER);
-        schema.put(TENTHOUS, JDBCType.INTEGER);
-        schema.put(ODD100, JDBCType.INTEGER);
-        schema.put(EVEN100, JDBCType.INTEGER);
-        schema.put(STRINGU1, JDBCType.VARCHAR);
-        schema.put(STRINGU2, JDBCType.VARCHAR);
-        schema.put(STRING4, JDBCType.VARCHAR);
-        return schema;
-    }
-
-    private static String getSQLType(JDBCType type) {
-        String suffix = "";
-        if (type == JDBCType.VARCHAR) {
-            suffix = "(256)";
-        }
-        return type.getName() + suffix;
-    }
-
-    private static IAType getAsterixType(JDBCType type) {
-        switch (type) {
-            case INTEGER:
-                return BuiltinType.AINT32;
-            case VARCHAR:
-                return BuiltinType.ASTRING;
-            default:
-                throw new UnsupportedOperationException();
-        }
-    }
-
-    private static void setColumnValue(PreparedStatement stmt, int jdbcParamIdx, JDBCType type, String value)
-            throws SQLException {
-        switch (type) {
-            case INTEGER:
-                stmt.setInt(jdbcParamIdx, Integer.parseInt(value));
-                break;
-            case VARCHAR:
-                stmt.setString(jdbcParamIdx, value);
-                break;
-            default:
-                throw new UnsupportedOperationException(type.getName());
-        }
-    }
-
-    private static <T> List<T> randomize(Collection<T> input, Random random) {
-        List<T> output = new ArrayList<>(input);
-        Collections.shuffle(output, random);
-        return output;
-    }
-
-    private static String getConfigurationPropertyName(String propertyName) {
-        return String.format("%s.%s", SqlppRQGGroupingSetsIT.class.getSimpleName(), propertyName);
-    }
-
-    private static long getLongConfigurationProperty(String propertyName, long defValue) {
-        String textValue = System.getProperty(propertyName);
-        if (textValue == null) {
-            return defValue;
-        }
-        try {
-            return Long.parseLong(textValue);
-        } catch (NumberFormatException e) {
-            LOGGER.warn(String.format("Cannot parse configuration property: %s. Will use default value: %d",
-                    propertyName, defValue));
-            return defValue;
-        }
-    }
-
-    private File writeResult(ArrayNode result, String resultKind) throws IOException {
-        String outFileName = String.format("%d.%s.txt", testcaseId, resultKind);
-        File outFile = new File(RESULT_OUTPUT_DIR.toFile(), outFileName);
-        try (PrintWriter pw = new PrintWriter(outFile, StandardCharsets.UTF_8.name())) {
-            pw.print("---");
-            pw.println(groupByClause);
-            for (int i = 0, ln = result.size(); i < ln; i++) {
-                pw.println(ResultExtractor.prettyPrint(result.get(i)));
-            }
-        }
-        return outFile;
-    }
-
-    @BeforeClass
-    public static void setUp() throws Exception {
-        startAsterix();
-        startPostgres();
-        FileUtils.forceMkdir(RESULT_OUTPUT_DIR.toFile());
-    }
-
-    @AfterClass
-    public static void tearDown() throws Exception {
-        stopPostgres();
-        stopAsterix();
-    }
-
-    private static void startAsterix() throws Exception {
-        testExecutor = new TestExecutor();
-        LangExecutionUtil.setUp(TEST_CONFIG_FILE_NAME, testExecutor);
-        loadAsterixData();
-    }
-
-    private static void stopAsterix() throws Exception {
-        LangExecutionUtil.tearDown();
-    }
-
-    private static void startPostgres() throws SQLException, IOException {
-        if (!Boolean.parseBoolean(System.getenv(TESTCONTAINERS_RYUK_DISABLED))) {
-            throw new IllegalStateException(
-                    String.format("Set environment variable %s=%s", TESTCONTAINERS_RYUK_DISABLED, true));
-        }
-        LOGGER.info("Starting Postgres");
-        postgres = new PostgreSQLContainer<>(POSTGRES_IMAGE);
-        postgres.start();
-        conn = DriverManager.getConnection(postgres.getJdbcUrl(), postgres.getUsername(), postgres.getPassword());
-        stmt = conn.createStatement();
-        loadSQLData();
-    }
-
-    private static void stopPostgres() {
-        LOGGER.info("Stopping Postgres");
-        if (stmt != null) {
-            try {
-                stmt.close();
-            } catch (Exception e) {
-                e.printStackTrace();
-            }
-        }
-        if (conn != null) {
-            try {
-                conn.close();
-            } catch (Exception e) {
-                e.printStackTrace();
-            }
-        }
-        if (postgres != null) {
-            try {
-                postgres.close();
-            } catch (Exception e) {
-                e.printStackTrace();
-            }
-        }
-    }
-
     private static class TestQuery {
         final String sqlQuery;
         final String sqlppQuery;
diff --git a/asterixdb/asterix-app/src/test/java/org/apache/asterix/test/runtime/SqlppRQGJoinsIT.java b/asterixdb/asterix-app/src/test/java/org/apache/asterix/test/runtime/SqlppRQGJoinsIT.java
new file mode 100644
index 0000000..6f16400
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/java/org/apache/asterix/test/runtime/SqlppRQGJoinsIT.java
@@ -0,0 +1,235 @@
+/*
+ * 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.
+ */
+
+package org.apache.asterix.test.runtime;
+
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.List;
+import java.util.function.IntUnaryOperator;
+
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
+import org.junit.Test;
+import org.junit.runner.RunWith;
+import org.junit.runners.Parameterized;
+
+@RunWith(Parameterized.class)
+public class SqlppRQGJoinsIT extends SqlppRQGTestBase {
+
+    private final int testcaseId;
+
+    private final String sqlQuery;
+
+    private final String sqlppQuery;
+
+    private final String desc;
+
+    static final String PROJECT_FIELD = "unique1";
+
+    static final String JOIN_FIELD = "unique2";
+
+    static final String FILTER_FIELD = JOIN_FIELD;
+
+    static final char[] SHAPES = new char[] { 'c', 's' }; //TODO: 'q'
+
+    @Parameterized.Parameters(name = "SqlppRQGJoinsIT {index}: {3}")
+    public static Collection<Object[]> tests() {
+        List<Object[]> testCases = new ArrayList<>();
+
+        IntUnaryOperator filterComputer = i -> 2 * (i + 1);
+
+        String[] allJoinKinds = new String[] { "INNER", "LEFT", "RIGHT" };
+        String[] queryJoinKinds = new String[3];
+        int id = 0;
+
+        for (String jk0 : allJoinKinds) {
+            queryJoinKinds[0] = jk0;
+            TestQuery q1 = generateQuery(queryJoinKinds, 1, filterComputer, SHAPES[0]);
+            testCases.add(new Object[] { id++, q1.sqlQuery, q1.sqlppQuery, q1.summary });
+
+            for (char s : SHAPES) {
+                for (String jk1 : allJoinKinds) {
+                    queryJoinKinds[1] = jk1;
+                    TestQuery q2 = generateQuery(queryJoinKinds, 2, filterComputer, s);
+                    testCases.add(new Object[] { id++, q2.sqlQuery, q2.sqlppQuery, q2.summary });
+
+                    for (String jk2 : allJoinKinds) {
+                        queryJoinKinds[2] = jk2;
+                        TestQuery q3 = generateQuery(queryJoinKinds, 3, filterComputer, s);
+                        testCases.add(new Object[] { id++, q3.sqlQuery, q3.sqlppQuery, q3.summary });
+                    }
+                }
+            }
+        }
+
+        return testCases;
+    }
+
+    private static TestQuery generateQuery(String[] joinKinds, int joinKindsSize, IntUnaryOperator filterComputer,
+            char shape) {
+        int tCount = joinKindsSize + 1;
+        List<String> tDefs = new ArrayList<>(tCount);
+        for (int i = 0; i < tCount; i++) {
+            int filterValue = filterComputer.applyAsInt(i);
+            String tDef = String.format("SELECT %s, %s FROM %s WHERE %s < %d", PROJECT_FIELD, JOIN_FIELD, TABLE_NAME,
+                    FILTER_FIELD, filterValue);
+            tDefs.add(tDef);
+        }
+
+        StringBuilder joinCondBuilderSql = new StringBuilder(128);
+        StringBuilder joinCondBuilderSqlpp = new StringBuilder(128);
+        StringBuilder selectClauseSql = new StringBuilder(128);
+        StringBuilder selectClauseSqlpp = new StringBuilder(128);
+        StringBuilder fromClauseSql = new StringBuilder(128);
+        StringBuilder fromClauseSqlpp = new StringBuilder(128);
+        StringBuilder orderbyClauseSql = new StringBuilder(128);
+        StringBuilder orderbyClauseSqlpp = new StringBuilder(128);
+        StringBuilder summary = new StringBuilder(128);
+
+        String fieldExprFormat = "%s.%s";
+
+        for (int i = 0; i < tCount; i++) {
+            String tThis = "t" + i;
+            String clause = i == 0 ? "FROM" : joinKinds[i - 1] + " JOIN";
+
+            String joinConditionSql, joinConditionSqlpp;
+            if (i == 0) {
+                joinConditionSqlpp = joinConditionSql = "";
+            } else {
+                String joinFieldThisExprSql = String.format(fieldExprFormat, tThis, JOIN_FIELD);
+                String joinFieldThisExprSqlpp = joinFieldThisExprSql; //missing2Null(joinFieldThisExprSql);
+                String joinConditionFormat;
+                switch (shape) {
+                    case 'c':
+                        String tPrev = "t" + (i - 1);
+                        String joinFieldPrevExprSql = String.format(fieldExprFormat, tPrev, JOIN_FIELD);
+                        String joinFieldPrevExprSqlpp = joinFieldPrevExprSql; // missing2Null(joinFieldPrevExprSql);
+                        joinConditionFormat = "ON %s = %s ";
+                        joinConditionSql =
+                                String.format(joinConditionFormat, joinFieldPrevExprSql, joinFieldThisExprSql);
+                        joinConditionSqlpp =
+                                String.format(joinConditionFormat, joinFieldPrevExprSqlpp, joinFieldThisExprSqlpp);
+                        break;
+                    case 's':
+                        String t0 = "t" + 0;
+                        String joinField0ExprSql = String.format(fieldExprFormat, t0, JOIN_FIELD);
+                        String joinField0ExprSqlpp = joinField0ExprSql; // missing2Null(joinField0ExprSql);
+                        joinConditionFormat = "ON %s = %s ";
+                        joinConditionSql = String.format(joinConditionFormat, joinField0ExprSql, joinFieldThisExprSql);
+                        joinConditionSqlpp =
+                                String.format(joinConditionFormat, joinField0ExprSqlpp, joinFieldThisExprSqlpp);
+                        break;
+                    case 'q':
+                        joinCondBuilderSql.setLength(0);
+                        joinCondBuilderSqlpp.setLength(0);
+                        joinConditionFormat = "%s %s = %s ";
+                        for (int j = 0; j < i; j++) {
+                            String kwj = j == 0 ? "ON" : "AND";
+                            String tj = "t" + j;
+                            String joinFieldJExprSql = String.format(fieldExprFormat, tj, JOIN_FIELD);
+                            String joinFieldJExprSqlpp = joinFieldJExprSql; //missing2Null(joinFieldJExprSql);
+                            String joinCondPartSql =
+                                    String.format(joinConditionFormat, kwj, joinFieldJExprSql, joinFieldThisExprSql);
+                            String joinCondPartSqlpp = String.format(joinConditionFormat, kwj, joinFieldJExprSqlpp,
+                                    joinFieldThisExprSqlpp);
+                            joinCondBuilderSql.append(joinCondPartSql);
+                            joinCondBuilderSqlpp.append(joinCondPartSqlpp);
+                        }
+                        joinConditionSql = joinCondBuilderSql.toString();
+                        joinConditionSqlpp = joinCondBuilderSqlpp.toString();
+                        break;
+                    default:
+                        throw new IllegalArgumentException(String.valueOf(shape));
+                }
+            }
+
+            String fromClauseFormat = "%s (%s) %s %s";
+            fromClauseSql.append(String.format(fromClauseFormat, clause, tDefs.get(i), tThis, joinConditionSql));
+            fromClauseSqlpp.append(String.format(fromClauseFormat, clause, tDefs.get(i), tThis, joinConditionSqlpp));
+
+            if (i > 0) {
+                selectClauseSql.append(", ");
+                selectClauseSqlpp.append(", ");
+                orderbyClauseSql.append(", ");
+                orderbyClauseSqlpp.append(", ");
+                if (i > 1) {
+                    summary.append(',');
+                }
+                summary.append(joinKinds[i - 1]);
+            }
+            String projectFieldExprSql = String.format(fieldExprFormat, tThis, PROJECT_FIELD);
+            String projectFieldExprSqlpp = missing2Null(projectFieldExprSql);
+            String projectFieldAlias = String.format("%s_%s", tThis, PROJECT_FIELD);
+            String projectFormat = "%s AS %s";
+            selectClauseSql.append(String.format(projectFormat, projectFieldExprSql, projectFieldAlias));
+            selectClauseSqlpp.append(String.format(projectFormat, projectFieldExprSqlpp, projectFieldAlias));
+            orderbyClauseSql.append(String.format("%s NULLS FIRST", projectFieldAlias));
+            orderbyClauseSqlpp.append(projectFieldAlias);
+        }
+
+        if (tCount > 1) {
+            summary.append(';').append(shape);
+        }
+
+        String queryFormat = "SELECT %s %sORDER BY %s";
+        String sqlQuery = String.format(queryFormat, selectClauseSql, fromClauseSql, orderbyClauseSql);
+        String sqlppQuery = String.format(queryFormat, selectClauseSqlpp, fromClauseSqlpp, orderbyClauseSqlpp);
+
+        return new TestQuery(sqlQuery, sqlppQuery, summary.toString());
+    }
+
+    private static String missing2Null(String expr) {
+        return String.format("if_missing(%s, null)", expr);
+    }
+
+    public SqlppRQGJoinsIT(int testcaseId, String sqlQuery, String sqlppQuery, String desc) {
+        this.testcaseId = testcaseId;
+        this.sqlQuery = sqlQuery;
+        this.sqlppQuery = sqlppQuery;
+        this.desc = desc;
+    }
+
+    @BeforeClass
+    public static void setUp() throws Exception {
+        setUpBeforeClass();
+    }
+
+    @AfterClass
+    public static void tearDown() throws Exception {
+        tearDownAfterClass();
+    }
+
+    @Test
+    public void test() throws Exception {
+        runTestCase(testcaseId, desc, sqlQuery, sqlppQuery);
+    }
+
+    private static class TestQuery {
+        final String sqlQuery;
+        final String sqlppQuery;
+        final String summary;
+
+        TestQuery(String sqlQuery, String sqlppQuery, String summary) {
+            this.sqlQuery = sqlQuery;
+            this.sqlppQuery = sqlppQuery;
+            this.summary = summary;
+        }
+    }
+}
diff --git a/asterixdb/asterix-app/src/test/java/org/apache/asterix/test/runtime/SqlppRQGTestBase.java b/asterixdb/asterix-app/src/test/java/org/apache/asterix/test/runtime/SqlppRQGTestBase.java
new file mode 100644
index 0000000..44af727
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/java/org/apache/asterix/test/runtime/SqlppRQGTestBase.java
@@ -0,0 +1,416 @@
+/*
+ * 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.
+ */
+
+package org.apache.asterix.test.runtime;
+
+import java.io.BufferedReader;
+import java.io.File;
+import java.io.IOException;
+import java.io.InputStream;
+import java.io.InputStreamReader;
+import java.io.PrintWriter;
+import java.nio.charset.StandardCharsets;
+import java.nio.file.Files;
+import java.nio.file.Path;
+import java.nio.file.Paths;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.JDBCType;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.sql.Types;
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.Collections;
+import java.util.LinkedHashMap;
+import java.util.List;
+import java.util.Random;
+import java.util.stream.Collectors;
+
+import org.apache.asterix.common.utils.Servlets;
+import org.apache.asterix.om.types.BuiltinType;
+import org.apache.asterix.om.types.IAType;
+import org.apache.asterix.test.common.ExtractedResult;
+import org.apache.asterix.test.common.ResultExtractor;
+import org.apache.asterix.test.common.TestExecutor;
+import org.apache.asterix.test.common.TestHelper;
+import org.apache.asterix.testframework.context.TestCaseContext;
+import org.apache.commons.io.FileUtils;
+import org.apache.commons.lang3.StringUtils;
+import org.apache.logging.log4j.LogManager;
+import org.apache.logging.log4j.Logger;
+import org.junit.Assert;
+import org.testcontainers.containers.PostgreSQLContainer;
+
+import com.fasterxml.jackson.core.JsonProcessingException;
+import com.fasterxml.jackson.databind.JsonNode;
+import com.fasterxml.jackson.databind.ObjectMapper;
+import com.fasterxml.jackson.databind.ObjectReader;
+import com.fasterxml.jackson.databind.node.ArrayNode;
+import com.fasterxml.jackson.databind.node.ObjectNode;
+
+// Prerequisite:
+// setenv TESTCONTAINERS_RYUK_DISABLED true
+
+public abstract class SqlppRQGTestBase {
+
+    private static final Logger LOGGER = LogManager.getLogger(SqlppRQGTestBase.class);
+
+    protected static final String TESTCONTAINERS_RYUK_DISABLED = "TESTCONTAINERS_RYUK_DISABLED";
+
+    protected static final String TEST_CONFIG_FILE_NAME = "src/main/resources/cc.conf";
+
+    protected static final String POSTGRES_IMAGE = "postgres:12.2";
+
+    protected static final String TABLE_NAME = "tenk";
+
+    protected static final Path TABLE_FILE = Paths.get("data", "tenk.tbl");
+
+    protected static final char TABLE_FILE_COLUMN_SEPARATOR = '|';
+
+    protected static final Path RESULT_OUTPUT_DIR = Paths.get("target", SqlppRQGGroupingSetsIT.class.getSimpleName());
+
+    protected static final String UNIQUE_1 = "unique1";
+    protected static final String UNIQUE_2 = "unique2";
+    protected static final String TWO = "two";
+    protected static final String FOUR = "four";
+    protected static final String TEN = "ten";
+    protected static final String TWENTY = "twenty";
+    protected static final String HUNDRED = "hundred";
+    protected static final String THOUSAND = "thousand";
+    protected static final String TWOTHOUSAND = "twothousand";
+    protected static final String FIVETHOUS = "fivethous";
+    protected static final String TENTHOUS = "tenthous";
+    protected static final String ODD100 = "odd100";
+    protected static final String EVEN100 = "even100";
+    protected static final String STRINGU1 = "stringu1";
+    protected static final String STRINGU2 = "stringu2";
+    protected static final String STRING4 = "string4";
+
+    protected static final LinkedHashMap<String, JDBCType> TABLE_SCHEMA = createTableSchema();
+
+    protected static final ObjectReader JSON_NODE_READER = new ObjectMapper().readerFor(JsonNode.class);
+
+    protected static TestExecutor testExecutor;
+
+    protected static PostgreSQLContainer<?> postgres;
+
+    protected static Connection conn;
+
+    protected static Statement stmt;
+
+    public static void setUpBeforeClass() throws Exception {
+        startAsterix();
+        startPostgres();
+        FileUtils.forceMkdir(RESULT_OUTPUT_DIR.toFile());
+    }
+
+    public static void tearDownAfterClass() throws Exception {
+        stopPostgres();
+        stopAsterix();
+    }
+
+    protected ArrayNode asJson(ExtractedResult aresult) throws IOException {
+        ArrayNode result = (ArrayNode) JSON_NODE_READER.createArrayNode();
+        try (BufferedReader reader =
+                new BufferedReader(new InputStreamReader(aresult.getResult(), StandardCharsets.UTF_8))) {
+            reader.lines().forEachOrdered(l -> {
+                try {
+                    result.add(JSON_NODE_READER.readTree(l));
+                } catch (JsonProcessingException e) {
+                    throw new RuntimeException(e);
+                }
+            });
+        }
+        return result;
+    }
+
+    protected void runTestCase(int testcaseId, String testcaseDescription, String sqlQuery, String sqlppQuery)
+            throws Exception {
+        LOGGER.info(String.format("Starting testcase #%d: %s", testcaseId, testcaseDescription));
+
+        LOGGER.info("Running SQL");
+        LOGGER.info(sqlQuery);
+        stmt.execute(sqlQuery);
+        ArrayNode sqlResult;
+        try (ResultSet rs = stmt.getResultSet()) {
+            sqlResult = asJson(rs);
+        }
+
+        LOGGER.info("Running SQL++");
+        LOGGER.info(sqlppQuery);
+        ArrayNode sqlppResult;
+        try (InputStream resultStream = testExecutor.executeQueryService(sqlppQuery,
+                testExecutor.getEndpoint(Servlets.QUERY_SERVICE), TestCaseContext.OutputFormat.ADM)) {
+            sqlppResult = asJson(
+                    ResultExtractor.extract(resultStream, StandardCharsets.UTF_8, TestCaseContext.OutputFormat.ADM));
+        }
+
+        boolean eq = TestHelper.equalJson(sqlResult, sqlppResult, false);
+
+        File sqlResultFile = writeResult(sqlResult, testcaseId, "sql", testcaseDescription);
+        File sqlppResultFile = writeResult(sqlppResult, testcaseId, "sqlpp", testcaseDescription);
+
+        if (!eq) {
+            /*
+            File sqlResultFile = writeResult(sqlResult, testcaseId, "sql", testcaseDescription);
+            File sqlppResultFile = writeResult(sqlppResult, testcaseId, "sqlpp", testcaseDescription);
+            */
+
+            Assert.fail(String.format("Results do not match.\n%s\n%s", sqlResultFile.getCanonicalPath(),
+                    sqlppResultFile.getCanonicalPath()));
+        }
+    }
+
+    protected ArrayNode asJson(ResultSet rs) throws SQLException {
+        ResultSetMetaData rsmd = rs.getMetaData();
+        int rsColumnCount = rsmd.getColumnCount();
+        ArrayNode result = (ArrayNode) JSON_NODE_READER.createArrayNode();
+        while (rs.next()) {
+            ObjectNode row = (ObjectNode) JSON_NODE_READER.createObjectNode();
+            for (int i = 0; i < rsColumnCount; i++) {
+                int jdbcColumnIdx = i + 1;
+                String columnName = rsmd.getColumnName(jdbcColumnIdx);
+                switch (rsmd.getColumnType(jdbcColumnIdx)) {
+                    case Types.INTEGER:
+                        int intValue = rs.getInt(jdbcColumnIdx);
+                        if (rs.wasNull()) {
+                            row.putNull(columnName);
+                        } else {
+                            row.put(columnName, intValue);
+                        }
+                        break;
+                    case Types.BIGINT:
+                        long longValue = rs.getLong(jdbcColumnIdx);
+                        if (rs.wasNull()) {
+                            row.putNull(columnName);
+                        } else {
+                            row.put(columnName, longValue);
+                        }
+                        break;
+                    case Types.VARCHAR:
+                        String stringValue = rs.getString(jdbcColumnIdx);
+                        if (rs.wasNull()) {
+                            row.putNull(columnName);
+                        } else {
+                            row.put(columnName, stringValue);
+                        }
+                        break;
+                    default:
+                        throw new UnsupportedOperationException();
+                }
+            }
+            result.add(row);
+        }
+        return result;
+    }
+
+    protected static void loadAsterixData() throws Exception {
+        String tableTypeName = TABLE_NAME + "Type";
+        String createTypeStmtText =
+                String.format("CREATE TYPE %s AS CLOSED { %s }", tableTypeName,
+                        TABLE_SCHEMA.entrySet().stream()
+                                .map(e -> e.getKey() + ':' + getAsterixType(e.getValue()).getTypeName())
+                                .collect(Collectors.joining(",")));
+
+        LOGGER.debug(createTypeStmtText);
+        testExecutor.executeSqlppUpdateOrDdl(createTypeStmtText, TestCaseContext.OutputFormat.ADM);
+
+        String createDatasetStmtText =
+                String.format("CREATE DATASET %s(%s) PRIMARY KEY %s", TABLE_NAME, tableTypeName, UNIQUE_2);
+        LOGGER.debug(createDatasetStmtText);
+        testExecutor.executeSqlppUpdateOrDdl(createDatasetStmtText, TestCaseContext.OutputFormat.ADM);
+
+        String loadStmtText =
+                String.format("LOAD DATASET %s USING localfs ((`path`=`%s`),(`format`=`%s`),(`delimiter`=`%s`))",
+                        TABLE_NAME, "asterix_nc1://" + TABLE_FILE, "delimited-text", "|");
+        LOGGER.debug(loadStmtText);
+        testExecutor.executeSqlppUpdateOrDdl(loadStmtText, TestCaseContext.OutputFormat.ADM);
+    }
+
+    protected static void loadSQLData() throws SQLException, IOException {
+        String createTableStmtText = String.format("CREATE TEMPORARY TABLE %s (%s)", TABLE_NAME, TABLE_SCHEMA.entrySet()
+                .stream().map(e -> e.getKey() + ' ' + getSQLType(e.getValue())).collect(Collectors.joining(",")));
+
+        stmt.execute(createTableStmtText);
+
+        String insertStmtText = String.format("INSERT INTO %s VALUES (%s)", TABLE_NAME,
+                StringUtils.repeat("?", ",", TABLE_SCHEMA.size()));
+
+        try (PreparedStatement insertStmt = conn.prepareStatement(insertStmtText)) {
+            Files.lines(TABLE_FILE).forEachOrdered(line -> {
+                String[] values = StringUtils.split(line, TABLE_FILE_COLUMN_SEPARATOR);
+                try {
+                    insertStmt.clearParameters();
+                    int i = 0;
+                    for (JDBCType type : TABLE_SCHEMA.values()) {
+                        setColumnValue(insertStmt, i + 1, type, values[i]);
+                        i++;
+                    }
+                    insertStmt.addBatch();
+                } catch (SQLException e) {
+                    throw new RuntimeException(e);
+                }
+            });
+            insertStmt.executeBatch();
+        }
+    }
+
+    protected static LinkedHashMap<String, JDBCType> createTableSchema() {
+        LinkedHashMap<String, JDBCType> schema = new LinkedHashMap<>();
+        schema.put(UNIQUE_1, JDBCType.INTEGER);
+        schema.put(UNIQUE_2, JDBCType.INTEGER);
+        schema.put(TWO, JDBCType.INTEGER);
+        schema.put(FOUR, JDBCType.INTEGER);
+        schema.put(TEN, JDBCType.INTEGER);
+        schema.put(TWENTY, JDBCType.INTEGER);
+        schema.put(HUNDRED, JDBCType.INTEGER);
+        schema.put(THOUSAND, JDBCType.INTEGER);
+        schema.put(TWOTHOUSAND, JDBCType.INTEGER);
+        schema.put(FIVETHOUS, JDBCType.INTEGER);
+        schema.put(TENTHOUS, JDBCType.INTEGER);
+        schema.put(ODD100, JDBCType.INTEGER);
+        schema.put(EVEN100, JDBCType.INTEGER);
+        schema.put(STRINGU1, JDBCType.VARCHAR);
+        schema.put(STRINGU2, JDBCType.VARCHAR);
+        schema.put(STRING4, JDBCType.VARCHAR);
+        return schema;
+    }
+
+    protected static String getSQLType(JDBCType type) {
+        String suffix = "";
+        if (type == JDBCType.VARCHAR) {
+            suffix = "(256)";
+        }
+        return type.getName() + suffix;
+    }
+
+    protected static IAType getAsterixType(JDBCType type) {
+        switch (type) {
+            case INTEGER:
+                return BuiltinType.AINT32;
+            case VARCHAR:
+                return BuiltinType.ASTRING;
+            default:
+                throw new UnsupportedOperationException();
+        }
+    }
+
+    protected static void setColumnValue(PreparedStatement stmt, int jdbcParamIdx, JDBCType type, String value)
+            throws SQLException {
+        switch (type) {
+            case INTEGER:
+                stmt.setInt(jdbcParamIdx, Integer.parseInt(value));
+                break;
+            case VARCHAR:
+                stmt.setString(jdbcParamIdx, value);
+                break;
+            default:
+                throw new UnsupportedOperationException(type.getName());
+        }
+    }
+
+    protected File writeResult(ArrayNode result, int testcaseId, String resultKind, String comment) throws IOException {
+        String outFileName = String.format("%d.%s.txt", testcaseId, resultKind);
+        File outFile = new File(RESULT_OUTPUT_DIR.toFile(), outFileName);
+        try (PrintWriter pw = new PrintWriter(outFile, StandardCharsets.UTF_8.name())) {
+            pw.print("---");
+            pw.println(comment);
+            for (int i = 0, ln = result.size(); i < ln; i++) {
+                pw.println(ResultExtractor.prettyPrint(result.get(i)));
+            }
+        }
+        return outFile;
+    }
+
+    protected static <T> List<T> randomize(Collection<T> input, Random random) {
+        List<T> output = new ArrayList<>(input);
+        Collections.shuffle(output, random);
+        return output;
+    }
+
+    protected static String getConfigurationPropertyName(Class<?> testClass, String propertyName) {
+        return String.format("%s.%s", testClass.getSimpleName(), propertyName);
+    }
+
+    protected static long getLongConfigurationProperty(String propertyName, long defValue) {
+        String textValue = System.getProperty(propertyName);
+        if (textValue == null) {
+            return defValue;
+        }
+        try {
+            return Long.parseLong(textValue);
+        } catch (NumberFormatException e) {
+            LOGGER.warn(String.format("Cannot parse configuration property: %s. Will use default value: %d",
+                    propertyName, defValue));
+            return defValue;
+        }
+    }
+
+    protected static void startAsterix() throws Exception {
+        testExecutor = new TestExecutor();
+        LangExecutionUtil.setUp(TEST_CONFIG_FILE_NAME, testExecutor);
+        loadAsterixData();
+    }
+
+    protected static void stopAsterix() throws Exception {
+        LangExecutionUtil.tearDown();
+    }
+
+    protected static void startPostgres() throws SQLException, IOException {
+        if (!Boolean.parseBoolean(System.getenv(TESTCONTAINERS_RYUK_DISABLED))) {
+            throw new IllegalStateException(
+                    String.format("Set environment variable %s=%s", TESTCONTAINERS_RYUK_DISABLED, true));
+        }
+        LOGGER.info("Starting Postgres");
+        postgres = new PostgreSQLContainer<>(POSTGRES_IMAGE);
+        postgres.start();
+        conn = DriverManager.getConnection(postgres.getJdbcUrl(), postgres.getUsername(), postgres.getPassword());
+        stmt = conn.createStatement();
+        loadSQLData();
+    }
+
+    protected static void stopPostgres() {
+        LOGGER.info("Stopping Postgres");
+        if (stmt != null) {
+            try {
+                stmt.close();
+            } catch (Exception e) {
+                e.printStackTrace();
+            }
+        }
+        if (conn != null) {
+            try {
+                conn.close();
+            } catch (Exception e) {
+                e.printStackTrace();
+            }
+        }
+        if (postgres != null) {
+            try {
+                postgres.close();
+            } catch (Exception e) {
+                e.printStackTrace();
+            }
+        }
+    }
+}
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rightouterjoin/roj-01-core.1.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rightouterjoin/roj-01-core.1.sqlpp
new file mode 100644
index 0000000..a766ad0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rightouterjoin/roj-01-core.1.sqlpp
@@ -0,0 +1,60 @@
+/*
+ * 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.
+ */
+
+/*
+ * Test basic RIGHT OUTER JOIN cases
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+SELECT
+  if_missing(t0.unique1, null) AS t0_unique1,
+  if_missing(t1.unique1, null) AS t1_unique1
+FROM (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 2
+) t0
+RIGHT JOIN (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 4
+) t1 ON t0.unique2 = t1.unique2
+ORDER BY t0_unique1, t1_unique1;
+
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rightouterjoin/roj-01-core.2.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rightouterjoin/roj-01-core.2.sqlpp
new file mode 100644
index 0000000..03ca3cd
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rightouterjoin/roj-01-core.2.sqlpp
@@ -0,0 +1,63 @@
+/*
+ * 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.
+ */
+
+/*
+ * Test basic RIGHT OUTER JOIN cases
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+SELECT
+  if_missing(t0.unique1, null) AS t0_unique1,
+  if_missing(t1.unique1, null) AS t1_unique1,
+  if_missing(t2.unique1, null) AS t2_unique1
+FROM (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 2
+) t0
+RIGHT JOIN (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 4
+) t1 ON t0.unique2 = t1.unique2
+RIGHT JOIN (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 6
+) t2 ON t1.unique2 = t2.unique2
+ORDER BY t0_unique1, t1_unique1, t2_unique1;
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/rightouterjoin/roj-01-core.1.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/rightouterjoin/roj-01-core.1.plan
new file mode 100644
index 0000000..274b951
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/rightouterjoin/roj-01-core.1.plan
@@ -0,0 +1,28 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$#1(ASC), $#2(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$#1(ASC), $#2(ASC)]  |PARTITIONED|
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ASSIGN  |PARTITIONED|
+                  -- STREAM_PROJECT  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- HYBRID_HASH_JOIN [$$91][$$92]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ASSIGN  |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_PROJECT  |PARTITIONED|
+                            -- ASSIGN  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- BTREE_SEARCH  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- ASSIGN  |PARTITIONED|
+                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/rightouterjoin/roj-01-core.2.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/rightouterjoin/roj-01-core.2.plan
new file mode 100644
index 0000000..2061d8d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/rightouterjoin/roj-01-core.2.plan
@@ -0,0 +1,44 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$#1(ASC), $#2(ASC), $#3(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$#1(ASC), $#2(ASC), $#3(ASC)]  |PARTITIONED|
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ASSIGN  |PARTITIONED|
+                  -- STREAM_PROJECT  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- HYBRID_HASH_JOIN [$$154][$$155]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ASSIGN  |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_PROJECT  |PARTITIONED|
+                            -- ASSIGN  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- HYBRID_HASH_JOIN [$$155][$$156]  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ASSIGN  |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_PROJECT  |PARTITIONED|
+                                            -- ASSIGN  |PARTITIONED|
+                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                -- BTREE_SEARCH  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- ASSIGN  |PARTITIONED|
+                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-01-core/roj-01-core.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-01-core/roj-01-core.1.ddl.sqlpp
new file mode 100644
index 0000000..97e2a5e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-01-core/roj-01-core.1.ddl.sqlpp
@@ -0,0 +1,48 @@
+/*
+ * 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.
+ */
+
+/*
+ * Test basic RIGHT OUTER JOIN cases
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-01-core/roj-01-core.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-01-core/roj-01-core.2.update.sqlpp
new file mode 100644
index 0000000..2d7e768
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-01-core/roj-01-core.2.update.sqlpp
@@ -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 test;
+
+load  dataset tenk using localfs ((`path`=`asterix_nc1://data/tenk.tbl`),(`format`=`delimited-text`),(`delimiter`=`|`));
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-01-core/roj-01-core.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-01-core/roj-01-core.3.query.sqlpp
new file mode 100644
index 0000000..04c6b89
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-01-core/roj-01-core.3.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.
+ */
+
+/* RIGHT;c */
+
+use test;
+
+SELECT
+  if_missing(t0.unique1, null) AS t0_unique1,
+  if_missing(t1.unique1, null) AS t1_unique1
+FROM (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 2
+) t0
+RIGHT JOIN (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 4
+) t1 ON t0.unique2 = t1.unique2
+ORDER BY t0_unique1, t1_unique1;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-01-core/roj-01-core.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-01-core/roj-01-core.4.query.sqlpp
new file mode 100644
index 0000000..c74d919
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-01-core/roj-01-core.4.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.
+ */
+
+/* RIGHT,RIGHT;c */
+
+use test;
+
+SELECT
+  if_missing(t0.unique1, null) AS t0_unique1,
+  if_missing(t1.unique1, null) AS t1_unique1,
+  if_missing(t2.unique1, null) AS t2_unique1
+FROM (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 2
+) t0
+RIGHT JOIN (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 4
+) t1 ON t0.unique2 = t1.unique2
+RIGHT JOIN (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 6
+) t2 ON t1.unique2 = t2.unique2
+ORDER BY t0_unique1, t1_unique1, t2_unique1;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-01-core/roj-01-core.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-01-core/roj-01-core.5.query.sqlpp
new file mode 100644
index 0000000..75be058
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-01-core/roj-01-core.5.query.sqlpp
@@ -0,0 +1,42 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/* RIGHT,RIGHT,RIGHT;c */
+
+use test;
+
+SELECT
+  if_missing(t0.unique1, null) AS t0_unique1,
+  if_missing(t1.unique1, null) AS t1_unique1,
+  if_missing(t2.unique1, null) AS t2_unique1,
+  if_missing(t3.unique1, null) AS t3_unique1
+FROM (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 2
+) t0
+RIGHT JOIN (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 4
+) t1 ON t0.unique2 = t1.unique2
+RIGHT JOIN (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 6
+) t2 ON t1.unique2 = t2.unique2
+RIGHT JOIN (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 8
+) t3 ON t2.unique2 = t3.unique2
+ORDER BY t0_unique1, t1_unique1, t2_unique1, t3_unique1;
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-01-core/roj-01-core.6.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-01-core/roj-01-core.6.query.sqlpp
new file mode 100644
index 0000000..832a8ae
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-01-core/roj-01-core.6.query.sqlpp
@@ -0,0 +1,40 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/* LEFT,RIGHT;s */
+
+use test;
+
+SELECT
+  if_missing(t0.unique1, null) AS t0_unique1,
+  if_missing(t1.unique1, null) AS t1_unique1,
+  if_missing(t2.unique1, null) AS t2_unique1
+FROM (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 2
+) t0
+LEFT JOIN (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 4
+) t1 ON t0.unique2 = t1.unique2
+RIGHT JOIN (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 6
+) t2 ON t0.unique2 = t2.unique2
+ORDER BY t0_unique1, t1_unique1, t2_unique1;
+
+
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-01-core/roj-01-core.7.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-01-core/roj-01-core.7.query.sqlpp
new file mode 100644
index 0000000..d7ab2a6
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-01-core/roj-01-core.7.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.
+ */
+
+/* RIGHT,LEFT;s */
+
+use test;
+
+SELECT
+  if_missing(t0.unique1, null) AS t0_unique1,
+  if_missing(t1.unique1, null) AS t1_unique1,
+  if_missing(t2.unique1, null) AS t2_unique1
+FROM (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 2
+) t0
+RIGHT JOIN (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 4
+) t1 ON t0.unique2 = t1.unique2
+LEFT JOIN (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 6
+) t2 ON t0.unique2 = t2.unique2
+ORDER BY t0_unique1, t1_unique1, t2_unique1;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.1.ddl.sqlpp
new file mode 100644
index 0000000..93f35eb
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.1.ddl.sqlpp
@@ -0,0 +1,60 @@
+/*
+ * 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.
+ */
+
+/*
+ * Test basic RIGHT OUTER JOIN cases
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create type tenkTypeNested1000 as open {
+  thousand        : integer
+};
+
+create dataset tenkNested1000(tenkTypeNested1000) primary key thousand;
+
+create type tenkTypeNested5000 as open {
+  fivethous        : integer
+};
+
+create dataset tenkNested5000(tenkTypeNested5000) primary key fivethous;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.10.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.10.query.sqlpp
new file mode 100644
index 0000000..a80b614
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.10.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.
+ */
+
+/* SELECT v.*; RIGHT,RIGHT,RIGHT; */
+
+use test;
+
+SELECT t0.*, t1.*, t2.*, t3.*
+FROM (
+  SELECT unique1 as t0_unique1, unique2 as t0_unique2 FROM tenk WHERE unique2 < 2
+) t0
+RIGHT JOIN (
+  SELECT unique1 as t1_unique1, unique2 as t1_unique2 FROM tenk WHERE unique2 < 4
+) t1 ON t0.t0_unique2 = t1.t1_unique2
+RIGHT JOIN (
+  SELECT unique1 as t2_unique1, unique2 as t2_unique2 FROM tenk WHERE unique2 < 6
+) t2 ON t1.t1_unique2 = t2.t2_unique2
+RIGHT JOIN (
+  SELECT unique1 as t3_unique1, unique2 as t3_unique2 FROM tenk WHERE unique2 < 8
+) t3 ON t2.t2_unique2 = t3.t3_unique2
+ORDER BY t0.t0_unique1, t1.t1_unique1, t2.t2_unique1, t3.t3_unique1;
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.2.update.sqlpp
new file mode 100644
index 0000000..e135a7b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.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;
+
+load  dataset tenk using localfs ((`path`=`asterix_nc1://data/tenk.tbl`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+insert into tenkNested1000 ((
+  select thousand, g
+  from tenk
+  where unique2 > 1
+  group by thousand group as g(unique1 as unique1, unique2 as unique2)
+));
+
+insert into tenkNested5000 ((
+  select fivethous, g
+  from tenk
+  where unique2 > 1
+  group by fivethous group as g(unique1 as unique1, unique2 as unique2)
+));
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.3.query.sqlpp
new file mode 100644
index 0000000..2062cd8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.3.query.sqlpp
@@ -0,0 +1,35 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use test;
+
+/* UNNEST, RIGHT JOIN */
+
+use test;
+
+SELECT
+  if_missing(t0.unique1, null) AS t0_unique1,
+  if_missing(t1.unique1, null) AS t1_unique1
+
+FROM tenkNested1000 tn
+UNNEST tn.g t0
+RIGHT JOIN (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 4
+) t1 ON t0.unique2 = t1.unique2
+ORDER BY t0_unique1, t1_unique1;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.4.query.sqlpp
new file mode 100644
index 0000000..4fbec97
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.4.query.sqlpp
@@ -0,0 +1,40 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use test;
+
+/* RIGHT JOIN, LEFT UNNEST */
+
+use test;
+
+SELECT
+    if_missing(t0.fivethous, null) AS t0_fivethous,
+    if_missing(t0_n.unique1, null) AS t0_n_unique1,
+    if_missing(t1.fivethous, null) AS t1_fivethous,
+    if_missing(t1.unique1, null) AS t1_unique1
+FROM (
+  SELECT fivethous, g
+  FROM tenkNested5000
+  WHERE fivethous < 1
+) t0
+RIGHT JOIN (
+  SELECT fivethous, unique1 FROM tenk WHERE fivethous < 2
+) t1 ON t0.fivethous = t1.fivethous
+LEFT UNNEST t0.g AS t0_n
+ORDER BY t1_fivethous, t1_unique1, t0_n_unique1;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.5.query.sqlpp
new file mode 100644
index 0000000..bc79bba
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.5.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.
+ */
+
+/* Use statement parameters */
+
+// requesttype=application/json
+
+// param $p2:json=2
+// param $p4:json=4
+
+use test;
+
+SELECT
+  if_missing(t0.unique1, null) AS t0_unique1,
+  if_missing(t1.unique1, null) AS t1_unique1
+FROM (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < $p2
+) t0
+RIGHT JOIN (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < $p4
+) t1 ON t0.unique2 = t1.unique2
+ORDER BY t0_unique1, t1_unique1;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.6.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.6.ddl.sqlpp
new file mode 100644
index 0000000..1fe402c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.6.ddl.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.
+ */
+
+/*
+ * Test basic RIGHT OUTER JOIN cases
+ */
+
+use test;
+
+create function f1(p2, p4) {
+  SELECT
+    if_missing(t0.unique1, null) AS t0_unique1,
+    if_missing(t1.unique1, null) AS t1_unique1
+  FROM (
+    SELECT unique1, unique2 FROM tenk WHERE unique2 < p2
+  ) t0
+  RIGHT JOIN (
+    SELECT unique1, unique2 FROM tenk WHERE unique2 < p4
+  ) t1 ON t0.unique2 = t1.unique2
+};
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.7.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.7.query.sqlpp
new file mode 100644
index 0000000..bb12774
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.7.query.sqlpp
@@ -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 test;
+
+SELECT t0_unique1, t1_unique1
+FROM f1(2, 4) t
+ORDER BY t0_unique1, t1_unique1;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.8.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.8.query.sqlpp
new file mode 100644
index 0000000..fa56a4d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.8.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.
+ */
+
+/* SELECT *; RIGHT,RIGHT,RIGHT; */
+
+use test;
+
+SELECT *
+FROM (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 2
+) t0
+RIGHT JOIN (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 4
+) t1 ON t0.unique2 = t1.unique2
+RIGHT JOIN (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 6
+) t2 ON t1.unique2 = t2.unique2
+RIGHT JOIN (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 8
+) t3 ON t2.unique2 = t3.unique2
+ORDER BY t0.unique1, t1.unique1, t2.unique1, t3.unique1;
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.9.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.9.query.sqlpp
new file mode 100644
index 0000000..ef0d112
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-02-core/roj-02-core.9.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.
+ */
+
+/* SELECT t0,t1,t2,t3; RIGHT,RIGHT,RIGHT; */
+
+use test;
+
+SELECT t0, t1, t2, t3
+FROM (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 2
+) t0
+RIGHT JOIN (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 4
+) t1 ON t0.unique2 = t1.unique2
+RIGHT JOIN (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 6
+) t2 ON t1.unique2 = t2.unique2
+RIGHT JOIN (
+  SELECT unique1, unique2 FROM tenk WHERE unique2 < 8
+) t3 ON t2.unique2 = t3.unique2
+ORDER BY t0.unique1, t1.unique1, t2.unique1, t3.unique1;
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-03-negative/roj-03-negative.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-03-negative/roj-03-negative.1.ddl.sqlpp
new file mode 100644
index 0000000..4c066b5
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-03-negative/roj-03-negative.1.ddl.sqlpp
@@ -0,0 +1,54 @@
+/*
+ * 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.
+ */
+
+/*
+ * Test basic RIGHT OUTER JOIN cases
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create type tenkTypeNested1000 as open {
+  thousand        : integer
+};
+
+create dataset tenkNested1000(tenkTypeNested1000) primary key thousand;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-03-negative/roj-03-negative.2.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-03-negative/roj-03-negative.2.query.sqlpp
new file mode 100644
index 0000000..6b4d1ee
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/rightouterjoin/roj-03-negative/roj-03-negative.2.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.
+ */
+
+/*
+ * Expected result: Failure. Cannot use RIGHT JOIN if it's left branch has free variables.
+ */
+
+use test;
+
+SELECT tn, x
+FROM tenkNested1000 tn
+LET x = (
+  SELECT t0.unique1, t1.unique1
+  FROM tn.g t0
+  RIGHT JOIN (
+    SELECT unique1, unique2 FROM tenk WHERE unique2 < 4
+  ) t1 ON t0.unique2 = t1.unique2
+);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-01-core/roj-01-core.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-01-core/roj-01-core.3.adm
new file mode 100644
index 0000000..e7f5f55
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-01-core/roj-01-core.3.adm
@@ -0,0 +1,4 @@
+{ "t0_unique1": null, "t1_unique1": 3420 }
+{ "t0_unique1": null, "t1_unique1": 9850 }
+{ "t0_unique1": 1891, "t1_unique1": 1891 }
+{ "t0_unique1": 8800, "t1_unique1": 8800 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-01-core/roj-01-core.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-01-core/roj-01-core.4.adm
new file mode 100644
index 0000000..d3b4a40
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-01-core/roj-01-core.4.adm
@@ -0,0 +1,6 @@
+{ "t0_unique1": null, "t1_unique1": null, "t2_unique1": 7164 }
+{ "t0_unique1": null, "t1_unique1": null, "t2_unique1": 8009 }
+{ "t0_unique1": null, "t1_unique1": 3420, "t2_unique1": 3420 }
+{ "t0_unique1": null, "t1_unique1": 9850, "t2_unique1": 9850 }
+{ "t0_unique1": 1891, "t1_unique1": 1891, "t2_unique1": 1891 }
+{ "t0_unique1": 8800, "t1_unique1": 8800, "t2_unique1": 8800 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-01-core/roj-01-core.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-01-core/roj-01-core.5.adm
new file mode 100644
index 0000000..37c7036
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-01-core/roj-01-core.5.adm
@@ -0,0 +1,8 @@
+{ "t0_unique1": null, "t1_unique1": null, "t2_unique1": null, "t3_unique1": 5057 }
+{ "t0_unique1": null, "t1_unique1": null, "t2_unique1": null, "t3_unique1": 6701 }
+{ "t0_unique1": null, "t1_unique1": null, "t2_unique1": 7164, "t3_unique1": 7164 }
+{ "t0_unique1": null, "t1_unique1": null, "t2_unique1": 8009, "t3_unique1": 8009 }
+{ "t0_unique1": null, "t1_unique1": 3420, "t2_unique1": 3420, "t3_unique1": 3420 }
+{ "t0_unique1": null, "t1_unique1": 9850, "t2_unique1": 9850, "t3_unique1": 9850 }
+{ "t0_unique1": 1891, "t1_unique1": 1891, "t2_unique1": 1891, "t3_unique1": 1891 }
+{ "t0_unique1": 8800, "t1_unique1": 8800, "t2_unique1": 8800, "t3_unique1": 8800 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-01-core/roj-01-core.6.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-01-core/roj-01-core.6.adm
new file mode 100644
index 0000000..ed96b75
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-01-core/roj-01-core.6.adm
@@ -0,0 +1,6 @@
+{ "t0_unique1": null, "t1_unique1": null, "t2_unique1": 3420 }
+{ "t0_unique1": null, "t1_unique1": null, "t2_unique1": 7164 }
+{ "t0_unique1": null, "t1_unique1": null, "t2_unique1": 8009 }
+{ "t0_unique1": null, "t1_unique1": null, "t2_unique1": 9850 }
+{ "t0_unique1": 1891, "t1_unique1": 1891, "t2_unique1": 1891 }
+{ "t0_unique1": 8800, "t1_unique1": 8800, "t2_unique1": 8800 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-01-core/roj-01-core.7.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-01-core/roj-01-core.7.adm
new file mode 100644
index 0000000..5984a21
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-01-core/roj-01-core.7.adm
@@ -0,0 +1,4 @@
+{ "t0_unique1": null, "t1_unique1": 3420, "t2_unique1": null }
+{ "t0_unique1": null, "t1_unique1": 9850, "t2_unique1": null }
+{ "t0_unique1": 1891, "t1_unique1": 1891, "t2_unique1": 1891 }
+{ "t0_unique1": 8800, "t1_unique1": 8800, "t2_unique1": 8800 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-02-core/roj-02-core.10.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-02-core/roj-02-core.10.adm
new file mode 100644
index 0000000..edbaaf0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-02-core/roj-02-core.10.adm
@@ -0,0 +1,8 @@
+{ "t3_unique1": 5057, "t3_unique2": 6 }
+{ "t3_unique1": 6701, "t3_unique2": 7 }
+{ "t3_unique1": 7164, "t3_unique2": 4, "t2_unique1": 7164, "t2_unique2": 4 }
+{ "t3_unique1": 8009, "t3_unique2": 5, "t2_unique1": 8009, "t2_unique2": 5 }
+{ "t3_unique1": 3420, "t3_unique2": 2, "t2_unique1": 3420, "t2_unique2": 2, "t1_unique1": 3420, "t1_unique2": 2 }
+{ "t3_unique1": 9850, "t3_unique2": 3, "t2_unique1": 9850, "t2_unique2": 3, "t1_unique1": 9850, "t1_unique2": 3 }
+{ "t3_unique1": 1891, "t3_unique2": 1, "t2_unique1": 1891, "t2_unique2": 1, "t1_unique1": 1891, "t1_unique2": 1, "t0_unique1": 1891, "t0_unique2": 1 }
+{ "t3_unique1": 8800, "t3_unique2": 0, "t2_unique1": 8800, "t2_unique2": 0, "t1_unique1": 8800, "t1_unique2": 0, "t0_unique1": 8800, "t0_unique2": 0 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-02-core/roj-02-core.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-02-core/roj-02-core.3.adm
new file mode 100644
index 0000000..9571ef0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-02-core/roj-02-core.3.adm
@@ -0,0 +1,4 @@
+{ "t1_unique1": 1891, "t0_unique1": null }
+{ "t1_unique1": 8800, "t0_unique1": null }
+{ "t1_unique1": 3420, "t0_unique1": 3420 }
+{ "t1_unique1": 9850, "t0_unique1": 9850 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-02-core/roj-02-core.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-02-core/roj-02-core.4.adm
new file mode 100644
index 0000000..39f7696
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-02-core/roj-02-core.4.adm
@@ -0,0 +1,6 @@
+{ "t0_fivethous": 0, "t1_fivethous": 0, "t1_unique1": 0, "t0_n_unique1": 0 }
+{ "t0_fivethous": 0, "t1_fivethous": 0, "t1_unique1": 0, "t0_n_unique1": 5000 }
+{ "t0_fivethous": 0, "t1_fivethous": 0, "t1_unique1": 5000, "t0_n_unique1": 0 }
+{ "t0_fivethous": 0, "t1_fivethous": 0, "t1_unique1": 5000, "t0_n_unique1": 5000 }
+{ "t0_fivethous": null, "t1_fivethous": 1, "t1_unique1": 1, "t0_n_unique1": null }
+{ "t0_fivethous": null, "t1_fivethous": 1, "t1_unique1": 5001, "t0_n_unique1": null }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-02-core/roj-02-core.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-02-core/roj-02-core.5.adm
new file mode 100644
index 0000000..e7f5f55
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-02-core/roj-02-core.5.adm
@@ -0,0 +1,4 @@
+{ "t0_unique1": null, "t1_unique1": 3420 }
+{ "t0_unique1": null, "t1_unique1": 9850 }
+{ "t0_unique1": 1891, "t1_unique1": 1891 }
+{ "t0_unique1": 8800, "t1_unique1": 8800 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-02-core/roj-02-core.7.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-02-core/roj-02-core.7.adm
new file mode 100644
index 0000000..e7f5f55
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-02-core/roj-02-core.7.adm
@@ -0,0 +1,4 @@
+{ "t0_unique1": null, "t1_unique1": 3420 }
+{ "t0_unique1": null, "t1_unique1": 9850 }
+{ "t0_unique1": 1891, "t1_unique1": 1891 }
+{ "t0_unique1": 8800, "t1_unique1": 8800 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-02-core/roj-02-core.8.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-02-core/roj-02-core.8.adm
new file mode 100644
index 0000000..1872bb7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-02-core/roj-02-core.8.adm
@@ -0,0 +1,8 @@
+{ "t3": { "unique1": 5057, "unique2": 6 } }
+{ "t3": { "unique1": 6701, "unique2": 7 } }
+{ "t3": { "unique1": 7164, "unique2": 4 }, "t2": { "unique1": 7164, "unique2": 4 } }
+{ "t3": { "unique1": 8009, "unique2": 5 }, "t2": { "unique1": 8009, "unique2": 5 } }
+{ "t3": { "unique1": 3420, "unique2": 2 }, "t1": { "unique1": 3420, "unique2": 2 }, "t2": { "unique1": 3420, "unique2": 2 } }
+{ "t3": { "unique1": 9850, "unique2": 3 }, "t1": { "unique1": 9850, "unique2": 3 }, "t2": { "unique1": 9850, "unique2": 3 } }
+{ "t3": { "unique1": 1891, "unique2": 1 }, "t1": { "unique1": 1891, "unique2": 1 }, "t0": { "unique1": 1891, "unique2": 1 }, "t2": { "unique1": 1891, "unique2": 1 } }
+{ "t3": { "unique1": 8800, "unique2": 0 }, "t1": { "unique1": 8800, "unique2": 0 }, "t0": { "unique1": 8800, "unique2": 0 }, "t2": { "unique1": 8800, "unique2": 0 } }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-02-core/roj-02-core.9.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-02-core/roj-02-core.9.adm
new file mode 100644
index 0000000..663291c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/rightouterjoin/roj-02-core/roj-02-core.9.adm
@@ -0,0 +1,8 @@
+{ "t3": { "unique1": 5057, "unique2": 6 } }
+{ "t3": { "unique1": 6701, "unique2": 7 } }
+{ "t2": { "unique1": 7164, "unique2": 4 }, "t3": { "unique1": 7164, "unique2": 4 } }
+{ "t2": { "unique1": 8009, "unique2": 5 }, "t3": { "unique1": 8009, "unique2": 5 } }
+{ "t1": { "unique1": 3420, "unique2": 2 }, "t2": { "unique1": 3420, "unique2": 2 }, "t3": { "unique1": 3420, "unique2": 2 } }
+{ "t1": { "unique1": 9850, "unique2": 3 }, "t2": { "unique1": 9850, "unique2": 3 }, "t3": { "unique1": 9850, "unique2": 3 } }
+{ "t0": { "unique1": 1891, "unique2": 1 }, "t1": { "unique1": 1891, "unique2": 1 }, "t2": { "unique1": 1891, "unique2": 1 }, "t3": { "unique1": 1891, "unique2": 1 } }
+{ "t0": { "unique1": 8800, "unique2": 0 }, "t1": { "unique1": 8800, "unique2": 0 }, "t2": { "unique1": 8800, "unique2": 0 }, "t3": { "unique1": 8800, "unique2": 0 } }
\ 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 66cb684..04093a3 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -12847,6 +12847,24 @@
       </compilation-unit>
     </test-case>
   </test-group>
+  <test-group name="rightouterjoin">
+    <test-case FilePath="rightouterjoin">
+      <compilation-unit name="roj-01-core">
+        <output-dir compare="Text">roj-01-core</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="rightouterjoin">
+      <compilation-unit name="roj-02-core">
+        <output-dir compare="Text">roj-02-core</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="rightouterjoin">
+      <compilation-unit name="roj-03-negative">
+        <output-dir compare="Text">none</output-dir>
+        <expected-error>ASX1130: Illegal use of RIGHT OUTER JOIN</expected-error>
+      </compilation-unit>
+    </test-case>
+  </test-group>
   <test-group name="tinysocial">
     <test-case FilePath="tinysocial">
       <compilation-unit name="tinysocial-suite">
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 431d5b3..3aae2e1 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
@@ -218,6 +218,7 @@
     public static final int UNSUPPORTED_ADAPTER_LANGUAGE = 1127;
     public static final int INCONSISTENT_FILTER_INDICATOR = 1128;
     public static final int UNSUPPORTED_GBY_OBY_SELECT_COMBO = 1129;
+    public static final int ILLEGAL_RIGHT_OUTER_JOIN = 1130;
 
     // Feed errors
     public static final int 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 127ec04..40caeb8 100644
--- a/asterixdb/asterix-common/src/main/resources/asx_errormsg/en.properties
+++ b/asterixdb/asterix-common/src/main/resources/asx_errormsg/en.properties
@@ -215,6 +215,7 @@
 1127 = Unsupported adapter language: %1$s
 1128 = Filter field is not defined properly
 1129 = Cannot compile SELECT variable.* with GROUP BY GROUPING SETS/ROLLUP/CUBE followed by ORDER BY/LIMIT
+1130 = Illegal use of RIGHT OUTER JOIN
 
 # Feed Errors
 3001 = Illegal state.
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/annotation/ExcludeFromSelectStarAnnotation.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/annotation/ExcludeFromSelectStarAnnotation.java
new file mode 100644
index 0000000..a430ae6
--- /dev/null
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/annotation/ExcludeFromSelectStarAnnotation.java
@@ -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.
+ */
+
+package org.apache.asterix.lang.sqlpp.annotation;
+
+import org.apache.hyracks.algebricks.core.algebra.expressions.AbstractExpressionAnnotation;
+import org.apache.hyracks.algebricks.core.algebra.expressions.IExpressionAnnotation;
+
+public final class ExcludeFromSelectStarAnnotation extends AbstractExpressionAnnotation {
+
+    public static final ExcludeFromSelectStarAnnotation INSTANCE = new ExcludeFromSelectStarAnnotation();
+
+    @Override
+    public IExpressionAnnotation copy() {
+        return this;
+    }
+}
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/AbstractBinaryCorrelateClause.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/AbstractBinaryCorrelateClause.java
index ba1c498..21770c3 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/AbstractBinaryCorrelateClause.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/AbstractBinaryCorrelateClause.java
@@ -24,27 +24,19 @@
 import org.apache.asterix.lang.common.base.AbstractClause;
 import org.apache.asterix.lang.common.base.Expression;
 import org.apache.asterix.lang.common.expression.VariableExpr;
-import org.apache.asterix.lang.sqlpp.optype.JoinType;
 
 public abstract class AbstractBinaryCorrelateClause extends AbstractClause {
 
-    private JoinType joinType;
     private Expression rightExpr;
     private VariableExpr rightVar;
     private VariableExpr rightPosVar;
 
-    public AbstractBinaryCorrelateClause(JoinType joinType, Expression rightExpr, VariableExpr rightVar,
-            VariableExpr rightPosVar) {
-        this.joinType = joinType;
+    public AbstractBinaryCorrelateClause(Expression rightExpr, VariableExpr rightVar, VariableExpr rightPosVar) {
         this.rightExpr = rightExpr;
         this.rightVar = rightVar;
         this.rightPosVar = rightPosVar;
     }
 
-    public JoinType getJoinType() {
-        return joinType;
-    }
-
     public Expression getRightExpression() {
         return rightExpr;
     }
@@ -67,7 +59,7 @@
 
     @Override
     public int hashCode() {
-        return Objects.hash(joinType, rightExpr, rightPosVar, rightVar);
+        return Objects.hash(rightExpr, rightPosVar, rightVar);
     }
 
     @Override
@@ -79,8 +71,8 @@
             return false;
         }
         AbstractBinaryCorrelateClause target = (AbstractBinaryCorrelateClause) object;
-        return Objects.equals(joinType, target.joinType) && Objects.equals(rightExpr, target.rightExpr)
-                && Objects.equals(rightPosVar, target.rightPosVar) && Objects.equals(rightVar, target.rightVar);
+        return Objects.equals(rightExpr, target.rightExpr) && Objects.equals(rightPosVar, target.rightPosVar)
+                && Objects.equals(rightVar, target.rightVar);
     }
 
 }
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/AbstractBinaryCorrelateWithConditionClause.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/AbstractBinaryCorrelateWithConditionClause.java
index 3773ea9..581efdd 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/AbstractBinaryCorrelateWithConditionClause.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/AbstractBinaryCorrelateWithConditionClause.java
@@ -21,15 +21,14 @@
 
 import org.apache.asterix.lang.common.base.Expression;
 import org.apache.asterix.lang.common.expression.VariableExpr;
-import org.apache.asterix.lang.sqlpp.optype.JoinType;
 
 public abstract class AbstractBinaryCorrelateWithConditionClause extends AbstractBinaryCorrelateClause {
 
     private Expression conditionExpr;
 
-    public AbstractBinaryCorrelateWithConditionClause(JoinType joinType, Expression rightExpr, VariableExpr rightVar,
+    public AbstractBinaryCorrelateWithConditionClause(Expression rightExpr, VariableExpr rightVar,
             VariableExpr rightPosVar, Expression conditionExpr) {
-        super(joinType, rightExpr, rightVar, rightPosVar);
+        super(rightExpr, rightVar, rightPosVar);
         this.conditionExpr = conditionExpr;
     }
 
@@ -57,5 +56,4 @@
         AbstractBinaryCorrelateWithConditionClause target = (AbstractBinaryCorrelateWithConditionClause) object;
         return super.equals(target) && conditionExpr.equals(target.getConditionExpression());
     }
-
 }
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/JoinClause.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/JoinClause.java
index 0b2a346..18b7a24 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/JoinClause.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/JoinClause.java
@@ -28,9 +28,12 @@
 
 public class JoinClause extends AbstractBinaryCorrelateWithConditionClause {
 
+    private final JoinType joinType;
+
     public JoinClause(JoinType joinType, Expression rightExpr, VariableExpr rightVar, VariableExpr rightPosVar,
             Expression conditionExpr) {
-        super(joinType, rightExpr, rightVar, rightPosVar, conditionExpr);
+        super(rightExpr, rightVar, rightPosVar, conditionExpr);
+        this.joinType = joinType;
     }
 
     @Override
@@ -43,4 +46,24 @@
         return ClauseType.JOIN_CLAUSE;
     }
 
+    public JoinType getJoinType() {
+        return joinType;
+    }
+
+    @Override
+    public int hashCode() {
+        return 31 * super.hashCode() + joinType.hashCode();
+    }
+
+    @Override
+    public boolean equals(Object object) {
+        if (this == object) {
+            return true;
+        }
+        if (!(object instanceof JoinClause)) {
+            return false;
+        }
+        JoinClause target = (JoinClause) object;
+        return super.equals(target) && joinType.equals(target.getJoinType());
+    }
 }
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/NestClause.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/NestClause.java
index a2c27f0..5f0afb0 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/NestClause.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/NestClause.java
@@ -23,14 +23,17 @@
 import org.apache.asterix.lang.common.base.Expression;
 import org.apache.asterix.lang.common.expression.VariableExpr;
 import org.apache.asterix.lang.common.visitor.base.ILangVisitor;
-import org.apache.asterix.lang.sqlpp.optype.JoinType;
+import org.apache.asterix.lang.sqlpp.optype.UnnestType;
 import org.apache.asterix.lang.sqlpp.visitor.base.ISqlppVisitor;
 
 public class NestClause extends AbstractBinaryCorrelateWithConditionClause {
 
-    public NestClause(JoinType joinType, Expression rightExpr, VariableExpr rightVar, VariableExpr rightPosVar,
+    private final UnnestType nestType;
+
+    public NestClause(UnnestType nestType, Expression rightExpr, VariableExpr rightVar, VariableExpr rightPosVar,
             Expression conditionExpr) {
-        super(joinType, rightExpr, rightVar, rightPosVar, conditionExpr);
+        super(rightExpr, rightVar, rightPosVar, conditionExpr);
+        this.nestType = nestType;
     }
 
     @Override
@@ -43,4 +46,24 @@
         return ClauseType.NEST_CLAUSE;
     }
 
+    public UnnestType getNestType() {
+        return nestType;
+    }
+
+    @Override
+    public int hashCode() {
+        return 31 * super.hashCode() + nestType.hashCode();
+    }
+
+    @Override
+    public boolean equals(Object object) {
+        if (this == object) {
+            return true;
+        }
+        if (!(object instanceof NestClause)) {
+            return false;
+        }
+        NestClause target = (NestClause) object;
+        return super.equals(target) && nestType.equals(target.getNestType());
+    }
 }
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/UnnestClause.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/UnnestClause.java
index f1a3964..f035be4 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/UnnestClause.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/UnnestClause.java
@@ -23,13 +23,16 @@
 import org.apache.asterix.lang.common.base.Expression;
 import org.apache.asterix.lang.common.expression.VariableExpr;
 import org.apache.asterix.lang.common.visitor.base.ILangVisitor;
-import org.apache.asterix.lang.sqlpp.optype.JoinType;
+import org.apache.asterix.lang.sqlpp.optype.UnnestType;
 import org.apache.asterix.lang.sqlpp.visitor.base.ISqlppVisitor;
 
 public class UnnestClause extends AbstractBinaryCorrelateClause {
 
-    public UnnestClause(JoinType joinType, Expression rightExpr, VariableExpr rightVar, VariableExpr rightPosVar) {
-        super(joinType, rightExpr, rightVar, rightPosVar);
+    private final UnnestType unnestType;
+
+    public UnnestClause(UnnestType unnestType, Expression rightExpr, VariableExpr rightVar, VariableExpr rightPosVar) {
+        super(rightExpr, rightVar, rightPosVar);
+        this.unnestType = unnestType;
     }
 
     @Override
@@ -42,4 +45,24 @@
         return ClauseType.UNNEST_CLAUSE;
     }
 
+    public UnnestType getUnnestType() {
+        return unnestType;
+    }
+
+    @Override
+    public int hashCode() {
+        return 31 * super.hashCode() + unnestType.hashCode();
+    }
+
+    @Override
+    public boolean equals(Object object) {
+        if (this == object) {
+            return true;
+        }
+        if (!(object instanceof UnnestClause)) {
+            return false;
+        }
+        UnnestClause target = (UnnestClause) object;
+        return super.equals(target) && unnestType.equals(target.getUnnestType());
+    }
 }
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/optype/JoinType.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/optype/JoinType.java
index f4b84f5..352db3c 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/optype/JoinType.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/optype/JoinType.java
@@ -20,8 +20,7 @@
 package org.apache.asterix.lang.sqlpp.optype;
 
 public enum JoinType {
-
     INNER,
-    LEFTOUTER
-
+    LEFTOUTER,
+    RIGHTOUTER
 }
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/optype/UnnestType.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/optype/UnnestType.java
new file mode 100644
index 0000000..819e842
--- /dev/null
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/optype/UnnestType.java
@@ -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.
+ */
+
+package org.apache.asterix.lang.sqlpp.optype;
+
+public enum UnnestType {
+    INNER,
+    LEFTOUTER
+}
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppFunctionBodyRewriter.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppFunctionBodyRewriter.java
index a89ca5b..356a683 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppFunctionBodyRewriter.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppFunctionBodyRewriter.java
@@ -91,6 +91,9 @@
         // Rewrites several variable-arg functions into their corresponding internal list-input functions.
         rewriteListInputFunctions();
 
+        // Rewrites RIGHT OUTER JOINs into LEFT OUTER JOINs if possible
+        rewriteRightJoins();
+
         // Inlines functions recursively.
         inlineDeclaredUdfs(inlineUdfs);
     }
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 3f93dd0..365c42e 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
@@ -72,6 +72,7 @@
 import org.apache.asterix.lang.sqlpp.rewrites.visitor.SqlppGroupingSetsVisitor;
 import org.apache.asterix.lang.sqlpp.rewrites.visitor.SqlppInlineUdfsVisitor;
 import org.apache.asterix.lang.sqlpp.rewrites.visitor.SqlppListInputFunctionRewriteVisitor;
+import org.apache.asterix.lang.sqlpp.rewrites.visitor.SqlppRightJoinRewriteVisitor;
 import org.apache.asterix.lang.sqlpp.rewrites.visitor.SqlppSpecialFunctionNameRewriteVisitor;
 import org.apache.asterix.lang.sqlpp.rewrites.visitor.SqlppWindowAggregationSugarVisitor;
 import org.apache.asterix.lang.sqlpp.rewrites.visitor.SqlppWindowRewriteVisitor;
@@ -176,6 +177,9 @@
         // Rewrites several variable-arg functions into their corresponding internal list-input functions.
         rewriteListInputFunctions();
 
+        // Rewrites RIGHT OUTER JOINs into LEFT OUTER JOINs if possible
+        rewriteRightJoins();
+
         // Inlines functions.
         inlineDeclaredUdfs(inlineUdfs);
 
@@ -297,6 +301,12 @@
         rewriteTopExpr(visitor, null);
     }
 
+    protected void rewriteRightJoins() throws CompilationException {
+        // Rewrites RIGHT OUTER JOINs into LEFT OUTER JOINs if possible
+        SqlppRightJoinRewriteVisitor visitor = new SqlppRightJoinRewriteVisitor(context, externalVars);
+        rewriteTopExpr(visitor, null);
+    }
+
     protected void inlineDeclaredUdfs(boolean inlineUdfs) throws CompilationException {
         List<FunctionSignature> funIds = new ArrayList<FunctionSignature>();
         for (FunctionDecl fdecl : declaredFunctions) {
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppGroupingSetsVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppGroupingSetsVisitor.java
index c474ee3..791bfe1 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppGroupingSetsVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppGroupingSetsVisitor.java
@@ -369,7 +369,7 @@
 
         return selectElement
                 ? SetOperationVisitor.createSelectBlock(newSelectExpr, distinct,
-                        selectElementConvertToRegular ? SqlppGroupingSetsVisitor::getFieldByName : null,
+                        selectElementConvertToRegular ? SqlppRewriteUtil::getFieldByName : null,
                         selectElementConvertToRegular ? selectElementMainProjectionName : null, context)
                 : SetOperationVisitor.createSelectBlock(newSelectExpr, distinct,
                         SqlppGroupingSetsVisitor::removeFieldsByName, extraProjections.values(), context);
@@ -435,17 +435,6 @@
         return resultExpr;
     }
 
-    private static Expression getFieldByName(Expression inExpr, String fieldName) {
-        LiteralExpr fieldNameExpr = new LiteralExpr(new StringLiteral(fieldName));
-        fieldNameExpr.setSourceLocation(inExpr.getSourceLocation());
-        List<Expression> argList = new ArrayList<>(2);
-        argList.add(inExpr);
-        argList.add(fieldNameExpr);
-        CallExpr callExpr = new CallExpr(new FunctionSignature(BuiltinFunctions.FIELD_ACCESS_BY_NAME), argList);
-        callExpr.setSourceLocation(inExpr.getSourceLocation());
-        return callExpr;
-    }
-
     private String generateProjectionName(List<Projection> projectionList) {
         String projectionName;
         do {
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppRightJoinRewriteVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppRightJoinRewriteVisitor.java
new file mode 100644
index 0000000..a8db06f
--- /dev/null
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppRightJoinRewriteVisitor.java
@@ -0,0 +1,381 @@
+/*
+ * 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.
+ */
+
+package org.apache.asterix.lang.sqlpp.rewrites.visitor;
+
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.HashMap;
+import java.util.LinkedHashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+
+import org.apache.asterix.common.exceptions.CompilationException;
+import org.apache.asterix.common.exceptions.ErrorCode;
+import org.apache.asterix.lang.common.base.Clause;
+import org.apache.asterix.lang.common.base.Expression;
+import org.apache.asterix.lang.common.base.ILangExpression;
+import org.apache.asterix.lang.common.clause.LetClause;
+import org.apache.asterix.lang.common.expression.VariableExpr;
+import org.apache.asterix.lang.common.rewrites.LangRewritingContext;
+import org.apache.asterix.lang.common.struct.VarIdentifier;
+import org.apache.asterix.lang.sqlpp.annotation.ExcludeFromSelectStarAnnotation;
+import org.apache.asterix.lang.sqlpp.clause.AbstractBinaryCorrelateClause;
+import org.apache.asterix.lang.sqlpp.clause.AbstractBinaryCorrelateWithConditionClause;
+import org.apache.asterix.lang.sqlpp.clause.FromClause;
+import org.apache.asterix.lang.sqlpp.clause.FromTerm;
+import org.apache.asterix.lang.sqlpp.clause.JoinClause;
+import org.apache.asterix.lang.sqlpp.clause.Projection;
+import org.apache.asterix.lang.sqlpp.clause.SelectBlock;
+import org.apache.asterix.lang.sqlpp.clause.SelectClause;
+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.JoinType;
+import org.apache.asterix.lang.sqlpp.struct.SetOperationInput;
+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.hyracks.algebricks.common.utils.Pair;
+import org.apache.hyracks.api.exceptions.SourceLocation;
+
+/**
+ * Rewrites RIGHT OUTER JOIN into LEFT OUTER JOIN as follows:
+ * <p>
+ * Single preceding clause:
+ * <pre>
+ * FROM x RIGHT OUTER JOIN y
+ * -->
+ * FROM y LEFT OUTER JOIN x
+ * </pre>
+ * Multiple preceding clauses:
+ * <pre>
+ * FROM x JOIN y RIGHT OUTER JOIN z
+ * -->
+ * FROM z LEFT OUTER JOIN ( FROM x JOIN y SELECT ... )
+ * </pre>
+ * Note, clauses preceding to the RIGHT JOIN clause should not have any non-external free variables.
+ * If they do then this rewriting fails and the query would fail later during logical plan generation.
+ * E.g.
+ * <pre>
+ * FROM x
+ * LET v = ( FROM x.nested RIGHT OUTER JOIN y ... )
+ * ...
+ * </pre>
+ */
+public final class SqlppRightJoinRewriteVisitor extends AbstractSqlppSimpleExpressionVisitor {
+
+    private final LangRewritingContext context;
+
+    private final Collection<VarIdentifier> externalVars;
+
+    public SqlppRightJoinRewriteVisitor(LangRewritingContext context, Collection<VarIdentifier> externalVars) {
+        this.context = context;
+        this.externalVars = externalVars;
+    }
+
+    @Override
+    public Expression visit(SelectBlock selectBlock, ILangExpression arg) throws CompilationException {
+        super.visit(selectBlock, arg);
+
+        if (selectBlock.hasFromClause()) {
+            // we only support a FromClause with a single FromTerm for now
+            List<FromTerm> fromTerms = selectBlock.getFromClause().getFromTerms();
+            if (fromTerms.size() == 1) {
+                FromTerm fromTerm = fromTerms.get(0);
+                if (canRewriteFromTerm(fromTerm)) {
+                    Pair<FromTerm, List<LetClause>> newFromLetPair = rewriteFromTerm(fromTerm);
+                    fromTerms.set(0, newFromLetPair.first);
+                    selectBlock.getLetWhereList().addAll(0, newFromLetPair.second);
+                }
+            }
+        }
+
+        return null;
+    }
+
+    private boolean canRewriteFromTerm(FromTerm fromTerm) throws CompilationException {
+        boolean hasRightOuterJoin = false;
+        List<AbstractBinaryCorrelateClause> clauseList = fromTerm.getCorrelateClauses();
+        for (AbstractBinaryCorrelateClause correlateClause : clauseList) {
+            if (correlateClause.getClauseType() == Clause.ClauseType.JOIN_CLAUSE) {
+                JoinClause joinClause = (JoinClause) correlateClause;
+                if (joinClause.getJoinType() == JoinType.RIGHTOUTER) {
+                    hasRightOuterJoin = true;
+                    break;
+                }
+            }
+        }
+        if (!hasRightOuterJoin) {
+            // nothing to do
+            return false;
+        }
+
+        Set<VariableExpr> fromExprFreeVars = SqlppVariableUtil.getFreeVariables(fromTerm.getLeftExpression());
+        for (VariableExpr freeVarExpr : fromExprFreeVars) {
+            if (!externalVars.contains(freeVarExpr.getVar())) {
+                // cannot rewrite. will fail later
+                return false;
+            }
+        }
+
+        return true;
+    }
+
+    private Pair<FromTerm, List<LetClause>> rewriteFromTerm(FromTerm fromTerm) throws CompilationException {
+        Map<VariableExpr, Expression> substMapInner = new HashMap<>();
+        Map<VariableExpr, Expression> substMapOuterTmp = new HashMap<>();
+        Map<VariableExpr, Expression> substMapOuterFinal = new LinkedHashMap<>();
+
+        Expression fromExpr = fromTerm.getLeftExpression();
+        VariableExpr fromVar = fromTerm.getLeftVariable();
+        VariableExpr fromPosVar = fromTerm.getPositionalVariable();
+
+        List<AbstractBinaryCorrelateClause> correlateClauses = fromTerm.getCorrelateClauses();
+        for (int i = 0; i < correlateClauses.size(); i++) {
+            AbstractBinaryCorrelateClause correlateClause = correlateClauses.get(i);
+            if (correlateClause.getClauseType() == Clause.ClauseType.JOIN_CLAUSE
+                    && ((JoinClause) correlateClause).getJoinType() == JoinType.RIGHTOUTER) {
+                JoinClause joinClause = (JoinClause) correlateClause;
+                SourceLocation joinClauseSourceLoc = joinClause.getSourceLocation();
+
+                Expression rightExpr = joinClause.getRightExpression();
+                VariableExpr rightVar = joinClause.getRightVariable();
+                VariableExpr rightPosVar = joinClause.getPositionalVariable();
+                Expression condExpr = joinClause.getConditionExpression();
+                if (i == 0) {
+                    JoinClause newJoinClause =
+                            new JoinClause(JoinType.LEFTOUTER, fromExpr, fromVar, fromPosVar, condExpr);
+                    newJoinClause.setSourceLocation(joinClauseSourceLoc);
+
+                    fromExpr = rightExpr;
+                    fromVar = rightVar;
+                    fromPosVar = rightPosVar;
+                    correlateClauses.set(i, newJoinClause);
+                } else {
+                    VarIdentifier newRightVar = context.newVariable();
+
+                    substMapOuterTmp.clear();
+                    substMapInner.clear();
+                    List<Projection> projectList = new ArrayList<>();
+
+                    SourceLocation fromVarSourceLoc = fromVar.getSourceLocation();
+                    VarIdentifier newFromVar = context.newVariable();
+                    String newFromVarFieldName = generateFieldName();
+                    projectList.add(createProjection(newVariableExpr(newFromVar, fromVarSourceLoc), newFromVarFieldName,
+                            fromVarSourceLoc));
+                    substMapOuterTmp.put(fromVar, SqlppRewriteUtil
+                            .getFieldByName(newVariableExpr(newRightVar, fromVarSourceLoc), newFromVarFieldName));
+                    substMapInner.put(fromVar, newVariableExpr(newFromVar, fromVarSourceLoc));
+
+                    VarIdentifier newFromPosVar = null;
+                    if (fromPosVar != null) {
+                        SourceLocation fromPosVarSourceLoc = fromPosVar.getSourceLocation();
+                        newFromPosVar = context.newVariable();
+                        String newFromPosVarFieldName = generateFieldName();
+                        projectList.add(createProjection(newVariableExpr(newFromPosVar, fromPosVarSourceLoc),
+                                newFromPosVarFieldName, fromPosVarSourceLoc));
+                        substMapOuterTmp.put(fromPosVar, SqlppRewriteUtil.getFieldByName(
+                                newVariableExpr(newRightVar, fromPosVarSourceLoc), newFromPosVarFieldName));
+                        substMapInner.put(fromPosVar, newVariableExpr(newFromPosVar, fromPosVarSourceLoc));
+                    }
+
+                    List<AbstractBinaryCorrelateClause> newPrecedingClauseList = new ArrayList<>(i);
+                    for (int j = 0; j < i; j++) {
+                        AbstractBinaryCorrelateClause precedingClause = correlateClauses.get(j);
+                        SourceLocation precedingClauseSourceLoc = precedingClause.getSourceLocation();
+
+                        VariableExpr precedingClauseRightVar = precedingClause.getRightVariable();
+                        SourceLocation precedingClauseRightVarSourceLoc = precedingClauseRightVar.getSourceLocation();
+                        VarIdentifier newPrecedingClauseRightVar = context.newVariable();
+                        String newPrecedingClauseRightVarFieldName = generateFieldName();
+                        projectList.add(createProjection(
+                                newVariableExpr(newPrecedingClauseRightVar, precedingClauseRightVarSourceLoc),
+                                newPrecedingClauseRightVarFieldName, precedingClauseRightVarSourceLoc));
+                        substMapOuterTmp.put(precedingClauseRightVar,
+                                SqlppRewriteUtil.getFieldByName(
+                                        newVariableExpr(newRightVar, precedingClauseRightVarSourceLoc),
+                                        newPrecedingClauseRightVarFieldName));
+                        substMapInner.put(precedingClauseRightVar,
+                                newVariableExpr(newPrecedingClauseRightVar, precedingClauseRightVarSourceLoc));
+
+                        VariableExpr precedingClauseRightPosVar = precedingClause.getPositionalVariable();
+                        SourceLocation precedingClauseRightPosVarSourceLoc = null;
+                        VarIdentifier newPrecedingClauseRightPosVar = null;
+                        if (precedingClauseRightPosVar != null) {
+                            precedingClauseRightPosVarSourceLoc = precedingClauseRightPosVar.getSourceLocation();
+                            newPrecedingClauseRightPosVar = context.newVariable();
+                            String newPrecedingClauseRightPosVarFieldName = generateFieldName();
+                            projectList.add(createProjection(
+                                    newVariableExpr(newPrecedingClauseRightPosVar, precedingClauseRightPosVarSourceLoc),
+                                    newPrecedingClauseRightPosVarFieldName, precedingClauseRightPosVarSourceLoc));
+                            substMapOuterTmp.put(precedingClauseRightPosVar,
+                                    SqlppRewriteUtil.getFieldByName(
+                                            newVariableExpr(newRightVar, precedingClauseRightPosVarSourceLoc),
+                                            newPrecedingClauseRightPosVarFieldName));
+                            substMapInner.put(precedingClauseRightPosVar, newVariableExpr(newPrecedingClauseRightPosVar,
+                                    precedingClauseRightPosVarSourceLoc));
+                        }
+
+                        AbstractBinaryCorrelateClause newPrecedingClause;
+                        switch (precedingClause.getClauseType()) {
+                            case JOIN_CLAUSE:
+                                JoinClause joinPrecedingClause = (JoinClause) precedingClause;
+                                Expression newCondExpr = (Expression) SqlppRewriteUtil
+                                        .deepCopy(joinPrecedingClause.getConditionExpression());
+                                SqlppRewriteUtil.substituteExpression(newCondExpr, substMapInner, context);
+                                newPrecedingClause = new JoinClause(joinPrecedingClause.getJoinType(),
+                                        joinPrecedingClause.getRightExpression(),
+                                        newVariableExpr(newPrecedingClauseRightVar, precedingClauseRightVarSourceLoc),
+                                        newPrecedingClauseRightPosVar != null
+                                                ? newVariableExpr(newPrecedingClauseRightPosVar,
+                                                        precedingClauseRightPosVarSourceLoc)
+                                                : null,
+                                        newCondExpr);
+                                newPrecedingClause.setSourceLocation(precedingClauseSourceLoc);
+                                break;
+                            case UNNEST_CLAUSE:
+                                UnnestClause unnestPrecedingClause = (UnnestClause) precedingClause;
+                                Expression newRightExpr = (Expression) SqlppRewriteUtil
+                                        .deepCopy(unnestPrecedingClause.getRightExpression());
+                                SqlppRewriteUtil.substituteExpression(newRightExpr, substMapInner, context);
+                                newPrecedingClause = new UnnestClause(unnestPrecedingClause.getUnnestType(),
+                                        newRightExpr,
+                                        newVariableExpr(newPrecedingClauseRightVar, precedingClauseRightVarSourceLoc),
+                                        newPrecedingClauseRightPosVar != null
+                                                ? newVariableExpr(newPrecedingClauseRightPosVar,
+                                                        precedingClauseRightPosVarSourceLoc)
+                                                : null);
+                                newPrecedingClause.setSourceLocation(precedingClauseSourceLoc);
+                                break;
+                            default:
+                                throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_STATE,
+                                        String.valueOf(precedingClause.getClauseType()));
+                        }
+                        newPrecedingClauseList.add(newPrecedingClause);
+                    }
+
+                    Expression newRightExpr = createRightSelectExpression(fromExpr, newFromVar, newFromPosVar,
+                            newPrecedingClauseList, projectList, joinClauseSourceLoc);
+
+                    VariableExpr newRightVarExpr = newVariableExpr(newRightVar, rightVar.getSourceLocation());
+                    newRightVarExpr.addHint(ExcludeFromSelectStarAnnotation.INSTANCE);
+
+                    // compose substitution maps
+                    for (VariableExpr varExpr : substMapOuterFinal.keySet()) {
+                        Expression substExpr = substMapOuterFinal.get(varExpr);
+                        Expression newSubstExpr =
+                                SqlppRewriteUtil.substituteExpression(substExpr, substMapOuterTmp, context);
+                        substMapOuterFinal.put(varExpr, newSubstExpr);
+                    }
+                    substMapOuterFinal.putAll(substMapOuterTmp);
+
+                    Expression newCondExpr = SqlppRewriteUtil.substituteExpression(
+                            (Expression) SqlppRewriteUtil.deepCopy(condExpr), substMapOuterFinal, context);
+
+                    JoinClause newJoinClause =
+                            new JoinClause(JoinType.LEFTOUTER, newRightExpr, newRightVarExpr, null, newCondExpr);
+                    newJoinClause.setSourceLocation(joinClauseSourceLoc);
+
+                    fromExpr = rightExpr;
+                    fromVar = rightVar;
+                    fromPosVar = rightPosVar;
+
+                    correlateClauses.subList(0, i).clear();
+                    correlateClauses.set(0, newJoinClause);
+
+                    i = 0;
+                }
+            } else if (!substMapOuterFinal.isEmpty()) {
+                switch (correlateClause.getClauseType()) {
+                    case JOIN_CLAUSE:
+                        AbstractBinaryCorrelateWithConditionClause correlateConditionClause =
+                                (AbstractBinaryCorrelateWithConditionClause) correlateClause;
+                        Expression condExpr = correlateConditionClause.getConditionExpression();
+                        Expression newCondExpr = SqlppRewriteUtil.substituteExpression(
+                                (Expression) SqlppRewriteUtil.deepCopy(condExpr), substMapOuterFinal, context);
+                        correlateConditionClause.setConditionExpression(newCondExpr);
+                        // fall thru to UNNEST_CLAUSE
+                    case UNNEST_CLAUSE:
+                        Expression rightExpr = correlateClause.getRightExpression();
+                        Expression newRightExpr = SqlppRewriteUtil.substituteExpression(
+                                (Expression) SqlppRewriteUtil.deepCopy(rightExpr), substMapOuterFinal, context);
+                        correlateClause.setRightExpression(newRightExpr);
+                        break;
+                    default:
+                        throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_STATE,
+                                correlateClause.getSourceLocation(), String.valueOf(correlateClause.getClauseType()));
+                }
+
+            }
+        }
+
+        FromTerm newFromTerm = new FromTerm(fromExpr, fromVar, fromPosVar, correlateClauses);
+        newFromTerm.setSourceLocation(fromTerm.getSourceLocation());
+
+        List<LetClause> newLetClauses = new ArrayList<>(substMapOuterFinal.size());
+        for (Map.Entry<VariableExpr, Expression> me : substMapOuterFinal.entrySet()) {
+            VariableExpr newVarExpr = (VariableExpr) SqlppRewriteUtil.deepCopy(me.getKey());
+            Expression newValueExpr = (Expression) SqlppRewriteUtil.deepCopy(me.getValue());
+            LetClause newLetClause = new LetClause(newVarExpr, newValueExpr);
+            newLetClause.setSourceLocation(newVarExpr.getSourceLocation());
+            newLetClauses.add(newLetClause);
+        }
+
+        return new Pair<>(newFromTerm, newLetClauses);
+    }
+
+    private Expression createRightSelectExpression(Expression fromExpr, VarIdentifier fromVar, VarIdentifier fromPosVar,
+            List<AbstractBinaryCorrelateClause> correlateClauseList, List<Projection> projectList,
+            SourceLocation sourceLoc) {
+        FromTerm newFromTerm = new FromTerm(fromExpr, newVariableExpr(fromVar, sourceLoc),
+                fromPosVar != null ? newVariableExpr(fromPosVar, sourceLoc) : null, correlateClauseList);
+        List<FromTerm> newFromTermList = new ArrayList<>(1);
+        newFromTermList.add(newFromTerm);
+        FromClause newFromClause = new FromClause(newFromTermList);
+        newFromClause.setSourceLocation(sourceLoc);
+        SelectClause newSelectClause = new SelectClause(null, new SelectRegular(projectList), false);
+        newSelectClause.setSourceLocation(sourceLoc);
+        SelectBlock newSelectBlock = new SelectBlock(newSelectClause, newFromClause, null, null, null);
+        newSelectBlock.setSourceLocation(sourceLoc);
+        SelectSetOperation newSelectSetOp = new SelectSetOperation(new SetOperationInput(newSelectBlock, null), null);
+        newSelectSetOp.setSourceLocation(sourceLoc);
+        SelectExpression newSelectExpr = new SelectExpression(null, newSelectSetOp, null, null, true);
+        newSelectExpr.setSourceLocation(sourceLoc);
+        return newSelectExpr;
+    }
+
+    private VariableExpr newVariableExpr(VarIdentifier newFromVar, SourceLocation sourceLoc) {
+        VariableExpr varExpr = new VariableExpr(newFromVar);
+        varExpr.setSourceLocation(sourceLoc);
+        return varExpr;
+    }
+
+    private Projection createProjection(VariableExpr var, String fieldName, SourceLocation sourceLoc) {
+        Projection projection = new Projection(newVariableExpr(var.getVar(), null), fieldName, false, false);
+        projection.setSourceLocation(sourceLoc);
+        return projection;
+    }
+
+    private String generateFieldName() {
+        return SqlppVariableUtil.variableNameToDisplayedFieldName(context.newVariable().getValue());
+    }
+}
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/util/SqlppRewriteUtil.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/util/SqlppRewriteUtil.java
index 5a30b42..62acfeb 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/util/SqlppRewriteUtil.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/util/SqlppRewriteUtil.java
@@ -18,20 +18,27 @@
  */
 package org.apache.asterix.lang.sqlpp.util;
 
+import java.util.ArrayList;
 import java.util.HashSet;
+import java.util.List;
 import java.util.Map;
 import java.util.Set;
 
 import org.apache.asterix.common.exceptions.CompilationException;
+import org.apache.asterix.common.functions.FunctionSignature;
 import org.apache.asterix.lang.common.base.Expression;
 import org.apache.asterix.lang.common.base.ILangExpression;
+import org.apache.asterix.lang.common.expression.CallExpr;
+import org.apache.asterix.lang.common.expression.LiteralExpr;
 import org.apache.asterix.lang.common.expression.VariableExpr;
+import org.apache.asterix.lang.common.literal.StringLiteral;
 import org.apache.asterix.lang.common.rewrites.LangRewritingContext;
 import org.apache.asterix.lang.common.statement.Query;
 import org.apache.asterix.lang.sqlpp.visitor.CheckSubqueryVisitor;
 import org.apache.asterix.lang.sqlpp.visitor.DeepCopyVisitor;
 import org.apache.asterix.lang.sqlpp.visitor.FreeVariableVisitor;
 import org.apache.asterix.lang.sqlpp.visitor.SqlppSubstituteExpressionVisitor;
+import org.apache.asterix.om.functions.BuiltinFunctions;
 
 public class SqlppRewriteUtil {
 
@@ -75,7 +82,7 @@
      *         replacement expression.
      * @throws CompilationException
      */
-    public static Expression substituteExpression(Expression expression, Map<Expression, Expression> exprMap,
+    public static Expression substituteExpression(Expression expression, Map<? extends Expression, Expression> exprMap,
             LangRewritingContext context) throws CompilationException {
         if (exprMap.isEmpty()) {
             return expression;
@@ -90,4 +97,15 @@
         wrapper.accept(visitor, wrapper);
         return wrapper.getBody();
     }
+
+    public static Expression getFieldByName(Expression inExpr, String fieldName) {
+        LiteralExpr fieldNameExpr = new LiteralExpr(new StringLiteral(fieldName));
+        fieldNameExpr.setSourceLocation(inExpr.getSourceLocation());
+        List<Expression> argList = new ArrayList<>(2);
+        argList.add(inExpr);
+        argList.add(fieldNameExpr);
+        CallExpr callExpr = new CallExpr(new FunctionSignature(BuiltinFunctions.FIELD_ACCESS_BY_NAME), argList);
+        callExpr.setSourceLocation(inExpr.getSourceLocation());
+        return callExpr;
+    }
 }
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/DeepCopyVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/DeepCopyVisitor.java
index 684a738..51bbe98 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/DeepCopyVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/DeepCopyVisitor.java
@@ -124,7 +124,7 @@
         VariableExpr rightPositionVar = nestClause.getPositionalVariable() == null ? null
                 : (VariableExpr) nestClause.getPositionalVariable().accept(this, arg);
         Expression conditionExpresion = (Expression) nestClause.getConditionExpression().accept(this, arg);
-        NestClause copy = new NestClause(nestClause.getJoinType(), rightExpression, rightVar, rightPositionVar,
+        NestClause copy = new NestClause(nestClause.getNestType(), rightExpression, rightVar, rightPositionVar,
                 conditionExpresion);
         copy.setSourceLocation(nestClause.getSourceLocation());
         return copy;
@@ -136,7 +136,7 @@
         VariableExpr rightVar = (VariableExpr) unnestClause.getRightVariable().accept(this, arg);
         VariableExpr rightPositionVar = unnestClause.getPositionalVariable() == null ? null
                 : (VariableExpr) unnestClause.getPositionalVariable().accept(this, arg);
-        UnnestClause copy = new UnnestClause(unnestClause.getJoinType(), rightExpression, rightVar, rightPositionVar);
+        UnnestClause copy = new UnnestClause(unnestClause.getUnnestType(), rightExpression, rightVar, rightPositionVar);
         copy.setSourceLocation(unnestClause.getSourceLocation());
         return copy;
     }
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppAstPrintVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppAstPrintVisitor.java
index 038be3e..083015b 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppAstPrintVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppAstPrintVisitor.java
@@ -115,7 +115,7 @@
 
     @Override
     public Void visit(NestClause nestClause, Integer step) throws CompilationException {
-        out.println(skip(step) + nestClause.getJoinType() + " NEST");
+        out.println(skip(step) + nestClause.getNestType() + " NEST");
         nestClause.getRightExpression().accept(this, step + 1);
         out.print(skip(step + 1) + "AS ");
         nestClause.getRightVariable().accept(this, 0);
@@ -229,7 +229,7 @@
 
     @Override
     public Void visit(UnnestClause unnestClause, Integer step) throws CompilationException {
-        out.println(skip(step) + unnestClause.getJoinType() + " UNNEST");
+        out.println(skip(step) + unnestClause.getUnnestType() + " UNNEST");
         unnestClause.getRightExpression().accept(this, step + 1);
         out.print(skip(step + 1) + " AS ");
         unnestClause.getRightVariable().accept(this, 0);
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppCloneAndSubstituteVariablesVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppCloneAndSubstituteVariablesVisitor.java
index 418a960..efe1c3d 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppCloneAndSubstituteVariablesVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppCloneAndSubstituteVariablesVisitor.java
@@ -170,7 +170,7 @@
         Expression conditionExpr = (Expression) nestClause.getConditionExpression().accept(this, currentEnv).first;
 
         NestClause newNestClause =
-                new NestClause(nestClause.getJoinType(), rightExpr, newRightVar, newRightPosVar, conditionExpr);
+                new NestClause(nestClause.getNestType(), rightExpr, newRightVar, newRightPosVar, conditionExpr);
         newNestClause.setSourceLocation(nestClause.getSourceLocation());
         return new Pair<>(newNestClause, currentEnv);
     }
@@ -194,7 +194,7 @@
         }
         // The condition can refer to the newRightVar and newRightPosVar.
         UnnestClause newUnnestClause =
-                new UnnestClause(unnestClause.getJoinType(), rightExpr, newRightVar, newRightPosVar);
+                new UnnestClause(unnestClause.getUnnestType(), rightExpr, newRightVar, newRightPosVar);
         newUnnestClause.setSourceLocation(unnestClause.getSourceLocation());
         return new Pair<>(newUnnestClause, currentEnv);
     }
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppFormatPrintVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppFormatPrintVisitor.java
index 8798dfa..bdf5c9d 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppFormatPrintVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppFormatPrintVisitor.java
@@ -109,7 +109,7 @@
 
     @Override
     public Void visit(NestClause nestClause, Integer step) throws CompilationException {
-        out.print(nestClause.getJoinType());
+        out.print(nestClause.getNestType());
         nestClause.getRightExpression().accept(this, step + 2);
         out.println(skip(step + 1) + " as ");
         nestClause.getRightVariable().accept(this, step + 2);
@@ -233,7 +233,7 @@
 
     @Override
     public Void visit(UnnestClause unnestClause, Integer step) throws CompilationException {
-        out.print(unnestClause.getJoinType());
+        out.print(unnestClause.getUnnestType());
         unnestClause.getRightExpression().accept(this, step + 2);
         out.print(" as ");
         unnestClause.getRightVariable().accept(this, step + 2);
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj b/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
index b23492a..bc0c1c2 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
+++ b/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
@@ -180,6 +180,7 @@
 import org.apache.asterix.lang.sqlpp.expression.WindowExpression;
 import org.apache.asterix.lang.sqlpp.optype.JoinType;
 import org.apache.asterix.lang.sqlpp.optype.SetOpType;
+import org.apache.asterix.lang.sqlpp.optype.UnnestType;
 import org.apache.asterix.lang.sqlpp.parser.SqlppGroupingSetsParser;
 import org.apache.asterix.lang.sqlpp.parser.SqlppGroupingSetsParser.GroupingElement;
 import org.apache.asterix.lang.sqlpp.parser.SqlppGroupingSetsParser.GroupingSet;
@@ -389,7 +390,7 @@
             throw new CompilationException(ErrorCode.PARSE_ERROR, LogRedactionUtil.userData(getMessage(e)));
         } catch (Error e) {
             // this is here as the JavaCharStream that's below the lexer sometimes throws Errors that are not handled
-            // by the ANTLR-generated lexer or parser (e.g it does this for invalid backslash u + 4 hex digits escapes)
+            // by the generated lexer or parser (e.g it does this for invalid backslash u + 4 hex digits escapes)
             final String msg = e.getClass().getSimpleName() + (e.getMessage() != null ? ": " + e.getMessage() : "");
             throw new CompilationException(ErrorCode.PARSE_ERROR, LogRedactionUtil.userData(msg));
         } finally {
@@ -4025,18 +4026,17 @@
   Expression leftExpr = null;
   VariableExpr leftVar = null;
   VariableExpr posVar = null;
+  AbstractBinaryCorrelateClause correlateClause = null;
   List<AbstractBinaryCorrelateClause> correlateClauses = new ArrayList<AbstractBinaryCorrelateClause>();
 }
 {
   leftExpr = Expression() ((<AS>)? leftVar = Variable())? (<AT> posVar = Variable())?
   (
-     {JoinType joinType = JoinType.INNER; }
-     (joinType = JoinType())?
-     {
-       AbstractBinaryCorrelateClause correlateClause = null;
-     }
-     (correlateClause = JoinClause(joinType)
-      | correlateClause = UnnestClause(joinType)
+     (
+      correlateClause = JoinOrUnnestClause(JoinType.INNER, UnnestType.INNER)
+      | ( <INNER> correlateClause = JoinOrUnnestClause(JoinType.INNER, UnnestType.INNER) )
+      | ( <LEFT> ( <OUTER> )? correlateClause = JoinOrUnnestClause(JoinType.LEFTOUTER, UnnestType.LEFTOUTER) )
+      | ( <RIGHT> ( <OUTER> )? correlateClause = JoinClause(JoinType.RIGHTOUTER) )
      )
      {
         correlateClauses.add(correlateClause);
@@ -4052,6 +4052,17 @@
   }
 }
 
+AbstractBinaryCorrelateClause JoinOrUnnestClause(JoinType joinType, UnnestType unnestType) throws ParseException :
+{
+  AbstractBinaryCorrelateClause correlateClause = null;
+}
+{
+  ( correlateClause = JoinClause(joinType) | correlateClause = UnnestClause(unnestType) )
+  {
+    return correlateClause;
+  }
+}
+
 JoinClause JoinClause(JoinType joinType) throws ParseException :
 {
     Token startToken = null;
@@ -4063,15 +4074,15 @@
 {
   <JOIN> { startToken = token; } rightExpr = Expression() ((<AS>)? rightVar = Variable())? (<AT> posVar = Variable())? <ON> conditionExpr = Expression()
   {
-    if(rightVar==null){
-        rightVar = ExpressionToVariableUtil.getGeneratedVariable(rightExpr, true);
+    if (rightVar == null) {
+      rightVar = ExpressionToVariableUtil.getGeneratedVariable(rightExpr, true);
     }
     JoinClause joinClause = new JoinClause(joinType, rightExpr, rightVar, posVar, conditionExpr);
     return addSourceLocation(joinClause, startToken);
   }
 }
 
-UnnestClause UnnestClause(JoinType joinType) throws ParseException :
+UnnestClause UnnestClause(UnnestType unnestType) throws ParseException :
 {
     Token startToken = null;
     Expression rightExpr;
@@ -4084,22 +4095,11 @@
     if (rightVar == null) {
       rightVar = ExpressionToVariableUtil.getGeneratedVariable(rightExpr, true);
     }
-    UnnestClause unnestClause = new UnnestClause(joinType, rightExpr, rightVar, posVar);
+    UnnestClause unnestClause = new UnnestClause(unnestType, rightExpr, rightVar, posVar);
     return addSourceLocation(unnestClause, startToken);
   }
 }
 
-JoinType JoinType() throws ParseException :
-{
-   JoinType joinType = JoinType.INNER;
-}
-{
-     (<INNER>|<LEFT> (<OUTER>)? {joinType = JoinType.LEFTOUTER; })
-     {
-       return joinType;
-     }
-}
-
 List<LetClause> LetClause() throws ParseException:
 {
     List<LetClause> letList = new ArrayList<LetClause>();
@@ -4575,6 +4575,7 @@
   | <REFRESH : "refresh">
   | <RETURN : "return">
   | <RETURNING : "returning">
+  | <RIGHT : "right">
   | <RTREE : "rtree">
   | <RUN : "run">
   | <SATISFIES : "satisfies">