SQL Query problems with Multiple select item from datasource

[azuniga]

Using gwt 2.3 and smartclient 2.5 nightly. I am having problems with the query that is generated by smartclient when a listgrid is set up to use a multiple select item for one of it's listgrid fields. Here is the data source file:

<DataSource ID="ADataSource" dataFormat="iscServer" serverType="sql" 
dataSourceVersion="1" dbName="Oracle" tableName="P">

    <fields>
        <field name="P_TYPE_NAME" type="text" length="30" title="P Type Name">
            <nativeName>Name</nativeName>
            <tableName>P_TYPE</tableName>
        </field>
        <field name="S_TIMESTAMP" type="datetime" title="S Time"/>
        <field name="P_T_ID" type="float"/>
        <field name="P_ID" type="float" title="P ID" primaryKey="true"/>
        <field name="Lat" type="float" title="Lat"/>
        <field name="Mode" type="text" length="20" title="Mode"/>
        <field name="VALUE" type="float">
            <table>Value</table>
            <tableName>P_A</tableName>
        </field>
        <field name="C_type" type="text" length="10" title="C type">
            <tableName>P_TYPE</tableName>
        </field>
        <field name="NAME" type="text" length="40" title="A">
            <tableName>P_A_TYPE</tableName>
        </field>
        <field name="B_number" type="float" title="B">          
            <tableName>I_B</tableName>
        </field>
        <field name="I_N" type="text" length="10" title="I">       
            <tableName>I_B</tableName>
        </field>
    </fields>
    <operationBindings>
        <OperationBinding>
            <operationType>fetch</operationType>
            
            <tableClause>P, P_A_TYPE, 
    	P_A, P_TYPE, I_B</tableClause>
            <whereClause>P.P_ID = P_A.P_ID AND
    	P_A.P_A_TYPE_ID = 
    	P_A_TYPE.P_A_TYPE_ID AND
    	P_TYPE.P_T_ID = P.P_T_ID AND
    	P_TYPE.I_B_ID = I_B.I_B_ID AND
    	(P_A.VALUE > P_A_TYPE.MAX_THRESHOLD OR 
    	P_A.VALUE &lt; P_A_TYPE.MAX_THRESHOLD) AND
    	($defaultWhereClause)
    	</whereClause>
        </OperationBinding>
    </operationBindings>
    <generatedBy>SC_SNAPSHOT-2011-09-15/PowerEdition Deployment 2011-09-15</generatedBy>
</DataSource>

Here is the console output:

=== 2011-10-10 16:03:02,733 [0-14] INFO  RequestContext - URL: '/myapplication/sc/IDACall', User-Agent: 'Mozilla/5.0 (X11; U; Linux i686 (x86_64); en-US; rv:1.9.2.20) Gecko/20110803 Firefox/3.6.20': Moz (Gecko) with Accept-Encoding header
=== 2011-10-10 16:03:02,846 [0-14] DEBUG XML - Parsed XML from (in memory stream): 111ms
=== 2011-10-10 16:03:02,865 [0-14] DEBUG RPCManager - Processing 1 requests.
=== 2011-10-10 16:03:02,870 [0-14] DEBUG RPCManager - Request #1 (DSRequest) payload: {
    criteria:{
        operator:"and",
        _constructor:"AdvancedCriteria",
        criteria:[
            {
                fieldName:"P_T_NAME",
                operator:"equals",
                value:[
                    "Apples",
                    "Pears",
                    "Oranges"
                ],
                _constructor:"AdvancedCriteria"
            }
        ]
    },
    operationConfig:{
        dataSource:"ADataSource",
        operationType:"fetch",
        textMatchStyle:"substring"
    },
    startRow:0,
    endRow:75,
    componentId:"isc_ListGrid_0",
    appID:"builtinApplication",
    operation:"ADataSource_fetch",
    oldValues:{
        operator:"and",
        _constructor:"AdvancedCriteria",
        criteria:[
            {
                fieldName:"P_T_NAME",
                operator:"equals",
                value:[
                    "Apples",
                    "Pears",
                    "Oranges"
                ],
                _constructor:"AdvancedCriteria"
            }
        ]
    }
}
=== 2011-10-10 16:03:02,871 [0-14] INFO  IDACall - Performing 1 operation(s)
=== 2011-10-10 16:03:02,871 [0-14] DEBUG AppBase - [builtinApplication.ADataSource_fetch] No userTypes defined, allowing anyone access to all operations for this application
=== 2011-10-10 16:03:02,872 [0-14] DEBUG AppBase - [builtinApplication.ADataSource_fetch] No public zero-argument method named '_ADataSource_fetch' found, performing generic datasource operation
=== 2011-10-10 16:03:02,872 [0-14] INFO  SQLDataSource - [builtinApplication.ADataSource_fetch] Performing fetch operation with
	criteria: {operator:"and",_constructor:"AdvancedCriteria",criteria:[{fieldName:"P_T_NAME",operator:"equals",value:["apples ","pears","oranges"],_constructor:"AdvancedCriteria"}]}	values: {operator:"and",_constructor:"AdvancedCriteria",criteria:[{fieldName:"P_T_NAME",operator:"equals",value:["apples ","pears","oranges"],_constructor:"AdvancedCriteria"}]}
