[SPARK-19035] rand() function in case when cause failed

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Incomplete
• Affects Version/s: 2.0.0, 2.0.1, 2.0.2
• Component/s: SQL
• Labels: bulk-closed
• Assignee: Unassigned
• Reporter: 袁枫
• Created: 30/Dec/16 07:06
• Updated: 21/May/19 04:17
• Resolved: 21/May/19 04:17

Description

In this case:
select
case when a=1 then 1 else concat(a,cast(rand() as string)) end b,count(1)
from
yuanfeng1_a
group by
case when a=1 then 1 else concat(a,cast(rand() as string)) end;

Throw error:
Error in query: expression 'yuanfeng1_a.`a`' is neither present in the group by, nor is it an aggregate function. Add to group by or wrap in first() (or first_value) if you don't care which value you get.;;
Aggregate CASE WHEN (a#2075 = 1) THEN cast(1 as string) ELSE concat(cast(a#2075 as string), cast(rand(519367429988179997) as string)) END, CASE WHEN (a#2075 = 1) THEN cast(1 as string) ELSE concat(cast(a#2075 as string), cast(rand(8090243936131101651) as string)) END AS b#2074
+- MetastoreRelation default, yuanfeng1_a
select case when a=1 then 1 else rand() end b,count(1) from yuanfeng1_a group by case when a=1 then rand() end also output this
Notice:
If replace rand() as 1,it work.

A simpler way to reproduce this bug: `SELECT a + rand() FROM t GROUP BY a + rand()`.

Issue Links

• duplicates: SPARK-18969 PullOutNondeterministic should work for Aggregate operator
• relates to: SPARK-18969 PullOutNondeterministic should work for Aggregate operator