Datasource FETCH with default SORT possibly broken in SmartGWT 13.0

[tece321]

SmartClient Version: SNAPSHOT_v13.0d_2021-09-17/PowerEdition Deployment (built 2021-09-17)
and also in version
SmartClient Version: SNAPSHOT_v13.0d_2022-01-15/PowerEdition Deployment (built 2022-01-15)

Using GWT 2.9.0

We have a simple DataSource that has been working for years in SmartGWT 6.x , and 12.x

The DataSource is associated with a ListGrid.

In the two releases of 13.0 in which I have tested this problem, the sort field is modified in the ORDER by clause by adding the primary key of the main table, but this PK is NOT a member of the group clause (just doesn't make sense) and we see a SQL failure that the PK is invalid.

The ListGrid defaults to sort the results by the field called 'Block_Name'

Here is the DataSource fetch definition:

<operationBinding operationType="fetch">
            <tableClause>
            $defaultTableClause 
            JOIN BLOCK_PROPS_REAL_USERS ON Block_Instance.FK_Block_Props = BLOCK_PROPS_REAL_USERS.idBlock_Props
            JOIN Project as Source ON BLOCK_PROPS_REAL_USERS.FK_idProject = Source.PK_Project
            JOIN Project ON Block_Instance.FK_idProject = Project.PK_Project
            </tableClause>
        </operationBinding>

The Developer console shows the RPCRequest as the following, it is the same for 12.1 and, both versions of 13.0 (2022-01-15 and 2021-09-17):

{
    "actionURL":"http://10.75.224.44:8815/beer/sc/IDACall",
    "showPrompt":true,
    "prompt":"Finding Records that match your criteria...",
    "transport":"xmlHttpRequest",
    "promptStyle":null,
    "bypassCache":true,
    "data":{
        "criteria":{
            "FK_idProject":878
        },
        "operationConfig":{
            "dataSource":"Block_Instance",
            "repo":null,
            "operationType":"fetch",
            "textMatchStyle":"exact"
        },
        "startRow":0,
        "endRow":75,
     "sortBy":[
            "Block_Name"
        ],
        "componentId":"isc_BCMProjectBlocks_1_0",
        "groupBy":[
            "Block_Name",
            "Macro",
            "CoreLead",
            "SynthLead",
            "BackendLead",
            "FK_CoreLead",
            "FK_SynthLead",
            "FK_BackendLead"
        ],
        "appID":"builtinApplication",
        "operation":"Block_Instance_fetch",
        "oldValues":{
            "FK_idProject":878
        }
    }
}

In 12.1 the ORDER BY clause is simply : "ORDER BY Block_Name ". Works fine.

In the version SmartClient Version: SNAPSHOT_v13.0d_2021-09-17/PowerEdition Deployment (built 2021-09-17), it generates this SQL:

SELECT * FROM
        (SELECT *, ROW_NUMBER() OVER (
 ORDER BY
            PK_Block_Instance) AS rowID
        FROM
            (SELECT TOP 100 PERCENT Block_Instance.Macro, BLOCK_PROPS_REAL_USERS.CoreLead AS CoreLead, BLOCK_PROPS_REAL_USERS.SynthLead AS SynthLead, BLOCK_PROPS_REAL_USERS.BackendLead AS BackendLead, BLOCK_PROPS_REAL_USERS.FK_BackendLead AS FK_BackendLead, BLOCK_PROPS_REAL_USERS.FK_CoreLead AS FK_CoreLead, BLOCK_PROPS_REAL_USERS.FK_SynthLead AS FK_SynthLead
            FROM
                Block_Instance
            JOIN BLOCK_PROPS_REAL_USERS ON Block_Instance.FK_Block_Props = BLOCK_PROPS_REAL_USERS.idBlock_Props
            JOIN Project as Source ON BLOCK_PROPS_REAL_USERS.FK_idProject = Source.PK_Project
            JOIN Project ON Block_Instance.FK_idProject = Project.PK_Project
            WHERE ( Block_Instance.FK_idProject=867 )
            GROUP BY
                Block_Instance.Macro, BLOCK_PROPS_REAL_USERS.CoreLead, BLOCK_PROPS_REAL_USERS.SynthLead,
                BLOCK_PROPS_REAL_USERS.BackendLead, BLOCK_PROPS_REAL_USERS.FK_BackendLead, BLOCK_PROPS_REAL_USERS.FK_CoreLead,
                BLOCK_PROPS_REAL_USERS.FK_SynthLead) x) y
        WHERE
            y.rowID BETWEEN 1 AND 75

In this version of smartGWT the SQL error is: Invalid column name 'PK_Block_Instance'.

In the version SmartClient Version: SNAPSHOT_v13.0d_2022-01-15/PowerEdition Deployment (built 2022-01-15), it generates this SQL:

SELECT
        Block_Instance.Macro, BLOCK_PROPS_REAL_USERS.CoreLead AS CoreLead, BLOCK_PROPS_REAL_USERS.SynthLead AS SynthLead,
        BLOCK_PROPS_REAL_USERS.BackendLead AS BackendLead, BLOCK_PROPS_REAL_USERS.FK_BackendLead AS FK_BackendLead,
        BLOCK_PROPS_REAL_USERS.FK_CoreLead AS FK_CoreLead, BLOCK_PROPS_REAL_USERS.FK_SynthLead AS FK_SynthLead, Block_Name.Block_Name
    FROM Block_Instance --Block_Instance
    JOIN BLOCK_PROPS_REAL_USERS ON Block_Instance.FK_Block_Props = BLOCK_PROPS_REAL_USERS.idBlock_Props
  JOIN Project as Source ON BLOCK_PROPS_REAL_USERS.FK_idProject = Source.PK_Project
    JOIN Project ON Block_Instance.FK_idProject = Project.PK_Project
    JOIN Block_Name ON Block_Instance.Block_Name_PK_Block_Name = Block_Name.PK_Block_Name
    WHERE ( Block_Instance.FK_idProject=867 )
    GROUP BY
        Block_Instance.Macro, BLOCK_PROPS_REAL_USERS.CoreLead, BLOCK_PROPS_REAL_USERS.SynthLead, BLOCK_PROPS_REAL_USERS.BackendLead, BLOCK_PROPS_REAL_USERS.FK_BackendLead, BLOCK_PROPS_REAL_USERS.FK_CoreLead, BLOCK_PROPS_REAL_USERS.FK_SynthLead, Block_Name.Block_Name
ORDER BY
        Block_Name, PK_Block_Instance OFFSET 0 ROWS FETCH NEXT 75 ROWS ONLY

In this second case the SQL reports that: the Column "Block_Instance.PK_Block_Instance" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.


Obviously something has changed between versions. I'm not saying that the problem lies within the Framework. It is probably more likely that we have to change our code, but I don't understand why.

We could remove the "blocksGrid.setSortField("Block_Name");" from the ListGrid, or we could add a field of the PK_Block_Instance to the "blocksGrid.setFields (.... grid field definitons.. ) or even remove the .setFields and rely on the field defintions of the DataSource.

What do you recommend and do you have an explanation for why this is happening, i.e why the PK_Block_Instance is getting added to the sort order?

The Datasource defines the the field of PK_Block_Instance as in:

<DataSource ID="Block_Instance" serverType="sql" tableName="Block_Instance" dropExtraFields="false">
    <fields>
        <field name="PK_Block_Instance" type="sequence" primaryKey="true" hidden="true" />
        <field name="FK_idProject" type="integer" foreignKey="Project.PK_Project" hidden="true" />

..... etc.

Aside: I know I have to suppress asm-8.0.1.jar in the build path to get 13.0 to run successfully, from prior email exchanges, but I can't find the documentation online with that troubleshooting tip for 13.x. Where can I find it? Tried Google and Forum searches for this information, in case I missed something else.

[Isomorphic]

Thank you for the report and all the details, the issue is reproduced. In order to ensure consisting sorting we sometimes do some automatic sorting, which appears to have an issue with certain types of requests. We are working on the fix and will follow up soon.

[tece321]

FYI.
This is what the SQL fetch looks like in 12.1

SELECT * FROM
        (SELECT *, ROW_NUMBER() OVER (
        ORDER BY
            Block_Name) AS rowID
        FROM
            (SELECT TOP 100 PERCENT Block_Instance.Macro, BLOCK_PROPS_REAL_USERS.CoreLead AS CoreLead, BLOCK_PROPS_REAL_USERS.SynthLead AS SynthLead, BLOCK_PROPS_REAL_USERS.BackendLead AS BackendLead, BLOCK_PROPS_REAL_USERS.FK_BackendLead AS FK_BackendLead, BLOCK_PROPS_REAL_USERS.FK_CoreLead AS FK_CoreLead, BLOCK_PROPS_REAL_USERS.FK_SynthLead AS FK_SynthLead, Block_Name.Block_Name
            FROM Block_Instance --Block_Instance
            JOIN BLOCK_PROPS_REAL_USERS ON Block_Instance.FK_Block_Props = BLOCK_PROPS_REAL_USERS.idBlock_Props
            JOIN Project as Source ON BLOCK_PROPS_REAL_USERS.FK_idProject = Source.PK_Project
            JOIN Project ON Block_Instance.FK_idProject = Project.PK_Project
            JOIN Block_Name ON Block_Instance.Block_Name_PK_Block_Name = Block_Name.PK_Block_Name
            WHERE ( Block_Instance.FK_idProject=878 )
            GROUP BY Block_Instance.Macro, BLOCK_PROPS_REAL_USERS.CoreLead, BLOCK_PROPS_REAL_USERS.SynthLead, BLOCK_PROPS_REAL_USERS.BackendLead, BLOCK_PROPS_REAL_USERS.FK_BackendLead, BLOCK_PROPS_REAL_USERS.FK_CoreLead, BLOCK_PROPS_REAL_USERS.FK_SynthLead, Block_Name.Block_Name
            ORDER BY Block_Name) x) y
        WHERE
            y.rowID BETWEEN 1 AND 75

This works fine.

The DataSource defines the key field, Block_Name, like this:

<DataSource ID="Block_Instance" serverType="sql" tableName="Block_Instance" dropExtraFields="false">
    <fields>
        <field name="PK_Block_Instance" type="sequence" primaryKey="true" hidden="true" />
        <field name="FK_idProject" type="integer" foreignKey="Project.PK_Project" hidden="true" />
        <field name="Block_Name" type="text" includeFrom="Block_Name.Block_Name" canEdit="true" />
        <field name="Block_Name_PK_Block_Name" type="integer" foreignKey="Block_Name.PK_Block_Name" hidden="true" />
        <field name="Instance" title="Chip Instance Path" type="text" />
        <field name="FK_Owners" title="Owners" type="text" customSQL="true" />
etc....

Can you answer the addendum question?

Can you give me the URL reference to where I may again find the troubleshooting guide on 13.0 ? I can't find it in searches, or at least what I believe to be the correct reference.

[Isomorphic]

Aside: I know I have to suppress asm-8.0.1.jar in the build path to get 13.0 to run successfully, from prior email exchanges, but I can't find the documentation online with that troubleshooting tip for 13.x. Where can I find it? Tried Google and Forum searches for this information, in case I missed something else.

You may be referring to the SuperDevMode Toubleshooting help topic. Specifically, the last row of the troubleshooting table at the bottom of the page that starts:

An exception involving org.objectweb.asm.ClassVisitor near the top frame is thrown in Eclipse, and the project won't run.

Does the solution suggested in that table help?

[tece321]

You may be referring to the SuperDevMode Toubleshooting help topic. Specifically, the last row of the troubleshooting table at the bottom of the page that starts:

Does the solution suggested in that table help?

Thanks. This is the reference I search for, but was not sure I found the correct version.

I am able to build and run 13.0. In fact, excluding asm-8.0.1.jar, in build Path and in .classpath when using GWT 2.9.0 and Java 1.8 works. Whereas leaving it in the WEB-INF/lib causes a compile error / conflict at run time.

Now that I think of it, I have not run the reverse test, leaving asm-8.0.1.jar and removing the earlier versions of asm-5.0.4.jar and asm-3.1.jar and seeing if I can build/run. I will try that combination later.

None of these factors/ .jars affects the basic SQL problem of referring to the PK when it is not aggregated.

[Isomorphic]

Regarding the reported broken SQL issue - it is fixed and is available for download in nightly builds since Jan 25. You don't have to change anything in your code, extra columns are removed from the order clause. Let us know please how this worked for you.

[tece321]

Regarding the reported broken SQL issue - it is fixed and is available for download in nightly builds since Jan 25. You don't have to change anything in your code, extra columns are removed from the order clause. Let us know please how this worked for you.

Thanks I will take 13.0 out for a spin next week, with your suggested fix, and will let you know.

I encountered another deep rooted problem in our apps based on 13.0, but have yet to investigate it. Wanted to address this low and obvious hanging issue first, and hope it might solve the deeper issue.

[tece321]

I compiled version 13.0p 2022-02-09 and confirmed that the problem in this thread is resolved; there is not a problem with the autosorting in the generated SQL.

However as I noted in my last posting, we do seem to have a deeper problem, an incompatibility between view state of 12.1 and 13.0. When I run an app in 13.0, which will cause the view state to be updated ( selections, widget configs, tree expansion/collapse, etc.) , reading the same view state back into a 12.1 version of the app, causes the 12.1 app to hang in the middle of drawing, I assume while applying view state that was updated by the 13.1 version.

View state appears to continue to work in the 13.1 version, there are no hangs. But reading that view state map back into a 12.1 version of the app, causes that version to hang. There must be some key changes, or value changes causing problems back in 12.1. Suggestions?

There doesn't seem to be a problem moving from a 12.1 view state map to a 13.0 version. That appears to work. But before we release this change to our user base I think I will have to figure out the root cause of the failure within 12.1 versions.

Thanks

[tece321]

I take my last posting back.

Whereas there are no false SQL generation now happening, the sorting of items seems broken, as many repeated rows are being returned in ListGrids. I will have to investigate further and report back,

[Isomorphic]

Thanks for the report, and apologies for the delay in addressing the problem - we've made some changes to have 12.1 handle viewState from later versions without issue.

Please retest with a build dated February 16 or later.