String is displayed as Array in Generated SQL.

[curiousgally]

Isomorphic,

I have seen various threads regarding this where an array passed to Advanced Criteria is used as it is in the Query with [] etc. I have ensured that I have no toString() calls in my project, but I still have this issue. I'm casting to (String) object everywhere instead. I even tried the getValueAsString approach wherever applicable but to no avail.

Can you provide me a solution to this. It happens only on tomcat but not on hosted mode.!!!!

Thanks.

[Isomorphic]

Here's the GWT bug for this. If it's affecting you, star it - that's one way they use to figure out how many people are being affected.

As far as how you still have a problem, you'll just need to isolate it to a small amount of code, and go over that carefully.

[curiousgally]

Isomorphic,

My initial code was trying to take a comma seperated textitem value from values manager and split and create a criteria with array as below:

String origin = valuesManager.getValue("origin").toString().toUpperCase();
advancedCriteria.addCriteria(new Criterion("ORIG", OperatorId.EQUALS, origin.split(",")));

I tried changing to this:

String origin = ((String) valuesManager.getValue("origin")).toUpperCase();
advancedCriteria.addCriteria(new Criterion("ORIG", OperatorId.EQUALS, origin.split(",")));

then this:

String origin = valuesManager.getValueAsString("origin").toUpperCase();
advancedCriteria.addCriteria(new Criterion("ORIG", OperatorId.EQUALS, origin.split(",")));

and even this:

advancedCriteria.addCriteria(new Criterion("ORIG", OperatorId.EQUALS, new String[]{"DFW","JFK"}));

nothing worked.

All these cases just show that one can never use the method addCriteria passing an array of strings (last sample shows this clearly). So are you using string operation on the array passed to addCriteria method in this case internally. I could get it to work by manually creating the 'OR' criteria among the array elements. This is definitely a overkill. Can this be fixed, else we end up creating a new criteria with array of Criterion objects, manually constructing the "OR" implementation among the elements present in the array.

Thanks.

[Isomorphic]

What's led you to focus on this line of code and what specifically is going wrong?

If you try:

		AdvancedCriteria crit = new AdvancedCriteria("SKU", OperatorId.NOT_NULL);
		crit.addCriteria(new Criterion("SKU", OperatorId.EQUALS, new String[]{"DFW","JFK"}));
		DataSource.get("supplyItem").fetchData(crit, new DSCallback() {
			@Override
			public void execute(DSResponse response, Object rawData, DSRequest request) {
			}
		});

.. you'll see a correct dsRequest go to the server - no problems with Strings becoming Arrays.

[curiousgally]

All of the different cases i have posted I'm doing nothing but trying to pass the String array to the Criterion so that it automatically constructs the OR for values within the array. This line is the only criteria in which I'm passing an Array. That particular line works fine in Hosted mode but not when deployed in tomcat. In tomcat that particular line creates a query which has something like this:

ORIG = '[JFK,DFW]' instead of (ORIG = 'JFK' OR ORIG='DFW')

These are not the exact outputs but this is how it would like.

Thanks.

[Isomorphic]

Ah. This is unrelated to the GWT bug. You titled this thread "String is displayed as Array in Generated SQL" but it seems like you really meant "Array is used as String in Generated SQL".

We've already enhanced the SQL generator to automatically turn this type of criteria into multiple comparisons with an OR in 3.0. If you're sticking with 2.5 you do need to create the separate Criterions for each value and combine them with an OR operator yourself.

[curiousgally]

Code snippet where I'm constructing the advanced criteria:

AdvancedCriteria advancedCriteria = new AdvancedCriteria();

advancedCriteria.addCriteria(new Criterion("ORIG", OperatorId.EQUALS, new String[]{"DFW", "JFK"}));
        advancedCriteria.addCriteria(new Criterion("DEST", OperatorId.EQUALS, "LHR"));
        advancedCriteria.addCriteria("SECURITY_ID", OperatorId.EQUALS, "120");
        advancedCriteria.addCriteria(new Criterion("DATES", OperatorId.GREATER_OR_EQUAL, 200901));
        advancedCriteria.addCriteria(new Criterion("DATES", OperatorId.LESS_OR_EQUAL,200901));
        advancedCriteria.addCriteria("REPORT_TYPE", "DIR");
        advancedCriteria.addCriteria("AIRLINE_TYPE", "Marketing");

LOGS showing query formed:

