tidb/tests/integrationtest/t/generated_columns.test

set tidb_cost_model_version=1;
# 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 format = 'plan_tree' 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 format = 'plan_tree' SELECT a FROM sgc where a < 3;
EXPLAIN format = 'plan_tree' SELECT a, b FROM sgc where a < 3;
EXPLAIN format = 'plan_tree' SELECT a, b from sgc where b < 3;
EXPLAIN format = 'plan_tree' 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"}');

# FIXME: With all options on, it got runtime error: index out of range [-1]
ANALYZE TABLE sgc1, sgc2 predicate columns;

EXPLAIN format = 'plan_tree' SELECT /*+ TIDB_INLJ(sgc1, sgc2) */ * from sgc1 join sgc2 on sgc1.a=sgc2.a;
EXPLAIN format = 'plan_tree' SELECT * from sgc1 join sgc2 on sgc1.a=sgc2.a;


# Stored generated columns as partition columns

set @old_prune_mode = @@tidb_partition_prune_mode;
set @@tidb_partition_prune_mode='static';
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 format = 'plan_tree' SELECT * FROM sgc3 WHERE a <= 1;
EXPLAIN format = 'plan_tree' SELECT * FROM sgc3 WHERE a < 7;

set @@tidb_partition_prune_mode='dynamic';
DROP TABLE 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);

analyze table sgc3;

EXPLAIN format = 'plan_tree' SELECT * FROM sgc3 WHERE a <= 1;
EXPLAIN format = 'plan_tree' SELECT * FROM sgc3 WHERE a < 7;

set @@tidb_partition_prune_mode = @old_prune_mode;

# 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 format = 'plan_tree' SELECT b FROM t1 WHERE b=1;
EXPLAIN format = 'plan_tree' SELECT b, c, d FROM t1 WHERE b=1;
EXPLAIN format = 'plan_tree' SELECT * FROM t1 WHERE b=1;
EXPLAIN format = 'plan_tree' 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 format = 'plan_tree' 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 format = 'plan_tree' SELECT sum(a) FROM t1 GROUP BY b;
EXPLAIN format = 'plan_tree' SELECT sum(a) FROM t1 GROUP BY c;
EXPLAIN format = 'plan_tree' SELECT sum(b) FROM t1 GROUP BY a;
EXPLAIN format = 'plan_tree' SELECT sum(b) FROM t1 GROUP BY c;
EXPLAIN format = 'plan_tree' SELECT sum(c) FROM t1 GROUP BY a;
EXPLAIN format = 'plan_tree' 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 format = 'plan_tree' SELECT * FROM tu WHERE c = 1;
EXPLAIN format = 'plan_tree' SELECT a, c FROM tu WHERE c = 1;
EXPLAIN format = 'plan_tree' SELECT * FROM tu WHERE c in(1, 2, 3);
EXPLAIN format = 'plan_tree' SELECT c, a FROM tu WHERE c in(1, 2, 3);

# should handle divide zero error for default sql mode
set @@sql_mode=default;
drop table if exists t1;
create table t1(a int);
insert into t1 values(0);
--error 1365
alter table t1 add index i((100/a));
drop table t1;

# should ignore divide zero error for default sql mode
set @@sql_mode='';
create table t1(a int);
insert into t1 values(0);
alter table t1 add index i((100/a));
drop table t1;
set @@sql_mode=default;