###############################################################################
# This test checks the behaviour of Generated Columns with GR
#
# Test
# 0. This test requires 2 servers.(M1 and M2)
# 1. Check the behaviour of GR with Stored Columns.
#	1.1 Create table st1 and execute DMLs on M1.Test if data is replicated
#	    properly by verifying data on both the servers.
#		st1: stored column genarated on primary key
#	1.2 Create table st2 and execute DMLs on M2.Test if data is replicated
#	    properly by verifying data on both the servers.
#		st2: stored column as primary key
#	1.3 Create table st3 and execute DMLs on M1.Test if data is replicated
#	    properly by verifying data on both the servers.
#		st3: stored column as unique
#	1.4 Create table st4 and execute DMLs on M2.Test if data is replicated
#	    properly by verifying data on both the servers.
#		st4: Stored column as foreign key
# 2. Check the behaviour of GR with Virtual Columns.
#	2.1 Create table vt1 and execute DMLs on M1.Test if data is replicated
#	    properly by verifying data on both the servers.
#		vt1: virtual column genarated on primary key
#	2.2 Create table vt2 and execute DMLs on M2.Test if data is replicated
#	    properly by verifying data on both the servers.
#		vt2: virtual column as unique key
#	2.3 Create table vt3 and execute DMLs on M1.Test if data is replicated
#	    properly by verifying data on both the servers.
#		vt3: Index on virtual column and foreign key constraints
# 3. Check the behaviour of GR with Generated Columns
#	3.1 Create table gt1 and execute DMLs on M2.Test if data is replicated
#	    properly by verifying data on both the servers.
#		gt1: both stored and virtual columns.
# 4. Clean up.
###############################################################################

--source include/have_group_replication_plugin.inc
--source include/group_replication.inc

# Scenario 1.1 :

--echo # Table st1: stored column genarated on primary key

CREATE TABLE st1 (c1 INT PRIMARY KEY);
ALTER TABLE st1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1+1) STORED,INDEX(c2));

# Perform some basic DML on st1
INSERT INTO st1(c1) VALUES(1);
INSERT INTO st1(c1) VALUES(2);
UPDATE st1 SET c1=3 WHERE c2=3;
UPDATE st1 SET c1=4 WHERE c1=3;
DELETE FROM st1 WHERE c2=5;

--source include/rpl_sync.inc
--echo # check that st1 exists and has same values in both servers
--let $diff_tables=server1:st1, server2:st1
--source include/diff_tables.inc

# Scenario 1.2 :

--echo # Table st2: stored column as primary key
--let $rpl_connection_name= server2
--source include/rpl_connection.inc

CREATE TABLE st2 (c1 VARCHAR(10),c2 CHAR(2) GENERATED ALWAYS AS (SUBSTRING(c1, 1, 2)) STORED PRIMARY KEY);

# Perform some basic DML on st2
INSERT INTO st2(c1) VALUES("abcd");
INSERT INTO st2(c1) VALUES("efgh");
UPDATE st2 SET c1="abgh" WHERE c1='abcd';
UPDATE st2 SET c1="cdgh" WHERE c2='ef';
DELETE FROM st2 WHERE c2='cd';

--source include/rpl_sync.inc
--echo # check that st2 exists and has same values in both servers
--let $diff_tables=server1:st2, server2:st2
--source include/diff_tables.inc

# Scenario 1.3 :

--echo Table st3: stored column as unique
--let $rpl_connection_name= server1
--source include/rpl_connection.inc

CREATE TABLE st3 (
	c1 INT AUTO_INCREMENT PRIMARY KEY,
	c2 VARCHAR(10) NOT NULL,
	c3 VARCHAR(10) NOT NULL,
	c4 VARCHAR(21) AS (CONCAT(c2,c3)) STORED UNIQUE
);

# Perform some basic DML on st3
INSERT INTO st3(c2,c3) VALUES('first','insert');
INSERT INTO st3(c2,c3) VALUES('second','insert');

# Error for duplicate entry
--error ER_DUP_ENTRY
INSERT INTO st3 (c2,c3) VALUES('first','insert');

UPDATE st3 SET c3='update' WHERE c2='first';
UPDATE st3 SET c3='update' WHERE c2='second';
DELETE FROM st3 WHERE c2='first';
DELETE FROM st3 WHERE c3='update';

--source include/rpl_sync.inc
--echo # check that st3 exists and has same values in both servers
--let $diff_tables=server1:st3, server2:st3
--source include/diff_tables.inc

# Scenario 1.4 :

--echo Table st4: Stored column as foreign key
--let $rpl_connection_name= server2
--source include/rpl_connection.inc

# Parent table
CREATE TABLE t1 (a INT,b INT GENERATED ALWAYS AS (a-10)STORED, PRIMARY KEY(b));
CREATE TABLE st4 (c1 INT PRIMARY KEY,c2 INT GENERATED ALWAYS AS (c1%10)STORED,FOREIGN KEY(c2) REFERENCES t1(b));

# Insert on parent table
INSERT INTO t1(a) VALUES(11);
INSERT INTO t1(a) VALUES(12);

