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	count	task	operator info
Projection_4	10.00	root	test.person.name, test.person.id
└─IndexLookUp_10	10.00	root	
  ├─IndexScan_8	10.00	cop	table:person, index:city, range:["Beijing","Beijing"], keep order:false, stats:pseudo
  └─TableScan_9	10.00	cop	table: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	count	task	operator info
IndexReader_6	3323.33	root	index:IndexScan_5
└─IndexScan_5	3323.33	cop	table:sgc, index:a, range:[-inf,3), keep order:false, stats:pseudo
EXPLAIN SELECT a, b FROM sgc where a < 3;
id	count	task	operator info
IndexReader_6	3323.33	root	index:IndexScan_5
└─IndexScan_5	3323.33	cop	table:sgc, index:a, b, range:[-inf,3), keep order:false, stats:pseudo
EXPLAIN SELECT a, b from sgc where b < 3;
id	count	task	operator info
TableReader_7	3323.33	root	data:Selection_6
└─Selection_6	3323.33	cop	lt(test.sgc.b, 3)
  └─TableScan_5	10000.00	cop	table:sgc, range:[-inf,+inf], keep order:false, stats:pseudo
EXPLAIN SELECT a, b from sgc where a < 3 and b < 3;
id	count	task	operator info
IndexReader_11	1104.45	root	index:Selection_10
└─Selection_10	1104.45	cop	lt(test.sgc.b, 3)
  └─IndexScan_9	3323.33	cop	table:sgc, index: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 /*+ TIDB_INLJ(sgc1, sgc2) */ * from sgc1 join sgc2 on sgc1.a=sgc2.a;
id	count	task	operator info
IndexJoin_17	5.00	root	inner join, inner:IndexLookUp_16, outer key:test.sgc2.a, inner key:test.sgc1.a
├─IndexLookUp_16	5.00	root	
│ ├─Selection_15	5.00	cop	not(isnull(test.sgc1.a))
│ │ └─IndexScan_13	5.00	cop	table:sgc1, index:a, range: decided by [eq(test.sgc1.a, test.sgc2.a)], keep order:false
│ └─TableScan_14	5.00	cop	table:sgc1, keep order:false
└─TableReader_20	1.00	root	data:Selection_19
  └─Selection_19	1.00	cop	not(isnull(test.sgc2.a))
    └─TableScan_18	1.00	cop	table:sgc2, range:[-inf,+inf], keep order:false
EXPLAIN SELECT * from sgc1 join sgc2 on sgc1.a=sgc2.a;
id	count	task	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
└─HashRightJoin_20	5.00	root	inner join, inner:TableReader_39, equal:[eq(test.sgc2.a, test.sgc1.a)]
  ├─TableReader_39	1.00	root	data:Selection_38
  │ └─Selection_38	1.00	cop	not(isnull(test.sgc2.a))
  │   └─TableScan_37	1.00	cop	table:sgc2, range:[-inf,+inf], keep order:false
  └─TableReader_48	5.00	root	data:Selection_47
    └─Selection_47	5.00	cop	not(isnull(test.sgc1.a))
      └─TableScan_46	5.00	cop	table:sgc1, range:[-inf,+inf], 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	count	task	operator info
Union_8	6646.67	root	
├─TableReader_11	3323.33	root	data:Selection_10
│ └─Selection_10	3323.33	cop	le(test.sgc3.a, 1)
│   └─TableScan_9	10000.00	cop	table:sgc3, partition:p0, range:[-inf,+inf], keep order:false, stats:pseudo
└─TableReader_14	3323.33	root	data:Selection_13
  └─Selection_13	3323.33	cop	le(test.sgc3.a, 1)
    └─TableScan_12	10000.00	cop	table:sgc3, partition:p1, range:[-inf,+inf], keep order:false, stats:pseudo
EXPLAIN SELECT * FROM sgc3 WHERE a < 7;
id	count	task	operator info
Union_13	23263.33	root	
├─TableReader_16	3323.33	root	data:Selection_15
│ └─Selection_15	3323.33	cop	lt(test.sgc3.a, 7)
│   └─TableScan_14	10000.00	cop	table:sgc3, partition:p0, range:[-inf,+inf], keep order:false, stats:pseudo
├─TableReader_19	3323.33	root	data:Selection_18
│ └─Selection_18	3323.33	cop	lt(test.sgc3.a, 7)
│   └─TableScan_17	10000.00	cop	table:sgc3, partition:p1, range:[-inf,+inf], keep order:false, stats:pseudo
├─TableReader_22	3323.33	root	data:Selection_21
│ └─Selection_21	3323.33	cop	lt(test.sgc3.a, 7)
│   └─TableScan_20	10000.00	cop	table:sgc3, partition:p2, range:[-inf,+inf], keep order:false, stats:pseudo
├─TableReader_25	3323.33	root	data:Selection_24
│ └─Selection_24	3323.33	cop	lt(test.sgc3.a, 7)
│   └─TableScan_23	10000.00	cop	table:sgc3, partition:p3, range:[-inf,+inf], keep order:false, stats:pseudo
├─TableReader_28	3323.33	root	data:Selection_27
│ └─Selection_27	3323.33	cop	lt(test.sgc3.a, 7)
│   └─TableScan_26	10000.00	cop	table:sgc3, partition:p4, range:[-inf,+inf], keep order:false, stats:pseudo
├─TableReader_31	3323.33	root	data:Selection_30
│ └─Selection_30	3323.33	cop	lt(test.sgc3.a, 7)
│   └─TableScan_29	10000.00	cop	table:sgc3, partition:p5, range:[-inf,+inf], keep order:false, stats:pseudo
└─TableReader_34	3323.33	root	data:Selection_33
  └─Selection_33	3323.33	cop	lt(test.sgc3.a, 7)
    └─TableScan_32	10000.00	cop	table:sgc3, partition:max, range:[-inf,+inf], keep order:false, stats:pseudo
