Open
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
CREATE TABLE t1 (
id VARCHAR(64) PRIMARY KEY
);
CREATE TABLE t2 (
c1 VARCHAR(64) NOT NULL,
c2 VARCHAR(64) NOT NULL,
c3 VARCHAR(64) NOT NULL,
PRIMARY KEY (c1, c2, c3),
KEY c3 (c3)
);
CREATE TABLE t3 (
c1 VARCHAR(64) NOT NULL,
c2 VARCHAR(64) NOT NULL,
c3 VARCHAR(64) NOT NULL,
PRIMARY KEY (c1, c2, c3),
KEY c3 (c3)
);
CREATE TABLE t4 (
c1 VARCHAR(64) NOT NULL,
c2 VARCHAR(64) NOT NULL,
c3 VARCHAR(64) NOT NULL,
state VARCHAR(64) NOT NULL DEFAULT 'ACTIVE',
PRIMARY KEY (c1, c2, c3),
KEY c3 (c3)
);
CREATE TABLE t5 (
c1 VARCHAR(64) NOT NULL,
c2 VARCHAR(64) NOT NULL,
PRIMARY KEY (c1, c2)
);
explain format='verbose' SELECT i.id, ip_products.products
FROM t1 AS i
LEFT JOIN t4 ON i.id = t4.c3
LEFT JOIN (
SELECT t4.c3,
GROUP_CONCAT(DISTINCT t2.c3 ORDER BY t2.c3 ASC) AS products
FROM t4
JOIN t3 ON t4.c1 = t3.c1
AND t4.c2 = t3.c2
LEFT JOIN t2 ON t4.c1 = t2.c1
AND t4.c2 = t2.c2
WHERE t3.c3 = 'production'
AND t4.state = 'ACTIVE'
GROUP BY t4.c3, t4.c1, t4.c2
) AS ip_products ON t4.c3 = ip_products.c3
LEFT JOIN t5 ON i.id = t5.c1
AND t5.c2 = 'production'
WHERE t4.state = 'ACTIVE'
AND t5.c1 IS NULL
GROUP BY i.id, ip_products.products
HAVING FIND_IN_SET('info', products)
ORDER BY i.id ASC
LIMIT 500 OFFSET 5500;
2. What did you expect to see? (Required)
TopN_36 8.00 1177779.76 root test.t1.id, offset:5500, count:500
└─HashAgg_41 8.00 495969.50 root group by:Column#18, test.t1.id, funcs:firstrow(test.t1.id)->test.t1.id, funcs:firstrow(Column#18)->Column#18
└─Selection_42 8.00 493651.71 root isnull(test.t5.c1)
└─Projection_43 10.00 493152.71 root test.t1.id, Column#18, test.t5.c1
└─HashJoin_56 10.00 493149.71 root inner join, equal:[eq(test.t4.c3, test.t4.c3)]
├─Selection_123(Build) 6.40 18442.06 root find_in_set("info", Column#18)
│ └─HashAgg_124 8.00 18042.86 root group by:test.t4.c3, test.t4.c2, test.t4.c1, funcs:group_concat(distinct test.t2.c3 order by test.t2.c3 separator ",")->Column#18, funcs:firstrow(test.t4.c3)->test.t4.c3
│ └─IndexJoin_127 15.62 15105.72 root left outer join, inner:TableReader_177, left side:IndexJoin_160, outer key:test.t4.c1, test.t4.c2, inner key:test.t2.c1, test.t2.c2, equal cond:eq(test.t4.c2, test.t2.c2), eq(test.t4.c1, test.t2.c1)
│ ├─IndexJoin_160(Build) 12.50 7150.52 root inner join, inner:TableReader_173, outer key:test.t3.c1, test.t3.c2, inner key:test.t4.c1, test.t4.c2, equal cond:eq(test.t3.c2, test.t4.c2), eq(test.t3.c1, test.t4.c1)
│ │ ├─IndexReader_175(Build) 10.00 655.36 root index:IndexRangeScan_174
│ │ │ └─IndexRangeScan_174 10.00 3177.59 cop[tikv] table:t3, index:c2(c2) range:["production","production"], keep order:false, stats:pseudo
│ │ └─TableReader_173(Probe) 0.01 23.96 root data:Selection_172
│ │ └─Selection_172 0.01 358.61 cop[tikv] eq(test.t4.state, "ACTIVE")
│ │ └─TableRangeScan_171 10.00 308.71 cop[tikv] table:t4 range: decided by [eq(test.t4.c1, test.t3.c1) eq(test.t4.c2, test.t3.c2)], keep order:false, stats:pseudo
│ └─TableReader_177(Probe) 12.50 57.47 root data:TableRangeScan_176
│ └─TableRangeScan_176 12.50 291.82 cop[tikv] table:t2 range: decided by [eq(test.t2.c1, test.t4.c1) eq(test.t2.c2, test.t4.c2)], keep order:false, stats:pseudo
└─IndexJoin_59(Probe) 12.50 471600.51 root left outer join, inner:TableReader_113, left side:IndexJoin_90, outer key:test.t1.id, inner key:test.t5.c1, equal cond:eq(test.t1.id, test.t5.c1)
├─IndexJoin_90(Build) 12.50 463857.00 root inner join, inner:TableReader_108, outer key:test.t4.c3, inner key:test.t1.id, equal cond:eq(test.t4.c3, test.t1.id)
│ ├─TableReader_106(Build) 10.00 457241.47 root data:Selection_105
│ │ └─Selection_105 10.00 6854186.91 cop[tikv] eq(test.t4.state, "ACTIVE")
│ │ └─TableFullScan_104 10000.00 6355186.91 cop[tikv] table:t4 keep order:false, stats:pseudo
│ └─TableReader_108(Probe) 10.00 28.57 root data:TableRangeScan_107
│ └─TableRangeScan_107 10.00 238.42 cop[tikv] table:i range: decided by [eq(test.t1.id, test.t4.c3)], keep order:false, stats:pseudo
└─TableReader_113(Probe) 0.01 21.22 root data:Selection_112
└─Selection_112 0.01 317.91 cop[tikv] eq(test.t5.c2, "production")
└─TableRangeScan_111 12.50 268.01 cop[tikv] table:t5 range: decided by [eq(test.t5.c1, test.t1.id) eq(test.t5.c2, production)], keep order:false, stats:pseud
3. What did you see instead (Required)
+------------------------------------------+----------+------------+-----------+------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | estCost | task | access object | operator info |
+------------------------------------------+----------+------------+-----------+------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TopN_36 | 8.00 | 1211034.27 | root | | test.t1.id, offset:5500, count:500 |
| └─HashAgg_41 | 8.00 | 529224.01 | root | | group by:Column#16, test.t1.id, funcs:firstrow(test.t1.id)->test.t1.id, funcs:firstrow(Column#16)->Column#16 |
| └─Selection_42 | 8.00 | 526906.22 | root | | isnull(test.t5.c1) |
| └─Projection_43 | 10.00 | 526407.22 | root | | test.t1.id, Column#16, test.t5.c1 |
| └─HashJoin_56 | 10.00 | 526404.22 | root | | inner join, equal:[eq(test.t4.c3, test.t4.c3)] |
| ├─Selection_129(Build) | 6.40 | 18442.06 | root | | find_in_set("info", Column#16) |
| │ └─HashAgg_130 | 8.00 | 18042.86 | root | | group by:test.t4.c1, test.t4.c2, test.t4.c3, funcs:group_concat(distinct test.t2.c3 order by test.t2.c3 separator ",")->Column#16, funcs:firstrow(test.t4.c3)->test.t4.c3 |
| │ └─IndexJoin_133 | 15.62 | 15105.72 | root | | left outer join, inner:TableReader_183, left side:IndexJoin_166, outer key:test.t4.c1, test.t4.c2, inner key:test.t2.c1, test.t2.c2, equal cond:eq(test.t4.c1, test.t2.c1), eq(test.t4.c2, test.t2.c2) |
| │ ├─IndexJoin_166(Build) | 12.50 | 7150.52 | root | | inner join, inner:TableReader_179, outer key:test.t3.c1, test.t3.c2, inner key:test.t4.c1, test.t4.c2, equal cond:eq(test.t3.c1, test.t4.c1), eq(test.t3.c2, test.t4.c2) |
| │ │ ├─IndexReader_181(Build) | 10.00 | 655.36 | root | | index:IndexRangeScan_180 |
| │ │ │ └─IndexRangeScan_180 | 10.00 | 3177.59 | cop[tikv] | table:t3, index:c3(c3) | range:["production","production"], keep order:false, stats:pseudo |
| │ │ └─TableReader_179(Probe) | 0.01 | 23.96 | root | | data:Selection_178 |
| │ │ └─Selection_178 | 0.01 | 358.61 | cop[tikv] | | eq(test.t4.state, "ACTIVE") |
| │ │ └─TableRangeScan_177 | 10.00 | 308.71 | cop[tikv] | table:t4 | range: decided by [eq(test.t4.c1, test.t3.c1) eq(test.t4.c2, test.t3.c2)], keep order:false, stats:pseudo |
| │ └─TableReader_183(Probe) | 12.50 | 57.47 | root | | data:TableRangeScan_182 |
| │ └─TableRangeScan_182 | 12.50 | 291.82 | cop[tikv] | table:t2 | range: decided by [eq(test.t2.c1, test.t4.c1) eq(test.t2.c2, test.t4.c2)], keep order:false, stats:pseudo |
| └─IndexJoin_59(Probe) | 12.49 | 504855.27 | root | | left outer join, inner:TableReader_119, left side:IndexJoin_91, outer key:test.t1.id, inner key:test.t5.c1, equal cond:eq(test.t1.id, test.t5.c1) |
| ├─IndexJoin_91(Build) | 12.49 | 497118.00 | root | | inner join, inner:TableReader_114, outer key:test.t4.c3, inner key:test.t1.id, equal cond:eq(test.t4.c3, test.t1.id) |
| │ ├─TableReader_107(Build) | 9.99 | 490507.84 | root | | data:Selection_106 |
| │ │ └─Selection_106 | 9.99 | 7353186.91 | cop[tikv] | | eq(test.t4.state, "ACTIVE"), not(isnull(test.t4.c3)) |
| │ │ └─TableFullScan_105 | 10000.00 | 6355186.91 | cop[tikv] | table:t4 | keep order:false, stats:pseudo |
| │ └─TableReader_114(Probe) | 9.99 | 27.83 | root | | data:TableRangeScan_113 |
| │ └─TableRangeScan_113 | 9.99 | 227.31 | cop[tikv] | table:i | range: decided by [eq(test.t1.id, test.t4.c3)], keep order:false, stats:pseudo |
| └─TableReader_119(Probe) | 0.01 | 21.22 | root | | data:Selection_118
|
| └─Selection_118 | 0.01 | 317.91 | cop[tikv] | | eq(test.t5.c2, "production")
|
| └─TableRangeScan_117 | 12.49 | 268.01 | cop[tikv] | table:t5 | range: decided by [eq(test.t5.c1, test.t1.id) eq(test.t5.c2, production)], keep order:false, stats:pseudo
|
+------------------------------------------+----------+------------+-----------+------------------------+-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------+