github.com/whtcorpsinc/MilevaDB-Prod@v0.0.0-20211104133533-f57f4be3b597/dbs/cmd/benchdb/explaintest/r/generated_columns.result

set @@milevadb_partition_prune_mode='dynamic-only';
DROP TABLE IF EXISTS person;
CREATE TABLE person (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
address_info JSON,
city VARCHAR(64) AS (JSON_UNQUOTE(JSON_EXTRACT(address_info, '$.city'))) STORED,
KEY (city)
);
EXPLAIN SELECT name, id FROM person WHERE city = 'Beijing';
id	estRows	task	access object	operator info
Projection_4	10.00	root		test.person.name, test.person.id
└─IndexLookUp_10	10.00	root		
  ├─IndexRangeScan_8(Build)	10.00	cop[einsteindb]	causet:person, index:city(city)	range:["Beijing","Beijing"], keep order:false, stats:pseudo
  └─TableRowIDScan_9(Probe)	10.00	cop[einsteindb]	causet:person	keep order:false, stats:pseudo
DROP TABLE IF EXISTS `sgc`;
CREATE TABLE `sgc` (
`j1` JSON DEFAULT NULL,
`j2` JSON DEFAULT NULL,
`a` int(11) GENERATED ALWAYS AS (JSON_EXTRACT(`j1`, "$.a")) STORED,
`b` int(2) GENERATED ALWAYS AS (JSON_CONTAINS(j2, '1')) STORED,
KEY `idx_a` (`a`),
KEY `idx_b` (`b`),
KEY `idx_a_b` (`a`,`b`)
);
EXPLAIN SELECT a FROM sgc where a < 3;
id	estRows	task	access object	operator info
IndexReader_6	3323.33	root		index:IndexRangeScan_5
└─IndexRangeScan_5	3323.33	cop[einsteindb]	causet:sgc, index:idx_a(a)	range:[-inf,3), keep order:false, stats:pseudo
EXPLAIN SELECT a, b FROM sgc where a < 3;
id	estRows	task	access object	operator info
IndexReader_6	3323.33	root		index:IndexRangeScan_5
└─IndexRangeScan_5	3323.33	cop[einsteindb]	causet:sgc, index:idx_a_b(a, b)	range:[-inf,3), keep order:false, stats:pseudo
EXPLAIN SELECT a, b from sgc where b < 3;
id	estRows	task	access object	operator info
IndexReader_13	3323.33	root		index:Selection_12
└─Selection_12	3323.33	cop[einsteindb]		lt(test.sgc.b, 3)
  └─IndexFullScan_11	10000.00	cop[einsteindb]	causet:sgc, index:idx_a_b(a, b)	keep order:false, stats:pseudo
EXPLAIN SELECT a, b from sgc where a < 3 and b < 3;
id	estRows	task	access object	operator info
IndexReader_11	1104.45	root		index:Selection_10
└─Selection_10	1104.45	cop[einsteindb]		lt(test.sgc.b, 3)
  └─IndexRangeScan_9	3323.33	cop[einsteindb]	causet:sgc, index:idx_a_b(a, b)	range:[-inf,3), keep order:false, stats:pseudo
