public void replaceAll(Consumer<String> queryConsumer, List<Parameter>... parametersLists) { requireNonNull(queryConsumer, "queryConsumer is null"); replaceAll(queryTemplate, queryConsumer, ImmutableList.copyOf(parametersLists)); }
@SafeVarargs public final Stream<String> replaceAll(List<Parameter>... parametersLists) { List<String> queries = new ArrayList<>(); replaceAll(queries::add, parametersLists); return queries.stream(); }
@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 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); }
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); } } }
@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 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)"))); }
@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"); }); }
@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 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 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 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)"))); }
@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"); }); }
@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()); }