[SPARK-18863] Output non-aggregate expressions without GROUP BY in a subquery does not yield an error

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.0.0
• Fix Version/s: 2.0.3, 2.1.1, 2.2.0
• Component/s: SQL
• Assignee: Nattavut Sutyanyong
• Reporter: Nattavut Sutyanyong
• Created: 14/Dec/16 16:01
• Updated: 25/Jan/17 16:10
• Resolved: 25/Jan/17 16:10

Description

Xiao Li has found that the following query does not raise a syntax error (note the GROUP BY clause is commented out):

SELECT pk, cv
FROM   p, c
WHERE  p.pk = c.ck
AND    c.cv = (SELECT max(avg)
               FROM   (SELECT   c1.cv, avg(c1.cv) avg
                       FROM     c c1
                       WHERE    c1.ck = p.pk
--                       GROUP BY c1.cv
                      ))

There could be multiple values of c1.cv for each value of avg(c1.cv).

Issue Links

• relates to: SPARK-19047 Invalid correlated column may not be reported as an error
• links to: [Github] Pull Request #16572 (nsyca)