in modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItJoinTest.java [280:465]
public void testInnerJoin(JoinType joinType) {
assertQuery("SELECT c1 FROM t3 WHERE c1 = ANY(SELECT c1 FROM t3) ORDER BY c1", joinType)
.ordered()
.returns(1)
.returns(2)
.returns(3)
.returns(7)
.returns(8)
.check();
assertQuery(""
+ "select t1.c1 c11, t1.c2 c12, t1.c3 c13, t2.c1 c21, t2.c2 c22 "
+ " from t1 "
+ " 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(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 "
+ " join t2 "
+ " on t1.c1 = t2.c1 "
+ " and t1.c2 = t2.c2 "
+ " order by t1.c1, t1.c2, t1.c3 nulls first",
joinType
)
.ordered()
.returns(1, 1, 1, 1, 1)
.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 "
+ " join t2 "
+ " on t1.c1 = t2.c1 "
+ " and t1.c2 = t2.c2 "
+ " order by t1.c1, t1.c2, 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(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 "
+ " 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(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 "
+ " 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(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 "
+ " 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(1, 1, 1, 1, 1)
.check();
assertQuery(""
+ "select t1.c3 c13, t1.c2 c12, t2.c3 c23, t2.c2 c22 "
+ " from t1 "
+ " 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(3, 3, 3, 3)
.returns(4, 4, 4, 4)
.check();
assertQuery(""
+ "select t1.c3 c13, t1.c2 c12, t1.c1 c11, t2.c1 c21, t2.c2 c22 "
+ " from t1 "
+ " 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(1, 1, 1, 1, 1)
.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 "
+ " 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, 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 "
+ " 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(3, 3, 3, 3, 3)
.returns(4, 4, 4, 4, 4)
.returns(null, 3, 3, 3, 3)
.check();
}