7 files changed, 308 insertions, 0 deletions
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index 78393ee1104..b41f6cd71c8 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -707,3 +707,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/order_by.test b/mysql-test/t/order_by.test
index 1104c859ab8..30dc4d2653e 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -577,6 +577,22 @@ INSERT INTO t1 VALUES (1,30), (2,20), (1,10), (2,30), (1,20), (2,10);
 
 DROP TABLE t1;
 
+#
+# Bug #22457: Column alias in ORDER BY works, but not if in an expression
+#
+
+CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2);
+SELECT a + 1 AS num FROM t1 ORDER BY 30 - num;
+SELECT CONCAT('test', a) AS str FROM t1 ORDER BY UPPER(str);
+SELECT a + 1 AS num FROM t1 GROUP BY 30 - num;
+SELECT a + 1 AS num FROM t1 HAVING 30 - num;
+--error 1054
+SELECT a + 1 AS num, num + 1 FROM t1;
+SELECT a + 1 AS num, (select num + 2 FROM t1 LIMIT 1) FROM t1;
+--error 1054
+SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a;
+DROP TABLE t1;
+
 # End of 4.1 tests
 
 #
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 6d5082c360b..75c1c0c0ffe 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -1955,6 +1955,39 @@ SELECT field1, field2
 
 DROP TABLE t1, t2;
 
+#
+# Bug #23478: not top-level IN subquery returning a non-empty result set
+#             with possible NULL values by index access from the outer query
+#
+
+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;
+SELECT a, a IN (SELECT a FROM t1) FROM t2;
+
+DROP TABLE t1,t2;
+
+#
+# Bug #11302: getObject() returns a String for a sub-query of type datetime
+#
+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;
+
+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;
+
+DROP TABLE t1,t2,t3;
+
 # End of 4.1 tests
 
 #
@@ -2426,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 09576d415e5..3aa162b4700 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 6bf18b30c41..5e13909521b 100644
--- a/mysql-test/t/type_newdecimal.test
+++ b/mysql-test/t/type_newdecimal.test
@@ -1120,6 +1120,12 @@ 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#16172 DECIMAL data type processed incorrectly
 #
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.
 
 #