Application: gvSIG desktop

/**

 * gvSIG. Desktop Geographic Information System.

 *

 * Copyright (C) 2007-2020 gvSIG Association.

 *

 * This program is free software; you can redistribute it and/or

 * modify it under the terms of the GNU General Public License

 * as published by the Free Software Foundation; either version 3

 * of the License, or (at your option) any later version.

 *

 * This program is distributed in the hope that it will be useful,

 * but WITHOUT ANY WARRANTY; without even the implied warranty of

 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

 * GNU General Public License for more details.

 *

 * You should have received a copy of the GNU General Public License

 * along with this program; if not, write to the Free Software

 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,

 * MA  02110-1301, USA.

 *

 * For any additional information, do not hesitate to contact us

 * at info AT gvsig.com, or visit our website www.gvsig.com.

 */

package org.gvsig.fmap.dal.store.jdbc2.spi.operations;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import org.apache.commons.lang3.ArrayUtils;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.expressionevaluator.Code;

import org.gvsig.expressionevaluator.Expression;

import org.gvsig.expressionevaluator.ExpressionBuilder;

import static org.gvsig.expressionevaluator.ExpressionBuilder.FUNCTION_LET;

import static org.gvsig.expressionevaluator.ExpressionBuilder.VALUE_NULL;

import org.gvsig.expressionevaluator.ExpressionEvaluatorLocator;

import org.gvsig.expressionevaluator.ExpressionUtils;

import org.gvsig.expressionevaluator.GeometryExpressionBuilder;

import org.gvsig.expressionevaluator.SymbolTable;

import org.gvsig.fmap.dal.SQLBuilder;

import org.gvsig.fmap.dal.SQLBuilder.Column;

import org.gvsig.fmap.dal.SQLBuilder.OrderByBuilder;

import org.gvsig.fmap.dal.SQLBuilder.SelectBuilder;

import org.gvsig.fmap.dal.exception.DataException;

import org.gvsig.fmap.dal.expressionevaluator.FeatureAttributeEmulatorExpression;

import org.gvsig.fmap.dal.feature.EditableFeatureAttributeDescriptor;

import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor;

import org.gvsig.fmap.dal.feature.FeatureAttributeEmulator;

import org.gvsig.fmap.dal.feature.FeatureQuery;

import org.gvsig.fmap.dal.feature.FeatureQueryOrder;

import org.gvsig.fmap.dal.feature.FeatureQueryOrder.FeatureQueryOrderMember;

import org.gvsig.fmap.dal.feature.FeatureType;

import org.gvsig.fmap.dal.store.jdbc2.JDBCConnection;

import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper;

import static org.gvsig.fmap.dal.store.jdbc2.JDBCServerExplorer.QUERY_DONT_ADD_PRIMARY_KEY_IN_THE_ORDER;

import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference;

import org.gvsig.fmap.dal.store.jdbc2.ResulSetControler.ResultSetEntry;

import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase;

import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_FEATURE_TYPE;

import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_JDBCHELPER;

import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_QUERY;

import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_SYMBOLTABLE;

import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_TABLE;

import org.gvsig.fmap.geom.DataTypes;

import org.gvsig.tools.dataTypes.DataTypeUtils;

import org.gvsig.tools.dynobject.DynField;

import org.gvsig.tools.evaluator.Evaluator;

import org.gvsig.tools.util.ChainedIterable;

import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCHelperBase.ADD_TABLE_NAME_TO_COLUMNS;

public class ResultSetForSetProviderOperation extends AbstractConnectionOperation {

    private final TableReference table;

    private final String baseFilter;

    private final String baseOrder;

    private final FeatureType storeType;

    private final FeatureType setType;

    private final FeatureQuery query;

    private final long limit;

    private final long offset;

    private final int fetchSize;

    public ResultSetForSetProviderOperation(

            JDBCHelper helper,

            TableReference table,

            String baseFilter,

            String baseOrder,

            FeatureQuery query,

            FeatureType storeType,

            FeatureType setType,

            long limit,

            long offset,

            int fetchSize

    ) {

        super(helper);

        this.table = table;

        this.baseFilter = baseFilter;

        this.baseOrder = baseOrder;

        this.storeType = storeType;

        this.setType = setType;

        this.query = query;

        this.limit = limit;

        this.offset = offset;

        this.fetchSize = fetchSize;

    }

