LEFT OUTER JOIN and partitioned tables

[Wouter de Bie <wo...@spotify.com>]

Hi all,

I'm experiencing problems with using LEFT OUTER JOIN with partitioned tables. The following example works as expected:

SELECT
a.val1,
b.val2
FROM a
JOIN b ON
a.val1 = b.val1 AND
a.dt = 20110708
b.dt = 20110708

But when I change it to use a LEFT OUTER JOIN like:

SELECT
a.val1,
b.val2
FROM a
LEFT OUTER JOIN b ON
a.val1 = b.val1 AND
a.dt = 20110708 AND
b.dt = 20110708

On rows where there is a record in both a and b, I get something like

a.val1, NULL
a.val1, b.val2

I would expect that the first row (a.val1, NULL) would not be there.

Am I doing something wrong?

// Wouter 

Re: LEFT OUTER JOIN and partitioned tables

[Wouter de Bie <wo...@spotify.com>]

 Hi all,

I've solved the issue by setting hive.outerjoin.supports.filters=false. What does this setting do? It's completely undocumented. 
--Wouter de Bie
Developer Business Intelligence, Spotify
wouter@spotify.com
+46 72 018 0777

This e-mail (including any attachments) may contain information that is confidential and/or privileged. It is intended only for the recipient(s). If you have reason to believe that you are not the intended recipient of this e-mail, please contact the sender immediately and delete the e-mail from your computer.

On Friday, July 8, 2011 at 1:32 PM, Wouter de Bie wrote:

> Hi all,
> 
> I'm experiencing problems with using LEFT OUTER JOIN with partitioned tables. The following example works as expected:
> 
> SELECT
> a.val1,
> b.val2
> FROM a
> JOIN b ON
> a.val1 = b.val1 AND
> a.dt = 20110708
> b.dt = 20110708
> 
> But when I change it to use a LEFT OUTER JOIN like:
> 
> SELECT
> a.val1,
> b.val2
> FROM a
> LEFT OUTER JOIN b ON
> a.val1 = b.val1 AND
> a.dt = 20110708 AND
> b.dt = 20110708
> 
> On rows where there is a record in both a and b, I get something like
> 
> a.val1, NULL
> a.val1, b.val2
> 
> I would expect that the first row (a.val1, NULL) would not be there.
> 
> Am I doing something wrong?
> 
> // Wouter