=== 2011-11-16 07:38:49,143 [81-6] DEBUG RPCManager - Processing 1 requests.
=== 2011-11-16 07:38:49,158 [81-6] DEBUG RPCManager - Request #1 (DSRequest) payload: {
    criteria:{
        _constructor:"AdvancedCriteria",
        operator:"and",
        criteria:[
            {
                fieldName:"ORIG",
                operator:"equals",
                value:[
                    "DFW",
                    "JFK"
                ]
            },
            {
                fieldName:"DEST",
                operator:"equals",
                value:"LHR"
            },
            {
                fieldName:"SECURITY_ID",
                operator:"equals",
                value:"120"
            },
            {
                fieldName:"DATES",
                operator:"greaterOrEqual",
                value:200901
            },
            {
                fieldName:"DATES",
                operator:"lessOrEqual",
                value:200901
            },
            {
                fieldName:"REPORT_TYPE",
                operator:"equals",
                value:"DIR"
            },
            {
                fieldName:"AIRLINE_TYPE",
                operator:"equals",
                value:"Marketing"
            }
        ]
    },
    operationConfig:{
        dataSource:"ADB",
        operationType:"fetch",
        textMatchStyle:"exact"
    },
    startRow:0,
    endRow:5000,
    componentId:"isc_ListGrid_0",
    appID:"builtinApplication",
    operation:"summaryReport",
    oldValues:{
        _constructor:"AdvancedCriteria",
        operator:"and",
        criteria:[
            {
                fieldName:"ORIG",
                operator:"equals",
                value:[
                    "DFW",
                    "JFK"
                ]
            },
            {
                fieldName:"DEST",
                operator:"equals",
                value:"LHR"
            },
            {
                fieldName:"SECURITY_ID",
                operator:"equals",
                value:"120"
            },
            {
                fieldName:"DATES",
                operator:"greaterOrEqual",
                value:200901
            },
            {
                fieldName:"DATES",
                operator:"lessOrEqual",
                value:200901
            },
            {
                fieldName:"REPORT_TYPE",
                operator:"equals",
                value:"DIR"
            },
            {
                fieldName:"AIRLINE_TYPE",
                operator:"equals",
                value:"Marketing"
            }
        ]
    }
}
=== 2011-11-16 07:38:49,205 [81-6] INFO  IDACall - Performing 1 operation(s)
=== 2011-11-16 07:38:49,221 [81-6] DEBUG AppBase - [builtinApplication.summaryReport] No userTypes defined, allowing anyone access to all operations for this application
=== 2011-11-16 07:38:49,221 [81-6] DEBUG AppBase - [builtinApplication.summaryReport] No public zero-argument method named '_summaryReport' found, performing generic datasource operation
=== 2011-11-16 07:38:49,222 [81-6] INFO  SQLDataSource - [builtinApplication.summaryReport] Performing fetch operation with
	criteria: {_constructor:"AdvancedCriteria",operator:"and",criteria:[{fieldName:"ORIG",operator:"equals",value:["DFW","JFK"]},{fieldName:"DEST",operator:"equals",value:"LHR"},{fieldName:"SECURITY_ID",operator:"equals",value:"120"},{fieldName:"DATES",operator:"greaterOrEqual",value:200901},{fieldName:"DATES",operator:"lessOrEqual",value:200901},{fieldName:"REPORT_TYPE",operator:"equals",value:"DIR"},{fieldName:"AIRLINE_TYPE",operator:"equals",value:"Marketing"}]}	values: {_constructor:"AdvancedCriteria",operator:"and",criteria:[{fieldName:"ORIG",operator:"equals",value:["DFW","JFK"]},{fieldName:"DEST",operator:"equals",value:"LHR"},{fieldName:"SECURITY_ID",operator:"equals",value:"120"},{fieldName:"DATES",operator:"greaterOrEqual",value:200901},{fieldName:"DATES",operator:"lessOrEqual",value:200901},{fieldName:"REPORT_TYPE",operator:"equals",value:"DIR"},{fieldName:"AIRLINE_TYPE",operator:"equals",value:"Marketing"}]}
