Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed

Discussion:

mahender bigdata

2016-05-03 01:21:46 UTC

Hi,

Is there a way to implement not exists in Hive. I'm using Hive 1.2. I'm
getting below error

"Unsupported SubQuery Expression '1': Only SubQuery expressions that are
top level conjuncts are allowed"

_Query:_

_
_

select A.Col1,A.Col2....B.Col3

From Table1 A

LEFT OUTER JOIN Table2 B
ON A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 mb WHERE ma.Col1= adi1.Col1 GROUP BY
ma.Col1 HAVING COUNT(*)>1 )
AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN 'NOT-NULL'
ELSE 'NULL' END) = B.Col2)

I Would like to have OR Condition in LEFT Join hive statement. or
alternative way by splitting.

thanks

Markovitz, Dudu

2016-05-03 05:52:55 UTC

Permalink

Hi

Before dealing the issue itself, can you please fix the query?
There are 3 aliased tables - Table1 (A), Table2 (B) & Table2 (mb) but youâre using additional 2 aliases â ma & adi1.

Thanks

Dudu

select A.Col1,A.Col2....B.Col3

From Table1 A

LEFT OUTER JOIN Table2 B
ON A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 mb WHERE ma.Col1= adi1.Col1 GROUP BY ma.Col1 HAVING COUNT(*)>1 )
AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN 'NOT-NULL' ELSE 'NULL' END) = B.Col2)

From: mahender bigdata [mailto:***@outlook.com]
Sent: Tuesday, May 03, 2016 4:22 AM
To: ***@hive.apache.org
Subject: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed

Hi,

Is there a way to implement not exists in Hive. I'm using Hive 1.2. I'm getting below error

"Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed"

Query:

select A.Col1,A.Col2....B.Col3

From Table1 A

LEFT OUTER JOIN Table2 B
ON A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 mb WHERE ma.Col1= adi1.Col1 GROUP BY ma.Col1 HAVING COUNT(*)>1 )
AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN 'NOT-NULL' ELSE 'NULL' END) = B.Col2)

I Would like to have OR Condition in LEFT Join hive statement. or alternative way by splitting.

thanks

mahender bigdata

2016-05-03 13:02:05 UTC

Permalink

Updated..

select A.Col1,A.Col2....B.Col3

From Table1 A

LEFT OUTER JOIN Table2 B
ON A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 B WHERE B.Col1= A.Col1 GROUP BY
A.Col1 HAVING COUNT(*)>1 )
AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN 'NOT-NULL'
ELSE 'NULL' END) = B.Col2)

Post by Markovitz, Dudu
Hi
Before dealing the issue itself, can you please fix the query?
There are 3 aliased tables - Table1 (A), Table2 (B) & Table2 (mb) but
youâre using additional 2 aliases â ma & adi1.
Thanks
Dudu
select A.Col1,A.Col2....B.Col3
From Table1 A
LEFT OUTER JOIN Table2 B
ON A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 B WHERE B.Col1= A.Col1 GROUP BY
A.Col1 HAVING COUNT(*)>1 )
AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN 'NOT-NULL'
ELSE 'NULL' END) = B.Col2)
*Sent:* Tuesday, May 03, 2016 4:22 AM
*Subject:* Unsupported SubQuery Expression '1': Only SubQuery
expressions that are top level conjuncts are allowed
Hi,
Is there a way to implement not exists in Hive. I'm using Hive 1.2.
I'm getting below error
"Unsupported SubQuery Expression '1': Only SubQuery expressions that
are top level conjuncts are allowed"
_Query:_
select A.Col1,A.Col2....B.Col3
From Table1 A
LEFT OUTER JOIN Table2 B
ON A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 mb WHERE ma.Col1= adi1.Col1 GROUP
BY ma.Col1 HAVING COUNT(*)>1 )
AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN 'NOT-NULL'
ELSE 'NULL' END) = B.Col2)
I Would like to have OR Condition in LEFT Join hive statement. or
alternative way by splitting.
thanks

Markovitz, Dudu

