set tidb_cost_model_version=1;
# ----------------------------------------------------------------------
# Time consuming tests of window functions.
# ----------------------------------------------------------------------
# ----------------------------------------------------------------------
# Test of SQL window functions with frame buffer in MEM only and with
# spill over to INNODB as tmp file for the frame buffer (slower)
# ----------------------------------------------------------------------
default size is 16777216. Use 10 * 16777216 to fit frame buffer in memory
SET tmp_table_size=167772160;
SET max_heap_table_size=167772160;
CREATE TABLE td(d INT);
INSERT INTO td(d) VALUES (10),(1),(2),(3),(4),(5),(6),(7),(8),(9);
INSERT INTO td(d) SELECT d+10 FROM td UNION
SELECT d+20 FROM td UNION
SELECT d+30 FROM td UNION
SELECT d+40 FROM td UNION
SELECT d+50 FROM td UNION
SELECT d+60 FROM td UNION
SELECT d+70 FROM td UNION
SELECT d+80 FROM td UNION
SELECT d+90 FROM td;
INSERT INTO td(d) SELECT d+100 FROM td UNION
SELECT d+200 FROM td UNION
SELECT d+300 FROM td UNION
SELECT d+400 FROM td UNION
SELECT d+500 FROM td UNION
SELECT d+600 FROM td UNION
SELECT d+700 FROM td UNION
SELECT d+800 FROM td UNION
SELECT d+900 FROM td;
INSERT INTO td(d) SELECT d+1000 FROM td UNION
SELECT d+2000 FROM td UNION
SELECT d+3000 FROM td UNION
SELECT d+4000 FROM td UNION
SELECT d+5000 FROM td UNION
SELECT d+6000 FROM td UNION
SELECT d+7000 FROM td UNION
SELECT d+8000 FROM td UNION
SELECT d+9000 FROM td;
CREATE TABLE cpy(d INT, summ INT, nth INT, lagg INT);
Frame buffer in MEM engine
INSERT INTO cpy SELECT d, SUM(d) OVER w summ, NTH_VALUE(d, 3) OVER w nth, LAG(d, 20) OVER w lagg FROM td
WINDOW w AS (ORDER BY d ROWS BETWEEN 10 PRECEDING AND 10 FOLLOWING);
SELECT * from cpy ORDER BY d DESC LIMIT 10;
d	summ	nth	lagg
10000	109945	9992	9980
9999	119934	9991	9979
9998	129922	9990	9978
9997	139909	9989	9977
9996	149895	9988	9976
9995	159880	9987	9975
9994	169864	9986	9974
9993	179847	9985	9973
9992	189829	9984	9972
9991	199810	9983	9971
TRUNCATE cpy;
set tmp_table_size=16384;
set max_heap_table_size=16384;
Spill frame buffer to INNODB
INSERT INTO cpy SELECT d, SUM(d) OVER w summ, NTH_VALUE(d, 3) OVER w nth, LAG(d, 20) OVER w lagg FROM td
WINDOW w AS (ORDER BY d ROWS BETWEEN 10 PRECEDING AND 10 FOLLOWING);
SELECT * from cpy ORDER BY d DESC LIMIT 10;
d	summ	nth	lagg
10000	109945	9992	9980
9999	119934	9991	9979
9998	129922	9990	9978
9997	139909	9989	9977
9996	149895	9988	9976
9995	159880	9987	9975
9994	169864	9986	9974
9993	179847	9985	9973
9992	189829	9984	9972
9991	199810	9983	9971
DROP TABLE td, cpy;
Set tmp_table_size and max_heap_table_size back to default
SET tmp_table_size=DEFAULT;
SET max_heap_table_size=DEFAULT;
#
# Bug#25894783 WL9603: CAN'T FIND RECORD IN '/TMP/#SQL6E16_3_4'
#
SET SQL_MODE='';
SET @sav_big= (SELECT COALESCE(@session.big_tables, 0));
SET SESSION big_tables=1;
CREATE TABLE `C` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int` int(11) NOT NULL,
`col_int_gckey` int(11) GENERATED ALWAYS AS ((`col_int` + `col_int`)) VIRTUAL NOT NULL,
`col_int_key` int(11) GENERATED ALWAYS AS ((`col_int_gckey` + `col_int`)) VIRTUAL NOT NULL,
`col_date` date NOT NULL,
`col_date_gckey` date GENERATED ALWAYS AS ((`col_date` + interval 30 day)) VIRTUAL NOT NULL,
`col_date_key` date GENERATED ALWAYS AS ((`col_date_gckey` + interval 30 day)) VIRTUAL NOT NULL,
`col_datetime` datetime NOT NULL,
`col_time` time NOT NULL,
`col_datetime_gckey` datetime GENERATED ALWAYS AS (addtime(`col_datetime`,`col_time`)) VIRTUAL NOT NULL,
`col_time_gckey` time GENERATED ALWAYS AS (addtime(`col_datetime`,`col_time`)) VIRTUAL NOT NULL,
`col_datetime_key` datetime GENERATED ALWAYS AS (addtime(`col_datetime_gckey`,`col_time`)) VIRTUAL NOT NULL,
`col_time_key` time GENERATED ALWAYS AS (addtime(`col_datetime`,`col_time_gckey`)) VIRTUAL NOT NULL,
`col_varchar` varchar(15) NOT NULL,
`col_varchar_gckey` varchar(15) GENERATED ALWAYS AS (concat(`col_varchar`,`col_varchar`)) VIRTUAL NOT NULL,
`col_varchar_key` varchar(15) GENERATED ALWAYS AS (concat(`col_varchar_gckey`,_latin1'x')) VIRTUAL NOT NULL,
PRIMARY KEY (`pk`),
UNIQUE KEY `col_date_key` (`col_date_key`),
UNIQUE KEY `col_date_key_2` (`col_date_key`,`col_time_key`,`col_datetime_key`),
UNIQUE KEY `col_varchar_key` (`col_varchar_key`(3)),
UNIQUE KEY `col_varchar_key_2` (`col_varchar_key`(5) DESC),
UNIQUE KEY `col_int_key_3` (`col_int_key`,`col_varchar_key`(5)),
UNIQUE KEY `col_int_key_4` (`col_int_key` DESC,`col_varchar_key`(5)),
UNIQUE KEY `col_varchar_key_3` (`col_varchar_key`(2),`col_varchar`(3)),
UNIQUE KEY `col_varchar_key_4` (`col_varchar_key`(2) DESC,`col_varchar`(3) DESC),
UNIQUE KEY `col_int_key_10` (`col_int_key`,`col_varchar_key`(5),`col_date_key`,`col_time_key`,`col_datetime_key`),
KEY `col_int_gckey` (`col_int_gckey`),
KEY `col_int_gckey_2` (`col_int_gckey` DESC),
KEY `col_date_gckey` (`col_date_gckey`),
KEY `col_datetime_gckey` (`col_datetime_gckey`),
KEY `col_time_gckey` (`col_time_gckey`),
KEY `col_varchar_gckey` (`col_varchar_gckey`(5)),
KEY `col_int_key` (`col_int_key`),
KEY `col_int_key_2` (`col_int_key` DESC),
KEY `col_time_key` (`col_time_key`),
KEY `col_datetime_key` (`col_datetime_key`),
KEY `col_int_key_5` (`col_int_key`,`col_int`),
KEY `col_int_key_6` (`col_int_key` DESC,`col_int` DESC),
KEY `col_int_key_7` (`col_int_key`,`col_date_key`),
KEY `col_int_key_8` (`col_int_key`,`col_time_key`),
KEY `col_int_key_9` (`col_int_key`,`col_datetime_key`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;
INSERT INTO `C` (`pk`, `col_int`, `col_date`, `col_datetime`, `col_time`, `col_varchar`)
VALUES (1, 9, '2009-11-04', '2006-10-12 19:52:02', '18:19:40', 'a'),
(2, 4, '2009-05-21', '2005-09-13 00:00:00', '07:45:25', 'tef'),
(3, 0, '1900-01-01', '2002-09-03 04:42:41', '13:17:14', 'efqsd'),
(4, 149, '2000-11-05', '2007-02-08 07:29:31', '10:38:21', 'fqsdk'),
(5, 8, '0000-00-00', '2000-11-07 15:28:31', '23:04:47', 'qsdksji'),
(6, 8, '2002-06-07', '2007-09-19 02:35:12', '07:33:31', 'sdks'),
(7, 5, '2008-06-02', '1900-01-01 00:00:00', '14:41:02', 'dksjij'),
(8, 7, '2000-07-26', '2007-11-27 00:19:33', '23:30:25', 'sjijcsz'),
(9, 8, '2008-09-16', '2004-12-17 11:22:46', '06:11:14', 'i'),
(10, 104, '2002-03-06', '2007-02-04 13:09:16', '22:24:50', 'jcszxw'),
(11, 1, '2004-01-10', '2008-03-19 08:36:41', '00:03:00', 'csz'),
(12, 4, '2002-02-21', '2008-03-27 03:09:30', '06:52:39', 'szxwbjj'),
(13, 8, '2004-07-01', '2001-10-20 06:42:39', '08:49:41', 'xwb'),
(14, 7, '2008-08-13', '2002-04-05 00:00:00', '05:52:03', 'wbjjvvk'),
(15, 8, '2008-12-18', '1900-01-01 00:00:00', '00:00:00', 'bj'),
(16, 6, '2002-08-03', '2008-04-14 09:20:36', '00:00:00', 'jjvvk'),
(17, 97, '2001-06-11', '2002-11-07 00:00:00', '13:30:55', 'j');
CREATE TABLE `DD` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int` int(11) NOT NULL,
`col_int_gckey` int(11) GENERATED ALWAYS AS ((`col_int` + `col_int`)) VIRTUAL NOT NULL,
`col_int_key` int(11) GENERATED ALWAYS AS ((`col_int_gckey` + `col_int`)) VIRTUAL NOT NULL,
`col_date` date NOT NULL,
`col_date_gckey` date GENERATED ALWAYS AS ((`col_date` + interval 30 day)) VIRTUAL NOT NULL,
`col_date_key` date GENERATED ALWAYS AS ((`col_date_gckey` + interval 30 day)) VIRTUAL NOT NULL,
`col_datetime` datetime NOT NULL,
`col_time` time NOT NULL,
`col_datetime_gckey` datetime GENERATED ALWAYS AS (addtime(`col_datetime`,`col_time`)) VIRTUAL NOT NULL,
`col_time_gckey` time GENERATED ALWAYS AS (addtime(`col_datetime`,`col_time`)) VIRTUAL NOT NULL,
`col_datetime_key` datetime GENERATED ALWAYS AS (addtime(`col_datetime_gckey`,`col_time`)) VIRTUAL NOT NULL,
`col_time_key` time GENERATED ALWAYS AS (addtime(`col_datetime`,`col_time_gckey`)) VIRTUAL NOT NULL,
`col_varchar` varchar(15) NOT NULL,
`col_varchar_gckey` varchar(15) GENERATED ALWAYS AS (concat(`col_varchar`,`col_varchar`)) VIRTUAL NOT NULL,
`col_varchar_key` varchar(15) GENERATED ALWAYS AS (concat(`col_varchar_gckey`,_latin1'x')) VIRTUAL NOT NULL,
PRIMARY KEY (`pk`),
UNIQUE KEY `col_date_key` (`col_date_key`),
UNIQUE KEY `col_date_key_2` (`col_date_key`,`col_time_key`,`col_datetime_key`),
UNIQUE KEY `col_varchar_key` (`col_varchar_key`(3)),
UNIQUE KEY `col_varchar_key_2` (`col_varchar_key`(5) DESC),
UNIQUE KEY `col_int_key_3` (`col_int_key`,`col_varchar_key`(5)),
UNIQUE KEY `col_int_key_4` (`col_int_key` DESC,`col_varchar_key`(5)),
UNIQUE KEY `col_varchar_key_3` (`col_varchar_key`(2),`col_varchar`(3)),
UNIQUE KEY `col_varchar_key_4` (`col_varchar_key`(2) DESC,`col_varchar`(3) DESC),
UNIQUE KEY `col_int_key_10` (`col_int_key`,`col_varchar_key`(5),`col_date_key`,`col_time_key`,`col_datetime_key`),
KEY `col_int_gckey` (`col_int_gckey`),
KEY `col_int_gckey_2` (`col_int_gckey` DESC),
KEY `col_date_gckey` (`col_date_gckey`),
KEY `col_datetime_gckey` (`col_datetime_gckey`),
KEY `col_time_gckey` (`col_time_gckey`),
KEY `col_varchar_gckey` (`col_varchar_gckey`(5)),
KEY `col_int_key` (`col_int_key`),
KEY `col_int_key_2` (`col_int_key` DESC),
KEY `col_time_key` (`col_time_key`),
KEY `col_datetime_key` (`col_datetime_key`),
KEY `col_int_key_5` (`col_int_key`,`col_int`),
KEY `col_int_key_6` (`col_int_key` DESC,`col_int` DESC),
KEY `col_int_key_7` (`col_int_key`,`col_date_key`),
KEY `col_int_key_8` (`col_int_key`,`col_time_key`),
KEY `col_int_key_9` (`col_int_key`,`col_datetime_key`)
) ENGINE=InnoDB AUTO_INCREMENT=73 DEFAULT CHARSET=latin1;
INSERT INTO `DD` (`pk`, `col_int`, `col_date`, `col_datetime`, `col_time`, `col_varchar`)
VALUES (10,7,'0000-00-00','2000-02-09 06:46:23','03:56:10','i'),
(11,5,'2008-12-11','2004-03-07 18:05:11','00:00:00','jrll'),
(12,7,'2005-11-18','2001-01-18 08:29:29','20:17:57','rllqunt'),
(13,9,'2009-02-08','2005-10-25 00:00:00','08:09:49','l'),
(14,3,'2002-05-26','2009-09-01 10:19:05','09:40:42','lq'),
(15,66,'2002-03-10','2002-09-06 04:43:02','08:28:55','quntp'),
(16,3,'2003-07-07','2006-04-07 00:00:00','20:12:00','untppi'),
(17,95,'2006-06-22','2004-05-08 00:00:00','18:50:24','ntppirz'),
(18,7,'2004-01-21','2000-01-23 03:34:04','17:01:57','tppirzd'),
(19,5,'2001-05-01','2005-12-26 20:42:01','15:11:27','pirzdp'),
(20,8,'2008-12-15','1900-01-01 00:00:00','05:49:51','irzd'),
(21,3,'2000-08-28','2003-02-28 16:30:52','14:58:44','zdphpdu'),
(22,96,'2008-06-08','2005-09-15 03:55:22','02:20:01','dp'),
(23,9,'2002-04-02','2001-01-08 10:44:10','19:03:57','p'),
(24,3,'2005-03-04','2001-03-23 00:00:00','00:27:13','h'),
(25,8,'2001-01-21','2004-03-02 00:00:00','13:39:32','pduhwq'),
(26,8,'2006-10-05','1900-01-01 00:00:00','08:06:08','uhwqh'),
(27,4,'2001-12-26','2006-10-24 05:59:20','16:15:34','hwqh'),
(28,7,'1900-01-01','2005-06-14 00:00:00','12:04:50','wqhnsm'),
(29,6,'2007-12-02','2001-08-25 03:00:31','00:00:00','qh'),
(30,4,'2009-02-06','2001-06-14 19:13:14','06:00:42','nsmu'),
(31,9,'2007-01-15','2006-12-18 07:54:16','11:18:35','smujjj'),
(32,5,'2004-11-07','2000-09-18 04:53:37','16:20:06','muj'),
(33,1,'2003-12-07','2002-08-18 04:47:11','01:41:35','jj'),
(34,1,'2008-09-07','2000-10-14 16:58:18','17:42:13','jbld'),
(35,5,'2005-03-08','2008-11-22 16:40:01','00:59:59','bldnki'),
(36,181,'2006-11-18','1900-01-01 00:00:00','00:00:00','nkiws'),
(37,5,'2007-01-26','2008-01-21 00:00:00','02:16:04','kiwsr'),
(38,1,'2003-08-24','1900-01-01 00:00:00','00:00:00','iwsrsx'),
(39,162,'2001-12-01','2008-05-17 00:00:00','14:34:36','srsxnd'),
(40,8,'2003-07-02','2000-06-07 00:00:00','23:02:05','r'),
(41,2,'2007-03-01','2009-01-03 12:22:04','00:00:00','sxndo'),
(42,7,'2009-08-04','2009-10-05 04:15:15','00:00:00','xndolp'),
(43,119,'2000-05-03','2002-02-17 23:12:12','23:23:35','olpujd'),
(44,3,'2001-05-18','2008-03-27 11:51:54','11:26:20','lp'),
(45,119,'2004-02-22','1900-01-01 00:00:00','00:00:00','pu'),
(46,8,'2002-07-15','2008-08-24 21:36:28','12:51:37','dnozrhh'),
(47,2,'2008-04-22','2005-01-12 08:50:22','20:55:45','no'),
(48,4,'2006-06-01','2000-04-20 00:00:00','13:02:05','ozrhhcx'),
(49,8,'2009-09-12','2000-02-16 03:57:05','17:04:35','zrhhcxs'),
(50,6,'2009-01-06','1900-01-01 00:00:00','05:15:45','rhhcxsx'),
(51,6,'2008-07-13','2002-04-27 14:13:27','00:00:00','hhcxsxw'),
(52,8,'2002-03-15','2008-01-17 20:30:57','07:09:22','hcxsxw'),
(53,6,'2007-10-14','2006-10-11 22:48:02','06:11:59','cxs'),
(54,1,'2008-07-23','2005-09-11 07:19:40','03:05:06','x'),
(55,1,'2007-05-22','2002-11-24 16:25:27','10:10:42','s'),
(56,6,'2008-01-08','2005-06-09 01:11:17','06:03:27','w'),
(57,9,'2006-10-18','1900-01-01 00:00:00','00:00:00','uju'),
(58,7,'2000-07-22','1900-01-01 00:00:00','00:00:00','ju'),
(59,6,'2004-07-21','2009-10-25 16:05:29','11:04:39','ul'),
(60,2,'2001-10-03','2002-06-13 11:41:55','10:20:49','lpjd'),
(61,8,'2002-08-17','1900-01-01 00:00:00','00:00:00','jdz'),
(62,0,'2009-11-10','2000-05-04 05:15:19','00:00:00','zvkpaij'),
(63,6,'2005-06-26','2002-08-19 00:00:00','09:21:09','vkpaij'),
(64,6,'2000-06-04','2002-03-22 04:37:00','00:00:00','kp'),
(65,9,'2005-10-02','2009-01-10 09:03:59','04:56:37','paiju'),
(66,0,'2009-11-13','1900-01-01 00:00:00','00:00:00','aij'),
(67,0,'2006-11-26','2001-09-21 00:00:00','08:16:28','ijurspr'),
(68,6,'2007-09-24','2003-08-27 05:11:09','19:55:11','j'),
(69,0,'2009-01-24','1900-01-01 00:00:00','11:25:58','urspr'),
(70,5,'2001-06-22','2005-07-07 00:00:00','14:38:03','rsprn'),
(71,4,'2006-07-18','2000-07-16 06:17:20','15:32:00','sprnw'),
(72,5,'2009-05-12','2007-07-26 00:00:00','09:25:59','rnwgrp');
CREATE TABLE `E` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int` int(11) NOT NULL,
`col_int_gckey` int(11) GENERATED ALWAYS AS ((`col_int` + `col_int`)) VIRTUAL NOT NULL,
`col_int_key` int(11) GENERATED ALWAYS AS ((`col_int_gckey` + `col_int`)) VIRTUAL NOT NULL,
`col_date` date NOT NULL,
`col_date_gckey` date GENERATED ALWAYS AS ((`col_date` + interval 30 day)) VIRTUAL NOT NULL,
`col_date_key` date GENERATED ALWAYS AS ((`col_date_gckey` + interval 30 day)) VIRTUAL NOT NULL,
`col_datetime` datetime NOT NULL,
`col_time` time NOT NULL,
`col_datetime_gckey` datetime GENERATED ALWAYS AS (addtime(`col_datetime`,`col_time`)) VIRTUAL NOT NULL,
`col_time_gckey` time GENERATED ALWAYS AS (addtime(`col_datetime`,`col_time`)) VIRTUAL NOT NULL,
`col_datetime_key` datetime GENERATED ALWAYS AS (addtime(`col_datetime_gckey`,`col_time`)) VIRTUAL NOT NULL,
`col_time_key` time GENERATED ALWAYS AS (addtime(`col_datetime`,`col_time_gckey`)) VIRTUAL NOT NULL,
`col_varchar` varchar(15) NOT NULL,
`col_varchar_gckey` varchar(15) GENERATED ALWAYS AS (concat(`col_varchar`,`col_varchar`)) VIRTUAL NOT NULL,
`col_varchar_key` varchar(15) GENERATED ALWAYS AS (concat(`col_varchar_gckey`,_latin1'x')) VIRTUAL NOT NULL,
PRIMARY KEY (`pk`),
UNIQUE KEY `col_date_key` (`col_date_key`),
UNIQUE KEY `col_date_key_2` (`col_date_key`,`col_time_key`,`col_datetime_key`),
UNIQUE KEY `col_varchar_key` (`col_varchar_key`(3)),
UNIQUE KEY `col_varchar_key_2` (`col_varchar_key`(5) DESC),
UNIQUE KEY `col_int_key_3` (`col_int_key`,`col_varchar_key`(5)),
UNIQUE KEY `col_int_key_4` (`col_int_key` DESC,`col_varchar_key`(5)),
UNIQUE KEY `col_varchar_key_3` (`col_varchar_key`(2),`col_varchar`(3)),
UNIQUE KEY `col_varchar_key_4` (`col_varchar_key`(2) DESC,`col_varchar`(3) DESC),
UNIQUE KEY `col_int_key_10` (`col_int_key`,`col_varchar_key`(5),`col_date_key`,`col_time_key`,`col_datetime_key`),
KEY `col_int_gckey` (`col_int_gckey`),
KEY `col_int_gckey_2` (`col_int_gckey` DESC),
KEY `col_date_gckey` (`col_date_gckey`),
KEY `col_datetime_gckey` (`col_datetime_gckey`),
KEY `col_time_gckey` (`col_time_gckey`),
KEY `col_varchar_gckey` (`col_varchar_gckey`(5)),
KEY `col_int_key` (`col_int_key`),
KEY `col_int_key_2` (`col_int_key` DESC),
KEY `col_time_key` (`col_time_key`),
KEY `col_datetime_key` (`col_datetime_key`),
KEY `col_int_key_5` (`col_int_key`,`col_int`),
KEY `col_int_key_6` (`col_int_key` DESC,`col_int` DESC),
KEY `col_int_key_7` (`col_int_key`,`col_date_key`),
KEY `col_int_key_8` (`col_int_key`,`col_time_key`),
KEY `col_int_key_9` (`col_int_key`,`col_datetime_key`)
) ENGINE=InnoDB AUTO_INCREMENT=80 DEFAULT CHARSET=latin1;
INSERT INTO `E` (`pk`, `col_int`, `col_date`, `col_datetime`, `col_time`, `col_varchar`)
VALUES (1, 202, '0000-00-00', '2008-11-25 09:14:26', '07:23:12', 'en'),
(2, 4, '2005-07-10', '2005-03-15 22:48:25', '23:28:02', 'nchyhu'),
(3, 7, '2005-06-09', '2006-11-22 00:00:00', '10:51:23', 'chy'),
(4, 2, '2007-12-08', '2007-11-01 09:02:50', '01:12:13', 'hyhu'),
(5, 7, '2007-12-22', '2001-04-08 00:00:00', '06:34:46', 'yhuoo'),
(6, 1, '1900-01-01', '2001-11-27 19:47:15', '10:16:53', 'huoo'),
(7, 7, '2002-10-07', '2009-09-15 04:42:26', '07:07:58', 'uoowit'),
(8, 7, '2005-01-09', '2001-08-12 02:07:43', '06:15:07', 'oo'),
(9, 3, '2007-10-12', '2009-05-09 17:06:27', '00:00:00', 'ow'),
(10, 3, '2004-01-22', '1900-01-01 00:00:00', '06:41:21', 'wityzg'),
(11, 5, '2007-10-11', '2000-03-03 23:40:04', '22:28:00', 'ityzg'),
(12, 8, '2001-08-19', '2005-10-18 17:41:54', '04:47:49', 'tyz'),
(13, 9, '2001-02-12', '2000-03-23 23:22:54', '03:24:01', 'gktbkjr'),
(14, 0, '2000-07-14', '2007-01-25 11:00:51', '14:37:06', 'ktbkjrk'),
(15, 4, '2007-11-14', '2003-12-21 10:46:23', '05:53:49', 'tbkjrkm'),
(16, 9, '2004-01-25', '2003-09-02 01:45:27', '00:00:00', 'k'),
(17, 2, '2003-12-15', '2009-05-28 08:03:38', '23:41:09', 'j'),
(18, 4, '2002-01-25', '2003-10-23 18:22:15', '09:26:45', 'kmqm'),
(19, 0, '2009-09-08', '2001-12-28 00:00:00', '17:04:03', 'mq'),
(20, 7, '2008-03-15', '2005-05-06 19:42:18', '02:15:17', 'mkn'),
(21, 0, '2005-11-10', '2003-03-05 00:00:00', '00:00:00', 'knbtoe'),
(22, 1, '2008-11-12', '2001-12-26 16:47:05', '19:09:36', 'n'),
(23, 2, '2007-11-22', '2003-02-09 00:00:00', '07:55:11', 'btoer'),
(24, 4, '2002-04-25', '2008-10-13 00:00:00', '11:24:50', 'toe'),
(25, 4, '2004-02-14', '2001-07-16 16:05:48', '08:46:01', 'oervq'),
(26, 4, '2004-04-21', '2004-04-23 14:00:22', '20:16:19', 'rvqlzs'),
(27, 3, '2003-03-26', '2002-11-10 08:15:17', '13:03:14', 'vqlzs'),
(28, 0, '2007-06-18', '2006-06-24 03:59:58', '06:11:33', 'qlzsva'),
(29, 5, '2006-12-09', '2008-04-08 18:06:18', '09:40:31', 'lzsvasu'),
(30, 8, '2001-10-01', '2000-10-12 16:32:35', '03:34:01', 'zsvasu'),
(31, 6, '2001-01-07', '2005-09-11 10:09:54', '00:00:00', 'svas'),
(32, 0, '2007-11-02', '2009-09-10 01:44:18', '12:23:27', 'v'),
(33, 9, '2005-07-23', '2002-10-20 21:55:02', '05:12:10', 'surqdhu'),
(34, 4, '2003-09-13', '2009-11-03 09:54:42', '20:54:06', 'urqdh'),
(35, 165, '2001-05-14', '2002-10-19 00:00:00', '00:00:00', 'rqd'),
(36, 2, '2006-07-04', '2008-10-26 00:00:00', '00:59:06', 'qdhu'),
(37, 6, '2001-08-15', '2002-08-14 14:52:08', '07:22:34', 'dhu'),
(38, 5, '2000-04-27', '2007-06-10 00:00:00', '11:27:19', 'hucjx'),
(39, 9, '2007-10-13', '2002-07-07 04:10:43', '10:03:09', 'uc'),
(40, 214, '2004-02-06', '2007-08-15 13:56:29', '23:00:35', 'cjxd'),
(41, 194, '2008-12-27', '1900-01-01 00:00:00', '11:59:05', 'jx'),
(42, 1, '2002-08-16', '2000-08-11 11:34:38', '21:39:43', 'xdo'),
(43, 220, '2001-06-17', '1900-01-01 00:00:00', '00:00:00', 'oyg'),
(44, 9, '2002-10-16', '2008-12-07 23:41:33', '00:00:00', 'gx'),
(45, 248, '2008-04-06', '1900-01-01 00:00:00', '12:32:24', 'x'),
(46, 0, '2000-07-08', '2001-12-27 19:38:22', '00:00:00', 'vgqmw'),
(47, 0, '2005-03-16', '1900-01-01 00:00:00', '06:22:01', 'qmwcid'),
(48, 4, '2002-06-19', '2007-03-08 02:43:50', '07:00:21', 'mwc'),
(49, 3, '2005-11-25', '2001-11-14 17:21:32', '17:59:20', 'wcidtu'),
(50, 7, '2007-07-08', '1900-01-01 00:00:00', '01:58:05', 'cidtum'),
(51, 7, '2000-06-20', '2004-07-20 11:05:12', '22:24:24', 'dtumxwc'),
(52, 5, '2006-03-28', '2008-08-15 08:28:18', '04:22:26', 'tumxwc'),
(53, 1, '2004-03-05', '1900-01-01 00:00:00', '00:00:00', 'umxwcf'),
(54, 0, '2009-05-10', '2004-01-28 15:16:19', '11:46:32', 'mxwcft'),
(55, 67, '2004-04-18', '2001-06-23 00:00:00', '20:12:09', 'xwcfted'),
(56, 204, '2008-01-10', '2009-02-12 07:59:52', '13:58:17', 'wc'),
(57, 9, '2000-07-12', '2004-12-10 07:32:31', '04:04:48', 'ftedx'),
(58, 5, '2001-06-16', '2006-09-06 12:15:44', '10:14:16', 't'),
(59, 6, '2000-02-20', '2003-09-13 14:23:06', '21:22:20', 'dx'),
(60, 6, '2001-02-07', '2004-01-18 00:00:00', '10:15:21', 'xqyciak'),
(61, 1, '2008-12-24', '2004-04-02 07:16:01', '16:30:10', 'qy'),
(62, 1, '2009-12-14', '2000-01-04 14:51:24', '03:57:54', 'y'),
(63, 5, '2008-03-07', '2001-06-24 00:00:00', '06:41:05', 'ciak'),
(64, 4, '2005-01-19', '2001-06-02 03:41:12', '00:00:00', 'iakh'),
(65, 4, '2003-02-10', '1900-01-01 00:00:00', '08:51:25', 'ak'),
(66, 9, '2005-12-25', '2007-07-13 14:26:05', '14:32:55', 'hxptz'),
(67, 4, '2003-10-13', '2008-03-20 21:14:50', '00:21:31', 'xptzfp'),
(68, 3, '2001-08-03', '1900-01-01 00:00:00', '00:00:00', 'ptzfpjw'),
(69, 0, '2006-04-01', '1900-01-01 00:00:00', '11:26:05', 'tzfpjwr'),
(70, 2, '2003-12-27', '2002-05-09 18:39:28', '05:28:11', 'wrgeo'),
(71, 100, '2001-10-25', '2006-01-13 00:00:00', '04:35:51', 'r'),
(72, 37, '2006-09-12', '2003-12-04 05:20:00', '06:10:43', 'geo'),
(73, 5, '2003-06-04', '2003-07-21 11:43:03', '17:26:47', 'eozxnby'),
(74, 6, '2009-11-13', '2006-12-24 00:00:00', '22:34:54', 'oz'),
(75, 1, '2006-08-13', '2005-08-25 00:00:00', '21:27:38', 'zxnbyc'),
(76, 7, '2007-07-09', '2003-10-16 01:16:30', '03:14:14', 'xnbycjz'),
(77, 6, '2000-01-07', '2001-06-22 00:00:00', '00:00:00', 'nby'),
(78, 5, '2004-12-21', '2004-09-01 18:53:04', '16:06:30', 'bycj'),
(79, 0, '2003-10-14', '2000-04-13 05:21:03', '19:04:51', 'ycjzxie');
SELECT outr1.col_varchar AS f1, outr2.col_varchar AS f2
FROM `C` AS outr1 LEFT JOIN `E` AS outr2
ON (outr1.col_int_key = outr2.col_int_key)
WHERE outr1.col_varchar_key !=
(SELECT LEAD(innr1.pk, 5) OVER () AS y
FROM `DD` AS innr2 LEFT OUTER JOIN `DD` AS innr1
ON (innr2.col_date <> innr1.col_date_key)
WHERE innr1.col_int < 5 XOR outr2.col_date IS NULL
LIMIT 1);
f1	f2
a	ftedx
a	gktbkjr
a	gx
a	hxptz
a	k
a	surqdhu
a	uc
bj	tyz
bj	zsvasu
csz	huoo
csz	n
csz	qy
csz	umxwcf
csz	xdo
csz	y
csz	zxnbyc
dksjij	bycj
dksjij	ciak
dksjij	eozxnby
dksjij	hucjx
dksjij	ityzg
dksjij	lzsvasu
dksjij	t
dksjij	tumxwc
efqsd	knbtoe
efqsd	ktbkjrk
efqsd	mq
efqsd	mxwcft
efqsd	qlzsva
efqsd	qmwcid
efqsd	tzfpjwr
efqsd	v
efqsd	vgqmw
efqsd	ycjzxie
fqsdk	NULL
i	tyz
i	zsvasu
j	NULL
jcszxw	NULL
jjvvk	dhu
jjvvk	dx
jjvvk	nby
jjvvk	oz
jjvvk	svas
jjvvk	xqyciak
qsdksji	tyz
qsdksji	zsvasu
sdks	tyz
sdks	zsvasu
sjijcsz	chy
sjijcsz	cidtum
sjijcsz	dtumxwc
sjijcsz	mkn
sjijcsz	oo
sjijcsz	uoowit
sjijcsz	xnbycjz
sjijcsz	yhuoo
szxwbjj	ak
szxwbjj	iakh
szxwbjj	kmqm
szxwbjj	mwc
szxwbjj	nchyhu
szxwbjj	oervq
szxwbjj	rvqlzs
szxwbjj	tbkjrkm
szxwbjj	toe
szxwbjj	urqdh
szxwbjj	xptzfp
tef	ak
tef	iakh
tef	kmqm
tef	mwc
tef	nchyhu
tef	oervq
tef	rvqlzs
tef	tbkjrkm
tef	toe
tef	urqdh
tef	xptzfp
wbjjvvk	chy
wbjjvvk	cidtum
wbjjvvk	dtumxwc
wbjjvvk	mkn
wbjjvvk	oo
wbjjvvk	uoowit
wbjjvvk	xnbycjz
wbjjvvk	yhuoo
xwb	tyz
xwb	zsvasu
DROP TABLE `C`, `E`, `DD`;
SET SQL_MODE=DEFAULT;
SET SESSION big_tables=(SELECT CAST(@sav_big AS SIGNED));
SET SQL_MODE=default;