=== 2011-11-16 07:38:49,246 [81-6] INFO  SQLDataSource - [builtinApplication.summaryReport] derived query: SELECT 
                #if($advancedCriteria.REPORT_TYPE == "NDIR")
                 SUBSTR(AIRPORT_PAIR_DIM.AIRPP_NDIR_AIRP_PAIR,1,3) AS
                ORIG,SUBSTR(AIRPORT_PAIR_DIM.AIRPP_NDIR_AIRP_PAIR,4,3) AS DEST,
                SUBSTR(AIRPORT_PAIR_DIM.CTY_NDIR_CITY_PAIR,1,3) AS ORG_CTY_CD ,
                SUBSTR(AIRPORT_PAIR_DIM.CTY_NDIR_CITY_PAIR,4,3) AS DEST_CTY_CD,
                SUBSTR(AIRPORT_PAIR_DIM.CO_NDIR_CNTRY_PAIR,1,3) AS ORG_CNTRY_CD,
                SUBSTR(AIRPORT_PAIR_DIM.CO_NDIR_CNTRY_PAIR,4,3) AS
                DEST_CNTRY_CD,SUBSTR(AIRPORT_PAIR_DIM.WA_NDIR_WA_PAIR,1,3) AS
                WACO,SUBSTR(AIRPORT_PAIR_DIM.WA_NDIR_WA_PAIR,4,3) AS WACD,
                #else
                AIRPORT_PAIR_DIM.AIRPP_DEPART_AIRP_CODE AS ORIG,AIRPORT_PAIR_DIM.AIRPP_ARRIVAL_AIRP_CODE AS DEST,
                AIRPORT_PAIR_DIM.CTY_DEP_CITY_CODE AS ORG_CTY_CD , AIRPORT_PAIR_DIM.CTY_ARR_CITY_CODE AS DEST_CTY_CD,
                AIRPORT_PAIR_DIM.CO_DEP_CNTRY_CODE AS ORG_CNTRY_CD, AIRPORT_PAIR_DIM.CO_ARR_CNTRY_CODE AS
                DEST_CNTRY_CD,AIRPORT_PAIR_DIM.WA_ARR_WA_CODE AS WACO,AIRPORT_PAIR_DIM.WA_DEP_WA_CODE AS WACD,
                #end
                #if($advancedCriteria.AIRLINE_TYPE == "Marketing")
                DOMAIRL,
                #else
                OPAIRL,
                #end
                SUM(UPAX) AS UNADJBOOKINGS, SUM(PAX_COUNT) AS BOOKINGS,ROUND(SUM(ADB_REVENUE)/SUM(PAX_COUNT),2) AS FARE,
                SUM(ADB_REVENUE) AS REVENUE
             FROM 
                ADB,AIRPORT_PAIR_DIM
             WHERE 
                ADB.ORIG=AIRPORT_PAIR_DIM.AIRPP_DEPART_AIRP_CODE AND ADB.DEST= AIRPORT_PAIR_DIM.AIRPP_ARRIVAL_AIRP_CODE
                AND ($defaultWhereClause)
             GROUP BY 
                #if($advancedCriteria.REPORT_TYPE == "NDIR")
                SUBSTR(AIRPORT_PAIR_DIM.AIRPP_NDIR_AIRP_PAIR,1,3),SUBSTR(AIRPORT_PAIR_DIM.AIRPP_NDIR_AIRP_PAIR,4,3),
                SUBSTR(AIRPORT_PAIR_DIM.CTY_NDIR_CITY_PAIR,1,3), SUBSTR(AIRPORT_PAIR_DIM.CTY_NDIR_CITY_PAIR,4,3),
                SUBSTR(AIRPORT_PAIR_DIM.CO_NDIR_CNTRY_PAIR,1,3),SUBSTR(AIRPORT_PAIR_DIM.CO_NDIR_CNTRY_PAIR,4,3),
                SUBSTR(AIRPORT_PAIR_DIM.WA_NDIR_WA_PAIR,1,3),SUBSTR(AIRPORT_PAIR_DIM.WA_NDIR_WA_PAIR,4,3),
                #else
                AIRPORT_PAIR_DIM.AIRPP_DEPART_AIRP_CODE ,AIRPORT_PAIR_DIM.AIRPP_ARRIVAL_AIRP_CODE,
                AIRPORT_PAIR_DIM.CTY_DEP_CITY_CODE, AIRPORT_PAIR_DIM.CTY_ARR_CITY_CODE,
                AIRPORT_PAIR_DIM.CO_DEP_CNTRY_CODE, AIRPORT_PAIR_DIM.CO_ARR_CNTRY_CODE,
                AIRPORT_PAIR_DIM.WA_ARR_WA_CODE,AIRPORT_PAIR_DIM.WA_DEP_WA_CODE,
                #end
                #if($advancedCriteria.AIRLINE_TYPE == "Marketing")
                DOMAIRL
                #else
                OPAIRL
                #end
             ORDER BY 
                SUM(PAX_COUNT) DESC
            