2016-05-03 17:36:13 UTC

Permalink

I left out the filter on column Col2 in order to simplify the test case.
The following query is logically equal to your original query.

BTW â
You donât need the GROUP BY A.Col1 part in your original query

Dudu

create table Table1 (Col1 int,Col3 int);
create table Table2 (Col1 int,Col3 int);

insert into Table1 values (10,1),(20,2),(40,4),(60,7),(80,8);
insert into Table2 values (10,1),(30,2),(20,3),(50,4),(40,5),(40,6),(70,7);

select *

from table1 a

left join (select col1

from table2

group by col1

having count(*) > 1

)

b2

on b2.col1 =

a.col1

left join table2 b

on a.col3 =

b.col3

and b2.col1 is null

;

10 1 NULL 10 1
20 2 NULL 30 2
40 4 40 NULL NULL
60 7 NULL 70 7
80 8 NULL NULL NULL

From: mahender bigdata [mailto:***@outlook.com]
Sent: Tuesday, May 03, 2016 4:02 PM
To: ***@hive.apache.org
Subject: Re: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed

Updated..

select A.Col1,A.Col2....B.Col3

From Table1 A
LEFT OUTER JOIN Table2 B
ON A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 B WHERE B.Col1= A.Col1 GROUP BY A.Col1 HAVING COUNT(*)>1 )
AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN 'NOT-NULL' ELSE 'NULL' END) = B.Col2)

On 5/2/2016 10:52 PM, Markovitz, Dudu wrote:
Hi

Before dealing the issue itself, can you please fix the query?
There are 3 aliased tables - Table1 (A), Table2 (B) & Table2 (mb) but youâre using additional 2 aliases â ma & adi1.

Thanks

Dudu

select A.Col1,A.Col2....B.Col3

From Table1 A

LEFT OUTER JOIN Table2 B
ON A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 B WHERE B.Col1= A.Col1 GROUP BY A.Col1 HAVING COUNT(*)>1 )
AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN 'NOT-NULL' ELSE 'NULL' END) = B.Col2)

From: mahender bigdata [mailto:***@outlook.com]
Sent: Tuesday, May 03, 2016 4:22 AM
To: ***@hive.apache.org<mailto:***@hive.apache.org>
Subject: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed

Hi,

Is there a way to implement not exists in Hive. I'm using Hive 1.2. I'm getting below error

"Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed"

Query:

select A.Col1,A.Col2....B.Col3

From Table1 A

LEFT OUTER JOIN Table2 B
ON A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 mb WHERE ma.Col1= adi1.Col1 GROUP BY ma.Col1 HAVING COUNT(*)>1 )
AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN 'NOT-NULL' ELSE 'NULL' END) = B.Col2)

I Would like to have OR Condition in LEFT Join hive statement. or alternative way by splitting.

thanks

Markovitz, Dudu

2016-05-03 18:24:11 UTC

Permalink

Forget about the BTWâŠ
Apparently hive behaves like sqlite in that matter and not like other databases

hive> select 1 from table1 having 1=1;
FAILED: SemanticException HAVING specified without GROUP BY

From: Markovitz, Dudu [mailto:***@paypal.com]
Sent: Tuesday, May 03, 2016 8:36 PM
To: ***@hive.apache.org
Subject: RE: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed

I left out the filter on column Col2 in order to simplify the test case.
The following query is logically equal to your original query.

BTW â
You donât need the GROUP BY A.Col1 part in your original query

Dudu

create table Table1 (Col1 int,Col3 int);
create table Table2 (Col1 int,Col3 int);

insert into Table1 values (10,1),(20,2),(40,4),(60,7),(80,8);
insert into Table2 values (10,1),(30,2),(20,3),(50,4),(40,5),(40,6),(70,7);

select *

from table1 a

left join (select col1

from table2

group by col1

having count(*) > 1

)

b2

on b2.col1 =

a.col1

left join table2 b

on a.col3 =

b.col3

and b2.col1 is null

;