    @Override

    protected Object perform_operation() throws Exception {

        ResultSetEntry rs = createResultSet();

        return rs;

    }

    @Override

    public Object perform(JDBCConnection conn) throws DataException {

        throw new UnsupportedOperationException("Not supported yet.");

    }

    public String getSQL() {

        List<FeatureAttributeDescriptor> columns = new ArrayList<>();

        JDBCSQLBuilderBase sqlbuilder = createSQLBuilder();

        String sql = this.getSQL(sqlbuilder, columns, null);

        return sql;

    }

    public String getSQL(

            JDBCSQLBuilderBase sqlbuilder,

            List<FeatureAttributeDescriptor> columns,

            List<String> extraColumnNames

    ) {

        double tolerance = -1; //query.getScale(); 

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        expbuilder.setProperty(PROP_FEATURE_TYPE, this.storeType);

        expbuilder.setProperty(PROP_TABLE, table);

        expbuilder.setProperty(PROP_SYMBOLTABLE, this.query==null? null:this.query.getSymbolTable());

        expbuilder.setProperty(PROP_JDBCHELPER, this.helper);

        expbuilder.setProperty(PROP_QUERY, this.query);

        SelectBuilder select = sqlbuilder.select();

        select.from().table()

                .database(this.table.getDatabase())

                .schema(this.table.getSchema())

                .name(this.table.getTable());

        select.from().subquery(this.table.getSubquery());

        List<ExpressionBuilder.Value> valuesToRemoveFeatureType = new ArrayList<>();

        if( shouldUseACustomSelect() ) {

            return processCustomSelect(select, expbuilder, sqlbuilder, valuesToRemoveFeatureType, columns);

        }

        Map<String, EditableFeatureAttributeDescriptor> allExtraColumns = getAllExtraColumns(this.setType, this.query);

        List<String> primaryKeys = new ArrayList<>();

        for (FeatureAttributeDescriptor attr : storeType.getPrimaryKey()) {

            primaryKeys.add(attr.getName());

        }

        //Don't change order of this processes

        process1_SimpleFields(helper, setType, query, sqlbuilder, select, columns, primaryKeys, extraColumnNames);

        process2_ComputedFields(helper, setType, query, sqlbuilder, select, extraColumnNames);

        process3_Where(helper, storeType, query, sqlbuilder, select);

        process4_Aggregates(this.table, this.setType, this.query, allExtraColumns, sqlbuilder, select, extraColumnNames);

        process5_GroupBys(this.table, this.setType, this.query, allExtraColumns, sqlbuilder, select, extraColumnNames);

        process6_OrderBy(storeType, setType, query, allExtraColumns, sqlbuilder, select, primaryKeys, extraColumnNames, valuesToRemoveFeatureType);

        if (!StringUtils.isEmpty(baseFilter)) {

            select.where().and(expbuilder.toValue(baseFilter));

        }

        if (!StringUtils.isEmpty(baseOrder)) {

            select.order_by().custom(baseOrder);

        }

        if (limit > 0) {

            select.limit(limit);

        } else {

            select.limit(query == null ? null : query.getLimit());

        }

        if (offset > 0) {

            select.offset(offset);

        }

        sqlbuilder.setProperties(

                null,

                PROP_FEATURE_TYPE, this.storeType,

                PROP_TABLE, table,

                PROP_SYMBOLTABLE, this.query==null? null:this.query.getSymbolTable(),

                PROP_JDBCHELPER, this.helper,

                PROP_QUERY, this.query

        );

        for (ExpressionBuilder.Value value : valuesToRemoveFeatureType) {

            value.setProperty(PROP_FEATURE_TYPE, null);

        }

        this.helper.expandCalculedColumns(sqlbuilder);

        this.helper.processSpecialFunctions(sqlbuilder, storeType, extraColumnNames);

        String sql = StringUtils.trim(sqlbuilder.toString());

        LOGGER.debug(sql);

        return sql;

    }