log4j:WARN No appenders could be found for logger (org.apache.Velocity).
log4j:WARN Please initialize the log4j system properly.
=== 2011-11-16 07:38:49,563 [81-6] DEBUG SQLDataSource - [builtinApplication.summaryReport] Executing row count query: SELECT COUNT(*) FROM (SELECT 
                #if($advancedCriteria.REPORT_TYPE == "NDIR")
                 SUBSTR(AIRPORT_PAIR_DIM.AIRPP_NDIR_AIRP_PAIR,1,3) AS
                ORIG,SUBSTR(AIRPORT_PAIR_DIM.AIRPP_NDIR_AIRP_PAIR,4,3) AS DEST,
                SUBSTR(AIRPORT_PAIR_DIM.CTY_NDIR_CITY_PAIR,1,3) AS ORG_CTY_CD ,
                SUBSTR(AIRPORT_PAIR_DIM.CTY_NDIR_CITY_PAIR,4,3) AS DEST_CTY_CD,
                SUBSTR(AIRPORT_PAIR_DIM.CO_NDIR_CNTRY_PAIR,1,3) AS ORG_CNTRY_CD,
                SUBSTR(AIRPORT_PAIR_DIM.CO_NDIR_CNTRY_PAIR,4,3) AS
                DEST_CNTRY_CD,SUBSTR(AIRPORT_PAIR_DIM.WA_NDIR_WA_PAIR,1,3) AS
                WACO,SUBSTR(AIRPORT_PAIR_DIM.WA_NDIR_WA_PAIR,4,3) AS WACD,
                #else
                AIRPORT_PAIR_DIM.AIRPP_DEPART_AIRP_CODE AS ORIG,AIRPORT_PAIR_DIM.AIRPP_ARRIVAL_AIRP_CODE AS DEST,
                AIRPORT_PAIR_DIM.CTY_DEP_CITY_CODE AS ORG_CTY_CD , AIRPORT_PAIR_DIM.CTY_ARR_CITY_CODE AS DEST_CTY_CD,
                AIRPORT_PAIR_DIM.CO_DEP_CNTRY_CODE AS ORG_CNTRY_CD, AIRPORT_PAIR_DIM.CO_ARR_CNTRY_CODE AS
                DEST_CNTRY_CD,AIRPORT_PAIR_DIM.WA_ARR_WA_CODE AS WACO,AIRPORT_PAIR_DIM.WA_DEP_WA_CODE AS WACD,
                #end
                #if($advancedCriteria.AIRLINE_TYPE == "Marketing")
                DOMAIRL,
                #else
                OPAIRL,
                #end
                SUM(UPAX) AS UNADJBOOKINGS, SUM(PAX_COUNT) AS BOOKINGS,ROUND(SUM(ADB_REVENUE)/SUM(PAX_COUNT),2) AS FARE,
                SUM(ADB_REVENUE) AS REVENUE
             FROM 
                ADB,AIRPORT_PAIR_DIM
             WHERE 
                ADB.ORIG=AIRPORT_PAIR_DIM.AIRPP_DEPART_AIRP_CODE AND ADB.DEST= AIRPORT_PAIR_DIM.AIRPP_ARRIVAL_AIRP_CODE
                AND ($defaultWhereClause)
             GROUP BY 
                #if($advancedCriteria.REPORT_TYPE == "NDIR")
                SUBSTR(AIRPORT_PAIR_DIM.AIRPP_NDIR_AIRP_PAIR,1,3),SUBSTR(AIRPORT_PAIR_DIM.AIRPP_NDIR_AIRP_PAIR,4,3),
                SUBSTR(AIRPORT_PAIR_DIM.CTY_NDIR_CITY_PAIR,1,3), SUBSTR(AIRPORT_PAIR_DIM.CTY_NDIR_CITY_PAIR,4,3),
                SUBSTR(AIRPORT_PAIR_DIM.CO_NDIR_CNTRY_PAIR,1,3),SUBSTR(AIRPORT_PAIR_DIM.CO_NDIR_CNTRY_PAIR,4,3),
                SUBSTR(AIRPORT_PAIR_DIM.WA_NDIR_WA_PAIR,1,3),SUBSTR(AIRPORT_PAIR_DIM.WA_NDIR_WA_PAIR,4,3),
                #else
                AIRPORT_PAIR_DIM.AIRPP_DEPART_AIRP_CODE ,AIRPORT_PAIR_DIM.AIRPP_ARRIVAL_AIRP_CODE,
                AIRPORT_PAIR_DIM.CTY_DEP_CITY_CODE, AIRPORT_PAIR_DIM.CTY_ARR_CITY_CODE,
                AIRPORT_PAIR_DIM.CO_DEP_CNTRY_CODE, AIRPORT_PAIR_DIM.CO_ARR_CNTRY_CODE,
                AIRPORT_PAIR_DIM.WA_ARR_WA_CODE,AIRPORT_PAIR_DIM.WA_DEP_WA_CODE,
                #end
                #if($advancedCriteria.AIRLINE_TYPE == "Marketing")
                DOMAIRL
                #else
                OPAIRL
                #end
            ) work
=== 2011-11-16 07:38:49,585 [81-6] DEBUG SQLDataSource - [builtinApplication.summaryReport] Eval'd row count query: SELECT COUNT(*) FROM (SELECT 
                                AIRPORT_PAIR_DIM.AIRPP_DEPART_AIRP_CODE AS ORIG,AIRPORT_PAIR_DIM.AIRPP_ARRIVAL_AIRP_CODE AS DEST,
                AIRPORT_PAIR_DIM.CTY_DEP_CITY_CODE AS ORG_CTY_CD , AIRPORT_PAIR_DIM.CTY_ARR_CITY_CODE AS DEST_CTY_CD,
                AIRPORT_PAIR_DIM.CO_DEP_CNTRY_CODE AS ORG_CNTRY_CD, AIRPORT_PAIR_DIM.CO_ARR_CNTRY_CODE AS
                DEST_CNTRY_CD,AIRPORT_PAIR_DIM.WA_ARR_WA_CODE AS WACO,AIRPORT_PAIR_DIM.WA_DEP_WA_CODE AS WACD,
                                                DOMAIRL,
                                SUM(UPAX) AS UNADJBOOKINGS, SUM(PAX_COUNT) AS BOOKINGS,ROUND(SUM(ADB_REVENUE)/SUM(PAX_COUNT),2) AS FARE,
                SUM(ADB_REVENUE) AS REVENUE
             FROM 
                ADB,AIRPORT_PAIR_DIM
             WHERE 
                ADB.ORIG=AIRPORT_PAIR_DIM.AIRPP_DEPART_AIRP_CODE AND ADB.DEST= AIRPORT_PAIR_DIM.AIRPP_ARRIVAL_AIRP_CODE
                AND (((ORIG = '[DFW, JFK]' AND ORIG IS NOT NULL) AND (DEST = 'LHR' AND DEST IS NOT NULL) AND (SECURITY_ID = '120' AND SECURITY_ID IS NOT NULL) AND (DATES >= '200901' AND DATES IS NOT NULL) AND (DATES <= '200901' OR DATES IS NULL) AND '1'='1' AND '1'='1'))
             GROUP BY 
                                AIRPORT_PAIR_DIM.AIRPP_DEPART_AIRP_CODE ,AIRPORT_PAIR_DIM.AIRPP_ARRIVAL_AIRP_CODE,
                AIRPORT_PAIR_DIM.CTY_DEP_CITY_CODE, AIRPORT_PAIR_DIM.CTY_ARR_CITY_CODE,
                AIRPORT_PAIR_DIM.CO_DEP_CNTRY_CODE, AIRPORT_PAIR_DIM.CO_ARR_CNTRY_CODE,
                AIRPORT_PAIR_DIM.WA_ARR_WA_CODE,AIRPORT_PAIR_DIM.WA_DEP_WA_CODE,
                                                DOMAIRL
                            ) work