10 1 NULL 10 1
20 2 NULL 30 2
40 4 40 NULL NULL
60 7 NULL 70 7
80 8 NULL NULL NULL

From: mahender bigdata [mailto:***@outlook.com]
Sent: Tuesday, May 03, 2016 4:02 PM
To: ***@hive.apache.org<mailto:***@hive.apache.org>
Subject: Re: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed

Updated..

select A.Col1,A.Col2....B.Col3

From Table1 A
LEFT OUTER JOIN Table2 B
ON A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 B WHERE B.Col1= A.Col1 GROUP BY A.Col1 HAVING COUNT(*)>1 )
AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN 'NOT-NULL' ELSE 'NULL' END) = B.Col2)
On 5/2/2016 10:52 PM, Markovitz, Dudu wrote:
Hi

Before dealing the issue itself, can you please fix the query?
There are 3 aliased tables - Table1 (A), Table2 (B) & Table2 (mb) but youâre using additional 2 aliases â ma & adi1.

Thanks

Dudu

select A.Col1,A.Col2....B.Col3

From Table1 A

LEFT OUTER JOIN Table2 B
ON A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 B WHERE B.Col1= A.Col1 GROUP BY A.Col1 HAVING COUNT(*)>1 )
AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN 'NOT-NULL' ELSE 'NULL' END) = B.Col2)

From: mahender bigdata [mailto:***@outlook.com]
Sent: Tuesday, May 03, 2016 4:22 AM
To: ***@hive.apache.org<mailto:***@hive.apache.org>
Subject: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed

Hi,

Is there a way to implement not exists in Hive. I'm using Hive 1.2. I'm getting below error

"Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed"

Query:

select A.Col1,A.Col2....B.Col3

From Table1 A

LEFT OUTER JOIN Table2 B
ON A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 mb WHERE ma.Col1= adi1.Col1 GROUP BY ma.Col1 HAVING COUNT(*)>1 )
AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN 'NOT-NULL' ELSE 'NULL' END) = B.Col2)

I Would like to have OR Condition in LEFT Join hive statement. or alternative way by splitting.

thanks

mahender bigdata

2016-05-04 16:38:44 UTC

Permalink

Thanks Dudu,

Can you help me in parsing below logic, I see First you are starting
join of table1 with result set of Group by > 1 and perform left join
with table2, how can we get reference a. alias of joined result or will
hive pickup "a" column from table 1 and 3 column in table2.

thanks in advance