    private String processCustomSelect(SelectBuilder select, ExpressionBuilder expbuilder, JDBCSQLBuilderBase sqlbuilder, List<ExpressionBuilder.Value> valuesToRemoveFeatureType, List<FeatureAttributeDescriptor> columns) {

        String sql = table.getSubquery();

        if( StringUtils.containsAny(sql, "${where_filter}", "${and_filter}") ) {

            Evaluator filter = query.getFilter();

            if (filter != null) {

                String sqlfilter = filter.getSQL();

                if (!StringUtils.isEmpty(sqlfilter)) {

                    if (this.helper.supportFilter(this.storeType, filter)) {

                        select.where().set(expbuilder.toValue(sqlfilter));

                    }

                }

            }

            if (!StringUtils.isEmpty(baseFilter)) {

                select.where().and(expbuilder.toValue(baseFilter));

            }

        }

        if( StringUtils.containsAny(sql, "${order_by_orderspec}", "${comma_orderspec}") ) {

            FeatureQueryOrder order = query.getOrder();

            if (order != null) {

                for (FeatureQueryOrderMember member : order.members()) {

                    String attrName = member.getAttributeName();

                    ExpressionBuilder.Variable col = expbuilder.column(attrName);

                    select.order_by().value(col).ascending(member.getAscending());

                }

            }

        }

        if( select.has_where() || select.has_order_by() ) {

            sqlbuilder.setProperties(

                    null,

                    PROP_FEATURE_TYPE, this.storeType,

                    PROP_TABLE, table,

                    PROP_SYMBOLTABLE, this.query==null? null:this.query.getSymbolTable(),

                    PROP_JDBCHELPER, this.helper,

                    PROP_QUERY, this.query

            );

            for (ExpressionBuilder.Value value : valuesToRemoveFeatureType) {

                value.setProperty(PROP_FEATURE_TYPE, null);

            }

            if( select.has_where()) {

                String s = select.where().toString();

                sql = StringUtils.replace(sql, "${where_filter}", "WHERE " + s);

                sql = StringUtils.replace(sql, "${and_filter}", "AND (" + s + ")");

            }

            if( select.has_order_by() ) {

                String s = select.order_by().toString();

                sql = StringUtils.replace(sql, "${order_by_orderspec}", "ORDER BY " + s);

                sql = StringUtils.replace(sql, "${comma_orderspec}", ", "+ s);

            }

        }

        for (FeatureAttributeDescriptor attr : storeType) {

            columns.add(attr);

        }

        return sql;

    }

    public static void process1_SimpleFields(

            JDBCHelper helper, 

            FeatureType setType, 

            FeatureQuery query, 

            SQLBuilder sqlbuilder, 

            SelectBuilder select, 

            List<FeatureAttributeDescriptor> columns,

            List<String> primaryKeys,

            List<String> extraColumnNames

        ){

        if(!select.from().table().has_name()){

            throw new IllegalStateException("Need table in FROM.");

        }

        GeometryExpressionBuilder expbuilder = sqlbuilder.expression();

        List<String> forcedColumns = new ArrayList<>(primaryKeys);

        String[] constantsAttributeNames = null;

        if (query != null && query.hasConstantsAttributeNames()) {

            constantsAttributeNames = query.getConstantsAttributeNames();

        }

        for (FeatureAttributeDescriptor attr : setType) {

            if (attr.isComputed()) {

                // Campos calculados se a?aden despues para asegurarnos que

                // primeros se a?aden los campos reales y al final los calculados

                continue;

            }

            if (ArrayUtils.contains(constantsAttributeNames, attr.getName())) {

                continue;

            }

            if (attr.isPrimaryKey()) {

                forcedColumns.remove(attr.getName());

            }

            if (attr.getType() == DataTypes.GEOMETRY) {

                select.column().name(attr.getName()).as_geometry();

            } else {

                select.column().name(attr.getName());

            }

            columns.add(attr);

        }

        if (query == null || !query.hasGroupByColumns()) {

            for (String attrName : forcedColumns) {

                select.column().name(attrName);

                FeatureAttributeDescriptor attr = setType.getAttributeDescriptor(attrName);

                // Ojo, que cuando estamos pintando una vista NO TENEMOS LA PK y attr vale null!!!!

                // No se si deberiamos forzar a que cuando se crea un subfeaturetype se meta siempre

                // la pk, o simplemente nunca asumir que la vamos a tener.

                if (attr != null) {

                    columns.add(attr);

                }

            }

        }

    }

