hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mich Talebzadeh <mich.talebza...@gmail.com>
Subject Re: Issue with correlated subqueries being case-sensitive
Date Fri, 29 Apr 2016 13:52:53 GMT
yes sounds like a bug in parser. I am using Hive 2

hive> select count(1) from smallsales where exists(select 1 from
sales_staging where smallsales.PROD_ID = sales_staging.PROD_ID);
FAILED: SemanticException [Error 10250]: Line 1:59 Invalid SubQuery
expression 'PROD_ID': For Exists/Not Exists operator SubQuery must be
Correlated.

As a work around This works but not that efficient
hive> select count(1) from smallsales where PROD_ID IN (SELECT PROD_ID FROM
sales_staging);


HTH

Dr Mich Talebzadeh



LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*



http://talebzadehmich.wordpress.com



On 29 April 2016 at 14:31, jack <hieutd.ict@gmail.com> wrote:

> Hi,
>
> It is just a string literal I used as an example. Replacing it with "1"
> does not affect the issue in any way
>
> Best regards,
> /jack
>
>
> On 04/29/2016 03:24 PM, Mich Talebzadeh wrote:
>
> Why not just try the standard way
>
> SELECT * FROM P WHERE EXISTS(SELECT 1 FROM B WHERE P.ID = B.ID)
>
> You don't need '*' that is not standard SQL as far as I know
>
> HTH
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>
>
>
> <http://talebzadehmich.wordpress.com/>http://talebzadehmich.wordpress.com
>
>
>
> On 29 April 2016 at 14:07, jack <hieutd.ict@gmail.com> wrote:
>
>> Hi,
>>
>> I am having an issue with correlated sub-queries such as the following
>>
>> SELECT * FROM P WHERE EXISTS (SELECT '*' FROM B WHERE P.ID = B.ID)
>>
>> Both Beeline and Java JDBC client falied with the following message
>>
>> Error: Error while compiling statement: FAILED: SemanticException [Error
>> 10250]: Line 1:51 Invalid SubQuery expression 'ID': For Exists/Not Exists
>> operator SubQuery must be Correlated. (state=42000,code=10250)
>>
>> However the query succeeds if the cross-referenced column is referred to
>> using lowercase. Note that all other references are case-insensitive and I
>> can mix the case in whichever way I want.
>>
>> SELECT * FROM P WHERE EXISTS (SELECT '*' FROM B WHERE P.id = B.ID)
>>
>> The expected behavior for me will be that all identifiers are
>> case-insensitive.
>>
>> Is it a bug or am I missing something here?
>>
>> HDFS version: 2.7.2, rb165c4fe8a74265c792ce23f546c64604acf0e41
>>
>> Hive Version 2.0.0, r7f9f1fcb8697fb33f0edc2c391930a3728d247d7 with Derby
>> metastore
>> Hive JDBC driver: hive-jdbc-2.0.0.2.3.5.0-81-standalone.jar
>>
>>
>> Best regards,
>> /jack
>>
>
>
>

Mime
View raw message