Post by Markovitz, Dudu
Forget about the BTWâŠ
Apparently hive behaves like sqlite in that matter and not like other databases
hive> select 1 from table1 having 1=1;
FAILED: SemanticException HAVING specified without GROUP BY
*Sent:* Tuesday, May 03, 2016 8:36 PM
*Subject:* RE: Unsupported SubQuery Expression '1': Only SubQuery
expressions that are top level conjuncts are allowed
I left out the filter on column Col2in order to simplify the test case.
The following query is logically equal to your original query.
**
BTW â
You donât need the GROUP BY A.Col1 part in your original query
Dudu**
**
*create**table*Table1(Col1int,Col3int)*;*
*create**table*Table2(Col1int,Col3int)*;*
*insert**into*Table1*values*(10,1),(20,2),(40,4),(60,7),(80,8)*;*
*insert**into*Table2*values*(10,1),(30,2),(20,3),(50,4),(40,5),(40,6),(70,7)*;*
*select**
*from*table1a
*left**join* (*select*col1
*from*table2
*group**by*col1
*having*count(*) >1
)
b2
*on*b2.col1=
a.col1
*left**join*table2b
*on*a.col3=
b.col3
*and*b2.col1*is**null*
*;*
10 1 NULL 10 1
20 2 NULL 30 2
40 4 40 NULL NULL
60 7 NULL 70 7
80 8 NULL NULL NULL
*Sent:* Tuesday, May 03, 2016 4:02 PM
*Subject:* Re: Unsupported SubQuery Expression '1': Only SubQuery
expressions that are top level conjuncts are allowed
Updated..
select A.Col1,A.Col2....B.Col3
From Table1 A
LEFT OUTER JOIN Table2 B
ON A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 B WHERE B.Col1= A.Col1 GROUP BY
A.Col1 HAVING COUNT(*)>1 )
AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN 'NOT-NULL'
ELSE 'NULL' END) = B.Col2)
Hi
Before dealing the issue itself, can you please fix the query?
There are 3 aliased tables - Table1 (A), Table2 (B) & Table2 (mb)
but youâre using additional 2 aliases â ma & adi1.
Thanks
Dudu
select A.Col1,A.Col2....B.Col3
From Table1 A
LEFT OUTER JOIN Table2 B
ON A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 B WHERE B.Col1= A.Col1 GROUP
BY A.Col1 HAVING COUNT(*)>1 )
AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN 'NOT-NULL'
ELSE 'NULL' END) = B.Col2)
*Sent:* Tuesday, May 03, 2016 4:22 AM
*Subject:* Unsupported SubQuery Expression '1': Only SubQuery
expressions that are top level conjuncts are allowed
Hi,
Is there a way to implement not exists in Hive. I'm using Hive
1.2. I'm getting below error
"Unsupported SubQuery Expression '1': Only SubQuery expressions
that are top level conjuncts are allowed"
_Query:_
select A.Col1,A.Col2....B.Col3
From Table1 A
LEFT OUTER JOIN Table2 B
ON A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 mb WHERE ma.Col1= adi1.Col1
GROUP BY ma.Col1 HAVING COUNT(*)>1 )
AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN
'NOT-NULL' ELSE 'NULL' END) = B.Col2)
I Would like to have OR Condition in LEFT Join hive statement. or
alternative way by splitting.
thanks

Markovitz, Dudu

2016-05-04 17:57:39 UTC

Permalink

Hi

The syntax is not Hive specific but SQL ANSI/ISO.
In a series of âJOIN âŠ ON âŠâ any âONâ can (but not necessarily have to) refer any of its preceding tables, e.g. â

select âŠ from t1 join t2 on âŠ *1 âŠ join t3 on âŠ *2 âŠ join t4 on âŠ *3 âŠ
*1 The 1st âONâ can refer tables t1 & t2
*2 The 2nd âONâ can refer tables t1, t2 & t3
*3 The 3rd âONâ can refer tables t1, t2, t3 & t4

In our query the ââŠ group by âŠ > 1â combined with âb2.col1 is nullâ implements the functionality of the ânot existsâ from the original query.
The rest of the query stays quite the same.

Dudu

From: mahender bigdata [mailto:***@outlook.com]
Sent: Wednesday, May 04, 2016 7:39 PM
To: ***@hive.apache.org
Subject: Re: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed

Thanks Dudu,

Can you help me in parsing below logic, I see First you are starting join of table1 with result set of Group by > 1 and perform left join with table2, how can we get reference a. alias of joined result or will hive pickup "a" column from table 1 and 3 column in table2.

thanks in advance

On 5/3/2016 11:24 AM, Markovitz, Dudu wrote:
Forget about the BTWâŠ
Apparently hive behaves like sqlite in that matter and not like other databases

hive> select 1 from table1 having 1=1;
FAILED: SemanticException HAVING specified without GROUP BY

From: Markovitz, Dudu [mailto:***@paypal.com]
Sent: Tuesday, May 03, 2016 8:36 PM
To: ***@hive.apache.org<mailto:***@hive.apache.org>
Subject: RE: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed

I left out the filter on column Col2 in order to simplify the test case.
The following query is logically equal to your original query.

BTW â
You donât need the GROUP BY A.Col1 part in your original query

Dudu

create table Table1 (Col1 int,Col3 int);
create table Table2 (Col1 int,Col3 int);

insert into Table1 values (10,1),(20,2),(40,4),(60,7),(80,8);
insert into Table2 values (10,1),(30,2),(20,3),(50,4),(40,5),(40,6),(70,7);

select *

from table1 a