=== 2011-10-10 16:03:02,873 [0-14] INFO  SQLDataSource - [builtinApplication.ADataSource_fetch] derived query: SELECT $defaultSelectClause FROM P, P_A_TYPE, 
    	P_A, P_T, I_B WHERE P.P_ID = P_A.P_ID AND
    	P_A.P_A_TYPE_ID = 
    	P_A_TYPE.P_A_TYPE_ID AND
    	P_T.P_T_ID = P.P_T_ID AND
    	P_T.I_B_ID = I_B.I_B_ID AND
    	(P_A.VALUE > P_A_TYPE.MAX_THRESHOLD OR 
    	P_A.VALUE < P_A_TYPE.MAX_THRESHOLD) AND
    	($defaultWhereClause)
    	
=== 2011-10-10 16:03:02,877 [0-14] DEBUG SQLDataSource - [builtinApplication.ADataSource_fetch] Executing row count query: SELECT COUNT(*) FROM P, P_A_TYPE, 
    	P_A, P_T, I_B WHERE P.P_ID = P_A.P_ID AND
    	P_A.P_A_TYPE_ID = 
    	P_A_TYPE.P_A_TYPE_ID AND
    	P_T.P_T_ID = P.P_T_ID AND
    	P_T.I_B_ID = I_B.I_B_ID AND
    	(P_A.VALUE > P_A_TYPE.MAX_THRESHOLD OR 
    	P_A.VALUE < P_A_TYPE.MAX_THRESHOLD) AND
    	($defaultWhereClause)
    	
=== 2011-10-10 16:03:02,879 [0-14] DEBUG SQLDataSource - [builtinApplication.ADataSource_fetch] Eval'd row count query: SELECT COUNT(*) FROM P, P_A_TYPE, 
    	P_A, P_T, I_B WHERE P.P_ID = P_A.P_ID AND
    	P_A.P_A_TYPE_ID = 
    	P_A_TYPE.P_A_TYPE_ID AND
    	P_T.P_T_ID = P.P_T_ID AND
    	P_T.I_B_ID = I_B.I_B_ID AND
    	(P_A.VALUE > P_A_TYPE.MAX_THRESHOLD OR 
    	P_A.VALUE < P_A_TYPE.MAX_THRESHOLD) AND
    	(((P_T.Name = '[apples , pears, oranges]' AND P_T.Name IS NOT NULL)))

Here is where I set the list grid properties:

protected void setListGridProperties(String aDataSourceName)
	{
		// set the properties of the list grid
		theListGrid.setWidth100();
		theListGrid.setHeight100();
		theListGridDataSource = DataSource.get(aDataSourceName);
		if(theListGridDataSource != null)
		{
			theListGrid.setDataSource(theListGridDataSource);	
		}
		
		// set some properties for the list grid
		theListGrid.setAutoFetchData(true);
		theListGrid.setShowFilterEditor(true);
		theListGrid.setAllowFilterExpressions(true);
		
		// get all the fields from the data source and add them to the
		// list grid so we can create multiple select items for the 
		// fields that have value maps. 
		ArrayList<ListGridField> fieldList = new ArrayList<ListGridField>();
		for(String s : theListGridDataSource.getFieldNames())
		{
			fieldList.add(new ListGridField(s)); 
		}
		ListGridField []fieldArray = new ListGridField[fieldList.size()];
		fieldList.toArray(fieldArray);
		theListGrid.setFields(fieldArray);
	}