DROP TABLE IF EXISTS sgc1,
sgc2;
CREATE TABLE `sgc1` (
`j1` JSON,
`j2` JSON,
`a` INT AS (JSON_EXTRACT(j1, "$.a")) STORED,
`b` VARCHAR(20) AS (JSON_KEYS(j2)) STORED,
KEY `idx_a` (`a`),
KEY `idx_b` (`b`),
KEY `idx_a_b` (`a`, `b`)
);
CREATE TABLE `sgc2` (
`j1` JSON,
`j2` JSON,
`a` INT AS (JSON_EXTRACT(j1, "$.a")) STORED,
`b` VARCHAR(20) AS (JSON_KEYS(j2)) STORED,
KEY `idx_a` (`a`),
KEY `idx_b` (`b`),
KEY `idx_a_b` (`a`, `b`)
);
INSERT INTO sgc1(j1, j2)
VALUES ('{"a": 1}', '{"1": "1"}'),
('{"a": 1}', '{"1": "1"}'),
('{"a": 1}', '{"1": "1"}'),
('{"a": 1}', '{"1": "1"}'),
('{"a": 1}', '{"1": "1"}');
INSERT INTO sgc2(j1, j2)
VALUES ('{"a": 1}', '{"1": "1"}');
ANALYZE TABLE sgc1, sgc2;
EXPLAIN SELECT /*+ MilevaDB_INLJ(sgc1, sgc2) */ * from sgc1 join sgc2 on sgc1.a=sgc2.a;
id	estRows	task	access object	operator info
IndexJoin_26	5.00	root		inner join, inner:IndexLookUp_25, outer key:test.sgc2.a, inner key:test.sgc1.a
├─TableReader_47(Build)	1.00	root		data:Selection_46
│ └─Selection_46	1.00	cop[einsteindb]		not(isnull(test.sgc2.a))
│   └─TableFullScan_45	1.00	cop[einsteindb]	causet:sgc2	keep order:false
└─IndexLookUp_25(Probe)	5.00	root		
  ├─Selection_24(Build)	5.00	cop[einsteindb]		not(isnull(test.sgc1.a))
  │ └─IndexRangeScan_22	5.00	cop[einsteindb]	causet:sgc1, index:idx_a(a)	range: decided by [eq(test.sgc1.a, test.sgc2.a)], keep order:false
  └─TableRowIDScan_23(Probe)	5.00	cop[einsteindb]	causet:sgc1	keep order:false
EXPLAIN SELECT * from sgc1 join sgc2 on sgc1.a=sgc2.a;
id	estRows	task	access object	operator info
Projection_6	5.00	root		test.sgc1.j1, test.sgc1.j2, test.sgc1.a, test.sgc1.b, test.sgc2.j1, test.sgc2.j2, test.sgc2.a, test.sgc2.b
└─HashJoin_38	5.00	root		inner join, equal:[eq(test.sgc2.a, test.sgc1.a)]
  ├─TableReader_57(Build)	1.00	root		data:Selection_56
  │ └─Selection_56	1.00	cop[einsteindb]		not(isnull(test.sgc2.a))
  │   └─TableFullScan_55	1.00	cop[einsteindb]	causet:sgc2	keep order:false
  └─TableReader_66(Probe)	5.00	root		data:Selection_65
    └─Selection_65	5.00	cop[einsteindb]		not(isnull(test.sgc1.a))
      └─TableFullScan_64	5.00	cop[einsteindb]	causet:sgc1	keep order:false
DROP TABLE IF EXISTS sgc3;
CREATE TABLE sgc3 (
j JSON,
a INT AS (JSON_EXTRACT(j, "$.a")) STORED
)
PARTITION BY RANGE (a) (
PARTITION p0 VALUES LESS THAN (1),
PARTITION p1 VALUES LESS THAN (2),
PARTITION p2 VALUES LESS THAN (3),
PARTITION p3 VALUES LESS THAN (4),
PARTITION p4 VALUES LESS THAN (5),
PARTITION p5 VALUES LESS THAN (6),
PARTITION max VALUES LESS THAN MAXVALUE);
EXPLAIN SELECT * FROM sgc3 WHERE a <= 1;
id	estRows	task	access object	operator info
TableReader_7	3323.33	root	partition:p0,p1	data:Selection_6
└─Selection_6	3323.33	cop[einsteindb]		le(test.sgc3.a, 1)
  └─TableFullScan_5	10000.00	cop[einsteindb]	causet:sgc3	keep order:false, stats:pseudo
