TPC-H Query 20

[alamb]

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

IN (Subquery) in (WHERE) expression

Error: NotImplemented("Unsupported ast node InSubquery { expr: Identifier(Ident { value: "s_suppkey", quote_style: None }), subquery: Query { with: None, body: Select(Select { distinct: false, top: None, projection: [UnnamedExpr(Identifier(Ident { value: "ps_suppkey", quote_style: None }))], from: [TableWithJoins { relation: Table { name: ObjectName([Ident { value: "partsupp", quote_style: None }]), alias: None, args: [], with_hints: [] }, joins: [] }], selection: Some(BinaryOp { left: InSubquery { expr: Identifier(Ident { value: "ps_partkey", quote_style: None }), subquery: Query { with: None, body: Select(Select { distinct: false, top: None, projection: [UnnamedExpr(Identifier(Ident { value: "p_partkey", quote_style: None }))], from: [TableWithJoins { relation: Table { name: ObjectName([Ident { value: "part", quote_style: None }]), alias: None, args: [], with_hints: [] }, joins: [] }], selection: Some(BinaryOp { left: Identifier(Ident { value: "p_name", quote_style: None }), op: Like, right: Value(SingleQuotedString("forest%")) }), group_by: [], having: None }), order_by: [], limit: None, offset: None, fetch: None }, negated: false }, op: And, right: BinaryOp { left: Identifier(Ident { value: "ps_availqty", quote_style: None }), op: Gt, right: Subquery(Query { with: None, body: Select(Select { distinct: false, top: None, projection: [UnnamedExpr(BinaryOp { left: Value(Number("0.5")), op: Multiply, right: Function(Function { name: ObjectName([Ident { value: "sum", quote_style: None }]), args: [Unnamed(Identifier(Ident { value: "l_quantity", quote_style: None }))], over: None, distinct: false }) })], from: [TableWithJoins { relation: Table { name: ObjectName([Ident { value: "lineitem", quote_style: None }]), alias: None, args: [], with_hints: [] }, joins: [] }], selection: Some(BinaryOp { left: BinaryOp { left: BinaryOp { left: BinaryOp { left: Identifier(Ident { value: "l_partkey", quote_style: None }), op: Eq, right: Identifier(Ident { value: "ps_partkey", quote_style: None }) }, op: And, right: BinaryOp { left: Identifier(Ident { value: "l_suppkey", quote_style: None }), op: Eq, right: Identifier(Ident { value: "ps_suppkey", quote_style: None }) } }, op: And, right: BinaryOp { left: Identifier(Ident { value: "l_shipdate", quote_style: None }), op: GtEq, right: TypedString { data_type: Date, value: "1994-01-01" } } }, op: And, right: BinaryOp { left: Identifier(Ident { value: "l_shipdate", quote_style: None }), op: Lt, right: BinaryOp { left: Value(SingleQuotedString("date 1994-01-01")), op: Plus, right: Value(Interval { value: "1", leading_field: Some(Year), leading_precision: None, last_field: None, fractional_seconds_precision: None }) } } }), group_by: [], having: None }), order_by: [], limit: None, offset: None, fetch: None }) } }), group_by: [], having: None }), order_by: [], limit: None, offset: None, fetch: None }, negated: false } in sqltorel")

[andygrove]

Latest failure:

Error: NotImplemented("Physical plan does not support logical expression (Subquery: Projection: Float64(0.5) * #SUM(lineitem.l_quantity)\n  Aggregate: groupBy=[[]], aggr=[[SUM(#lineitem.l_quantity)]]\n    Filter: #lineitem.l_partkey = #partsupp.ps_partkey AND #lineitem.l_suppkey = #partsupp.ps_suppkey AND #lineitem.l_shipdate >= CAST(Utf8(\"1994-01-01\") AS Date32) AND #lineitem.l_shipdate < Utf8(\"date 1994-01-01\") + IntervalYearMonth(\"12\")\n      TableScan: lineitem projection=None)")
t

[avantgardnerio]

Working in this branch :

| Supplier#000009812 | APFRMy3lCbgFga53n5t9DxzFPQPgnjrGt32      |
| Supplier#000009846 | 57sNwJJ3PtBDu,hMPP5QvpcOcSNRXn3PypJJrh   |
| Supplier#000009899 | 7XdpAHrzr1t,UQFZE                        |
| Supplier#000009974 | 7wJ,J5DKcxSU4Kp1cQLpbcAvB5AsvKT          |
+--------------------+------------------------------------------+
Query 20 iteration 2 took 85923.1 ms and returned 186 rows
Query 20 avg time: 86933.58 ms