Here is where I set the multiple select items:

protected void createMultipleSelectItemForListGridField(String aFieldName, 
			String aDataSourceName)
	{
		// set the value maps for the fields that have a fixed number
		// of possible values
		ListGridField aField = theListGrid.getField(aFieldName);
		aField.setOptionDataSource(DataSource.get(aDataSourceName));
		aField.setAutoFetchDisplayMap(true);
		
		// create the select item that will be used by the list grid field
		SelectItem anItem = new SelectItem();
		anItem.setMultiple(true);
		aField.setFilterEditorProperties(anItem);
		
		aField.setFilterEditorProperties(anItem);

	}

That is all I do with the list grid. How can I fix this? I tried looking at the showcase example, but I'm not doing anything different here.

[pkelley]

I am seeing the exact same behavior. It seems like something is not getting converted correctly when the SQL statement is being generated which is causing the brackets you see in the example above ("[A,B]") to be placed into the SQL that goes to the server.

Everything up to this point works correctly including getting the original value list to be displayed correctly. It is only when it tries to filter the values by sending this SQL statement that it has an issue.

[Isomorphic]

Looking at these two lines in the logs:

fieldName:"P_T_NAME",
operator:"equals",
value:[
"Apples",
"Pears",
"Oranges"
],
...
=== 2011-10-10 16:03:02,872 [0-14] INFO SQLDataSource - [builtinApplication.ADataSource_fetch] Performing fetch operation with
criteria: {operator:"and",_constructor:"AdvancedCriteria",criteria:[{fieldName:"P_T_NAME",operator:"equals",value:["apples ","pears","oranges"]

Do you have logic in place to *lowercase* all values for the P_T_NAME field? If so, that may be converting the array to a String. If not, then presumably this is a hand-edited log?

[azuniga]

Looking at these two lines in the logs:



Do you have logic in place to *lowercase* all values for the P_T_NAME field? If so, that may be converting the array to a String. If not, then presumably this is a hand-edited log?

It is hand edited. There is no logic in place to lowercase the values.

[pkelley]

After testing this further it seems there is almost certainly a bug here that is causing the SQL to come out of misformed. Any help on this would be greatly appreciated.

[Isomorphic]

We plan to treat this as a bug, however if you need an immediate workaround, you can transform the criteria so that it consists of 3 "equals" operators grouped together under a logical "or" operator, then allow it through the SQLDataSource.

[pkelley]

That is excellent, thanks. I would like to try this workaround until the fix makes it into a release. Would you mind showing a small code sample that shows how this would look? I can't picture where to start to transform the criteria before it is sent to the server.

[Isomorphic]

The easiest thing is to start with the server-side dsRequest.getAdvancedCriteria() API. For fully generality, you will need to descend this structure recursively through logical operators, and whenever you find a Criterion with array value, replace it with an AndOperator with N sub-criteria with the specific singular values.

The framework will just handle this automatically soon.

[toddblackwell]

I work with both pkelley and azuniga, and from a release point-of-view, in what release is the fix that automatically handles this going to reside, and when will it become available as an official release?

The reason I'm asking is that we have a formal process for pushing through software updates in our development and testing environments that is not trivial, so the sooner we can get this information, the better. If it's not going to be for months, then we have to code to the workaround as the end of our testing and integration phase is coming up fast. We'll then have to do some regression testing once the new release is available and pushed into our environment. I'd like to avoid the multiple version testing if at all possible.

Thanks,

Todd

[Isomorphic]

The task has been allocated, and should be complete within a week or so. Since we have decided to treat this as a bug, it will be fixed in the 2.5 nightlies (and therefore in the upcoming maintenance release for 2.5) as well as in 3.0.

[azuniga]

The task has been allocated, and should be complete within a week or so. Since we have decided to treat this as a bug, it will be fixed in the 2.5 nightlies (and therefore in the upcoming maintenance release for 2.5) as well as in 3.0.

Excellent! Thank you very much.

[pkelley]

Has this made its way into a nightly build yet? Thank you!

[Isomorphic]

Yes - since Oct 20th actually. Sorry, we updated one thread about this issue but not another.