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