    public static void process2_ComputedFields(JDBCHelper helper, FeatureType setType, FeatureQuery query, SQLBuilder sqlbuilder, SelectBuilder select, List<String> extraColumnNames){

        if(!select.from().table().has_name()){

            throw new IllegalStateException("Need table in FROM.");

        }

//        En las columnas del select, si hay funciones de agregado en el select o un GROUP BY

//        los campos calculados que no lleven funci?n de agregado no deben incluir el nombre de tabla

        for (FeatureAttributeDescriptor attr : attributeDescriptorsIterable(setType, query) ) {

            if (attr.isComputed()) {

                ExpressionBuilder.Value value = createValueFromAttribute(sqlbuilder, attr);

                if( value != VALUE_NULL ) {

                    select.column()

                            .name(attr.getName())

                            .value(value)

                            .as(attr.getName());

                    if (extraColumnNames != null && !extraColumnNames.contains(attr.getName())) {

                        extraColumnNames.add(attr.getName());

                    }

//                    if(query != null && (query.hasGroupByColumns() || query.hasAggregateFunctions())){

                        addTableNameToColumns(sqlbuilder, value, true);

//                    }

                }

            }

        }

    }

    public static void process4_Aggregates(TableReference table, FeatureType setType, FeatureQuery query, Map<String, EditableFeatureAttributeDescriptor> allExtraColumns, SQLBuilder sqlbuilder, SelectBuilder select, List<String> extraColumnNames) throws RuntimeException {

        if(query == null){

            return;

        }

        if(!select.from().table().has_name()){

            throw new IllegalStateException("Need table in FROM.");

        }

        GeometryExpressionBuilder expbuilder = sqlbuilder.expression();

        if (query.hasAggregateFunctions() ) {

            // Ponemos NULL AS... en las columnas que no tengan funcion de argregado y no esten en el group by.

            for (FeatureAttributeDescriptor attr : attributeDescriptorsIterable(setType,query) )  {

                final String name = attr.getName();

                if( select.has_column(name) ) {

//                    if( attr.isComputed() ) {

//                        if (!query.isAggregate(name) ) {

//                            select.column(name).value(VALUE_NULL).as(name);

//                        }

//                    } else {

                        if ( !query.isAGroupByColumn(name) && !query.isAggregate(name) ) {

                            select.column(name).value(VALUE_NULL).as(name);

                        }

//                    }

                }

            }

            for (Map.Entry<String, String> entry : query.getAggregateFunctions().entrySet()) {

                String aggregateFunction = entry.getValue();

                String aggregateExpression = entry.getKey();

                FeatureAttributeDescriptor attr = getAttributeDescriptor(aggregateExpression, setType, allExtraColumns);

                if (attr == null) {

                    //Aggregate_expression AS name -> name:=aggregate_expression

                    Expression exp = ExpressionUtils.createExpression(aggregateExpression);

                    Code code = exp.getCode();

                    if (!(code instanceof Code.Callable)) {

                        throw new RuntimeException("Not able to use aggregate function with this expression(1): " + aggregateExpression);

                    }

                    Code.Callable callable = (Code.Callable) code;

                    if (!callable.name().equalsIgnoreCase(FUNCTION_LET)) {

                        throw new RuntimeException("Not able to use aggregate function with this expression(2): " + aggregateExpression);

                    }

                    String name = ((Code.Identifier) callable.parameters().get(0)).name();

                    ExpressionBuilder.Value value = callable.parameters().get(1).toValue();

                    ExpressionBuilder.Function aggregateExp = expbuilder.function(aggregateFunction, value);                    

                    select.column(name).value(aggregateExp).as(name);

                } else {

                    SQLBuilder.SelectColumnBuilder column = select.column(attr.getName());

                    ExpressionBuilder.Value value = createValueFromAttribute(sqlbuilder, attr);

                    ExpressionBuilder.Function aggregateExp = expbuilder.function(aggregateFunction, value);

                    addTableNameToColumns(sqlbuilder, aggregateExp, true);

                    column.value(aggregateExp).as(attr.getName());

                }

            }

        }

    }

