feat: support TPC-H Q3

[skyzh]

TODO for supporting TPC-H Q3

• Fix varchar to varchar casting fix: casting between different varchar length #228
• Support decimal type feat: add Decimal type #235
• Support date type
• Case-insensitive column/table name
• Support order by on expr alias query: support order by on expr alias #258
• Add testcase
• Support implicit type casting (query: support implicit type cast #280)
• Support date comparison
• Modify sqllogictest to support multi-line query in TPC-H
• Support aggr with alias (bug: agg plan is not generated when using aggr with alias #303)
• Support binop on decimal
• Generate input ref for group key
• Support typed string
• Load TPC-H data from risingwave

[a9QrX3Lu]

Related tables

CREATE TABLE CUSTOMER (
    C_CUSTKEY     INTEGER NOT NULL,
    C_NAME        VARCHAR(25) NOT NULL,
    C_ADDRESS     VARCHAR(40) NOT NULL,
    C_NATIONKEY   INTEGER NOT NULL,
    C_PHONE       CHAR(15) NOT NULL,
    C_ACCTBAL     DECIMAL(15,2)   NOT NULL,
    C_MKTSEGMENT  CHAR(10) NOT NULL,
    C_COMMENT     VARCHAR(117) NOT NULL
);

CREATE TABLE ORDERS (
    O_ORDERKEY       INTEGER NOT NULL,
    O_CUSTKEY        INTEGER NOT NULL,
    O_ORDERSTATUS    CHAR(1) NOT NULL,
    O_TOTALPRICE     DECIMAL(15,2) NOT NULL,
    O_ORDERDATE      DATE NOT NULL,
    O_ORDERPRIORITY  CHAR(15) NOT NULL,
    O_CLERK          CHAR(15) NOT NULL,
    O_SHIPPRIORITY   INTEGER NOT NULL,
    O_COMMENT        VARCHAR(79) NOT NULL
);

CREATE TABLE LINEITEM (
    L_ORDERKEY    INTEGER NOT NULL,
    L_PARTKEY     INTEGER NOT NULL,
    L_SUPPKEY     INTEGER NOT NULL,
    L_LINENUMBER  INTEGER NOT NULL,
    L_QUANTITY    DECIMAL(15,2) NOT NULL,
    L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
    L_DISCOUNT    DECIMAL(15,2) NOT NULL,
    L_TAX         DECIMAL(15,2) NOT NULL,
    L_RETURNFLAG  CHAR(1) NOT NULL,
    L_LINESTATUS  CHAR(1) NOT NULL,
    L_SHIPDATE    DATE NOT NULL,
    L_COMMITDATE  DATE NOT NULL,
    L_RECEIPTDATE DATE NOT NULL,
    L_SHIPINSTRUCT CHAR(25) NOT NULL,
    L_SHIPMODE     CHAR(10) NOT NULL,
    L_COMMENT      VARCHAR(44) NOT NULL
);

TPC-H Q3

select
  l_orderkey,
  sum(l_extendedprice * (1 - l_discount)) as revenue,
  o_orderdate,
  o_shippriority
from
  customer,
  orders,
  lineitem
where
  c_mktsegment = 'BUILDING'
  and c_custkey = o_custkey
  and l_orderkey = o_orderkey
  and o_orderdate < '1995-03-15'
  and l_shipdate > '1995-03-15'
group by
  l_orderkey,
  o_orderdate,
  o_shippriority
order by
  revenue desc,
  o_orderdate,
  l_orderkey
limit 10

[skyzh]

I've moved your todo lists to the issue body so that we can track them easily in issue list. By the way, do we really need to implement Decimal type in our system? Is it possible to use i64? (or even f64)?

[a9QrX3Lu]

I've moved your todo lists to the issue body so that we can track them easily in issue list. By the way, do we really need to implement Decimal type in our system? Is it possible to use i64? (or even f64)?

Doesn't this violate the semantics of Decimal? If we do not handle decimal in our system, users can get return values with scale larger than the scale they declared in decimal type.

[skyzh]

Sounds reasonable to me. You may also take a look at RisingWave's array implementation and #124, so as to write less code when adding new types.

The only concern is that, to add Decimal or DateTime type, the storage will need a new set of encoder and decoders. Both of them are 128bits (16B) in memory, which cannot be represented by any of our current supported types.

[skyzh]

Maybe we can implement FixedWidthEncode for Decimal and DateTime. As they are fixed-width (128B) and simple, we may only need little effort to support them.