Adding upstream version 1:10.11.6.upstream/1%10.11.6

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 418 insertions, 0 deletions

diff --git a/mysql-test/main/subselect2.result b/mysql-test/main/subselect2.result
new file mode 100644
index 00000000..db6c8590
--- /dev/null
+++ b/mysql-test/main/subselect2.result
@@ -0,0 +1,418 @@
+drop table if exists t1, t2, t3, t4;
+set @subselect2_test_tmp=@@optimizer_switch;
+set optimizer_switch='semijoin=on,firstmatch=on,mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
+CREATE TABLE t1
+(
+DOCID VARCHAR(32)BINARY NOT NULL
+, UUID VARCHAR(32)BINARY NOT NULL
+, MIMETYPE VARCHAR(80)BINARY
+, CONTENTDATA LONGBLOB
+, CONTENTSIZE INTEGER
+, VERSIONID INTEGER
+, REPID VARCHAR(32)BINARY
+, MODIFIED TIMESTAMP
+, MODIFIER VARCHAR(255)BINARY
+, ORIGINATOR INTEGER
+, PRIMARY KEY ( DOCID )
+) ENGINE=InnoDB
+;
+INSERT IGNORE INTO t1 (DOCID) VALUES ("1"), ("2");
+Warnings:
+Warning	1364	Field 'UUID' doesn't have a default value
+CREATE TABLE t2
+(
+DOCID VARCHAR(32)BINARY NOT NULL
+, DOCNAME VARCHAR(255)BINARY NOT NULL
+, DOCTYPEID VARCHAR(32)BINARY NOT NULL
+, FOLDERID VARCHAR(32)BINARY NOT NULL
+, AUTHOR VARCHAR(255)BINARY
+, CREATED TIMESTAMP NOT NULL
+, TITLE VARCHAR(255)BINARY
+, SUBTITLE VARCHAR(255)BINARY
+, DOCABSTRACT LONGBLOB
+, PUBLISHDATE TIMESTAMP
+, EXPIRATIONDATE TIMESTAMP
+, LOCKEDBY VARCHAR(80)BINARY
+, STATUS VARCHAR(80)BINARY
+, PARENTDOCID VARCHAR(32)BINARY
+, REPID VARCHAR(32)BINARY
+, MODIFIED TIMESTAMP NOT NULL
+, MODIFIER VARCHAR(255)BINARY NOT NULL
+, PUBLISHSTATUS INTEGER
+, ORIGINATOR INTEGER
+, PRIMARY KEY ( DOCID )
+) ENGINE=InnoDB
+;
+CREATE INDEX DDOCTYPEID_IDX ON t2 (DOCTYPEID);
+CREATE INDEX DFOLDERID_IDX ON t2 (FOLDERID);
+CREATE TABLE t3
+(
+FOLDERID VARCHAR(32)BINARY NOT NULL
+, FOLDERNAME VARCHAR(255)BINARY NOT NULL
+, CREATOR VARCHAR(255)BINARY
+, CREATED TIMESTAMP NOT NULL
+, DESCRIPTION VARCHAR(255)BINARY
+, FOLDERTYPE INTEGER NOT NULL
+, MODIFIED TIMESTAMP
+, MODIFIER VARCHAR(255)BINARY
+, FOLDERSIZE INTEGER NOT NULL
+, PARENTID VARCHAR(32)BINARY
+, REPID VARCHAR(32)BINARY
+, ORIGINATOR INTEGER
+, PRIMARY KEY ( FOLDERID )
+) ENGINE=InnoDB;
+CREATE INDEX FFOLDERID_IDX ON t3 (FOLDERID);
+CREATE INDEX CMFLDRPARNT_IDX ON t3 (PARENTID);
+CREATE TABLE t4
+(
+DOCTYPEID VARCHAR(32)BINARY NOT NULL
+, DOCTYPENAME VARCHAR(80)BINARY NOT NULL
+, DESCRIPTION VARCHAR(255)BINARY
+, EXTNDATA LONGBLOB
+, MODIFIED TIMESTAMP
+, MODIFIER VARCHAR(255)BINARY
+, ORIGINATOR INTEGER
+, PRIMARY KEY ( DOCTYPEID )
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES("c373e9f59cf15a6c3e57444553544200", "c373e9f59cf15a6c3e57444553544200", "340d243d45f111d497b00010a4ef934d", "2f6161e879db43c1a5b82c21ddc49089", NULL, "2003-06-06 07:48:42", NULL, NULL, NULL, "2003-06-06 07:48:42", "2003-06-06 07:48:42", NULL, NULL, NULL, "03eea05112b845949f3fd03278b5fe43", "2003-06-06 07:48:42", "admin", "0", NULL);
+INSERT INTO t2 VALUES("c373e9f5a472f43ba45e444553544200", "c373e9f5a472f43ba45e444553544200", "340d243d45f111d497b00010a4ef934d", "2f6161e879db43c1a5b82c21ddc49089", NULL, "2003-06-07 18:50:12", NULL, NULL, NULL, "2003-06-07 18:50:12", "2003-06-07 18:50:12", NULL, NULL, NULL, "03eea05112b845949f3fd03278b5fe43", "2003-06-07 18:50:12", "admin", "0", NULL);
+INSERT INTO t2 VALUES("c373e9f5a4a0f56014eb444553544200", "c373e9f5a4a0f56014eb444553544200", "340d243d45f111d497b00010a4ef934d", "2f6161e879db43c1a5b82c21ddc49089", NULL, "2003-06-07 19:39:26", NULL, NULL, NULL, "2003-06-07 19:39:26", "2003-06-07 19:39:26", NULL, NULL, NULL, "03eea05112b845949f3fd03278b5fe43", "2003-06-07 19:39:26", "admin", "0", NULL);
+INSERT INTO t2 VALUES("c373e9f5a4a0f8fa4a86444553544200", "c373e9f5a4a0f8fa4a86444553544200", "340d243d45f111d497b00010a4ef934d", "2f6161e879db43c1a5b82c21ddc49089", NULL, "2003-06-07 19:43:05", NULL, NULL, NULL, "2003-06-07 19:43:05", "2003-06-07 19:43:05", NULL, NULL, NULL, "03eea05112b845949f3fd03278b5fe43", "2003-06-07 19:43:05", "admin", "0", NULL);
+INSERT INTO t2 VALUES("c373e9f5ac7b537205ce444553544200", "c373e9f5ac7b537205ce444553544200", "340d243d45f111d497b00010a4ef934d", "2f6161e879db43c1a5b82c21ddc49089", NULL, "2003-06-09 08:15:24", NULL, NULL, NULL, "2003-06-09 08:15:24", "2003-06-09 08:15:24", NULL, NULL, NULL, "03eea05112b845949f3fd03278b5fe43", "2003-06-09 08:15:24", "admin", "0", NULL);
+INSERT INTO t2 VALUES("c373e9f5ad0792012454444553544200", "c373e9f5ad0792012454444553544200", "340d243d45f111d497b00010a4ef934d", "2f6161e879db43c1a5b82c21ddc49089", NULL, "2003-06-09 10:51:44", NULL, NULL, NULL, "2003-06-09 10:51:44", "2003-06-09 10:51:44", NULL, NULL, NULL, "03eea05112b845949f3fd03278b5fe43", "2003-06-09 10:51:44", "admin", "0", NULL);
+INSERT INTO t2 VALUES("c373e9f5ad079821ef34444553544200", "First Discussion", "c373e9f5ad079174ff17444553544200", "c373e9f5ad0796c0eca4444553544200", "Goldilocks", "2003-06-09 11:16:50", "Title: First Discussion", NULL, NULL, "2003-06-09 10:51:26", "2003-06-09 10:51:26", NULL, NULL, NULL, "03eea05112b845949f3fd03278b5fe43", "2003-06-09 11:16:50", "admin", "0", NULL);
+INSERT INTO t2 VALUES("c373e9f5ad07993f3859444553544200", "Last Discussion", "c373e9f5ad079174ff17444553544200", "c373e9f5ad0796c0eca4444553544200", "Goldilocks", "2003-06-09 11:21:06", "Title: Last Discussion", NULL, "Setting new abstract and keeping doc checked out", "2003-06-09 10:51:26", "2003-06-09 10:51:26", NULL, NULL, NULL, "03eea05112b845949f3fd03278b5fe43", "2003-06-09 11:21:06", "admin", "0", NULL);
+INSERT INTO t2 VALUES("c373e9f5ad079a3219c4444553544200", "testdoclayout", "340d243c45f111d497b00010a4ef934d", "c373e9f5ad0796c0eca4444553544200", "Goldilocks", "2003-06-09 11:25:31", "Title: Test doc layout", "Subtitle: test doc layout", NULL, "2003-06-09 10:51:27", "2003-06-09 10:51:27", NULL, NULL, NULL, "03eea05112b845949f3fd03278b5fe43", "2003-06-09 11:25:31", "admin", "0", NULL);
+INSERT INTO t3 VALUES("0c9aab05b15048c59bc35c8461507deb", "System", "System", "2003-06-05 16:30:00", "The system content repository folder.", "3", "2003-06-05 16:30:00", "System", "0", NULL, "9c9aab05b15048c59bc35c8461507deb", "1");
+INSERT INTO t3 VALUES("2f6161e879db43c1a5b82c21ddc49089", "Default", "System", "2003-06-09 10:52:02", "The default content repository folder.", "3", "2003-06-05 16:30:00", "System", "0", NULL, "03eea05112b845949f3fd03278b5fe43", "1");
+INSERT INTO t3 VALUES("c373e9f5ad0791724315444553544200", "AddDocumentTest", "admin", "2003-06-09 10:51:25", "Movie Reviews", "0", "2003-06-09 10:51:25", "admin", "0", "2f6161e879db43c1a5b82c21ddc49089", "03eea05112b845949f3fd03278b5fe43", NULL);
+INSERT INTO t3 VALUES("c373e9f5ad07919e1963444553544200", "NewDestDirectory", "admin", "2003-06-09 10:51:28", "Adding new directory", "128", "2003-06-09 10:51:28", "admin", "0", "2f6161e879db43c1a5b82c21ddc49089", "03eea05112b845949f3fd03278b5fe43", NULL);
+INSERT INTO t3 VALUES("c373e9f5ad07919fe525444553544200", "SubDestDirectory", "admin", "2003-06-09 10:51:28", "Adding new directory", "128", "2003-06-09 10:51:28", "admin", "0", "c373e9f5ad07919e1963444553544200", "03eea05112b845949f3fd03278b5fe43", NULL);
+INSERT INTO t3 VALUES("c373e9f5ad0791a0dab5444553544200", "Level1", "admin", "2003-06-09 10:51:29", NULL, "0", "2003-06-09 10:51:29", "admin", "0", "2f6161e879db43c1a5b82c21ddc49089", "03eea05112b845949f3fd03278b5fe43", NULL);
+INSERT INTO t3 VALUES("c373e9f5ad0791a14669444553544200", "Level2", "admin", "2003-06-09 10:51:29", NULL, "0", "2003-06-09 10:51:29", "admin", "0", "c373e9f5ad0791a0dab5444553544200", "03eea05112b845949f3fd03278b5fe43", NULL);
+INSERT INTO t3 VALUES("c373e9f5ad0791a23c0e444553544200", "Level3", "admin", "2003-06-09 10:51:29", NULL, "0", "2003-06-09 10:51:29", "admin", "0", "c373e9f5ad0791a14669444553544200", "03eea05112b845949f3fd03278b5fe43", NULL);
+INSERT INTO t3 VALUES("c373e9f5ad0791a6b11f444553544200", "Dir1", "admin", "2003-06-09 10:51:30", NULL, "0", "2003-06-09 10:51:30", "admin", "0", "2f6161e879db43c1a5b82c21ddc49089", "03eea05112b845949f3fd03278b5fe43", NULL);
+INSERT INTO t3 VALUES("c373e9f5ad0791a897d6444553544200", "Dir2", "admin", "2003-06-09 10:51:30", NULL, "0", "2003-06-09 10:51:30", "admin", "0", "c373e9f5ad0791a6b11f444553544200", "03eea05112b845949f3fd03278b5fe43", NULL);
+INSERT INTO t3 VALUES("c373e9f5ad0791a9a063444553544200", "NewDestDirectory", "admin", "2003-06-09 10:51:31", NULL, "0", "2003-06-09 10:51:31", "admin", "0", "c373e9f5ad0791a897d6444553544200", "03eea05112b845949f3fd03278b5fe43", NULL);
+INSERT INTO t3 VALUES("c373e9f5ad0791aa73e3444553544200", "LevelA", "admin", "2003-06-09 10:51:31", NULL, "0", "2003-06-09 10:51:31", "admin", "0", "c373e9f5ad0791a0dab5444553544200", "03eea05112b845949f3fd03278b5fe43", NULL);
+INSERT INTO t3 VALUES("c373e9f5ad0791ab034b444553544200", "LevelB", "admin", "2003-06-09 10:51:31", NULL, "0", "2003-06-09 10:51:31", "admin", "0", "c373e9f5ad0791aa73e3444553544200", "03eea05112b845949f3fd03278b5fe43", NULL);
+INSERT INTO t3 VALUES("c373e9f5ad0791ac7311444553544200", "LevelC", "admin", "2003-06-09 10:51:32", NULL, "0", "2003-06-09 10:51:32", "admin", "0", "c373e9f5ad0791ab034b444553544200", "03eea05112b845949f3fd03278b5fe43", NULL);
+INSERT INTO t3 VALUES("c373e9f5ad0791ad66cf444553544200", "test2", "admin", "2003-06-09 10:51:32", NULL, "0", "2003-06-09 10:51:32", "admin", "0", "c373e9f5ad0791724315444553544200", "03eea05112b845949f3fd03278b5fe43", NULL);
+INSERT INTO t3 VALUES("c373e9f5ad0791aebd87444553544200", "test3", "admin", "2003-06-09 10:51:33", NULL, "0", "2003-06-09 10:51:33", "admin", "0", "c373e9f5ad0791ad66cf444553544200", "03eea05112b845949f3fd03278b5fe43", NULL);
+INSERT INTO t3 VALUES("c373e9f5ad0791dbaac4444553544200", "Special Café Folder", "admin", "2003-06-09 10:51:43", "test folder names with special chars", "0", "2003-06-09 10:51:43", "admin", "0", "2f6161e879db43c1a5b82c21ddc49089", "03eea05112b845949f3fd03278b5fe43", NULL);
+INSERT INTO t3 VALUES("c373e9f5ad0796bf913f444553544200", "CopiedFolder", "admin", "2003-06-09 11:09:05", "Movie Reviews", "0", "2003-06-09 11:09:05", "admin", "0", "c373e9f5ad0791a23c0e444553544200", "03eea05112b845949f3fd03278b5fe43", NULL);
+INSERT INTO t3 VALUES("c373e9f5ad0796c0eca4444553544200", "Movie Reviews", "admin", "2003-06-09 11:09:13", "Movie Reviews", "0", "2003-06-09 11:09:13", "admin", "33", "c373e9f5ad0796bf913f444553544200", "03eea05112b845949f3fd03278b5fe43", NULL);
+INSERT INTO t3 VALUES("c373e9f5ad0796d9b895444553544200", "NewBookFolder", "admin", "2003-06-09 11:12:41", "NewBooks - folder", "0", "2003-06-09 11:12:41", "admin", "0", "c373e9f5ad0796c0eca4444553544200", "03eea05112b845949f3fd03278b5fe43", NULL);
+INSERT INTO t3 VALUES("c373e9f5ad079b4c9355444553544200", "CopiedFolder", "admin", "2003-06-09 11:26:34", "Movie Reviews", "0", "2003-06-09 11:26:34", "admin", "0", "2f6161e879db43c1a5b82c21ddc49089", "03eea05112b845949f3fd03278b5fe43", NULL);
+INSERT INTO t4 VALUES("340d243c45f111d497b00010a4ef934d", "Document Layout", "The system Document Layouts Document Type", NULL, "2003-06-05 16:30:00", "System", "1");
+INSERT INTO t4 VALUES("340d243d45f111d497b00010a4ef934d", "Default", "The default system Document Type", NULL, "2003-06-05 16:30:00", "System", "1");
+INSERT INTO t4 VALUES("4d09dd60850711d4998a204c4f4f5020", "__SystemResourceType", "The type for all the uploaded resources", NULL, "2003-06-05 16:30:00", "System", "1");
+INSERT INTO t4 VALUES("91d4d595478211d497b40010a4ef934d", "__PmcSystemDefaultType", "The type for all the default available fields", NULL, "2003-06-05 16:30:00", "System", "1");
+INSERT INTO t4 VALUES("c373e9f59cf15a59b08a444553544200", "NoFieldDocType", "plain doc type", NULL, "2003-06-06 07:48:40", "admin", NULL);
+INSERT INTO t4 VALUES("c373e9f59cf15a5c6a99444553544200", "Movie Review", "This doc type is for movie reviews", "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\r\n<props autocheckin=\"false\" autopublish=\"false\" binary=\"choice\" categories=\"none\" cleanup=\"false\" folder=\"none\"><![CDATA[Doc type for cm tests]]></props>\r\n", "2003-06-06 07:48:40", "admin", NULL);
+INSERT INTO t4 VALUES("c373e9f59cf15a6116a5444553544200", "Special DocÃu20A4u20A4u0113ééøÉu016BType", "test special chars xxxé in doc type", NULL, "2003-06-06 07:48:41", "admin", NULL);
+INSERT INTO t4 VALUES("c373e9f59cf15a695d47444553544200", "Movie", NULL, NULL, "2003-06-06 07:48:41", "admin", NULL);
+INSERT INTO t4 VALUES("c373e9f5ad079174ff17444553544200", "Discussion", NULL, NULL, "2003-06-09 10:51:25", "admin", NULL);
+INSERT INTO t4 VALUES("c373e9f5ad0791da7e2b444553544200", "Books", "list of recommended books", "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\r\n<props autocheckin=\"false\" autopublish=\"false\" binary=\"choice\" categories=\"none\" cleanup=\"false\" folder=\"none\"><![CDATA[Doc type for cm tests]]><![CDATA[Doc type for book tests]]></props>\r\n", "2003-06-09 10:51:40", "admin", NULL);
+ALTER TABLE t2 ADD  FOREIGN KEY FK_DCMNTS_DCTYPES ( DOCTYPEID)
+REFERENCES t4 (DOCTYPEID );
+ALTER TABLE t2 ADD  FOREIGN KEY FK_DCMNTS_FLDRS ( FOLDERID)
+REFERENCES t3 (FOLDERID );
+ALTER TABLE t3 ADD  FOREIGN KEY FK_FLDRS_PRNTID ( PARENTID)
+REFERENCES t3 (FOLDERID );
+SELECT t2.*, t4.DOCTYPENAME, t1.CONTENTSIZE,t1.MIMETYPE FROM t2 INNER JOIN t4 ON t2.DOCTYPEID = t4.DOCTYPEID LEFT OUTER JOIN t1 ON t2.DOCID = t1.DOCID WHERE t2.FOLDERID IN(SELECT t3_a.FOLDERID FROM t3 as t3_a WHERE t3_a.PARENTID IN(SELECT t3_b.FOLDERID FROM t3 as t3_b WHERE t3_b.PARENTID IN(SELECT t3_c.FOLDERID FROM t3 as t3_c WHERE t3_c.PARENTID IN(SELECT t3_d.FOLDERID FROM t3 as t3_d WHERE t3_d.PARENTID IN(SELECT t3_e.FOLDERID FROM t3 as t3_e WHERE t3_e.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t3_e.FOLDERNAME = 'Level1') AND t3_d.FOLDERNAME = 'Level2') AND t3_c.FOLDERNAME = 'Level3') AND t3_b.FOLDERNAME = 'CopiedFolder') AND t3_a.FOLDERNAME = 'Movie Reviews') AND t2.DOCNAME = 'Last Discussion';
+DOCID	DOCNAME	DOCTYPEID	FOLDERID	AUTHOR	CREATED	TITLE	SUBTITLE	DOCABSTRACT	PUBLISHDATE	EXPIRATIONDATE	LOCKEDBY	STATUS	PARENTDOCID	REPID	MODIFIED	MODIFIER	PUBLISHSTATUS	ORIGINATOR	DOCTYPENAME	CONTENTSIZE	MIMETYPE
+c373e9f5ad07993f3859444553544200	Last Discussion	c373e9f5ad079174ff17444553544200	c373e9f5ad0796c0eca4444553544200	Goldilocks	2003-06-09 11:21:06	Title: Last Discussion	NULL	Setting new abstract and keeping doc checked out	2003-06-09 10:51:26	2003-06-09 10:51:26	NULL	NULL	NULL	03eea05112b845949f3fd03278b5fe43	2003-06-09 11:21:06	admin	0	NULL	Discussion	NULL	NULL
+EXPLAIN SELECT t2.*, t4.DOCTYPENAME, t1.CONTENTSIZE,t1.MIMETYPE FROM t2 INNER JOIN t4 ON t2.DOCTYPEID = t4.DOCTYPEID LEFT OUTER JOIN t1 ON t2.DOCID = t1.DOCID WHERE t2.FOLDERID IN(SELECT t3_a.FOLDERID FROM t3 as t3_a WHERE t3_a.PARENTID IN(SELECT t3_b.FOLDERID FROM t3 as t3_b WHERE t3_b.PARENTID IN(SELECT t3_c.FOLDERID FROM t3 as t3_c WHERE t3_c.PARENTID IN(SELECT t3_d.FOLDERID FROM t3 as t3_d WHERE t3_d.PARENTID IN(SELECT t3_e.FOLDERID FROM t3 as t3_e WHERE t3_e.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t3_e.FOLDERNAME = 'Level1') AND t3_d.FOLDERNAME = 'Level2') AND t3_c.FOLDERNAME = 'Level3') AND t3_b.FOLDERNAME = 'CopiedFolder') AND t3_a.FOLDERNAME = 'Movie Reviews') AND t2.DOCNAME = 'Last Discussion';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	DDOCTYPEID_IDX,DFOLDERID_IDX	NULL	NULL	NULL	9	Using where
+1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	34	test.t2.DOCTYPEID	1	
+1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	34	test.t2.DOCID	1	
+1	PRIMARY	t3_a	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t2.FOLDERID	1	Using where
+1	PRIMARY	t3_b	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3_a.PARENTID	1	Using where
+1	PRIMARY	t3_c	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3_b.PARENTID	1	Using where
+1	PRIMARY	t3_d	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3_c.PARENTID	1	Using where
+1	PRIMARY	t3_e	ref|filter	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	FFOLDERID_IDX|CMFLDRPARNT_IDX	34|35	test.t3_d.PARENTID	1 (29%)	Using where; Using rowid filter
+drop table t1, t2, t3, t4;
+CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (a int(10), PRIMARY KEY (a)) Engine=InnoDB;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (a int(10), b int(10), c int(10),
+PRIMARY KEY (a)) Engine=InnoDB;
+INSERT INTO t3 VALUES (1,2,1);
+SELECT t1.* FROM t1 WHERE (SELECT COUNT(*) FROM t3,t2 WHERE t3.c=t2.a 
+and t2.a='1' AND t1.a=t3.b) > 0;
+a
+2
+DROP TABLE t1,t2,t3;
+#
+# Bug #902356: DISTINCT in materialized subquery 
+#
+CREATE TABLE t1 (pk int PRIMARY KEY, a int, KEY(a)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (0, 4), (8, 6);
+CREATE TABLE t2 (pk int PRIMARY KEY, a int, KEY(a)) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (0, 4), (8, 6);
+CREATE TABLE t3 (b INT, KEY(b));
+INSERT INTO t3 VALUES (7), (0), (4), (2);
+CREATE VIEW v1 AS SELECT * FROM t1;
+SET @tmp_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='materialization=on,in_to_exists=on';
+EXPLAIN
+SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM t1) OR a = b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	a	a	5	NULL	2	Using where; Using index
+1	PRIMARY	t3	ref	b	b	5	test.t2.a	2	Using index
+2	SUBQUERY	t1	const	PRIMARY,a	PRIMARY	4	const	1	Using where
+SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM t1) OR a = b;
+pk	a	b
+0	4	4
+EXPLAIN
+SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM v1) OR a = b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	a	a	5	NULL	2	Using where; Using index
+1	PRIMARY	t3	ref	b	b	5	test.t2.a	2	Using index
+2	SUBQUERY	t1	const	PRIMARY,a	PRIMARY	4	const	1	Using where
+SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM v1) OR a = b;
+pk	a	b
+0	4	4
+SET optimizer_switch=@tmp_optimizer_switch;
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+#
+# MDEV-536: LP:1050806 - different result for a query using subquery 
+#
+DROP TABLE IF EXISTS `t1`;
+Warnings:
+Note	1051	Unknown table 'test.t1'
+CREATE TABLE `t1` (
+`node_uid` bigint(20) unsigned DEFAULT NULL,
+`date` datetime DEFAULT NULL,
+`mirror_date` datetime DEFAULT NULL,
+KEY `date` (`date`)
+) ENGINE=MyISAM;
+INSERT INTO `t1` VALUES (2085,'2012-01-01 00:00:00','2013-01-01 00:00:00');
+INSERT INTO `t1` VALUES (2084,'2012-02-01 00:00:00','2013-01-01 00:00:00');
+INSERT INTO `t1` VALUES (2088,'2012-03-01 00:00:00','2013-01-01 00:00:00');
+SELECT * FROM (
+SELECT node_uid, date, mirror_date, @result := 0 AS result
+FROM t1
+WHERE date < '2012-12-12 12:12:12'
+    AND node_uid in (2085, 2084)
+ORDER BY mirror_date ASC
+) AS calculated_result;
+node_uid	date	mirror_date	result
+2085	2012-01-01 00:00:00	2013-01-01 00:00:00	0
+2084	2012-02-01 00:00:00	2013-01-01 00:00:00	0
+DROP TABLE t1;
+#
+# MDEV-567: Wrong result from a query with correlated subquery if ICP is allowed
+#
+CREATE TABLE t1 (a int, b int, INDEX idx(a));
+INSERT INTO t1 VALUES (9,0), (7,1), (1,9), (7,3), (2,1);
+CREATE TABLE t2 (a int, b int, INDEX idx(a));
+INSERT INTO t2 VALUES (2,1), (6,4), (7,6), (9,4);
+CREATE TABLE t3 (a int, b int);
+INSERT INTO t3 VALUES (1,0), (1,1), (1,3);
+SELECT * FROM t3
+WHERE a = (SELECT COUNT(DISTINCT t2.b) FROM t1, t2
+WHERE t1.a = t2.a AND t2.a BETWEEN 7 AND 9
+AND t3.b = t1.b
+GROUP BY t1.b);
+a	b
+1	0
+1	1
+1	3
+set @tmp_mdev567=@@optimizer_switch;
+set optimizer_switch='mrr=off';
+SELECT * FROM t3
+WHERE a = (SELECT COUNT(DISTINCT t2.b) FROM t1, t2
+WHERE t1.a = t2.a AND t2.a BETWEEN 7 AND 9
+AND t3.b = t1.b
+GROUP BY t1.b);
+a	b
+1	0
+1	1
+1	3
+DROP TABLE t1,t2,t3;
+set optimizer_switch=@tmp_mdev567;
+#
+# MDEV-4996: degenerate OR formula in WHERE of a subquery
+#
+CREATE TABLE t1 (a int, c1 varchar(1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (2,'x'), (8,'d');
+CREATE TABLE t2 (m int, n int, c2 varchar(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (0, 5, 'x'), (1, 4,'p');
+SELECT * FROM t1 WHERE c1 NOT IN (
+SELECT t2a.c2 FROM t2 AS t2a, t2 AS t2b, t2 AS t2c
+WHERE t2c.c2 = t2b.c2 AND ( t2a.m = t2b.n OR 0 ) AND
+( t2b.m != a OR t2b.m = t2a.m ));
+a	c1
+2	x
+8	d
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE c1 NOT IN (
+SELECT t2a.c2 FROM t2 AS t2a, t2 AS t2b, t2 AS t2c
+WHERE t2c.c2 = t2b.c2 AND ( t2a.m = t2b.n OR 0 ) AND
+( t2b.m != a OR t2b.m = t2a.m ));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DEPENDENT SUBQUERY	t2a	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DEPENDENT SUBQUERY	t2b	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	t2c	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
+Warnings:
+Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`c1` AS `c1` from `test`.`t1` where !<expr_cache><`test`.`t1`.`c1`,`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#2 */ select `test`.`t2a`.`c2` from `test`.`t2` `t2a` join `test`.`t2` `t2b` join `test`.`t2` `t2c` where `test`.`t2c`.`c2` = `test`.`t2b`.`c2` and `test`.`t2b`.`n` = `test`.`t2a`.`m` and (`test`.`t2b`.`m` <> `test`.`t1`.`a` or `test`.`t2b`.`m` = `test`.`t2a`.`m`) and trigcond(<cache>(`test`.`t1`.`c1`) = `test`.`t2a`.`c2` or `test`.`t2a`.`c2` is null) having trigcond(`test`.`t2a`.`c2` is null))))
+DROP TABLE t1,t2;
+#
+# MDEV-614, also MDEV-536, also LP:1050806:
+#  different result for a query using subquery between 5.5.25 and 5.5.27
+#
+CREATE TABLE `t1` (
+`node_uid` bigint(20) unsigned DEFAULT NULL,
+`date` datetime DEFAULT NULL,
+`mirror_date` datetime DEFAULT NULL,
+KEY `date` (`date`)
+) ENGINE=MyISAM;
+INSERT INTO `t1` VALUES (2085,'2012-01-01 00:00:00','2013-01-01 00:00:00');
+INSERT INTO `t1` VALUES (2084,'2012-02-01 00:00:00','2013-01-01 00:00:00');
+INSERT INTO `t1` VALUES (2088,'2012-03-01 00:00:00','2013-01-01 00:00:00');
+explain
+SELECT * FROM (
+SELECT node_uid, date, mirror_date, @result := 0 AS result
+FROM t1
+WHERE date < '2012-12-12 12:12:12'
+    AND node_uid in (2085, 2084)
+ORDER BY mirror_date ASC
+) AS calculated_result;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	
+2	DERIVED	t1	range	date	date	6	NULL	3	Using index condition; Using where; Rowid-ordered scan; Using filesort
+SELECT * FROM (
+SELECT node_uid, date, mirror_date, @result := 0 AS result
+FROM t1
+WHERE date < '2012-12-12 12:12:12'
+    AND node_uid in (2085, 2084)
+ORDER BY mirror_date ASC
+) AS calculated_result;
+node_uid	date	mirror_date	result
+2085	2012-01-01 00:00:00	2013-01-01 00:00:00	0
+2084	2012-02-01 00:00:00	2013-01-01 00:00:00	0
+set @tmp_mdev614=@@optimizer_switch;
+set optimizer_switch='mrr=off';
+explain
+SELECT * FROM (
+SELECT node_uid, date, mirror_date, @result := 0 AS result
+FROM t1
+WHERE date < '2012-12-12 12:12:12'
+    AND node_uid in (2085, 2084)
+ORDER BY mirror_date ASC
+) AS calculated_result;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	
+2	DERIVED	t1	range	date	date	6	NULL	3	Using index condition; Using where; Using filesort
+SELECT * FROM (
+SELECT node_uid, date, mirror_date, @result := 0 AS result
+FROM t1
+WHERE date < '2012-12-12 12:12:12'
+    AND node_uid in (2085, 2084)
+ORDER BY mirror_date ASC
+) AS calculated_result;
+node_uid	date	mirror_date	result
+2085	2012-01-01 00:00:00	2013-01-01 00:00:00	0
+2084	2012-02-01 00:00:00	2013-01-01 00:00:00	0
+set optimizer_switch=@tmp_mdev614;
+DROP TABLE t1;
+#
+# MDEV-4420: non-expensive single-value subquery used as
+#            used as an access key to join a table
+#
+create table t1 (a varchar(3));
+insert into t1 values ('USA'), ('FRA');
+create table t2 select * from t1;
+insert into t2 values ('RUS');
+create table t3 select * from t2;
+create index idx on t3(a);
+explain extended
+select * from t1, t2 left join t3 on ( t2.a = t3.a )
+where t1.a = t2.a and ( t1.a = ( select min(a) from t1 ) or 0 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t3	ref	idx	idx	6	func	2	100.00	Using where; Using index
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on(`test`.`t3`.`a` = `test`.`t1`.`a`) where `test`.`t1`.`a` = (/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1`) and `test`.`t2`.`a` = (/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1`)
+select * from t1, t2 left join t3 on ( t2.a = t3.a )
+where t1.a = t2.a and ( t1.a = ( select min(a) from t1 ) or 0 );
+a	a	a
+FRA	FRA	FRA
+drop table t1,t2,t3;
+#
+# MDEV-10148: Database crashes in the query to the View
+#
+CREATE TABLE t1 (
+key_code INT(11) NOT NULL,
+value_string VARCHAR(50) NULL DEFAULT NULL,
+PRIMARY KEY (key_code)
+) COLLATE='utf8_general_ci' ENGINE=InnoDB ;
+CREATE TABLE t2 (
+key_code INT(11) NOT NULL,
+target_date DATE NULL DEFAULT NULL,
+PRIMARY KEY (key_code)
+) COLLATE='utf8_general_ci' ENGINE=InnoDB ;
+CREATE TABLE t3 (
+now_date DATE NOT NULL,
+PRIMARY KEY (now_date)
+) COLLATE='utf8_general_ci' ENGINE=InnoDB ;
+CREATE VIEW v1
+AS
+SELECT
+B.key_code,
+B.target_date
+FROM
+t2 B INNER JOIN t3 C ON
+B.target_date = C.now_date
+;
+SET @s = 'SELECT A.* FROM t1 A WHERE A.key_code IN (SELECT key_code FROM v1)';
+PREPARE stmt FROM @s;
+EXECUTE stmt;
+key_code	value_string
+EXECUTE stmt;
+key_code	value_string
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+set optimizer_switch=@save_optimizer_switch;
+create table t1 (a int);
+create table t2 (a int);
+create table t3(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+insert into t2 select a from t1;
+insert into t3 select a from t1;
+select null in (select a from t1 where a < out3.a union select a from t2 where
+(select a from t3) +1 < out3.a+1) from t3 out3;
+ERROR 21000: Subquery returns more than 1 row
+drop table t1, t2, t3;
+CREATE TABLE t1(
+q11 int, q12 int, q13 int, q14 int, q15 int, q16 int, q17 int, q18 int, q19 int,
+q21 int, q22 int, q23 int, q24 int, q25 int, q26 int, q27 int, q28 int, q29 int,
+f1 int
+);
+CREATE TABLE t2(f2 int, f21 int, f3 timestamp, f4 int, f5 int, f6 int);
+INSERT INTO t1 (f1) VALUES (1),(1),(2),(2);
+INSERT INTO t2 VALUES (1,1,"2004-02-29 11:11:11",0,0,0), (2,2,"2004-02-29 11:11:11",0,0,0);
+SELECT f1,
+(SELECT t.f21 from t2 t where max(
+q11+q12+q13+q14+q15+q16+q17+q18+q19+
+q21+q22+q23+q24+q25+q26+q27+q28+q29) = t.f2 UNION
+SELECT t.f3 FROM t2 AS t WHERE t1.f1=t.f2 AND t.f3=MAX(t1.f1) UNION
+SELECT 1 LIMIT 1) AS test
+FROM t1 GROUP BY f1;
+f1	test
+1	1
+2	1
+Warnings:
+Warning	1292	Truncated incorrect datetime value: '1'
+Warning	1292	Truncated incorrect datetime value: '2'
+DROP TABLE t1,t2;