=== 2011-11-16 07:38:49,623 [81-6] DEBUG PoolableSQLConnectionFactory - [builtinApplication.summaryReport] Initializing SQL config for 'Oracle' from system config - using DataSource:  oracle.jdbc.pool.OracleDataSource
=== 2011-11-16 07:38:52,702 [81-6] DEBUG PoolableSQLConnectionFactory - [builtinApplication.summaryReport] Returning pooled Connection
=== 2011-11-16 07:38:53,357 [81-6] INFO  SQLDriver - [builtinApplication.summaryReport] Executing SQL query on 'Oracle': SELECT COUNT(*) FROM (SELECT 
                                AIRPORT_PAIR_DIM.AIRPP_DEPART_AIRP_CODE AS ORIG,AIRPORT_PAIR_DIM.AIRPP_ARRIVAL_AIRP_CODE AS DEST,
                AIRPORT_PAIR_DIM.CTY_DEP_CITY_CODE AS ORG_CTY_CD , AIRPORT_PAIR_DIM.CTY_ARR_CITY_CODE AS DEST_CTY_CD,
                AIRPORT_PAIR_DIM.CO_DEP_CNTRY_CODE AS ORG_CNTRY_CD, AIRPORT_PAIR_DIM.CO_ARR_CNTRY_CODE AS
                DEST_CNTRY_CD,AIRPORT_PAIR_DIM.WA_ARR_WA_CODE AS WACO,AIRPORT_PAIR_DIM.WA_DEP_WA_CODE AS WACD,
                                                DOMAIRL,
                                SUM(UPAX) AS UNADJBOOKINGS, SUM(PAX_COUNT) AS BOOKINGS,ROUND(SUM(ADB_REVENUE)/SUM(PAX_COUNT),2) AS FARE,
                SUM(ADB_REVENUE) AS REVENUE
             FROM 
                ADB,AIRPORT_PAIR_DIM
             WHERE 
                ADB.ORIG=AIRPORT_PAIR_DIM.AIRPP_DEPART_AIRP_CODE AND ADB.DEST= AIRPORT_PAIR_DIM.AIRPP_ARRIVAL_AIRP_CODE
                AND (((ORIG = '[DFW, JFK]' AND ORIG IS NOT NULL) AND (DEST = 'LHR' AND DEST IS NOT NULL) AND (SECURITY_ID = '120' AND SECURITY_ID IS NOT NULL) AND (DATES >= '200901' AND DATES IS NOT NULL) AND (DATES <= '200901' OR DATES IS NULL) AND '1'='1' AND '1'='1'))
             GROUP BY 
                                AIRPORT_PAIR_DIM.AIRPP_DEPART_AIRP_CODE ,AIRPORT_PAIR_DIM.AIRPP_ARRIVAL_AIRP_CODE,
                AIRPORT_PAIR_DIM.CTY_DEP_CITY_CODE, AIRPORT_PAIR_DIM.CTY_ARR_CITY_CODE,
                AIRPORT_PAIR_DIM.CO_DEP_CNTRY_CODE, AIRPORT_PAIR_DIM.CO_ARR_CNTRY_CODE,
                AIRPORT_PAIR_DIM.WA_ARR_WA_CODE,AIRPORT_PAIR_DIM.WA_DEP_WA_CODE,
                                                DOMAIRL
                            ) work
=== 2011-11-16 07:38:54,003 [81-6] INFO  DSResponse - [builtinApplication.summaryReport] DSResponse: List with 0 items
=== 2011-11-16 07:38:54,297 [81-6] DEBUG RPCManager - Content type for RPC transaction: text/plain; charset=UTF-8
=== 2011-11-16 07:38:54,298 [81-6] DEBUG RPCManager - non-DMI response, dropExtraFields: false

