FYI: "DISTINCT" realized differently in Oracle for automatic row count and data query

[Blama]

Hello Isomorphic,

I'm using your "DISTINCT" as described in the ServerSummaries-docs (v10.0p_2015-04-23).

This is my operation binding:

<operationBinding operationType="fetch" operationId="fetchAdminarea" outputs="COUNTRY_ID, ADMINAREA">
	<groupBy>COUNTRY_ID</groupBy>
	<groupBy>ADMINAREA</groupBy>
</operationBinding>

This is a request in the server log:

=== 2015-04-30 15:21:06,658 [c-10] DEBUG RPCManager - Request #1 (DSRequest) payload: {
    criteria:{
        operator:"equals",
        fieldName:"COUNTRY_ID",
        value:2541,
        _constructor:"AdvancedCriteria"
    },
    operationConfig:{
        dataSource:"MV_ZIPCODE",
        repo:null,
        operationType:"fetch",
        textMatchStyle:"startsWith"
    },
    startRow:0,
    endRow:75,
    sortBy:[
        "ADMINAREA"
    ],
    componentId:"isc_PickListMenu_2",
    appID:"builtinApplication",
    operation:"fetchAdminarea",
    oldValues:{
        operator:"equals",
        fieldName:"COUNTRY_ID",
        value:2541,
        _constructor:"AdvancedCriteria"
    }
}
=== 2015-04-30 15:21:06,659 [c-10] INFO  LMSIDACall - Performing 1 operation(s)
=== 2015-04-30 15:21:06,660 [c-10] DEBUG AppBase - [builtinApplication.fetchAdminarea] No userTypes defined, allowing anyone access to all operations for this application
=== 2015-04-30 15:21:06,661 [c-10] DEBUG AppBase - [builtinApplication.fetchAdminarea] No public zero-argument method named '_fetchAdminarea' found, performing generic datasource operation
=== 2015-04-30 15:21:06,664 [c-10] INFO  SQLDataSource - [builtinApplication.fetchAdminarea] Performing fetch operation with
	criteria: {criteria:[{value:12,fieldName:"TENANT_ID",operator:"equals"},{value:2541,fieldName:"COUNTRY_ID",operator:"equals"}],operator:"and",_constructor:"AdvancedCriteria"}	values: {criteria:[{value:12,fieldName:"TENANT_ID",operator:"equals"},{value:2541,fieldName:"COUNTRY_ID",operator:"equals"}],operator:"and",_constructor:"AdvancedCriteria"}
=== 2015-04-30 15:21:06,666 [c-10] INFO  SQLDataSource - [builtinApplication.fetchAdminarea] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause GROUP BY MV_ZIPCODE.COUNTRY_ID, MV_ZIPCODE.ADMINAREA ORDER BY $defaultOrderClause
=== 2015-04-30 15:21:06,669 [c-10] DEBUG SQLDataSource - [builtinApplication.fetchAdminarea] Executing row count query: SELECT COUNT(*) FROM (SELECT distinct $defaultGroupWithAliasClause FROM $defaultTableClause WHERE $defaultWhereClause ) work
=== 2015-04-30 15:21:06,670 [c-10] DEBUG SQLDataSource - [builtinApplication.fetchAdminarea] Eval'd row count query: SELECT COUNT(*) FROM (SELECT distinct MV_ZIPCODE.COUNTRY_ID, MV_ZIPCODE.ADMINAREA FROM MV_ZIPCODE WHERE ((MV_ZIPCODE.TENANT_ID = 12 AND MV_ZIPCODE.TENANT_ID IS NOT NULL) AND (MV_ZIPCODE.COUNTRY_ID = 2541 AND MV_ZIPCODE.COUNTRY_ID IS NOT NULL)) ) work
=== 2015-04-30 15:21:06,672 [c-10] DEBUG SQLConnectionManager - [builtinApplication.fetchAdminarea] Borrowed connection '131232799'
=== 2015-04-30 15:21:06,672 [c-10] DEBUG SQLTransaction - [builtinApplication.fetchAdminarea] Started new Oracle transaction "131232799"
=== 2015-04-30 15:21:06,672 [c-10] DEBUG SQLDriver - [builtinApplication.fetchAdminarea] About to execute SQL query in 'Oracle' using connection '131232799'
=== 2015-04-30 15:21:06,672 [c-10] INFO  SQLDriver - [builtinApplication.fetchAdminarea] Executing SQL query on 'Oracle': SELECT COUNT(*) FROM (SELECT distinct MV_ZIPCODE.COUNTRY_ID, MV_ZIPCODE.ADMINAREA FROM MV_ZIPCODE WHERE ((MV_ZIPCODE.TENANT_ID = 12 AND MV_ZIPCODE.TENANT_ID IS NOT NULL) AND (MV_ZIPCODE.COUNTRY_ID = 2541 AND MV_ZIPCODE.COUNTRY_ID IS NOT NULL)) ) work
=== 2015-04-30 15:21:06,689 [c-10] DEBUG SQLDataSource - [builtinApplication.fetchAdminarea] JDBC driver windowed select rows 0->75, result size 75. Query: SELECT MV_ZIPCODE.COUNTRY_ID, MV_ZIPCODE.ADMINAREA FROM MV_ZIPCODE WHERE ((MV_ZIPCODE.TENANT_ID = 12 AND MV_ZIPCODE.TENANT_ID IS NOT NULL) AND (MV_ZIPCODE.COUNTRY_ID = 2541 AND MV_ZIPCODE.COUNTRY_ID IS NOT NULL)) GROUP BY MV_ZIPCODE.COUNTRY_ID, MV_ZIPCODE.ADMINAREA ORDER BY MV_ZIPCODE.ADMINAREA
=== 2015-04-30 15:21:06,716 [c-10] DEBUG SQLDataSource - [builtinApplication.fetchAdminarea] Using paging strategy 'jdbcScroll' - scrolling to absolute position 1
=== 2015-04-30 15:21:06,717 [c-10] DEBUG SQLDataSource - [builtinApplication.fetchAdminarea] Scrolling / positioning took 0ms
=== 2015-04-30 15:21:06,719 [c-10] INFO  DSResponse - [builtinApplication.fetchAdminarea] DSResponse: List with 17 items
=== 2015-04-30 15:21:06,719 [c-10] DEBUG RPCManager - Content type for RPC transaction: text/plain; charset=UTF-8
=== 2015-04-30 15:21:06,720 [c-10] DEBUG SQLTransaction - Committing Oracle transaction "131232799"
=== 2015-04-30 15:21:06,720 [c-10] DEBUG RPCManager - non-DMI response, dropExtraFields: false
=== 2015-04-30 15:21:06,722 [c-10] DEBUG SQLTransaction - getConnection() found transactional connection for Oracle with hashcode "131232799"
=== 2015-04-30 15:21:06,722 [c-10] DEBUG SQLTransaction - Ending Oracle transaction "131232799"
=== 2015-04-30 15:21:06,722 [c-10] DEBUG SQLConnectionManager - About to close connection with hashcode "131232799"
=== 2015-04-30 15:21:06,724 [c-10] INFO  Compression - /lms/lms/sc/IDACall: 889 -> 336 bytes

As you can see in the logs the COUNT(*) query and the data query use a different approach to distinct the values (DISTINCT vs GROUP-BY-ALL). Both yield the correct results, but I don't suppose you intended them to be different. So this is a FYI-post and not an issue report.

Best regards
Blama

[Isomorphic]

Two different types of queries are used because, for one of them, more advanced options are possible which would make it impossible to use the simpler query format. Doesn't appear to be a bug, but let us know if you uncover an actual issue with the results of these queries.