Merge 5.3-5.5

1 files changed, 110 insertions, 0 deletions

diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index 459ece978fd..1ba078c1c1c 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -879,6 +879,116 @@ NULL
 7
 drop view v;
 drop table t1, t2;
+CREATE TABLE t1 (
+id int, i1 int, i2 int DEFAULT 0,
+d1 date DEFAULT '2000-01-01', d2 date DEFAULT '2000-01-01',
+t1 time DEFAULT '00:00:00', t2 time DEFAULT '00:00:00',
+dt1 datetime NOT NULL DEFAULT '2000-01-01 00:00:00',
+dt2 datetime NOT NULL DEFAULT '2000-01-01 00:00:00',
+c1 varchar(1) NOT NULL, c2 varchar(1) NOT NULL
+) ENGINE=MyISAM;
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 (id,i1,c1,c2) VALUES 
+(1,7,'t','t'),(2,4,'k','k'),(3,2,'e','e'),(4,0,'i','i'),(5,1,'t','t'),(6,91,'m','m'),
+(7,6,'z','z'),(8,3,'c','c'),(9,6,'i','i'),(10,8,'v','v'), (11,1,'l','l'),(12,4,'j','j'),
+(13,5,'w','w'),(14,0,'r','r'),(15,7,'o','o'),(16,9,'o','o'),(17,7,'u','u'),(18,6,'f','f'),
+(19,0,'l','l'),(20,6,'g','g'),(21,1,'e','e'),(22,7,'y','y'),(23,0,'p','p'),(24,6,'v','v'),
+(25,5,'d','d'),(26,9,'i','i'),(27,5,'z','z'),(28,2,'q','q'),(29,4,'j','j'),(30,9,'m','m'),
+(31,8,'d','d'),(32,5,'r','r'),(33,1,'r','r'),(34,1,'k','k'),(35,4,'p','p'),(36,2,'x','x'),
+(37,5,'w','w'),(38,0,'k','k'),(39,7,'y','y'),(40,4,'p','p'),(41,9,'l','l'),(42,2,'u','u'),
+(43,3,'r','r'),(44,5,'y','y'),(45,3,'u','u'),(46,9,'t','t'),(47,8,'f','f'),(48,2,'f','f'),
+(49,2,'q','q'),(50,6,'v','v'),(51,6,'u','u'),(52,0,'b','b'),(53,1,'n','n'),(54,2,'p','p'),
+(55,0,'y','y'),(56,1,'l','l'),(57,1,'c','c'),(58,0,'d','d'),(59,2,'y','y'),(60,7,'l','l'),
+(61,6,'m','m'),(62,9,'q','q'),(63,0,'j','j'),(64,3,'u','u'),(65,4,'w','w'),(66,5,'p','p'),
+(67,8,'z','z'),(68,5,'u','u'),(69,7,'b','b'),(70,0,'f','f'),(71,6,'u','u'),(72,1,'i','i'),
+(73,9,'s','s'),(74,3,'y','y'),(75,5,'s','s'),(76,8,'x','x'),(77,3,'s','s'),(78,3,'l','l'),
+(79,8,'b','b'),(80,0,'p','p'),(81,9,'m','m'),(82,5,'k','k'),(83,7,'u','u'),(84,0,'y','y'),
+(85,2,'x','x'),(86,5,'h','h'),(87,5,'j','j'),(88,5,'o','o'),(89,9,'o','o'),(90,1,'c','c'),
+(91,7,'k','k'),(92,9,'t','t'),(93,3,'h','h'),(94,6,'g','g'),(95,9,'r','r'),(96,2,'i','i');
+CREATE TABLE t2 (i INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (7),(8);
+SELECT STRAIGHT_JOIN COUNT(DISTINCT t1.id)  FROM
+t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3;
+COUNT(DISTINCT t1.id)
+18
+EXPLAIN EXTENDED
+SELECT STRAIGHT_JOIN DISTINCT t1.id  FROM
+t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	96	100.00	Using where; Using temporary
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.i1	9	100.00	Using where; Distinct
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Distinct; Using join buffer (flat, BNL join)
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	96	100.00	
+Warnings:
+Note	1003	select straight_join distinct `test`.`t1`.`id` AS `id` from `test`.`t1` join `test`.`v1` join `test`.`t2` where ((`test`.`t2`.`i` = `v1`.`id`) and (`v1`.`i1` = `test`.`t1`.`i1`) and (`v1`.`id` <> 3))
+set join_buffer_size=1024;
+SELECT STRAIGHT_JOIN DISTINCT t1.id  FROM
+t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3;
+id
+7
+8
+9
+18
+20
+24
+43
+45
+50
+51
+61
+64
+71
+74
+77
+78
+93
+94
+set join_buffer_size=1024*16;
+SELECT STRAIGHT_JOIN DISTINCT t1.id  FROM
+t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3;
+id
+7
+9
+18
+20
+24
+8
+50
+51
+61
+43
+45
+71
+64
+74
+77
+78
+94
+93
+set join_buffer_size=default;
+SELECT STRAIGHT_JOIN DISTINCT t1.id  FROM
+t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3;
+id
+7
+9
+18
+20
+24
+50
+51
+61
+71
+94
+8
+43
+45
+64
+74
+77
+78
+93
+DROP VIEW v1;
+DROP TABLE t1,t2;
 #
 # Bug #11744875: 4082: integer lengths cause truncation with distinct concat and innodb
 #