[Isomorphic]

Not sure what this is intended to show. Again, 3.0 has been enhanced (as of a couple of weeks ago) to automatically expand string arrays into an OR clause. So this log is either from 2.5 (which will not have this enhancement backported) or an older 3.0 nightly.

[curiousgally]

Thanks Isomorphic,

I will try with latest 3.0 and get back.

[curiousgally]

Isomorphic,

I downloaded the latest nightly and checked this. It works fine..!!!!

Thanks.

[harsha.galla]

Isomorphic,

I see this happening but in a little different scenario:

AdvancedCriteria criteria = new AdvancedCriteria(OperatorId.OR,new Criterion[]{new Criterion("MB.LEG2",OperatorId.EQUALS,new String[]{"BOM","AMD"}),
            new Criterion("MB.LEG3",OperatorId.EQUALS,new String[]{"BOM","AMD"}),new Criterion("MB.LEG4",OperatorId.EQUALS,new String[]{"BOM","AMD"})});

which constructs the query as:

(((MB.LEG2 = '[BOM, AMD]' AND MB.LEG2 IS NOT NULL) OR (MB.LEG3 = '[BOM, AMD]' AND MB.LEG3 IS NOT NULL) OR (MB.LEG4 = '[BOM, AMD]' AND MB.LEG4 IS NOT NULL))

while expected output is:

(MB.LEG2 ='BOM' OR MB.LEG2='AMD') OR (MB.LEG3='BOM' OR MB.LEG3='AMD') 
OR (MB.LEG4='BOM' OR MB.LEG4='AMD')

which is wrong. however the scenario mentioned in the earliers posts in this thread work fine.
Can this be fixed.

Snapshot: Isomorphic SmartClient Framework (SC_SNAPSHOT-2012-01-17_v8.2p/Enterprise Deployment 2012-01-17)
Thanks.

[Isomorphic]

We're showing this as fixed in 3.0p. If you disagree, please provide information that would allow us to reproduce the problem.

[harsha.galla]

Isomorphic,

I could able to reproduce this with the sample.

package com.smartgwt.sample.client;

import com.google.gwt.core.client.EntryPoint;
import com.smartgwt.client.core.KeyIdentifier;
import com.smartgwt.client.data.AdvancedCriteria;
import com.smartgwt.client.data.Criterion;
import com.smartgwt.client.data.DataSource;
import com.smartgwt.client.data.Record;
import com.smartgwt.client.types.OperatorId;
import com.smartgwt.client.types.SelectionStyle;
import com.smartgwt.client.types.SortArrow;
import com.smartgwt.client.util.KeyCallback;
import com.smartgwt.client.util.Page;
import com.smartgwt.client.util.SC;
import com.smartgwt.client.widgets.IButton;
import com.smartgwt.client.widgets.Label;
import com.smartgwt.client.widgets.events.ClickEvent;
import com.smartgwt.client.widgets.events.ClickHandler;
import com.smartgwt.client.widgets.form.DynamicForm;
import com.smartgwt.client.widgets.grid.ListGrid;
import com.smartgwt.client.widgets.grid.ListGridField;
import com.smartgwt.client.widgets.grid.ListGridRecord;
import com.smartgwt.client.widgets.grid.events.RecordClickEvent;
import com.smartgwt.client.widgets.grid.events.RecordClickHandler;
import com.smartgwt.client.widgets.layout.HLayout;
import com.smartgwt.client.widgets.layout.VStack;
import com.smartgwt.client.widgets.viewer.DetailViewer;

/**
 * Entry point classes define <code>onModuleLoad()</code>.
 */
public class BuiltInDS implements EntryPoint {
    private ListGrid boundList;
    private DynamicForm boundForm;
    private IButton saveBtn;
    private DetailViewer boundViewer;
    private IButton newBtn;

    /**
     * This is the entry point method.
     */
    public void onModuleLoad() {
        KeyIdentifier debugKey = new KeyIdentifier();
        debugKey.setCtrlKey(true);
        debugKey.setKeyName("D");

        Page.registerKey(debugKey, new KeyCallback() {
            public void execute(String keyName) {
                SC.showConsole();
            }
        });


        ListGrid grid = new ListGrid();
        grid.setLeft(20);
        grid.setTop(75);
        grid.setWidth(130);
        grid.setLeaveScrollbarGap(false);
        grid.setShowSortArrow(SortArrow.NONE);
        grid.setCanSort(false);
        grid.setFields(new ListGridField("dsTitle", "Select a DataSource"));
        grid.setData(new ListGridRecord[]{
                new DSRecord("Animals", "animals"),
                new DSRecord("Office Supplies", "supplyItem"),
                new DSRecord("Employees", "employees")}
        );
        grid.setSelectionType(SelectionStyle.SINGLE);
        grid.addRecordClickHandler(new RecordClickHandler() {
            public void onRecordClick(RecordClickEvent event) {
                DSRecord record = (DSRecord) event.getRecord();
                bindComponents(record.getDsName());
            }
        });

        grid.draw();

        VStack vStack = new VStack();
        vStack.setLeft(175);
        vStack.setTop(75);
        vStack.setWidth("70%");
        vStack.setMembersMargin(20);

        Label label = new Label();
        label.setContents("<ul>" +
                "<li>select a datasource from the list at left to bind to these components</li>" +
                "<li>click a record in the grid to view and edit that record in the form</li>" +
                "<li>click <b>New</b> to start editing a new record in the form</li>" +
                "<li>click <b>Save</b> to save changes to a new or edited record in the form</li>" +
                "<li>click <b>Clear</b> to clear all fields in the form</li>" +
                "<li>click <b>Filter</b> to filter (substring match) the grid based on form values</li>" +
                "<li>click <b>Fetch</b> to fetch records (exact match) for the grid based on form values</li>" +
                "<li>double-click a record in the grid to edit inline (press Return, or arrow/tab to another record, to save)</li>" +
                "</ul>");
        vStack.addMember(label);

        boundList = new ListGrid();
        boundList.setHeight(200);
        boundList.setCanEdit(true);

        boundList.addRecordClickHandler(new RecordClickHandler() {
            public void onRecordClick(RecordClickEvent event) {
                Record record = event.getRecord();
                boundForm.editRecord(record);
                saveBtn.enable();
                boundViewer.viewSelectedData(boundList);
            }
        });
        vStack.addMember(boundList);

        boundForm = new DynamicForm();
        boundForm.setNumCols(6);
        boundForm.setAutoFocus(false);
        vStack.addMember(boundForm);

        HLayout hLayout = new HLayout(10);
        hLayout.setMembersMargin(10);
        hLayout.setHeight(22);

        saveBtn = new IButton("Save");
        saveBtn.addClickHandler(new ClickHandler() {
            public void onClick(ClickEvent event) {
                boundForm.saveData();
                if (!boundForm.hasErrors()) {
                    boundForm.clearValues();
                    saveBtn.disable();
                }
            }
        });
        hLayout.addMember(saveBtn);

        newBtn = new IButton("New");
        newBtn.addClickHandler(new ClickHandler() {
            public void onClick(ClickEvent event) {
                boundForm.editNewRecord();
                saveBtn.enable();
            }
        });
        hLayout.addMember(newBtn);

        IButton clearBtn = new IButton("Clear");
        clearBtn.addClickHandler(new ClickHandler() {
            public void onClick(ClickEvent event) {
                boundForm.clearValues();
                saveBtn.disable();
            }
        });
        hLayout.addMember(clearBtn);

        IButton filterBtn = new IButton("Filter");
        filterBtn.addClickHandler(new ClickHandler() {
            public void onClick(ClickEvent event) {
                boundList.filterData(boundForm.getValuesAsCriteria());
                saveBtn.disable();
            }
        });
        hLayout.addMember(filterBtn);

        IButton fetchBtn = new IButton("Fetch");
        fetchBtn.addClickHandler(new ClickHandler() {
            public void onClick(ClickEvent event) {
                boundList.fetchData(boundForm.getValuesAsCriteria());
                saveBtn.disable();
            }
        });
        hLayout.addMember(fetchBtn);

        vStack.addMember(hLayout);

        boundViewer = new DetailViewer();
        vStack.addMember(boundViewer);

        vStack.draw();
    }

    private void bindComponents(String dsName) {
        DataSource ds = DataSource.get(dsName);
        boundList.setDataSource(ds);
        boundViewer.setDataSource(ds);
        boundForm.setDataSource(ds);
        boundList.fetchData(getCriteria());
        newBtn.enable();
        saveBtn.disable();
    }

    private AdvancedCriteria getCriteria() {
//        AdvancedCriteria advancedCriteria = new AdvancedCriteria();
//        advancedCriteria.addCriteria("status",OperatorId.EQUALS,new String[]{"Threatened"});
//         advancedCriteria.addCriteria(new AdvancedCriteria(OperatorId.OR, new Criterion[]{
//                new Criterion("status", OperatorId.EQUALS, new String[]{"Herbivore", "Carnivore"})
//        }));

        AdvancedCriteria advancedCriteria = new AdvancedCriteria(OperatorId.AND,new Criterion[]{new Criterion("status",OperatorId.EQUALS,new String[]{"Threatened"}),
        new AdvancedCriteria(OperatorId.OR, new Criterion[]{
                new Criterion("status", OperatorId.EQUALS, new String[]{"Herbivore", "Carnivore"})
        })});

        return advancedCriteria;
    }
}

The logs:

=== 2012-02-13 18:05:16,154 [0-34] DEBUG XML - Parsed XML from (in memory stream): 3ms
=== 2012-02-13 18:05:16,163 [0-34] DEBUG RPCManager - Processing 1 requests.
=== 2012-02-13 18:05:16,165 [0-34] DEBUG RPCManager - Request #1 (DSRequest) payload: {
    criteria:{
        operator:"and",
        criteria:[
            {
                fieldName:"status",
                operator:"equals",
                value:[
                    "Threatened"
                ]
            },
            {
                operator:"or",
                criteria:[
                    {
                        fieldName:"status",
                        operator:"equals",
                        value:[
                            "Herbivore",
                            "Carnivore"
                        ]
                    }
                ]
            }
        ],
        _constructor:"AdvancedCriteria"
    },
    operationConfig:{
        dataSource:"animals",
        operationType:"fetch",
        textMatchStyle:"exact"
    },
    startRow:0,
    endRow:75,
    componentId:"isc_ListGrid_1",
    appID:"builtinApplication",
    operation:"animals_fetch",
    oldValues:{
        operator:"and",
        criteria:[
            {
                fieldName:"status",
                operator:"equals",
                value:[
                    "Threatened"
                ]
            },
            {
                operator:"or",
                criteria:[
                    {
                        fieldName:"status",
                        operator:"equals",
                        value:[
                            "Herbivore",
                            "Carnivore"
                        ]
                    }
                ]
            }
        ],
        _constructor:"AdvancedCriteria"
    }
}
=== 2012-02-13 18:05:16,165 [0-34] INFO  IDACall - Performing 1 operation(s)
=== 2012-02-13 18:05:16,165 [0-34] DEBUG AppBase - [builtinApplication.animals_fetch] No userTypes defined, allowing anyone access to all operations for this application
=== 2012-02-13 18:05:16,165 [0-34] DEBUG AppBase - [builtinApplication.animals_fetch] No public zero-argument method named '_animals_fetch' found, performing generic datasource operation
=== 2012-02-13 18:05:16,166 [0-34] INFO  SQLDataSource - [builtinApplication.animals_fetch] Performing fetch operation with
	criteria: {criteria:[{criteria:[{value:"Threatened",fieldName:"status",operator:"equals"}],operator:"or"},{operator:"or",criteria:[{fieldName:"status",operator:"equals",value:["Herbivore","Carnivore"]}]}],operator:"and",_constructor:"AdvancedCriteria"}	values: {criteria:[{criteria:[{value:"Threatened",fieldName:"status",operator:"equals"}],operator:"or"},{operator:"or",criteria:[{fieldName:"status",operator:"equals",value:["Herbivore","Carnivore"]}]}],operator:"and",_constructor:"AdvancedCriteria"}
