@Test public void testSubqueriesWithDisjunction() { List<QueryTemplate.Parameter> projections = parameter("projection").of("count(*)", "*", "%condition%"); List<QueryTemplate.Parameter> conditions = parameter("condition").of( "nationkey IN (SELECT 1) OR TRUE", "EXISTS(SELECT 1) OR TRUE"); queryTemplate("SELECT %projection% FROM nation WHERE %condition%") .replaceAll(projections, conditions) .forEach(this::assertQuery); queryTemplate("SELECT %projection% FROM nation WHERE (%condition%) AND nationkey <3") .replaceAll(projections, conditions) .forEach(this::assertQuery); assertQuery( "SELECT count(*) FROM nation WHERE (SELECT true FROM (SELECT 1) t(a) WHERE a = nationkey) OR TRUE", "SELECT 25"); assertQuery( "SELECT (SELECT true FROM (SELECT 1) t(a) WHERE a = nationkey) " + "FROM nation " + "WHERE (SELECT true FROM (SELECT 1) t(a) WHERE a = nationkey) OR TRUE " + "ORDER BY nationkey " + "LIMIT 2", "VALUES true, null"); }
joinType, condition); List<QueryTemplate.Parameter> conditions = condition.of( "EXISTS(SELECT avg(orderkey) FROM orders)", "(SELECT avg(orderkey) FROM orders) > 3"); for (QueryTemplate.Parameter actualCondition : conditions) { for (QueryTemplate.Parameter actualJoinType : joinType.of("", "LEFT", "RIGHT")) { assertQuery(queryTemplate.replace(actualJoinType, actualCondition)); queryTemplate.replace(joinType.of("FULL"), actualCondition), "VALUES (1, 10), (2, 10), (3, 10), (4, 10), (5, 10)");
QueryTemplate.Parameter type = parameter("type").of(""); QueryTemplate.Parameter condition = parameter("condition").of("true"); QueryTemplate queryTemplate = queryTemplate( "SELECT * FROM (VALUES 1,2,3,4) t(x) %type% JOIN (VALUES 1,2,3,5) t2(y) ON %condition%", QueryTemplate.Parameter twoDuplicatedInSubqueriesCondition = condition.of( "(x in (VALUES 1,2,3)) = (y in (VALUES 1,2,3)) AND (x in (VALUES 1,2,4)) = (y in (VALUES 1,2,4))"); assertQuery( "VALUES (1,1), (1,2), (2,2), (2,1), (3,3)"); assertQuery( queryTemplate.replace(condition.of("(x in (VALUES 1,2)) = (y in (VALUES 1,2)) AND (x in (VALUES 1)) = (y in (VALUES 3))")), "VALUES (2,2), (2,1), (3,5), (4,5)"); assertQuery( queryTemplate.replace(condition.of("(x in (VALUES 1,2)) = (y in (VALUES 1,2)) AND (x in (VALUES 1)) != (y in (VALUES 3))")), "VALUES (1,2), (1,1), (3, 3), (4,3)"); assertQuery( queryTemplate.replace(condition.of("(x in (VALUES 1)) = (y in (VALUES 1)) AND (x in (SELECT 2)) != (y in (SELECT 2))")), "VALUES (2,3), (2,5), (3, 2), (4,2)"); QueryTemplate.Parameter left = type.of("left"); QueryTemplate.Parameter right = type.of("right"); QueryTemplate.Parameter full = type.of("full"); for (QueryTemplate.Parameter joinType : ImmutableList.of(left, right, full)) { for (String joinCondition : ImmutableList.of("x IN (VALUES 1)", "y in (VALUES 1)")) { assertQueryFails( queryTemplate.replace(joinType, condition.of(joinCondition)), ".*IN with subquery predicate in join condition is not supported");
QueryTemplate.Parameter type = parameter("type").of(""); QueryTemplate.Parameter condition = parameter("condition"); QueryTemplate queryTemplate = queryTemplate( QueryTemplate.Parameter xPlusYEqualsSubqueryJoinCondition = condition.of("(x+y = (SELECT 4))"); assertQuery( queryTemplate.replace(xPlusYEqualsSubqueryJoinCondition), "VALUES (1,3), (2,2), (3,1)"); assertQuery(queryTemplate.replace(condition.of("(x+y = (VALUES 4)) AND (x*y = (VALUES 4))")), "VALUES (2,2)"); queryTemplate.replace(condition.of("x+y > (VALUES 3) AND (x = (VALUES 3)) != (y = (VALUES 3))")), "VALUES (3,1), (3,2), (1,3), (2,3), (4,3), (3,5)"); assertQuery( queryTemplate.replace(condition.of("x+y >= (VALUES 5) AND (x = (VALUES 3)) != (y = (VALUES 3))")), "VALUES (3,2), (2,3), (4,3), (3,5)"); assertQuery( queryTemplate.replace(condition.of("x+y >= (VALUES 3) AND (x = (VALUES 5)) != (y = (VALUES 3))")), "VALUES (1,3), (2,3), (3,3), (4,3)"); assertQuery( queryTemplate.replace(condition.of("x+y >= (VALUES 3) AND (x = (VALUES 3)) != (y = (VALUES 5))")), "VALUES (3,1), (3,2), (3,3), (1,5), (2,5), (4,5)"); assertQuery( queryTemplate.replace(condition.of("x+y >= (VALUES 4) AND (x = (VALUES 3)) != (y = (VALUES 5))")), "VALUES (3,1), (3,2), (3,3), (1,5), (2,5), (4,5)"); queryTemplate.replace(type.of("left"), xPlusYEqualsSubqueryJoinCondition), "VALUES (1,3), (2,2), (3,1), (4, null)"); assertQuery( queryTemplate.replace(type.of("right"), xPlusYEqualsSubqueryJoinCondition),
@Test public void testJoinWithInSubqueryToBeExecutedAsPostJoinFilter() { QueryTemplate.Parameter type = parameter("type").of(""); QueryTemplate.Parameter condition = parameter("condition").of("true"); QueryTemplate queryTemplate = queryTemplate( "SELECT * FROM (VALUES 1,2,3,4) t(x) %type% JOIN (VALUES 1,2,3,5) t2(y) ON %condition%", type, condition); assertQuery( queryTemplate.replace(condition.of("(x+y in (VALUES 4))")), "VALUES (1,3), (2,2), (3,1)"); assertQuery( queryTemplate.replace(condition.of("(x+y in (VALUES 4)) AND (x*y in (VALUES 4,5))")), "VALUES (2,2)"); assertQuery( queryTemplate.replace(condition.of("(x+y in (VALUES 4,5)) AND (x*y IN (VALUES 4,5))")), "VALUES (4,1), (2,2)"); assertQuery( queryTemplate.replace(condition.of("(x+y in (VALUES 4,5)) AND (x in (VALUES 4,5)) != (y in (VALUES 4,5))")), "VALUES (4,1)"); for (QueryTemplate.Parameter joinType : type.of("left", "right", "full")) { assertQueryFails( queryTemplate.replace( joinType, condition.of("(x+y in (VALUES 4,5)) AND (x in (VALUES 4,5)) != (y in (VALUES 4,5))")), ".*IN with subquery predicate in join condition is not supported"); } }
@Test public void testJoinWithMultipleScalarSubqueryClauses() { QueryTemplate.Parameter type = parameter("type").of(""); QueryTemplate.Parameter condition = parameter("condition"); QueryTemplate queryTemplate = queryTemplate( "SELECT * FROM (VALUES 1,2,3,4) t(x) %type% JOIN (VALUES 1,2,3,5) t2(y) ON %condition%", type, condition); QueryTemplate.Parameter multipleScalarJoinCondition = condition.of("(x = (VALUES 1)) AND (y = (VALUES 2)) AND (x in (VALUES 2)) = (y in (VALUES 1))"); assertQuery(queryTemplate.replace(multipleScalarJoinCondition), "VALUES (1,2)"); assertQuery( queryTemplate.replace(condition.of("(x = (VALUES 2)) = (y > (VALUES 0)) AND (x > (VALUES 1)) = (y < (VALUES 3))")), "VALUES (2,2), (2,1)"); assertQuery( queryTemplate.replace(condition.of("(x = (VALUES 1)) = (y = (VALUES 1)) AND (x = (SELECT 2)) != (y = (SELECT 3))")), "VALUES (2,5), (2,2), (3,3), (4,3)"); assertQuery( queryTemplate.replace(type.of("left"), multipleScalarJoinCondition), "VALUES (1,2), (2,null), (3, null), (4, null)"); assertQuery( queryTemplate.replace(type.of("right"), multipleScalarJoinCondition), "VALUES (1,2), (null,1), (null, 3), (null, 5)"); assertQuery( queryTemplate.replace(type.of("full"), multipleScalarJoinCondition), "VALUES (1,2), (2,null), (3, null), (4, null), (null,1), (null, 3), (null, 5)"); }
@Test public void testSubqueriesWithDisjunction() { List<QueryTemplate.Parameter> projections = parameter("projection").of("count(*)", "*", "%condition%"); List<QueryTemplate.Parameter> conditions = parameter("condition").of( "nationkey IN (SELECT 1) OR TRUE", "EXISTS(SELECT 1) OR TRUE"); queryTemplate("SELECT %projection% FROM nation WHERE %condition%") .replaceAll(projections, conditions) .forEach(this::assertQuery); queryTemplate("SELECT %projection% FROM nation WHERE (%condition%) AND nationkey <3") .replaceAll(projections, conditions) .forEach(this::assertQuery); assertQuery( "SELECT count(*) FROM nation WHERE (SELECT true FROM (SELECT 1) t(a) WHERE a = nationkey) OR TRUE", "SELECT 25"); assertQuery( "SELECT (SELECT true FROM (SELECT 1) t(a) WHERE a = nationkey) " + "FROM nation " + "WHERE (SELECT true FROM (SELECT 1) t(a) WHERE a = nationkey) OR TRUE " + "ORDER BY nationkey " + "LIMIT 2", "VALUES true, null"); }
@Test public void testPreparedStatementWithSubqueries() { List<QueryTemplate.Parameter> leftValues = parameter("left").of( "", "1 = ", "EXISTS", "1 IN", "1 = ANY", "1 = ALL", "2 <> ANY", "2 <> ALL", "0 < ALL", "0 < ANY", "1 <= ALL", "1 <= ANY"); queryTemplate("SELECT %left% (SELECT 1 WHERE 2 = ?)") .replaceAll(leftValues) .forEach(query -> { Session session = Session.builder(getSession()) .addPreparedStatement("my_query", query) .build(); assertQuery(session, "EXECUTE my_query USING 2", "SELECT true"); }); }
@Test public void testJoinWithScalarSubqueryToBeExecutedAsPostJoinFilterWithEmptyInnerTable() { String noOutputQuery = "SELECT 1 WHERE false"; QueryTemplate.Parameter type = parameter("type").of(""); QueryTemplate.Parameter condition = parameter("condition"); QueryTemplate queryTemplate = queryTemplate( "SELECT * FROM (" + noOutputQuery + ") t(x) %type% JOIN (VALUES 1) t2(y) ON %condition%", type); QueryTemplate.Parameter xPlusYEqualsSubqueryJoinCondition = condition.of("(x+y = (SELECT 4))"); assertQuery(queryTemplate.replace(xPlusYEqualsSubqueryJoinCondition), noOutputQuery); assertQuery(queryTemplate.replace(condition.of("(x+y = (VALUES 4)) AND (x*y = (VALUES 4))")), noOutputQuery); // non inner joins assertQuery(queryTemplate.replace(xPlusYEqualsSubqueryJoinCondition, type.of("left")), noOutputQuery); assertQuery(queryTemplate.replace(xPlusYEqualsSubqueryJoinCondition, type.of("right")), "VALUES (null,1)"); assertQuery(queryTemplate.replace(xPlusYEqualsSubqueryJoinCondition, type.of("full")), "VALUES (null,1)"); }
@Test public void testOrderByWithAggregation() { assertQuery("" + "SELECT x, sum(cast(x AS double))\n" + "FROM (VALUES '1.0') t(x)\n" + "GROUP BY x\n" + "ORDER BY sum(cast(t.x AS double))", "VALUES ('1.0', 1.0)"); queryTemplate("SELECT count(*) %output% FROM (SELECT substr(name,1,1) letter FROM nation) x GROUP BY %groupBy% ORDER BY %orderBy%") .replaceAll( parameter("output").of("", ", letter", ", letter AS y"), parameter("groupBy").of("x.letter", "letter"), parameter("orderBy").of("x.letter", "letter")) .forEach(this::assertQueryOrdered); }
@DataProvider(name = "quantified_comparisons_corner_cases") public Object[][] qualifiedComparisonsCornerCases() { //the %subquery% is wrapped in a SELECT so that H2 does not blow up on the VALUES subquery return queryTemplate("SELECT %value% %operator% %quantifier% (SELECT * FROM (%subquery%))") .replaceAll( parameter("subquery").of( "SELECT 1 WHERE false", "SELECT CAST(NULL AS INTEGER)", "VALUES (1), (NULL)"), parameter("quantifier").of("ALL", "ANY"), parameter("value").of("1", "NULL"), parameter("operator").of("=", "!=", "<", ">", "<=", ">=")) .collect(toDataProvider()); }
@Test public void testOuterJoinWithComplexCorrelatedSubquery() { QueryTemplate.Parameter type = parameter("type"); QueryTemplate.Parameter condition = parameter("condition"); QueryTemplate queryTemplate = queryTemplate( "SELECT * FROM (VALUES 1,2,3,4) t(x) %type% JOIN (VALUES 1,2,3,5) t2(y) ON %condition%", type, condition); queryTemplate.replaceAll( (query) -> assertQueryFails(query, "line .*: .* is not supported"), ImmutableList.of(type.of("left"), type.of("right"), type.of("full")), ImmutableList.of( condition.of("EXISTS(SELECT 1 WHERE x = y)"), condition.of("(SELECT x = y)"), condition.of("true IN (SELECT x = y)"))); }
joinType, condition); List<QueryTemplate.Parameter> conditions = condition.of( "EXISTS(SELECT avg(orderkey) FROM orders)", "(SELECT avg(orderkey) FROM orders) > 3"); for (QueryTemplate.Parameter actualCondition : conditions) { for (QueryTemplate.Parameter actualJoinType : joinType.of("", "LEFT", "RIGHT")) { assertQuery(queryTemplate.replace(actualJoinType, actualCondition)); queryTemplate.replace(joinType.of("FULL"), actualCondition), "VALUES (1, 10), (2, 10), (3, 10), (4, 10), (5, 10)");
QueryTemplate.Parameter type = parameter("type").of(""); QueryTemplate.Parameter condition = parameter("condition"); QueryTemplate queryTemplate = queryTemplate( QueryTemplate.Parameter xPlusYEqualsSubqueryJoinCondition = condition.of("(x+y = (SELECT 4))"); assertQuery( queryTemplate.replace(xPlusYEqualsSubqueryJoinCondition), "VALUES (1,3), (2,2), (3,1)"); assertQuery(queryTemplate.replace(condition.of("(x+y = (VALUES 4)) AND (x*y = (VALUES 4))")), "VALUES (2,2)"); queryTemplate.replace(condition.of("x+y > (VALUES 3) AND (x = (VALUES 3)) != (y = (VALUES 3))")), "VALUES (3,1), (3,2), (1,3), (2,3), (4,3), (3,5)"); assertQuery( queryTemplate.replace(condition.of("x+y >= (VALUES 5) AND (x = (VALUES 3)) != (y = (VALUES 3))")), "VALUES (3,2), (2,3), (4,3), (3,5)"); assertQuery( queryTemplate.replace(condition.of("x+y >= (VALUES 3) AND (x = (VALUES 5)) != (y = (VALUES 3))")), "VALUES (1,3), (2,3), (3,3), (4,3)"); assertQuery( queryTemplate.replace(condition.of("x+y >= (VALUES 3) AND (x = (VALUES 3)) != (y = (VALUES 5))")), "VALUES (3,1), (3,2), (3,3), (1,5), (2,5), (4,5)"); assertQuery( queryTemplate.replace(condition.of("x+y >= (VALUES 4) AND (x = (VALUES 3)) != (y = (VALUES 5))")), "VALUES (3,1), (3,2), (3,3), (1,5), (2,5), (4,5)"); queryTemplate.replace(type.of("left"), xPlusYEqualsSubqueryJoinCondition), "VALUES (1,3), (2,2), (3,1), (4, null)"); assertQuery( queryTemplate.replace(type.of("right"), xPlusYEqualsSubqueryJoinCondition),
QueryTemplate.Parameter type = parameter("type").of(""); QueryTemplate.Parameter condition = parameter("condition").of("true"); QueryTemplate queryTemplate = queryTemplate( "SELECT * FROM (VALUES 1,2,3,4) t(x) %type% JOIN (VALUES 1,2,3,5) t2(y) ON %condition%", QueryTemplate.Parameter twoDuplicatedInSubqueriesCondition = condition.of( "(x in (VALUES 1,2,3)) = (y in (VALUES 1,2,3)) AND (x in (VALUES 1,2,4)) = (y in (VALUES 1,2,4))"); assertQuery( "VALUES (1,1), (1,2), (2,2), (2,1), (3,3)"); assertQuery( queryTemplate.replace(condition.of("(x in (VALUES 1,2)) = (y in (VALUES 1,2)) AND (x in (VALUES 1)) = (y in (VALUES 3))")), "VALUES (2,2), (2,1), (3,5), (4,5)"); assertQuery( queryTemplate.replace(condition.of("(x in (VALUES 1,2)) = (y in (VALUES 1,2)) AND (x in (VALUES 1)) != (y in (VALUES 3))")), "VALUES (1,2), (1,1), (3, 3), (4,3)"); assertQuery( queryTemplate.replace(condition.of("(x in (VALUES 1)) = (y in (VALUES 1)) AND (x in (SELECT 2)) != (y in (SELECT 2))")), "VALUES (2,3), (2,5), (3, 2), (4,2)"); QueryTemplate.Parameter left = type.of("left"); QueryTemplate.Parameter right = type.of("right"); QueryTemplate.Parameter full = type.of("full"); for (QueryTemplate.Parameter joinType : ImmutableList.of(left, right, full)) { for (String joinCondition : ImmutableList.of("x IN (VALUES 1)", "y in (VALUES 1)")) { assertQueryFails( queryTemplate.replace(joinType, condition.of(joinCondition)), ".*IN with subquery predicate in join condition is not supported");
@Test public void testJoinWithInSubqueryToBeExecutedAsPostJoinFilter() { QueryTemplate.Parameter type = parameter("type").of(""); QueryTemplate.Parameter condition = parameter("condition").of("true"); QueryTemplate queryTemplate = queryTemplate( "SELECT * FROM (VALUES 1,2,3,4) t(x) %type% JOIN (VALUES 1,2,3,5) t2(y) ON %condition%", type, condition); assertQuery( queryTemplate.replace(condition.of("(x+y in (VALUES 4))")), "VALUES (1,3), (2,2), (3,1)"); assertQuery( queryTemplate.replace(condition.of("(x+y in (VALUES 4)) AND (x*y in (VALUES 4,5))")), "VALUES (2,2)"); assertQuery( queryTemplate.replace(condition.of("(x+y in (VALUES 4,5)) AND (x*y IN (VALUES 4,5))")), "VALUES (4,1), (2,2)"); assertQuery( queryTemplate.replace(condition.of("(x+y in (VALUES 4,5)) AND (x in (VALUES 4,5)) != (y in (VALUES 4,5))")), "VALUES (4,1)"); for (QueryTemplate.Parameter joinType : type.of("left", "right", "full")) { assertQueryFails( queryTemplate.replace( joinType, condition.of("(x+y in (VALUES 4,5)) AND (x in (VALUES 4,5)) != (y in (VALUES 4,5))")), ".*IN with subquery predicate in join condition is not supported"); } }
@Test public void testJoinWithMultipleScalarSubqueryClauses() { QueryTemplate.Parameter type = parameter("type").of(""); QueryTemplate.Parameter condition = parameter("condition"); QueryTemplate queryTemplate = queryTemplate( "SELECT * FROM (VALUES 1,2,3,4) t(x) %type% JOIN (VALUES 1,2,3,5) t2(y) ON %condition%", type, condition); QueryTemplate.Parameter multipleScalarJoinCondition = condition.of("(x = (VALUES 1)) AND (y = (VALUES 2)) AND (x in (VALUES 2)) = (y in (VALUES 1))"); assertQuery(queryTemplate.replace(multipleScalarJoinCondition), "VALUES (1,2)"); assertQuery( queryTemplate.replace(condition.of("(x = (VALUES 2)) = (y > (VALUES 0)) AND (x > (VALUES 1)) = (y < (VALUES 3))")), "VALUES (2,2), (2,1)"); assertQuery( queryTemplate.replace(condition.of("(x = (VALUES 1)) = (y = (VALUES 1)) AND (x = (SELECT 2)) != (y = (SELECT 3))")), "VALUES (2,5), (2,2), (3,3), (4,3)"); assertQuery( queryTemplate.replace(type.of("left"), multipleScalarJoinCondition), "VALUES (1,2), (2,null), (3, null), (4, null)"); assertQuery( queryTemplate.replace(type.of("right"), multipleScalarJoinCondition), "VALUES (1,2), (null,1), (null, 3), (null, 5)"); assertQuery( queryTemplate.replace(type.of("full"), multipleScalarJoinCondition), "VALUES (1,2), (2,null), (3, null), (4, null), (null,1), (null, 3), (null, 5)"); }
@Test public void testPreparedStatementWithSubqueries() { List<QueryTemplate.Parameter> leftValues = parameter("left").of( "", "1 = ", "EXISTS", "1 IN", "1 = ANY", "1 = ALL", "2 <> ANY", "2 <> ALL", "0 < ALL", "0 < ANY", "1 <= ALL", "1 <= ANY"); queryTemplate("SELECT %left% (SELECT 1 WHERE 2 = ?)") .replaceAll(leftValues) .forEach(query -> { Session session = Session.builder(getSession()) .addPreparedStatement("my_query", query) .build(); assertQuery(session, "EXECUTE my_query USING 2", "SELECT true"); }); }
@Test public void testJoinWithScalarSubqueryToBeExecutedAsPostJoinFilterWithEmptyInnerTable() { String noOutputQuery = "SELECT 1 WHERE false"; QueryTemplate.Parameter type = parameter("type").of(""); QueryTemplate.Parameter condition = parameter("condition"); QueryTemplate queryTemplate = queryTemplate( "SELECT * FROM (" + noOutputQuery + ") t(x) %type% JOIN (VALUES 1) t2(y) ON %condition%", type); QueryTemplate.Parameter xPlusYEqualsSubqueryJoinCondition = condition.of("(x+y = (SELECT 4))"); assertQuery(queryTemplate.replace(xPlusYEqualsSubqueryJoinCondition), noOutputQuery); assertQuery(queryTemplate.replace(condition.of("(x+y = (VALUES 4)) AND (x*y = (VALUES 4))")), noOutputQuery); // non inner joins assertQuery(queryTemplate.replace(xPlusYEqualsSubqueryJoinCondition, type.of("left")), noOutputQuery); assertQuery(queryTemplate.replace(xPlusYEqualsSubqueryJoinCondition, type.of("right")), "VALUES (null,1)"); assertQuery(queryTemplate.replace(xPlusYEqualsSubqueryJoinCondition, type.of("full")), "VALUES (null,1)"); }