Re: Issue with correlated subqueries being case-sensitive

[jack]

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 <http://P.ID> 
= B.ID <http://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/

http://talebzadehmich.wordpress.com <http://talebzadehmich.wordpress.com/>


On 29 April 2016 at 14:07, jack <hieutd....@gmail.com 
<mailto: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
    <http://P.ID> = B.ID <http://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
    <http://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