Application: gvSIG desktop

-- Group and Subselect

SELECT 

    MIN("ID") AS "ID", MAX("Byte") AS "Byte", NULL AS "Bool1", "Long", 

    NULL AS "Timestamp", NULL AS "Date", NULL AS "Time", NULL AS "Bool2", 

    NULL AS "String", NULL AS "Bool3", SUM("Double") AS "Double", 

    NULL AS "Bool4", NULL AS "Float", NULL AS "Bool5", NULL AS "Decimal", 

    NULL AS "Geometry" 

  FROM "PUBLIC"."test" 

  WHERE NVL2(COALESCE((

    SELECT "ISO_A2" 

    FROM "countries" 

    WHERE (("TEST"."STRING" = "countries"."CONTINENT") AND 

      ("countries"."LASTCENSUS" < 0)) LIMIT 1)),TRUE,FALSE) 

  GROUP BY "Long";

  public void testGroupAndSubselect() throws Exception {

    JDBCHelper helper = TestUtils.getJDBCHelper();

    JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder();

    OperationsFactory operations = helper.getOperations();

    List<String> expectedSQLs = TestUtils.getSQLs("resultSetForSetProvider.sql");

    FeatureStore sourceStore = TestUtils.openSourceStore1();

    TableReference table = operations.createTableReference(

            "dbtest", 

            sqlbuilder.default_schema(), 

            "test", 

            null

    );

    StringBuilder filter = new StringBuilder();

    filter.append("EXISTS(");

    filter.append(" SELECT \"ISO_A2\" FROM countries");

    filter.append("   WHERE ");

    filter.append("     TEST.STRING = countries.CONTINENT AND ");

    filter.append("     countries.LASTCENSUS < 0 ");

    filter.append("   LIMIT 1;, ");

    filter.append(" 'EXISTS62a964cd7bc24f409b97c03b9170408d' ");

    filter.append(")");

    FeatureType featureType = sourceStore.getDefaultFeatureType();

    FeatureQuery query = sourceStore.createFeatureQuery();

    query.getGroupByColumns().add("Long");

    query.getAggregateFunctions().put("ID", "MIN");

    query.getAggregateFunctions().put("Byte", "MAX");

    query.getAggregateFunctions().put("Double", "SUM");

    query.addFilter(filter.toString());

    ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider(

            table,

            null,

            null,

            query,

            featureType,

            featureType,

            0,

            0, 

            0

    );

    String sql = resultSetForSetProvider.getSQL();

    System.out.println("###### SQL:"+sql);

    System.out.println("###### EXP:"+expectedSQLs.get(3));

    assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get(3), sql);

  }

    if (where == null || where.isEmpty() || select.has_group_by() ) {