Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

frontend: support having clause in binder & planner #2166

Closed
Tracked by #338
fuyufjh opened this issue Apr 27, 2022 · 0 comments · Fixed by #2273
Closed
Tracked by #338

frontend: support having clause in binder & planner #2166

fuyufjh opened this issue Apr 27, 2022 · 0 comments · Fixed by #2273
Labels
component/frontend Protocol, parsing, binder. type/feature

Comments

@fuyufjh
Copy link
Contributor

fuyufjh commented Apr 27, 2022

TPC-H Q11:

select
	ps_partkey,
	round(sum(ps_supplycost * ps_availqty), 1) as value
from
	partsupp,
	supplier,
	nation
where
	ps_suppkey = s_suppkey
	and s_nationkey = n_nationkey
	and n_name = 'ARGENTINA'
group by
	ps_partkey
having
    sum(ps_supplycost * ps_availqty) > (
        select
            sum(ps_supplycost * ps_availqty) * 0.0001000000
        from
            partsupp,
            supplier,
            nation
        where
            ps_suppkey = s_suppkey
            and s_nationkey = n_nationkey
            and n_name = 'ARGENTINA'
    )
order by
	value desc;

the having clause is ignored. we have not support to bind and plan having clause yet.

- id: tpch_q11
  sql: |
    select
      ps_partkey,
      sum(ps_supplycost * ps_availqty) as value
    from
      partsupp,
      supplier,
      nation
    where
      ps_suppkey = s_suppkey
      and s_nationkey = n_nationkey
      and n_name = 'ARGENTINA'
    group by
      ps_partkey having
        sum(ps_supplycost * ps_availqty) > (
          select
            sum(ps_supplycost * ps_availqty) * 0.0001000000
          from
            partsupp,
            supplier,
            nation
          where
            ps_suppkey = s_suppkey
            and s_nationkey = n_nationkey
            and n_name = 'ARGENTINA'
        )
    order by
      value desc;
  logical_plan: |
    LogicalProject { exprs: [$0, $1], expr_alias: [ps_partkey, value] }
      LogicalAgg { group_keys: [0], agg_calls: [sum($1)] }
        LogicalProject { exprs: [$1, ($4 * $3)], expr_alias: [ ,  ] }
          LogicalFilter { predicate: ($2 = $7) AND ($10 = $15) AND ($16 = 'ARGENTINA':Varchar) }
            LogicalJoin { type: Inner, on: always }
              LogicalJoin { type: Inner, on: always }
                LogicalScan { table: partsupp, columns: [_row_id#0, ps_partkey, ps_suppkey, ps_availqty, ps_supplycost, ps_comment] }
                LogicalScan { table: supplier, columns: [_row_id#0, s_suppkey, s_name, s_address, s_nationkey, s_phone, s_acctbal, s_comment] }
              LogicalScan { table: nation, columns: [_row_id#0, n_nationkey, n_name, n_regionkey, n_comment] }

Originally posted by @st1page in #2152 (comment)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
component/frontend Protocol, parsing, binder. type/feature
Projects
None yet
Development

Successfully merging a pull request may close this issue.

1 participant