CREATE DATABASE temptable_test;
USE temptable_test;
# Test 01
SELECT * FROM information_schema.table_constraints
WHERE table_schema = 'mysql' AND table_name != 'ndb_binlog_index'
ORDER BY table_schema,table_name,constraint_name COLLATE utf8_general_ci;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE	ENFORCED
def	mysql	PRIMARY	mysql	columns_priv	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	component	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	db	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	default_roles	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	engine_cost	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	func	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	global_grants	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	gtid_executed	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	help_category	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	help_keyword	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	help_relation	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	help_topic	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	innodb_index_stats	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	innodb_table_stats	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	password_history	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	plugin	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	procs_priv	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	proxies_priv	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	role_edges	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	server_cost	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	servers	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	slave_master_info	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	slave_relay_log_info	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	slave_worker_info	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	tables_priv	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	time_zone	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	time_zone_leap_second	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	time_zone_name	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	time_zone_transition	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	time_zone_transition_type	PRIMARY KEY	YES
def	mysql	PRIMARY	mysql	user	PRIMARY KEY	YES
def	mysql	name	mysql	help_category	UNIQUE	YES
def	mysql	name	mysql	help_keyword	UNIQUE	YES
def	mysql	name	mysql	help_topic	UNIQUE	YES
# Test 03
SET optimizer_switch = 'derived_merge=off';
SELECT DISTINCT
alias1.`col_int` AS field1,
alias1.`pk` AS field2,
alias1.`col_int` AS field3,
alias1.`col_int_key` AS field4,
alias1.`col_int_key` AS field5
FROM
view_K AS alias1
LEFT JOIN
view_HH AS alias2
ON
alias1.`col_varchar_255_latin1` = alias2.`col_varchar_255_utf8_key`
WHERE alias1.`col_int` IS NULL
ORDER BY field1;
field1	field2	field3	field4	field5
NULL	1	NULL	5	5
NULL	13	NULL	NULL	NULL
NULL	15	NULL	NULL	NULL
NULL	16	NULL	536608768	536608768
NULL	2	NULL	315490304	315490304
NULL	7	NULL	NULL	NULL
SET optimizer_switch = default;
# Test 04
SELECT
GRANDPARENT1.`pk` AS g1,
GRANDPARENT1.`col_datetime_key`
FROM
CC AS GRANDPARENT1
LEFT JOIN
CC AS GRANDPARENT2
USING (`col_int_key`)
WHERE
GRANDPARENT1.`col_int_key` IN (
SELECT PARENT1.`col_int_key` AS p1 FROM CC AS PARENT1
) AND GRANDPARENT1.`pk` <> 2
HAVING g1 <> 'p'
ORDER BY GRANDPARENT1.`col_datetime_key`;
g1	col_datetime_key
10	2005-07-20 00:00:00
10	2005-07-20 00:00:00
10	2005-07-20 00:00:00
11	2007-10-06 17:56:40
11	2007-10-06 17:56:40
12	NULL
12	NULL
13	2007-12-23 05:17:49
13	2007-12-23 05:17:49
14	NULL
14	NULL
14	NULL
16	NULL
16	NULL
17	2003-02-07 09:50:39
17	2003-02-07 09:50:39
17	2003-02-07 09:50:39
18	2003-08-04 23:00:25
18	2003-08-04 23:00:25
19	2009-08-07 12:57:18
19	2009-08-07 12:57:18
19	2009-08-07 12:57:18
20	1900-01-01 00:00:00
20	1900-01-01 00:00:00
21	2001-06-17 01:34:03
21	2001-06-17 01:34:03
22	NULL
22	NULL
23	1900-01-01 00:00:00
23	1900-01-01 00:00:00
24	NULL
24	NULL
24	NULL
27	2008-07-18 00:00:00
27	2008-07-18 00:00:00
29	2003-06-13 23:19:49
29	2003-06-13 23:19:49
29	2003-06-13 23:19:49
Warning	1292	Truncated incorrect DOUBLE value: 'p'
Warnings:
# Test 05
SELECT
GRANDPARENT1.`col_int_key` AS g1,
GRANDPARENT1.`col_datetime_key` AS dt
FROM
C AS GRANDPARENT1
LEFT JOIN
C AS GRANDPARENT2
ON (GRANDPARENT2.`pk` <> GRANDPARENT1.`pk`)
WHERE
(GRANDPARENT1.`pk`, GRANDPARENT1.`pk`) IN (
SELECT DISTINCT
PARENT1.`col_int_key` AS p1,
PARENT1.`col_int_key` AS p2
FROM
C AS PARENT1
LEFT JOIN
C AS PARENT2
USING (`col_varchar_key`)
WHERE
((PARENT1.`pk` > GRANDPARENT1.`col_int_key`)
OR ((PARENT1.`col_time_key` <= GRANDPARENT1.`col_time_key`)
AND (PARENT1.`col_datetime_key` > '2005-02-01')
)
)
ORDER BY PARENT1.`col_int_key`
  )