    public static void process5_GroupBys(TableReference table, FeatureType setType, FeatureQuery query, Map<String, EditableFeatureAttributeDescriptor> allExtraColumns, SQLBuilder sqlbuilder, SelectBuilder select, List<String> extraColumnNames) throws RuntimeException {

        if(query == null){

            return;

        }

        if(!select.from().table().has_name()){

            throw new IllegalStateException("Need table in FROM.");

        }

        if (query.hasGroupByColumns()) { // if tiene agrupacion

            GeometryExpressionBuilder expbuilder = sqlbuilder.expression();

            for (FeatureAttributeDescriptor attr : attributeDescriptorsIterable(setType,query.getExtraColumns()) ) {

                final String name = attr.getName();

                if( select.has_column(name) ) {

//                    if( attr.isComputed() ) {

//                        if (!query.isAggregate(name) ) {

//                            select.column(name).value(VALUE_NULL).as(name);

//                        }

//                    } else {

                        if ( !query.isAGroupByColumn(name) && !query.isAggregate(name) ) {

                            select.column(name).value(VALUE_NULL).as(name);

                        }

//                    }

                }

            }

            for (String attrName : query.getGroupByColumns()) {

                FeatureAttributeDescriptor attr = getAttributeDescriptor(attrName,setType, allExtraColumns);

                if ( attr == null ) {

                    try {

                        Code code = ExpressionUtils.compile(attrName);

                        select.group_by(code.toValue());

                    } catch (Exception ex) {

                        throw new RuntimeException("Not able to create column by expression in groupby query", ex);

                    }

                } else {

                    ExpressionBuilder.Value value = createValueFromAttribute(sqlbuilder, attr);

                    if( value instanceof Column ) {

                        ((Column)value).table(select.from().table());

                        if( select.getColumns().isEmpty() ) {

                            // Tratamos de asegurarnos de que al menos hay una columna en el select

                            select.column(attr.getName());

                        }

                    }

                    addTableNameToColumns(sqlbuilder, value, true);

                    select.group_by(value);

                }

            }

        }

    }

    public static void process3_Where(

            JDBCHelper helper, 

            FeatureType storeType, 

            FeatureQuery query, 

            SQLBuilder sqlbuilder,

            SelectBuilder select

        ) {

        if(query == null){

            return;

        }

        if(!select.from().table().has_name()){

            throw new IllegalStateException("Need table in FROM.");

        }

        GeometryExpressionBuilder expbuilder = sqlbuilder.expression();

        Evaluator filter = query.getFilter();

        if (filter != null) {

            String sqlfilter = filter.getSQL();

            if (!StringUtils.isEmpty(sqlfilter)) {

                if (helper.supportFilter(storeType, filter)) {

                    ExpressionBuilder.Value value = expbuilder.toValue(sqlfilter);

                    addTableNameToColumns(sqlbuilder, value, true);

                    select.where().set(value);

                }

            }

        }

    }