left join (select col1

from table2

group by col1

having count(*) > 1

)

b2

on b2.col1 =

a.col1

left join table2 b

on a.col3 =

b.col3

and b2.col1 is null

;

10 1 NULL 10 1
20 2 NULL 30 2
40 4 40 NULL NULL
60 7 NULL 70 7
80 8 NULL NULL NULL

From: mahender bigdata [mailto:***@outlook.com]
Sent: Tuesday, May 03, 2016 4:02 PM
To: ***@hive.apache.org<mailto:***@hive.apache.org>
Subject: Re: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed

Updated..

select A.Col1,A.Col2....B.Col3

From Table1 A
LEFT OUTER JOIN Table2 B
ON A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 B WHERE B.Col1= A.Col1 GROUP BY A.Col1 HAVING COUNT(*)>1 )
AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN 'NOT-NULL' ELSE 'NULL' END) = B.Col2)
On 5/2/2016 10:52 PM, Markovitz, Dudu wrote:
Hi

Before dealing the issue itself, can you please fix the query?
There are 3 aliased tables - Table1 (A), Table2 (B) & Table2 (mb) but youâre using additional 2 aliases â ma & adi1.

Thanks

Dudu

select A.Col1,A.Col2....B.Col3

From Table1 A

LEFT OUTER JOIN Table2 B
ON A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 B WHERE B.Col1= A.Col1 GROUP BY A.Col1 HAVING COUNT(*)>1 )
AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN 'NOT-NULL' ELSE 'NULL' END) = B.Col2)

From: mahender bigdata [mailto:***@outlook.com]
Sent: Tuesday, May 03, 2016 4:22 AM
To: ***@hive.apache.org<mailto:***@hive.apache.org>
Subject: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed

Hi,

Is there a way to implement not exists in Hive. I'm using Hive 1.2. I'm getting below error

"Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed"

Query:

select A.Col1,A.Col2....B.Col3

From Table1 A

LEFT OUTER JOIN Table2 B
ON A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 mb WHERE ma.Col1= adi1.Col1 GROUP BY ma.Col1 HAVING COUNT(*)>1 )
AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN 'NOT-NULL' ELSE 'NULL' END) = B.Col2)

I Would like to have OR Condition in LEFT Join hive statement. or alternative way by splitting.

thanks

mahender bigdata

2016-05-10 22:42:39 UTC

Permalink

Thanks Dudu, I made modification as per our requirement. ur query helped
me to modify as per our requirement.

