Mailing-List: contact user-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@hive.apache.org
User-Agent: Microsoft-MacOutlook/14.5.1.150515
Date: Thu, 04 Jun 2015 14:55:58 -0700
Subject: Re: Top N query
From: Gopal Vijayaraghavan <gopalv@apache.org>
Sender: Gopal Vijayaraghavan <gopal@hortonworks.com>
To: "user@hive.apache.org" <user@hive.apache.org>
Message-ID: <D195DA19.2D4FC%gopal@hortonworks.com>
Thread-Topic: Top N query
References: 
 <CAAiXLGm_QbBX9bgc9P86B0ZMmDzaxHHsHhx7=JbDfWCpFL9uHg@mail.gmail.com>
 <DB3PR07MB203FF240DE7A1F0CC6FDCAEA3B40@DB3PR07MB203.eurprd07.prod.outlook.com>
 <CAAiXLGmSV_u9sTUtfajaYVyN1bRnXtt8GLSwQJk0CqGzfqR=aA@mail.gmail.com>
 <DB3PR07MB2031C64993D3A4758D6ECE7A3B40@DB3PR07MB203.eurprd07.prod.outlook.com>
 <D19476C2.2CE0C%gopal@hortonworks.com>
 <CAAiXLGkATgWW=epU2_U=sF_qzmwHqFRrfjDFZLyRZJJ7+bvkng@mail.gmail.com>
In-Reply-To: 
 <CAAiXLGkATgWW=epU2_U=sF_qzmwHqFRrfjDFZLyRZJJ7+bvkng@mail.gmail.com>
Mime-version: 1.0
Content-type: text/plain;
	charset="US-ASCII"
Content-transfer-encoding: 7bit


> I does't understand about your solution. Could you please provide exact
>query?

...
 GROUP BY recommend_ratings2.movieid  ORDER BY Measure_0 DESC limit 2 ) T_0
ON ( recommend_ratings2.movieid <=> T_0.movieid_0)


Follow the docs on the mysql impl of the space-ship operator -
<https://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator
_equal-to>

The hive impl follows the same rules which is (a.x = b.x) OR (a.x is NULL
and b.x is NULL).

AFAIK, most of the complex optimizations are turned off in the presence of
null-safe operators.

Cheers,
Gopal