[teiid-issues] [JBoss JIRA] Created: (TEIID-1358) Getting wrong results when using JOINs in a query

[Wanja Pernath (JIRA)]

Getting wrong results when using JOINs in a query
-------------------------------------------------

                 Key: TEIID-1358
                 URL: https://jira.jboss.org/browse/TEIID-1358
             Project: Teiid
          Issue Type: Bug
          Components: Salesforce Connector
    Affects Versions: 7.1
         Environment: Fedora 12 64bit, Java 1.6.0_21 64bit
Teiid-7.1 is running on a fresh EAP 5.1 instance with 1.5GB of RAM.

            Reporter: Wanja Pernath
            Assignee: Steven Hawkins


I am getting completely wrong results if I am using a JOIN in my SELECT statement:

select o.Id,
	   o.Name, 
	   o.Amount, 
	   o.StageName, 
	   o.ForecastCategory
from 	general.salesforce.Opportunity o
where 1=1
and   o.Fiscal = '2011 3'
and   o.OwnerId = (select Id from general.salesforce.User where Name = '****') --> real name
;

This statement returns 36 lines. I would now like to also get OpportunityLineItem(s) to this Opportunities, so I am adding a join

select o.Id,
	   (select Name from general.salesforce.User where id = o.OwnerId) as OpportunityOwner,
	   (select Name from general.salesforce.Account where id = o.AccountId) as AccountName,
	   o.Name, 
	   o.Amount, 
	   o.StageName, 
	   o.ForecastCategory
from general.salesforce.OpportunityLineItem oli
LEFT INNER general.salesforce.Opportunity o ON oli.OpportunityId = o.id
where 1=1
and   o.Fiscal = '2011 3'
and   o.OwnerId = (select Id from general.salesforce.User where Name = '****') --> same name as above
;

Now I do not get any results back.

However, one may think that there is no OpportunityLineItem for those Opportunities above. But if I am querying just OpportunityLineItems of one Opp above, I do get many of them:

select * from general.salesforce.OpportunityLineItem oli where OpportunityId = '0066000000DoIHaAAN';


Maybe I am missing something here?


Thanks for any help. 


Cheers,
  Wanja



-- 
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira