LEFT JOIN over proxy tables on MS SQL Server fails with wrong syntax

[VolkerBarth]

That's a follow-up to this older and this current FAQ:

I'm basically trying to run queries over a few proxy tables declared in a 12.0.1.3817 database and located in a MS SQL 2008 R2 server. These queries are not very complex, i.e. mainly using joins over 3-5 proxy tables.

However, as soon as they do use an INNER JOIN and a LEFT JOIN, this doesn't work at all.

I seem to have either the choice (by setting the capability 'Unrestricted ANSI ON' OFF or ON):

1. to have them run in "NO PASSTHRU mode" (what will usually fail with a MS SQL error: "Connection is busy with results for another command") or
2. to have them run in "FULL PASSTHRU mode" (as desired).

However, the second approach at leads to the following incorrect syntax (the comma in the table list is rejected):

SELECT ...  
FROM (pt1, pt2) LEFT OUTER JOIN pt3 ON pt1.pk = pt3.pk  
WHERE pt1.pk = pt2.pk AND ...

Note, the original query does use an INNER JOIN between pt1 and pt2 here - which would work on MS SQL, too:

SELECT ...
FROM pt1 INNER JOIN pt2 ON pt1.pk = pt2.pk LEFT OUTER JOIN pt3 ON pt1.pk = pt3.pk
WHERE ...

So how can I force the remote query not to replace the INNER JOIN with a table list and a WHERE CLAUSE?

Note: If the query does only include INNER JOINs or LEFT JOINs, then they can be executed successfully, and then the inner join syntax is sent as such to the remote server...

---

Current setting of (possibly) relevant JOIN capabilities:

select * from syscapabilities where srvid = 1 and capid between 25 and 39

capid,srvid,capname,capvalue

25,1,Joins,T
26,1,Outer joins,T
27,1,Full outer joins,T -- set to ON (default is OFF), no obvious effect 28,1,Multiple outer joins,T
29,1,Logical operators in outer join,T
30,1,Outer joins mixed with normal joins,T
31,1,ANSI join syntax,T
32,1,TSQL join syntax,F
33,1,ODBC outer join syntax,T -- set to ON (default is OFF), no obvious effect
34,1,Unrestricted ANSI ON,T -- set to ON (default is OFF), if OFF, then the query is executed locally...

[Former Member]

I have to think about this one for a bit and will try and post a follow-up response once I have a better idea as to what is going on. BUT, as a quick stab in the dark could you try turning off "ANSI join syntax" and "ODBC outer join syntax" and instead turn on "TSQL join syntax". See if that gets you any further.

[VolkerBarth]

Hm, I tested different combinations of these caps, however, with no further success:

Basically, if one of the caps 'ANSI join syntax' and 'Unrestricted ANSI ON' is set to OFF, then OMNI claims "The capability check of 31/34 failed" and runs the query in no passthrough mode. Whether 'ODBC outer join syntax' or 'TSQL join syntax' are set or not, doesn't seem to matter here - the query is just split into pieces.

Additionally, if both ANSI join settings are set to ON, then the other join caps don't seem to make a difference, either...

[thomas_duemesnil]

As I'm always having compatibility issues with joins my approach is to create a view in the source database and then setup a proxy table to this view. This is logically only an option if you are allowed to modify the source Database.

[VolkerBarth]

Yes, a remote view is what I'm commonly using for classical "data import features", too - i.e. to use a remote view to define the data I'm needing and then to import them via a proxy table refering to that view and to store the data locally. Then, the further data access is locally and does not suffer from any proxy rewrite rules...

Here, in contrast, I'm trying to make an existing application run with remote data instead of local data - and therefore I'm trying to mask the underlying differences as good as possible. Since the local data is queried by hundreds of normal (and rather simple) queries, I'm sticking to use that - switching to using views instead would simply be a waste of time and a maintenance nightmare...

[Former Member · Accepted solution]

Looks like there is no way around this one. You will probably have to force no passthru for now or use sp_forward_to_remote_server() if you can. I will open a bug report and see if we can do a better job of building remote queries containing inner joins.

[VolkerBarth · Accepted solution]

Karim, thanks for the clarification. Using sp_forward_to_remote_server() is not a solution here as the names and structures of remote and local tables do not match, and my goal is to run the existing application without having to adapt the database layer - see my comment in Thomas's suggestion.

[VolkerBarth · Accepted solution]

Just another idea as a work around: I may be able to modify those queries to consist of left joins only - and then to add conditions in the where clauses for the "fake" null-supplying sides to turn them into actual inner joins, something like

SELECT ...
FROM pt1 LEFT JOIN pt2 ON pt1.pk = pt2.pk
  LEFT OUTER JOIN pt3 ON pt1.pk = pt3.pk
WHERE pt2.pk is not null AND ...

I may try this approach, simply as running in no passthrough mode is rather bad for performance reasons...