Analytics GROUP BY behaviour (duplicate alias)

n1ql

#1

I’m having some trouble understanding the correct behaviour for SQL++ in CB Analytics.

I was trying to run a query where I aggregate and group things together. Two of the fields were the same as it was a self join type thing. An example of the type of query I was trying to run is below. This threw an error (“msg”: “Duplicate alias definitions: description”). I tried it in N1QL, however, and it worked as is.

SELECT g.description AS account_name
     , p.description AS parent_account_name
  FROM `bucketname` g
  LEFT JOIN `bucketname` p 
    ON g.parent_account_name = p.name
   AND p.`type` = 'group'
 WHERE g.`type` = 'group'
 GROUP BY g.description 
     , p.description 

I tried a few things, including the below query which turned out to work in Analytics without throwing the error seen before. It didn’t work in N1QL as the following error was raised (“msg”: “syntax error - at AS”), which is expected.

SELECT g.description AS account_name
     , p.description AS parent_account_name
  FROM `bucketname` g
  LEFT JOIN `bucketname` p 
    ON g.parent_account_name = p.name
   AND p.`type` = 'group'
 WHERE g.`type` = 'group'
 GROUP BY g.description AS account_name
     , p.description AS parent_account_name

The behaviour in N1QL is what I’d expect for both but Analytics seems to be different. Is there a reason for this or is it a possible bug?

Thanks!


#2

N1QL doesn’t allow aliasing in GROUP BY clause MB-28449


#3

Yes I’m aware of that and it makes sense to me, as that’s how I learned to write SQL in a relational database.
My confusion is why Analytics works differently. Why is it that I must add the aliases into the GROUP BY clause in Analytics?


#4

This is a bug in Analytics. GROUP BY aliases should not be required for this query to run. The issue has been filed to track it: https://issues.couchbase.com/browse/MB-30477