diff options
30 files changed, 466 insertions, 34 deletions
diff --git a/include/decimal.h b/include/decimal.h index 56962009025..c5385b58658 100644 --- a/include/decimal.h +++ b/include/decimal.h @@ -47,6 +47,7 @@ int decimal_bin_size(int precision, int scale); int decimal_result_size(decimal_t *from1, decimal_t *from2, char op, int param); +int decimal_intg(decimal_t *from); int decimal_add(decimal_t *from1, decimal_t *from2, decimal_t *to); int decimal_sub(decimal_t *from1, decimal_t *from2, decimal_t *to); int decimal_cmp(decimal_t *from1, decimal_t *from2); 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. diff --git a/sql/field.cc b/sql/field.cc index b2def4ca8d2..d2e72371bc1 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -8426,8 +8426,7 @@ bool create_field::init(THD *thd, char *fld_name, enum_field_types fld_type, case FIELD_TYPE_NULL: break; case FIELD_TYPE_NEWDECIMAL: - if (!fld_length && !decimals) - length= 10; + my_decimal_trim(&length, &decimals); if (length > DECIMAL_MAX_PRECISION) { my_error(ER_TOO_BIG_PRECISION, MYF(0), length, fld_name, diff --git a/sql/item_create.cc b/sql/item_create.cc index c1a81da0285..c4008d36aae 100644 --- a/sql/item_create.cc +++ b/sql/item_create.cc @@ -445,11 +445,13 @@ Item *create_load_file(Item* a) } -Item *create_func_cast(Item *a, Cast_target cast_type, int len, int dec, +Item *create_func_cast(Item *a, Cast_target cast_type, + const char *c_len, const char *c_dec, CHARSET_INFO *cs) { Item *res; - int tmp_len; + ulong len; + uint dec; LINT_INIT(res); switch (cast_type) { @@ -460,15 +462,18 @@ Item *create_func_cast(Item *a, Cast_target cast_type, int len, int dec, case ITEM_CAST_TIME: res= new Item_time_typecast(a); break; case ITEM_CAST_DATETIME: res= new Item_datetime_typecast(a); break; case ITEM_CAST_DECIMAL: - tmp_len= (len>0) ? len : 10; - if (tmp_len < dec) + len= c_len ? atoi(c_len) : 0; + dec= c_dec ? atoi(c_dec) : 0; + my_decimal_trim(&len, &dec); + if (len < dec) { my_error(ER_M_BIGGER_THAN_D, MYF(0), ""); return 0; } - res= new Item_decimal_typecast(a, tmp_len, dec ? dec : 2); + res= new Item_decimal_typecast(a, len, dec); break; case ITEM_CAST_CHAR: + len= c_len ? atoi(c_len) : -1; res= new Item_char_typecast(a, len, cs ? cs : current_thd->variables.collation_connection); break; @@ -476,6 +481,7 @@ Item *create_func_cast(Item *a, Cast_target cast_type, int len, int dec, return res; } + Item *create_func_is_free_lock(Item* a) { current_thd->lex->uncacheable(UNCACHEABLE_SIDEEFFECT); diff --git a/sql/item_create.h b/sql/item_create.h index 2ff849263c6..46b209b3e49 100644 --- a/sql/item_create.h +++ b/sql/item_create.h @@ -27,7 +27,8 @@ Item *create_func_bit_length(Item* a); Item *create_func_coercibility(Item* a); Item *create_func_ceiling(Item* a); Item *create_func_char_length(Item* a); -Item *create_func_cast(Item *a, Cast_target cast_type, int len, int dec, +Item *create_func_cast(Item *a, Cast_target cast_type, + const char *len, const char *dec, CHARSET_INFO *cs); Item *create_func_connection_id(void); Item *create_func_conv(Item* a, Item *b, Item *c); diff --git a/sql/item_func.cc b/sql/item_func.cc index 7a31b564502..c0a9647e382 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -1050,18 +1050,61 @@ longlong Item_decimal_typecast::val_int() my_decimal *Item_decimal_typecast::val_decimal(my_decimal *dec) { my_decimal tmp_buf, *tmp= args[0]->val_decimal(&tmp_buf); + bool sign; + uint precision; + if ((null_value= args[0]->null_value)) return NULL; my_decimal_round(E_DEC_FATAL_ERROR, tmp, decimals, FALSE, dec); + sign= dec->sign(); + if (unsigned_flag) + { + if (sign) + { + my_decimal_set_zero(dec); + goto err; + } + } + precision= my_decimal_length_to_precision(max_length, + decimals, unsigned_flag); + if (precision - decimals < (uint) my_decimal_intg(dec)) + { + max_my_decimal(dec, precision, decimals); + dec->sign(sign); + goto err; + } + return dec; + +err: + push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_ERROR, + ER_WARN_DATA_OUT_OF_RANGE, + ER(ER_WARN_DATA_OUT_OF_RANGE), + name, 1); return dec; } void Item_decimal_typecast::print(String *str) { + char len_buf[20*3 + 1]; + char *end; + + uint precision= my_decimal_length_to_precision(max_length, decimals, + unsigned_flag); str->append(STRING_WITH_LEN("cast(")); args[0]->print(str); - str->append(STRING_WITH_LEN(" as decimal)")); + str->append(STRING_WITH_LEN(" as decimal(")); + + end=int10_to_str(precision, len_buf,10); + str->append(len_buf, (uint32) (end - len_buf)); + + str->append(','); + + end=int10_to_str(decimals, len_buf,10); + str->append(len_buf, (uint32) (end - len_buf)); + + str->append(')'); + str->append(')'); } diff --git a/sql/item_func.h b/sql/item_func.h index 0443e394585..7ec3c0b00c4 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -331,8 +331,8 @@ class Item_decimal_typecast :public Item_func public: Item_decimal_typecast(Item *a, int len, int dec) :Item_func(a) { - max_length= len + 2; decimals= dec; + max_length= my_decimal_precision_to_length(len, dec, unsigned_flag); } String *val_str(String *str); double val_real(); diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index ccd361dba99..48b82e3cde6 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -1829,6 +1829,8 @@ int subselect_single_select_engine::exec() if (cond_guard && !*cond_guard) { /* Change the access method to full table scan */ + tab->save_read_first_record= tab->read_first_record; + tab->save_read_record= tab->read_record.read_record; tab->read_first_record= init_read_record_seq; tab->read_record.record= tab->table->record[0]; tab->read_record.thd= join->thd; @@ -1849,8 +1851,8 @@ int subselect_single_select_engine::exec() JOIN_TAB *tab= *ptab; tab->read_record.record= 0; tab->read_record.ref_length= 0; - tab->read_first_record= join_read_always_key_or_null; - tab->read_record.read_record= join_read_next_same_or_null; + tab->read_first_record= tab->save_read_first_record; + tab->read_record.read_record= tab->save_read_record; } executed= 1; thd->where= save_where; diff --git a/sql/my_decimal.h b/sql/my_decimal.h index 45270150d22..9558b00f0cf 100644 --- a/sql/my_decimal.h +++ b/sql/my_decimal.h @@ -387,5 +387,25 @@ int my_decimal_cmp(const my_decimal *a, const my_decimal *b) return decimal_cmp((decimal_t*) a, (decimal_t*) b); } + +inline +int my_decimal_intg(const my_decimal *a) +{ + return decimal_intg((decimal_t*) a); +} + + +inline +void my_decimal_trim(ulong *precision, uint *scale) +{ + if (!(*precision) && !(*scale)) + { + *precision= 10; + *scale= 0; + return; + } +} + + #endif /*my_decimal_h*/ diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index f1d86224adb..5f54bc2b43b 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -1258,7 +1258,8 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) DBUG_ASSERT(info->update_fields->elements == info->update_values->elements); if (fill_record_n_invoke_before_triggers(thd, *info->update_fields, - *info->update_values, 0, + *info->update_values, + info->ignore, table->triggers, TRG_EVENT_UPDATE)) goto before_trg_err; diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 1ec65743b0f..90361f8ff0d 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -1164,8 +1164,9 @@ static int mysql_test_update(Prepared_statement *stmt, goto error; #ifndef NO_EMBEDDED_ACCESS_CHECKS - /* TABLE_LIST contain right privilages request */ - want_privilege= table_list->grant.want_privilege; + /* Force privilege re-checking for views after they have been opened. */ + want_privilege= (table_list->view ? UPDATE_ACL : + table_list->grant.want_privilege); #endif if (mysql_prepare_update(thd, table_list, &select->where, diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 3a480c01ac1..967322600a7 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -4189,7 +4189,7 @@ best_access_path(JOIN *join, !(s->quick && best_key && s->quick->index == best_key->key && // (2) best_max_key_part >= s->table->quick_key_parts[best_key->key]) &&// (2) !((s->table->file->table_flags() & HA_TABLE_SCAN_ON_INDEX) && // (3) - ! s->table->used_keys.is_clear_all() && best_key) && // (3) + ! s->table->used_keys.is_clear_all() && best_key && !s->quick) &&// (3) !(s->table->force_index && best_key && !s->quick)) // (4) { // Check full join ha_rows rnd_records= s->found_records; diff --git a/sql/sql_select.h b/sql/sql_select.h index 5081366c10b..3cdd265df9a 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -159,6 +159,13 @@ typedef struct st_join_table { Read_record_func read_first_record; Next_select_func next_select; READ_RECORD read_record; + /* + Currently the following two fields are used only for a [NOT] IN subquery + if it is executed by an alternative full table scan when the left operand of + the subquery predicate is evaluated to NULL. + */ + Read_record_func save_read_first_record;/* to save read_first_record */ + int (*save_read_record) (READ_RECORD *);/* to save read_record.read_record */ double worst_seeks; key_map const_keys; /* Keys with constant part */ key_map checked_keys; /* Keys checked in find_best */ diff --git a/sql/sql_update.cc b/sql/sql_update.cc index e17c71ae541..222e33345cc 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -173,8 +173,9 @@ int mysql_update(THD *thd, table->quick_keys.clear_all(); #ifndef NO_EMBEDDED_ACCESS_CHECKS - /* TABLE_LIST contain right privilages request */ - want_privilege= table_list->grant.want_privilege; + /* Force privilege re-checking for views after they have been opened. */ + want_privilege= (table_list->view ? UPDATE_ACL : + table_list->grant.want_privilege); #endif if (mysql_prepare_update(thd, table_list, &conds, order_num, order)) DBUG_RETURN(1); diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 4a50a602121..0bb5f6a5e25 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -4699,15 +4699,12 @@ simple_expr: | ASCII_SYM '(' expr ')' { $$= new Item_func_ascii($3); } | BINARY simple_expr %prec NEG { - $$= create_func_cast($2, ITEM_CAST_CHAR, -1, 0, &my_charset_bin); + $$= create_func_cast($2, ITEM_CAST_CHAR, NULL, NULL, &my_charset_bin); } | CAST_SYM '(' expr AS cast_type ')' { LEX *lex= Lex; - $$= create_func_cast($3, $5, - lex->length ? atoi(lex->length) : -1, - lex->dec ? atoi(lex->dec) : 0, - lex->charset); + $$= create_func_cast($3, $5, lex->length, lex->dec, lex->charset); if (!$$) MYSQL_YYABORT; } @@ -4715,10 +4712,7 @@ simple_expr: { $$= new Item_func_case(* $3, $2, $4 ); } | CONVERT_SYM '(' expr ',' cast_type ')' { - $$= create_func_cast($3, $5, - Lex->length ? atoi(Lex->length) : -1, - Lex->dec ? atoi(Lex->dec) : 0, - Lex->charset); + $$= create_func_cast($3, $5, Lex->length, Lex->dec, Lex->charset); if (!$$) MYSQL_YYABORT; } diff --git a/strings/decimal.c b/strings/decimal.c index ea92174bfc8..65db68b1b59 100644 --- a/strings/decimal.c +++ b/strings/decimal.c @@ -1911,6 +1911,14 @@ static int do_sub(decimal_t *from1, decimal_t *from2, decimal_t *to) return error; } +int decimal_intg(decimal_t *from) +{ + int res; + dec1 *tmp_res; + tmp_res= remove_leading_zeroes(from, &res); + return res; +} + int decimal_add(decimal_t *from1, decimal_t *from2, decimal_t *to) { if (likely(from1->sign == from2->sign)) |