=== 2012-02-13 18:05:16,166 [0-34] INFO  SQLDataSource - [builtinApplication.animals_fetch] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause
=== 2012-02-13 18:05:16,166 [0-34] DEBUG SQLDataSource - [builtinApplication.animals_fetch] Executing row count query: SELECT COUNT(*) FROM $defaultTableClause WHERE $defaultWhereClause
=== 2012-02-13 18:05:16,167 [0-34] DEBUG SQLDataSource - [builtinApplication.animals_fetch] Eval'd row count query: SELECT COUNT(*) FROM animals WHERE (((animals.status = 'Threatened' AND animals.status IS NOT NULL)) AND ((animals.status = '[Herbivore, Carnivore]' AND animals.status IS NOT NULL)))
=== 2012-02-13 18:05:16,167 [0-34] DEBUG PoolableSQLConnectionFactory - [builtinApplication.animals_fetch] DriverManager fetching connection for HSQLDB via jdbc url jdbc:hsqldb:hsql://localhost/isomorphic
=== 2012-02-13 18:05:16,167 [0-34] DEBUG PoolableSQLConnectionFactory - [builtinApplication.animals_fetch] Passing JDBC URL only to getConnection
=== 2012-02-13 18:05:16,219 [0-34] DEBUG PoolableSQLConnectionFactory - [builtinApplication.animals_fetch] Returning unpooled Connection
=== 2012-02-13 18:05:16,219 [0-34] INFO  SQLDriver - [builtinApplication.animals_fetch] Executing SQL query on 'HSQLDB': SELECT COUNT(*) FROM animals WHERE (((animals.status = 'Threatened' AND animals.status IS NOT NULL)) AND ((animals.status = '[Herbivore, Carnivore]' AND animals.status IS NOT NULL)))
=== 2012-02-13 18:05:16,220 [0-34] INFO  DSResponse - [builtinApplication.animals_fetch] DSResponse: List with 0 items
=== 2012-02-13 18:05:16,220 [0-34] DEBUG RPCManager - Content type for RPC transaction: text/plain; charset=UTF-8
=== 2012-02-13 18:05:16,221 [0-34] DEBUG RPCManager - non-DMI response, dropExtraFields: false
=== 2012-02-13 18:05:16,222 [0-34] INFO  Compression - /builtinds/sc/IDACall: 173 -> 148 bytes

version: Isomorphic SmartClient Framework (SC_SNAPSHOT-2012-02-07_v8.2p/Enterprise Deployment 2012-02-07)

Thanks

[harsha.galla]

Isomorphic,

Though i have mistakenly used 'status' field name for getting Carnivore or Herbivore in the sample query provided, the issue still indicates the problem in the constructed SQL.

Thanks.

[harsha.galla]

Isomorphic,

Any update on this.

Thanks.