diff options
author | Jan Lindström <jan.lindstrom@mariadb.com> | 2015-06-24 07:16:08 +0300 |
---|---|---|
committer | Jan Lindström <jan.lindstrom@mariadb.com> | 2015-06-24 07:16:08 +0300 |
commit | 2e4984c185ddcd2da789017cd147338846ff409a (patch) | |
tree | 0293831900c860600efbaa747ea886d9d1cbf5bd /mysql-test/suite/innodb/r/innodb-virtual-columns.result | |
parent | 792b53e80806df893ee62c9a1c1bd117114c8c6d (diff) | |
parent | a6087e7dc1ef3561d8189c8db15e9591d0f9b520 (diff) | |
download | mariadb-git-2e4984c185ddcd2da789017cd147338846ff409a.tar.gz |
Merge tag 'mariadb-10.0.20' into 10.0-FusionIO10.0-FusionIO
Conflicts:
storage/innobase/os/os0file.cc
storage/xtradb/os/os0file.cc
storage/xtradb/srv/srv0start.cc
Diffstat (limited to 'mysql-test/suite/innodb/r/innodb-virtual-columns.result')
-rw-r--r-- | mysql-test/suite/innodb/r/innodb-virtual-columns.result | 322 |
1 files changed, 322 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/r/innodb-virtual-columns.result b/mysql-test/suite/innodb/r/innodb-virtual-columns.result new file mode 100644 index 00000000000..558bb23de0a --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-virtual-columns.result @@ -0,0 +1,322 @@ +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; +INSERT INTO `gso_grad_supr` VALUES ('1031',2,'CSM','','1009'); +INSERT INTO `gso_grad_supr` VALUES ('1035',2,'CSM','ACAD','1009'); +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_as_of_term_ndx ON grad_degree (deg_as_of_term); +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 +; +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 +; +drop table 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, +ofis_deg_status2 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress2' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed2' + ELSE 'Not Completed2' + END) VIRTUAL, +ofis_deg_status3 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress3' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed3' + ELSE 'Not Completed3' + END) VIRTUAL, +ofis_deg_status4 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress4' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed4' + ELSE 'Not Completed4' + END) VIRTUAL, +ofis_deg_status5 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress5' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed5' + ELSE 'Not Completed5' + END) VIRTUAL, +ofis_deg_status6 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress6' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed6' + ELSE 'Not Completed6' + END) VIRTUAL, +ofis_deg_status7 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress7' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed7' + ELSE 'Not Completed7' + END) VIRTUAL, +ofis_deg_status8 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress8' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed8' + ELSE 'Not Completed8' + 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_as_of_term_ndx ON grad_degree (deg_as_of_term); +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 +; +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 +; +ALTER TABLE grad_degree DROP INDEX grad_degree_wdraw_rsn_ndx; +ALTER TABLE grad_degree DROP COLUMN deg_start_term; +SHOW CREATE TABLE grad_degree; +Table Create Table +grad_degree CREATE TABLE `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, + `ofis_deg_status2` varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress2' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed2' + ELSE 'Not Completed2' + END) VIRTUAL, + `ofis_deg_status3` varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress3' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed3' + ELSE 'Not Completed3' + END) VIRTUAL, + `ofis_deg_status4` varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress4' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed4' + ELSE 'Not Completed4' + END) VIRTUAL, + `ofis_deg_status5` varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress5' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed5' + ELSE 'Not Completed5' + END) VIRTUAL, + `ofis_deg_status6` varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress6' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed6' + ELSE 'Not Completed6' + END) VIRTUAL, + `ofis_deg_status7` varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress7' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed7' + ELSE 'Not Completed7' + END) VIRTUAL, + `ofis_deg_status8` varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress8' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed8' + ELSE 'Not Completed8' + END) VIRTUAL, + `deg_as_of_term` char(4) NOT NULL COMMENT 'In most cases also end term', + PRIMARY KEY (`student_id`,`plan`,`admit_term`), + KEY `grad_degree_as_of_term_ndx` (`deg_as_of_term`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 +DROP TABLE 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, +ofis_deg_status2 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress2' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed2' + ELSE 'Not Completed2' + END) VIRTUAL, +ofis_deg_status3 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress3' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed3' + ELSE 'Not Completed3' + END) VIRTUAL, +ofis_deg_status4 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress4' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed4' + ELSE 'Not Completed4' + END) VIRTUAL, +ofis_deg_status5 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress5' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed5' + ELSE 'Not Completed5' + END) VIRTUAL, +ofis_deg_status6 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress6' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed6' + ELSE 'Not Completed6' + END) VIRTUAL, +ofis_deg_status7 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress7' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed7' + ELSE 'Not Completed7' + END) VIRTUAL, +ofis_deg_status8 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress8' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed8' + ELSE 'Not Completed8' + 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); +ALTER TABLE grad_degree DROP COLUMN ofis_deg_status2, DROP COLUMN ofis_deg_status3, +DROP COLUMN ofis_deg_status4, DROP COLUMN ofis_deg_status5, DROP COLUMN ofis_deg_status6, +DROP COLUMN ofis_deg_status7, DROP COLUMN ofis_deg_status8; +CREATE INDEX grad_degree_as_of_term_ndx ON grad_degree (deg_as_of_term); +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 +; +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 +; +select * from grad_degree; +student_id plan admit_term wdraw_rsn ofis_deg_status deg_start_term deg_as_of_term +2 CSM 1009 ACAD Not Completed 1009 1035 +select * from gso_grad_supr; +term uw_id plan wdraw_rsn admit_term +1031 2 CSM 1009 +1035 2 CSM ACAD 1009 +drop table grad_degree; +drop table gso_grad_supr; |