    public static void process6_OrderBy(

            FeatureType storeType, 

            FeatureType setType, 

            FeatureQuery query, 

            Map<String, EditableFeatureAttributeDescriptor> allExtraColumns, 

            SQLBuilder sqlbuilder,

            SelectBuilder select, 

            List<String> primaryKeys,

            List<String> extraColumnNames,

            List<ExpressionBuilder.Value> valuesToRemoveFeatureType

        ) {

        //

        // https://learnsql.com/cookbook/how-to-order-rows-by-group-sum-in-sql/

        //

        if(!select.from().table().has_name()){

            throw new IllegalStateException("Need table in FROM.");

        }

        GeometryExpressionBuilder expbuilder = sqlbuilder.expression();

        if (query != null) {

            FeatureQueryOrder order = query.getOrder();

            if (order != null) {

                if (query.hasAggregateFunctions() || query.hasGroupByColumns()) {

                    for (FeatureQueryOrderMember member : order.members()) {

                        String attrName = member.getAttributeName();

                        FeatureAttributeDescriptor attr = getAttributeDescriptor(attrName, setType, allExtraColumns);                        

                        if( attr == null ) {

                            // Order-by una expression en el query

                            String sqlorder = member.getEvaluator().getSQL();

                            select.order_by()

                                    .value(expbuilder.toValue(sqlorder))

                                    .ascending(member.getAscending());

                        } else {

                            ExpressionBuilder.Value value;

                            if ( query.isAGroupByColumn(attrName) ) {

                                value = createValueFromAttribute(sqlbuilder, attr);

                                if( value instanceof Column ) {

                                    ((Column)value).table(select.from().table());

                                }

                            } else if ( query.isAggregate(attrName) ) {

                                String fn = query.getAggregateFunctions().get(attr.getName());

                                value = createValueFromAttribute(sqlbuilder, attr);

                                if( value instanceof Column ) {

                                    ((Column)value).table(select.from().table());

                                }

                                value = expbuilder.function(fn, value);

                            } else {

                                continue;

//                                throw new RuntimeException("Can't order by column '" + attrName + "', it does not have an aggregate function nor is it grouping by it.");

                            }

                            int nullsMode = OrderByBuilder.MODE_NULLS_LAST;

                            if ( !attr.allowNull() || attr.isPrimaryKey() ) {

                                // Puede ser costoso ordenar los nulos al principio o final, asi que

                                // si el campo no admite nulos pasamos de indicarselo.

                                nullsMode = OrderByBuilder.MODE_NULLS_NOT_SPECIFIED;

                            }

                            boolean expand;

                            if ( !query.isAggregate(attrName) ) {

                                if (select.has_aggregate_functions()) {

                                    expand = true;

                                } else {

                                    expand = false;

                                }

                            } else {

                                expand = false;

                            }

                            if (select.isGroupBy(attrName)) {

                                expand = false;

                            }

                            expandColumns(value, expand, valuesToRemoveFeatureType);

                            addTableNameToColumns(sqlbuilder, value, !attr.isComputed());                                

                            select.order_by().value(value).ascending(member.getAscending()).nulls(nullsMode);

                        }

                    }

                } else {

                    for (FeatureQueryOrderMember member : order.members()) {

                        String attrName = member.getAttributeName();

                        FeatureAttributeDescriptor attr = getAttributeDescriptor(attrName, setType, allExtraColumns);                        

                        if( attr == null ) {

                            // Order-by una expression en el query

                            String sqlorder = member.getEvaluator().getSQL();

                            select.order_by()

                                    .value(expbuilder.toValue(sqlorder))

                                    .ascending(member.getAscending());

                        } else {

                            Column column = sqlbuilder.column(attr.getName());

                            if( !attr.isComputed() ) {

                                column.table(select.from().table());

                            }

                            int nullsMode = OrderByBuilder.MODE_NULLS_LAST;

                            if ( !attr.allowNull() || attr.isPrimaryKey() ) {

                                // Puede ser costoso ordenar los nulos al principio o final, asi que

                                // si el campo no admite nulos pasamos de indicarselo.

                                nullsMode = OrderByBuilder.MODE_NULLS_NOT_SPECIFIED;

                            }

                            expandColumns(column, false, valuesToRemoveFeatureType);

                            addTableNameToColumns(sqlbuilder, column, !attr.isComputed());                                

                            select.order_by().value(column).ascending(member.getAscending()).nulls(nullsMode);

                        }

                    }

                }

            }

        }

        if( query!=null && !query.hasOrder() ) {

            // Si no hay orden y hay grupos ordenaremos por estos

            if( query.hasGroupByColumns() ) { // && isPaginated()) {

                // Cuando paginamos debemos ordenar por las columnas del groupby.

                // Ordenamos siempre para obtener el mismo resultado cuando paginamos

                // y no paginamos.

                for (String attrName : query.getGroupByColumns()) {

                    FeatureAttributeDescriptor attr = getAttributeDescriptor(attrName,setType, allExtraColumns);

                    if ( attr != null ) {

                        if( query.getOrder().get(attrName)==null ) {

                            ExpressionBuilder.Value value;

                            value = createValueFromAttribute(sqlbuilder, attr);

                            if( value instanceof Column ) {

                                ((Column)value).table(select.from().table());

                            }

                            int nullsMode = OrderByBuilder.MODE_NULLS_LAST;

                            if ( !attr.allowNull() || attr.isPrimaryKey() ) {

                                // Puede ser costoso ordenar los nulos al principio o final, asi que

                                // si el campo no admite nulos pasamos de indicarselo.

                                nullsMode = OrderByBuilder.MODE_NULLS_NOT_SPECIFIED;

                            }

                            expandColumns(value, true, valuesToRemoveFeatureType);

                            addTableNameToColumns(sqlbuilder, value, true);                                

                            select.order_by().value(value).ascending(true).nulls(nullsMode);

                        }

                    }

                }

            }

        }

        if (primaryKeys.isEmpty()) {

            // Muy probablemente si no tiene pk sea una vista, asi que 

            // pasaremos de ordenar y esperemos que la vista este ya ordenada.

            select.disable_check_order_and_offset();

        } else {

            if (query == null || !(query.hasAggregateFunctions() || query.hasGroupByColumns())) {

                // Siempre ordenamos por la clave primaria

                Object x = query == null ? null : query.getQueryParameter(QUERY_DONT_ADD_PRIMARY_KEY_IN_THE_ORDER);

                if (x == null || !DataTypeUtils.toBoolean(x)) {

                    for (String attrName : primaryKeys) {

                        if (select.getOrderBy(attrName) == null) {

                            ExpressionBuilder.Variable col = sqlbuilder.column(select.from().table(),attrName);

                            select.order_by()

                                    .value(col) //column(attrName)

                                    .ascending()

                                    .nulls(OrderByBuilder.MODE_NULLS_NOT_SPECIFIED)

                                    ;

                            // No tengo claro que pasa si se esta agrupando, y no se ha

                            // incluido en el agrupamiento al PK. En ese caso el select

                            // que se genera tendria una pinta tal que asi:

                            //   SELECT NULL as pk, ... ORDER BY pk ASC

                            // Probablemente en ese caso no tendriamos que meter la PK

                            // en el order-by ya que creo que no cambiaria el resultado.

                        }

                    }

                }

            }

        }

    }

