sort on includeFrom field

[ssequeira]

smartGWT 5.0-p20150323

Sort not working for includeFrom field of SQL datasource.
The query generated is wrong, as it adds an alias for the sort field defined with the includeFrom property.

<DataSource ID="dsA" serverType="sql" dbName="db" tableName="table_a" >
  <fields>
    <field name="field_1_a" type="text" nativeName="field_1_a" />
    <field name="field_from_other_ds" type="text" includeFrom="dsB.field_1_b" />
  </fields>
</DataSource>

<DataSource ID="dsB" serverType="sql" dbName="db" tableName="table_b" >
  <fields>
    <field name="field_1_b" type="text" nativeName="field_1_b" />
  </fields>
</DataSource>

query generated

SELECT *
  FROM (SELECT a.*, rownum myrownum
          FROM (SELECT table_a.field_1_a          AS field_1_a,
		               table_b.field_1_b          AS field_from_other_ds
                  FROM table_a,
                       table_b,
                 WHERE (...)
                   AND ROWNUM <= 5000
                 ORDER BY table_b.field_1_b AS field_1_b) a
         where rownum <= 75)
 WHERE myrownum >= 1

[Isomorphic]

You seem to be complaining about the " AS field_1_b" in the order clause. It's redundant before you consider SQL templating, but potentially not redundant if SQL templating is being used.

Why is it an issue for you?

[Blama]

Hi Isomorphic,

I think the OP is complaining about invalid SQL. It seems to be Oracle with the rownum column (I might be wrong here), but without JDBC windowing.

WITH d AS (SELECT 1 AS ID, 'abc' AS shortname FROM DUAL)
SELECT d2.shortname AS fieldname
FROM d d1, d d2
WHERE d1.id = d2.id
ORDER BY d2.shortname AS fieldname;

Result:
ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:
Error at Line: 5 Column: 23

Best regards,
Blama

[ssequeira]

Yes, I have problem with "AS field_1_b"
It is an issue for me because it does not work...
Running the generated query on oracle throws an ORA-00907 [right parenthesis missing].
And yes, SQL templating is being used.

[Isomorphic]

Thanks for clarifying. This has been assigned for investigation.

[ssequeira]

waiting for a fix.

[Isomorphic]

it's being worked on, thank you for your patience.

[Isomorphic]

Hi, this issue appears to be not so obvious to reproduce. With these data sources you provided, sql query for Oracle with paging works correctly, i.e. alias "field_from_other_ds" is used in order by clause for field "table_b.field_1_b":

SELECT * FROM 
	(SELECT a.*, rownum myrownum FROM 
		(SELECT (...) table_b.field_1_b AS field_from_other_ds
		FROM table_a (...) 
  		 ORDER BY field_from_other_ds) a 
  	where rownum <=4) 
WHERE myrownum >= 1

Are you sure all your libs are from smartGWT 5.0-p20150323?
Could you please provide complete setup when such query is generated? I mean
a) complete DataSource definitions
b) how dsRequest looks like (javascript code that calls fetch or dsRequest payload from log)

You may use "dsA" and "dsB" example data sources, just make sure you reproduce the error locally with exactly same data sources you provide here.

[ssequeira]

I think I found your problem.

<operationBinding operationType="fetch" sqlUsePagingHint="false" serverMethod="fetch" skipRowCount="true" outputs="field_from_other_ds">

I reproduced this with very simple datasources (see atached). And I was able to fix the error when removing the outputs from the myTest datasource.

[Isomorphic]

This is fixed now, you may download next nightly build (2015-04-24) and try it out. Let us know if you still have this issue.

[ssequeira]

I would, if I could. The last available build is from the 23rd...

[Isomorphic]

Build from the 23rd does not have the fix you need. Please wait for the next one.