hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Premal Shah <premal.j.s...@gmail.com>
Subject Re: Hive on Tez CTAS query breaks
Date Sat, 12 Nov 2016 09:12:57 GMT
Sergey and Gopal,
Tried it will the alias. Still breaks.

CREATE TABLE unique_ip_tmp AS
SELECT DISTINCT
    new.ip
FROM
    (
        SELECT
            COUNT(0) as count ,
            ip as ip
        FROM t1
        WHERE dt BETWEEN '2016-11-08' AND '2016-11-08'
        GROUP BY ip

        UNION ALL

        SELECT
            COUNT(0) as count ,
            ip as ip
        FROM t2
        WHERE dt BETWEEN '2016-11-08' AND '2016-11-08'
        GROUP BY ip
    ) new
    LEFT JOIN unique_ip old
        ON old.ip = new.ip
WHERE
    old.ip IS NULL
;


Gopal.
Filed the ticket - https://issues.apache.org/jira/browse/HIVE-15187

Thanx guys.


On Fri, Nov 11, 2016 at 1:08 PM, Gopal Vijayaraghavan <gopalv@apache.org>
wrote:

> > Thanx for the suggestion. It works with the setting you suggested.
> >
> > What does this mean? Do I need to special case this query.
>
> You need to report a bug on https://issues.apache.org/jira/browse/HIVE
>
> Because, this needs to get fixed.
>
> > Turning off CBO cluster-wide won't be the right thing to do, would it?
>
> Nope, CBO is a good thing - a workaround would be to try what Sergey
> suggested, the '$f0' is probably the auto-generated name for the count(0).
>
> Naming that column explicitly on both branches of the UNION ALL, might get
> CBO back up.
>
> Cheers,
> Gopal
>
>
>


-- 
Regards,
Premal Shah.

Mime
View raw message