4.1 Power SQL datasource and nativeName

[rolf.woll]

Hi,

We are trying to upgrade to 4.1 Power edition build 03.30.2014.
After upgrade we get an SQL error when fetching data from some of our datasources.

We are using SQL server, and have datasources that join multiple tables using tableClause and groupClause. Since some of the joined in tables have fields with the same name, we use tableName and nativeName in the field declarations to identify the various fields:

		<field name="deleted_url" type="link" title="Link to cause for deletion" tableName="deleted_reference" nativeName="url" />
                <field name="approval_url" type="link" title="Link to approval document" tableName="archive_reference" nativeName="url" />

field deleted_url points to the deleted_reference.url and field approval_url points to table archive_reference.url.

In 4.0 SmartGwt generates a query like this:

SELECT COUNT(*) FROM (SELECT archive_reference.url AS approval_url, deleted_reference.url AS deleted_url, [various other fields removed] FROM ...

This query works fine.

However for 4.1 the query is:

SELECT COUNT(*) FROM (SELECT distinct archive_reference.url, deleted_reference.url,[various other fields removed] FROM ...

which gives the following SQL error:
The column 'url' was specified multiple times for 'work'

Any ideas?
Regards
Rolf

[Isomorphic]

The fact that there's a "select distinct" in the second query suggests that you've got a customized query, but you haven't shared any details of that.

We'll need everything that's necessary to reproduce the problem - ideally, a runnable test case.

[rolf.woll]

Here is a simplified version of datasource in question which produces the given error. We´re using tableClause and groupClause to join in related tables. (The groupClause is needed since I have an aggregated field in the original datasource)

<DataSource 
	schema="dbo" 
	dbName="MSSQL" 
	tableName="lor_contractor_approvals"
	ID="contractor" 
	dataSourceVersion="1" 
	serverType="sql" 
	title="Entreprenør"
	pluralTitle="Entreprenører"
	>
	<fields>
		<field name="id" type="sequence" primaryKey="true" hidden="true" />
		<field name="organization_id" title="Orgnr" type="integer" hidden="true">
		</field>
		<field name="approval_url" type="link" title="Godkj. i 360" tableName="archive_reference" nativeName="url" />
		<field name="approval_reference" hidden="true" type="text" title="Godkj. i 360" tableName="archive_reference" nativeName="reference" />
		<field name="approval_archive_reference_id" nativeName="approval_archive_reference_id" hidden="true" type="integer" />
		<field name="deleted_from" type="date" title="Slettedato" detail="true" />
		<field name="deleted_archive_reference_id" hidden="true" type="integer"></field>
		<field name="deleted_reference" type="text" hidden="true" tableName="deleted_reference" nativeName="reference" />
		<field name="deleted_url" type="link" title="Slettevedtak i 360" tableName="deleted_reference" nativeName="url" />
	</fields>
	<operationBindings>
		<OperationBinding operationType="fetch">
			<tableClause><![CDATA[
     lor_contractor_approvals
     left outer join archive_reference on archive_reference.id = lor_contractor_approvals.approval_archive_reference_id
     left outer join archive_reference deleted_reference on deleted_reference.id = lor_contractor_approvals.deleted_archive_reference_id
     ]]>
			</tableClause>
			<groupClause><![CDATA[lor_contractor_approvals.organization_id, 
								  lor_contractor_approvals.deleted_archive_reference_id, 
			                      lor_contractor_approvals.approval_archive_reference_id, 
			                      lor_contractor_approvals.deleted_from, 
			                      lor_contractor_approvals.id, 
			                      archive_reference.url,
			                      archive_reference.reference,
			                      deleted_reference.url,
			                      deleted_reference.reference
     ]]>
			</groupClause>
		</OperationBinding>
	</operationBindings>
</DataSource>

Generated SQL in 4.0. Notice, no distinct here. Also, fields are correctly given aliases

SELECT COUNT(*) FROM (SELECT lor_contractor_approvals.approval_archive_reference_id, archive_reference.reference AS approval_reference, archive_reference.url AS approval_url, lor_contractor_approvals.deleted_archive_reference_id, lor_contractor_approvals.deleted_from, deleted_reference.reference AS deleted_reference, deleted_reference.url AS deleted_url, lor_contractor_approvals.id, lor_contractor_approvals.organization_id FROM 
     lor_contractor_approvals
     left outer join archive_reference on archive_reference.id = lor_contractor_approvals.approval_archive_reference_id
     left outer join archive_reference deleted_reference on deleted_reference.id = lor_contractor_approvals.deleted_archive_reference_id
     
			 WHERE (lor_contractor_approvals.deleted_from IS NULL) GROUP BY lor_contractor_approvals.organization_id, 
								  lor_contractor_approvals.deleted_archive_reference_id, 
			                      lor_contractor_approvals.approval_archive_reference_id, 
			                      lor_contractor_approvals.deleted_from, 
			                      lor_contractor_approvals.id, 
			                      archive_reference.url,
			                      archive_reference.reference,
			                      deleted_reference.url,
			                      deleted_reference.reference
     
			) work

Generated SQL in 4.1. Notice distinct operator. Also, aliases for fields are not added, thus creating the SQL error The column 'url' was specified multiple times for 'work'. Also, the group by section is missing.

SELECT COUNT(*) FROM (SELECT distinct lor_contractor_approvals.organization_id, 
								  lor_contractor_approvals.deleted_archive_reference_id, 
			                      lor_contractor_approvals.approval_archive_reference_id, 
			                      lor_contractor_approvals.deleted_from, 
			                      lor_contractor_approvals.id, 
			                      archive_reference.url,
			                      archive_reference.reference,
			                      deleted_reference.url,
			                      deleted_reference.reference
     
			 FROM 
     lor_contractor_approvals
     left outer join archive_reference on archive_reference.id = lor_contractor_approvals.approval_archive_reference_id
     left outer join archive_reference deleted_reference on deleted_reference.id = lor_contractor_approvals.deleted_archive_reference_id
     
			 WHERE (lor_contractor_approvals.deleted_from IS NULL) ) work

[Isomorphic]

This is fixed now, you can find the fix in the next nightly build (2014-04-05).

[rolf.woll]

Great.

I have downloaded the last nightly build and the problem seems to be gone now.

Thanks a lot :-)
Regards
Rolf

[rolf.woll]

Hi,

this error has resurfaced for 4.1 build 2014-06-06.

The previous build we used, 2014-05-26 did not have this error.

Regards
Rolf

[Isomorphic]

Hi, are you sure it's the same error? This is working for us properly in latest 4.1. build.

Could you please describe your current issue in detail?

[rolf.woll]

Hi,

I believe so. I have the exact same symptoms as described in my earlier post:

- Group by clause is missing, fields aliases are not added and I get a distinct operator in the select count.

Note that the previous build we used was 2014-04-26 and not 2014-05-26. I´ve also tested 2014-05-11 which also works.

I´ve tested 2014-05-31, this has the same error.

So, something has broken between 2014-05-11 and 2014-05-31

Other queries without a custom groupBy clause seem to be working though.

Regards
Rolf

[Isomorphic]

We did some additional fixes. You may download latest (2014-06-16) nightly build and try it out. It would be great if you could let us know how it will work for you.

[rolf.woll]

Yes.

Of course. However 06-16 is not available for download yet(06-14 is the latest). I´ll download and try it out as soon as build 06-16 or later is available.

Thanks a lot
Rolf

[rolf.woll]

Hi,

I have now tested build 2014-06-17, and it is working again now :-)

Thanks a lot