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....@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....@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 >> > > >
