You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by mahender bigdata <Ma...@outlook.com> on 2016/05/03 03:21:46 UTC

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




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

Posted by "Markovitz, Dudu" <dm...@paypal.com>.
You’re welcome

Dudu

From: mahender bigdata [mailto:Mahender.BigData@outlook.com]
Sent: Wednesday, May 11, 2016 1:43 AM
To: user@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:Mahender.BigData@outlook.com]
Sent: Wednesday, May 04, 2016 7:39 PM
To: user@hive.apache.org<ma...@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:dmarkovitz@paypal.com]
Sent: Tuesday, May 03, 2016 8:36 PM
To: user@hive.apache.org<ma...@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:Mahender.BigData@outlook.com]
Sent: Tuesday, May 03, 2016 4:02 PM
To: user@hive.apache.org<ma...@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:Mahender.BigData@outlook.com]
Sent: Tuesday, May 03, 2016 4:22 AM
To: user@hive.apache.org<ma...@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








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

Posted by mahender bigdata <Ma...@outlook.com>.
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:Mahender.BigData@outlook.com]
> *Sent:* Wednesday, May 04, 2016 7:39 PM
> *To:* user@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:dmarkovitz@paypal.com]
>     *Sent:* Tuesday, May 03, 2016 8:36 PM
>     *To:* user@hive.apache.org <ma...@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 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
>
>     *From:*mahender bigdata [mailto:Mahender.BigData@outlook.com]
>     *Sent:* Tuesday, May 03, 2016 4:02 PM
>     *To:* user@hive.apache.org <ma...@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:Mahender.BigData@outlook.com]
>         *Sent:* Tuesday, May 03, 2016 4:22 AM
>         *To:* user@hive.apache.org <ma...@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
>


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

Posted by "Markovitz, Dudu" <dm...@paypal.com>.
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:Mahender.BigData@outlook.com]
Sent: Wednesday, May 04, 2016 7:39 PM
To: user@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:dmarkovitz@paypal.com]
Sent: Tuesday, May 03, 2016 8:36 PM
To: user@hive.apache.org<ma...@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:Mahender.BigData@outlook.com]
Sent: Tuesday, May 03, 2016 4:02 PM
To: user@hive.apache.org<ma...@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:Mahender.BigData@outlook.com]
Sent: Tuesday, May 03, 2016 4:22 AM
To: user@hive.apache.org<ma...@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







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

Posted by mahender bigdata <Ma...@outlook.com>.
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:dmarkovitz@paypal.com]
> *Sent:* Tuesday, May 03, 2016 8:36 PM
> *To:* user@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 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
>
> *From:*mahender bigdata [mailto:Mahender.BigData@outlook.com]
> *Sent:* Tuesday, May 03, 2016 4:02 PM
> *To:* user@hive.apache.org <ma...@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:Mahender.BigData@outlook.com]
>     *Sent:* Tuesday, May 03, 2016 4:22 AM
>     *To:* user@hive.apache.org <ma...@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
>


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

Posted by "Markovitz, Dudu" <dm...@paypal.com>.
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:dmarkovitz@paypal.com]
Sent: Tuesday, May 03, 2016 8:36 PM
To: user@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:Mahender.BigData@outlook.com]
Sent: Tuesday, May 03, 2016 4:02 PM
To: user@hive.apache.org<ma...@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:Mahender.BigData@outlook.com]
Sent: Tuesday, May 03, 2016 4:22 AM
To: user@hive.apache.org<ma...@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






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

Posted by "Markovitz, Dudu" <dm...@paypal.com>.
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:Mahender.BigData@outlook.com]
Sent: Tuesday, May 03, 2016 4:02 PM
To: user@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:Mahender.BigData@outlook.com]
Sent: Tuesday, May 03, 2016 4:22 AM
To: user@hive.apache.org<ma...@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






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

Posted by mahender bigdata <Ma...@outlook.com>.
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:Mahender.BigData@outlook.com]
> *Sent:* Tuesday, May 03, 2016 4:22 AM
> *To:* user@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
>


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

Posted by "Markovitz, Dudu" <dm...@paypal.com>.
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:Mahender.BigData@outlook.com]
Sent: Tuesday, May 03, 2016 4:22 AM
To: user@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