setSortBy on includedFrom field gives warning and lacks table name

[claudiobosticco]

SmartClient Version: v13.0p_2023-08-15/Enterprise Development Only (built 2023-08-15)

Oracle 19c

Hello, if I add a dsRequest.setSortBy on a field which is includedFrom, I see this WARN:

WARN SQLOrderClause Building order by clause, could not locate the correct index for includeFrom field ROLE (AUTH_ROLES.ROLE)

and the fieldName in the "order by" lacks the table name.

Could you please explain what is causing the warning and if it's the reason for not having the table name before the column name? Is it expected?

The field is defined as:

 <field name="ROLE" type="text" includeFrom="AUTH_ROLES.ROLE" includeVia="ID_ROLE_FK"/>

and the foreign key field:

 <field name="ID_ROLE_FK" type="integer" foreignKey="AUTH_ROLES.ID_REC" relatedTableAlias="ROLES" required="true"/>

please let me know if you need more details.

[claudiobosticco]

Hello, I would appreciate any comments or feedback. :)

[Isomorphic]

Apologies for the delay on this one - it's assigned and we'll update here when we have more information.

[Isomorphic]

This one is an example of unpredictable behavior we've mentioned in other thread of yours, because of the setup. Once the duplicate includeFrom is removed, there will be no warning.

[claudiobosticco]

Hello, after your explanation in the other thread, I've changed my dataSource so that I've got only one

includeFrom="AUTH_ROLES.ROLE"

and the other field has a customSelectExpression.

This way, everything works also with 13.1, so far so good.

But I still see the warning

WARN SQLOrderClause Building order by clause, could not locate the correct index for includeFrom field ROLE (AUTH_ROLES.ROLE)

and actually even if I comment out the field with the customSelectExpression, I still see the warning.

Do you need an actual test case?

[Isomorphic]

No need for the test case, thank you. It's fixed now and will be available in the 2023-09-22 nightly build (tomorrow).

[claudiobosticco]

SmartClient Version: v13.0p_2023-09-22/Enterprise Deployment (built 2023-09-22)

actually I'm still seeing the WARN message.


besides that, you didn't comment about this:

[cut] and the fieldName in the "order by" lacks the table name.

Could you please explain what is causing the warning and if it's the reason for not having the table name before the column name? Is it expected?

I mean, I don't remember if it's always been like that, but why not use the table/alias name?

[Isomorphic]

The warning was fixed only in version 13.1, we've missed the fact that this needs to be ported to 13.0 as well. Apologies, it is fixed now and will be available for download in 13.0 nightly builds since Sep 27 (tomorrow).

Regarding using tableName.columnName vs fieldName in the order by clause - we use both depending on the circumstances. There are situations when one would work and other wouldn't, considering the concrete structure of the SQL query we generate.

[claudiobosticco]

Regarding using tableName.columnName vs fieldName in the order by clause - we use both depending on the circumstances. There are situations when one would work and other wouldn't, considering the concrete structure of the SQL query we generate.

my question was relative to the situation where the order by is added in a DMI using the following code:

dsRequest.setSortBy("fieldName")

In this scenario, when 'fieldName' is an 'includeFrom' field, it is mentioned in the select clause of the generated SQL as:

tableAlias.columName

However, in the generated SQL for the 'order by' clause, it results in:

order by fieldName

This can either work or not, depending on the context.

If I attempt to use:

dsRequest.setSortBy("tableAlias.columName")

I receive a warning:

WARN DSRequest In sortBy, definition 'ROLES.ROLE' refers to a field name ('ROLE') that is already declared on this dataSource ('AUTH_USERS_ROLES'). Ignoring this sortBy entry.

So, it seems there is a limitation when compared to adding an orderClause in the operationBinding. Is this limitation expected?

[Isomorphic]

You're right, Smartclient SQL query generation some uses both approaches depending on the context, as was mentioned before:

Regarding using tableName.columnName vs fieldName in the order by clause - we use both depending on the circumstances. There are situations when one would work and other wouldn't, considering the concrete structure of the SQL query we generate.

As for the setSortBy() API usage, it is documented to use field names, which "tableAlias.columnName" is not.

[claudiobosticco]

SmartClient Version: SNAPSHOT_v13.1d_2024-04-04/Enterprise Deployment (built 2024-04-04)

as a maybe related question, I just found that I've got a dataSource where (at least for an operationBinding) the sortBy from the client dsRequest is ignored for an includeFrom field.

If I modify the field to use a customSelectExpression (as the join is generated because of another included field), then the sortBy is correctly used in the ORDER BY clause.

Is it expected? If not, do you need a test case? In case, I'll start a new thread

[Isomorphic]

We're guessing you set either operationBinding.outputs or dsRequest.outputs. When "outputs" is set, joins are skipped if they don't appear to be necessary because none of the fields that are produced by the join are in the outputs.

Possibly the field you are attempting to sort on wasn't in the outputs. This would generally be a bad idea because the client won't be able to do the same sort without values for that field.

[claudiobosticco]

I recall that joins are skipped in that case, but my field *is* in outputs, and in fact it's in the select. It's also in a <groupBy>, does this mean something?

[Isomorphic]

Unfortunately, it does mean something - it means we're almost certainly going to need a test case :(

What we're guessing here is that the logic for skipping joins based on outputs isn't understanding that this join is still going to happen due to another included field doing the join, perhaps indirectly? But that doesn't actually make sense with how the logic is built, so yes, it seems like we need a test case.