Support Questions

Find answers, ask questions, and share your expertise
Celebrating as our community reaches 100,000 members! Thank you!

Same query, same data, different results between SQL Server and Impala


I am running the following query on the same data (same tables, and the same number of records on those tables), but it gives a different result on Impala than what I get on SQL Server.


The following on Impala:


and this is on SQL Server:



I am very sure that the data is the same in everything ... actually, I have imported the data from SQL Server through Sqoop and after that made sure that the number of records is the same in the source and destination .... yet I don't know why I'm getting a defferent result here and there ?!!!



Master Collaborator

Hi @anis447 


Can you bring us this queries results in SQL server and Impala:

Select avg(tagno) from tag;
Select avg(tagno) from has_tag;
Select count(*) from tag where tagno is null;
Select count(*) from has_tag where tagno is null;

Also try to add this on Impala query, and let us know if there is any change:

Inner join has_tags hit on (s.tagno = hit.tagno and s.categorycode = hit.categorycode)

Good luck.


It'd be helpful to post your impala version too.


It seems unlikely that either SQL engine would return incorrect results on a straightforward query like this.


I'd suggest looking at a subset of the data and breaking down the query until you can see where the different lies, e.g.


select *
FROM tag s
  INNER JOIN  has_tags ht on S.TagNo = HT.TagNo and S.CategoryCode =  HT.CategoryCode
WHERE ht.categorycode = 'SYS'


The output of "explain <query>" is often helpful too.