# Perform some basic DML on st4
INSERT INTO st4(c1) VALUES(31);
INSERT INTO st4(c1) VALUES(32);
UPDATE st4 SET c1=21 WHERE c2=1;
DELETE FROM st4 WHERE c2=2;
DELETE FROM st4 WHERE c1=21;

--source include/rpl_sync.inc
--echo # check that st4 exists and has same values in both servers
--let $diff_tables=server1:st4, server2:st4
--source include/diff_tables.inc

# Scenario 2.1 :

--echo Table vt1: virtual column genarated on primary key
--let $rpl_connection_name= server1
--source include/rpl_connection.inc

CREATE TABLE vt1 (c1 DATE PRIMARY KEY,c2 INT AS (year(c1)) VIRTUAL);

# Perform some basic DML on vt1
INSERT INTO vt1(c1) VALUES('2008-09-02');
INSERT INTO vt1(c1) VALUES('1998-06-17');
UPDATE vt1 SET c1='2009-09-02' WHERE c2=2008;
UPDATE vt1 SET c1='1999-06-17' WHERE c1='1998-06-17';
DELETE FROM vt1 WHERE c1='2009-09-02';
DELETE FROM vt1 WHERE c2=1999;

--source include/rpl_sync.inc
--echo # check that vt1 exists and has same values in both servers
--let $diff_tables=server1:vt1, server2:vt1
--source include/diff_tables.inc

# Scenario 2.2 :

--echo Table  vt2: virtual column as unique key
--let $rpl_connection_name= server2
--source include/rpl_connection.inc

CREATE TABLE vt2 (c1 TEXT,PRIMARY KEY(c1(20)),c2 TEXT GENERATED ALWAYS AS (SUBSTRING(c1, 1, 2)) VIRTUAL ,UNIQUE(c2(2)));

# Perform some basic DML on vt2
INSERT INTO vt2(c1) VALUES('first insert');
INSERT INTO vt2(c1) VALUES('second insert');
UPDATE vt2 SET c1='first update' WHERE c1='first insert';
UPDATE vt2 SET c1='second update' WHERE c2='se';
DELETE FROM vt2 WHERE c1='first update';
DELETE FROM vt2 WHERE c2='se';

--source include/rpl_sync.inc
--echo # check that vt2 exists and has same values in both servers
--let $diff_tables=server1:vt2, server2:vt2
--source include/diff_tables.inc

# Scenario 2.3 :

--echo Table vt3: Virtual Column with foreign keys
--let $rpl_connection_name= server1
--source include/rpl_connection.inc

#Parent table
CREATE TABLE t2(a INT PRIMARY KEY,b INT GENERATED ALWAYS AS (a+1) VIRTUAL,INDEX(b));
CREATE TABLE vt3(a INT PRIMARY KEY,b INT,FOREIGN KEY(b) REFERENCES t2(a) ON UPDATE RESTRICT ON DELETE RESTRICT,c INT AS (a*b)VIRTUAL);

# Insert on Parent table
INSERT INTO t2(a) VALUES(1);
INSERT INTO t2(a) VALUES(3);

# Perform some basic DML on vt3
INSERT INTO vt3(a,b) VALUES(100,1),(200,3);
UPDATE vt3 SET b=NULL WHERE b=3;
UPDATE t2 SET a=2 WHERE b=4;
UPDATE vt3 SET b=2 WHERE b IS NULL;
UPDATE t2,vt3 SET vt3.a=t2.a WHERE t2.b=vt3.b;

# Error due to foreign key constraint
--error ER_ROW_IS_REFERENCED_2
DELETE FROM t2 WHERE a=2;

DELETE FROM vt3 WHERE a=1;
DELETE FROM vt3 WHERE c=100;

--source include/rpl_sync.inc
--echo # check that vt3 exists and has same values in both servers
--let $diff_tables=server1:vt3, server2:vt3
--source include/diff_tables.inc

# Scenario 3.1 :

--echo Table gt1: gt1: both stored and virtual columns
--let $rpl_connection_name= server2
--source include/rpl_connection.inc

CREATE TABLE gt1 (a INT, b INT AS (a+1)STORED PRIMARY KEY);
ALTER TABLE gt1 ADD COLUMN c INT GENERATED ALWAYS AS (b+1)VIRTUAL;
ALTER TABLE gt1 ADD COLUMN d INT GENERATED  ALWAYS AS (c+10) STORED;

# Perform some basic DML on gt1
--let $i=5
while ($i>0){
  --eval INSERT INTO gt1(a) VALUES($i)
  --dec $i
}
UPDATE gt1 SET a=21 WHERE b=2;
UPDATE gt1 SET a=22 WHERE c=4;
UPDATE gt1 SET a=23 WHERE d=15;
UPDATE gt1 SET a=24 WHERE a=4;
DELETE FROM gt1 WHERE a=5;
DELETE FROM gt1 WHERE b=24;
DELETE FROM gt1 WHERE c=24;
DELETE FROM gt1 WHERE d=33;

--source include/rpl_sync.inc
--echo # check that gt1 exists and has same values in both servers
--let $diff_tables=server1:gt1, server2:gt1
--source include/diff_tables.inc

--echo # Clean Up

DROP TABLE st1,st2,st3,st4;
DROP TABLE vt1,vt2,vt3;
DROP TABLE gt1;
DROP TABLE t1,t2;

--source include/group_replication_end.inc