criteria -> sql translation problem with SqlDataSource

[csillag]

Turns out the problem was with the database: the collation of one field was set to utf8_bin instead of utf8_general_ci, so "true" did not match "True".

(No idea how this one field got messed up, though.)

[csillag]

The same thing happens when I send this query from a ListGrid (instead of a ResultSet), using the filter row to set the boolean true value (ie. using the checkbox).

=== 2010-05-30 05:42:29,492 [l0-2] INFO  RequestContext - URL: '/szolorg/sc/IDACall', User-Agent: 'Mozilla/5.0 (X11; U; Linux x86_64; hu-HU; rv:1.9.1.9) Gecko/20100501 Iceweasel/3.5.9 (like Firefox/3.5.9)': Moz (Gecko) with Accept-Encoding header
=== 2010-05-30 05:42:29,495 [l0-2] DEBUG XML - Parsed XML from (in memory stream): 2ms
=== 2010-05-30 05:42:29,497 [l0-2] DEBUG RPCManager - Processing 1 requests.
=== 2010-05-30 05:42:29,497 [l0-2] DEBUG RPCManager - Request #1 (DSRequest) payload: {
    criteria:{
        FLAG_CLOSED:true
    },
    operationConfig:{
        dataSource:"fundamental_data_records_auto",
        operationType:"fetch",
        textMatchStyle:"substring"
    },
    startRow:0,
    endRow:75,
    componentId:"isc_OID_299",
    appID:"builtinApplication",
    operation:"fundamental_data_records_auto_fetch",
    oldValues:{
        FLAG_CLOSED:true
    }
}
=== 2010-05-30 05:42:29,497 [l0-2] INFO  IDACall - Performing 1 operation(s)
=== 2010-05-30 05:42:29,498 [l0-2] DEBUG AppBase - [builtinApplication.fundamental_data_records_auto_fetch] No userTypes defined, allowing anyone access to all operations for this application
=== 2010-05-30 05:42:29,498 [l0-2] DEBUG AppBase - [builtinApplication.fundamental_data_records_auto_fetch] No public zero-argument method named '_fundamental_data_records_auto_fetch' found, performing generic datasource operation
=== 2010-05-30 05:42:29,498 [l0-2] INFO  SQLDataSource - [builtinApplication.fundamental_data_records_auto_fetch] Performing fetch operation with
        criteria: {FLAG_CLOSED:true}    values: {FLAG_CLOSED:true}
=== 2010-05-30 05:42:29,499 [l0-2] INFO  SQLDataSource - [builtinApplication.fundamental_data_records_auto_fetch] derived query: SELECT DATA_ID.DATA_NAME as DATA_ID_DATA_NAME, MAIN_DATA.FUNDAMENTAL_DATA_RECORD_ID as MAIN_DATA_FUNDAMENTAL_DATA_RECORD_ID, MAIN_DATA.VALUE as MAIN_DATA_VALUE, MAIN_DATA.DATE_VALUE as MAIN_DATA_DATE_VALUE, MAIN_DATA.STRING_VALUE as MAIN_DATA_STRING_VALUE, MAIN_DATA.FLAG_CLOSED as MAIN_DATA_FLAG_CLOSED, $defaultSelectClause FROM fundamental_data_records MAIN_DATA, fundamental_data_records, fundamental_data_definitions DATA_ID WHERE fundamental_data_records.DATA_ID = DATA_ID.DATA_ID AND fundamental_data_records.MAIN_DATA = MAIN_DATA.FUNDAMENTAL_DATA_RECORD_ID AND ($defaultWhereClause) ORDER BY PERIOD DESC
=== 2010-05-30 05:42:29,499 [l0-2] DEBUG SQLDataSource - [builtinApplication.fundamental_data_records_auto_fetch] Executing row count query: SELECT COUNT(*) FROM fundamental_data_records MAIN_DATA, fundamental_data_records, fundamental_data_definitions DATA_ID WHERE fundamental_data_records.DATA_ID = DATA_ID.DATA_ID AND fundamental_data_records.MAIN_DATA = MAIN_DATA.FUNDAMENTAL_DATA_RECORD_ID AND ($defaultWhereClause)
=== 2010-05-30 05:42:29,504 [l0-2] DEBUG PoolableSQLConnectionFactory - [builtinApplication.fundamental_data_records_auto_fetch] Returning pooled Connection
=== 2010-05-30 05:42:29,504 [l0-2] INFO  SQLDriver - [builtinApplication.fundamental_data_records_auto_fetch] Executing SQL query on 'SzolorgDB': SELECT COUNT(*) FROM fundamental_data_records MAIN_DATA, fundamental_data_records, fundamental_data_definitions DATA_ID WHERE fundamental_data_records.DATA_ID = DATA_ID.DATA_ID AND fundamental_data_records.MAIN_DATA = MAIN_DATA.FUNDAMENTAL_DATA_RECORD_ID AND ((fundamental_data_records.FLAG_CLOSED='true'))
=== 2010-05-30 05:42:29,506 [l0-2] INFO  DSResponse - [builtinApplication.fundamental_data_records_auto_fetch] DSResponse: List with 0 items
=== 2010-05-30 05:42:29,506 [l0-2] DEBUG RPCManager - Content type for RPC transaction: text/plain; charset=UTF-8
=== 2010-05-30 05:42:29,507 [l0-2] DEBUG RPCManager - non-DMI response, dropExtraFields: false
=== 2010-05-30 05:42:29,507 [l0-2] INFO  Compression - /szolorg/sc/IDACall: 137 -> 123 bytes

As you can see, the boolean true filter criterion gets translated to "true", unstead of "True".

[csillag]

I have a DS with a boolean field (among other things.)
When I create a ResultSet with this DS, and run a query with this criteria:

    criteria:{
        FLAG_CLOSED:true
    },

The allowing SQL query is generated:

SELECT ... WHERE ... (FLAG_CLOSED='true')

Please note, that the FLAG_CLOSED field, which is a boolean field, gets a "true" string literal. However, the database contains "True" values (uppercase), so this does not find anything.

--------------------------------

Is this intended?

(This is with SmartGWT 2.1 Power.)