summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/r/cast.result8
-rw-r--r--mysql-test/r/grant.result19
-rw-r--r--mysql-test/r/innodb_mysql.result52
-rw-r--r--mysql-test/r/insert_update.result35
-rw-r--r--mysql-test/r/subselect3.result31
-rw-r--r--mysql-test/r/type_datetime.result4
-rw-r--r--mysql-test/r/type_newdecimal.result35
-rw-r--r--mysql-test/r/view.result15
-rw-r--r--mysql-test/t/grant.test25
-rw-r--r--mysql-test/t/innodb_mysql.test54
-rw-r--r--mysql-test/t/insert_update.test26
-rw-r--r--mysql-test/t/subselect3.test25
-rw-r--r--mysql-test/t/type_datetime.test2
-rw-r--r--mysql-test/t/type_newdecimal.test19
-rw-r--r--mysql-test/t/view.test12
15 files changed, 355 insertions, 7 deletions
diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result
index 454a3766572..6eceeff87e2 100644
--- a/mysql-test/r/cast.result
+++ b/mysql-test/r/cast.result
@@ -103,7 +103,7 @@ Warnings:
Warning 1292 Truncated incorrect DOUBLE value: 'a'
select 10.0+cast('a' as decimal);
10.0+cast('a' as decimal)
-10.00
+10.0
Warnings:
Warning 1292 Truncated incorrect DECIMAL value: 'a'
select 10E+0+'a';
@@ -378,7 +378,9 @@ create table t1(s1 time);
insert into t1 values ('11:11:11');
select cast(s1 as decimal(7,2)) from t1;
cast(s1 as decimal(7,2))
-111111.00
+99999.99
+Warnings:
+Error 1264 Out of range value adjusted for column 'cast(s1 as decimal(7,2))' at row 1
drop table t1;
CREATE TABLE t1 (v varchar(10), tt tinytext, t text,
mt mediumtext, lt longtext);
@@ -386,7 +388,7 @@ INSERT INTO t1 VALUES ('1.01', '2.02', '3.03', '4.04', '5.05');
SELECT CAST(v AS DECIMAL), CAST(tt AS DECIMAL), CAST(t AS DECIMAL),
CAST(mt AS DECIMAL), CAST(lt AS DECIMAL) from t1;
CAST(v AS DECIMAL) CAST(tt AS DECIMAL) CAST(t AS DECIMAL) CAST(mt AS DECIMAL) CAST(lt AS DECIMAL)
-1.01 2.02 3.03 4.04 5.05
+1 2 3 4 5
DROP TABLE t1;
select cast(NULL as decimal(6)) as t1;
t1
diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result
index 6d014fbb71b..a4c51cca277 100644
--- a/mysql-test/r/grant.result
+++ b/mysql-test/r/grant.result
@@ -1105,4 +1105,23 @@ ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table '
DROP DATABASE mysqltest1;
DROP DATABASE mysqltest2;
DROP USER mysqltest_1@localhost;
+use test;
+CREATE TABLE t1 (f1 int, f2 int);
+INSERT INTO t1 VALUES(1,1), (2,2);
+CREATE DATABASE db27878;
+GRANT UPDATE(f1) ON t1 TO 'mysqltest_1'@'localhost';
+GRANT SELECT ON `test`.* TO 'mysqltest_1'@'localhost';
+GRANT ALL ON db27878.* TO 'mysqltest_1'@'localhost';
+use db27878;
+CREATE SQL SECURITY INVOKER VIEW db27878.v1 AS SELECT * FROM test.t1;
+use db27878;
+UPDATE v1 SET f2 = 4;
+ERROR HY000: View 'db27878.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+SELECT * FROM test.t1;
+f1 f2
+1 1
+2 2
+DROP DATABASE db27878;
+use test;
+DROP TABLE t1;
End of 5.0 tests
diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index 009ae8776c3..48e01d8dd6f 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -544,4 +544,56 @@ id c counter
3 b 2
4 a 2
drop table t1;
+CREATE TABLE t1(
+id int AUTO_INCREMENT PRIMARY KEY,
+stat_id int NOT NULL,
+acct_id int DEFAULT NULL,
+INDEX idx1 (stat_id, acct_id),
+INDEX idx2 (acct_id)
+) ENGINE=MyISAM;
+CREATE TABLE t2(
+id int AUTO_INCREMENT PRIMARY KEY,
+stat_id int NOT NULL,
+acct_id int DEFAULT NULL,
+INDEX idx1 (stat_id, acct_id),
+INDEX idx2 (acct_id)
+) ENGINE=InnoDB;
+INSERT INTO t1(stat_id,acct_id) VALUES
+(1,759), (2,831), (3,785), (4,854), (1,921),
+(1,553), (2,589), (3,743), (2,827), (2,545),
+(4,779), (4,783), (1,597), (1,785), (4,832),
+(1,741), (1,833), (3,788), (2,973), (1,907);
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+UPDATE t1 SET acct_id=785
+WHERE MOD(stat_id,2)=0 AND MOD(id,stat_id)=MOD(acct_id,stat_id);
+OPTIMIZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 optimize status OK
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+40960
+SELECT COUNT(*) FROM t1 WHERE acct_id=785;
+COUNT(*)
+8702
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE stat_id IN (1,3) AND acct_id=785;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range idx1,idx2 idx1 9 NULL 2 Using where; Using index
+INSERT INTO t2 SELECT * FROM t1;
+OPTIMIZE TABLE t2;
+Table Op Msg_type Msg_text
+test.t2 optimize status OK
+EXPLAIN SELECT COUNT(*) FROM t2 WHERE stat_id IN (1,3) AND acct_id=785;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range idx1,idx2 idx1 9 NULL 2 Using where; Using index
+DROP TABLE t1,t2;
End of 5.0 tests
diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result
index 4a3e87d9d48..375961292a3 100644
--- a/mysql-test/r/insert_update.result
+++ b/mysql-test/r/insert_update.result
@@ -358,3 +358,38 @@ id c1 cnt
5 Y 1
6 Z 1
DROP TABLE t1;
+CREATE TABLE t1 (
+id INT AUTO_INCREMENT PRIMARY KEY,
+c1 INT NOT NULL,
+cnt INT DEFAULT 1
+);
+INSERT INTO t1 (id,c1) VALUES (1,10);
+SELECT * FROM t1;
+id c1 cnt
+1 10 1
+CREATE TABLE t2 (id INT, c1 INT);
+INSERT INTO t2 VALUES (1,NULL), (2,2);
+INSERT INTO t1 (id,c1) SELECT 1,NULL
+ON DUPLICATE KEY UPDATE c1=NULL;
+ERROR 23000: Column 'c1' cannot be null
+SELECT * FROM t1;
+id c1 cnt
+1 10 1
+INSERT IGNORE INTO t1 (id,c1) SELECT 1,NULL
+ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1;
+Warnings:
+Warning 1263 Column was set to data type implicit default; NULL supplied for NOT NULL column 'c1' at row 1
+Error 1048 Column 'c1' cannot be null
+SELECT * FROM t1;
+id c1 cnt
+1 0 2
+INSERT IGNORE INTO t1 (id,c1) SELECT * FROM t2
+ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1;
+Warnings:
+Warning 1263 Column was set to data type implicit default; NULL supplied for NOT NULL column 'c1' at row 1
+Error 1048 Column 'c1' cannot be null
+SELECT * FROM t1;
+id c1 cnt
+1 0 3
+2 2 1
+DROP TABLE t1;
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index f52249db8a1..9bbfdc6c5f9 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -711,3 +711,34 @@ a
1
4
DROP TABLE t1,t2;
+CREATE TABLE t1 (id int);
+CREATE TABLE t2 (id int PRIMARY KEY);
+CREATE TABLE t3 (id int PRIMARY KEY, name varchar(10));
+INSERT INTO t1 VALUES (2), (NULL), (3), (1);
+INSERT INTO t2 VALUES (234), (345), (457);
+INSERT INTO t3 VALUES (222,'bbb'), (333,'ccc'), (111,'aaa');
+EXPLAIN
+SELECT * FROM t1
+WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3
+WHERE t3.name='xxx' AND t2.id=t3.id);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where
+2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 func 1 Using where; Using index; Full scan on NULL key
+2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 func 1 Using where; Full scan on NULL key
+SELECT * FROM t1
+WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3
+WHERE t3.name='xxx' AND t2.id=t3.id);
+id
+2
+NULL
+3
+1
+SELECT (t1.id IN (SELECT t2.id FROM t2,t3
+WHERE t3.name='xxx' AND t2.id=t3.id)) AS x
+FROM t1;
+x
+0
+0
+0
+0
+DROP TABLE t1,t2,t3;
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 8e671597bca..b45ddd597c4 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -328,8 +328,8 @@ least(cast('01-01-01' as datetime), '01-01-02') + 0
select cast(least(cast('01-01-01' as datetime), '01-01-02') as signed);
cast(least(cast('01-01-01' as datetime), '01-01-02') as signed)
20010101000000
-select cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal);
-cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal)
+select cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal(20,2));
+cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal(20,2))
20010101000000.00
DROP PROCEDURE IF EXISTS test27759 ;
CREATE PROCEDURE test27759()
diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result
index c103de81bd7..cbcab126439 100644
--- a/mysql-test/r/type_newdecimal.result
+++ b/mysql-test/r/type_newdecimal.result
@@ -1430,4 +1430,39 @@ select * from t1;
a
123456789012345678
drop table t1;
+select cast(11.1234 as DECIMAL(3,2));
+cast(11.1234 as DECIMAL(3,2))
+9.99
+Warnings:
+Error 1264 Out of range value adjusted for column 'cast(11.1234 as DECIMAL(3,2))' at row 1
+select * from (select cast(11.1234 as DECIMAL(3,2))) t;
+cast(11.1234 as DECIMAL(3,2))
+9.99
+Warnings:
+Error 1264 Out of range value adjusted for column 'cast(11.1234 as DECIMAL(3,2))' at row 1
+select cast(a as DECIMAL(3,2))
+from (select 11.1233 as a
+UNION select 11.1234
+UNION select 12.1234
+) t;
+cast(a as DECIMAL(3,2))
+9.99
+9.99
+9.99
+Warnings:
+Error 1264 Out of range value adjusted for column 'cast(a as DECIMAL(3,2))' at row 1
+Error 1264 Out of range value adjusted for column 'cast(a as DECIMAL(3,2))' at row 1
+Error 1264 Out of range value adjusted for column 'cast(a as DECIMAL(3,2))' at row 1
+select cast(a as DECIMAL(3,2)), count(*)
+from (select 11.1233 as a
+UNION select 11.1234
+UNION select 12.1234
+) t group by 1;
+cast(a as DECIMAL(3,2)) count(*)
+9.99 3
+Warnings:
+Error 1264 Out of range value adjusted for column 'cast(a as DECIMAL(3,2))' at row 1
+Error 1264 Out of range value adjusted for column 'cast(a as DECIMAL(3,2))' at row 1
+Error 1264 Out of range value adjusted for column 'cast(a as DECIMAL(3,2))' at row 1
+Error 1264 Out of range value adjusted for column 'cast(a as DECIMAL(3,2))' at row 1
End of 5.0 tests
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 70dd6b2550f..8d9d802949d 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -1789,7 +1789,7 @@ drop table t1;
create view v1 as select cast(1 as decimal);
select * from v1;
cast(1 as decimal)
-1.00
+1
drop view v1;
create table t1(f1 int);
create table t2(f2 int);
@@ -3354,4 +3354,17 @@ id select_type table type possible_keys key key_len ref rows Extra
NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL Using filesort
DROP VIEW v1;
DROP TABLE t1;
+CREATE VIEW v1 AS SELECT CAST( 1.23456789 AS DECIMAL( 7,5 ) ) AS col;
+SELECT * FROM v1;
+col
+1.23457
+DESCRIBE v1;
+Field Type Null Key Default Extra
+col decimal(7,5) NO 0.00000
+DROP VIEW v1;
+CREATE VIEW v1 AS SELECT CAST(1.23456789 AS DECIMAL(8,0)) AS col;
+SHOW CREATE VIEW v1;
+View Create View
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(1.23456789 as decimal(8,0)) AS `col`
+DROP VIEW v1;
End of 5.0 tests.
diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test
index 197f20db76e..aa43e4225c5 100644
--- a/mysql-test/t/grant.test
+++ b/mysql-test/t/grant.test
@@ -413,6 +413,7 @@ connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK);
connection user1;
-- error 1142
alter table t1 rename t2;
+disconnect user1;
connection root;
revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;
delete from mysql.user where user=_binary'mysqltest_1';
@@ -1122,5 +1123,29 @@ DROP DATABASE mysqltest2;
DROP USER mysqltest_1@localhost;
+#
+# Bug#27878: Unchecked privileges on a view referring to a table from another
+# database.
+#
+use test;
+CREATE TABLE t1 (f1 int, f2 int);
+INSERT INTO t1 VALUES(1,1), (2,2);
+CREATE DATABASE db27878;
+GRANT UPDATE(f1) ON t1 TO 'mysqltest_1'@'localhost';
+GRANT SELECT ON `test`.* TO 'mysqltest_1'@'localhost';
+GRANT ALL ON db27878.* TO 'mysqltest_1'@'localhost';
+use db27878;
+CREATE SQL SECURITY INVOKER VIEW db27878.v1 AS SELECT * FROM test.t1;
+connect (user1,localhost,mysqltest_1,,test);
+connection user1;
+use db27878;
+--error 1356
+UPDATE v1 SET f2 = 4;
+SELECT * FROM test.t1;
+disconnect user1;
+connection default;
+DROP DATABASE db27878;
+use test;
+DROP TABLE t1;
--echo End of 5.0 tests
diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test
index c9e1de8c3ab..90f9047291c 100644
--- a/mysql-test/t/innodb_mysql.test
+++ b/mysql-test/t/innodb_mysql.test
@@ -518,4 +518,58 @@ select * from t1;
drop table t1;
+#
+# Bug #28189: optimizer erroniously prefers ref access to range access
+# for an InnoDB table
+#
+
+CREATE TABLE t1(
+ id int AUTO_INCREMENT PRIMARY KEY,
+ stat_id int NOT NULL,
+ acct_id int DEFAULT NULL,
+ INDEX idx1 (stat_id, acct_id),
+ INDEX idx2 (acct_id)
+) ENGINE=MyISAM;
+
+CREATE TABLE t2(
+ id int AUTO_INCREMENT PRIMARY KEY,
+ stat_id int NOT NULL,
+ acct_id int DEFAULT NULL,
+ INDEX idx1 (stat_id, acct_id),
+ INDEX idx2 (acct_id)
+) ENGINE=InnoDB;
+
+INSERT INTO t1(stat_id,acct_id) VALUES
+ (1,759), (2,831), (3,785), (4,854), (1,921),
+ (1,553), (2,589), (3,743), (2,827), (2,545),
+ (4,779), (4,783), (1,597), (1,785), (4,832),
+ (1,741), (1,833), (3,788), (2,973), (1,907);
+
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+UPDATE t1 SET acct_id=785
+ WHERE MOD(stat_id,2)=0 AND MOD(id,stat_id)=MOD(acct_id,stat_id);
+OPTIMIZE TABLE t1;
+
+SELECT COUNT(*) FROM t1;
+SELECT COUNT(*) FROM t1 WHERE acct_id=785;
+
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE stat_id IN (1,3) AND acct_id=785;
+
+INSERT INTO t2 SELECT * FROM t1;
+OPTIMIZE TABLE t2;
+
+EXPLAIN SELECT COUNT(*) FROM t2 WHERE stat_id IN (1,3) AND acct_id=785;
+
+DROP TABLE t1,t2;
+
--echo End of 5.0 tests
diff --git a/mysql-test/t/insert_update.test b/mysql-test/t/insert_update.test
index 0e199dab4bd..725fbdb25d7 100644
--- a/mysql-test/t/insert_update.test
+++ b/mysql-test/t/insert_update.test
@@ -264,3 +264,29 @@ INSERT INTO t1 (c1) VALUES ('A'), ('X'), ('Y'), ('Z')
ON DUPLICATE KEY UPDATE cnt=cnt+1;
SELECT * FROM t1;
DROP TABLE t1;
+
+#
+# Bug#28000: INSERT IGNORE ... SELECT ... ON DUPLICATE
+# with erroneous UPDATE: NOT NULL field with NULL value.
+#
+CREATE TABLE t1 (
+ id INT AUTO_INCREMENT PRIMARY KEY,
+ c1 INT NOT NULL,
+ cnt INT DEFAULT 1
+);
+INSERT INTO t1 (id,c1) VALUES (1,10);
+SELECT * FROM t1;
+CREATE TABLE t2 (id INT, c1 INT);
+INSERT INTO t2 VALUES (1,NULL), (2,2);
+--error 1048
+INSERT INTO t1 (id,c1) SELECT 1,NULL
+ ON DUPLICATE KEY UPDATE c1=NULL;
+SELECT * FROM t1;
+INSERT IGNORE INTO t1 (id,c1) SELECT 1,NULL
+ ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1;
+SELECT * FROM t1;
+INSERT IGNORE INTO t1 (id,c1) SELECT * FROM t2
+ ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1;
+SELECT * FROM t1;
+
+DROP TABLE t1;
diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test
index dfe09968fa2..65556012588 100644
--- a/mysql-test/t/subselect3.test
+++ b/mysql-test/t/subselect3.test
@@ -546,3 +546,28 @@ SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1));
SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1 WHERE a > 4));
DROP TABLE t1,t2;
+
+#
+# Bug #28375: crash for NOT IN subquery predicate when left operand becomes NULL
+#
+
+CREATE TABLE t1 (id int);
+CREATE TABLE t2 (id int PRIMARY KEY);
+CREATE TABLE t3 (id int PRIMARY KEY, name varchar(10));
+INSERT INTO t1 VALUES (2), (NULL), (3), (1);
+INSERT INTO t2 VALUES (234), (345), (457);
+INSERT INTO t3 VALUES (222,'bbb'), (333,'ccc'), (111,'aaa');
+
+EXPLAIN
+SELECT * FROM t1
+ WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3
+ WHERE t3.name='xxx' AND t2.id=t3.id);
+SELECT * FROM t1
+ WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3
+ WHERE t3.name='xxx' AND t2.id=t3.id);
+
+SELECT (t1.id IN (SELECT t2.id FROM t2,t3
+ WHERE t3.name='xxx' AND t2.id=t3.id)) AS x
+ FROM t1;
+
+DROP TABLE t1,t2,t3;
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index 5eb9d317a8c..c111f2f02cf 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -207,7 +207,7 @@ select least(cast('01-01-01' as date), '01-01-02') + 0;
select greatest(cast('01-01-01' as date), '01-01-02') + 0;
select least(cast('01-01-01' as datetime), '01-01-02') + 0;
select cast(least(cast('01-01-01' as datetime), '01-01-02') as signed);
-select cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal);
+select cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal(20,2));
--disable_warnings
DROP PROCEDURE IF EXISTS test27759 ;
--enable_warnings
diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test
index 4c6098d2121..a7906be79d4 100644
--- a/mysql-test/t/type_newdecimal.test
+++ b/mysql-test/t/type_newdecimal.test
@@ -1130,4 +1130,23 @@ alter table t1 modify column a decimal(19);
select * from t1;
drop table t1;
+#
+# Bug #27957 cast as decimal does not check overflow, also inconsistent with group, subselect
+#
+
+select cast(11.1234 as DECIMAL(3,2));
+select * from (select cast(11.1234 as DECIMAL(3,2))) t;
+
+select cast(a as DECIMAL(3,2))
+ from (select 11.1233 as a
+ UNION select 11.1234
+ UNION select 12.1234
+ ) t;
+
+select cast(a as DECIMAL(3,2)), count(*)
+ from (select 11.1233 as a
+ UNION select 11.1234
+ UNION select 12.1234
+ ) t group by 1;
+
--echo End of 5.0 tests
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index e5bf9de13eb..3275ba0a687 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -3221,4 +3221,16 @@ EXPLAIN SELECT * FROM t1 UNION SELECT * FROM v1 ORDER BY a;
DROP VIEW v1;
DROP TABLE t1;
+#
+# Bug #27921 View ignores precision for CAST()
+#
+CREATE VIEW v1 AS SELECT CAST( 1.23456789 AS DECIMAL( 7,5 ) ) AS col;
+SELECT * FROM v1;
+DESCRIBE v1;
+DROP VIEW v1;
+
+CREATE VIEW v1 AS SELECT CAST(1.23456789 AS DECIMAL(8,0)) AS col;
+SHOW CREATE VIEW v1;
+DROP VIEW v1;
+
--echo End of 5.0 tests.
View Lorry Controller Status