Minor bug with generated SQL for ORDER BY on included column

[Blama]

Hi Isomorpic,

please see the following example (builtInDS-based, 4.1-p20140822),
where the generated SQL does only include the tableName for the normal(=not-included) field.

Changes to animals.ds.xml (1 added, 1 modified):

<field name="incName" includeFrom="employees.Name" />
<field foreignKey="employees.EmployeeId" name="lifeSpan" title="Life Span" type="integer" />

Changes to BuiltInDS.bindComponents():

boundList.setDataSource(ds);
boundList.setSort(new SortSpecifier[] { new SortSpecifier("incName", SortDirection.ASCENDING),
	new SortSpecifier("lifeSpan", SortDirection.ASCENDING) });

Also set boundList.setProgressiveLoading(true); so that the real SELECT-SQL is executed even if the JOIN results in an empty resultset.

The resulting SQL is:

SELECT LIMIT 0 76  animals.userOrder, animals.Name, animals.ReportsTo, animals.scientificName, animals.lifeSpan, animals.status, animals.diet, animals.information, animals.picture, employeeTable.Name AS incName FROM animals, employeeTable WHERE ('1'='1') AND animals.lifeSpan = employeeTable.EmployeeId ORDER BY [B]incName[/B], animals.lifeSpan

I think it should be employeeTable.Name (or the relatedTableAlias, if given). This happens with HSQLDB as well as with Oracle.

Best regards,
Blama

[Isomorphic]

The column is given an alias:

employeeTable.Name AS incName

.. we don't understand why you would insist on aliases not being used.

[Blama]

Hi Isomorphic,

I'm talking about the generated ORDER BY-clause.

Best regards,
Blama

[Isomorphic]

Yes, the generated ORDER by clause uses the "incName" alias established earlier in the query by the SQL phrase we just quoted.

So where is the problem?

[Blama]

Hi Isomorphic,

I'm sorry, you are right. I thought this might lead to "ORA-00960: ambiguous column naming in select list", but it does not.
I made a SQLFiddle to test, but it seems that I can only produce an error with an setup that would be invalid in SmartGWT (same fieldname twice).

Sorry for not checking earlier.

Best regards,
Blama

PS: Oracle SQL, In case the fiddle link does not work:

[B]--works (which I did not think it would)[/B]
with
a as
(select 4711 as id, 'myorder' as myname, 'foobar' as foobar, 1 as b_id from dual),
b as
(select 1 as id, 'INPROGRESS' as name from dual)
 SELECT a.id, /* a.myname, */ a.foobar, a.b_id, b.name as myname from
 a inner join b on a.b_id = b.id
 ORDER BY myname;

[B]--works, but would have the same name="..." twice in .ds.xml - disallowed[/B]
with
a as
(select 4711 as id, 'myorder' as myname, 'foobar' as foobar, 1 as b_id from dual),
b as
(select 1 as id, 'INPROGRESS' as name from dual)
 SELECT a.id, a.myname, a.foobar, a.b_id, b.name as myname from
 a inner join b on a.b_id = b.id
 ORDER BY b.name;

[B]--does not work, but would have the same name="..." twice in .ds.xml - disallowed
[/B]with
a as
(select 4711 as id, 'myorder' as myname, 'foobar' as foobar, 1 as b_id from dual),
b as
(select 1 as id, 'INPROGRESS' as name from dual)
 SELECT a.id, a.myname, a.foobar, a.b_id, b.name as myname from
 a inner join b on a.b_id = b.id
 ORDER BY myname;