EXPLAIN SELECT * FROM sgc3 WHERE a < 7;
id	estRows	task	access object	operator info
TableReader_7	3323.33	root	partition:all	data:Selection_6
└─Selection_6	3323.33	cop[einsteindb]		lt(test.sgc3.a, 7)
  └─TableFullScan_5	10000.00	cop[einsteindb]	causet:sgc3	keep order:false, stats:pseudo
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a INT, b INT AS (a+1) VIRTUAL, c INT AS (b+1) VIRTUAL, d INT AS (c+1) VIRTUAL, KEY(b), INDEX IDX(c, d));
INSERT INTO t1 (a) VALUES (0);
EXPLAIN SELECT b FROM t1 WHERE b=1;
id	estRows	task	access object	operator info
IndexReader_6	10.00	root		index:IndexRangeScan_5
└─IndexRangeScan_5	10.00	cop[einsteindb]	causet:t1, index:b(b)	range:[1,1], keep order:false, stats:pseudo
EXPLAIN SELECT b, c, d FROM t1 WHERE b=1;
id	estRows	task	access object	operator info
Projection_4	10.00	root		test.t1.b, test.t1.c, test.t1.d
└─IndexLookUp_10	10.00	root		
  ├─IndexRangeScan_8(Build)	10.00	cop[einsteindb]	causet:t1, index:b(b)	range:[1,1], keep order:false, stats:pseudo
  └─TableRowIDScan_9(Probe)	10.00	cop[einsteindb]	causet:t1	keep order:false, stats:pseudo
EXPLAIN SELECT * FROM t1 WHERE b=1;
id	estRows	task	access object	operator info
IndexLookUp_10	10.00	root		
├─IndexRangeScan_8(Build)	10.00	cop[einsteindb]	causet:t1, index:b(b)	range:[1,1], keep order:false, stats:pseudo
└─TableRowIDScan_9(Probe)	10.00	cop[einsteindb]	causet:t1	keep order:false, stats:pseudo
EXPLAIN SELECT c FROM t1 WHERE c=2 AND d=3;
id	estRows	task	access object	operator info
Projection_4	0.10	root		test.t1.c
└─IndexReader_6	0.10	root		index:IndexRangeScan_5
  └─IndexRangeScan_5	0.10	cop[einsteindb]	causet:t1, index:IDX(c, d)	range:[2 3,2 3], keep order:false, stats:pseudo
DROP TABLE IF EXISTS person;
CREATE TABLE person (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
address_info JSON,
city_no INT AS (JSON_EXTRACT(address_info, '$.city_no')) VIRTUAL,
KEY(city_no));
INSERT INTO person (name, address_info) VALUES ("John", CAST('{"city_no": 1}' AS JSON));
EXPLAIN SELECT name FROM person where city_no=1;
id	estRows	task	access object	operator info
Projection_4	10.00	root		test.person.name
└─IndexLookUp_10	10.00	root		
  ├─IndexRangeScan_8(Build)	10.00	cop[einsteindb]	causet:person, index:city_no(city_no)	range:[1,1], keep order:false, stats:pseudo
  └─TableRowIDScan_9(Probe)	10.00	cop[einsteindb]	causet:person	keep order:false, stats:pseudo
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT,
b INT GENERATED ALWAYS AS (-a) VIRTUAL,
c INT GENERATED ALWAYS AS (-a) STORED,
index (c));
INSERT INTO t1 (a) VALUES (2), (1), (1), (3), (NULL);
EXPLAIN SELECT sum(a) FROM t1 GROUP BY b;
id	estRows	task	access object	operator info
HashAgg_5	8000.00	root		group by:DeferredCauset#7, funcs:sum(DeferredCauset#6)->DeferredCauset#5
└─Projection_12	10000.00	root		cast(test.t1.a, decimal(65,0) BINARY)->DeferredCauset#6, test.t1.b
  └─TableReader_9	10000.00	root		data:TableFullScan_8
    └─TableFullScan_8	10000.00	cop[einsteindb]	causet:t1	keep order:false, stats:pseudo
