-- //
-- *Purpose:*
--
-- * to duplicate the grad_degree virtual column mysql server crash bug for the mariadb team
--
-- *Design:*
--
-- * the data set is in the comma delimited file gso_grad_supr.csv
-- * to use as ansi standard sql as possible while taking advantage of mysql's query extensions for speed
-- * comments are in Textile format for converting to html using pandoc text library
--
-- *Observations:*
--
-- * after eliminating most columns, I have tracked the bug to these combination of columns:
-- ** the ofis_deg_status and deg_as_of_term being updated in the same query in combination with an index on deg_as_of_term
-- ** the deg_as_of_term's index is corrupted and thus crashing the database server
--
-- *Author:* Marlon A. Griffith
-- *Created:* Thurs, 2015-01-08
--
-- *Modified:*
--/

########################################
# For testing data input, turn off foreign key checks
#
SET FOREIGN_KEY_CHECKS=0;

SET FOREIGN_KEY_CHECKS=1;


-- h3. Table structure

-- recreate the data set table
DROP TABLE IF EXISTS gso_grad_supr;
CREATE TABLE IF NOT EXISTS gso_grad_supr (
  term char(4) NOT NULL DEFAULT '',
  uw_id int(8) UNSIGNED NOT NULL DEFAULT 0,
  plan varchar(10) NOT NULL DEFAULT '',

  wdraw_rsn varchar(4) NOT NULL DEFAULT '',

  admit_term char(4) NOT NULL DEFAULT '',

  CONSTRAINT gso_grad_supr_pky PRIMARY KEY (uw_id, term) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE INDEX gso_grad_supr_term_ky ON gso_grad_supr (term); 

-- 60 rows affected
LOAD DATA LOCAL INFILE '/private/var/folders/hc/vstzl6rj5pldz3zzwtk810hc0000gp/T/phpvyURf2' INTO TABLE `gso_grad_supr` FIELDS TERMINATED BY ',' ESCAPED BY '\\' LINES TERMINATED BY '\n' IGNORE 1 LINES;


-- h4. Table Test structure that works

-- 1. create modified data table without a deg_as_of_term index
DROP TABLE IF EXISTS grad_degree;
CREATE TABLE IF NOT EXISTS grad_degree (
  student_id int(8) UNSIGNED NOT NULL,
	plan varchar(10) NOT NULL,
  admit_term char(4) NOT NULL,

  wdraw_rsn varchar(4) NOT NULL DEFAULT '',

  ofis_deg_status varchar(15) AS (
		CASE
			WHEN wdraw_rsn = '' THEN 'In progress'
			WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed'
			ELSE 'Not Completed'
		END) VIRTUAL,

  deg_start_term char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data', 
  deg_as_of_term char(4) NOT NULL COMMENT 'In most cases also end term', 

  CONSTRAINT grad_degree_stu_plan_admit_pky PRIMARY KEY (student_id, plan, admit_term)
) ENGINE  = InnoDB  DEFAULT CHARSET  = utf8
;

CREATE INDEX grad_degree_wdraw_rsn_ndx ON grad_degree (wdraw_rsn);
CREATE INDEX grad_degree_admit_term_ndx ON grad_degree (admit_term);


-- 2. insert term 1031 data
-- 30 records affected
INSERT IGNORE grad_degree (
    student_id,
    plan,
    admit_term, 

    wdraw_rsn,

    deg_start_term, 
		deg_as_of_term
)
SELECT 
	ggs.uw_id AS c_student_id,
	ggs.plan,
	ggs.admit_term,

	ggs.wdraw_rsn,

	IF( (SELECT COUNT(*) FROM grad_degree AS gd WHERE gd.student_id = ggs.uw_id AND gd.admit_term = ggs.admit_term) > 0, ggs.term, ggs.admit_term ) AS c_deg_start_term, 
	ggs.term AS c_as_of_term 
FROM gso_grad_supr AS ggs
LEFT OUTER JOIN
        grad_degree AS gd
        ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term )
WHERE
        ggs.term = 1031 AND 
        gd.student_id IS NULL
;

-- 3. update any grad_degrees for term 1031
-- 0 records affected
UPDATE grad_degree AS gd 
INNER JOIN
        gso_grad_supr AS ggs 
        ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term )
SET 
    gd.wdraw_rsn = ggs.wdraw_rsn,

    gd.deg_as_of_term = 1031 
WHERE
        gd.wdraw_rsn NOT IN ('DCMP', 'TRDC') AND 
	ggs.term = 1031 
;

-- 4. insert term 1035 data
-- 0 records affected
INSERT IGNORE grad_degree (
    student_id,
    plan,
    admit_term, 

    wdraw_rsn,

    deg_start_term, 
		deg_as_of_term
)
SELECT 
	ggs.uw_id AS c_student_id,
	ggs.plan,
	ggs.admit_term,

	ggs.wdraw_rsn,

	IF( (SELECT COUNT(*) FROM grad_degree AS gd WHERE gd.student_id = ggs.uw_id AND gd.admit_term = ggs.admit_term) > 0, ggs.term, ggs.admit_term ) AS c_deg_start_term, 
	ggs.term AS c_as_of_term 
FROM gso_grad_supr AS ggs
LEFT OUTER JOIN
        grad_degree AS gd
        ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term )
WHERE
        ggs.term = 1035 AND 
        gd.student_id IS NULL
;

-- 5. update any grad_degrees for term 1035
-- 30 records affected
UPDATE grad_degree AS gd 
INNER JOIN
        gso_grad_supr AS ggs 
        ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term )
SET 
    gd.wdraw_rsn = ggs.wdraw_rsn,

    gd.deg_as_of_term = 1035 
WHERE
        gd.wdraw_rsn NOT IN ('DCMP', 'TRDC') AND 
	ggs.term = 1035 
;

-- RESULT: the mariadb server still runs!


-- h4. Table structure that does not work

-- rebuild the data table with the deg_as_of_term index added
DROP TABLE IF EXISTS grad_degree;
CREATE TABLE IF NOT EXISTS grad_degree (
  student_id int(8) UNSIGNED NOT NULL,
	plan varchar(10) NOT NULL,
  admit_term char(4) NOT NULL,

  wdraw_rsn varchar(4) NOT NULL DEFAULT '',

  ofis_deg_status varchar(15) AS (
		CASE
			WHEN wdraw_rsn = '' THEN 'In progress'
			WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed'
			ELSE 'Not Completed'
		END) VIRTUAL,

  deg_start_term char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data', 
  deg_as_of_term char(4) NOT NULL COMMENT 'In most cases also end term', 

  CONSTRAINT grad_degree_stu_plan_admit_pky PRIMARY KEY (student_id, plan, admit_term)
) ENGINE  = InnoDB  DEFAULT CHARSET  = utf8
;

CREATE INDEX grad_degree_wdraw_rsn_ndx ON grad_degree (wdraw_rsn);
CREATE INDEX grad_degree_admit_term_ndx ON grad_degree (admit_term);
CREATE INDEX grad_degree_as_of_term_ndx ON grad_degree (deg_as_of_term);

-- repeat steps 2 to 5
--
-- RESULT: the mariadb server has crashed and you will need to rebuild the test database!