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