You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Josh Ferguson <jo...@besquared.net> on 2009/02/17 07:04:14 UTC
Yet another join issue
Ok so this one I just ran into. It appears when doing a join of two
tables that, when using a LEFT OUTER JOIN and specifying a WHERE
condition on both tables, the resulting join is an INNER JOIN. Example
follows:
LEFT OUTER JOIN with WHERE condition on the LEFT table
SELECT communications.sender_id, conversions.actor_id,
communications.version, conversions.reference_id FROM communications
LEFT OUTER JOIN conversions ON communications.reference_id =
conversions.reference_id WHERE communications.hour >= 343013 AND
communications.hour < 343014
1 NULL A NULL
1 2 A 2
2 NULL A NULL
3 NULL B NULL
correct results, as expected
LEFT OUTER JOIN with WHERE conditions on both tables
SELECT communications.sender_id, conversions.actor_id,
communications.version, conversions.reference_id FROM communications
LEFT OUTER JOIN conversions ON communications.reference_id =
conversions.reference_id WHERE communications.hour >= 343013 AND
communications.hour < 343014 AND conversions.application = 'reference'
1 2 A 2
incorrect results, it simply displays the row that was matched in both
tables.
Any ideas what's going on here? Is this intended? How can I get a LEFT
OUTER JOIN and put conditions on both tables?
Josh Ferguson
Re: Yet another join issue
Posted by Josh Ferguson <jo...@besquared.net>.
I can't but if you do something similar in mysql you'll get what I
expected were the correct results. Not that Mysql is the "standard" by
any means, it was just something I expected to work like that. My
intuition about this is that it would apply the conditions first to
both tables and then perform the join, which would give the results I
was expecting.
Josh Ferguson
On Feb 16, 2009, at 10:28 PM, Zheng Shao wrote:
> The following will also work. In the case of left outer join, the
> value of columns from the missing table will be NULL, so checking
> whether the value IS NULL should solve the problem.
>
> SELECT communications.sender_id, conversions.actor_id,
> communications.version, conversions.reference_id FROM communications
> LEFT OUTER JOIN conversions ON communications.reference_id =
> conversions.reference_id WHERE communications.hour >= 343013 AND
> communications.hour < 343014 AND ( conversions.application =
> 'reference' OR conversions.application IS NULL);
>
> I am not sure whether SQL standard says something different from
> what Hive implements. It will be great if you could point us to some
> references.
>
>
>
>
> Zheng
>
> On Mon, Feb 16, 2009 at 10:21 PM, Josh Ferguson <jo...@besquared.net>
> wrote:
> Will this work if this select statement is already nested in a FROM?
>
> Josh Ferguson
>
> On Feb 16, 2009, at 10:14 PM, Prasad Chakka wrote:
>
>> Try below,
>>
>> SELECT a.sender_id,
>> b.actor_id,
>> a.version,
>> b.reference_id
>> FROM communications a
>> LEFT OUTER JOIN (select conversions.actor_id,
>> conversions.reference_id from conversions where
>> conversions.application='reference') b
>> ON a.reference_id = b.reference_id
>> WHERE a.hour >= 343013
>> AND a.hour < 343014
>>
>>
>> From: Josh Ferguson <jo...@besquared.net>
>> Reply-To: <hi...@hadoop.apache.org>
>> Date: Mon, 16 Feb 2009 22:04:14 -0800
>> To: <hi...@hadoop.apache.org>
>> Subject: Yet another join issue
>>
>> Ok so this one I just ran into. It appears when doing a join of two
>> tables that, when using a LEFT OUTER JOIN and specifying a WHERE
>> condition on both tables, the resulting join is an INNER JOIN.
>> Example
>> follows:
>>
>> LEFT OUTER JOIN with WHERE condition on the LEFT table
>>
>> SELECT communications.sender_id, conversions.actor_id,
>> communications.version, conversions.reference_id FROM communications
>> LEFT OUTER JOIN conversions ON communications.reference_id =
>> conversions.reference_id WHERE communications.hour >= 343013 AND
>> communications.hour < 343014
>>
>> 1 NULL A NULL
>> 1 2 A 2
>> 2 NULL A NULL
>> 3 NULL B NULL
>>
>> correct results, as expected
>>
>> LEFT OUTER JOIN with WHERE conditions on both tables
>>
>> SELECT communications.sender_id, conversions.actor_id,
>> communications.version, conversions.reference_id FROM communications
>> LEFT OUTER JOIN conversions ON communications.reference_id =
>> conversions.reference_id WHERE communications.hour >= 343013 AND
>> communications.hour < 343014 AND conversions.application =
>> 'reference'
>>
>> 1 2 A 2
>>
>> incorrect results, it simply displays the row that was matched in
>> both
>> tables.
>>
>> Any ideas what's going on here? Is this intended? How can I get a
>> LEFT
>> OUTER JOIN and put conditions on both tables?
>>
>> Josh Ferguson
>>
>
>
>
>
> --
> Yours,
> Zheng
Re: Yet another join issue
Posted by Josh Ferguson <jo...@besquared.net>.
Also something Else that seems work is to specify all my conditions
for conversions in the ON clause such as:
LEFT OUTER JOIN
conversions ON
communications.account = conversions.account AND
communications.application = conversions.application AND
communications.reference_id = conversions.reference_id AND
conversions.hour >= 343013 AND
conversions.hour < 343014
This appears to also filter the tables before the join is performed,
does this work how I think it is working?
Josh Ferguson
On Feb 16, 2009, at 10:28 PM, Zheng Shao wrote:
> The following will also work. In the case of left outer join, the
> value of columns from the missing table will be NULL, so checking
> whether the value IS NULL should solve the problem.
>
> SELECT communications.sender_id, conversions.actor_id,
> communications.version, conversions.reference_id FROM communications
> LEFT OUTER JOIN conversions ON communications.reference_id =
> conversions.reference_id WHERE communications.hour >= 343013 AND
> communications.hour < 343014 AND ( conversions.application =
> 'reference' OR conversions.application IS NULL);
>
> I am not sure whether SQL standard says something different from
> what Hive implements. It will be great if you could point us to some
> references.
>
>
>
>
> Zheng
>
> On Mon, Feb 16, 2009 at 10:21 PM, Josh Ferguson <jo...@besquared.net>
> wrote:
> Will this work if this select statement is already nested in a FROM?
>
> Josh Ferguson
>
> On Feb 16, 2009, at 10:14 PM, Prasad Chakka wrote:
>
>> Try below,
>>
>> SELECT a.sender_id,
>> b.actor_id,
>> a.version,
>> b.reference_id
>> FROM communications a
>> LEFT OUTER JOIN (select conversions.actor_id,
>> conversions.reference_id from conversions where
>> conversions.application='reference') b
>> ON a.reference_id = b.reference_id
>> WHERE a.hour >= 343013
>> AND a.hour < 343014
>>
>>
>> From: Josh Ferguson <jo...@besquared.net>
>> Reply-To: <hi...@hadoop.apache.org>
>> Date: Mon, 16 Feb 2009 22:04:14 -0800
>> To: <hi...@hadoop.apache.org>
>> Subject: Yet another join issue
>>
>> Ok so this one I just ran into. It appears when doing a join of two
>> tables that, when using a LEFT OUTER JOIN and specifying a WHERE
>> condition on both tables, the resulting join is an INNER JOIN.
>> Example
>> follows:
>>
>> LEFT OUTER JOIN with WHERE condition on the LEFT table
>>
>> SELECT communications.sender_id, conversions.actor_id,
>> communications.version, conversions.reference_id FROM communications
>> LEFT OUTER JOIN conversions ON communications.reference_id =
>> conversions.reference_id WHERE communications.hour >= 343013 AND
>> communications.hour < 343014
>>
>> 1 NULL A NULL
>> 1 2 A 2
>> 2 NULL A NULL
>> 3 NULL B NULL
>>
>> correct results, as expected
>>
>> LEFT OUTER JOIN with WHERE conditions on both tables
>>
>> SELECT communications.sender_id, conversions.actor_id,
>> communications.version, conversions.reference_id FROM communications
>> LEFT OUTER JOIN conversions ON communications.reference_id =
>> conversions.reference_id WHERE communications.hour >= 343013 AND
>> communications.hour < 343014 AND conversions.application =
>> 'reference'
>>
>> 1 2 A 2
>>
>> incorrect results, it simply displays the row that was matched in
>> both
>> tables.
>>
>> Any ideas what's going on here? Is this intended? How can I get a
>> LEFT
>> OUTER JOIN and put conditions on both tables?
>>
>> Josh Ferguson
>>
>
>
>
>
> --
> Yours,
> Zheng
RE: Yet another join issue
Posted by Joydeep Sen Sarma <js...@facebook.com>.
Searching my computer, I find Namit quoting: "ansi sql semantics are that the filter is executed after the join."
So there u go ..
In the same mail he suggested putting the filter condition for the table inside the ON clause for execution before the join. So I guess u might want to try:
SELECT communications.sender_id, conversions.actor_id,
communications.version, conversions.reference_id FROM communications
LEFT OUTER JOIN conversions ON (communications.reference_id =
conversions.reference_id AND conversions.application='reference') WHERE communications.hour >= 343013 AND
communications.hour < 343014
________________________________
From: Josh Ferguson [mailto:josh@besquared.net]
Sent: Monday, February 16, 2009 10:53 PM
To: hive-user@hadoop.apache.org
Subject: Re: Yet another join issue
The thing I want the WHERE clause to do here is filter the two sets *before* I join them not after. This is what Prasad's query was doing unfortunately it wasn't working properly inside of a SELECT TRANSFORM FROM(...) block. Anyone know the SQL standard involving WHERE clauses and joins?
Josh Ferguson
On Feb 16, 2009, at 10:28 PM, Zheng Shao wrote:
The following will also work. In the case of left outer join, the value of columns from the missing table will be NULL, so checking whether the value IS NULL should solve the problem.
SELECT communications.sender_id, conversions.actor_id, communications.version, conversions.reference_id FROM communications LEFT OUTER JOIN conversions ON communications.reference_id = conversions.reference_id WHERE communications.hour >= 343013 AND communications.hour < 343014 AND ( conversions.application = 'reference' OR conversions.application IS NULL);
I am not sure whether SQL standard says something different from what Hive implements. It will be great if you could point us to some references.
Zheng
On Mon, Feb 16, 2009 at 10:21 PM, Josh Ferguson <jo...@besquared.net>> wrote:
Will this work if this select statement is already nested in a FROM?
Josh Ferguson
On Feb 16, 2009, at 10:14 PM, Prasad Chakka wrote:
Try below,
SELECT a.sender_id,
b.actor_id,
a.version,
b.reference_id
FROM communications a
LEFT OUTER JOIN (select conversions.actor_id, conversions.reference_id from conversions where conversions.application='reference') b
ON a.reference_id = b.reference_id
WHERE a.hour >= 343013
AND a.hour < 343014
________________________________
From: Josh Ferguson <jo...@besquared.net>>
Reply-To: <hi...@hadoop.apache.org>>
Date: Mon, 16 Feb 2009 22:04:14 -0800
To: <hi...@hadoop.apache.org>>
Subject: Yet another join issue
Ok so this one I just ran into. It appears when doing a join of two
tables that, when using a LEFT OUTER JOIN and specifying a WHERE
condition on both tables, the resulting join is an INNER JOIN. Example
follows:
LEFT OUTER JOIN with WHERE condition on the LEFT table
SELECT communications.sender_id, conversions.actor_id,
communications.version, conversions.reference_id FROM communications
LEFT OUTER JOIN conversions ON communications.reference_id =
conversions.reference_id WHERE communications.hour >= 343013 AND
communications.hour < 343014
1 NULL A NULL
1 2 A 2
2 NULL A NULL
3 NULL B NULL
correct results, as expected
LEFT OUTER JOIN with WHERE conditions on both tables
SELECT communications.sender_id, conversions.actor_id,
communications.version, conversions.reference_id FROM communications
LEFT OUTER JOIN conversions ON communications.reference_id =
conversions.reference_id WHERE communications.hour >= 343013 AND
communications.hour < 343014 AND conversions.application = 'reference'
1 2 A 2
incorrect results, it simply displays the row that was matched in both
tables.
Any ideas what's going on here? Is this intended? How can I get a LEFT
OUTER JOIN and put conditions on both tables?
Josh Ferguson
--
Yours,
Zheng
Re: Yet another join issue
Posted by Josh Ferguson <jo...@besquared.net>.
The thing I want the WHERE clause to do here is filter the two sets
*before* I join them not after. This is what Prasad's query was doing
unfortunately it wasn't working properly inside of a SELECT TRANSFORM
FROM(...) block. Anyone know the SQL standard involving WHERE clauses
and joins?
Josh Ferguson
On Feb 16, 2009, at 10:28 PM, Zheng Shao wrote:
> The following will also work. In the case of left outer join, the
> value of columns from the missing table will be NULL, so checking
> whether the value IS NULL should solve the problem.
>
> SELECT communications.sender_id, conversions.actor_id,
> communications.version, conversions.reference_id FROM communications
> LEFT OUTER JOIN conversions ON communications.reference_id =
> conversions.reference_id WHERE communications.hour >= 343013 AND
> communications.hour < 343014 AND ( conversions.application =
> 'reference' OR conversions.application IS NULL);
>
> I am not sure whether SQL standard says something different from
> what Hive implements. It will be great if you could point us to some
> references.
>
>
>
>
> Zheng
>
> On Mon, Feb 16, 2009 at 10:21 PM, Josh Ferguson <jo...@besquared.net>
> wrote:
> Will this work if this select statement is already nested in a FROM?
>
> Josh Ferguson
>
> On Feb 16, 2009, at 10:14 PM, Prasad Chakka wrote:
>
>> Try below,
>>
>> SELECT a.sender_id,
>> b.actor_id,
>> a.version,
>> b.reference_id
>> FROM communications a
>> LEFT OUTER JOIN (select conversions.actor_id,
>> conversions.reference_id from conversions where
>> conversions.application='reference') b
>> ON a.reference_id = b.reference_id
>> WHERE a.hour >= 343013
>> AND a.hour < 343014
>>
>>
>> From: Josh Ferguson <jo...@besquared.net>
>> Reply-To: <hi...@hadoop.apache.org>
>> Date: Mon, 16 Feb 2009 22:04:14 -0800
>> To: <hi...@hadoop.apache.org>
>> Subject: Yet another join issue
>>
>> Ok so this one I just ran into. It appears when doing a join of two
>> tables that, when using a LEFT OUTER JOIN and specifying a WHERE
>> condition on both tables, the resulting join is an INNER JOIN.
>> Example
>> follows:
>>
>> LEFT OUTER JOIN with WHERE condition on the LEFT table
>>
>> SELECT communications.sender_id, conversions.actor_id,
>> communications.version, conversions.reference_id FROM communications
>> LEFT OUTER JOIN conversions ON communications.reference_id =
>> conversions.reference_id WHERE communications.hour >= 343013 AND
>> communications.hour < 343014
>>
>> 1 NULL A NULL
>> 1 2 A 2
>> 2 NULL A NULL
>> 3 NULL B NULL
>>
>> correct results, as expected
>>
>> LEFT OUTER JOIN with WHERE conditions on both tables
>>
>> SELECT communications.sender_id, conversions.actor_id,
>> communications.version, conversions.reference_id FROM communications
>> LEFT OUTER JOIN conversions ON communications.reference_id =
>> conversions.reference_id WHERE communications.hour >= 343013 AND
>> communications.hour < 343014 AND conversions.application =
>> 'reference'
>>
>> 1 2 A 2
>>
>> incorrect results, it simply displays the row that was matched in
>> both
>> tables.
>>
>> Any ideas what's going on here? Is this intended? How can I get a
>> LEFT
>> OUTER JOIN and put conditions on both tables?
>>
>> Josh Ferguson
>>
>
>
>
>
> --
> Yours,
> Zheng
Re: Yet another join issue
Posted by Zheng Shao <zs...@gmail.com>.
The following will also work. In the case of left outer join, the value of
columns from the missing table will be NULL, so checking whether the value
IS NULL should solve the problem.
SELECT communications.sender_id, conversions.actor_id,
communications.version, conversions.reference_id FROM communications LEFT
OUTER JOIN conversions ON communications.reference_id =
conversions.reference_id WHERE communications.hour >= 343013 AND
communications.hour < 343014 AND ( conversions.application = 'reference' OR
conversions.application IS NULL);
I am not sure whether SQL standard says something different from what Hive
implements. It will be great if you could point us to some references.
Zheng
On Mon, Feb 16, 2009 at 10:21 PM, Josh Ferguson <jo...@besquared.net> wrote:
> Will this work if this select statement is already nested in a FROM?
> Josh Ferguson
>
> On Feb 16, 2009, at 10:14 PM, Prasad Chakka wrote:
>
> Try below,
>
> SELECT a.sender_id,
> b.actor_id,
> a.version,
> b.reference_id
> FROM communications a
> LEFT OUTER JOIN (select conversions.actor_id,
> conversions.reference_id from conversions where
> conversions.application='reference') b
> ON a.reference_id = b.reference_id
> WHERE a.hour >= 343013
> AND a.hour < 343014
>
>
> ------------------------------
> *From: *Josh Ferguson <jo...@besquared.net>
> *Reply-To: *<hi...@hadoop.apache.org>
> *Date: *Mon, 16 Feb 2009 22:04:14 -0800
> *To: *<hi...@hadoop.apache.org>
> *Subject: *Yet another join issue
>
> Ok so this one I just ran into. It appears when doing a join of two
> tables that, when using a LEFT OUTER JOIN and specifying a WHERE
> condition on both tables, the resulting join is an INNER JOIN. Example
> follows:
>
> LEFT OUTER JOIN with WHERE condition on the LEFT table
>
> SELECT communications.sender_id, conversions.actor_id,
> communications.version, conversions.reference_id FROM communications
> LEFT OUTER JOIN conversions ON communications.reference_id =
> conversions.reference_id WHERE communications.hour >= 343013 AND
> communications.hour < 343014
>
> 1 NULL A NULL
> 1 2 A 2
> 2 NULL A NULL
> 3 NULL B NULL
>
> correct results, as expected
>
> LEFT OUTER JOIN with WHERE conditions on both tables
>
> SELECT communications.sender_id, conversions.actor_id,
> communications.version, conversions.reference_id FROM communications
> LEFT OUTER JOIN conversions ON communications.reference_id =
> conversions.reference_id WHERE communications.hour >= 343013 AND
> communications.hour < 343014 AND conversions.application = 'reference'
>
> 1 2 A 2
>
> incorrect results, it simply displays the row that was matched in both
> tables.
>
> Any ideas what's going on here? Is this intended? How can I get a LEFT
> OUTER JOIN and put conditions on both tables?
>
> Josh Ferguson
>
>
>
--
Yours,
Zheng
Re: Yet another join issue
Posted by Josh Ferguson <jo...@besquared.net>.
Will this work if this select statement is already nested in a FROM?
Josh Ferguson
On Feb 16, 2009, at 10:14 PM, Prasad Chakka wrote:
> Try below,
>
> SELECT a.sender_id,
> b.actor_id,
> a.version,
> b.reference_id
> FROM communications a
> LEFT OUTER JOIN (select conversions.actor_id,
> conversions.reference_id from conversions where
> conversions.application=’reference’) b
> ON a.reference_id = b.reference_id
> WHERE a.hour >= 343013
> AND a.hour < 343014
>
>
> From: Josh Ferguson <jo...@besquared.net>
> Reply-To: <hi...@hadoop.apache.org>
> Date: Mon, 16 Feb 2009 22:04:14 -0800
> To: <hi...@hadoop.apache.org>
> Subject: Yet another join issue
>
> Ok so this one I just ran into. It appears when doing a join of two
> tables that, when using a LEFT OUTER JOIN and specifying a WHERE
> condition on both tables, the resulting join is an INNER JOIN. Example
> follows:
>
> LEFT OUTER JOIN with WHERE condition on the LEFT table
>
> SELECT communications.sender_id, conversions.actor_id,
> communications.version, conversions.reference_id FROM communications
> LEFT OUTER JOIN conversions ON communications.reference_id =
> conversions.reference_id WHERE communications.hour >= 343013 AND
> communications.hour < 343014
>
> 1 NULL A NULL
> 1 2 A 2
> 2 NULL A NULL
> 3 NULL B NULL
>
> correct results, as expected
>
> LEFT OUTER JOIN with WHERE conditions on both tables
>
> SELECT communications.sender_id, conversions.actor_id,
> communications.version, conversions.reference_id FROM communications
> LEFT OUTER JOIN conversions ON communications.reference_id =
> conversions.reference_id WHERE communications.hour >= 343013 AND
> communications.hour < 343014 AND conversions.application = 'reference'
>
> 1 2 A 2
>
> incorrect results, it simply displays the row that was matched in both
> tables.
>
> Any ideas what's going on here? Is this intended? How can I get a LEFT
> OUTER JOIN and put conditions on both tables?
>
> Josh Ferguson
>
Re: Yet another join issue
Posted by Prasad Chakka <pr...@facebook.com>.
Try below,
SELECT a.sender_id,
b.actor_id,
a.version,
b.reference_id
FROM communications a
LEFT OUTER JOIN (select conversions.actor_id, conversions.reference_id from conversions where conversions.application='reference') b
ON a.reference_id = b.reference_id
WHERE a.hour >= 343013
AND a.hour < 343014
________________________________
From: Josh Ferguson <jo...@besquared.net>
Reply-To: <hi...@hadoop.apache.org>
Date: Mon, 16 Feb 2009 22:04:14 -0800
To: <hi...@hadoop.apache.org>
Subject: Yet another join issue
Ok so this one I just ran into. It appears when doing a join of two
tables that, when using a LEFT OUTER JOIN and specifying a WHERE
condition on both tables, the resulting join is an INNER JOIN. Example
follows:
LEFT OUTER JOIN with WHERE condition on the LEFT table
SELECT communications.sender_id, conversions.actor_id,
communications.version, conversions.reference_id FROM communications
LEFT OUTER JOIN conversions ON communications.reference_id =
conversions.reference_id WHERE communications.hour >= 343013 AND
communications.hour < 343014
1 NULL A NULL
1 2 A 2
2 NULL A NULL
3 NULL B NULL
correct results, as expected
LEFT OUTER JOIN with WHERE conditions on both tables
SELECT communications.sender_id, conversions.actor_id,
communications.version, conversions.reference_id FROM communications
LEFT OUTER JOIN conversions ON communications.reference_id =
conversions.reference_id WHERE communications.hour >= 343013 AND
communications.hour < 343014 AND conversions.application = 'reference'
1 2 A 2
incorrect results, it simply displays the row that was matched in both
tables.
Any ideas what's going on here? Is this intended? How can I get a LEFT
OUTER JOIN and put conditions on both tables?
Josh Ferguson