    private boolean isPaginated() {

        // No tengo claro que (offset==0 && limit>0) sea lo mas correcto,

        // Pero cuando se va a paginar y se pide la primera pagina offset es

        // 0 y limit>0, y si no ordenamos ya esa primera pagina los resultados

        // que se obtienen no son correctos, ya que la primera pagina se saca

        // sin ordenar y el resto ordenadas.

        // Probablemente deberiamos tener alguna otra forma de detectar que

        // estamos paginanado ya que asi no distinguimo si solo queremos 

        // obtener los primeros elementos sin importarnos su orden.

        return (offset > 0 || (offset == 0 && limit > 0));

    }

    public ResultSetEntry createResultSet() throws DataException {

        List<FeatureAttributeDescriptor> columns = new ArrayList<>();

        List<String> extraColumnNames = new ArrayList<>();

        JDBCSQLBuilderBase sqlbuilder = createSQLBuilder();

        String sql = this.getSQL(sqlbuilder, columns, extraColumnNames);

        ResultSetEntry resultSetEntry = this.helper.getResulSetControler().create(

                sql, fetchSize,

                columns.toArray(new FeatureAttributeDescriptor[columns.size()]),

                extraColumnNames.toArray(new String[extraColumnNames.size()])

        );

        return resultSetEntry;

    }

    private boolean shouldUseACustomSelect() {

        if( !table.hasSubquery() ) {

            return false;

        }

        if( this.query == null ) {

            return false;

        }

        if( this.query.isUseSubquery() ) {

            return false;

        }

        if( this.query.hasGroupByColumns() ) {

            return false;

        }

        if( this.query.hasAggregateFunctions() ) {

            return false;

        }        

        if( !this.query.hasFilter() && !this.query.hasOrder() ) {

            return true;

        }

//        si el filtro es incompatible con la BBDD

//            return false

//        si el orden es incompatible con la BBDD

//            return false

//        si filtro u orden usan la funcion foreing_value 

//            return false;

        return true;

    }

