You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 

139 lines
4.1 KiB

-- Tests of using stored generated column as index and partition column.
-- Most of the cases are ported from other tests to make sure generated columns behaves the same.
-- Stored generated columns as indices
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';
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;
EXPLAIN SELECT a, b FROM sgc where a < 3;
EXPLAIN SELECT a, b from sgc where b < 3;
EXPLAIN SELECT a, b from sgc where a < 3 and b < 3;
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;
EXPLAIN SELECT * from sgc1 join sgc2 on sgc1.a=sgc2.a;
-- Stored generated columns as partition columns
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;
EXPLAIN SELECT * FROM sgc3 WHERE a < 7;
-- Virtual generated columns as indices
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;
EXPLAIN SELECT b, c, d FROM t1 WHERE b=1;
EXPLAIN SELECT * FROM t1 WHERE b=1;
EXPLAIN SELECT c FROM t1 WHERE c=2 AND d=3;
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;
-- Virtual generated columns in aggregate statement
-- ISSUE https://github.com/pingcap/tidb/issues/14072
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;
EXPLAIN SELECT sum(a) FROM t1 GROUP BY c;
EXPLAIN SELECT sum(b) FROM t1 GROUP BY a;
EXPLAIN SELECT sum(b) FROM t1 GROUP BY c;
EXPLAIN SELECT sum(c) FROM t1 GROUP BY a;
EXPLAIN SELECT sum(c) FROM t1 GROUP BY b;
-- Virtual generated column for point get and batch point get
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;
EXPLAIN SELECT a, c FROM tu WHERE c = 1;
EXPLAIN SELECT * FROM tu WHERE c in(1, 2, 3);
EXPLAIN SELECT c, a FROM tu WHERE c in(1, 2, 3);