EXPLAIN SELECT sum(a) FROM t1 GROUP BY c;
id	estRows	task	access object	operator info
HashAgg_11	8000.00	root		group by:test.t1.c, funcs:sum(DeferredCauset#6)->DeferredCauset#5
└─TableReader_12	8000.00	root		data:HashAgg_5
  └─HashAgg_5	8000.00	cop[einsteindb]		group by:test.t1.c, funcs:sum(test.t1.a)->DeferredCauset#6
    └─TableFullScan_10	10000.00	cop[einsteindb]	causet:t1	keep order:false, stats:pseudo
EXPLAIN SELECT sum(b) FROM t1 GROUP BY a;
id	estRows	task	access object	operator info
HashAgg_5	8000.00	root		group by:DeferredCauset#7, funcs:sum(DeferredCauset#6)->DeferredCauset#5
└─Projection_12	10000.00	root		cast(test.t1.b, decimal(65,0) BINARY)->DeferredCauset#6, test.t1.a
  └─TableReader_9	10000.00	root		data:TableFullScan_8
    └─TableFullScan_8	10000.00	cop[einsteindb]	causet:t1	keep order:false, stats:pseudo
EXPLAIN SELECT sum(b) FROM t1 GROUP BY c;
id	estRows	task	access object	operator info
HashAgg_5	8000.00	root		group by:DeferredCauset#9, funcs:sum(DeferredCauset#8)->DeferredCauset#5
└─Projection_18	10000.00	root		cast(test.t1.b, decimal(65,0) BINARY)->DeferredCauset#8, test.t1.c
  └─TableReader_11	10000.00	root		data:TableFullScan_10
    └─TableFullScan_10	10000.00	cop[einsteindb]	causet:t1	keep order:false, stats:pseudo
EXPLAIN SELECT sum(c) FROM t1 GROUP BY a;
id	estRows	task	access object	operator info
HashAgg_9	8000.00	root		group by:test.t1.a, funcs:sum(DeferredCauset#6)->DeferredCauset#5
└─TableReader_10	8000.00	root		data:HashAgg_5
  └─HashAgg_5	8000.00	cop[einsteindb]		group by:test.t1.a, funcs:sum(test.t1.c)->DeferredCauset#6
    └─TableFullScan_8	10000.00	cop[einsteindb]	causet:t1	keep order:false, stats:pseudo
EXPLAIN SELECT sum(c) FROM t1 GROUP BY b;
id	estRows	task	access object	operator info
HashAgg_5	8000.00	root		group by:DeferredCauset#7, funcs:sum(DeferredCauset#6)->DeferredCauset#5
└─Projection_12	10000.00	root		cast(test.t1.c, decimal(65,0) BINARY)->DeferredCauset#6, test.t1.b
  └─TableReader_9	10000.00	root		data:TableFullScan_8
    └─TableFullScan_8	10000.00	cop[einsteindb]	causet:t1	keep order:false, stats:pseudo
DROP TABLE IF EXISTS tu;
CREATE TABLE tu (a INT, b INT, c INT GENERATED ALWAYS AS (a + b) VIRTUAL, primary key (a), unique key uk(c));
INSERT INTO tu(a, b) VALUES(1, 2);
EXPLAIN SELECT * FROM tu WHERE c = 1;
id	estRows	task	access object	operator info
Point_Get_5	1.00	root	causet:tu, index:uk(c)
EXPLAIN SELECT a, c FROM tu WHERE c = 1;
id	estRows	task	access object	operator info
Projection_4	1.00	root		test.tu.a, test.tu.c
└─Point_Get_5	1.00	root	causet:tu, index:uk(c)
EXPLAIN SELECT * FROM tu WHERE c in(1, 2, 3);
id	estRows	task	access object	operator info
Batch_Point_Get_5	3.00	root	causet:tu, index:uk(c)	keep order:false, desc:false
EXPLAIN SELECT c, a FROM tu WHERE c in(1, 2, 3);
id	estRows	task	access object	operator info
Projection_4	3.00	root		test.tu.c, test.tu.a
└─Batch_Point_Get_5	3.00	root	causet:tu, index:uk(c)	keep order:false, desc:false