-- 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;