Post by Markovitz, Dudu
Hi
The syntax is not Hive specific but SQL ANSI/ISO.
In a series of âJOIN âŠ ON âŠâ any âONâ can (but not necessarily have
to) refer any of its preceding tables, e.g. â
select âŠ from t1 join t2 on âŠ ^*1 âŠ join t3 on âŠ ^*2 âŠ join t4 on âŠ ^*3 âŠ
^*1 The 1st âONâ can refer tables t1 & t2^
^*2 The 2nd âONâ can refer tables t1, t2 & t3^
^*3 The 3rd âONâ can refer tables t1, t2, t3 & t4^
^
In our query the ââŠ *group* *by*âŠ > 1â combined with âb2.col1 *is*
*null*â implements the functionality of the ânot existsâ from the
original query.
The rest of the query stays quite the same.
Dudu
*Sent:* Wednesday, May 04, 2016 7:39 PM
*Subject:* Re: Unsupported SubQuery Expression '1': Only SubQuery
expressions that are top level conjuncts are allowed
Thanks Dudu,
Can you help me in parsing below logic, I see First you are starting
join of table1 with result set of Group by > 1 and perform left join
with table2, how can we get reference a. alias of joined result or
will hive pickup "a" column from table 1 and 3 column in table2.
thanks in advance
Forget about the BTWâŠ
Apparently hive behaves like sqlite in that matter and not like other databases
hive> select 1 from table1 having 1=1;
FAILED: SemanticException HAVING specified without GROUP BY
*Sent:* Tuesday, May 03, 2016 8:36 PM
*Subject:* RE: Unsupported SubQuery Expression '1': Only SubQuery
expressions that are top level conjuncts are allowed
I left out the filter on column Col2in order to simplify the test case.
The following query is logically equal to your original query.
**
BTW â
You donât need the GROUP BY A.Col1 part in your original query
Dudu
**
*create**table*Table1(Col1int,Col3int)*;*
*create**table*Table2(Col1int,Col3int)*;*
*insert**into*Table1*values*(10,1),(20,2),(40,4),(60,7),(80,8)*;*
*insert**into*Table2*values*(10,1),(30,2),(20,3),(50,4),(40,5),(40,6),(70,7)*;*
*select**
*from*table1a
*left**join* (*select*col1
*from*table2
*group**by*col1
*having*count(*) >1
)
b2
*on*b2.col1=
a.col1
*left**join*table2b
*on*a.col3=
b.col3
*and*b2.col1*is**null*
*;*
10 1 NULL 10 1
20 2 NULL 30 2
40 4 40 NULL NULL
60 7 NULL 70 7
80 8 NULL NULL NULL
*Sent:* Tuesday, May 03, 2016 4:02 PM
*Subject:* Re: Unsupported SubQuery Expression '1': Only SubQuery
expressions that are top level conjuncts are allowed
Updated..
select A.Col1,A.Col2....B.Col3
From Table1 A
LEFT OUTER JOIN Table2 B
ON A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 B WHERE B.Col1= A.Col1 GROUP
BY A.Col1 HAVING COUNT(*)>1 )
AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN 'NOT-NULL'
ELSE 'NULL' END) = B.Col2)
Hi
Before dealing the issue itself, can you please fix the query?
There are 3 aliased tables - Table1 (A), Table2 (B) & Table2
(mb) but youâre using additional 2 aliases â ma & adi1.
Thanks
Dudu
select A.Col1,A.Col2....B.Col3
From Table1 A
LEFT OUTER JOIN Table2 B
ON A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 B WHERE B.Col1= A.Col1
GROUP BY A.Col1 HAVING COUNT(*)>1 )
AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN
'NOT-NULL' ELSE 'NULL' END) = B.Col2)
*Sent:* Tuesday, May 03, 2016 4:22 AM
*Subject:* Unsupported SubQuery Expression '1': Only SubQuery
expressions that are top level conjuncts are allowed
Hi,
Is there a way to implement not exists in Hive. I'm using
Hive 1.2. I'm getting below error
"Unsupported SubQuery Expression '1': Only SubQuery
expressions that are top level conjuncts are allowed"
_Query:_
select A.Col1,A.Col2....B.Col3
From Table1 A
LEFT OUTER JOIN Table2 B
ON A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 mb WHERE ma.Col1=
adi1.Col1 GROUP BY ma.Col1 HAVING COUNT(*)>1 )
AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN
'NOT-NULL' ELSE 'NULL' END) = B.Col2)
I Would like to have OR Condition in LEFT Join hive statement.
or alternative way by splitting.
thanks

Markovitz, Dudu

2016-05-11 04:24:50 UTC

Permalink

Youâre welcome

Dudu

From: mahender bigdata [mailto:***@outlook.com]
Sent: Wednesday, May 11, 2016 1:43 AM
To: ***@hive.apache.org
Subject: Re: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed

Thanks Dudu, I made modification as per our requirement. ur query helped me to modify as per our requirement.

On 5/4/2016 10:57 AM, Markovitz, Dudu wrote:
Hi

The syntax is not Hive specific but SQL ANSI/ISO.
In a series of âJOIN âŠ ON âŠâ any âONâ can (but not necessarily have to) refer any of its preceding tables, e.g. â

select âŠ from t1 join t2 on âŠ *1 âŠ join t3 on âŠ *2 âŠ join t4 on âŠ *3 âŠ
*1 The 1st âONâ can refer tables t1 & t2
*2 The 2nd âONâ can refer tables t1, t2 & t3
*3 The 3rd âONâ can refer tables t1, t2, t3 & t4

