Support TPCH q22

[BohuTANG]

Summary
TPCH q22:

select
    cntrycode,
    count(*) as numcust,
    sum(c_acctbal) as totacctbal
from
    (
        select
            substring(c_phone from 1 for 2) as cntrycode,
            c_acctbal
        from
            customer
        where
                substring(c_phone from 1 for 2) in
                ('13', '31', '23', '29', '30', '18', '17')
          and c_acctbal > (
            select
                avg(c_acctbal)
            from
                customer
            where
                    c_acctbal > 0.00
              and substring(c_phone from 1 for 2) in
                  ('13', '31', '23', '29', '30', '18', '17')
        )
          and not exists (
                select
                    *
                from
                    orders
                where
                        o_custkey = c_custkey
            )
    ) as custsale
group by
    cntrycode
order by
    cntrycode;

[tlightsky]

I can try with this one:

select substring('abc' from 1 for 2) x;

+-----+
| x   |
+-----+
| ab  |
+-----+

[BohuTANG]

@tlightsky

substring is not implemented in Datafuse yet:

mysql> select substring('abc' from 1 for 2) x;
ERROR 1105 (HY000): Code: 5, displayText = Unsupported expression: SUBSTRING('abc' FROM 1 FOR 2), type: Substring { expr: Value(SingleQuotedString("abc")), substring_from: Some(Value(Number("1", false))), substring_for: Some(Value(Number("2", false))) }.

[tlightsky]

yeah, I mean I'll try to implement it, LOL

[tlightsky]

this issue could be closed

[BohuTANG]

Oh, this issue mainly for TPCH q22 not only substring.
There will be a suite benchmark for TPCH queries, this issue is one of them

[tlightsky]

ah, got it

[Xuanwo]

Based on the report at #6060, TPCH Q22 has been supported.

Feel free to reopen this issue if any problem exists.