mirror of
https://github.com/MariaDB/server.git
synced 2026-03-04 15:38:41 +01:00
7fed014308
columns Issue: - Purge thread attempts to purge a secondary index record that is not delete-marked. Root Cause: - When a secondary index includes a virtual column whose v_pos is greater than the number of fields in the clustered index record, the virtual column is incorrectly skipped while reading from the undo record. - This leads the purge logic to incorrectly assume it is safe to purge the secondary index record. - The code also confuses the nth virtual column with the nth stored column when writing ordering columns at the end of the undo record. Fix: - In trx_undo_update_rec_get_update(): Skip a virtual column only when v_pos == FIL_NULL, not when v_pos is greater than the number of fields. - In trx_undo_page_report_modify(): Ensure ordering columns are written based on the correct stored-column positions, without confusing them with virtual-column positions.
390 lines
17 KiB
Text
390 lines
17 KiB
Text
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;
|
|
Warnings:
|
|
Warning 1280 Name 'gso_grad_supr_pky' ignored for PRIMARY key.
|
|
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;
|
|
Warnings:
|
|
Warning 1280 Name 'grad_degree_stu_plan_admit_pky' ignored for PRIMARY key.
|
|
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) GENERATED ALWAYS 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',
|
|
PRIMARY KEY (`student_id`,`plan`,`admit_term`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
|
|
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;
|
|
Warnings:
|
|
Warning 1280 Name 'grad_degree_stu_plan_admit_pky' ignored for PRIMARY key.
|
|
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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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',
|
|
PRIMARY KEY (`student_id`,`plan`,`admit_term`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
|
|
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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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=utf8mb3 COLLATE=utf8mb3_general_ci
|
|
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;
|
|
Warnings:
|
|
Warning 1280 Name 'grad_degree_stu_plan_admit_pky' ignored for PRIMARY key.
|
|
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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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',
|
|
PRIMARY KEY (`student_id`,`plan`,`admit_term`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
|
|
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;
|
|
CREATE TABLE t1 (a INT, b CHAR(12), c INT AS (a) VIRTUAL, FULLTEXT KEY(b)) ENGINE=InnoDB;
|
|
INSERT INTO t1 (a,b) VALUES (1,'foo');
|
|
SELECT * FROM t1;
|
|
a b c
|
|
1 foo 1
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (a INT, b CHAR(12), c INT AS (a) VIRTUAL) ENGINE=InnoDB;
|
|
INSERT INTO t1 (a,b) VALUES (1,'foo');
|
|
ALTER TABLE t1 ADD FULLTEXT KEY(b);
|
|
SELECT * FROM t1;
|
|
a b c
|
|
1 foo 1
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (
|
|
id int primary key,value varchar(2),
|
|
vcomplete int AS (cast(substr(value,1,1) as int)),
|
|
va int AS (null),vb int AS (null),vc int AS (null),
|
|
vresult char(1) AS (substr(value,2)),
|
|
KEY key1(vresult,vcomplete)
|
|
) ENGINE=InnoDB;
|
|
BEGIN;
|
|
INSERT INTO t1(id,value) VALUES (1,'0F');
|
|
UPDATE t1 SET value = '1S';
|
|
UPDATE t1 SET value = '0F';
|
|
COMMIT;
|
|
SET GLOBAL innodb_max_purge_lag_wait=0;
|
|
SELECT * FROM t1;
|
|
id value vcomplete va vb vc vresult
|
|
1 0F 0 NULL NULL NULL F
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (
|
|
col1 int primary key,
|
|
col2 int,
|
|
col3 int,
|
|
col4 varchar(5),
|
|
v_col1 char(1) AS (substr(col4,1,1)),
|
|
v_col2 char(1) AS (substr(col4,2,1)),
|
|
v_col3 char(1) AS (substr(col4,3,1)),
|
|
v_col4 char(1) AS (substr(col4,4,1)),
|
|
v_col5 char(1) AS (substr(col4,5,1)),
|
|
KEY key1(v_col2, v_col1, v_col3, v_col4, v_col5),
|
|
KEY key2(col2, col3)
|
|
) ENGINE=InnoDB;
|
|
INSERT INTO t1(col1,col2,col3,col4) VALUES (1,1,1,'ABCDE');
|
|
UPDATE t1 SET col2=2, col4 = 'PQRST';
|
|
SET GLOBAL innodb_max_purge_lag_wait=0;
|
|
CHECK TABLE t1 EXTENDED;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
DROP TABLE t1;
|