in modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItJoinTest.java [473:689]
public void testLeftJoin(JoinType joinType) {
assertQuery("select t31.c1 from t3 t31 left join t3 t32 on t31.c1 = t32.c1 ORDER BY t31.c1;", joinType)
.ordered()
.returns(1)
.returns(2)
.returns(3)
.returns(7)
.returns(8)
.returns(null)
.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, 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();
}