Not sorted entries in "fetch" operation binding with orderClause

[Blama]

Hi Isomorphic,

I just noted following error:

See this ds.xml:

<DataSource schema="dbo" dbName="SQLServer" tableName="users"
	ID="users" dataSourceVersion="1"
	serverType="sql">
	<fields>
		<field primaryKey="true" name="id" title="ID" type="sequence"
			hidden="true"></field>
		<field name="givenname" title="Vorname" length="50" type="text"
			required="true"></field>
		<field name="lastname" title="Nachname" length="50" type="text"
			required="true"></field>
		<field name="gender" title="M/W" length="1" type="text"
			required="true">
			<valueMap>
				<value>M</value>
				<value>F</value>
			</valueMap>
		</field>
		<field name="email" title="eMail" length="50" type="text">
			<validators>
				<validator type="regexp" expression=".+@.+\.[a-z]+"
					errorMessage="Dies ist keine gueltige eMail-Adresse." />
				<validator type="doesntContain" substring=" "
					errorMessage="Die eMail-Adresse darf kein Leerzeichen enthalten." />
			</validators>
		</field>
	</fields>
	<operationBindings>
		<operationBinding operationType="fetch">
			<orderClause>users.lastname ASC</orderClause>
		</operationBinding>
	</operationBindings>
</DataSource>

The intention is that the data within this DS is already sorted when the data is received by the client.

The DS is bound to a ListGrid with setDataSource(DS).
The SQL executed (c&p from server log) is:

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY x.id) AS rowID FROM (SELECT TOP 100 PERCENT  users.id, users.email, users.givenname, users.lastname, users.gender FROM dbo.users WHERE ('1'='1') ORDER BY users.lastname ASC) x) y WHERE y.rowID BETWEEN 1 AND 75

As you can see, because of auto-paging/lazy load the "users.lastname ASC" from the <orderClause> is not applied to the outermost SELECT.
From an SQL Server point of view it is OK to deliver the rows in an unordered (even nondeterministic) resultset, then. (see common questions on views with order by clause not working as expected)

Of course I can to a clientside sort, but this should nevertheless be treated as a bug. What do you think?

Thanks,
Blama

[Isomorphic]

Are you seeing unordered results only with your custom orderClause? Because you don't need to customize the SQL to specify a sort - dsRequest.sortBy is how you would ordinarily do this.

[Blama]

Hi Isomorphic,

thanks for the hint on this topic. I removed my <operationBinding operationType="fetch"> and noticed interesting behavior after some code changes.

This sorts the entries as intended:

		boundList.setDataSource(ds);
		SortSpecifier sortspec = new SortSpecifier("lastname", SortDirection.ASCENDING);
		boundList.addSort(sortspec);

This doesn't:

		SortSpecifier sortspec = new SortSpecifier("lastname", SortDirection.ASCENDING);
		boundList.addSort(sortspec);
		boundList.setDataSource(ds);

Most likley this is expected behavior as a setDataSource reverts back to the plain .ds.xml file.

Without <operationBinding operationType="fetch"> the SQL does not include an ORDER BY clause. So the ordering must be done by the server (or the client?). For me this is OK, but I don't know if you want to treat the SQL generation as bug nevertheless.

I do not know how to use dsRequest.sortBy. Is this called by addSort(SortSpecifier)?

Thanks,
Blama

[Isomorphic]

When server sorting is needed (partially loaded large dataset), clicking on a ListGrid header or in some other way using the UI to specify the sort direction automatically results in a DSRequest being sent with sortBy specified.

[Blama]

So small dataset sorting takes place on the client and large dataset sorting on the server? Impressive...

Really looking forward to build a large app with SmartGWT.

[Isomorphic]

Yes, same thing with filtering, which has an even larger performance impact (see the Adaptive Filtering example).