[jira] [Created] (ASTERIXDB-2772) Exception thrown for explicit join subquery on group

["Glenn Galvizo (Jira)" <ji...@apache.org>]

Glenn Galvizo created ASTERIXDB-2772:
----------------------------------------

             Summary: Exception thrown for explicit join subquery on group
                 Key: ASTERIXDB-2772
                 URL: https://issues.apache.org/jira/browse/ASTERIXDB-2772
             Project: Apache AsterixDB
          Issue Type: Bug
    Affects Versions: 0.9.5
            Reporter: Glenn Galvizo
         Attachments: shopalot.tar.gz

(Using the BOSS data, attached below).

Running the following query:
{code:java}
SELECT O.user_id,
 (
 SELECT VALUE P.name
 FROM OI
 INNER JOIN Products P ON P.product_id = OI.I.product_id
 ) AS product_names
FROM Orders O
UNNEST O.items I
WHERE I.selling_price > 15
GROUP BY O.user_id
GROUP AS OI
HAVING SUM(O.total_price) BETWEEN 35 AND 50;{code}
Gives the error:
{code:java}
ASX1038: Illegal state. []:[$P] (in line 11, at column 1) [CompilationException] {code}
However, rewriting the join using a cross-product + a WHERE clause instead of an explicit JOIN succeeds:
{code:java}
USE ShopALot;
SELECT O.user_id,
 (
 SELECT VALUE P.name
 FROM OI, Products P
 WHERE P.product_id = OI.I.product_id
 ) AS product_names
FROM Orders O
UNNEST O.items I
WHERE I.selling_price > 15
GROUP BY O.user_id
GROUP AS OI
HAVING SUM(O.total_price) BETWEEN 35 AND 50;{code}
 

 

 



--
This message was sent by Atlassian Jira
(v8.3.4#803005)