    public static Map<String, EditableFeatureAttributeDescriptor> getAllExtraColumns(FeatureType setType, FeatureQuery query) {

        Map<String, EditableFeatureAttributeDescriptor> allExtraColumns = new HashMap<>();

        for (EditableFeatureAttributeDescriptor column : setType.getExtraColumns().getColumns()) {

            allExtraColumns.put(column.getName(), column);

        }

        if(query != null){

            for (EditableFeatureAttributeDescriptor column : query.getExtraColumn().getColumns()) {

                allExtraColumns.put(column.getName(), column);

            }

        }

        return allExtraColumns;

    }

    private static Iterable<FeatureAttributeDescriptor> attributeDescriptorsIterable(Iterable... iterables) {

        return new ChainedIterable<>(iterables);

    }

    private static Iterable<FeatureAttributeDescriptor> attributeDescriptorsIterable(FeatureType setType, FeatureQuery query) {

        if( query == null ) {

            return new ChainedIterable<>(setType);

        }

        return attributeDescriptorsIterable(setType, query.getExtraColumns());

    }

    private static boolean existsAttributeDescriptor(String attrName, FeatureType setType, Map<String, EditableFeatureAttributeDescriptor> allExtraColumns)  {

        return getAttributeDescriptor(attrName, setType, allExtraColumns)!=null;

    }

    private static FeatureAttributeDescriptor getAttributeDescriptor(String attrName, FeatureType setType, Map<String, EditableFeatureAttributeDescriptor> extraColumns) {

        FeatureAttributeDescriptor attr = null;

        if( setType!=null ) {

            attr = setType.getAttributeDescriptorFromAll(attrName);

        }

        if(attr == null && extraColumns!=null){

            attr = extraColumns.get(attrName);

        }

        return attr;

    }

    private static void expandColumns(ExpressionBuilder.Value value, boolean expand, List<ExpressionBuilder.Value> valuesToRemoveFeatureType) {

        if(!expand){

            // En el OrderBy no queremos que se sustituya el nombre del campo calculado

            // por su expresion. Se encarga el formater y lo evitamos quitandole el ftype

            // al value.

            valuesToRemoveFeatureType.add(value);

        }

    }

    private static void addTableNameToColumns(SQLBuilder sqlbuilder, ExpressionBuilder.Value expression, boolean add) {

        sqlbuilder.setProperties(expression, null, ADD_TABLE_NAME_TO_COLUMNS, add);

    }

    private static ExpressionBuilder.Value createValueFromAttribute(SQLBuilder sqlbuilder, FeatureAttributeDescriptor attr) {

        switch(attr.getRelationType()) {

            case DynField.RELATION_TYPE_AGGREGATE:

            case DynField.RELATION_TYPE_COMPOSITION:

                return VALUE_NULL;

            case DynField.RELATION_TYPE_NONE:

            case DynField.RELATION_TYPE_IDENTITY:

            case DynField.RELATION_TYPE_COLLABORATION:

                break;

        }

        if( attr.isComputed() ) {

            FeatureAttributeEmulator attrEmulator = attr.getFeatureAttributeEmulator();

            if( !(attrEmulator instanceof FeatureAttributeEmulatorExpression) ) {

                return VALUE_NULL;

            }

            FeatureAttributeEmulatorExpression x = (FeatureAttributeEmulatorExpression) attrEmulator;

            Expression exp = x.getExpression();

            if (exp == null || exp.isEmpty() ) {

                return VALUE_NULL;

            }

            Code code = exp.getCode();

            SymbolTable symbolTable = ExpressionEvaluatorLocator.getExpressionEvaluatorManager().getInmutableSymbolTable();

            code.link(symbolTable);

            ExpressionBuilder.Value value = code.toValue(sqlbuilder.expression());

            return value;

        }

        // Es un campo de la tabla

        Column column = sqlbuilder.column(attr.getName());

        return column;

    }

}