TPC-H Query 22

[alamb]

Note: migrated from original JIRA: https://issues.apache.org/jira/browse/ARROW-11543

Fails with parser error for the syntax SUBSTRING(col FROM 1)

[alamb]

Comment from Mike Seddon(MikeSeddonAU) @ 2021-02-08T21:23:39.672+0000:

I have added this in the Postgres string functions but if I put a link here the comment gets removed. It will need to be changed to the Postgres syntax:

substr('alphabet', 3, 2)

Comment from Mike Seddon(MikeSeddonAU) @ 2021-02-08T21:24:31.632+0000:

See ARROW-11298 which has the implementation ready for review.

[andygrove]

Latest failure:

Error: NotImplemented("Physical plan does not support logical expression (Subquery: Projection: #AVG(customer.c_acctbal)\n  Aggregate: groupBy=[[]], aggr=[[AVG(#customer.c_acctbal)]]\n    Filter: #customer.c_acctbal > Float64(0) AND substr(#customer.c_phone, Int64(1), Int64(2)) IN ([Utf8(\"13\"), Utf8(\"31\"), Utf8(\"23\"), Utf8(\"29\"), Utf8(\"30\"), Utf8(\"18\"), Utf8(\"17\")])\n      TableScan: customer projection=None)")

[avantgardnerio]

Working on this branch:

+-----------+---------+-------------------+
| cntrycode | numcust | totacctbal        |
+-----------+---------+-------------------+
| 13        | 888     | 6737713.989999997 |
| 17        | 861     | 6460573.719999999 |
| 18        | 964     | 7236687.399999998 |
| 23        | 892     | 6701457.949999994 |
| 29        | 948     | 7158866.630000005 |
| 30        | 909     | 6808436.13        |
| 31        | 922     | 6806670.18        |
+-----------+---------+-------------------+
Query 22 iteration 2 took 14523.9 ms and returned 7 rows
Query 22 avg time: 14616.16 ms

vs postgres:

+---------+-------+------------------+
|cntrycode|numcust|totacctbal        |
+---------+-------+------------------+
|13       |888    |6737713.99000001  |
|17       |861    |6460573.7200000025|
|18       |964    |7236687.400000007 |
|23       |892    |6701457.950000012 |
|29       |948    |7158866.629999992 |
|30       |909    |6808436.129999999 |
|31       |922    |6806670.179999995 |
+---------+-------+------------------+