# ----------------------------------------------------------------------
# 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;
SET internal_tmp_mem_storage_engine=MEMORY;
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;
INSERT INTO td(d) SELECT d+10000 FROM td UNION
SELECT d+20000 FROM td UNION
SELECT d+30000 FROM td UNION
SELECT d+40000 FROM td UNION
SELECT d+50000 FROM td UNION
SELECT d+60000 FROM td UNION
SELECT d+70000 FROM td UNION
SELECT d+80000 FROM td UNION
SELECT d+90000 FROM td;
INSERT INTO td(d) SELECT d+100000 FROM td UNION
SELECT d+200000 FROM td UNION
SELECT d+300000 FROM td UNION
SELECT d+400000 FROM td UNION
SELECT d+500000 FROM td UNION
SELECT d+600000 FROM td UNION
SELECT d+700000 FROM td UNION
SELECT d+800000 FROM td UNION
SELECT d+900000 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
1000000	10999945	999992	999980
999999	11999934	999991	999979
999998	12999922	999990	999978
999997	13999909	999989	999977
999996	14999895	999988	999976
999995	15999880	999987	999975
999994	16999864	999986	999974
999993	17999847	999985	999973
999992	18999829	999984	999972
999991	19999810	999983	999971
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
1000000	10999945	999992	999980
999999	11999934	999991	999979
999998	12999922	999990	999978
999997	13999909	999989	999977
999996	14999895	999988	999976
999995	15999880	999987	999975
999994	16999864	999986	999974
999993	17999847	999985	999973
999992	18999829	999984	999972
999991	19999810	999983	999971
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;
SET internal_tmp_mem_storage_engine=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;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
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');
Warnings:
Warning	1048	Column 'col_date_gckey' cannot be null
Warning	1048	Column 'col_date_key' cannot be null
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;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
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');
Warnings:
Warning	1048	Column 'col_date_gckey' cannot be null
Warning	1048	Column 'col_date_key' cannot be null
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;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
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');
Warnings:
Warning	1048	Column 'col_date_gckey' cannot be null
Warning	1048	Column 'col_date_key' cannot be null
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
tef	ak
tef	iakh
tef	kmqm
tef	mwc
tef	nchyhu
tef	oervq
tef	rvqlzs
tef	tbkjrkm
tef	toe
tef	urqdh
tef	xptzfp
efqsd	knbtoe
efqsd	ktbkjrk
efqsd	mq
efqsd	mxwcft
efqsd	qlzsva
efqsd	qmwcid
efqsd	tzfpjwr
efqsd	vgqmw
efqsd	v
efqsd	ycjzxie
fqsdk	NULL
qsdksji	tyz
qsdksji	zsvasu
sdks	tyz
sdks	zsvasu
dksjij	bycj
dksjij	ciak
dksjij	eozxnby
dksjij	hucjx
dksjij	ityzg
dksjij	lzsvasu
dksjij	t
dksjij	tumxwc
sjijcsz	chy
sjijcsz	cidtum
sjijcsz	dtumxwc
sjijcsz	mkn
sjijcsz	oo
sjijcsz	uoowit
sjijcsz	xnbycjz
sjijcsz	yhuoo
i	tyz
i	zsvasu
jcszxw	NULL
csz	huoo
csz	n
csz	qy
csz	umxwcf
csz	xdo
csz	y
csz	zxnbyc
szxwbjj	ak
szxwbjj	iakh
szxwbjj	kmqm
szxwbjj	mwc
szxwbjj	nchyhu
szxwbjj	oervq
szxwbjj	rvqlzs
szxwbjj	tbkjrkm
szxwbjj	toe
szxwbjj	urqdh
szxwbjj	xptzfp
xwb	tyz
xwb	zsvasu
wbjjvvk	chy
wbjjvvk	cidtum
wbjjvvk	dtumxwc
wbjjvvk	mkn
wbjjvvk	oo
wbjjvvk	uoowit
wbjjvvk	xnbycjz
wbjjvvk	yhuoo
bj	tyz
bj	zsvasu
jjvvk	dhu
jjvvk	dx
jjvvk	nby
jjvvk	oz
jjvvk	svas
jjvvk	xqyciak
j	NULL
Warnings:
Warning	1292	Truncated incorrect DOUBLE value: 'aax'
Warning	1292	Truncated incorrect DOUBLE value: 'aax'
Warning	1292	Truncated incorrect DOUBLE value: 'aax'
Warning	1292	Truncated incorrect DOUBLE value: 'aax'
Warning	1292	Truncated incorrect DOUBLE value: 'aax'
Warning	1292	Truncated incorrect DOUBLE value: 'aax'
Warning	1292	Truncated incorrect DOUBLE value: 'aax'
Warning	1292	Truncated incorrect DOUBLE value: 'teftefx'
Warning	1292	Truncated incorrect DOUBLE value: 'teftefx'
Warning	1292	Truncated incorrect DOUBLE value: 'teftefx'
Warning	1292	Truncated incorrect DOUBLE value: 'teftefx'
Warning	1292	Truncated incorrect DOUBLE value: 'teftefx'
Warning	1292	Truncated incorrect DOUBLE value: 'teftefx'
Warning	1292	Truncated incorrect DOUBLE value: 'teftefx'
Warning	1292	Truncated incorrect DOUBLE value: 'teftefx'
Warning	1292	Truncated incorrect DOUBLE value: 'teftefx'
Warning	1292	Truncated incorrect DOUBLE value: 'teftefx'
Warning	1292	Truncated incorrect DOUBLE value: 'teftefx'
Warning	1292	Truncated incorrect DOUBLE value: 'efqsdefqsdx'
Warning	1292	Truncated incorrect DOUBLE value: 'efqsdefqsdx'
Warning	1292	Truncated incorrect DOUBLE value: 'efqsdefqsdx'
Warning	1292	Truncated incorrect DOUBLE value: 'efqsdefqsdx'
Warning	1292	Truncated incorrect DOUBLE value: 'efqsdefqsdx'
Warning	1292	Truncated incorrect DOUBLE value: 'efqsdefqsdx'
Warning	1292	Truncated incorrect DOUBLE value: 'efqsdefqsdx'
Warning	1292	Truncated incorrect DOUBLE value: 'efqsdefqsdx'
Warning	1292	Truncated incorrect DOUBLE value: 'efqsdefqsdx'
Warning	1292	Truncated incorrect DOUBLE value: 'efqsdefqsdx'
Warning	1292	Truncated incorrect DOUBLE value: 'fqsdkfqsdkx'
Warning	1292	Truncated incorrect DOUBLE value: 'qsdksjiqsdksjix'
Warning	1292	Truncated incorrect DOUBLE value: 'qsdksjiqsdksjix'
Warning	1292	Truncated incorrect DOUBLE value: 'sdkssdksx'
Warning	1292	Truncated incorrect DOUBLE value: 'sdkssdksx'
Warning	1292	Truncated incorrect DOUBLE value: 'dksjijdksjijx'
Warning	1292	Truncated incorrect DOUBLE value: 'dksjijdksjijx'
Warning	1292	Truncated incorrect DOUBLE value: 'dksjijdksjijx'
Warning	1292	Truncated incorrect DOUBLE value: 'dksjijdksjijx'
Warning	1292	Truncated incorrect DOUBLE value: 'dksjijdksjijx'
Warning	1292	Truncated incorrect DOUBLE value: 'dksjijdksjijx'
Warning	1292	Truncated incorrect DOUBLE value: 'dksjijdksjijx'
Warning	1292	Truncated incorrect DOUBLE value: 'dksjijdksjijx'
Warning	1292	Truncated incorrect DOUBLE value: 'sjijcszsjijcszx'
Warning	1292	Truncated incorrect DOUBLE value: 'sjijcszsjijcszx'
Warning	1292	Truncated incorrect DOUBLE value: 'sjijcszsjijcszx'
Warning	1292	Truncated incorrect DOUBLE value: 'sjijcszsjijcszx'
Warning	1292	Truncated incorrect DOUBLE value: 'sjijcszsjijcszx'
Warning	1292	Truncated incorrect DOUBLE value: 'sjijcszsjijcszx'
Warning	1292	Truncated incorrect DOUBLE value: 'sjijcszsjijcszx'
Warning	1292	Truncated incorrect DOUBLE value: 'sjijcszsjijcszx'
Warning	1292	Truncated incorrect DOUBLE value: 'iix'
Warning	1292	Truncated incorrect DOUBLE value: 'iix'
Warning	1292	Truncated incorrect DOUBLE value: 'jcszxwjcszxwx'
Warning	1292	Truncated incorrect DOUBLE value: 'cszcszx'
Warning	1292	Truncated incorrect DOUBLE value: 'cszcszx'
Warning	1292	Truncated incorrect DOUBLE value: 'cszcszx'
Warning	1292	Truncated incorrect DOUBLE value: 'cszcszx'
Warning	1292	Truncated incorrect DOUBLE value: 'cszcszx'
Warning	1292	Truncated incorrect DOUBLE value: 'cszcszx'
Warning	1292	Truncated incorrect DOUBLE value: 'cszcszx'
Warning	1292	Truncated incorrect DOUBLE value: 'szxwbjjszxwbjjx'
Warning	1292	Truncated incorrect DOUBLE value: 'szxwbjjszxwbjjx'
Warning	1292	Truncated incorrect DOUBLE value: 'szxwbjjszxwbjjx'
Warning	1292	Truncated incorrect DOUBLE value: 'szxwbjjszxwbjjx'
Warning	1292	Truncated incorrect DOUBLE value: 'szxwbjjszxwbjjx'
Warning	1292	Truncated incorrect DOUBLE value: 'szxwbjjszxwbjjx'
Warning	1292	Truncated incorrect DOUBLE value: 'szxwbjjszxwbjjx'
Warning	1292	Truncated incorrect DOUBLE value: 'szxwbjjszxwbjjx'
Warning	1292	Truncated incorrect DOUBLE value: 'szxwbjjszxwbjjx'
Warning	1292	Truncated incorrect DOUBLE value: 'szxwbjjszxwbjjx'
Warning	1292	Truncated incorrect DOUBLE value: 'szxwbjjszxwbjjx'
Warning	1292	Truncated incorrect DOUBLE value: 'xwbxwbx'
Warning	1292	Truncated incorrect DOUBLE value: 'xwbxwbx'
Warning	1292	Truncated incorrect DOUBLE value: 'wbjjvvkwbjjvvkx'
Warning	1292	Truncated incorrect DOUBLE value: 'wbjjvvkwbjjvvkx'
Warning	1292	Truncated incorrect DOUBLE value: 'wbjjvvkwbjjvvkx'
Warning	1292	Truncated incorrect DOUBLE value: 'wbjjvvkwbjjvvkx'
Warning	1292	Truncated incorrect DOUBLE value: 'wbjjvvkwbjjvvkx'
Warning	1292	Truncated incorrect DOUBLE value: 'wbjjvvkwbjjvvkx'
Warning	1292	Truncated incorrect DOUBLE value: 'wbjjvvkwbjjvvkx'
Warning	1292	Truncated incorrect DOUBLE value: 'wbjjvvkwbjjvvkx'
Warning	1292	Truncated incorrect DOUBLE value: 'bjbjx'
Warning	1292	Truncated incorrect DOUBLE value: 'bjbjx'
Warning	1292	Truncated incorrect DOUBLE value: 'jjvvkjjvvkx'
Warning	1292	Truncated incorrect DOUBLE value: 'jjvvkjjvvkx'
Warning	1292	Truncated incorrect DOUBLE value: 'jjvvkjjvvkx'
Warning	1292	Truncated incorrect DOUBLE value: 'jjvvkjjvvkx'
Warning	1292	Truncated incorrect DOUBLE value: 'jjvvkjjvvkx'
Warning	1292	Truncated incorrect DOUBLE value: 'jjvvkjjvvkx'
Warning	1292	Truncated incorrect DOUBLE value: 'jjx'
DROP TABLE `C`, `E`, `DD`;
SET SQL_MODE=DEFAULT;
SET SESSION big_tables=(SELECT CAST(@sav_big AS SIGNED));