summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/r/func_group.result26
-rw-r--r--mysql-test/r/gis-rtree.result8
-rw-r--r--mysql-test/r/metadata.result34
-rw-r--r--mysql-test/r/query_cache.result48
-rw-r--r--mysql-test/r/subselect.result26
-rw-r--r--mysql-test/r/subselect3.result153
-rw-r--r--mysql-test/r/type_datetime.result12
-rw-r--r--mysql-test/r/type_newdecimal.result13
-rw-r--r--mysql-test/r/udf.result79
-rw-r--r--mysql-test/t/func_group.test25
-rw-r--r--mysql-test/t/gis-rtree.test12
-rw-r--r--mysql-test/t/metadata.test19
-rw-r--r--mysql-test/t/mysql.test18
-rw-r--r--mysql-test/t/order_by.test60
-rw-r--r--mysql-test/t/query_cache.test29
-rw-r--r--mysql-test/t/subselect.test37
-rw-r--r--mysql-test/t/subselect3.test137
-rw-r--r--mysql-test/t/type_datetime.test10
-rw-r--r--mysql-test/t/type_newdecimal.test12
-rw-r--r--mysql-test/t/udf.test44
20 files changed, 737 insertions, 65 deletions
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index c6117053a60..23517f7b603 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -1029,3 +1029,29 @@ t1 CREATE TABLE `t1` (
`stddev(0)` double(8,4) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
+INSERT INTO t1 SELECT a, b+8 FROM t1;
+INSERT INTO t1 SELECT a, b+16 FROM t1;
+INSERT INTO t1 SELECT a, b+32 FROM t1;
+INSERT INTO t1 SELECT a, b+64 FROM t1;
+INSERT INTO t1 SELECT a, b+128 FROM t1;
+INSERT INTO t1 SELECT a, b+256 FROM t1;
+INSERT INTO t1 SELECT a, b+512 FROM t1;
+INSERT INTO t1 SELECT a, b+1024 FROM t1;
+INSERT INTO t1 SELECT a, b+2048 FROM t1;
+INSERT INTO t1 SELECT a, b+4096 FROM t1;
+INSERT INTO t1 SELECT a, b+8192 FROM t1;
+INSERT INTO t1 SELECT a, b+16384 FROM t1;
+INSERT INTO t1 SELECT a, b+32768 FROM t1;
+SELECT a,COUNT(DISTINCT b) AS cnt FROM t1 GROUP BY a HAVING cnt > 50;
+a cnt
+1 65536
+SELECT a,SUM(DISTINCT b) AS sumation FROM t1 GROUP BY a HAVING sumation > 50;
+a sumation
+1 2147516416
+SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50;
+a average
+1 32768.5000
+DROP TABLE t1;
+End of 5.0 tests
diff --git a/mysql-test/r/gis-rtree.result b/mysql-test/r/gis-rtree.result
index e017a31f24b..e8134a50496 100644
--- a/mysql-test/r/gis-rtree.result
+++ b/mysql-test/r/gis-rtree.result
@@ -873,3 +873,11 @@ SELECT 1 FROM t1 WHERE foo != PointFromWKB(POINT(0,0));
1
1
DROP TABLE t1;
+CREATE TABLE t1(foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) );
+INSERT INTO t1(foo) VALUES (NULL);
+ERROR 23000: Column 'foo' cannot be null
+INSERT INTO t1() VALUES ();
+ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
+INSERT INTO t1(foo) VALUES ('');
+ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
+DROP TABLE t1;
diff --git a/mysql-test/r/metadata.result b/mysql-test/r/metadata.result
index 50b0b6ae294..34e961395c4 100644
--- a/mysql-test/r/metadata.result
+++ b/mysql-test/r/metadata.result
@@ -96,3 +96,37 @@ i
2
affected rows: 1
affected rows: 0
+create table t1 (id int(10));
+insert into t1 values (1);
+CREATE VIEW v1 AS select t1.id as id from t1;
+CREATE VIEW v2 AS select t1.id as renamed from t1;
+CREATE VIEW v3 AS select t1.id + 12 as renamed from t1;
+select * from v1 group by id limit 1;
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def test t1 v1 id id 3 10 1 Y 32768 0 63
+id
+1
+select * from v1 group by id limit 0;
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def test t1 v1 id id 3 10 0 Y 32768 0 63
+id
+select * from v1 where id=1000 group by id;
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def test t1 v1 id id 3 10 0 Y 32768 0 63
+id
+select * from v1 where id=1 group by id;
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def test t1 v1 id id 3 10 1 Y 32768 0 63
+id
+1
+select * from v2 where renamed=1 group by renamed;
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def test t1 v2 id renamed 3 10 1 Y 32768 0 63
+renamed
+1
+select * from v3 where renamed=1 group by renamed;
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def v3 renamed 8 12 0 Y 32896 0 63
+renamed
+drop table t1;
+drop view v1,v2,v3;
diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result
index 846c687967d..d2b022a93fb 100644
--- a/mysql-test/r/query_cache.result
+++ b/mysql-test/r/query_cache.result
@@ -1273,3 +1273,51 @@ Variable_name Value
Last_query_cost 0.000000
drop table t1;
SET GLOBAL query_cache_size=0;
+set global query_cache_size=1024*1024;
+flush status;
+create table t1 (a int);
+insert into t1 (a) values (1), (2), (3);
+select * from t1;
+a
+1
+2
+3
+show status like 'Qcache_hits';
+Variable_name Value
+Qcache_hits 0
+select * from t1;
+a
+1
+2
+3
+show status like 'Qcache_hits';
+Variable_name Value
+Qcache_hits 1
+create table t2 like t1;
+select * from t1;
+a
+1
+2
+3
+show status like 'Qcache_hits';
+Variable_name Value
+Qcache_hits 2
+insert into t2 select * from t1;
+select * from t1;
+a
+1
+2
+3
+show status like 'Qcache_hits';
+Variable_name Value
+Qcache_hits 3
+drop table t1, t2;
+create table t1(c1 int);
+create table t2(c1 int);
+create table t3(c1 int);
+create view v1 as select t3.c1 as c1 from t3,t2 where t3.c1 = t2.c1;
+start transaction;
+insert into t1(c1) select c1 from v1;
+drop table t1, t2, t3;
+drop view v1;
+set global query_cache_size=0;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 37f871a4c7c..43247b56096 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -744,7 +744,7 @@ id select_type table type possible_keys key key_len ref rows Extra
3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `1` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1)) union select 3 AS `3` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3))))
+Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `1` having trigcond((<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1))) union select 3 AS `3` having trigcond((<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3)))))
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
id
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
@@ -907,7 +907,7 @@ id select_type table type possible_keys key key_len ref rows Extra
2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 Using where; Using index
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
+Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and trigcond(((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
drop table t1,t2,t3;
create table t1 (a float);
select 10.5 IN (SELECT * from t1 LIMIT 1);
@@ -2816,19 +2816,19 @@ id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where
Warnings:
-Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'0') and ((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) having (<is_not_null_test>(`test`.`t2`.`one`) and <is_not_null_test>(`test`.`t2`.`two`)))) AS `test` from `test`.`t1`
+Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'0') and trigcond((((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))))) having trigcond((<is_not_null_test>(`test`.`t2`.`one`) and <is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where
Warnings:
-Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'N') and (<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`))))
+Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'N') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`))))))
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where; Using temporary; Using filesort
Warnings:
-Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = _latin1'0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)) and <is_not_null_test>(`test`.`t2`.`one`) and <is_not_null_test>(`test`.`t2`.`two`)))) AS `test` from `test`.`t1`
+Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = _latin1'0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having trigcond((((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)) and <is_not_null_test>(`test`.`t2`.`one`) and <is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
CREATE TABLE t1 (a char(5), b char(5));
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
@@ -3576,3 +3576,19 @@ FROM t1 GROUP BY t1.a LIMIT 1)
2
2
DROP TABLE t1,t2;
+CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));
+CREATE TABLE t2 (x int auto_increment, y int, z int,
+PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));
+SET SESSION sort_buffer_size = 32 * 1024;
+SELECT SQL_NO_CACHE COUNT(*)
+FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
+FROM t1) t;
+COUNT(*)
+3000
+SET SESSION sort_buffer_size = 8 * 1024 * 1024;
+SELECT SQL_NO_CACHE COUNT(*)
+FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
+FROM t1) t;
+COUNT(*)
+3000
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
new file mode 100644
index 00000000000..5ab8e448b39
--- /dev/null
+++ b/mysql-test/r/subselect3.result
@@ -0,0 +1,153 @@
+drop table if exists t0, t1, t2, t3, t4;
+create table t1 (oref int, grp int, ie int) ;
+insert into t1 (oref, grp, ie) values
+(1, 1, 1),
+(1, 1, 1),
+(1, 2, NULL),
+(2, 1, 3),
+(3, 1, 4),
+(3, 2, NULL);
+create table t2 (oref int, a int);
+insert into t2 values
+(1, 1),
+(2, 2),
+(3, 3),
+(4, NULL),
+(2, NULL);
+select a, oref, a in (select max(ie)
+from t1 where oref=t2.oref group by grp) from t2;
+a oref a in (select max(ie)
+from t1 where oref=t2.oref group by grp)
+1 1 1
+2 2 0
+3 3 NULL
+NULL 4 0
+NULL 2 NULL
+explain extended
+select a, oref, a in (select max(ie)
+from t1 where oref=t2.oref group by grp) from t2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 5
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort
+Warnings:
+Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie)
+from t1 where oref=t2.oref group by grp)` from `test`.`t2`
+explain extended
+select a, oref from t2
+where a in (select max(ie) from t1 where oref=t2.oref group by grp);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort
+Warnings:
+Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))
+create table t3 (a int);
+insert into t3 values (NULL), (NULL);
+flush status;
+select a in (select max(ie) from t1 where oref=4 group by grp) from t3;
+a in (select max(ie) from t1 where oref=4 group by grp)
+0
+0
+show status like 'Handler_read_rnd_next';
+Variable_name Value
+Handler_read_rnd_next 11
+select ' ^ This must show 11' Z;
+Z
+ ^ This must show 11
+explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort
+Warnings:
+Note 1003 select <in_optimizer>(`test`.`t3`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = 4) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t3`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) from t1 where oref=4 group by grp)` from `test`.`t3`
+drop table t1, t2, t3;
+create table t1 (a int, oref int, key(a));
+insert into t1 values
+(1, 1),
+(1, NULL),
+(2, 3),
+(2, NULL),
+(3, NULL);
+create table t2 (a int, oref int);
+insert into t2 values (1, 1), (2,2), (NULL, 3), (NULL, 4);
+select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2;
+oref a Z
+1 1 1
+2 2 0
+3 NULL NULL
+4 NULL 0
+explain extended
+select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 4
+2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Using where
+Warnings:
+Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where (`test`.`t1`.`oref` = `test`.`t2`.`oref`)))) AS `Z` from `test`.`t2`
+flush status;
+select oref, a from t2 where a in (select a from t1 where oref=t2.oref);
+oref a
+1 1
+show status like '%Handler_read_rnd_next';
+Variable_name Value
+Handler_read_rnd_next 5
+delete from t2;
+insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0);
+flush status;
+select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2;
+oref a Z
+0 NULL 0
+0 NULL 0
+0 NULL 0
+0 NULL 0
+show status like '%Handler_read%';
+Variable_name Value
+Handler_read_first 0
+Handler_read_key 0
+Handler_read_next 0
+Handler_read_prev 0
+Handler_read_rnd 0
+Handler_read_rnd_next 29
+select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z;
+Z
+No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.
+drop table t1, t2;
+create table t1 (a int, b int, primary key (a));
+insert into t1 values (1,1), (3,1),(100,1);
+create table t2 (a int, b int);
+insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0);
+select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ;
+a b Z
+1 1 1
+2 1 0
+NULL 1 NULL
+NULL 0 0
+drop table t1, t2;
+create table t1 (a int, b int, key(a));
+insert into t1 values
+(0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+create table t2 like t1;
+insert into t2 select * from t1;
+update t2 set b=1;
+create table t3 (a int, oref int);
+insert into t3 values (1, 1), (NULL,1), (NULL,0);
+select a, oref,
+t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z
+from t3;
+a oref Z
+1 1 1
+NULL 1 NULL
+NULL 0 0
+explain extended
+select a, oref,
+t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z
+from t3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 3
+2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 4 Using where
+2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 Using where
+Warnings:
+Note 1276 Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1
+Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond(((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`)))) AS `Z` from `test`.`t3`
+drop table t1, t2, t3;
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 49e4827cb97..7fc1c4f398d 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -179,3 +179,15 @@ a
2006-06-06 15:55:55
DROP PREPARE s;
DROP TABLE t1;
+SELECT CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6));
+CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6))
+20060810.000000
+SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6));
+CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6))
+20060810101112.000000
+SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6));
+CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6))
+20060810101112.000014
+SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6));
+CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6))
+101112.098700
diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result
index 84d2ea47298..e65e76ded3f 100644
--- a/mysql-test/r/type_newdecimal.result
+++ b/mysql-test/r/type_newdecimal.result
@@ -1410,3 +1410,16 @@ i2 count(distinct j)
1.0 2
2.0 2
drop table t1;
+create table t1(f1 decimal(20,6));
+insert into t1 values (CAST('10:11:12' AS date) + interval 14 microsecond);
+insert into t1 values (CAST('10:11:12' AS time));
+select * from t1;
+f1
+20101112000000.000014
+101112.000000
+drop table t1;
+select cast(19999999999999999999 as unsigned);
+cast(19999999999999999999 as unsigned)
+18446744073709551615
+Warnings:
+Error 1292 Truncated incorrect DECIMAL value: ''
diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result
index 8e37cca6aa9..396f1efa1b7 100644
--- a/mysql-test/r/udf.result
+++ b/mysql-test/r/udf.result
@@ -105,6 +105,85 @@ explain select myfunc_int(f1) from t1 order by 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
drop table t1;
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 values (1,1),(2,2);
+CREATE FUNCTION fn(a int) RETURNS int DETERMINISTIC
+BEGIN
+RETURN a;
+END
+||
+CREATE VIEW v1 AS SELECT a, fn(MIN(b)) as c FROM t1 GROUP BY a;
+SELECT myfunc_int(a AS attr_name) FROM t1;
+myfunc_int(a AS attr_name)
+1
+2
+EXPLAIN EXTENDED SELECT myfunc_int(a AS attr_name) FROM t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+Warnings:
+Note 1003 select myfunc_int(`test`.`t1`.`a` AS `attr_name`) AS `myfunc_int(a AS attr_name)` from `test`.`t1`
+EXPLAIN EXTENDED SELECT myfunc_int(a) FROM t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+Warnings:
+Note 1003 select myfunc_int(`test`.`t1`.`a` AS `a`) AS `myfunc_int(a)` from `test`.`t1`
+SELECT a,c FROM v1;
+a c
+1 1
+2 2
+SELECT a, fn(MIN(b) xx) as c FROM t1 GROUP BY a;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx) as c FROM t1 GROUP BY a' at line 1
+SELECT myfunc_int(fn(MIN(b) xx)) as c FROM t1 GROUP BY a;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx)) as c FROM t1 GROUP BY a' at line 1
+SELECT myfunc_int(test.fn(MIN(b) xx)) as c FROM t1 GROUP BY a;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx)) as c FROM t1 GROUP BY a' at line 1
+SELECT myfunc_int(fn(MIN(b)) xx) as c FROM t1 GROUP BY a;
+c
+1
+2
+SELECT myfunc_int(test.fn(MIN(b)) xx) as c FROM t1 GROUP BY a;
+c
+1
+2
+EXPLAIN EXTENDED SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
+Warnings:
+Note 1003 select myfunc_int(min(`test`.`t1`.`b`) AS `xx`) AS `c` from `test`.`t1` group by `test`.`t1`.`a`
+EXPLAIN EXTENDED SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
+Warnings:
+Note 1003 select `test`.`fn`(min(`test`.`t1`.`b`)) AS `c` from `test`.`t1` group by `test`.`t1`.`a`
+EXPLAIN EXTENDED SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
+Warnings:
+Note 1003 select myfunc_int(`test`.`fn`(min(`test`.`t1`.`b`)) AS `fn(MIN(b))`) AS `c` from `test`.`t1` group by `test`.`t1`.`a`
+EXPLAIN EXTENDED SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
+Warnings:
+Note 1003 select myfunc_int(`test`.`fn`(min(`test`.`t1`.`b`)) AS `test.fn(MIN(b))`) AS `c` from `test`.`t1` group by `test`.`t1`.`a`
+SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a;
+c
+1
+2
+SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a;
+c
+1
+2
+SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a;
+c
+1
+2
+SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a;
+c
+1
+2
+DROP VIEW v1;
+DROP TABLE t1;
+DROP FUNCTION fn;
End of 5.0 tests.
DROP FUNCTION metaphon;
DROP FUNCTION myfunc_double;
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index 079d107fad8..089f5ed9911 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -700,3 +700,28 @@ create table t1 select stddev(0);
show create table t1;
drop table t1;
+#
+# Bug #23184: SELECT causes server crash
+#
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
+INSERT INTO t1 SELECT a, b+8 FROM t1;
+INSERT INTO t1 SELECT a, b+16 FROM t1;
+INSERT INTO t1 SELECT a, b+32 FROM t1;
+INSERT INTO t1 SELECT a, b+64 FROM t1;
+INSERT INTO t1 SELECT a, b+128 FROM t1;
+INSERT INTO t1 SELECT a, b+256 FROM t1;
+INSERT INTO t1 SELECT a, b+512 FROM t1;
+INSERT INTO t1 SELECT a, b+1024 FROM t1;
+INSERT INTO t1 SELECT a, b+2048 FROM t1;
+INSERT INTO t1 SELECT a, b+4096 FROM t1;
+INSERT INTO t1 SELECT a, b+8192 FROM t1;
+INSERT INTO t1 SELECT a, b+16384 FROM t1;
+INSERT INTO t1 SELECT a, b+32768 FROM t1;
+SELECT a,COUNT(DISTINCT b) AS cnt FROM t1 GROUP BY a HAVING cnt > 50;
+SELECT a,SUM(DISTINCT b) AS sumation FROM t1 GROUP BY a HAVING sumation > 50;
+SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50;
+
+DROP TABLE t1;
+
+--echo End of 5.0 tests
diff --git a/mysql-test/t/gis-rtree.test b/mysql-test/t/gis-rtree.test
index 09cdcb2435e..e34dd14dbfc 100644
--- a/mysql-test/t/gis-rtree.test
+++ b/mysql-test/t/gis-rtree.test
@@ -242,3 +242,15 @@ INSERT INTO t1 (foo) VALUES (PointFromWKB(POINT(0,0)));
SELECT 1 FROM t1 WHERE foo != PointFromWKB(POINT(0,0));
DROP TABLE t1;
# End of 4.1 tests
+
+#
+# bug #21790 (UNKNOWN ERROR on NULLs in RTree)
+#
+CREATE TABLE t1(foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) );
+--error 1048
+INSERT INTO t1(foo) VALUES (NULL);
+--error 1416
+INSERT INTO t1() VALUES ();
+--error 1416
+INSERT INTO t1(foo) VALUES ('');
+DROP TABLE t1;
diff --git a/mysql-test/t/metadata.test b/mysql-test/t/metadata.test
index 65338448555..a6ebfdc14c1 100644
--- a/mysql-test/t/metadata.test
+++ b/mysql-test/t/metadata.test
@@ -61,4 +61,23 @@ drop table t1;//
delimiter ;//
--disable_info
+#
+# Bug #20191: getTableName gives wrong or inconsistent result when using VIEWs
+#
+--enable_metadata
+create table t1 (id int(10));
+insert into t1 values (1);
+CREATE VIEW v1 AS select t1.id as id from t1;
+CREATE VIEW v2 AS select t1.id as renamed from t1;
+CREATE VIEW v3 AS select t1.id + 12 as renamed from t1;
+select * from v1 group by id limit 1;
+select * from v1 group by id limit 0;
+select * from v1 where id=1000 group by id;
+select * from v1 where id=1 group by id;
+select * from v2 where renamed=1 group by renamed;
+select * from v3 where renamed=1 group by renamed;
+drop table t1;
+drop view v1,v2,v3;
+--disable_metadata
+
# End of 4.1 tests
diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test
index c06e52e2d78..cb4a5593de7 100644
--- a/mysql-test/t/mysql.test
+++ b/mysql-test/t/mysql.test
@@ -143,6 +143,24 @@ drop table t1;
--exec diff $MYSQLTEST_VARDIR/tmp/bug20328_1.result $MYSQLTEST_VARDIR/tmp/bug20328_2.result
#
+# Bug #19216: Client crashes on long SELECT
+#
+--exec echo "select" > $MYSQLTEST_VARDIR/tmp/b19216.tmp
+# 3400 * 20 makes 68000 columns that is more than the max number that can fit
+# in a 16 bit number.
+let $i= 3400;
+while ($i)
+{
+ --exec echo "'a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a'," >> $MYSQLTEST_VARDIR/tmp/b19216.tmp
+ dec $i;
+}
+
+--exec echo "'b';" >> $MYSQLTEST_VARDIR/tmp/b19216.tmp
+--disable_query_log
+--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/b19216.tmp >/dev/null
+--enable_query_log
+
+#
# Bug #20103: Escaping with backslash does not work
#
--exec echo "SET SQL_MODE = 'NO_BACKSLASH_ESCAPES';" > $MYSQLTEST_VARDIR/tmp/bug20103.sql
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index ff75186f64e..d7cf0e2a375 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -595,66 +595,6 @@ select a, b from t1 group by a, b order by sum(c);
drop table t1;
#
-# Bug #7331
-#
-
-create table t1 (
- `sid` decimal(8,0) default null,
- `wnid` varchar(11) not null default '',
- key `wnid14` (`wnid`(4)),
- key `wnid` (`wnid`)
-) engine=myisam default charset=latin1;
-
-insert into t1 (`sid`, `wnid`) values
-('10100','01019000000'),('37986','01019000000'),('37987','01019010000'),
-('39560','01019090000'),('37989','01019000000'),('37990','01019011000'),
-('37991','01019011000'),('37992','01019019000'),('37993','01019030000'),
-('37994','01019090000'),('475','02070000000'),('25253','02071100000'),
-('25255','02071100000'),('25256','02071110000'),('25258','02071130000'),
-('25259','02071190000'),('25260','02071200000'),('25261','02071210000'),
-('25262','02071290000'),('25263','02071300000'),('25264','02071310000'),
-('25265','02071310000'),('25266','02071320000'),('25267','02071320000'),
-('25269','02071330000'),('25270','02071340000'),('25271','02071350000'),
-('25272','02071360000'),('25273','02071370000'),('25281','02071391000'),
-('25282','02071391000'),('25283','02071399000'),('25284','02071400000'),
-('25285','02071410000'),('25286','02071410000'),('25287','02071420000'),
-('25288','02071420000'),('25291','02071430000'),('25290','02071440000'),
-('25292','02071450000'),('25293','02071460000'),('25294','02071470000'),
-('25295','02071491000'),('25296','02071491000'),('25297','02071499000');
-
-explain select * from t1 where wnid like '0101%' order by wnid;
-
-select * from t1 where wnid like '0101%' order by wnid;
-
-drop table t1;
-
-#
-# Bug #7672 - a wrong result for a select query in braces followed by order by
-#
-
-CREATE TABLE t1 (a int);
-INSERT INTO t1 VALUES (2), (1), (1), (2), (1);
-SELECT a FROM t1 ORDER BY a;
-(SELECT a FROM t1) ORDER BY a;
-DROP TABLE t1;
-
-#
-# Bug #18767: global ORDER BY applied to a SELECT with ORDER BY either was
-# ignored or 'concatened' to the latter.
-
-CREATE TABLE t1 (a int, b int);
-INSERT INTO t1 VALUES (1,30), (2,20), (1,10), (2,30), (1,20), (2,10);
-
-(SELECT b,a FROM t1 ORDER BY a,b) ORDER BY b,a;
-(SELECT b FROM t1 ORDER BY b DESC) ORDER BY b ASC;
-(SELECT b,a FROM t1 ORDER BY b,a) ORDER BY a,b;
-(SELECT b,a FROM t1 ORDER by b,a LIMIT 3) ORDER by a,b;
-
-DROP TABLE t1;
-
-# End of 4.1 tests
-
-#
# Bug#21302: Result not properly sorted when using an ORDER BY on a second
# table in a join
#
diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test
index d86f1a464b1..f6e480282ff 100644
--- a/mysql-test/t/query_cache.test
+++ b/mysql-test/t/query_cache.test
@@ -870,3 +870,32 @@ select * from t1 where a > 3;
show status like 'last_query_cost';
drop table t1;
SET GLOBAL query_cache_size=0;
+
+#
+# Bug #20045: Server crash on INSERT ... SELECT ... FROM non-mergeable view
+#
+set global query_cache_size=1024*1024;
+flush status;
+create table t1 (a int);
+insert into t1 (a) values (1), (2), (3);
+select * from t1;
+show status like 'Qcache_hits';
+select * from t1;
+show status like 'Qcache_hits';
+create table t2 like t1;
+select * from t1;
+show status like 'Qcache_hits';
+insert into t2 select * from t1;
+select * from t1;
+show status like 'Qcache_hits';
+drop table t1, t2;
+
+create table t1(c1 int);
+create table t2(c1 int);
+create table t3(c1 int);
+create view v1 as select t3.c1 as c1 from t3,t2 where t3.c1 = t2.c1;
+start transaction;
+insert into t1(c1) select c1 from v1;
+drop table t1, t2, t3;
+drop view v1;
+set global query_cache_size=0;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index d62b0e77952..1cfbc85128a 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -2459,3 +2459,40 @@ SELECT (
FROM t1 t2
GROUP BY t2.a;
DROP TABLE t1,t2;
+
+#
+# Bug #21727: Correlated subquery that requires filesort:
+# slow with big sort_buffer_size
+#
+
+CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));
+CREATE TABLE t2 (x int auto_increment, y int, z int,
+ PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));
+
+disable_query_log;
+let $1=3000;
+while ($1)
+{
+ eval INSERT INTO t1(a) VALUES(RAND()*1000);
+ eval SELECT MAX(b) FROM t1 INTO @id;
+ let $2=10;
+ while ($2)
+ {
+ eval INSERT INTO t2(y,z) VALUES(@id,RAND()*1000);
+ dec $2;
+ }
+ dec $1;
+}
+enable_query_log;
+
+SET SESSION sort_buffer_size = 32 * 1024;
+SELECT SQL_NO_CACHE COUNT(*)
+ FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
+ FROM t1) t;
+
+SET SESSION sort_buffer_size = 8 * 1024 * 1024;
+SELECT SQL_NO_CACHE COUNT(*)
+ FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
+ FROM t1) t;
+
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test
new file mode 100644
index 00000000000..f7fbafdd17f
--- /dev/null
+++ b/mysql-test/t/subselect3.test
@@ -0,0 +1,137 @@
+--disable_warnings
+drop table if exists t0, t1, t2, t3, t4;
+--enable_warnings
+
+#
+# 1. Subquery with GROUP/HAVING
+#
+create table t1 (oref int, grp int, ie int) ;
+insert into t1 (oref, grp, ie) values
+ (1, 1, 1),
+ (1, 1, 1),
+ (1, 2, NULL),
+
+ (2, 1, 3),
+
+ (3, 1, 4),
+ (3, 2, NULL);
+
+# Ok, for
+# select max(ie) from t1 where oref=PARAM group by grp
+# we'll have:
+# 1 -> (1, NULL) matching + NULL
+# 2 -> (3) non-matching
+# 3 -> (3, NULL) non-matching + NULL
+# 4 -> () nothing.
+
+create table t2 (oref int, a int);
+insert into t2 values
+ (1, 1),
+ (2, 2),
+ (3, 3),
+ (4, NULL),
+ (2, NULL);
+
+# true, false, null, false, null
+select a, oref, a in (select max(ie)
+ from t1 where oref=t2.oref group by grp) from t2;
+
+# This must have a trigcond
+explain extended
+select a, oref, a in (select max(ie)
+ from t1 where oref=t2.oref group by grp) from t2;
+
+# This must not have a trigcond:
+explain extended
+select a, oref from t2
+where a in (select max(ie) from t1 where oref=t2.oref group by grp);
+
+
+# Non-correlated subquery, 2 NULL evaluations
+create table t3 (a int);
+insert into t3 values (NULL), (NULL);
+flush status;
+select a in (select max(ie) from t1 where oref=4 group by grp) from t3;
+show status like 'Handler_read_rnd_next';
+select ' ^ This must show 11' Z;
+
+# This must show trigcond:
+explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3;
+
+drop table t1, t2, t3;
+
+#
+# 2. Subquery handled with 'index_subquery':
+#
+create table t1 (a int, oref int, key(a));
+insert into t1 values
+ (1, 1),
+ (1, NULL),
+ (2, 3),
+ (2, NULL),
+ (3, NULL);
+
+create table t2 (a int, oref int);
+insert into t2 values (1, 1), (2,2), (NULL, 3), (NULL, 4);
+
+select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2;
+
+# The next explain shows "using index" but that is just incorrect display
+# (there is a bug filed about this).
+explain extended
+select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2;
+
+flush status;
+select oref, a from t2 where a in (select a from t1 where oref=t2.oref);
+# This will only show access to t2:
+show status like '%Handler_read_rnd_next';
+
+# Check that repeated NULL-scans are not cached (subq. is not correlated):
+delete from t2;
+insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0);
+
+flush status;
+select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2;
+show status like '%Handler_read%';
+select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z;
+
+drop table t1, t2;
+
+#
+# 3. Subquery handled with 'unique_index_subquery':
+#
+create table t1 (a int, b int, primary key (a));
+insert into t1 values (1,1), (3,1),(100,1);
+
+create table t2 (a int, b int);
+insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0);
+
+select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ;
+
+drop table t1, t2;
+
+#
+# 4. Subquery that is a join, with ref access
+#
+create table t1 (a int, b int, key(a));
+insert into t1 values
+ (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+
+create table t2 like t1;
+insert into t2 select * from t1;
+update t2 set b=1;
+
+create table t3 (a int, oref int);
+insert into t3 values (1, 1), (NULL,1), (NULL,0);
+select a, oref,
+ t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z
+from t3;
+
+# This must have trigcond in WHERE and HAVING:
+explain extended
+select a, oref,
+ t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z
+from t3;
+
+drop table t1, t2, t3;
+
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index cdf73bf6c89..3ad6bdc53e4 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -125,3 +125,13 @@ PREPARE s FROM 'SELECT a FROM t1 WHERE a=(SELECT MAX(a) FROM t1) AND (a="2006060
EXECUTE s;
DROP PREPARE s;
DROP TABLE t1;
+
+
+#
+# Bug 19491 (CAST DATE AS DECIMAL returns incorrect result
+#
+SELECT CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6));
+SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6));
+SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6));
+SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6));
+
diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test
index e4843c3b83e..f2031498687 100644
--- a/mysql-test/t/type_newdecimal.test
+++ b/mysql-test/t/type_newdecimal.test
@@ -1108,3 +1108,15 @@ insert into t1 values (1,1), (1,2), (2,3), (2,4);
select i, count(distinct j) from t1 group by i;
select i+0.0 as i2, count(distinct j) from t1 group by i2;
drop table t1;
+
+create table t1(f1 decimal(20,6));
+insert into t1 values (CAST('10:11:12' AS date) + interval 14 microsecond);
+insert into t1 values (CAST('10:11:12' AS time));
+select * from t1;
+drop table t1;
+
+#
+# Bug #8663 (cant use bigint as input to CAST)
+#
+select cast(19999999999999999999 as unsigned);
+
diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test
index 96e559f5c05..37358a292be 100644
--- a/mysql-test/t/udf.test
+++ b/mysql-test/t/udf.test
@@ -127,6 +127,50 @@ create table t1(f1 int);
insert into t1 values(1),(2);
explain select myfunc_int(f1) from t1 order by 1;
drop table t1;
+
+#
+# Bug #21809: Error 1356 while selecting from view with grouping though
+# underlying select OK.
+#
+CREATE TABLE t1(a INT, b INT); INSERT INTO t1 values (1,1),(2,2);
+
+DELIMITER ||;
+CREATE FUNCTION fn(a int) RETURNS int DETERMINISTIC
+BEGIN
+ RETURN a;
+END
+||
+DELIMITER ;||
+
+CREATE VIEW v1 AS SELECT a, fn(MIN(b)) as c FROM t1 GROUP BY a;
+
+SELECT myfunc_int(a AS attr_name) FROM t1;
+EXPLAIN EXTENDED SELECT myfunc_int(a AS attr_name) FROM t1;
+EXPLAIN EXTENDED SELECT myfunc_int(a) FROM t1;
+SELECT a,c FROM v1;
+
+--error ER_PARSE_ERROR
+SELECT a, fn(MIN(b) xx) as c FROM t1 GROUP BY a;
+--error ER_PARSE_ERROR
+SELECT myfunc_int(fn(MIN(b) xx)) as c FROM t1 GROUP BY a;
+--error ER_PARSE_ERROR
+SELECT myfunc_int(test.fn(MIN(b) xx)) as c FROM t1 GROUP BY a;
+
+SELECT myfunc_int(fn(MIN(b)) xx) as c FROM t1 GROUP BY a;
+SELECT myfunc_int(test.fn(MIN(b)) xx) as c FROM t1 GROUP BY a;
+
+EXPLAIN EXTENDED SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a;
+EXPLAIN EXTENDED SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a;
+EXPLAIN EXTENDED SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a;
+EXPLAIN EXTENDED SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a;
+SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a;
+SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a;
+SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a;
+SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a;
+DROP VIEW v1;
+DROP TABLE t1;
+DROP FUNCTION fn;
+
--echo End of 5.0 tests.
#
View Lorry Controller Status