Examples with UNION_ALL org.jooq.impl.CombineOperator.UNION_ALL used on opensource projects

Example 1 with UNION_ALL

use of org.jooq.impl.CombineOperator.UNION_ALL in project jOOQ by jOOQ.

the class SelectQueryImpl method toSQLReference0.

/**
 * This method renders the main part of a query without the LIMIT clause.
 * This part is common to any type of limited query
 */
@SuppressWarnings("unchecked")
private final void toSQLReference0(Context<?> context, List<Field<?>> originalFields, List<Field<?>> alternativeFields) {
    SQLDialect family = context.family();
    boolean qualify = context.qualify();
    int unionOpSize = unionOp.size();
    boolean unionParensRequired = false;
    boolean unionOpNesting = false;
    // The SQL standard specifies:
    // 
    // <query expression> ::=
    // [ <with clause> ] <query expression body>
    // [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]
    // 
    // Depending on the dialect and on various syntax elements, parts of the above must be wrapped in
    // synthetic parentheses
    boolean wrapQueryExpressionInDerivedTable;
    boolean wrapQueryExpressionBodyInDerivedTable;
    boolean applySeekOnDerivedTable = applySeekOnDerivedTable();
    wrapQueryExpressionInDerivedTable = false;
    if (wrapQueryExpressionInDerivedTable)
        context.visit(K_SELECT).sql(" *").formatSeparator().visit(K_FROM).sql(" (").formatIndentStart().formatNewLine();
    wrapQueryExpressionBodyInDerivedTable = false || // predicate must be applied on a derived table, not on the individual subqueries
    applySeekOnDerivedTable;
    if (wrapQueryExpressionBodyInDerivedTable) {
        context.visit(K_SELECT).sql(' ');
        context.formatIndentStart().formatNewLine().sql("t.*");
        if (alternativeFields != null && originalFields.size() < alternativeFields.size())
            context.sql(", ").formatSeparator().declareFields(true, c -> c.visit(alternativeFields.get(alternativeFields.size() - 1)));
        context.formatIndentEnd().formatSeparator().visit(K_FROM).sql(" (").formatIndentStart().formatNewLine();
    }
    // all databases, we need to wrap relevant subqueries in parentheses.
    if (unionOpSize > 0) {
        if (!TRUE.equals(context.data(DATA_NESTED_SET_OPERATIONS)))
            context.data(DATA_NESTED_SET_OPERATIONS, unionOpNesting = unionOpNesting());
        for (int i = unionOpSize - 1; i >= 0; i--) {
            switch(unionOp.get(i)) {
                case EXCEPT:
                    context.start(SELECT_EXCEPT);
                    break;
                case EXCEPT_ALL:
                    context.start(SELECT_EXCEPT_ALL);
                    break;
                case INTERSECT:
                    context.start(SELECT_INTERSECT);
                    break;
                case INTERSECT_ALL:
                    context.start(SELECT_INTERSECT_ALL);
                    break;
                case UNION:
                    context.start(SELECT_UNION);
                    break;
                case UNION_ALL:
                    context.start(SELECT_UNION_ALL);
                    break;
            }
            // [#3676] There might be cases where nested set operations do not
            // imply required parentheses in some dialects, but better
            // play safe than sorry
            unionParenthesis(context, '(', alternativeFields != null ? alternativeFields : getSelect(), derivedTableRequired(context, this), unionParensRequired = unionOpNesting || unionParensRequired(context));
        }
    }
    traverseJoins(getFrom(), t -> {
        if (t instanceof TableImpl)
            context.scopeRegister(t, true);
    });
    for (Entry<QueryPart, QueryPart> entry : localQueryPartMapping.entrySet()) context.scopeRegister(entry.getKey(), true, entry.getValue());
    // SELECT clause
    // -------------
    context.start(SELECT_SELECT).visit(K_SELECT).separatorRequired(true);
    // [#1493] Oracle hints come directly after the SELECT keyword
    if (!StringUtils.isBlank(hint))
        context.sql(' ').sql(hint).separatorRequired(true);
    if (Tools.isNotEmpty(distinctOn))
        context.visit(K_DISTINCT_ON).sql(" (").visit(distinctOn).sql(')').separatorRequired(true);
    else if (distinct)
        context.visit(K_DISTINCT).separatorRequired(true);
    if (TRUE.equals(context.data(BooleanDataKey.DATA_RENDERING_DATA_CHANGE_DELTA_TABLE)))
        context.qualify(false);
    context.declareFields(true);
    // non-ambiguous column names as ambiguous column names are not allowed in subqueries
    if (alternativeFields != null)
        if (wrapQueryExpressionBodyInDerivedTable && originalFields.size() < alternativeFields.size())
            context.visit(new SelectFieldList<>(alternativeFields.subList(0, originalFields.size())));
        else
            context.visit(new SelectFieldList<>(alternativeFields));
    else
        // The default behaviour
        context.visit(getSelectResolveUnsupportedAsterisks(context.configuration()));
    if (TRUE.equals(context.data(BooleanDataKey.DATA_RENDERING_DATA_CHANGE_DELTA_TABLE)))
        context.qualify(qualify);
    context.declareFields(false).end(SELECT_SELECT);
    // only in top level SELECTs
    if (!context.subquery()) {
        context.start(SELECT_INTO);
        QueryPart actualIntoTable = (QueryPart) context.data(DATA_SELECT_INTO_TABLE);
        if (actualIntoTable == null)
            actualIntoTable = intoTable;
        if (actualIntoTable != null && !TRUE.equals(context.data(DATA_OMIT_INTO_CLAUSE)) && (SUPPORT_SELECT_INTO_TABLE.contains(context.dialect()) || !(actualIntoTable instanceof Table))) {
            context.formatSeparator().visit(K_INTO).sql(' ').visit(actualIntoTable);
        }
        context.end(SELECT_INTO);
    }
    // FROM and JOIN clauses
    // ---------------------
    context.start(SELECT_FROM).declareTables(true);
    // [#....] Some SQL dialects do not require a FROM clause. Others do and
    // jOOQ generates a "DUAL" table or something equivalent.
    // See also org.jooq.impl.Dual for details.
    boolean hasFrom = !getFrom().isEmpty() || !OPTIONAL_FROM_CLAUSE.contains(context.dialect());
    List<Condition> semiAntiJoinPredicates = null;
    ConditionProviderImpl where = getWhere(context);
    if (hasFrom) {
        Object previousCollect = context.data(DATA_COLLECT_SEMI_ANTI_JOIN, true);
        Object previousCollected = context.data(DATA_COLLECTED_SEMI_ANTI_JOIN, null);
        TableList tablelist = getFrom();
        tablelist = transformInlineDerivedTables(tablelist, where);
        context.formatSeparator().visit(K_FROM).separatorRequired(true).visit(tablelist);
        semiAntiJoinPredicates = (List<Condition>) context.data(DATA_COLLECTED_SEMI_ANTI_JOIN, previousCollected);
        context.data(DATA_COLLECT_SEMI_ANTI_JOIN, previousCollect);
    }
    context.declareTables(false).end(SELECT_FROM);
    // WHERE clause
    // ------------
    context.start(SELECT_WHERE);
    if (TRUE.equals(context.data().get(BooleanDataKey.DATA_SELECT_NO_DATA)))
        context.formatSeparator().visit(K_WHERE).sql(' ').visit(falseCondition());
    else if (!where.hasWhere() && semiAntiJoinPredicates == null)
        ;
    else {
        ConditionProviderImpl actual = new ConditionProviderImpl();
        if (semiAntiJoinPredicates != null)
            actual.addConditions(semiAntiJoinPredicates);
        if (where.hasWhere())
            actual.addConditions(where.getWhere());
        context.formatSeparator().visit(K_WHERE).sql(' ').visit(actual);
    }
    context.end(SELECT_WHERE);
    // GROUP BY and HAVING clause
    // --------------------------
    context.start(SELECT_GROUP_BY);
    if (!getGroupBy().isEmpty()) {
        context.formatSeparator().visit(K_GROUP_BY);
        if (groupByDistinct)
            context.sql(' ').visit(K_DISTINCT);
        context.separatorRequired(true);
        context.visit(groupBy);
    }
    context.end(SELECT_GROUP_BY);
    // HAVING clause
    // -------------
    context.start(SELECT_HAVING);
    if (getHaving().hasWhere())
        context.formatSeparator().visit(K_HAVING).sql(' ').visit(getHaving());
    context.end(SELECT_HAVING);
    // WINDOW clause
    // -------------
    context.start(SELECT_WINDOW);
    if (Tools.isNotEmpty(window) && !NO_SUPPORT_WINDOW_CLAUSE.contains(context.dialect()))
        context.formatSeparator().visit(K_WINDOW).separatorRequired(true).declareWindows(true, c -> c.visit(window));
    context.end(SELECT_WINDOW);
    if (getQualify().hasWhere())
        context.formatSeparator().visit(K_QUALIFY).sql(' ').visit(getQualify());
    // ORDER BY clause for local subselect
    // -----------------------------------
    toSQLOrderBy(context, originalFields, alternativeFields, false, wrapQueryExpressionBodyInDerivedTable, orderBy, limit);
    // --------------------------------------------
    if (unionOpSize > 0) {
        unionParenthesis(context, ')', null, derivedTableRequired(context, this), unionParensRequired);
        for (int i = 0; i < unionOpSize; i++) {
            CombineOperator op = unionOp.get(i);
            for (Select<?> other : union.get(i)) {
                boolean derivedTableRequired = derivedTableRequired(context, other);
                context.formatSeparator().visit(op.toKeyword(family));
                if (unionParensRequired)
                    context.sql(' ');
                else
                    context.formatSeparator();
                unionParenthesis(context, '(', other.getSelect(), derivedTableRequired, unionParensRequired);
                context.visit(other);
                unionParenthesis(context, ')', null, derivedTableRequired, unionParensRequired);
            }
            // [#1658] Close parentheses opened previously
            if (i < unionOpSize - 1)
                unionParenthesis(context, ')', null, derivedTableRequired(context, this), unionParensRequired);
            switch(unionOp.get(i)) {
                case EXCEPT:
                    context.end(SELECT_EXCEPT);
                    break;
                case EXCEPT_ALL:
                    context.end(SELECT_EXCEPT_ALL);
                    break;
                case INTERSECT:
                    context.end(SELECT_INTERSECT);
                    break;
                case INTERSECT_ALL:
                    context.end(SELECT_INTERSECT_ALL);
                    break;
                case UNION:
                    context.end(SELECT_UNION);
                    break;
                case UNION_ALL:
                    context.end(SELECT_UNION_ALL);
                    break;
            }
        }
        if (unionOpNesting)
            context.data().remove(DATA_NESTED_SET_OPERATIONS);
    }
    if (wrapQueryExpressionBodyInDerivedTable) {
        context.formatIndentEnd().formatNewLine().sql(") t");
        if (applySeekOnDerivedTable) {
            context.formatSeparator().visit(K_WHERE).sql(' ').qualify(false, c -> c.visit(getSeekCondition()));
        }
    }
    // ORDER BY clause for UNION
    // -------------------------
    context.qualify(false, c -> toSQLOrderBy(context, originalFields, alternativeFields, wrapQueryExpressionInDerivedTable, wrapQueryExpressionBodyInDerivedTable, unionOrderBy, unionLimit));
}