in modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItJoinTest.java [697:941]
public void testRightJoin(JoinType joinType) {
assertQuery(""
+ "select t1.c1 c11, t1.c2 c12, t2.c1 c21, t2.c2 c22, t2.c3 c23 "
+ " from t1 "
+ " right join t2 "
+ " on t1.c1 = t2.c1 "
+ " and t1.c2 = t2.c2 "
+ " order by t2.c1, t2.c2, t2.c3",
joinType
)
.ordered()
.returns(1, 1, 1, 1, 1)
.returns(2, 2, 2, 2, 2)
.returns(2, 2, 2, 2, null)
.returns(3, 3, 3, 3, 3)
.returns(3, 3, 3, 3, 3)
.returns(null, null, 3, null, 3)
.returns(4, 4, 4, 4, 4)
.check();
assertQuery(""
+ "select t1.c1 c11, t1.c2 c12, t2.c1 c21, t2.c2 c22, t2.c3 c23 "
+ " from t1 "
+ " right join t2 "
+ " on t1.c1 = t2.c1 "
+ " and t1.c2 = t2.c2 "
+ " order by t2.c1, t2.c2 nulls first, t2.c3 nulls first",
joinType
)
.ordered()
.returns(1, 1, 1, 1, 1)
.returns(2, 2, 2, 2, null)
.returns(2, 2, 2, 2, 2)
.returns(null, null, 3, null, 3)
.returns(3, 3, 3, 3, 3)
.returns(3, 3, 3, 3, 3)
.returns(4, 4, 4, 4, 4)
.check();
assertQuery(""
+ "select t1.c1 c11, t1.c2 c12, t2.c1 c21, t2.c2 c22, t2.c3 c23 "
+ " from t1 "
+ " right join t2 "
+ " on t1.c1 = t2.c1 "
+ " and t1.c2 = t2.c2 "
+ " order by t2.c1, t2.c2 nulls last, t2.c3 nulls last",
joinType
)
.ordered()
.returns(1, 1, 1, 1, 1)
.returns(2, 2, 2, 2, 2)
.returns(2, 2, 2, 2, null)
.returns(3, 3, 3, 3, 3)
.returns(3, 3, 3, 3, 3)
.returns(null, null, 3, null, 3)
.returns(4, 4, 4, 4, 4)
.check();
assertQuery(""
+ "select t1.c1 c11, t1.c2 c12, t2.c1 c21, t2.c2 c22, t2.c3 c23 "
+ " from t1 "
+ " right join t2 "
+ " on t1.c1 = t2.c1 "
+ " and t1.c2 = t2.c2 "
+ " order by t2.c1 desc, t2.c2, t2.c3",
joinType
)
.ordered()
.returns(4, 4, 4, 4, 4)
.returns(3, 3, 3, 3, 3)
.returns(3, 3, 3, 3, 3)
.returns(null, null, 3, null, 3)
.returns(2, 2, 2, 2, 2)
.returns(2, 2, 2, 2, null)
.returns(1, 1, 1, 1, 1)
.check();
assertQuery(""
+ "select t1.c1 c11, t1.c2 c12, t2.c1 c21, t2.c2 c22, t2.c3 c23 "
+ " from t1 "
+ " right join t2 "
+ " on t1.c1 = t2.c1 "
+ " and t1.c2 = t2.c2 "
+ " order by t2.c1 desc, t2.c2, t2.c3 nulls first",
joinType
)
.ordered()
.returns(4, 4, 4, 4, 4)
.returns(3, 3, 3, 3, 3)
.returns(3, 3, 3, 3, 3)
.returns(null, null, 3, null, 3)
.returns(2, 2, 2, 2, null)
.returns(2, 2, 2, 2, 2)
.returns(1, 1, 1, 1, 1)
.check();
assertQuery(""
+ "select t1.c1 c11, t1.c2 c12, t2.c1 c21, t2.c2 c22, t2.c3 c23 "
+ " from t1 "
+ " right join t2 "
+ " on t1.c1 = t2.c1 "
+ " and t1.c2 = t2.c2 "
+ " order by t2.c1 desc, t2.c2, t2.c3 nulls last",
joinType
)
.ordered()
.returns(4, 4, 4, 4, 4)
.returns(3, 3, 3, 3, 3)
.returns(3, 3, 3, 3, 3)
.returns(null, null, 3, null, 3)
.returns(2, 2, 2, 2, 2)
.returns(2, 2, 2, 2, null)
.returns(1, 1, 1, 1, 1)
.check();
assertQuery(""
+ "select t1.c3 c13, t1.c2 c12, t2.c3 c23, t2.c2 c22 "
+ " from t1 "
+ " right join t2 "
+ " on t1.c3 = t2.c3 "
+ " and t1.c2 = t2.c2 "
+ " order by t2.c3, t2.c2",
joinType
)
.ordered()
.returns(1, 1, 1, 1)
.returns(2, 2, 2, 2)
.returns(3, 3, 3, 3)
.returns(null, null, 3, null)
.returns(4, 4, 4, 4)
.returns(null, null, null, 2)
.check();
assertQuery(""
+ "select t1.c3 c13, t1.c2 c12, t2.c1 c21, t2.c2 c22, t2.c3 c23 "
+ " from t1 "
+ " right join t2 "
+ " on t1.c3 = t2.c3 "
+ " and t1.c2 = t2.c2 "
+ " order by t2.c3 nulls first, t2.c2 nulls first, t2.c1 nulls first",
joinType
)
.ordered()
.returns(null, null, 2, 2, null)
.returns(1, 1, 1, 1, 1)
.returns(2, 2, 2, 2, 2)
.returns(null, null, 3, null, 3)
.returns(3, 3, 3, 3, 3)
.returns(4, 4, 4, 4, 4)
.check();
assertQuery(""
+ "select t1.c3 c13, t1.c2 c12, t2.c1 c21, t2.c2 c22, t2.c3 c23 "
+ " from t1 "
+ " right join t2 "
+ " on t1.c3 = t2.c3 "
+ " and t1.c2 = t2.c2 "
+ " order by t2.c3 nulls last, t2.c2 nulls last, t2.c1 nulls last",
joinType
)
.ordered()
.returns(1, 1, 1, 1, 1)
.returns(2, 2, 2, 2, 2)
.returns(3, 3, 3, 3, 3)
.returns(null, null, 3, null, 3)
.returns(4, 4, 4, 4, 4)
.returns(null, null, 2, 2, null)
.check();
assertQuery(""
+ "select t1.c2 c12, t1.c1 c11, t2.c1 c21, t2.c2 c22, t2.c3 c23 "
+ " from t1 "
+ " right join t2 "
+ " on t1.c1 = t2.c1 "
+ " and t1.c2 = t2.c2 "
+ " order by t2.c3 nulls first, t2.c2 nulls first, t2.c1 nulls first",
joinType
)
.ordered()
.returns(2, 2, 2, 2, null)
.returns(1, 1, 1, 1, 1)
.returns(2, 2, 2, 2, 2)
.returns(null, null, 3, null, 3)
.returns(3, 3, 3, 3, 3)
.returns(3, 3, 3, 3, 3)
.returns(4, 4, 4, 4, 4)
.check();
assertQuery(""
+ "select t1.c2 c12, t1.c1 c11, t2.c1 c21, t2.c2 c22, t2.c3 c23 "
+ " from t1 "
+ " right join t2 "
+ " on t1.c1 = t2.c1 "
+ " and t1.c2 = t2.c2 "
+ " order by t2.c3 nulls last, t2.c2 nulls last, t2.c1 nulls last",
joinType
)
.ordered()
.returns(1, 1, 1, 1, 1)
.returns(2, 2, 2, 2, 2)
.returns(3, 3, 3, 3, 3)
.returns(3, 3, 3, 3, 3)
.returns(null, null, 3, null, 3)
.returns(4, 4, 4, 4, 4)
.returns(2, 2, 2, 2, null)
.check();
assertQuery(""
+ "select t1.c3 c13, t1.c2 c12, t1.c1 c11, t2.c1 c21, t2.c2 c22, t2.c3 c23 "
+ " from t1 "
+ " right join t2 "
+ " on t1.c1 = t2.c1 "
+ " and t1.c2 = t2.c2 "
+ " and t1.c3 = t2.c3 "
+ " order by t2.c3 nulls first, t2.c2 nulls first, t2.c1 nulls first",
joinType
)
.ordered()
.returns(null, null, null, 2, 2, null)
.returns(1, 1, 1, 1, 1, 1)
.returns(2, 2, 2, 2, 2, 2)
.returns(null, null, null, 3, null, 3)
.returns(3, 3, 3, 3, 3, 3)
.returns(4, 4, 4, 4, 4, 4)
.check();
assertQuery(""
+ "select t1.c3 c13, t1.c2 c12, t1.c1 c11, t2.c1 c21, t2.c2 c22, t2.c3 c23 "
+ " from t1 "
+ " right join t2 "
+ " on t1.c1 = t2.c1 "
+ " and t1.c2 = t2.c2 "
+ " and t1.c3 = t2.c3 "
+ " order by t2.c3 nulls last, t2.c2 nulls last, t2.c1 nulls last",
joinType
)
.ordered()
.returns(1, 1, 1, 1, 1, 1)
.returns(2, 2, 2, 2, 2, 2)
.returns(3, 3, 3, 3, 3, 3)
.returns(null, null, null, 3, null, 3)
.returns(4, 4, 4, 4, 4, 4)
.returns(null, null, null, 2, 2, null)
.check();
}