github.com/whtcorpsinc/milevadb-prod@v0.0.0-20211104133533-f57f4be3b597/dbs/cmd/benchdb/explaintest/t/generated_columns.test (about)

     1  -- Tests of using stored generated column as index and partition column.
     2  -- Most of the cases are ported from other tests to make sure generated columns behaves the same.
     3  
     4  -- Stored generated columns as indices
     5  set @@milevadb_partition_prune_mode='dynamic-only';
     6  
     7  DROP TABLE IF EXISTS person;
     8  CREATE TABLE person (
     9      id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    10      name VARCHAR(255) NOT NULL,
    11      address_info JSON,
    12      city VARCHAR(64) AS (JSON_UNQUOTE(JSON_EXTRACT(address_info, '$.city'))) STORED,
    13      KEY (city)
    14  );
    15  
    16  EXPLAIN SELECT name, id FROM person WHERE city = 'Beijing';
    17  
    18  
    19  DROP TABLE IF EXISTS `sgc`;
    20  CREATE TABLE `sgc` (
    21    `j1` JSON DEFAULT NULL,
    22    `j2` JSON DEFAULT NULL,
    23    `a` int(11) GENERATED ALWAYS AS (JSON_EXTRACT(`j1`, "$.a")) STORED,
    24    `b` int(2) GENERATED ALWAYS AS (JSON_CONTAINS(j2, '1')) STORED,
    25    KEY `idx_a` (`a`),
    26    KEY `idx_b` (`b`),
    27    KEY `idx_a_b` (`a`,`b`)
    28  );
    29  
    30  EXPLAIN SELECT a FROM sgc where a < 3;
    31  EXPLAIN SELECT a, b FROM sgc where a < 3;
    32  EXPLAIN SELECT a, b from sgc where b < 3;
    33  EXPLAIN SELECT a, b from sgc where a < 3 and b < 3;
    34  
    35  DROP TABLE IF EXISTS sgc1,
    36                       sgc2;
    37  
    38  
    39  CREATE TABLE `sgc1` (
    40  `j1` JSON,
    41  `j2` JSON,
    42  `a` INT AS (JSON_EXTRACT(j1, "$.a")) STORED,
    43  `b` VARCHAR(20) AS (JSON_KEYS(j2)) STORED,
    44  KEY `idx_a` (`a`),
    45  KEY `idx_b` (`b`),
    46  KEY `idx_a_b` (`a`, `b`)
    47  );
    48  
    49  CREATE TABLE `sgc2` (
    50  `j1` JSON,
    51  `j2` JSON,
    52  `a` INT AS (JSON_EXTRACT(j1, "$.a")) STORED,
    53  `b` VARCHAR(20) AS (JSON_KEYS(j2)) STORED,
    54  KEY `idx_a` (`a`),
    55  KEY `idx_b` (`b`),
    56  KEY `idx_a_b` (`a`, `b`)
    57  );
    58  
    59  INSERT INTO sgc1(j1, j2)
    60  VALUES ('{"a": 1}', '{"1": "1"}'),
    61         ('{"a": 1}', '{"1": "1"}'),
    62         ('{"a": 1}', '{"1": "1"}'),
    63         ('{"a": 1}', '{"1": "1"}'),
    64         ('{"a": 1}', '{"1": "1"}');
    65  
    66  INSERT INTO sgc2(j1, j2)
    67  VALUES ('{"a": 1}', '{"1": "1"}');
    68  
    69  ANALYZE TABLE sgc1, sgc2;
    70  
    71  EXPLAIN SELECT /*+ MilevaDB_INLJ(sgc1, sgc2) */ * from sgc1 join sgc2 on sgc1.a=sgc2.a;
    72  EXPLAIN SELECT * from sgc1 join sgc2 on sgc1.a=sgc2.a;
    73  
    74  
    75  -- Stored generated columns as partition columns
    76  
    77  DROP TABLE IF EXISTS sgc3;
    78  CREATE TABLE sgc3 (
    79  j JSON,
    80  a INT AS (JSON_EXTRACT(j, "$.a")) STORED
    81  )
    82  PARTITION BY RANGE (a) (
    83  PARTITION p0 VALUES LESS THAN (1),
    84  PARTITION p1 VALUES LESS THAN (2),
    85  PARTITION p2 VALUES LESS THAN (3),
    86  PARTITION p3 VALUES LESS THAN (4),
    87  PARTITION p4 VALUES LESS THAN (5),
    88  PARTITION p5 VALUES LESS THAN (6),
    89  PARTITION max VALUES LESS THAN MAXVALUE);
    90  
    91  EXPLAIN SELECT * FROM sgc3 WHERE a <= 1;
    92  EXPLAIN SELECT * FROM sgc3 WHERE a < 7;
    93  
    94  -- Virtual generated columns as indices
    95  
    96  DROP TABLE IF EXISTS t1;
    97  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));
    98  INSERT INTO t1 (a) VALUES (0);
    99  
   100  EXPLAIN SELECT b FROM t1 WHERE b=1;
   101  EXPLAIN SELECT b, c, d FROM t1 WHERE b=1;
   102  EXPLAIN SELECT * FROM t1 WHERE b=1;
   103  EXPLAIN SELECT c FROM t1 WHERE c=2 AND d=3;
   104  
   105  DROP TABLE IF EXISTS person;
   106  CREATE TABLE person (
   107  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   108  name VARCHAR(255) NOT NULL,
   109  address_info JSON,
   110  city_no INT AS (JSON_EXTRACT(address_info, '$.city_no')) VIRTUAL,
   111  KEY(city_no));
   112  
   113  INSERT INTO person (name, address_info) VALUES ("John", CAST('{"city_no": 1}' AS JSON));
   114  EXPLAIN SELECT name FROM person where city_no=1;
   115  
   116  -- Virtual generated columns in aggregate memex
   117  -- ISSUE https://github.com/whtcorpsinc/milevadb/issues/14072
   118  
   119  DROP TABLE IF EXISTS t1;
   120  CREATE TABLE t1 (a INT,
   121                   b INT GENERATED ALWAYS AS (-a) VIRTUAL,
   122                   c INT GENERATED ALWAYS AS (-a) STORED,
   123                   index (c));
   124  INSERT INTO t1 (a) VALUES (2), (1), (1), (3), (NULL);
   125  
   126  EXPLAIN SELECT sum(a) FROM t1 GROUP BY b;
   127  EXPLAIN SELECT sum(a) FROM t1 GROUP BY c;
   128  EXPLAIN SELECT sum(b) FROM t1 GROUP BY a;
   129  EXPLAIN SELECT sum(b) FROM t1 GROUP BY c;
   130  EXPLAIN SELECT sum(c) FROM t1 GROUP BY a;
   131  EXPLAIN SELECT sum(c) FROM t1 GROUP BY b;
   132  
   133  -- Virtual generated column for point get and batch point get
   134  DROP TABLE IF EXISTS tu;
   135  CREATE TABLE tu (a INT, b INT, c INT GENERATED ALWAYS AS (a + b) VIRTUAL, primary key (a), unique key uk(c));
   136  INSERT INTO tu(a, b) VALUES(1, 2);
   137  EXPLAIN SELECT * FROM tu WHERE c = 1;
   138  EXPLAIN SELECT a, c FROM tu WHERE c = 1;
   139  EXPLAIN SELECT * FROM tu WHERE c in(1, 2, 3);
   140  EXPLAIN SELECT c, a FROM tu WHERE c in(1, 2, 3);