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

Hello Isomorphic,

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

This is my operation binding:

Code:

<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:

Code:

=== 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

Announcement