The Q4 of TPC-H cannot be pushed down to TiFlash. #60991
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
func TestQ4(t *testing.T) {
store, dom := testkit.CreateMockStoreAndDomain(t)
tk := testkit.NewTestKit(t, store)
tk.MustExec("use test")
tk.MustExec(`
CREATE TABLE orders (
O_ORDERKEY bigint NOT NULL,
O_CUSTKEY bigint 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 bigint NOT NULL,
O_COMMENT varchar(79) NOT NULL,
PRIMARY KEY (O_ORDERKEY) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;`)
tk.MustExec(`
CREATE TABLE lineitem (
L_ORDERKEY bigint NOT NULL,
L_PARTKEY bigint NOT NULL,
L_SUPPKEY bigint NOT NULL,
L_LINENUMBER bigint 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,
PRIMARY KEY (L_ORDERKEY, L_LINENUMBER) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
`)
testkit.SetTiFlashReplica(t, dom, "test", "orders")
testkit.SetTiFlashReplica(t, dom, "test", "lineitem")
tk.MustQuery(`explain select
o_orderpriority,
count(*) as order_count
from
orders
where
o_orderdate >= '1995-01-01'
and o_orderdate < date_add('1995-01-01', interval '3' month)
and exists (
select *
from
lineitem
where
l_orderkey = o_orderkey
and l_commitdate < l_receiptdate )
group by o_orderpriority
order by o_orderpriority`).Check(testkit.Rows())
}
2. What did you expect to see? (Required)
+[Sort_10 160.00 root test.orders.o_orderpriority]
+[└─Projection_12 160.00 root test.orders.o_orderpriority, Column#26]
+[ └─HashAgg_16 160.00 root group by:test.orders.o_orderpriority, funcs:count(1)->Column#26, funcs:firstrow(test.orders.o_orderpriority)->test.orders.o_orderpriority]
+[ └─IndexJoin_29 200.00 root semi join, inner:TableReader_25, left side:TableReader_59, outer key:test.orders.o_orderkey, inner key:test.lineitem.l_orderkey, equal cond:eq(test.orders.o_orderkey, test.lineitem.l_orderkey)]
+[ ├─TableReader_59(Build) 250.00 root MppVersion: 3, data:ExchangeSender_58]
+[ │ └─ExchangeSender_58 250.00 mpp[tiflash] ExchangeType: PassThrough]
+[ │ └─TableFullScan_56 250.00 mpp[tiflash] table:orders pushed down filter:ge(test.orders.o_orderdate, 1995-01-01 00:00:00.000000), lt(test.orders.o_orderdate, 1995-04-01 00:00:00.000000), keep order:false, stats:pseudo]
+[ └─TableReader_25(Probe) 200.00 root data:Selection_24]
+[ └─Selection_24 200.00 cop[tiflash] lt(test.lineitem.l_commitdate, test.lineitem.l_receiptdate)]
+[ └─TableRangeScan_23 250.00 cop[tiflash] table:lineitem range: decided by [eq(test.lineitem.l_orderkey, test.orders.o_orderkey)], keep order:false, stats:pseudo]
3. What did you see instead (Required)
+[Sort_10 160.00 root test.orders.o_orderpriority]
+[└─Projection_12 160.00 root test.orders.o_orderpriority, Column#26]
+[ └─HashAgg_16 160.00 root group by:test.orders.o_orderpriority, funcs:count(1)->Column#26, funcs:firstrow(test.orders.o_orderpriority)->test.orders.o_orderpriority]
+[ └─IndexJoin_29 200.00 root semi join, inner:TableReader_25, left side:TableReader_59, outer key:test.orders.o_orderkey, inner key:test.lineitem.l_orderkey, equal cond:eq(test.orders.o_orderkey, test.lineitem.l_orderkey)]
+[ ├─TableReader_59(Build) 250.00 root MppVersion: 3, data:ExchangeSender_58]
+[ │ └─ExchangeSender_58 250.00 mpp[tiflash] ExchangeType: PassThrough]
+[ │ └─TableFullScan_56 250.00 mpp[tiflash] table:orders pushed down filter:ge(test.orders.o_orderdate, 1995-01-01 00:00:00.000000), lt(test.orders.o_orderdate, 1995-04-01 00:00:00.000000), keep order:false, stats:pseudo]
+[ └─TableReader_25(Probe) 200.00 root data:Selection_24]
+[ └─Selection_24 200.00 cop[tikv] lt(test.lineitem.l_commitdate, test.lineitem.l_receiptdate)]
+[ └─TableRangeScan_23 250.00 cop[tikv] table:lineitem range: decided by [eq(test.lineitem.l_orderkey, test.orders.o_orderkey)], keep order:false, stats:pseudo]