Uploaded image for project: 'Couchbase Server'
  1. Couchbase Server
  2. MB-28625

Can't project a group by attribute as part of an expression

    XMLWordPrintable

Details

    • Bug
    • Resolution: User Error
    • Critical
    • 5.5.0
    • 5.5.0
    • query
    • None
    • Untriaged
    • Unknown

    Description

      The following query on the beer-sample return error:

      SELECT "Mybeer": "My Beer is " || beer.name || ".",
      Array_agg({"name":brewery.name}) brewery,
      Array_agg({"name":brewery.name, "state":brewery.state, "city":brewery.city, "location":brewery.geo}) locations,
      Array_count(Array_agg(brewery.name)) AS brewery_count
      FROM `beer-sample` brewery LEFT OUTER JOIN `beer-sample` beer
      ON meta(brewery).id = beer.brewery_id
      WHERE beer.type = 'beer'
      AND brewery.type = 'brewery'
      AND brewery.state = 'California'
      GROUP BY beer.name
      ORDER BY array_count(array_agg(brewery.name)) DESC,
      beer.name ASC limit 5;

      I get the following error:

      [
      {
      "code": 3000,
      "msg": "syntax error - at :",
      "query_from_user": "SELECT \"Mybeer\": \"My Beer is \" || beer.name || \".\", \nArray_agg({\"name\":brewery.name}) brewery, \nArray_agg({\"name\":brewery.name, \"state\":brewery.state, \"city\":brewery.city, \"location\":brewery.geo}) locations, \nArray_count(Array_agg(brewery.name)) AS brewery_count\nFROM `beer-sample` brewery LEFT OUTER JOIN `beer-sample` beer\nON meta(brewery).id = beer.brewery_id\nWHERE beer.type = 'beer'\nAND brewery.type = 'brewery'\nAND brewery.state = 'California'\nGROUP BY beer.name\nORDER BY array_count(array_agg(brewery.name)) DESC,\nbeer.name ASC limit 5;"
      }
      ]

      If I only project beer.name, then it works

      SELECT beer.name,
      Array_agg({"name":brewery.name}) brewery,
      Array_agg({"name":brewery.name, "state":brewery.state, "city":brewery.city, "location":brewery.geo}) locations,
      Array_count(Array_agg(brewery.name)) AS brewery_count
      FROM `beer-sample` brewery LEFT OUTER JOIN `beer-sample` beer
      ON meta(brewery).id = beer.brewery_id
      WHERE beer.type = 'beer'
      AND brewery.type = 'brewery'
      AND brewery.state = 'California'
      GROUP BY beer.name
      ORDER BY array_count(array_agg(brewery.name)) DESC,
      beer.name ASC limit 5;

      Attachments

        No reviews matched the request. Check your Options in the drop-down menu of this sections header.

        Activity

          People

            Sitaram.Vemulapalli Sitaram Vemulapalli
            tai.tran Tai Tran (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty