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

--source include/master-slave.inc

CALL mtr.add_suppression("Statement is unsafe because it is being used inside a XA transaction");

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

--disable_warnings

# Perform XA Transaction on st1
XA START 'xstatement';
UPDATE st1 SET c1=3 WHERE c2=3;
UPDATE st1 SET c1=4 WHERE c1=3;
DELETE FROM st1 WHERE c2=5;
XA END 'xstatement';
XA PREPARE 'xstatement';
XA COMMIT 'xstatement';

--source include/sync_slave_sql_with_master.inc

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

# Scenario 1.2 :

--echo # Table st2: stored column as primary key
--source include/rpl_connection_master.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");

# Perform XA Transaction on st2
XA START 'xstatement';
UPDATE st2 SET c1="abgh" WHERE c1='abcd';
UPDATE st2 SET c1="cdgh" WHERE c2='ef';
DELETE FROM st2 WHERE c2='cd';
XA END 'xstatement';
XA PREPARE 'xstatement';
XA COMMIT 'xstatement';

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

# Scenario 1.3 :

--echo # Table st3: stored column as unique
--source include/rpl_connection_master.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');

# Perform XA Transaction on st3
XA START 'xstatement';
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';
XA END 'xstatement';
XA PREPARE 'xstatement';
XA COMMIT 'xstatement';

--source include/sync_slave_sql_with_master.inc

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

# Scenario 1.4 :

--echo # Table st4: Stored column as foreign key
--source include/rpl_connection_master.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);

# Perform XA Transaction on st4
XA START 'xstatement';
UPDATE st4 SET c1=21 WHERE c2=1;
DELETE FROM st4 WHERE c2=2;
DELETE FROM st4 WHERE c1=21;
XA END 'xstatement';
XA PREPARE 'xstatement';
XA COMMIT 'xstatement';

--source include/sync_slave_sql_with_master.inc

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

# Scenario 2.1 :

--echo # Table vt1: virtual column genarated on primary key
--source include/rpl_connection_master.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;

# Perform XA transaction on vt1
XA START 'xstatement';
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;
XA END 'xstatement';
XA PREPARE 'xstatement';
XA COMMIT 'xstatement';

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

# Scenario 2.2 :

--echo # Table  vt2: virtual column as unique key
--source include/rpl_connection_master.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';

# Perform XA Transaction on vt2
XA START 'xstatement';
UPDATE vt2 SET c1='second update' WHERE c2='se';
DELETE FROM vt2 WHERE c1='first update';
DELETE FROM vt2 WHERE c2='se';
XA END 'xstatement';
XA PREPARE 'xstatement';
XA COMMIT 'xstatement';

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

# Scenario 2.3 :

--echo # Table vt3: Virtual Column with foreign keys
--source include/rpl_connection_master.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 SET NULL 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 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;

# Perform XA Transaction on vt3
XA START 'xstatement';
DELETE FROM vt3 WHERE a=1;
DELETE FROM vt3 WHERE c=100;
XA END 'xstatement';
XA PREPARE 'xstatement';
XA COMMIT 'xstatement';

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

# Scenario 3.1 :

--echo # Table gt1: gt1: both stored and virtual columns
--source include/rpl_connection_master.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;

# Perform XA Transaction on gt1
XA START 'xstatement';
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;
XA END 'xstatement';
XA PREPARE 'xstatement';
XA COMMIT 'xstatement';

--enable_warnings

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

--echo # Clean Up

--source include/rpl_connection_master.inc
DROP TABLE st1,st2,st3,st4;
DROP TABLE vt1,vt2,vt3;
DROP TABLE gt1;
DROP TABLE t1,t2;

--source include/rpl_end.inc