In our query the ââŠ group by âŠ > 1â combined with âb2.col1 is nullâ implements the functionality of the ânot existsâ from the original query.
The rest of the query stays quite the same.

Dudu

From: mahender bigdata [mailto:***@outlook.com]
Sent: Wednesday, May 04, 2016 7:39 PM
To: ***@hive.apache.org<mailto:***@hive.apache.org>
Subject: Re: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed

Thanks Dudu,

Can you help me in parsing below logic, I see First you are starting join of table1 with result set of Group by > 1 and perform left join with table2, how can we get reference a. alias of joined result or will hive pickup "a" column from table 1 and 3 column in table2.

thanks in advance

On 5/3/2016 11:24 AM, Markovitz, Dudu wrote:
Forget about the BTWâŠ
Apparently hive behaves like sqlite in that matter and not like other databases

hive> select 1 from table1 having 1=1;
FAILED: SemanticException HAVING specified without GROUP BY

From: Markovitz, Dudu [mailto:***@paypal.com]
Sent: Tuesday, May 03, 2016 8:36 PM
To: ***@hive.apache.org<mailto:***@hive.apache.org>
Subject: RE: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed

I left out the filter on column Col2 in order to simplify the test case.
The following query is logically equal to your original query.

BTW â
You donât need the GROUP BY A.Col1 part in your original query

Dudu

create table Table1 (Col1 int,Col3 int);
create table Table2 (Col1 int,Col3 int);

insert into Table1 values (10,1),(20,2),(40,4),(60,7),(80,8);
insert into Table2 values (10,1),(30,2),(20,3),(50,4),(40,5),(40,6),(70,7);

select *

from table1 a

left join (select col1

from table2

group by col1

having count(*) > 1

)

b2

on b2.col1 =

a.col1

left join table2 b

on a.col3 =

b.col3

and b2.col1 is null

;

10 1 NULL 10 1
20 2 NULL 30 2
40 4 40 NULL NULL
60 7 NULL 70 7
80 8 NULL NULL NULL

From: mahender bigdata [mailto:***@outlook.com]
Sent: Tuesday, May 03, 2016 4:02 PM
To: ***@hive.apache.org<mailto:***@hive.apache.org>
Subject: Re: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed

Updated..

select A.Col1,A.Col2....B.Col3

From Table1 A
LEFT OUTER JOIN Table2 B
ON A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 B WHERE B.Col1= A.Col1 GROUP BY A.Col1 HAVING COUNT(*)>1 )
AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN 'NOT-NULL' ELSE 'NULL' END) = B.Col2)
On 5/2/2016 10:52 PM, Markovitz, Dudu wrote:
Hi

Before dealing the issue itself, can you please fix the query?
There are 3 aliased tables - Table1 (A), Table2 (B) & Table2 (mb) but youâre using additional 2 aliases â ma & adi1.

Thanks

Dudu

select A.Col1,A.Col2....B.Col3

From Table1 A

LEFT OUTER JOIN Table2 B
ON A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 B WHERE B.Col1= A.Col1 GROUP BY A.Col1 HAVING COUNT(*)>1 )
AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN 'NOT-NULL' ELSE 'NULL' END) = B.Col2)

From: mahender bigdata [mailto:***@outlook.com]
Sent: Tuesday, May 03, 2016 4:22 AM
To: ***@hive.apache.org<mailto:***@hive.apache.org>
Subject: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed

Hi,

Is there a way to implement not exists in Hive. I'm using Hive 1.2. I'm getting below error

"Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed"

Query:

select A.Col1,A.Col2....B.Col3

From Table1 A

LEFT OUTER JOIN Table2 B
ON A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 mb WHERE ma.Col1= adi1.Col1 GROUP BY ma.Col1 HAVING COUNT(*)>1 )
AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN 'NOT-NULL' ELSE 'NULL' END) = B.Col2)

I Would like to have OR Condition in LEFT Join hive statement. or alternative way by splitting.

thanks