AND GRANDPARENT1.`col_varchar_key` <> 'r'
HAVING g1 <> '13:16:53.053569'
ORDER BY GRANDPARENT1.`col_datetime_key`;
g1	dt
0	2001-11-08 21:02:12
0	2001-11-08 21:02:12
0	2001-11-08 21:02:12
0	2001-11-08 21:02:12
0	2001-11-08 21:02:12
0	2001-11-08 21:02:12
0	2001-11-08 21:02:12
0	2001-11-08 21:02:12
0	2001-11-08 21:02:12
0	2001-11-08 21:02:12
0	2001-11-08 21:02:12
0	2001-11-08 21:02:12
0	2001-11-08 21:02:12
0	2001-11-08 21:02:12
0	2001-11-08 21:02:12
0	2001-11-08 21:02:12
0	2001-11-08 21:02:12
0	2001-11-08 21:02:12
0	2001-11-08 21:02:12
1	2007-07-12 00:00:00
1	2007-07-12 00:00:00
1	2007-07-12 00:00:00
1	2007-07-12 00:00:00
1	2007-07-12 00:00:00
1	2007-07-12 00:00:00
1	2007-07-12 00:00:00
1	2007-07-12 00:00:00
1	2007-07-12 00:00:00
1	2007-07-12 00:00:00
1	2007-07-12 00:00:00
1	2007-07-12 00:00:00
1	2007-07-12 00:00:00
1	2007-07-12 00:00:00
1	2007-07-12 00:00:00
1	2007-07-12 00:00:00
1	2007-07-12 00:00:00
1	2007-07-12 00:00:00
1	2007-07-12 00:00:00
4	1900-01-01 00:00:00
4	1900-01-01 00:00:00
4	1900-01-01 00:00:00
4	1900-01-01 00:00:00
4	1900-01-01 00:00:00
4	1900-01-01 00:00:00
4	1900-01-01 00:00:00
4	1900-01-01 00:00:00
4	1900-01-01 00:00:00
4	1900-01-01 00:00:00
4	1900-01-01 00:00:00
4	1900-01-01 00:00:00
4	1900-01-01 00:00:00
4	1900-01-01 00:00:00
4	1900-01-01 00:00:00
4	1900-01-01 00:00:00
4	1900-01-01 00:00:00
4	1900-01-01 00:00:00
4	1900-01-01 00:00:00
62	2008-01-03 10:33:32
62	2008-01-03 10:33:32
62	2008-01-03 10:33:32
62	2008-01-03 10:33:32
62	2008-01-03 10:33:32
62	2008-01-03 10:33:32
62	2008-01-03 10:33:32
62	2008-01-03 10:33:32
62	2008-01-03 10:33:32
62	2008-01-03 10:33:32
62	2008-01-03 10:33:32
62	2008-01-03 10:33:32
62	2008-01-03 10:33:32
62	2008-01-03 10:33:32
62	2008-01-03 10:33:32
62	2008-01-03 10:33:32
62	2008-01-03 10:33:32
62	2008-01-03 10:33:32
62	2008-01-03 10:33:32
7	1900-01-01 00:00:00
7	1900-01-01 00:00:00
7	1900-01-01 00:00:00
7	1900-01-01 00:00:00
7	1900-01-01 00:00:00
7	1900-01-01 00:00:00
7	1900-01-01 00:00:00
7	1900-01-01 00:00:00
7	1900-01-01 00:00:00
7	1900-01-01 00:00:00
7	1900-01-01 00:00:00
7	1900-01-01 00:00:00
7	1900-01-01 00:00:00
7	1900-01-01 00:00:00
7	1900-01-01 00:00:00
7	1900-01-01 00:00:00
7	1900-01-01 00:00:00
7	1900-01-01 00:00:00
7	1900-01-01 00:00:00
7	2005-04-04 01:21:01
7	2005-04-04 01:21:01
7	2005-04-04 01:21:01
7	2005-04-04 01:21:01
7	2005-04-04 01:21:01
7	2005-04-04 01:21:01
7	2005-04-04 01:21:01
7	2005-04-04 01:21:01
7	2005-04-04 01:21:01
7	2005-04-04 01:21:01
7	2005-04-04 01:21:01
7	2005-04-04 01:21:01
7	2005-04-04 01:21:01
7	2005-04-04 01:21:01
7	2005-04-04 01:21:01
7	2005-04-04 01:21:01
7	2005-04-04 01:21:01
7	2005-04-04 01:21:01
7	2005-04-04 01:21:01
Warning	1292	Truncated incorrect DOUBLE value: '13:16:53.053569'
Warnings:
# Test 06
(SELECT DISTINCT
*
FROM
`view_table10000_innodb_int_autoinc`
WHERE
(`col_varchar_10_key` LIKE CONCAT('Michigan', '%')
OR `col_varchar_64_key` LIKE CONCAT('why', '%'))
AND (`col_varchar_64_key` IS NOT NULL
OR NOT (`col_varchar_64_key` = 'can\'t'))
        OR (`col_smallint_key` IN (1 , 244, 1, 1)
        OR `col_bigint_key` IS NOT NULL)
        AND (`col_bigint_key` IN (1 , - 89)
        OR (`col_bigint_key` != 1))
        AND (`col_varchar_10_key` IS NOT NULL
        AND `col_varchar_10_key` NOT IN ('Maine' , 'x'))
        AND (NOT (`col_bigint_key` = 1)
        AND `col_smallint_key` BETWEEN 1 AND 1 + 125)) UNION DISTINCT (SELECT
DISTINCT
    *
FROM
    `view_table10000_innodb_int_autoinc`
WHERE
    (`col_varchar_10_key` LIKE CONCAT('Michigan', '%')
        OR `col_varchar_64_key` LIKE CONCAT('why', '%'))
        AND (`col_varchar_64_key` IS NOT NULL
        OR NOT (`col_varchar_64_key` = 'can\'t'))
OR (`col_smallint_key` IN (1 , 244, 1, 1)
OR `col_bigint_key` IS NOT NULL)
AND (`col_bigint_key` IN (1 , - 89)
OR (`col_bigint_key` != 1))
AND (`col_varchar_10_key` IS NOT NULL
AND `col_varchar_10_key` NOT IN ('Maine' , 'x'))
AND (NOT (`col_bigint_key` = 1)
AND `col_smallint_key` BETWEEN 1 AND 1 + 125));
col_smallint	col_varchar_10	col_bigint	pk	col_smallint_key	col_varchar_64_key	col_varchar_10_key	col_varchar_64	col_bigint_key
0	Wyoming	-5797540095309381632	6	32	u	a	New York	30584
10485	New Hampsh	NULL	18	4	m	y	j	103
22872	toqiyowask	0	2	54	you	oqiyowaskl	really	2739595948324814848
32767	g	6	8	111	hsfysdlfwvzywlrokuydyjybesvqsaxfbmnaiejcfhlazotdcasrmxrulycvtifo	l	s	0
80	qi	-6770880564774830080	3	6	like	Alaska	iyowasklhsfysdlfwvzywlrokuydyjybesvqsaxfbmnaiejcfhl	40655
# Test 07
SET optimizer_switch = 'derived_merge=off';
SELECT
alias2.`col_int_key`, alias2.pk, alias2.`col_varchar_10_latin1_key`
FROM
MM AS alias1
LEFT OUTER JOIN
view_PP AS alias2
ON alias1.`col_varchar_10_latin1` = alias2.`col_varchar_10_latin1_key`
WHERE alias2.`col_int` NOT IN (1);
col_int_key	pk	col_varchar_10_latin1_key
1052704768	28	i
5	29	c
727187456	7	i
8	36	I
NULL	10	i
NULL	23	i
NULL	57	i
NULL	76	had
NULL	94	z
NULL	94	z
SET optimizer_switch = default;
# Test 08
SET optimizer_switch = 'derived_merge=on';
SELECT
alias2.`col_int_key`
FROM
MM AS alias1
LEFT OUTER JOIN
view_PP AS alias2
ON alias1.`col_varchar_10_latin1` = alias2.`col_varchar_10_latin1_key`
WHERE alias2.`col_int` NOT IN (1);
col_int_key
1052704768
5
727187456
8
NULL
NULL
NULL
NULL
NULL
NULL
SET optimizer_switch = default;
# Test 09
SET optimizer_switch = 'derived_merge=off';
SELECT table1.pk
FROM view_D AS table1
LEFT JOIN D AS table2 ON table1.col_int_key = table2.col_int_key
WHERE table1.col_int_key IS NULL;
pk
10
19
27
37
43
52
53
56
82
84
85
86
9
# Test 10
SET optimizer_switch = 'derived_merge=on';
SELECT table1.pk
FROM view_D AS table1
LEFT JOIN D AS table2 ON table1.col_int_key = table2.col_int_key
WHERE table1.col_int_key IS NULL;
pk
10
19
27
37
43
52
53
56
82
84
85
86
9
# Test 11
CREATE TABLE t1 (
pk int(11) NOT NULL DEFAULT '0',
col_int_key int(11) DEFAULT NULL,
col_varchar_key varchar(1) DEFAULT NULL
) ENGINE=InnoDB 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.
INSERT INTO t1 VALUES
(22,0,NULL),
(17,9,NULL),
(29,8,'c'),
(23,4,'d'),
(11,7,'d'),
(26,NULL,'f'),
(13,7,'f'),
(24,8,'g'),
(28,NULL,'j'),
(16,1,'m'),
(20,2,'m'),
(18,2,'o'),
(27,0,'p'),
(21,4,'q'),
(12,1,'r'),
(15,NULL,'u'),
(19,9,'w'),
(25,NULL,'x'),
(10,8,'x'),
(14,9,'y');
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
temptable_test.t1	analyze	status	OK
SELECT *
FROM (
SELECT DISTINCT SUBQUERY1_t1.*
FROM (
t1 AS SUBQUERY1_t1
LEFT OUTER JOIN
t1 AS SUBQUERY1_t2
ON (SUBQUERY1_t2.`pk` = SUBQUERY1_t1.`col_int_key`)
)
) AS table1
WHERE table1.`col_varchar_key` IS NULL;
pk	col_int_key	col_varchar_key
17	9	NULL
22	0	NULL
DROP TABLE t1;
# Test 12
CREATE TABLE t1 (
id INT NOT NULL AUTO_INCREMENT,
c1 CHAR(60) NOT NULL,
c2 CHAR(60),
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO t1 (c1, c2) VALUES
('abcdefghij', 'ABCDEFGHIJ'),
('mnopqrstuv', 'MNOPQRSTUV');
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
temptable_test.t1	analyze	status	OK
SELECT DISTINCT c1, c2 FROM t1 WHERE id BETWEEN 1 And 2 ORDER BY 1;
c1	c2
abcdefghij	ABCDEFGHIJ
mnopqrstuv	MNOPQRSTUV
DROP TABLE t1;
SET SESSION internal_tmp_mem_storage_engine = default;
USE test;
DROP DATABASE temptable_test;