summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/func_group.result26
-rw-r--r--mysql-test/r/order_by.result27
-rw-r--r--mysql-test/r/subselect.result58
-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.result8
-rw-r--r--mysql-test/r/udf.result79
7 files changed, 358 insertions, 5 deletions
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 40ca0a38db2..8158cab4323 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -1020,3 +1020,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/order_by.result b/mysql-test/r/order_by.result
index 6461696b0d0..577c831057e 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -854,6 +854,33 @@ b a
20 1
10 2
DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+SELECT a + 1 AS num FROM t1 ORDER BY 30 - num;
+num
+3
+2
+SELECT CONCAT('test', a) AS str FROM t1 ORDER BY UPPER(str);
+str
+test1
+test2
+SELECT a + 1 AS num FROM t1 GROUP BY 30 - num;
+num
+3
+2
+SELECT a + 1 AS num FROM t1 HAVING 30 - num;
+num
+2
+3
+SELECT a + 1 AS num, num + 1 FROM t1;
+ERROR 42S22: Unknown column 'num' in 'field list'
+SELECT a + 1 AS num, (select num + 2 FROM t1 LIMIT 1) FROM t1;
+num (select num + 2 FROM t1 LIMIT 1)
+2 4
+3 5
+SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a;
+ERROR 42S22: Unknown column 'num' in 'on clause'
+DROP TABLE t1;
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a));
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
explain SELECT t1.b as a, t2.b as c FROM
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index e57cc44ddd4..0b72814797c 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 filtered Extra
3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,3> ALL NULL 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 filtered Extra
2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 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);
@@ -2817,19 +2817,19 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 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 filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Using where
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 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 filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 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');
@@ -3002,6 +3002,38 @@ field1 field2
1 1
1 3
DROP TABLE t1, t2;
+CREATE TABLE t1(a int, INDEX (a));
+INSERT INTO t1 VALUES (1), (3), (5), (7);
+INSERT INTO t1 VALUES (NULL);
+CREATE TABLE t2(a int);
+INSERT INTO t2 VALUES (1),(2),(3);
+EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 3
+2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index
+SELECT a, a IN (SELECT a FROM t1) FROM t2;
+a a IN (SELECT a FROM t1)
+1 1
+2 NULL
+3 1
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a DATETIME);
+INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');
+CREATE TABLE t2 AS SELECT
+(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a
+FROM t1 WHERE a > '2000-01-01';
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `sub_a` datetime default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01');
+SHOW CREATE TABLE t3;
+Table Create Table
+t3 CREATE TABLE `t3` (
+ `a` datetime default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2,t3;
create table t1 (df decimal(5,1));
insert into t1 values(1.1);
insert into t1 values(2.2);
@@ -3545,3 +3577,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 04b37f7ae86..a8d5388097d 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 0b6bdfda6bc..84e479c4fe6 100644
--- a/mysql-test/r/type_newdecimal.result
+++ b/mysql-test/r/type_newdecimal.result
@@ -1422,6 +1422,14 @@ 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(143.481 as decimal(4,1));
cast(143.481 as decimal(4,1))
143.5
diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result
index a552282fece..30c335a12c7 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;
View Lorry Controller Status