@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()); }
public static QueryTemplate queryTemplate(String queryTemplate, Parameter... parameters) { return new QueryTemplate(queryTemplate, parameters); }
private void checkQueryHasAllParametersReplaced(String query) { for (Parameter parameter : defaultParameters) { String queryParameterKey = asQueryParameterKey(parameter.getKey()); checkArgument( !query.contains(queryParameterKey), "Query template parameters was not given: %s", queryParameterKey); } }
public String replace(Parameter... parameters) { String query = queryTemplate; for (Parameter parameter : parameters) { query = resolve(query, parameter); } for (Parameter parameter : defaultParameters) { query = resolve(query, parameter); } checkQueryHasAllParametersReplaced(query); return query; }
@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)"); }
private void replaceAll(String queryTemplate, Consumer<String> queryConsumer, List<List<Parameter>> parametersLists) { if (parametersLists.size() == 0) { checkQueryHasAllParametersReplaced(queryTemplate); queryConsumer.accept(queryTemplate); } else { List<List<Parameter>> restParameters = IntStream.range(1, parametersLists.size()) .mapToObj(parametersLists::get) .collect(toImmutableList()); for (Parameter parameter : parametersLists.get(0)) { String intermediateQueryTemplate = resolve(queryTemplate, parameter); replaceAll(intermediateQueryTemplate, queryConsumer, restParameters); } } }
public void replaceAll(Consumer<String> queryConsumer, List<Parameter>... parametersLists) { requireNonNull(queryConsumer, "queryConsumer is null"); replaceAll(queryTemplate, queryConsumer, ImmutableList.copyOf(parametersLists)); }
@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)"); }
@SafeVarargs public final Stream<String> replaceAll(List<Parameter>... parametersLists) { List<String> queries = new ArrayList<>(); replaceAll(queries::add, parametersLists); return queries.stream(); }
@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); }
@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"); } }
private QueryTemplate(String queryTemplate, Parameter... parameters) { for (Parameter parameter : parameters) { String queryParameterKey = asQueryParameterKey(parameter.getKey()); checkArgument( queryTemplate.contains(queryParameterKey), "Query template does not contain: %s", queryParameterKey); } this.queryTemplate = queryTemplate; this.defaultParameters = ImmutableList.copyOf(parameters); }
@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"); }
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, "(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( queryTemplate.replace(twoDuplicatedInSubqueriesCondition), "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)"); 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.replace(left, twoDuplicatedInSubqueriesCondition), "VALUES (1,1), (1,2), (2,2), (2,1), (3,3), (4, null)"); assertQuery( queryTemplate.replace(right, twoDuplicatedInSubqueriesCondition),
private String resolve(String query, Parameter parameter) { return parameter.getValue() .map(value -> query.replaceAll(asQueryParameterKey(parameter.getKey()), value)) .orElse(query); }
@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)"))); }
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, 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 testSubqueryPruning() { List<QueryTemplate.Parameter> subqueries = QueryTemplate.parameter("subquery").of( "orderkey IN (SELECT orderkey FROM lineitem WHERE orderkey % 2 = 0)", "EXISTS(SELECT orderkey FROM lineitem WHERE orderkey % 2 = 0)", "0 = (SELECT orderkey FROM lineitem WHERE orderkey % 2 = 0)"); queryTemplate("SELECT COUNT(*) FROM (SELECT %subquery% FROM orders)") .replaceAll(subqueries) .forEach(this::assertPlanContainsNoApplyOrAnyJoin); queryTemplate("SELECT * FROM orders WHERE true OR %subquery%") .replaceAll(subqueries) .forEach(this::assertPlanContainsNoApplyOrAnyJoin); }
QueryTemplate.Parameter joinType = parameter("join_type"); QueryTemplate.Parameter condition = parameter("condition"); QueryTemplate queryTemplate = queryTemplate( "SELECT o1.orderkey, COUNT(*) " + "FROM orders o1 %join_type% JOIN (SELECT * FROM orders LIMIT 10) o2 ON %condition% " + 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)");
@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"); }); }