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