diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/JoinEnum.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/JoinEnum.java
index b1a6fb6..67fcc7e 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/JoinEnum.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/JoinEnum.java
@@ -246,7 +246,7 @@
         return eqPredFound ? andExpr : null;
     }
 
-    public HashJoinExpressionAnnotation.BuildSide findHashJoinHint(List<Integer> newJoinConditions) {
+    public HashJoinExpressionAnnotation findHashJoinHint(List<Integer> newJoinConditions) {
         for (int i : newJoinConditions) {
             JoinCondition jc = joinConditions.get(i);
             if (jc.comparisonType != JoinCondition.comparisonOp.OP_EQ) {
@@ -257,14 +257,14 @@
                 AbstractFunctionCallExpression AFCexpr = (AbstractFunctionCallExpression) expr;
                 HashJoinExpressionAnnotation hjea = AFCexpr.getAnnotation(HashJoinExpressionAnnotation.class);
                 if (hjea != null) {
-                    return hjea.getBuildSide();
+                    return hjea;
                 }
             }
         }
         return null;
     }
 
-    public BroadcastExpressionAnnotation.BroadcastSide findBroadcastHashJoinHint(List<Integer> newJoinConditions) {
+    public BroadcastExpressionAnnotation findBroadcastHashJoinHint(List<Integer> newJoinConditions) {
         for (int i : newJoinConditions) {
             JoinCondition jc = joinConditions.get(i);
             if (jc.comparisonType != JoinCondition.comparisonOp.OP_EQ) {
@@ -275,14 +275,14 @@
                 AbstractFunctionCallExpression AFCexpr = (AbstractFunctionCallExpression) expr;
                 BroadcastExpressionAnnotation bcasthjea = AFCexpr.getAnnotation(BroadcastExpressionAnnotation.class);
                 if (bcasthjea != null) {
-                    return bcasthjea.getBroadcastSide();
+                    return bcasthjea;
                 }
             }
         }
         return null;
     }
 
-    public boolean findNLJoinHint(List<Integer> newJoinConditions) {
+    public IndexedNLJoinExpressionAnnotation findNLJoinHint(List<Integer> newJoinConditions) {
         for (int i : newJoinConditions) {
             JoinCondition jc = joinConditions.get(i);
             ILogicalExpression expr = jc.joinCondition;
@@ -291,18 +291,18 @@
                 IndexedNLJoinExpressionAnnotation inljea =
                         AFCexpr.getAnnotation(IndexedNLJoinExpressionAnnotation.class);
                 if (inljea != null) {
-                    return true;
+                    return inljea;
                 }
             }
         }
-        return false;
+        return null;
     }
 
     public int findJoinNodeIndexByName(String name) {
         for (int i = 1; i <= this.numberOfTerms; i++) {
             if (name.equals(jnArray[i].datasetNames.get(0))) {
                 return i;
-            } else if (name.equals(jnArray[i].alias)) {
+            } else if (name.equals(jnArray[i].aliases.get(0))) {
                 return i;
             }
         }
@@ -582,16 +582,19 @@
                     jn.datasetNames.addAll(jnI.datasetNames);
                     jn.datasetNames.addAll(jnJ.datasetNames);
                     Collections.sort(jn.datasetNames);
+                    jn.aliases = new ArrayList<>();
+                    jn.aliases.addAll(jnI.aliases);
+                    jn.aliases.addAll(jnJ.aliases);
+                    Collections.sort(jn.aliases);
                     jn.size = jnI.size + jnJ.size;
                     jn.cardinality = jn.computeJoinCardinality();
-
                 } else {
                     addPlansToThisJn = jnNewBits.jnIndex;
                 }
 
                 JoinNode jnIJ = jnArray[addPlansToThisJn];
                 jnIJ.jnArrayIndex = addPlansToThisJn;
-                jnIJ.addMultiDatasetPlans(jnI, jnJ, level);
+                jnIJ.addMultiDatasetPlans(jnI, jnJ);
                 if (forceJoinOrderMode) {
                     break;
                 }
@@ -668,7 +671,7 @@
             DataSourceScanOperator scanOp = emptyTupleAndDataSourceOps.get(i - 1).getSecond();
             if (scanOp != null) {
                 DataSourceId id = (DataSourceId) scanOp.getDataSource().getId();
-                jn.alias = findAlias(scanOp);
+                jn.aliases = new ArrayList<>(Collections.singleton(findAlias(scanOp)));
                 jn.datasetNames = new ArrayList<>(Collections.singleton(id.getDatasourceName()));
                 Index.SampleIndexDetails idxDetails;
                 Index index = stats.findSampleIndex(scanOp, optCtx);
@@ -695,6 +698,7 @@
             } else {
                 // could be unnest or assign
                 jn.datasetNames = new ArrayList<>(Collections.singleton("unnestOrAssign"));
+                jn.aliases = new ArrayList<>(Collections.singleton("unnestOrAssign"));
                 jn.origCardinality = jn.cardinality = findInListCard(leafInput);
                 // just a guess
                 jn.size = 10;
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/JoinNode.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/JoinNode.java
index cf4fcad..b2ac9c1 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/JoinNode.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/JoinNode.java
@@ -27,6 +27,7 @@
 import java.util.Objects;
 import java.util.TreeMap;
 
+import org.apache.asterix.common.annotations.IndexedNLJoinExpressionAnnotation;
 import org.apache.asterix.common.annotations.SkipSecondaryIndexSearchExpressionAnnotation;
 import org.apache.asterix.metadata.entities.Index;
 import org.apache.asterix.om.functions.BuiltinFunctions;
@@ -68,7 +69,7 @@
     protected int datasetBits; // this is bitmap of all the keyspaceBits present in this joinNode
     protected List<Integer> datasetIndexes;
     protected List<String> datasetNames;
-    protected String alias;
+    protected List<String> aliases;
     protected int cheapestPlanIndex;
     protected ICost cheapestPlanCost;
     protected double origCardinality; // without any selections
@@ -171,8 +172,8 @@
         return rightJn;
     }
 
-    public String getAlias() {
-        return alias;
+    public List<String> getAliases() {
+        return aliases;
     }
 
     public List<String> getDatasetNames() {
@@ -381,7 +382,8 @@
         }
     }
 
-    protected int buildHashJoinPlan(JoinNode leftJn, JoinNode rightJn, ILogicalExpression hashJoinExpr) {
+    protected int buildHashJoinPlan(JoinNode leftJn, JoinNode rightJn, ILogicalExpression hashJoinExpr,
+            HashJoinExpressionAnnotation hintHashJoin) {
         List<PlanNode> allPlans = joinEnum.allPlans;
         PlanNode pn;
         ICost hjCost, childCosts, totalCost;
@@ -405,7 +407,8 @@
             return PlanNode.NO_PLAN;
         }
 
-        if (rightJn.cardinality * rightJn.size <= leftJn.cardinality * leftJn.size || joinEnum.forceJoinOrderMode
+        if (rightJn.cardinality * rightJn.size <= leftJn.cardinality * leftJn.size || hintHashJoin != null
+                || joinEnum.forceJoinOrderMode
                 || !joinEnum.queryPlanShape.equals(AlgebricksConfig.QUERY_PLAN_SHAPE_ZIGZAG)) {
             // We want to build with the smaller side.
             hjCost = joinEnum.getCostMethodsHandle().costHashJoin(this);
@@ -419,6 +422,9 @@
                 pn.planIndexes[0] = leftPlan;
                 pn.planIndexes[1] = rightPlan;
                 pn.joinOp = PlanNode.JoinMethod.HYBRID_HASH_JOIN; // need to check that all the conditions have equality predicates ONLY.
+                if (hintHashJoin != null) {
+                    hintHashJoin.setBuildSide(HashJoinExpressionAnnotation.BuildSide.RIGHT);
+                }
                 pn.side = HashJoinExpressionAnnotation.BuildSide.RIGHT;
                 pn.joinExpr = hashJoinExpr;
                 pn.opCost = hjCost;
@@ -437,7 +443,8 @@
         return PlanNode.NO_PLAN;
     }
 
-    protected int buildBroadcastHashJoinPlan(JoinNode leftJn, JoinNode rightJn, ILogicalExpression hashJoinExpr) {
+    protected int buildBroadcastHashJoinPlan(JoinNode leftJn, JoinNode rightJn, ILogicalExpression hashJoinExpr,
+            BroadcastExpressionAnnotation hintBroadcastHashJoin) {
         List<PlanNode> allPlans = joinEnum.allPlans;
         PlanNode pn;
         ICost bcastHjCost, childCosts, totalCost;
@@ -461,7 +468,8 @@
             return PlanNode.NO_PLAN;
         }
 
-        if (rightJn.cardinality * rightJn.size <= leftJn.cardinality * leftJn.size || joinEnum.forceJoinOrderMode
+        if (rightJn.cardinality * rightJn.size <= leftJn.cardinality * leftJn.size || hintBroadcastHashJoin != null
+                || joinEnum.forceJoinOrderMode
                 || !joinEnum.queryPlanShape.equals(AlgebricksConfig.QUERY_PLAN_SHAPE_ZIGZAG)) {
             // We want to broadcast and build with the smaller side.
             bcastHjCost = joinEnum.getCostMethodsHandle().costBroadcastHashJoin(this);
@@ -475,6 +483,9 @@
                 pn.planIndexes[0] = leftPlan;
                 pn.planIndexes[1] = rightPlan;
                 pn.joinOp = PlanNode.JoinMethod.BROADCAST_HASH_JOIN; // need to check that all the conditions have equality predicates ONLY.
+                if (hintBroadcastHashJoin != null) {
+                    hintBroadcastHashJoin.setBroadcastSide(BroadcastExpressionAnnotation.BroadcastSide.RIGHT);
+                }
                 pn.side = HashJoinExpressionAnnotation.BuildSide.RIGHT;
                 pn.joinExpr = hashJoinExpr;
                 pn.opCost = bcastHjCost;
@@ -597,8 +608,7 @@
         return PlanNode.NO_PLAN;
     }
 
-    protected Pair<Integer, ICost> addMultiDatasetPlans(JoinNode leftJn, JoinNode rightJn, int level)
-            throws AlgebricksException {
+    protected Pair<Integer, ICost> addMultiDatasetPlans(JoinNode leftJn, JoinNode rightJn) throws AlgebricksException {
         this.leftJn = leftJn;
         this.rightJn = rightJn;
         ICost noJoinCost = joinEnum.getCostHandle().maxCost();
@@ -632,26 +642,37 @@
         hjPlan = commutativeHjPlan = bcastHjPlan =
                 commutativeBcastHjPlan = nljPlan = commutativeNljPlan = cpPlan = commutativeCpPlan = PlanNode.NO_PLAN;
 
-        HashJoinExpressionAnnotation.BuildSide hintHashJoin = joinEnum.findHashJoinHint(newJoinConditions);
-        BroadcastExpressionAnnotation.BroadcastSide hintBroadcastHashJoin = null;
-        boolean hintNLJoin = false;
-        if (hintHashJoin == null) {
-            hintBroadcastHashJoin = joinEnum.findBroadcastHashJoinHint(newJoinConditions);
-            if (hintBroadcastHashJoin == null) {
-                hintNLJoin = joinEnum.findNLJoinHint(newJoinConditions);
-            }
-        }
+        HashJoinExpressionAnnotation hintHashJoin = joinEnum.findHashJoinHint(newJoinConditions);
+        BroadcastExpressionAnnotation hintBroadcastHashJoin = joinEnum.findBroadcastHashJoinHint(newJoinConditions);;
+        IndexedNLJoinExpressionAnnotation hintNLJoin = joinEnum.findNLJoinHint(newJoinConditions);
 
         if (leftJn.cheapestPlanIndex == PlanNode.NO_PLAN || rightJn.cheapestPlanIndex == PlanNode.NO_PLAN) {
             return new Pair<>(PlanNode.NO_PLAN, noJoinCost);
         }
 
         if (hintHashJoin != null) {
-            hjPlan = buildHashJoinPlan(leftJn, rightJn, hashJoinExpr);
-            if (!joinEnum.forceJoinOrderMode && hintHashJoin != HashJoinExpressionAnnotation.BuildSide.RIGHT) {
-                commutativeHjPlan = buildHashJoinPlan(rightJn, leftJn, hashJoinExpr);
+            boolean build = (hintHashJoin.getBuildOrProbe() == HashJoinExpressionAnnotation.BuildOrProbe.BUILD);
+            boolean probe = (hintHashJoin.getBuildOrProbe() == HashJoinExpressionAnnotation.BuildOrProbe.PROBE);
+            boolean validBuildOrProbeObject = false;
+            String buildOrProbeObject = hintHashJoin.getName();
+            if (buildOrProbeObject != null && (rightJn.datasetNames.contains(buildOrProbeObject)
+                    || rightJn.aliases.contains(buildOrProbeObject) || leftJn.datasetNames.contains(buildOrProbeObject)
+                    || leftJn.aliases.contains(buildOrProbeObject))) {
+                validBuildOrProbeObject = true;
             }
-            if (hjPlan == PlanNode.NO_PLAN && commutativeHjPlan == PlanNode.NO_PLAN) {
+            if (validBuildOrProbeObject) {
+                if ((build && (rightJn.datasetNames.contains(buildOrProbeObject)
+                        || rightJn.aliases.contains(buildOrProbeObject)))
+                        || (probe && (leftJn.datasetNames.contains(buildOrProbeObject)
+                                || leftJn.aliases.contains(buildOrProbeObject)))) {
+                    hjPlan = buildHashJoinPlan(leftJn, rightJn, hashJoinExpr, hintHashJoin);
+                } else if ((build && (leftJn.datasetNames.contains(buildOrProbeObject)
+                        || leftJn.aliases.contains(buildOrProbeObject)))
+                        || (probe && (rightJn.datasetNames.contains(buildOrProbeObject)
+                                || rightJn.aliases.contains(buildOrProbeObject)))) {
+                    commutativeHjPlan = buildHashJoinPlan(rightJn, leftJn, hashJoinExpr, hintHashJoin);
+                }
+            } else {
                 // Hints are attached to predicates, so newJoinConditions should not be empty, but adding the check to be safe.
                 if (!joinEnum.getJoinConditions().isEmpty() && !newJoinConditions.isEmpty()) {
                     IWarningCollector warningCollector = joinEnum.optCtx.getWarningCollector();
@@ -659,12 +680,17 @@
                         warningCollector.warn(Warning.of(
                                 joinEnum.getJoinConditions().get(newJoinConditions.get(0)).joinCondition
                                         .getSourceLocation(),
-                                ErrorCode.INAPPLICABLE_HINT, "Hash join hint not applicable and was ignored"));
+                                ErrorCode.INAPPLICABLE_HINT, "hash join",
+                                (build ? "build " : "probe ") + "with " + buildOrProbeObject));
                     }
                 }
-                bcastHjPlan = buildBroadcastHashJoinPlan(leftJn, rightJn, hashJoinExpr);
+                hjPlan = buildHashJoinPlan(leftJn, rightJn, hashJoinExpr, null);
                 if (!joinEnum.forceJoinOrderMode) {
-                    commutativeBcastHjPlan = buildBroadcastHashJoinPlan(rightJn, leftJn, hashJoinExpr);
+                    commutativeHjPlan = buildHashJoinPlan(rightJn, leftJn, hashJoinExpr, null);
+                }
+                bcastHjPlan = buildBroadcastHashJoinPlan(leftJn, rightJn, hashJoinExpr, null);
+                if (!joinEnum.forceJoinOrderMode) {
+                    commutativeBcastHjPlan = buildBroadcastHashJoinPlan(rightJn, leftJn, hashJoinExpr, null);
                 }
                 nljPlan = buildNLJoinPlan(leftJn, rightJn, nestedLoopJoinExpr);
                 if (!joinEnum.forceJoinOrderMode) {
@@ -676,12 +702,27 @@
                 }
             }
         } else if (hintBroadcastHashJoin != null) {
-            bcastHjPlan = buildBroadcastHashJoinPlan(leftJn, rightJn, hashJoinExpr);
-            if (!joinEnum.forceJoinOrderMode
-                    && hintBroadcastHashJoin != BroadcastExpressionAnnotation.BroadcastSide.RIGHT) {
-                commutativeBcastHjPlan = buildBroadcastHashJoinPlan(rightJn, leftJn, hashJoinExpr);
+            boolean validBroadcastObject = false;
+            String broadcastObject = hintBroadcastHashJoin.getName();
+            if (broadcastObject != null && (rightJn.datasetNames.contains(broadcastObject)
+                    || rightJn.aliases.contains(broadcastObject) || leftJn.datasetNames.contains(broadcastObject)
+                    || leftJn.aliases.contains(broadcastObject))) {
+                validBroadcastObject = true;
             }
-            if (bcastHjPlan == PlanNode.NO_PLAN && commutativeBcastHjPlan == PlanNode.NO_PLAN) {
+            if (validBroadcastObject) {
+                if (rightJn.datasetNames.contains(broadcastObject) || rightJn.aliases.contains(broadcastObject)) {
+                    bcastHjPlan = buildBroadcastHashJoinPlan(leftJn, rightJn, hashJoinExpr, hintBroadcastHashJoin);
+                } else if (leftJn.datasetNames.contains(broadcastObject) || leftJn.aliases.contains(broadcastObject)) {
+                    commutativeBcastHjPlan =
+                            buildBroadcastHashJoinPlan(rightJn, leftJn, hashJoinExpr, hintBroadcastHashJoin);
+                }
+            } else if (broadcastObject == null) {
+                bcastHjPlan = buildBroadcastHashJoinPlan(leftJn, rightJn, hashJoinExpr, hintBroadcastHashJoin);
+                if (!joinEnum.forceJoinOrderMode) {
+                    commutativeBcastHjPlan =
+                            buildBroadcastHashJoinPlan(rightJn, leftJn, hashJoinExpr, hintBroadcastHashJoin);
+                }
+            } else {
                 // Hints are attached to predicates, so newJoinConditions should not be empty, but adding the check to be safe.
                 if (!joinEnum.getJoinConditions().isEmpty() && !newJoinConditions.isEmpty()) {
                     IWarningCollector warningCollector = joinEnum.optCtx.getWarningCollector();
@@ -689,14 +730,17 @@
                         warningCollector.warn(Warning.of(
                                 joinEnum.getJoinConditions().get(newJoinConditions.get(0)).joinCondition
                                         .getSourceLocation(),
-                                ErrorCode.INAPPLICABLE_HINT,
-                                "Broadcast hash join hint not applicable and was ignored"));
+                                ErrorCode.INAPPLICABLE_HINT, "broadcast hash join", "broadcast " + broadcastObject));
                     }
                 }
 
-                hjPlan = buildHashJoinPlan(leftJn, rightJn, hashJoinExpr);
+                hjPlan = buildHashJoinPlan(leftJn, rightJn, hashJoinExpr, null);
                 if (!joinEnum.forceJoinOrderMode) {
-                    commutativeHjPlan = buildHashJoinPlan(rightJn, leftJn, hashJoinExpr);
+                    commutativeHjPlan = buildHashJoinPlan(rightJn, leftJn, hashJoinExpr, null);
+                }
+                bcastHjPlan = buildBroadcastHashJoinPlan(leftJn, rightJn, hashJoinExpr, null);
+                if (!joinEnum.forceJoinOrderMode) {
+                    commutativeBcastHjPlan = buildBroadcastHashJoinPlan(rightJn, leftJn, hashJoinExpr, null);
                 }
                 nljPlan = buildNLJoinPlan(leftJn, rightJn, nestedLoopJoinExpr);
                 if (!joinEnum.forceJoinOrderMode) {
@@ -707,7 +751,7 @@
                     commutativeCpPlan = buildCPJoinPlan(rightJn, leftJn, hashJoinExpr, nestedLoopJoinExpr);
                 }
             }
-        } else if (hintNLJoin) {
+        } else if (hintNLJoin != null) {
             nljPlan = buildNLJoinPlan(leftJn, rightJn, nestedLoopJoinExpr);
             if (!joinEnum.forceJoinOrderMode) {
                 commutativeNljPlan = buildNLJoinPlan(rightJn, leftJn, nestedLoopJoinExpr);
@@ -720,16 +764,16 @@
                         warningCollector.warn(Warning.of(
                                 joinEnum.getJoinConditions().get(newJoinConditions.get(0)).joinCondition
                                         .getSourceLocation(),
-                                ErrorCode.INAPPLICABLE_HINT, "Index nested join hint not applicable and was ignored"));
+                                ErrorCode.INAPPLICABLE_HINT, "index nested loop join", "ignored"));
                     }
                 }
-                hjPlan = buildHashJoinPlan(leftJn, rightJn, hashJoinExpr);
+                hjPlan = buildHashJoinPlan(leftJn, rightJn, hashJoinExpr, null);
                 if (!joinEnum.forceJoinOrderMode) {
-                    commutativeHjPlan = buildHashJoinPlan(rightJn, leftJn, hashJoinExpr);
+                    commutativeHjPlan = buildHashJoinPlan(rightJn, leftJn, hashJoinExpr, null);
                 }
-                bcastHjPlan = buildBroadcastHashJoinPlan(leftJn, rightJn, hashJoinExpr);
+                bcastHjPlan = buildBroadcastHashJoinPlan(leftJn, rightJn, hashJoinExpr, null);
                 if (!joinEnum.forceJoinOrderMode) {
-                    commutativeBcastHjPlan = buildBroadcastHashJoinPlan(rightJn, leftJn, hashJoinExpr);
+                    commutativeBcastHjPlan = buildBroadcastHashJoinPlan(rightJn, leftJn, hashJoinExpr, null);
                 }
                 cpPlan = buildCPJoinPlan(leftJn, rightJn, hashJoinExpr, nestedLoopJoinExpr);
                 if (!joinEnum.forceJoinOrderMode) {
@@ -737,13 +781,13 @@
                 }
             }
         } else {
-            hjPlan = buildHashJoinPlan(leftJn, rightJn, hashJoinExpr);
+            hjPlan = buildHashJoinPlan(leftJn, rightJn, hashJoinExpr, null);
             if (!joinEnum.forceJoinOrderMode) {
-                commutativeHjPlan = buildHashJoinPlan(rightJn, leftJn, hashJoinExpr);
+                commutativeHjPlan = buildHashJoinPlan(rightJn, leftJn, hashJoinExpr, null);
             }
-            bcastHjPlan = buildBroadcastHashJoinPlan(leftJn, rightJn, hashJoinExpr);
+            bcastHjPlan = buildBroadcastHashJoinPlan(leftJn, rightJn, hashJoinExpr, null);
             if (!joinEnum.forceJoinOrderMode) {
-                commutativeBcastHjPlan = buildBroadcastHashJoinPlan(rightJn, leftJn, hashJoinExpr);
+                commutativeBcastHjPlan = buildBroadcastHashJoinPlan(rightJn, leftJn, hashJoinExpr, null);
             }
             nljPlan = buildNLJoinPlan(leftJn, rightJn, nestedLoopJoinExpr);
             if (!joinEnum.forceJoinOrderMode) {
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/broadcast_hint_1.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/broadcast_join_hint/broadcast_join_hint_1.sqlpp
similarity index 100%
rename from asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/broadcast_hint_1.sqlpp
rename to asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/broadcast_join_hint/broadcast_join_hint_1.sqlpp
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/broadcast_hint_2.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/broadcast_join_hint/broadcast_join_hint_2.sqlpp
similarity index 100%
rename from asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/broadcast_hint_2.sqlpp
rename to asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/broadcast_join_hint/broadcast_join_hint_2.sqlpp
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/broadcast_hint_3.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/broadcast_join_hint/broadcast_join_hint_3.sqlpp
similarity index 100%
rename from asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/broadcast_hint_3.sqlpp
rename to asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/broadcast_join_hint/broadcast_join_hint_3.sqlpp
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/broadcast_join_hint/broadcast_join_hint_4.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/broadcast_join_hint/broadcast_join_hint_4.sqlpp
new file mode 100644
index 0000000..39a04ec
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/broadcast_join_hint/broadcast_join_hint_4.sqlpp
@@ -0,0 +1,58 @@
+/*
+ * 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.
+ */
+/*
+* Description  : Test enhanced broadcast join hint
+*                /*+ hash-bcast */
+* Expected Res : Success
+* Date         : 11/20/2022
+*/
+
+DROP dataverse tpch IF EXISTS;
+CREATE  dataverse tpch;
+
+USE tpch;
+
+CREATE TYPE OrderType AS {
+  o_orderkey : integer
+};
+
+CREATE TYPE CustomerType AS {
+  c_custkey : integer
+};
+
+CREATE TYPE NationType AS {
+  n_nationkey : integer
+};
+
+CREATE DATASET orders(OrderType) PRIMARY KEY o_orderkey;
+
+CREATE DATASET customer(CustomerType) PRIMARY KEY c_custkey;
+
+CREATE DATASET nation(NationType) PRIMARY KEY n_nationkey;
+
+SELECT COUNT(*)
+FROM orders o,
+            (
+              SELECT *
+              FROM customer c,
+                            nation n
+              WHERE c.c_nationkey = n.n_nationkey) cn
+WHERE cn.c_custkey /*+ hash-bcast */ = o.o_custkey;
+
+
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/broadcast_join_hint/broadcast_join_hint_5.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/broadcast_join_hint/broadcast_join_hint_5.sqlpp
new file mode 100644
index 0000000..e6d50ca
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/broadcast_join_hint/broadcast_join_hint_5.sqlpp
@@ -0,0 +1,58 @@
+/*
+ * 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.
+ */
+/*
+* Description  : Test enhanced broadcast join hint
+*                /*+ hash-bcast (<collection_name>) */
+* Expected Res : Success
+* Date         : 11/20/2022
+*/
+
+DROP dataverse tpch IF EXISTS;
+CREATE  dataverse tpch;
+
+USE tpch;
+
+CREATE TYPE OrderType AS {
+  o_orderkey : integer
+};
+
+CREATE TYPE CustomerType AS {
+  c_custkey : integer
+};
+
+CREATE TYPE NationType AS {
+  n_nationkey : integer
+};
+
+CREATE DATASET orders(OrderType) PRIMARY KEY o_orderkey;
+
+CREATE DATASET customer(CustomerType) PRIMARY KEY c_custkey;
+
+CREATE DATASET nation(NationType) PRIMARY KEY n_nationkey;
+
+SELECT COUNT(*)
+FROM orders o,
+            (
+              SELECT *
+              FROM customer c,
+                            nation n
+              WHERE c.c_nationkey = n.n_nationkey) cn
+WHERE cn.c_custkey /*+ hash-bcast (o) */ = o.o_custkey;
+
+
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/broadcast_join_hint/broadcast_join_hint_6.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/broadcast_join_hint/broadcast_join_hint_6.sqlpp
new file mode 100644
index 0000000..bf700cd
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/broadcast_join_hint/broadcast_join_hint_6.sqlpp
@@ -0,0 +1,57 @@
+/*
+ * 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.
+ */
+/*
+* Description  : Test enhanced broadcast join hint
+*                /*+ hash-bcast */
+*                /*+ hash-bcast (<collection_name>) */
+* Expected Res : Success
+* Date         : 11/20/2022
+*/
+
+DROP dataverse tpch IF EXISTS;
+CREATE  dataverse tpch;
+
+USE tpch;
+
+CREATE TYPE OrderType AS {
+  o_orderkey : integer
+};
+
+CREATE TYPE CustomerType AS {
+  c_custkey : integer
+};
+
+CREATE TYPE NationType AS {
+  n_nationkey : integer
+};
+
+CREATE DATASET orders(OrderType) PRIMARY KEY o_orderkey;
+
+CREATE DATASET customer(CustomerType) PRIMARY KEY c_custkey;
+
+CREATE DATASET nation(NationType) PRIMARY KEY n_nationkey;
+
+SELECT COUNT(*)
+FROM orders o,
+            (
+              SELECT *
+              FROM customer c,
+                            nation n
+              WHERE c.c_nationkey = n.n_nationkey) cn
+WHERE cn.c_custkey /*+ hash-bcast (c) */ = o.o_custkey;
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/broadcast_join_hint/broadcast_join_hint_7.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/broadcast_join_hint/broadcast_join_hint_7.sqlpp
new file mode 100644
index 0000000..ab9c099
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/broadcast_join_hint/broadcast_join_hint_7.sqlpp
@@ -0,0 +1,59 @@
+/*
+ * 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.
+ */
+/*
+* Description  : Test enhanced broadcast join hint
+*                /*+ hash-bcast */
+*                /*+ hash-bcast (<collection_name>) */
+* Expected Res : Success
+* Date         : 11/20/2022
+*/
+
+DROP dataverse tpch IF EXISTS;
+CREATE  dataverse tpch;
+
+USE tpch;
+
+CREATE TYPE OrderType AS {
+  o_orderkey : integer
+};
+
+CREATE TYPE CustomerType AS {
+  c_custkey : integer
+};
+
+CREATE TYPE NationType AS {
+  n_nationkey : integer
+};
+
+CREATE DATASET orders(OrderType) PRIMARY KEY o_orderkey;
+
+CREATE DATASET customer(CustomerType) PRIMARY KEY c_custkey;
+
+CREATE DATASET nation(NationType) PRIMARY KEY n_nationkey;
+
+SELECT COUNT(*)
+FROM orders o,
+            (
+              SELECT *
+              FROM customer c,
+                            nation n
+              WHERE c.c_nationkey = n.n_nationkey) cn
+WHERE cn.c_custkey /*+ hash-bcast (n) */ = o.o_custkey;
+
+
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/hashjoin_hint/hashjoin_hint_1.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/hashjoin_hint/hashjoin_hint_1.sqlpp
new file mode 100644
index 0000000..de52a17
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/hashjoin_hint/hashjoin_hint_1.sqlpp
@@ -0,0 +1,58 @@
+/*
+ * 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.
+ */
+/*
+* Description  : Test hash join hint
+*                /*+ hashjoin build (<collection_name>) */
+*                /*+ hashjoin probe (<collection_name>) */
+* Expected Res : Success
+* Date         : 11/20/2022
+*/
+
+DROP dataverse tpch IF EXISTS;
+CREATE  dataverse tpch;
+
+USE tpch;
+
+
+CREATE TYPE OrderType AS {
+  o_orderkey : integer
+};
+
+CREATE TYPE CustomerType AS {
+  c_custkey : integer
+};
+
+CREATE TYPE NationType AS {
+  n_nationkey : integer
+};
+
+CREATE DATASET orders(OrderType) PRIMARY KEY o_orderkey;
+
+CREATE DATASET customer(CustomerType) PRIMARY KEY c_custkey;
+
+CREATE DATASET nation(NationType) PRIMARY KEY n_nationkey;
+
+SELECT COUNT(*)
+FROM orders o,
+            (
+              SELECT *
+              FROM customer c,
+                            nation n
+              WHERE c.c_nationkey = n.n_nationkey) cn
+WHERE cn.c_custkey /*+ hashjoin build (o) */ = o.o_custkey;
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/hashjoin_hint/hashjoin_hint_2.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/hashjoin_hint/hashjoin_hint_2.sqlpp
new file mode 100644
index 0000000..b961d3a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/hashjoin_hint/hashjoin_hint_2.sqlpp
@@ -0,0 +1,58 @@
+/*
+ * 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.
+ */
+/*
+* Description  : Test hash join hint
+*                /*+ hashjoin build (<collection_name>) */
+*                /*+ hashjoin probe (<collection_name>) */
+* Expected Res : Success
+* Date         : 11/20/2022
+*/
+
+DROP dataverse tpch IF EXISTS;
+CREATE  dataverse tpch;
+
+USE tpch;
+
+
+CREATE TYPE OrderType AS {
+  o_orderkey : integer
+};
+
+CREATE TYPE CustomerType AS {
+  c_custkey : integer
+};
+
+CREATE TYPE NationType AS {
+  n_nationkey : integer
+};
+
+CREATE DATASET orders(OrderType) PRIMARY KEY o_orderkey;
+
+CREATE DATASET customer(CustomerType) PRIMARY KEY c_custkey;
+
+CREATE DATASET nation(NationType) PRIMARY KEY n_nationkey;
+
+SELECT COUNT(*)
+FROM orders o,
+            (
+              SELECT *
+              FROM customer c,
+                            nation n
+              WHERE c.c_nationkey = n.n_nationkey) cn
+WHERE cn.c_custkey /*+ hashjoin build (c) */ = o.o_custkey;
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/hashjoin_hint/hashjoin_hint_3.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/hashjoin_hint/hashjoin_hint_3.sqlpp
new file mode 100644
index 0000000..67f074f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/hashjoin_hint/hashjoin_hint_3.sqlpp
@@ -0,0 +1,58 @@
+/*
+ * 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.
+ */
+/*
+* Description  : Test hash join hint
+*                /*+ hashjoin build (<collection_name>) */
+*                /*+ hashjoin probe (<collection_name>) */
+* Expected Res : Success
+* Date         : 11/20/2022
+*/
+
+DROP dataverse tpch IF EXISTS;
+CREATE  dataverse tpch;
+
+USE tpch;
+
+
+CREATE TYPE OrderType AS {
+  o_orderkey : integer
+};
+
+CREATE TYPE CustomerType AS {
+  c_custkey : integer
+};
+
+CREATE TYPE NationType AS {
+  n_nationkey : integer
+};
+
+CREATE DATASET orders(OrderType) PRIMARY KEY o_orderkey;
+
+CREATE DATASET customer(CustomerType) PRIMARY KEY c_custkey;
+
+CREATE DATASET nation(NationType) PRIMARY KEY n_nationkey;
+
+SELECT COUNT(*)
+FROM orders o,
+            (
+              SELECT *
+              FROM customer c,
+                            nation n
+              WHERE c.c_nationkey = n.n_nationkey) cn
+WHERE cn.c_custkey /*+ hashjoin build (n) */ = o.o_custkey;
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/hashjoin_hint/hashjoin_hint_4.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/hashjoin_hint/hashjoin_hint_4.sqlpp
new file mode 100644
index 0000000..47cf50c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/hashjoin_hint/hashjoin_hint_4.sqlpp
@@ -0,0 +1,58 @@
+/*
+ * 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.
+ */
+/*
+* Description  : Test hash join hint
+*                /*+ hashjoin build (<collection_name>) */
+*                /*+ hashjoin probe (<collection_name>) */
+* Expected Res : Success
+* Date         : 11/20/2022
+*/
+
+DROP dataverse tpch IF EXISTS;
+CREATE  dataverse tpch;
+
+USE tpch;
+
+
+CREATE TYPE OrderType AS {
+  o_orderkey : integer
+};
+
+CREATE TYPE CustomerType AS {
+  c_custkey : integer
+};
+
+CREATE TYPE NationType AS {
+  n_nationkey : integer
+};
+
+CREATE DATASET orders(OrderType) PRIMARY KEY o_orderkey;
+
+CREATE DATASET customer(CustomerType) PRIMARY KEY c_custkey;
+
+CREATE DATASET nation(NationType) PRIMARY KEY n_nationkey;
+
+SELECT COUNT(*)
+FROM orders o,
+            (
+              SELECT *
+              FROM customer c,
+                            nation n
+              WHERE c.c_nationkey = n.n_nationkey) cn
+WHERE cn.c_custkey /*+ hashjoin probe (o) */ = o.o_custkey;
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/hashjoin_hint/hashjoin_hint_5.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/hashjoin_hint/hashjoin_hint_5.sqlpp
new file mode 100644
index 0000000..31af68b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/hashjoin_hint/hashjoin_hint_5.sqlpp
@@ -0,0 +1,58 @@
+/*
+ * 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.
+ */
+/*
+* Description  : Test hash join hint
+*                /*+ hashjoin build (<collection_name>) */
+*                /*+ hashjoin probe (<collection_name>) */
+* Expected Res : Success
+* Date         : 11/20/2022
+*/
+
+DROP dataverse tpch IF EXISTS;
+CREATE  dataverse tpch;
+
+USE tpch;
+
+
+CREATE TYPE OrderType AS {
+  o_orderkey : integer
+};
+
+CREATE TYPE CustomerType AS {
+  c_custkey : integer
+};
+
+CREATE TYPE NationType AS {
+  n_nationkey : integer
+};
+
+CREATE DATASET orders(OrderType) PRIMARY KEY o_orderkey;
+
+CREATE DATASET customer(CustomerType) PRIMARY KEY c_custkey;
+
+CREATE DATASET nation(NationType) PRIMARY KEY n_nationkey;
+
+SELECT COUNT(*)
+FROM orders o,
+            (
+              SELECT *
+              FROM customer c,
+                            nation n
+              WHERE c.c_nationkey = n.n_nationkey) cn
+WHERE cn.c_custkey /*+ hashjoin probe (c) */ = o.o_custkey;
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/hashjoin_hint/hashjoin_hint_6.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/hashjoin_hint/hashjoin_hint_6.sqlpp
new file mode 100644
index 0000000..de36c1f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/hints/hashjoin_hint/hashjoin_hint_6.sqlpp
@@ -0,0 +1,59 @@
+/*
+ * 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.
+ */
+/*
+* Description  : Test hash join hint
+*                /*+ hashjoin build (<collection_name>) */
+*                /*+ hashjoin probe (<collection_name>) */
+* Expected Res : Success
+* Date         : 11/20/2022
+*/
+
+DROP dataverse tpch IF EXISTS;
+CREATE  dataverse tpch;
+
+USE tpch;
+
+
+CREATE TYPE OrderType AS {
+  o_orderkey : integer
+};
+
+CREATE TYPE CustomerType AS {
+  c_custkey : integer
+};
+
+CREATE TYPE NationType AS {
+  n_nationkey : integer
+};
+
+CREATE DATASET orders(OrderType) PRIMARY KEY o_orderkey;
+
+CREATE DATASET customer(CustomerType) PRIMARY KEY c_custkey;
+
+CREATE DATASET nation(NationType) PRIMARY KEY n_nationkey;
+
+SELECT COUNT(*)
+FROM orders o,
+            (
+              SELECT *
+              FROM customer c,
+                            nation n
+              WHERE c.c_nationkey = n.n_nationkey) cn
+WHERE cn.c_custkey /*+ hashjoin probe (n) */ = o.o_custkey;
+
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/broadcast_hint_1.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/broadcast_join_hint/broadcast_join_hint_1.plan
similarity index 100%
rename from asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/broadcast_hint_1.plan
rename to asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/broadcast_join_hint/broadcast_join_hint_1.plan
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/broadcast_hint_2.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/broadcast_join_hint/broadcast_join_hint_2.plan
similarity index 100%
rename from asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/broadcast_hint_2.plan
rename to asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/broadcast_join_hint/broadcast_join_hint_2.plan
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/broadcast_hint_3.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/broadcast_join_hint/broadcast_join_hint_3.plan
similarity index 100%
rename from asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/broadcast_hint_3.plan
rename to asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/broadcast_join_hint/broadcast_join_hint_3.plan
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/broadcast_join_hint/broadcast_join_hint_4.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/broadcast_join_hint/broadcast_join_hint_4.plan
new file mode 100644
index 0000000..00c7dfd
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/broadcast_join_hint/broadcast_join_hint_4.plan
@@ -0,0 +1,34 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_PROJECT  |UNPARTITIONED|
+      -- ASSIGN  |UNPARTITIONED|
+        -- AGGREGATE  |UNPARTITIONED|
+          -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+            -- AGGREGATE  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- HYBRID_HASH_JOIN [$$86][$$85]  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- DATASOURCE_SCAN (tpch.orders)  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                  -- BROADCAST_EXCHANGE  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- HYBRID_HASH_JOIN [$$83][$$81]  |PARTITIONED|
+                              -- HASH_PARTITION_EXCHANGE [$$83]  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- DATASOURCE_SCAN (tpch.customer)  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- DATASOURCE_SCAN (tpch.nation)  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/broadcast_join_hint/broadcast_join_hint_5.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/broadcast_join_hint/broadcast_join_hint_5.plan
new file mode 100644
index 0000000..b5ed081
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/broadcast_join_hint/broadcast_join_hint_5.plan
@@ -0,0 +1,34 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_PROJECT  |UNPARTITIONED|
+      -- ASSIGN  |UNPARTITIONED|
+        -- AGGREGATE  |UNPARTITIONED|
+          -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+            -- AGGREGATE  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- HYBRID_HASH_JOIN [$$85][$$86]  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- HYBRID_HASH_JOIN [$$83][$$81]  |PARTITIONED|
+                              -- HASH_PARTITION_EXCHANGE [$$83]  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- DATASOURCE_SCAN (tpch.customer)  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- DATASOURCE_SCAN (tpch.nation)  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                  -- BROADCAST_EXCHANGE  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- DATASOURCE_SCAN (tpch.orders)  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/broadcast_join_hint/broadcast_join_hint_6.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/broadcast_join_hint/broadcast_join_hint_6.plan
new file mode 100644
index 0000000..00c7dfd
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/broadcast_join_hint/broadcast_join_hint_6.plan
@@ -0,0 +1,34 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_PROJECT  |UNPARTITIONED|
+      -- ASSIGN  |UNPARTITIONED|
+        -- AGGREGATE  |UNPARTITIONED|
+          -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+            -- AGGREGATE  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- HYBRID_HASH_JOIN [$$86][$$85]  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- DATASOURCE_SCAN (tpch.orders)  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                  -- BROADCAST_EXCHANGE  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- HYBRID_HASH_JOIN [$$83][$$81]  |PARTITIONED|
+                              -- HASH_PARTITION_EXCHANGE [$$83]  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- DATASOURCE_SCAN (tpch.customer)  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- DATASOURCE_SCAN (tpch.nation)  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/broadcast_join_hint/broadcast_join_hint_7.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/broadcast_join_hint/broadcast_join_hint_7.plan
new file mode 100644
index 0000000..00c7dfd
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/broadcast_join_hint/broadcast_join_hint_7.plan
@@ -0,0 +1,34 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_PROJECT  |UNPARTITIONED|
+      -- ASSIGN  |UNPARTITIONED|
+        -- AGGREGATE  |UNPARTITIONED|
+          -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+            -- AGGREGATE  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- HYBRID_HASH_JOIN [$$86][$$85]  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- DATASOURCE_SCAN (tpch.orders)  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                  -- BROADCAST_EXCHANGE  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- HYBRID_HASH_JOIN [$$83][$$81]  |PARTITIONED|
+                              -- HASH_PARTITION_EXCHANGE [$$83]  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- DATASOURCE_SCAN (tpch.customer)  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- DATASOURCE_SCAN (tpch.nation)  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/hashjoin_hint/hashjoin_hint_1.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/hashjoin_hint/hashjoin_hint_1.plan
new file mode 100644
index 0000000..c5b8dc2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/hashjoin_hint/hashjoin_hint_1.plan
@@ -0,0 +1,34 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_PROJECT  |UNPARTITIONED|
+      -- ASSIGN  |UNPARTITIONED|
+        -- AGGREGATE  |UNPARTITIONED|
+          -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+            -- AGGREGATE  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- HYBRID_HASH_JOIN [$$85][$$86]  |PARTITIONED|
+                  -- HASH_PARTITION_EXCHANGE [$$85]  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- HYBRID_HASH_JOIN [$$83][$$81]  |PARTITIONED|
+                              -- HASH_PARTITION_EXCHANGE [$$83]  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- DATASOURCE_SCAN (tpch.customer)  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- DATASOURCE_SCAN (tpch.nation)  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                  -- HASH_PARTITION_EXCHANGE [$$86]  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- DATASOURCE_SCAN (tpch.orders)  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/hashjoin_hint/hashjoin_hint_2.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/hashjoin_hint/hashjoin_hint_2.plan
new file mode 100644
index 0000000..5610321
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/hashjoin_hint/hashjoin_hint_2.plan
@@ -0,0 +1,34 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_PROJECT  |UNPARTITIONED|
+      -- ASSIGN  |UNPARTITIONED|
+        -- AGGREGATE  |UNPARTITIONED|
+          -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+            -- AGGREGATE  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- HYBRID_HASH_JOIN [$$86][$$85]  |PARTITIONED|
+                  -- HASH_PARTITION_EXCHANGE [$$86]  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- DATASOURCE_SCAN (tpch.orders)  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                  -- HASH_PARTITION_EXCHANGE [$$85]  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- HYBRID_HASH_JOIN [$$83][$$81]  |PARTITIONED|
+                              -- HASH_PARTITION_EXCHANGE [$$83]  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- DATASOURCE_SCAN (tpch.customer)  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- DATASOURCE_SCAN (tpch.nation)  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/hashjoin_hint/hashjoin_hint_3.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/hashjoin_hint/hashjoin_hint_3.plan
new file mode 100644
index 0000000..5610321
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/hashjoin_hint/hashjoin_hint_3.plan
@@ -0,0 +1,34 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_PROJECT  |UNPARTITIONED|
+      -- ASSIGN  |UNPARTITIONED|
+        -- AGGREGATE  |UNPARTITIONED|
+          -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+            -- AGGREGATE  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- HYBRID_HASH_JOIN [$$86][$$85]  |PARTITIONED|
+                  -- HASH_PARTITION_EXCHANGE [$$86]  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- DATASOURCE_SCAN (tpch.orders)  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                  -- HASH_PARTITION_EXCHANGE [$$85]  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- HYBRID_HASH_JOIN [$$83][$$81]  |PARTITIONED|
+                              -- HASH_PARTITION_EXCHANGE [$$83]  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- DATASOURCE_SCAN (tpch.customer)  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- DATASOURCE_SCAN (tpch.nation)  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/hashjoin_hint/hashjoin_hint_4.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/hashjoin_hint/hashjoin_hint_4.plan
new file mode 100644
index 0000000..5610321
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/hashjoin_hint/hashjoin_hint_4.plan
@@ -0,0 +1,34 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_PROJECT  |UNPARTITIONED|
+      -- ASSIGN  |UNPARTITIONED|
+        -- AGGREGATE  |UNPARTITIONED|
+          -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+            -- AGGREGATE  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- HYBRID_HASH_JOIN [$$86][$$85]  |PARTITIONED|
+                  -- HASH_PARTITION_EXCHANGE [$$86]  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- DATASOURCE_SCAN (tpch.orders)  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                  -- HASH_PARTITION_EXCHANGE [$$85]  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- HYBRID_HASH_JOIN [$$83][$$81]  |PARTITIONED|
+                              -- HASH_PARTITION_EXCHANGE [$$83]  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- DATASOURCE_SCAN (tpch.customer)  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- DATASOURCE_SCAN (tpch.nation)  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/hashjoin_hint/hashjoin_hint_5.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/hashjoin_hint/hashjoin_hint_5.plan
new file mode 100644
index 0000000..c5b8dc2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/hashjoin_hint/hashjoin_hint_5.plan
@@ -0,0 +1,34 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_PROJECT  |UNPARTITIONED|
+      -- ASSIGN  |UNPARTITIONED|
+        -- AGGREGATE  |UNPARTITIONED|
+          -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+            -- AGGREGATE  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- HYBRID_HASH_JOIN [$$85][$$86]  |PARTITIONED|
+                  -- HASH_PARTITION_EXCHANGE [$$85]  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- HYBRID_HASH_JOIN [$$83][$$81]  |PARTITIONED|
+                              -- HASH_PARTITION_EXCHANGE [$$83]  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- DATASOURCE_SCAN (tpch.customer)  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- DATASOURCE_SCAN (tpch.nation)  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                  -- HASH_PARTITION_EXCHANGE [$$86]  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- DATASOURCE_SCAN (tpch.orders)  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/hashjoin_hint/hashjoin_hint_6.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/hashjoin_hint/hashjoin_hint_6.plan
new file mode 100644
index 0000000..c5b8dc2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/hints/hashjoin_hint/hashjoin_hint_6.plan
@@ -0,0 +1,34 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_PROJECT  |UNPARTITIONED|
+      -- ASSIGN  |UNPARTITIONED|
+        -- AGGREGATE  |UNPARTITIONED|
+          -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+            -- AGGREGATE  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- HYBRID_HASH_JOIN [$$85][$$86]  |PARTITIONED|
+                  -- HASH_PARTITION_EXCHANGE [$$85]  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- HYBRID_HASH_JOIN [$$83][$$81]  |PARTITIONED|
+                              -- HASH_PARTITION_EXCHANGE [$$83]  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- DATASOURCE_SCAN (tpch.customer)  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- DATASOURCE_SCAN (tpch.nation)  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                  -- HASH_PARTITION_EXCHANGE [$$86]  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- DATASOURCE_SCAN (tpch.orders)  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/tpch/q12_shipping_broadcast.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/tpch/q12_shipping_broadcast.plan
index 613c7cc..55f68b7 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/tpch/q12_shipping_broadcast.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/tpch/q12_shipping_broadcast.plan
@@ -3,13 +3,13 @@
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
         -- SORT_MERGE_EXCHANGE [$$l_shipmode(ASC) ]  |PARTITIONED|
-          -- SORT_GROUP_BY[$$131]  |PARTITIONED|
+          -- SORT_GROUP_BY[$$135]  |PARTITIONED|
                   {
                     -- AGGREGATE  |LOCAL|
                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                   }
-            -- HASH_PARTITION_EXCHANGE [$$131]  |PARTITIONED|
-              -- SORT_GROUP_BY[$$114]  |PARTITIONED|
+            -- HASH_PARTITION_EXCHANGE [$$135]  |PARTITIONED|
+              -- SORT_GROUP_BY[$$118]  |PARTITIONED|
                       {
                         -- AGGREGATE  |LOCAL|
                           -- NESTED_TUPLE_SOURCE  |LOCAL|
@@ -17,15 +17,22 @@
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                   -- STREAM_PROJECT  |PARTITIONED|
                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                      -- HYBRID_HASH_JOIN [$$120][$$114]  |PARTITIONED|
+                      -- HYBRID_HASH_JOIN [$$124][$$118]  |PARTITIONED|
                         -- RANDOM_PARTITION_EXCHANGE  |PARTITIONED|
                           -- UNNEST  |UNPARTITIONED|
                             -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
                         -- BROADCAST_EXCHANGE  |PARTITIONED|
                           -- STREAM_PROJECT  |PARTITIONED|
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              -- HYBRID_HASH_JOIN [$$121][$$118]  |PARTITIONED|
-                                -- HASH_PARTITION_EXCHANGE [$$121]  |PARTITIONED|
+                              -- HYBRID_HASH_JOIN [$$122][$$125]  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ASSIGN  |PARTITIONED|
+                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                        -- DATASOURCE_SCAN (tpch.Orders)  |PARTITIONED|
+                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                -- BROADCAST_EXCHANGE  |PARTITIONED|
                                   -- STREAM_PROJECT  |PARTITIONED|
                                     -- STREAM_SELECT  |PARTITIONED|
                                       -- ASSIGN  |PARTITIONED|
@@ -34,10 +41,3 @@
                                             -- DATASOURCE_SCAN (tpch.LineItem)  |PARTITIONED|
                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                 -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                  -- STREAM_PROJECT  |PARTITIONED|
-                                    -- ASSIGN  |PARTITIONED|
-                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                        -- DATASOURCE_SCAN (tpch.Orders)  |PARTITIONED|
-                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/tpch/q12_shipping_broadcast_ps.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/tpch/q12_shipping_broadcast_ps.plan
index 7fce16c..a73b911 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/tpch/q12_shipping_broadcast_ps.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/tpch/q12_shipping_broadcast_ps.plan
@@ -9,13 +9,13 @@
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                   -- REPLICATE  |PARTITIONED|
                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                      -- SORT_GROUP_BY[$$131]  |PARTITIONED|
+                      -- SORT_GROUP_BY[$$135]  |PARTITIONED|
                               {
                                 -- AGGREGATE  |LOCAL|
                                   -- NESTED_TUPLE_SOURCE  |LOCAL|
                               }
-                        -- HASH_PARTITION_EXCHANGE [$$131]  |PARTITIONED|
-                          -- SORT_GROUP_BY[$$114]  |PARTITIONED|
+                        -- HASH_PARTITION_EXCHANGE [$$135]  |PARTITIONED|
+                          -- SORT_GROUP_BY[$$118]  |PARTITIONED|
                                   {
                                     -- AGGREGATE  |LOCAL|
                                       -- NESTED_TUPLE_SOURCE  |LOCAL|
@@ -23,15 +23,22 @@
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                               -- STREAM_PROJECT  |PARTITIONED|
                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                  -- HYBRID_HASH_JOIN [$$120][$$114]  |PARTITIONED|
+                                  -- HYBRID_HASH_JOIN [$$124][$$118]  |PARTITIONED|
                                     -- RANDOM_PARTITION_EXCHANGE  |PARTITIONED|
                                       -- UNNEST  |UNPARTITIONED|
                                         -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
                                     -- BROADCAST_EXCHANGE  |PARTITIONED|
                                       -- STREAM_PROJECT  |PARTITIONED|
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                          -- HYBRID_HASH_JOIN [$$121][$$118]  |PARTITIONED|
-                                            -- HASH_PARTITION_EXCHANGE [$$121]  |PARTITIONED|
+                                          -- HYBRID_HASH_JOIN [$$122][$$125]  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ASSIGN  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- DATASOURCE_SCAN (tpch.Orders)  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                            -- BROADCAST_EXCHANGE  |PARTITIONED|
                                               -- STREAM_PROJECT  |PARTITIONED|
                                                 -- STREAM_SELECT  |PARTITIONED|
                                                   -- ASSIGN  |PARTITIONED|
@@ -40,13 +47,6 @@
                                                         -- DATASOURCE_SCAN (tpch.LineItem)  |PARTITIONED|
                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                             -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                              -- STREAM_PROJECT  |PARTITIONED|
-                                                -- ASSIGN  |PARTITIONED|
-                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                    -- DATASOURCE_SCAN (tpch.Orders)  |PARTITIONED|
-                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                 -- BROADCAST_EXCHANGE  |PARTITIONED|
                   -- AGGREGATE  |UNPARTITIONED|
                     -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
@@ -55,13 +55,13 @@
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                             -- REPLICATE  |PARTITIONED|
                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                -- SORT_GROUP_BY[$$131]  |PARTITIONED|
+                                -- SORT_GROUP_BY[$$135]  |PARTITIONED|
                                         {
                                           -- AGGREGATE  |LOCAL|
                                             -- NESTED_TUPLE_SOURCE  |LOCAL|
                                         }
-                                  -- HASH_PARTITION_EXCHANGE [$$131]  |PARTITIONED|
-                                    -- SORT_GROUP_BY[$$114]  |PARTITIONED|
+                                  -- HASH_PARTITION_EXCHANGE [$$135]  |PARTITIONED|
+                                    -- SORT_GROUP_BY[$$118]  |PARTITIONED|
                                             {
                                               -- AGGREGATE  |LOCAL|
                                                 -- NESTED_TUPLE_SOURCE  |LOCAL|
@@ -69,15 +69,22 @@
                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                         -- STREAM_PROJECT  |PARTITIONED|
                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                            -- HYBRID_HASH_JOIN [$$120][$$114]  |PARTITIONED|
+                                            -- HYBRID_HASH_JOIN [$$124][$$118]  |PARTITIONED|
                                               -- RANDOM_PARTITION_EXCHANGE  |PARTITIONED|
                                                 -- UNNEST  |UNPARTITIONED|
                                                   -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
                                               -- BROADCAST_EXCHANGE  |PARTITIONED|
                                                 -- STREAM_PROJECT  |PARTITIONED|
                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                    -- HYBRID_HASH_JOIN [$$121][$$118]  |PARTITIONED|
-                                                      -- HASH_PARTITION_EXCHANGE [$$121]  |PARTITIONED|
+                                                    -- HYBRID_HASH_JOIN [$$122][$$125]  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                          -- ASSIGN  |PARTITIONED|
+                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                              -- DATASOURCE_SCAN (tpch.Orders)  |PARTITIONED|
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                      -- BROADCAST_EXCHANGE  |PARTITIONED|
                                                         -- STREAM_PROJECT  |PARTITIONED|
                                                           -- STREAM_SELECT  |PARTITIONED|
                                                             -- ASSIGN  |PARTITIONED|
@@ -86,10 +93,3 @@
                                                                   -- DATASOURCE_SCAN (tpch.LineItem)  |PARTITIONED|
                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                       -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                        -- STREAM_PROJECT  |PARTITIONED|
-                                                          -- ASSIGN  |PARTITIONED|
-                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                              -- DATASOURCE_SCAN (tpch.Orders)  |PARTITIONED|
-                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/broadcast-join-hint-warning/broadcast-join-hint-warning.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/broadcast-join-hint-warning/broadcast-join-hint-warning.1.ddl.sqlpp
new file mode 100644
index 0000000..79ede1c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/broadcast-join-hint-warning/broadcast-join-hint-warning.1.ddl.sqlpp
@@ -0,0 +1,46 @@
+/*
+ * 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.
+ */
+/*
+* Description  : Test warnings for enhanced broadcast join hint
+* Expected Res : Warning, ignore hint
+* Date         : 11/20/2022
+*/
+
+DROP dataverse tpch IF EXISTS;
+CREATE  dataverse tpch;
+
+USE tpch;
+
+CREATE TYPE OrderType AS {
+  o_orderkey : integer
+};
+
+CREATE TYPE CustomerType AS {
+  c_custkey : integer
+};
+
+CREATE TYPE NationType AS {
+  n_nationkey : integer
+};
+
+CREATE DATASET orders(OrderType) PRIMARY KEY o_orderkey;
+
+CREATE DATASET customer(CustomerType) PRIMARY KEY c_custkey;
+
+CREATE DATASET nation(NationType) PRIMARY KEY n_nationkey;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/broadcast-join-hint-warning/broadcast-join-hint-warning.2.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/broadcast-join-hint-warning/broadcast-join-hint-warning.2.query.sqlpp
new file mode 100644
index 0000000..5c3d855
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/broadcast-join-hint-warning/broadcast-join-hint-warning.2.query.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.
+ */
+/*
+* Description  : Test warnings for enhanced broadcast join hint
+* Expected Res : Warning, ignore hint
+* Date         : 11/20/2022
+*/
+// requesttype=application/json
+// param max-warnings:json=10
+
+use tpch;
+
+SELECT COUNT(*)
+FROM orders o,
+            (
+              SELECT *
+              FROM customer c,
+                            nation n
+              WHERE c.c_nationkey = n.n_nationkey) cn
+WHERE cn.c_custkey /*+ hash-bcas */ = o.o_custkey;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/broadcast-join-hint-warning/broadcast-join-hint-warning.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/broadcast-join-hint-warning/broadcast-join-hint-warning.3.query.sqlpp
new file mode 100644
index 0000000..2639913
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/broadcast-join-hint-warning/broadcast-join-hint-warning.3.query.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.
+ */
+/*
+* Description  : Test warnings for enhanced broadcast join hint
+* Expected Res : Warning, ignore hint
+* Date         : 11/20/2022
+*/
+// requesttype=application/json
+// param max-warnings:json=10
+
+use tpch;
+
+SELECT COUNT(*)
+FROM orders o,
+            (
+              SELECT *
+              FROM customer c,
+                            nation n
+              WHERE c.c_nationkey = n.n_nationkey) cn
+WHERE cn.c_custkey /*+ hash-bcast () */ = o.o_custkey;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/broadcast-join-hint-warning/broadcast-join-hint-warning.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/broadcast-join-hint-warning/broadcast-join-hint-warning.4.query.sqlpp
new file mode 100644
index 0000000..73149a8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/broadcast-join-hint-warning/broadcast-join-hint-warning.4.query.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.
+ */
+/*
+* Description  : Test warnings for enhanced broadcast join hint
+* Expected Res : Warning, ignore hint
+* Date         : 11/20/2022
+*/
+// requesttype=application/json
+// param max-warnings:json=10
+
+use tpch;
+
+SELECT COUNT(*)
+FROM orders o,
+            (
+              SELECT *
+              FROM customer c,
+                            nation n
+              WHERE c.c_nationkey = n.n_nationkey) cn
+WHERE cn.c_custkey /*+ hash-bcast (cn) */ = o.o_custkey;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.01.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.01.ddl.sqlpp
new file mode 100644
index 0000000..14ff86d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.01.ddl.sqlpp
@@ -0,0 +1,47 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+* Description  : Test warnings for enhanced hash join hint
+* Expected Res : Warning, ignore hint
+* Date         : 11/20/2022
+*/
+
+DROP dataverse tpch IF EXISTS;
+CREATE  dataverse tpch;
+
+USE tpch;
+
+
+CREATE TYPE OrderType AS {
+  o_orderkey : integer
+};
+
+CREATE TYPE CustomerType AS {
+  c_custkey : integer
+};
+
+CREATE TYPE NationType AS {
+  n_nationkey : integer
+};
+
+CREATE DATASET orders(OrderType) PRIMARY KEY o_orderkey;
+
+CREATE DATASET customer(CustomerType) PRIMARY KEY c_custkey;
+
+CREATE DATASET nation(NationType) PRIMARY KEY n_nationkey;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.02.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.02.query.sqlpp
new file mode 100644
index 0000000..2134749
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.02.query.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.
+ */
+/*
+* Description  : Test warnings for enhanced hash join hint
+* Expected Res : Warning, ignore hint
+* Date         : 11/20/2022
+*/
+// requesttype=application/json
+// param max-warnings:json=10
+
+use tpch;
+
+SELECT COUNT(*)
+FROM orders o,
+            (
+              SELECT *
+              FROM customer c,
+                            nation n
+              WHERE c.c_nationkey = n.n_nationkey) cn
+WHERE cn.c_custkey /*+ hashjoin build */ = o.o_custkey;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.03.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.03.query.sqlpp
new file mode 100644
index 0000000..218c395
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.03.query.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.
+ */
+/*
+* Description  : Test warnings for enhanced hash join hint
+* Expected Res : Warning, ignore hint
+* Date         : 11/20/2022
+*/
+// requesttype=application/json
+// param max-warnings:json=10
+
+use tpch;
+
+SELECT COUNT(*)
+FROM orders o,
+            (
+              SELECT *
+              FROM customer c,
+                            nation n
+              WHERE c.c_nationkey = n.n_nationkey) cn
+WHERE cn.c_custkey /*+ hashjon build */ = o.o_custkey;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.04.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.04.query.sqlpp
new file mode 100644
index 0000000..e447779
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.04.query.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.
+ */
+/*
+* Description  : Test warnings for enhanced hash join hint
+* Expected Res : Warning, ignore hint
+* Date         : 11/20/2022
+*/
+// requesttype=application/json
+// param max-warnings:json=10
+
+use tpch;
+
+SELECT COUNT(*)
+FROM orders o,
+            (
+              SELECT *
+              FROM customer c,
+                            nation n
+              WHERE c.c_nationkey = n.n_nationkey) cn
+WHERE cn.c_custkey /*+ hashjoin buil */ = o.o_custkey;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.05.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.05.query.sqlpp
new file mode 100644
index 0000000..447f68f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.05.query.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.
+ */
+/*
+* Description  : Test warnings for enhanced hash join hint
+* Expected Res : Warning, ignore hint
+* Date         : 11/20/2022
+*/
+// requesttype=application/json
+// param max-warnings:json=10
+
+use tpch;
+
+SELECT COUNT(*)
+FROM orders o,
+            (
+              SELECT *
+              FROM customer c,
+                            nation n
+              WHERE c.c_nationkey = n.n_nationkey) cn
+WHERE cn.c_custkey /*+ hashjoin build () */ = o.o_custkey;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.06.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.06.query.sqlpp
new file mode 100644
index 0000000..6ec7e4a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.06.query.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.
+ */
+/*
+* Description  : Test warnings for enhanced hash join hint
+* Expected Res : Warning, ignore hint
+* Date         : 11/20/2022
+*/
+// requesttype=application/json
+// param max-warnings:json=10
+
+use tpch;
+
+SELECT COUNT(*)
+FROM orders o,
+            (
+              SELECT *
+              FROM customer c,
+                            nation n
+              WHERE c.c_nationkey = n.n_nationkey) cn
+WHERE cn.c_custkey /*+ hashjoin build (cn) */ = o.o_custkey;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.07.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.07.query.sqlpp
new file mode 100644
index 0000000..bc5da6d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.07.query.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.
+ */
+/*
+* Description  : Test warnings for enhanced hash join hint
+* Expected Res : Warning, ignore hint
+* Date         : 11/20/2022
+*/
+// requesttype=application/json
+// param max-warnings:json=10
+
+use tpch;
+
+SELECT COUNT(*)
+FROM orders o,
+            (
+              SELECT *
+              FROM customer c,
+                            nation n
+              WHERE c.c_nationkey = n.n_nationkey) cn
+WHERE cn.c_custkey /*+ hashjoin probe */ = o.o_custkey;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.08.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.08.query.sqlpp
new file mode 100644
index 0000000..a2aad7d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.08.query.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.
+ */
+/*
+* Description  : Test warnings for enhanced hash join hint
+* Expected Res : Warning, ignore hint
+* Date         : 11/20/2022
+*/
+// requesttype=application/json
+// param max-warnings:json=10
+
+use tpch;
+
+SELECT COUNT(*)
+FROM orders o,
+            (
+              SELECT *
+              FROM customer c,
+                            nation n
+              WHERE c.c_nationkey = n.n_nationkey) cn
+WHERE cn.c_custkey /*+ hashjon probe */ = o.o_custkey;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.09.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.09.query.sqlpp
new file mode 100644
index 0000000..e4d7fb0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.09.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.
+ */
+/*
+* Description  : Test warnings for enhanced hash join hint
+* Expected Res : Warning, ignore hint
+* Date         : 11/20/2022
+*/
+// requesttype=application/json
+// param max-warnings:json=10
+
+use tpch;
+
+SELECT COUNT(*)
+FROM orders o,
+            (
+              SELECT *
+              FROM customer c,
+                            nation n
+              WHERE c.c_nationkey = n.n_nationkey) cn
+WHERE cn.c_custkey /*+ hashjoin prob */ = o.o_custkey;
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.10.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.10.query.sqlpp
new file mode 100644
index 0000000..f69bc62
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.10.query.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.
+ */
+/*
+* Description  : Test warnings for enhanced hash join hint
+* Expected Res : Warning, ignore hint
+* Date         : 11/20/2022
+*/
+// requesttype=application/json
+// param max-warnings:json=10
+
+use tpch;
+
+SELECT COUNT(*)
+FROM orders o,
+            (
+              SELECT *
+              FROM customer c,
+                            nation n
+              WHERE c.c_nationkey = n.n_nationkey) cn
+WHERE cn.c_custkey /*+ hashjoin probe () */ = o.o_custkey;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.11.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.11.query.sqlpp
new file mode 100644
index 0000000..6bd515c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/warnings/hashjoin-hint-warning/hashjoin-hint-warning.11.query.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.
+ */
+/*
+* Description  : Test warnings for enhanced hash join hint
+* Expected Res : Warning, ignore hint
+* Date         : 11/20/2022
+*/
+// requesttype=application/json
+// param max-warnings:json=10
+
+use tpch;
+
+SELECT COUNT(*)
+FROM orders o,
+            (
+              SELECT *
+              FROM customer c,
+                            nation n
+              WHERE c.c_nationkey = n.n_nationkey) cn
+WHERE cn.c_custkey /*+ hashjoin probe (cn) */ = o.o_custkey;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/broadcast-join-hint-warning/broadcast-join-hint-warning.2.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/broadcast-join-hint-warning/broadcast-join-hint-warning.2.adm
new file mode 100644
index 0000000..267992b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/broadcast-join-hint-warning/broadcast-join-hint-warning.2.adm
@@ -0,0 +1 @@
+{ "$1": 0 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/broadcast-join-hint-warning/broadcast-join-hint-warning.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/broadcast-join-hint-warning/broadcast-join-hint-warning.3.adm
new file mode 100644
index 0000000..267992b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/broadcast-join-hint-warning/broadcast-join-hint-warning.3.adm
@@ -0,0 +1 @@
+{ "$1": 0 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/broadcast-join-hint-warning/broadcast-join-hint-warning.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/broadcast-join-hint-warning/broadcast-join-hint-warning.4.adm
new file mode 100644
index 0000000..267992b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/broadcast-join-hint-warning/broadcast-join-hint-warning.4.adm
@@ -0,0 +1 @@
+{ "$1": 0 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.02.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.02.adm
new file mode 100644
index 0000000..267992b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.02.adm
@@ -0,0 +1 @@
+{ "$1": 0 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.03.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.03.adm
new file mode 100644
index 0000000..267992b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.03.adm
@@ -0,0 +1 @@
+{ "$1": 0 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.04.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.04.adm
new file mode 100644
index 0000000..267992b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.04.adm
@@ -0,0 +1 @@
+{ "$1": 0 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.05.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.05.adm
new file mode 100644
index 0000000..267992b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.05.adm
@@ -0,0 +1 @@
+{ "$1": 0 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.06.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.06.adm
new file mode 100644
index 0000000..267992b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.06.adm
@@ -0,0 +1 @@
+{ "$1": 0 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.07.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.07.adm
new file mode 100644
index 0000000..267992b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.07.adm
@@ -0,0 +1 @@
+{ "$1": 0 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.08.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.08.adm
new file mode 100644
index 0000000..267992b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.08.adm
@@ -0,0 +1 @@
+{ "$1": 0 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.09.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.09.adm
new file mode 100644
index 0000000..267992b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.09.adm
@@ -0,0 +1 @@
+{ "$1": 0 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.10.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.10.adm
new file mode 100644
index 0000000..267992b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.10.adm
@@ -0,0 +1 @@
+{ "$1": 0 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.11.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.11.adm
new file mode 100644
index 0000000..267992b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/warnings/hashjoin-hint-warning/hashjoin-hint-warning.11.adm
@@ -0,0 +1 @@
+{ "$1": 0 }
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 7b875e2..5ef74bd 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -15411,6 +15411,29 @@
   </test-group>
   <test-group name="warnings">
     <test-case FilePath="warnings" check-warnings="true">
+      <compilation-unit name="broadcast-join-hint-warning">
+        <output-dir compare="Text">broadcast-join-hint-warning</output-dir>
+        <expected-warn><![CDATA[ASX1107: Unexpected hint: hash-bcas. "hash-bcast", "indexnl", "hashjoin", "skip-index", "use-index", "selectivity", "productivity" expected at this location (in line 36, at column 22)]]></expected-warn>
+        <expected-warn><![CDATA[ASX1132: Invalid specification for hint hash-bcast. ASX1001: Syntax error: In line 1 >>()<< Encountered ")" at column 2.  (in line 36, at column 22)]]></expected-warn>
+        <expected-warn>HYR10006: Could not apply broadcast hash join hint: broadcast cn (in line 36, at column 43)</expected-warn>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="warnings" check-warnings="true">
+      <compilation-unit name="hashjoin-hint-warning">
+        <output-dir compare="Text">hashjoin-hint-warning</output-dir>
+        <expected-warn><![CDATA[ASX1132: Invalid specification for hint hashjoin. ASX1001: Syntax error: In line 1 >>build<< Encountered <EOF> at column 5.  (in line 36, at column 22)]]></expected-warn>
+        <expected-warn><![CDATA[ASX1107: Unexpected hint: hashjon build. "hash-bcast", "indexnl", "hashjoin", "skip-index", "use-index", "selectivity", "productivity" expected at this location (in line 36, at column 22)]]></expected-warn>
+        <expected-warn><![CDATA[ASX1132: Invalid specification for hint hashjoin. ASX1001: Syntax error: In line 1 >>buil<< Encountered <EOF> at column 4.  (in line 36, at column 22)]]></expected-warn>
+        <expected-warn><![CDATA[ASX1132: Invalid specification for hint hashjoin. ASX1001: Syntax error: In line 1 >>build ()<< Encountered ")" at column 8.  (in line 36, at column 22)]]></expected-warn>
+        <expected-warn>HYR10006: Could not apply hash join hint: build with cn (in line 36, at column 47)</expected-warn>
+        <expected-warn><![CDATA[ASX1132: Invalid specification for hint hashjoin. ASX1001: Syntax error: In line 1 >>probe<< Encountered <EOF> at column 5.  (in line 36, at column 22)]]></expected-warn>
+        <expected-warn><![CDATA[ASX1107: Unexpected hint: hashjon probe. "hash-bcast", "indexnl", "hashjoin", "skip-index", "use-index", "selectivity", "productivity" expected at this location (in line 36, at column 22)]]></expected-warn>
+        <expected-warn><![CDATA[ASX1132: Invalid specification for hint hashjoin. ASX1001: Syntax error: In line 1 >>prob<< Encountered <EOF> at column 4.  (in line 36, at column 22)]]></expected-warn>
+        <expected-warn><![CDATA[ASX1132: Invalid specification for hint hashjoin. ASX1001: Syntax error: In line 1 >>probe ()<< Encountered ")" at column 8.  (in line 36, at column 22)]]></expected-warn>
+        <expected-warn>HYR10006: Could not apply hash join hint: probe with cn (in line 36, at column 47)</expected-warn>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="warnings" check-warnings="true">
       <compilation-unit name="inapplicable-hint-warning">
         <output-dir compare="Text">inapplicable-hint-warning</output-dir>
         <expected-warn>HYR10006: Could not apply Group By hint: hash</expected-warn>
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj b/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
index 59d6b35..6cc0c82 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
+++ b/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
@@ -414,6 +414,32 @@
         return new SQLPPParser(text).parseParenthesizedIdentifierList();
     }
 
+    private Pair<HashJoinExpressionAnnotation.BuildOrProbe, String> parseHashJoinParams() throws CompilationException {
+        return parseImpl(new ParseFunction<Pair<HashJoinExpressionAnnotation.BuildOrProbe, String>>() {
+            @Override
+            public  Pair<HashJoinExpressionAnnotation.BuildOrProbe, String> parse() throws ParseException {
+                return SQLPPParser.this.buildOrProbeParenthesizedIdentifier();
+            }
+        });
+    }
+
+    private static Pair<HashJoinExpressionAnnotation.BuildOrProbe, String> parseHashJoinParams(String text) throws CompilationException {
+        return new SQLPPParser(text).parseHashJoinParams();
+    }
+
+    private String parseBroadcastJoinParams() throws CompilationException {
+            return parseImpl(new ParseFunction<String>() {
+                @Override
+                public  String parse() throws ParseException {
+                    return SQLPPParser.this.parenthesizedIdentifier();
+                }
+            });
+        }
+
+    private static String parseBroadcastJoinParams(String text) throws CompilationException {
+        return new SQLPPParser(text).parseBroadcastJoinParams();
+    }
+
     private List<Literal> parseParenthesizedLiteralList() throws CompilationException {
         return parseImpl(new ParseFunction<List<Literal>>() {
             @Override
@@ -692,9 +718,23 @@
             // attach hint to global scope
             return new JoinProductivityAnnotation (productivity, leftSideDataSet);
           case HASH_BROADCAST_JOIN_HINT:
-            return new BroadcastExpressionAnnotation(BroadcastExpressionAnnotation.BroadcastSide.RIGHT);
+            if (hintToken.hintParams == null) {
+              return new BroadcastExpressionAnnotation(BroadcastExpressionAnnotation.BroadcastSide.RIGHT);
+            }
+            else {
+              // if parameter parsing fails then ignore this hint.
+              String name = parseBroadcastJoinParams(hintToken.hintParams);
+              return new BroadcastExpressionAnnotation(name);
+            }
           case HASH_JOIN_HINT:
-            return new HashJoinExpressionAnnotation(HashJoinExpressionAnnotation.BuildSide.RIGHT);
+            if (hintToken.hintParams == null) {
+              throw new SqlppParseException(getSourceLocation(hintToken), "Expected hash join build/probe collection name");
+            }
+            else {
+              // if parameter parsing fails then ignore this hint.
+              Pair<HashJoinExpressionAnnotation.BuildOrProbe, String> pair = parseHashJoinParams(hintToken.hintParams);
+              return new HashJoinExpressionAnnotation(pair);
+            }
           case INDEXED_NESTED_LOOP_JOIN_HINT:
             if (hintToken.hintParams == null) {
               return IndexedNLJoinExpressionAnnotation.INSTANCE_ANY_INDEX;
@@ -3170,6 +3210,39 @@
   }
 }
 
+Pair<HashJoinExpressionAnnotation.BuildOrProbe, String> buildOrProbeParenthesizedIdentifier() throws ParseException:
+{
+  String ident1 = null;
+  String ident2 = null;
+}
+{
+  ident1 = Identifier() <LEFTPAREN> ident2 = Identifier() <RIGHTPAREN>
+  {
+    // check
+    if (ident1.equals("build")) {
+        return new Pair<HashJoinExpressionAnnotation.BuildOrProbe, String>(HashJoinExpressionAnnotation.BuildOrProbe.BUILD, ident2);
+    }
+    else if (ident1.equals("probe")) {
+            return new Pair<HashJoinExpressionAnnotation.BuildOrProbe, String>(HashJoinExpressionAnnotation.BuildOrProbe.PROBE, ident2);
+    }
+    else {
+        throw new SqlppParseException(getSourceLocation(token), "The string after hashjoin has to be \"build\" or \"probe\".");
+    }
+    return null;
+  }
+}
+
+String parenthesizedIdentifier() throws ParseException:
+{
+  String ident = null;
+}
+{
+  <LEFTPAREN> ident = Identifier() <RIGHTPAREN>
+  {
+    return ident;
+  }
+}
+
 List<Literal> ParenthesizedLiteralList() throws ParseException:
 {
   List<Literal> list = new ArrayList<Literal>();
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/BroadcastExpressionAnnotation.java b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/BroadcastExpressionAnnotation.java
index 79b9e2c..f1b5093 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/BroadcastExpressionAnnotation.java
+++ b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/BroadcastExpressionAnnotation.java
@@ -38,13 +38,37 @@
         }
     }
 
-    private final BroadcastSide side;
+    private String name;
+    private BroadcastSide side;
+
+    public BroadcastExpressionAnnotation(String name) {
+        this.name = validateName(name);
+        this.side = null;
+    }
 
     public BroadcastExpressionAnnotation(BroadcastSide side) {
+        this.name = null;
         this.side = Objects.requireNonNull(side);
     }
 
+    public String getName() {
+        return name;
+    }
+
     public BroadcastSide getBroadcastSide() {
         return side;
     }
+
+    public void setBroadcastSide(BroadcastSide side) {
+        this.name = null;
+        this.side = Objects.requireNonNull(side);
+    }
+
+    private String validateName(String name) {
+        String n = Objects.requireNonNull(name);
+        if (n.isBlank()) {
+            throw new IllegalArgumentException("BroadcastExpressionAnnotation:" + name + "cannot be blank");
+        }
+        return n;
+    }
 }
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/HashJoinExpressionAnnotation.java b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/HashJoinExpressionAnnotation.java
index 02ba2db..88d9dd5 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/HashJoinExpressionAnnotation.java
+++ b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/HashJoinExpressionAnnotation.java
@@ -21,19 +21,59 @@
 
 import java.util.Objects;
 
+import org.apache.hyracks.algebricks.common.utils.Pair;
+
 public class HashJoinExpressionAnnotation implements IExpressionAnnotation {
+
+    public enum BuildOrProbe {
+        BUILD,
+        PROBE
+    }
+
     public enum BuildSide {
         LEFT,
         RIGHT
     }
 
-    private final BuildSide side;
+    private BuildOrProbe buildOrProbe;
+    private String name;
+    private BuildSide side;
+
+    public HashJoinExpressionAnnotation(Pair<BuildOrProbe, String> pair) {
+        this.buildOrProbe = Objects.requireNonNull(pair.getFirst());
+        this.name = validateName(pair.getSecond());
+        this.side = null;
+    }
 
     public HashJoinExpressionAnnotation(BuildSide side) {
+        this.buildOrProbe = null;
+        this.name = null;
         this.side = Objects.requireNonNull(side);
     }
 
+    public BuildOrProbe getBuildOrProbe() {
+        return buildOrProbe;
+    }
+
+    public String getName() {
+        return name;
+    }
+
     public BuildSide getBuildSide() {
         return side;
     }
+
+    public void setBuildSide(BuildSide side) {
+        this.buildOrProbe = null;
+        this.name = null;
+        this.side = Objects.requireNonNull(side);
+    }
+
+    private String validateName(String name) {
+        String n = Objects.requireNonNull(name);
+        if (n.isBlank()) {
+            throw new IllegalArgumentException("HashJoinExpressionAnnotation:" + name + "cannot be blank");
+        }
+        return n;
+    }
 }
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/BroadcastSideSwitchingVisitor.java b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/BroadcastSideSwitchingVisitor.java
index aa0f642..d8a8348 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/BroadcastSideSwitchingVisitor.java
+++ b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/BroadcastSideSwitchingVisitor.java
@@ -70,7 +70,7 @@
             }
         }
         BroadcastExpressionAnnotation bcastAnn = expr.removeAnnotation(BroadcastExpressionAnnotation.class);
-        if (bcastAnn != null) {
+        if (bcastAnn != null && bcastAnn.getBroadcastSide() != null) {
             BroadcastExpressionAnnotation.BroadcastSide oppositeSide =
                     BroadcastExpressionAnnotation.BroadcastSide.getOppositeSide(bcastAnn.getBroadcastSide());
             expr.putAnnotation(new BroadcastExpressionAnnotation(oppositeSide));
diff --git a/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/util/JoinUtils.java b/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/util/JoinUtils.java
index 1ac7614..fa0549f 100644
--- a/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/util/JoinUtils.java
+++ b/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/util/JoinUtils.java
@@ -32,6 +32,8 @@
 import org.apache.hyracks.algebricks.core.algebra.expressions.AbstractFunctionCallExpression;
 import org.apache.hyracks.algebricks.core.algebra.expressions.BroadcastExpressionAnnotation;
 import org.apache.hyracks.algebricks.core.algebra.expressions.BroadcastExpressionAnnotation.BroadcastSide;
+import org.apache.hyracks.algebricks.core.algebra.expressions.HashJoinExpressionAnnotation;
+import org.apache.hyracks.algebricks.core.algebra.expressions.HashJoinExpressionAnnotation.BuildSide;
 import org.apache.hyracks.algebricks.core.algebra.expressions.VariableReferenceExpression;
 import org.apache.hyracks.algebricks.core.algebra.functions.AlgebricksBuiltinFunctions;
 import org.apache.hyracks.algebricks.core.algebra.functions.AlgebricksBuiltinFunctions.ComparisonKind;
@@ -65,11 +67,35 @@
         List<LogicalVariable> varsRight = op.getInputs().get(1).getValue().getSchema();
         ILogicalExpression conditionExpr = op.getCondition().getValue();
         if (isHashJoinCondition(conditionExpr, varsLeft, varsRight, sideLeft, sideRight)) {
-            BroadcastSide side = getBroadcastJoinSide(conditionExpr);
-            if (side == null) {
-                setHashJoinOp(op, JoinPartitioningType.PAIRWISE, sideLeft, sideRight, context);
+            BroadcastSide broadcastSide = getBroadcastJoinSide(conditionExpr, varsLeft, varsRight, context);
+            if (broadcastSide == null) {
+                BuildSide buildSide = getHashJoinBuildSide(conditionExpr, varsLeft, varsRight, context);
+                if (buildSide == null) {
+                    setHashJoinOp(op, JoinPartitioningType.PAIRWISE, sideLeft, sideRight, context);
+                } else {
+                    switch (buildSide) {
+                        case RIGHT:
+                            setHashJoinOp(op, JoinPartitioningType.PAIRWISE, sideLeft, sideRight, context);
+                            break;
+                        case LEFT:
+                            if (op.getJoinKind() == AbstractBinaryJoinOperator.JoinKind.INNER) {
+                                Mutable<ILogicalOperator> opRef0 = op.getInputs().get(0);
+                                Mutable<ILogicalOperator> opRef1 = op.getInputs().get(1);
+                                ILogicalOperator tmp = opRef0.getValue();
+                                opRef0.setValue(opRef1.getValue());
+                                opRef1.setValue(tmp);
+                                setHashJoinOp(op, JoinPartitioningType.PAIRWISE, sideRight, sideLeft, context);
+                            } else {
+                                setHashJoinOp(op, JoinPartitioningType.PAIRWISE, sideLeft, sideRight, context);
+                            }
+                            break;
+                        default:
+                            // This should never happen
+                            throw new IllegalStateException(buildSide.toString());
+                    }
+                }
             } else {
-                switch (side) {
+                switch (broadcastSide) {
                     case RIGHT:
                         setHashJoinOp(op, JoinPartitioningType.BROADCAST, sideLeft, sideRight, context);
                         break;
@@ -87,7 +113,7 @@
                         break;
                     default:
                         // This should never happen
-                        throw new IllegalStateException(side.toString());
+                        throw new IllegalStateException(broadcastSide.toString());
                 }
             }
         } else {
@@ -188,7 +214,8 @@
         }
     }
 
-    private static BroadcastSide getBroadcastJoinSide(ILogicalExpression e) {
+    private static BroadcastSide getBroadcastJoinSide(ILogicalExpression e, List<LogicalVariable> varsLeft,
+            List<LogicalVariable> varsRight, IOptimizationContext context) {
         BroadcastSide side = null;
         if (e.getExpressionTag() != LogicalExpressionTag.FUNCTION_CALL) {
             return null;
@@ -197,7 +224,7 @@
         FunctionIdentifier fi = fexp.getFunctionIdentifier();
         if (fi.equals(AlgebricksBuiltinFunctions.AND)) {
             for (Mutable<ILogicalExpression> a : fexp.getArguments()) {
-                BroadcastSide newSide = getBroadcastJoinSide(a.getValue());
+                BroadcastSide newSide = getBroadcastJoinSide(a.getValue(), varsLeft, varsRight, context);
                 if (side == null) {
                     side = newSide;
                 } else if (newSide != null && !newSide.equals(side)) {
@@ -206,9 +233,83 @@
             }
             return side;
         } else {
-            BroadcastExpressionAnnotation bcastAnnnotation = fexp.getAnnotation(BroadcastExpressionAnnotation.class);
-            if (bcastAnnnotation != null) {
-                return bcastAnnnotation.getBroadcastSide();
+            BroadcastExpressionAnnotation bcastAnnotation = fexp.getAnnotation(BroadcastExpressionAnnotation.class);
+            if (bcastAnnotation != null) {
+                BroadcastExpressionAnnotation.BroadcastSide bcastSide = bcastAnnotation.getBroadcastSide();
+                if (bcastSide != null) {
+                    return bcastSide;
+                }
+                String broadcastObject = "$$" + bcastAnnotation.getName();
+                if (varsRight.stream().map(LogicalVariable::toString).anyMatch(v -> v.equals(broadcastObject))) {
+                    bcastAnnotation.setBroadcastSide(BroadcastSide.RIGHT);
+                    return bcastAnnotation.getBroadcastSide();
+                } else if (varsLeft.stream().map(LogicalVariable::toString).anyMatch(v -> v.equals(broadcastObject))) {
+                    bcastAnnotation.setBroadcastSide(BroadcastSide.LEFT);
+                    return bcastAnnotation.getBroadcastSide();
+                } else {
+                    IWarningCollector warningCollector = context.getWarningCollector();
+                    if (warningCollector.shouldWarn()) {
+                        warningCollector.warn(Warning.of(e.getSourceLocation(), ErrorCode.INAPPLICABLE_HINT,
+                                "broadcast hash join", "broadcast " + bcastAnnotation.getName()));
+                    }
+                    return null;
+                }
+            }
+        }
+        return null;
+    }
+
+    private static BuildSide getHashJoinBuildSide(ILogicalExpression e, List<LogicalVariable> varsLeft,
+            List<LogicalVariable> varsRight, IOptimizationContext context) {
+        BuildSide side = null;
+        if (e.getExpressionTag() != LogicalExpressionTag.FUNCTION_CALL) {
+            return null;
+        }
+        AbstractFunctionCallExpression fexp = (AbstractFunctionCallExpression) e;
+        FunctionIdentifier fi = fexp.getFunctionIdentifier();
+        if (fi.equals(AlgebricksBuiltinFunctions.AND)) {
+            for (Mutable<ILogicalExpression> a : fexp.getArguments()) {
+                BuildSide newSide = getHashJoinBuildSide(a.getValue(), varsLeft, varsRight, context);
+                if (side == null) {
+                    side = newSide;
+                } else if (newSide != null && !newSide.equals(side)) {
+                    return null;
+                }
+            }
+            return side;
+        } else {
+            HashJoinExpressionAnnotation hashJoinAnnotation = fexp.getAnnotation(HashJoinExpressionAnnotation.class);
+            if (hashJoinAnnotation != null) {
+                BuildSide buildSide = hashJoinAnnotation.getBuildSide();
+                if (buildSide != null) {
+                    return buildSide;
+                }
+                boolean build =
+                        (hashJoinAnnotation.getBuildOrProbe() == HashJoinExpressionAnnotation.BuildOrProbe.BUILD);
+                boolean probe =
+                        (hashJoinAnnotation.getBuildOrProbe() == HashJoinExpressionAnnotation.BuildOrProbe.PROBE);
+
+                String buildOrProbeObject = "$$" + hashJoinAnnotation.getName();
+                if ((build && varsRight.stream().map(LogicalVariable::toString)
+                        .anyMatch(v -> v.equals(buildOrProbeObject)))
+                        || (probe && varsLeft.stream().map(LogicalVariable::toString)
+                                .anyMatch(v -> v.equals(buildOrProbeObject)))) {
+                    hashJoinAnnotation.setBuildSide(HashJoinExpressionAnnotation.BuildSide.RIGHT);
+                    return hashJoinAnnotation.getBuildSide();
+                } else if ((build
+                        && varsLeft.stream().map(LogicalVariable::toString).anyMatch(v -> v.equals(buildOrProbeObject)))
+                        || (probe && varsRight.stream().map(LogicalVariable::toString)
+                                .anyMatch(v -> v.equals(buildOrProbeObject)))) {
+                    hashJoinAnnotation.setBuildSide(HashJoinExpressionAnnotation.BuildSide.LEFT);
+                    return hashJoinAnnotation.getBuildSide();
+                } else {
+                    IWarningCollector warningCollector = context.getWarningCollector();
+                    if (warningCollector.shouldWarn()) {
+                        warningCollector.warn(Warning.of(e.getSourceLocation(), ErrorCode.INAPPLICABLE_HINT,
+                                "hash join", (build ? "build " : "probe ") + "with " + hashJoinAnnotation.getName()));
+                    }
+                    return null;
+                }
             }
         }
         return null;