Weird issue while querying Salesforce through Teiid embedded

[pranavk]

Hi,

I was using the Teiid sample code to test out certain queries through Teiid embedded when I noticed that changing the order of FROM part of a working query gave me an error.

This gave me an error:

SELECT Account.Phone, Account.Name, Account.Type, Contact.LastName FROM Account, Contact WHERE Account.Id = Contact.AccountId

Whereas the following ran fine:

SELECT Account.Phone, Account.Name, Account.Type, Contact.LastName FROM Contact, Account WHERE Contact.AccountId = Account.Id

I am looking into what could be the issue. Meanwhile does anybody have any pointer to what could be the issue?

Thanks,

Pranav

[rareddy]

Can you post what is the error you are seeing?

[shawkins]

The engine is compensating for the SF translator by turning the query effectively into a left outer join to mimic relationship query semantics.  However the compensation adds a predicate that apparently the translator cannot handle if it processes the query as a parent-to-child join, but works just fine as a child-to-parent.  Can you log an issue for this?

[shawkins]

Can you convert this into an issue?

Effectively an inner join on a parent-to-child relationship cannot be compensated for in the query itself - entering a query directly such as "SELECT Account.Phone, Account.Name, Account.Type, Contact.LastName FROM Account left outer join Contact on Account.Id = Contact.AccountId where Contact.id is not null"  will result in a failure as the parent-to-child join will not allow a predicate effectively after the join.  This would need to be handled in the engine or differently at the translator.

[pranavk]

Thanks for the helpful response. I have logged an issue -> TEIID-3128

[shawkins]

The fix is in master, which will part of Teiid 8.9 Beta2.

[pranavk]

Thanks. There's another issue I noticed with respect to exception handling. For the case in which a user supplies wrong user credentials to the login method of SalesforceConnectionImpl, he would expect to be notified through some exception returned by the login method, regarding what exactly the issue was (i.e the original exception that Salesforce throws).

The only way I am able to handle this is by catching a Throwable/Error in my application; and in this case the original exception message gets lost due to which I cant figure out whether login failed or server has stopped responding. Is there any way I can get the real exception?

Pranav

[shawkins]

I'm not sure I see that behavior.  At least on latest.

I see the top level as: org.teiid.jdbc.TeiidSQLException: TEIID30504 salesforce: TEIID60000 javax.resource.ResourceException: com.sforce.soap.partner.LoginFault: INVALID_LOGIN: Invalid username, password, security token; or user locked out.

Or over a socket as: org.teiid.jdbc.TeiidSQLException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 salesforce: TEIID60000 javax.resource.ResourceException: com.sforce.soap.partner.LoginFault: INVALID_LOGIN: Invalid username, password, security token; or user locked out.

Did you check the exception chain?

[pranavk]

I created a basic sample app to test the connector stand-alone and it was giving me the exception as you pointed out. I'm checking at my end to see what's causing this to happen in my project environment. Thanks again!