asterixdb/asterix-app/src/test/resources/optimizerts/results_parser_sqlpp/nest_aggregate.ast

DataverseUse tpch
TypeDecl OrderType [
  closed RecordType {
    o_orderkey : integer,
    o_custkey : integer,
    o_orderstatus : string,
    o_totalprice : double,
    o_orderdate : string,
    o_orderpriority : string,
    o_clerk : string,
    o_shippriority : integer,
    o_comment : string
  }
]
TypeDecl CustomerType [
  closed RecordType {
    c_custkey : integer,
    c_name : string,
    c_address : string,
    c_nationkey : integer,
    c_phone : string,
    c_acctbal : double,
    c_mktsegment : string,
    c_comment : string
  }
]
TypeDecl SupplierType [
  closed RecordType {
    s_suppkey : integer,
    s_name : string,
    s_address : string,
    s_nationkey : integer,
    s_phone : string,
    s_acctbal : double,
    s_comment : string
  }
]
TypeDecl NationType [
  closed RecordType {
    n_nationkey : integer,
    n_name : string,
    n_regionkey : integer,
    n_comment : string
  }
]
TypeDecl RegionType [
  closed RecordType {
    r_regionkey : integer,
    r_name : string,
    r_comment : string
  }
]
TypeDecl PartType [
  closed RecordType {
    p_partkey : integer,
    p_name : string,
    p_mfgr : string,
    p_brand : string,
    p_type : string,
    p_size : integer,
    p_container : string,
    p_retailprice : double,
    p_comment : string
  }
]
TypeDecl PartSuppType [
  closed RecordType {
    ps_partkey : integer,
    ps_suppkey : integer,
    ps_availqty : integer,
    ps_supplycost : double,
    ps_comment : string
  }
]
DatasetDecl Orders(OrderType) partitioned by [[o_orderkey]]
DatasetDecl Supplier(SupplierType) partitioned by [[s_suppkey]]
DatasetDecl Region(RegionType) partitioned by [[r_regionkey]]
DatasetDecl Nation(NationType) partitioned by [[n_nationkey]]
DatasetDecl Part(PartType) partitioned by [[p_partkey]]
DatasetDecl Partsupp(PartSuppType) partitioned by [[ps_partkey], [ps_suppkey]]
DatasetDecl Customer(CustomerType) partitioned by [[c_custkey]]
DatasetDecl SelectedNation(NationType) partitioned by [[n_nationkey]]
Query:
SELECT ELEMENT [
RecordConstructor [
  (
    LiteralExpr [STRING] [nation_key]
    :
    FieldAccessor [
      Variable [ Name=$nation ]
      Field=n_nationkey
    ]
  )
  (
    LiteralExpr [STRING] [name]
    :
    FieldAccessor [
      Variable [ Name=$nation ]
      Field=n_name
    ]
  )
  (
    LiteralExpr [STRING] [aggregates]
    :
    (
      SELECT ELEMENT [
      RecordConstructor [
        (
          LiteralExpr [STRING] [order_date]
          :
          Variable [ Name=$orderdate ]
        )
        (
          LiteralExpr [STRING] [sum_price]
          :
          Variable [ Name=$sum ]
        )
      ]
      ]
      FROM [        FunctionCall Metadata.dataset@1[
          LiteralExpr [STRING] [Orders]
        ]
        AS Variable [ Name=$orders ]
,
        FunctionCall Metadata.dataset@1[
          LiteralExpr [STRING] [Customer]
        ]
        AS Variable [ Name=$customer ]
      ]
      Where
        OperatorExpr [
          OperatorExpr [
            FieldAccessor [
              Variable [ Name=$orders ]
              Field=o_custkey
            ]
            =
            FieldAccessor [
              Variable [ Name=$customer ]
              Field=c_custkey
            ]
          ]
          and
          OperatorExpr [
            FieldAccessor [
              Variable [ Name=$customer ]
              Field=c_nationkey
            ]
            =
            FieldAccessor [
              Variable [ Name=$nation ]
              Field=n_nationkey
            ]
          ]
        ]
      Groupby
        Variable [ Name=$orderdate ]
        :=
        FieldAccessor [
          Variable [ Name=$orders ]
          Field=o_orderdate
        ]
        GROUP AS Variable [ Name=#1 ]
        (
          orders:=Variable [ Name=$orders ]
          customer:=Variable [ Name=$customer ]
        )

      Let Variable [ Name=$sum ]
        :=
        FunctionCall tpch.sum@1[
          (
            SELECT ELEMENT [
            FieldAccessor [
              Variable [ Name=$o ]
              Field=o_totalprice
            ]
            ]
            FROM [              Variable [ Name=$orders ]
              AS Variable [ Name=$o ]
            ]
          )
        ]
      Orderby
        Variable [ Name=$sum ]
        ASC

      Limit
        LiteralExpr [LONG] [3]
    )
  )
]
]
FROM [  FunctionCall Metadata.dataset@1[
    LiteralExpr [STRING] [Nation]
  ]
  AS Variable [ Name=$nation ]
,
  FunctionCall Metadata.dataset@1[
    LiteralExpr [STRING] [SelectedNation]
  ]
  AS Variable [ Name=$sn ]
]
Where
  OperatorExpr [
    FieldAccessor [
      Variable [ Name=$nation ]
      Field=n_nationkey
    ]
    =
    FieldAccessor [
      Variable [ Name=$sn ]
      Field=n_nationkey
    ]
  ]