diff options
37 files changed, 591 insertions, 171 deletions
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index ab9e6762d21..53892b219eb 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -460,8 +460,8 @@ t2 CREATE TABLE `t2` ( `ifnull(c,c)` mediumint(8) DEFAULT NULL, `ifnull(d,d)` int(11) DEFAULT NULL, `ifnull(e,e)` bigint(20) DEFAULT NULL, - `ifnull(f,f)` float(24,2) DEFAULT NULL, - `ifnull(g,g)` double(53,3) DEFAULT NULL, + `ifnull(f,f)` float(3,2) DEFAULT NULL, + `ifnull(g,g)` double(4,3) DEFAULT NULL, `ifnull(h,h)` decimal(5,4) DEFAULT NULL, `ifnull(i,i)` year(4) DEFAULT NULL, `ifnull(j,j)` date DEFAULT NULL, diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index 7a8f59c65f4..992d1cd418b 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -477,6 +477,11 @@ a Warnings: Warning 1292 Incorrect date value: '19772-07-29' for column 'a' at row 1 DROP TABLE t1,t2,t3,t4; +CREATE TABLE t1 (id int not null); +INSERT INTO t1 VALUES (1),(2); +SELECT id FROM t1 WHERE id IN(4564, (SELECT IF(1=0,1,1/0)) ); +id +DROP TABLE t1; End of 5.0 tests create table t1(f1 char(1)); insert into t1 values ('a'),('b'),('1'); diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result index 18a0ed1a1cb..57339b2e29f 100644 --- a/mysql-test/r/insert_select.result +++ b/mysql-test/r/insert_select.result @@ -730,3 +730,32 @@ f1 f2 2 2 10 10 DROP TABLE t1, t2; +SET SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; +CREATE TABLE t1 (c VARCHAR(30), INDEX ix_c (c(10))); +CREATE TABLE t2 (d VARCHAR(10)); +INSERT INTO t1 (c) VALUES ('7_chars'), ('13_characters'); +EXPLAIN +SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ref ix_c ix_c 13 const 1 Using where +SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1; +(SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') +13 +13 +INSERT INTO t2 (d) +SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1; +INSERT INTO t2 (d) +SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='7_chars') FROM t1; +INSERT INTO t2 (d) +SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c IN (SELECT t1.c FROM t1)) +FROM t1; +SELECT * FROM t2; +d +13 +13 +7 +7 +20 +20 +DROP TABLE t1,t2; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 1372ac3687c..8bc9350bd6c 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -3712,12 +3712,6 @@ bb 2 cc 3 dd 1 DROP TABLE t1,t2,t3; -CREATE TABLE t1 (s1 char(1)); -INSERT INTO t1 VALUES ('a'); -SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1); -s1 -a -DROP TABLE t1; CREATE TABLE t1(f1 int); CREATE TABLE t2(f2 int, f21 int, f3 timestamp); INSERT INTO t1 VALUES (1),(1),(2),(2); @@ -3886,3 +3880,34 @@ this is a test. 3 this is a test. 1 this is a test. 2 DROP table t1; +CREATE TABLE t1 (a int, b int); +CREATE TABLE t2 (m int, n int); +INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4); +INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44); +SELECT COUNT(*), a, +(SELECT m FROM t2 WHERE m = count(*) LIMIT 1) +FROM t1 GROUP BY a; +COUNT(*) a (SELECT m FROM t2 WHERE m = count(*) LIMIT 1) +2 2 2 +3 3 3 +1 4 1 +SELECT COUNT(*), a, +(SELECT MIN(m) FROM t2 WHERE m = count(*)) +FROM t1 GROUP BY a; +COUNT(*) a (SELECT MIN(m) FROM t2 WHERE m = count(*)) +2 2 2 +3 3 3 +1 4 1 +SELECT COUNT(*), a +FROM t1 GROUP BY a +HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1; +COUNT(*) a +2 2 +3 3 +DROP TABLE t1,t2; +CREATE TABLE t1 (s1 char(1)); +INSERT INTO t1 VALUES ('a'); +SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1); +s1 +a +DROP TABLE t1; diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 03c35d51045..2727a18c8d8 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -645,3 +645,56 @@ a b Z 2 2 0 3 3 1 drop table t1,t2; +CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b)); +INSERT INTO t1 VALUES (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'), +(2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'),(3,3,'j'), (3,2,'k'), (3,1,'l'), +(1,9,'m'); +CREATE TABLE t2 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b)); +INSERT INTO t2 SELECT * FROM t1; +SELECT a, MAX(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) +as test FROM t1 GROUP BY a; +a MAX(b) test +1 9 m +2 3 h +3 4 i +SELECT * FROM t1 GROUP by t1.a +HAVING (MAX(t1.b) > (SELECT MAX(t2.b) FROM t2 WHERE t2.c < t1.c +HAVING MAX(t2.b+t1.a) < 10)); +a b c +SELECT a, AVG(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=AVG(t1.b)) +AS test FROM t1 GROUP BY a; +a AVG(b) test +1 4.0000 NULL +2 2.0000 k +3 2.5000 NULL +SELECT a,b,c FROM t1 WHERE b in (9,3,4) ORDER BY b,c; +a b c +1 3 c +2 3 h +3 3 j +1 4 d +3 4 i +1 9 m +SELECT a, MAX(b), +(SELECT COUNT(DISTINCT t.c) FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) +LIMIT 1) +as cnt, +(SELECT t.b FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) +as t_b, +(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) +as t_b, +(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) ORDER BY t.c LIMIT 1) +as t_b +FROM t1 GROUP BY a; +a MAX(b) cnt t_b t_b t_b +1 9 1 9 m m +2 3 1 3 h h +3 4 1 4 i i +SELECT a, MAX(b), +(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) as test +FROM t1 GROUP BY a; +a MAX(b) test +1 9 m +2 3 h +3 4 i +DROP TABLE t1, t2; diff --git a/mysql-test/r/temp_table.result b/mysql-test/r/temp_table.result index 6c9a389c1f4..9cb4a6b2427 100644 --- a/mysql-test/r/temp_table.result +++ b/mysql-test/r/temp_table.result @@ -152,6 +152,27 @@ SELECT * FROM t1; i DROP TABLE t1; End of 4.1 tests. +CREATE TABLE t1 ( c FLOAT( 20, 14 ) ); +INSERT INTO t1 VALUES( 12139 ); +CREATE TABLE t2 ( c FLOAT(30,18) ); +INSERT INTO t2 VALUES( 123456 ); +SELECT AVG( c ) FROM t1 UNION SELECT 1; +AVG( c ) +12139 +1 +SELECT 1 UNION SELECT AVG( c ) FROM t1; +1 +1 +12139 +SELECT 1 UNION SELECT * FROM t2 UNION SELECT 1; +1 +1 +123456 +SELECT c/1 FROM t1 UNION SELECT 1; +c/1 +12139 +1 +DROP TABLE t1, t2; create temporary table t1 (a int); insert into t1 values (4711); select * from t1; diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index a8d5388097d..b54bd155c7d 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -168,6 +168,9 @@ dt 0000-00-00 00:00:00 0000-00-00 00:00:00 drop table t1; +select cast('2006-12-05 22:10:10' as datetime) + 0; +cast('2006-12-05 22:10:10' as datetime) + 0 +20061205221010.000000 CREATE TABLE t1(a DATETIME NOT NULL); INSERT INTO t1 VALUES ('20060606155555'); SELECT a FROM t1 WHERE a=(SELECT MAX(a) FROM t1) AND (a="20060606155555"); diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result index dbe60aff3d9..ac1270d33a0 100644 --- a/mysql-test/r/type_float.result +++ b/mysql-test/r/type_float.result @@ -92,7 +92,7 @@ show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `col1` double DEFAULT NULL, - `col2` double(53,5) DEFAULT NULL, + `col2` double(22,5) DEFAULT NULL, `col3` double DEFAULT NULL, `col4` double DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 @@ -232,12 +232,12 @@ insert into t2 values ("1.23456780"); create table t3 select * from t2 union select * from t1; select * from t3; d -1.234567800 -100000000.000000000 +1.2345678 +100000000 show create table t3; Table Create Table t3 CREATE TABLE `t3` ( - `d` double(22,9) DEFAULT NULL + `d` double DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2, t3; create table t1 select 105213674794682365.00 + 0.0 x; diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 2c33ffc08d7..275f3357c65 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -554,7 +554,7 @@ aa show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` varbinary(20) NOT NULL DEFAULT '' + `a` varbinary(2) NOT NULL DEFAULT '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT 12 as a UNION select 12.2 as a; @@ -655,7 +655,7 @@ f show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `f` varbinary(24) DEFAULT NULL + `f` varbinary(12) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT y from t2 UNION select da from t2; @@ -1437,4 +1437,12 @@ a SELECT a FROM (SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY c) AS test; ERROR 42S22: Unknown column 'c' in 'order clause' DROP TABLE t1; +(select 1 into @var) union (select 1); +ERROR HY000: Incorrect usage of UNION and INTO +(select 1) union (select 1 into @var); +select @var; +@var +1 +(select 2) union (select 1 into @var); +ERROR 42000: Result consisted of more than one row End of 5.0 tests diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 46b09b55565..0fa7cda1187 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -3321,38 +3321,4 @@ DROP TABLE `t-2`; DROP VIEW `v-2`; DROP DATABASE `d-1`; USE test; -DROP VIEW IF EXISTS v1; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 (i INT); -CREATE VIEW v1 AS SELECT * FROM t1; -ALTER VIEW v1 AS SELECT * FROM t1; -SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i` from `t1` -ALTER DEFINER=no_such@user_1 VIEW v1 AS SELECT * FROM t1; -Warnings: -Note 1449 There is no 'no_such'@'user_1' registered -SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`no_such`@`user_1` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i` from `t1` -Warnings: -Note 1449 There is no 'no_such'@'user_1' registered -ALTER ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; -Warnings: -Note 1449 There is no 'no_such'@'user_1' registered -SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=MERGE DEFINER=`no_such`@`user_1` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i` from `t1` -Warnings: -Note 1449 There is no 'no_such'@'user_1' registered -ALTER ALGORITHM=TEMPTABLE DEFINER=no_such@user_2 VIEW v1 AS SELECT * FROM t1; -Warnings: -Note 1449 There is no 'no_such'@'user_2' registered -SHOW CREATE VIEW v1; -View Create View -v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`no_such`@`user_2` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i` from `t1` -Warnings: -Note 1449 There is no 'no_such'@'user_2' registered -DROP VIEW v1; -DROP TABLE t1; End of 5.1 tests. diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result index a95184b8576..2a0bedc1443 100644 --- a/mysql-test/r/view_grant.result +++ b/mysql-test/r/view_grant.result @@ -775,4 +775,58 @@ DROP DATABASE mysqltest_db1; DROP DATABASE mysqltest_db2; DROP USER mysqltest_u1@localhost; DROP USER mysqltest_u2@localhost; +CREATE DATABASE db26813; +USE db26813; +CREATE TABLE t1(f1 INT, f2 INT); +CREATE VIEW v1 AS SELECT f1 FROM t1; +CREATE VIEW v2 AS SELECT f1 FROM t1; +CREATE VIEW v3 AS SELECT f1 FROM t1; +CREATE USER u26813@localhost; +GRANT DROP ON db26813.v1 TO u26813@localhost; +GRANT CREATE VIEW ON db26813.v2 TO u26813@localhost; +GRANT DROP, CREATE VIEW ON db26813.v3 TO u26813@localhost; +GRANT SELECT ON db26813.t1 TO u26813@localhost; +ALTER VIEW v1 AS SELECT f2 FROM t1; +ERROR 42000: CREATE VIEW command denied to user 'u26813'@'localhost' for table 'v1' +ALTER VIEW v2 AS SELECT f2 FROM t1; +ERROR 42000: DROP command denied to user 'u26813'@'localhost' for table 'v2' +ALTER VIEW v3 AS SELECT f2 FROM t1; +SHOW CREATE VIEW v3; +View Create View +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`f2` AS `f2` from `t1` +DROP USER u26813@localhost; +DROP DATABASE db26813; End of 5.0 tests. +DROP VIEW IF EXISTS v1; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (i INT); +CREATE VIEW v1 AS SELECT * FROM t1; +ALTER VIEW v1 AS SELECT * FROM t1; +SHOW CREATE VIEW v1; +View Create View +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i` from `t1` +ALTER DEFINER=no_such@user_1 VIEW v1 AS SELECT * FROM t1; +Warnings: +Note 1449 There is no 'no_such'@'user_1' registered +SHOW CREATE VIEW v1; +View Create View +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`no_such`@`user_1` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i` from `t1` +Warnings: +Note 1449 There is no 'no_such'@'user_1' registered +ALTER ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; +SHOW CREATE VIEW v1; +View Create View +v1 CREATE ALGORITHM=MERGE DEFINER=`no_such`@`user_1` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i` from `t1` +Warnings: +Note 1449 There is no 'no_such'@'user_1' registered +ALTER ALGORITHM=TEMPTABLE DEFINER=no_such@user_2 VIEW v1 AS SELECT * FROM t1; +Warnings: +Note 1449 There is no 'no_such'@'user_2' registered +SHOW CREATE VIEW v1; +View Create View +v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`no_such`@`user_2` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i` from `t1` +Warnings: +Note 1449 There is no 'no_such'@'user_2' registered +DROP VIEW v1; +DROP TABLE t1; +End of 5.1 tests. diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index 31352e4c639..e9583f982ff 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -361,6 +361,17 @@ SELECT * FROM t4 WHERE a IN ('1972-02-06','19772-07-29'); DROP TABLE t1,t2,t3,t4; +# +# BUG#27362: IN with a decimal expression that may return NULL +# + +CREATE TABLE t1 (id int not null); +INSERT INTO t1 VALUES (1),(2); + +SELECT id FROM t1 WHERE id IN(4564, (SELECT IF(1=0,1,1/0)) ); + +DROP TABLE t1; + --echo End of 5.0 tests diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index 7ae6e3adda7..9cf42070f2f 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -1,5 +1,6 @@ -- source include/have_geometry.inc + # # Spatial objects # diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index 0f9a0ca4872..95a55b732c5 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -293,4 +293,29 @@ INSERT INTO t2 (f1, f2) SELECT * FROM t2; DROP TABLE t1, t2; - +# +# Bug #26207: inserts don't work with shortened index +# +SET SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; + +CREATE TABLE t1 (c VARCHAR(30), INDEX ix_c (c(10))); +CREATE TABLE t2 (d VARCHAR(10)); +INSERT INTO t1 (c) VALUES ('7_chars'), ('13_characters'); + +EXPLAIN + SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1; + +SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1; + +INSERT INTO t2 (d) + SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1; + +INSERT INTO t2 (d) + SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='7_chars') FROM t1; + +INSERT INTO t2 (d) + SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c IN (SELECT t1.c FROM t1)) + FROM t1; + +SELECT * FROM t2; +DROP TABLE t1,t2; diff --git a/mysql-test/t/query_cache_sql_prepare.test b/mysql-test/t/query_cache_sql_prepare.test index 69b504e2fd1..98141153002 100644 --- a/mysql-test/t/query_cache_sql_prepare.test +++ b/mysql-test/t/query_cache_sql_prepare.test @@ -5,7 +5,7 @@ -- source include/have_query_cache.inc -connect (con1,127.0.0.1,root,,test,$MASTER_MYPORT,); +connect (con1,localhost,root,,test,$MASTER_MYPORT,); connection default; set global query_cache_size=100000; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 9c2248cbcc2..0f42b3e06a5 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -2602,13 +2602,6 @@ SELECT * FROM t1 DROP TABLE t1,t2,t3; -# -# Bug#20835 (literal string with =any values) -# -CREATE TABLE t1 (s1 char(1)); -INSERT INTO t1 VALUES ('a'); -SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1); -DROP TABLE t1; # # Bug#23800: Outer fields in correlated subqueries is used in a temporary # table created for sorting. @@ -2748,3 +2741,33 @@ SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t; DROP table t1; +# +# Bug #27257: COUNT(*) aggregated in outer query +# + +CREATE TABLE t1 (a int, b int); +CREATE TABLE t2 (m int, n int); +INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4); +INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44); + +SELECT COUNT(*), a, + (SELECT m FROM t2 WHERE m = count(*) LIMIT 1) + FROM t1 GROUP BY a; + +SELECT COUNT(*), a, + (SELECT MIN(m) FROM t2 WHERE m = count(*)) + FROM t1 GROUP BY a; + +SELECT COUNT(*), a + FROM t1 GROUP BY a + HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1; + +DROP TABLE t1,t2; + +# +# Bug#20835 (literal string with =any values) +# +CREATE TABLE t1 (s1 char(1)); +INSERT INTO t1 VALUES ('a'); +SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1); +DROP TABLE t1; diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test index ed8480ba464..e3703c0da16 100644 --- a/mysql-test/t/subselect3.test +++ b/mysql-test/t/subselect3.test @@ -489,3 +489,44 @@ select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1; drop table t1,t2; +# +# Bug #24484: Aggregate function used in column list subquery gives erroneous +# error +# +CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b)); +INSERT INTO t1 VALUES (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'), + (2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'),(3,3,'j'), (3,2,'k'), (3,1,'l'), + (1,9,'m'); +CREATE TABLE t2 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b)); +INSERT INTO t2 SELECT * FROM t1; + +# Gives error, but should work since it is (a, b) is the PK so only one +# given match possible +SELECT a, MAX(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) + as test FROM t1 GROUP BY a; +SELECT * FROM t1 GROUP by t1.a + HAVING (MAX(t1.b) > (SELECT MAX(t2.b) FROM t2 WHERE t2.c < t1.c + HAVING MAX(t2.b+t1.a) < 10)); +SELECT a, AVG(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=AVG(t1.b)) + AS test FROM t1 GROUP BY a; + +SELECT a,b,c FROM t1 WHERE b in (9,3,4) ORDER BY b,c; + +SELECT a, MAX(b), + (SELECT COUNT(DISTINCT t.c) FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) + LIMIT 1) + as cnt, + (SELECT t.b FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) + as t_b, + (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) + as t_b, + (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) ORDER BY t.c LIMIT 1) + as t_b + FROM t1 GROUP BY a; + +SELECT a, MAX(b), + (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) as test + FROM t1 GROUP BY a; + + +DROP TABLE t1, t2; diff --git a/mysql-test/t/temp_table.test b/mysql-test/t/temp_table.test index d06fde87b34..e10fd386bd3 100644 --- a/mysql-test/t/temp_table.test +++ b/mysql-test/t/temp_table.test @@ -164,6 +164,21 @@ DROP TABLE t1; --echo End of 4.1 tests. +# +# Bug #24791: Union with AVG-groups generates wrong results +# +CREATE TABLE t1 ( c FLOAT( 20, 14 ) ); +INSERT INTO t1 VALUES( 12139 ); + +CREATE TABLE t2 ( c FLOAT(30,18) ); +INSERT INTO t2 VALUES( 123456 ); + +SELECT AVG( c ) FROM t1 UNION SELECT 1; +SELECT 1 UNION SELECT AVG( c ) FROM t1; +SELECT 1 UNION SELECT * FROM t2 UNION SELECT 1; +SELECT c/1 FROM t1 UNION SELECT 1; + +DROP TABLE t1, t2; # # Test truncate with temporary tables diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test index 3aa162b4700..9246080630e 100644 --- a/mysql-test/t/type_datetime.test +++ b/mysql-test/t/type_datetime.test @@ -113,6 +113,12 @@ insert into t1 values ("00-00-00"), ("00-00-00 00:00:00"); select * from t1; drop table t1; +# +# Bug #16546 DATETIME+0 not always coerced the same way +# +select cast('2006-12-05 22:10:10' as datetime) + 0; + + # End of 4.1 tests # diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index cc93fbd715a..a57ab469ac2 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -913,4 +913,13 @@ SELECT a FROM (SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY c) AS test; DROP TABLE t1; +# +# Bug#23345: Wrongly allowed INTO in a non-last select of a UNION. +# +--error 1221 +(select 1 into @var) union (select 1); +(select 1) union (select 1 into @var); +select @var; +--error 1172 +(select 2) union (select 1 into @var); --echo End of 5.0 tests diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 026a08bf365..4961d95d0ac 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -3216,29 +3216,4 @@ DROP VIEW `v-2`; DROP DATABASE `d-1`; USE test; - -# -# Test that ALTER VIEW accepts DEFINER and ALGORITHM, see bug#16425. -# ---disable_warnings -DROP VIEW IF EXISTS v1; -DROP TABLE IF EXISTS t1; ---enable_warnings - -CREATE TABLE t1 (i INT); -CREATE VIEW v1 AS SELECT * FROM t1; - -ALTER VIEW v1 AS SELECT * FROM t1; -SHOW CREATE VIEW v1; -ALTER DEFINER=no_such@user_1 VIEW v1 AS SELECT * FROM t1; -SHOW CREATE VIEW v1; -ALTER ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; -SHOW CREATE VIEW v1; -ALTER ALGORITHM=TEMPTABLE DEFINER=no_such@user_2 VIEW v1 AS SELECT * FROM t1; -SHOW CREATE VIEW v1; - -DROP VIEW v1; -DROP TABLE t1; - - --echo End of 5.1 tests. diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index a4a1cefd0aa..64cafe89d10 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -1040,5 +1040,63 @@ DROP DATABASE mysqltest_db2; DROP USER mysqltest_u1@localhost; DROP USER mysqltest_u2@localhost; +# +# Bug#26813: The SUPER privilege is wrongly required to alter a view created +# by another user. +# +connection root; +CREATE DATABASE db26813; +USE db26813; +CREATE TABLE t1(f1 INT, f2 INT); +CREATE VIEW v1 AS SELECT f1 FROM t1; +CREATE VIEW v2 AS SELECT f1 FROM t1; +CREATE VIEW v3 AS SELECT f1 FROM t1; +CREATE USER u26813@localhost; +GRANT DROP ON db26813.v1 TO u26813@localhost; +GRANT CREATE VIEW ON db26813.v2 TO u26813@localhost; +GRANT DROP, CREATE VIEW ON db26813.v3 TO u26813@localhost; +GRANT SELECT ON db26813.t1 TO u26813@localhost; + +connect (u1,localhost,u26813,,db26813); +connection u1; +--error 1142 +ALTER VIEW v1 AS SELECT f2 FROM t1; +--error 1142 +ALTER VIEW v2 AS SELECT f2 FROM t1; +ALTER VIEW v3 AS SELECT f2 FROM t1; + +connection root; +SHOW CREATE VIEW v3; + +DROP USER u26813@localhost; +DROP DATABASE db26813; +disconnect u1; --echo End of 5.0 tests. + + +# +# Test that ALTER VIEW accepts DEFINER and ALGORITHM, see bug#16425. +# +connection default; +--disable_warnings +DROP VIEW IF EXISTS v1; +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (i INT); +CREATE VIEW v1 AS SELECT * FROM t1; + +ALTER VIEW v1 AS SELECT * FROM t1; +SHOW CREATE VIEW v1; +ALTER DEFINER=no_such@user_1 VIEW v1 AS SELECT * FROM t1; +SHOW CREATE VIEW v1; +ALTER ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; +SHOW CREATE VIEW v1; +ALTER ALGORITHM=TEMPTABLE DEFINER=no_such@user_2 VIEW v1 AS SELECT * FROM t1; +SHOW CREATE VIEW v1; + +DROP VIEW v1; +DROP TABLE t1; + +--echo End of 5.1 tests. diff --git a/sql/field.h b/sql/field.h index f27ed8b9394..b96be208e41 100644 --- a/sql/field.h +++ b/sql/field.h @@ -504,6 +504,7 @@ public: {} int store_decimal(const my_decimal *); my_decimal *val_decimal(my_decimal *); + uint32 max_display_length() { return field_length; } }; @@ -532,7 +533,6 @@ public: void overflow(bool negative); bool zero_pack() const { return 0; } void sql_type(String &str) const; - uint32 max_display_length() { return field_length; } }; @@ -783,7 +783,6 @@ public: void sort_string(char *buff,uint length); uint32 pack_length() const { return sizeof(float); } void sql_type(String &str) const; - uint32 max_display_length() { return 24; } }; @@ -825,7 +824,6 @@ public: void sort_string(char *buff,uint length); uint32 pack_length() const { return sizeof(double); } void sql_type(String &str) const; - uint32 max_display_length() { return 53; } uint size_of() const { return sizeof(*this); } }; diff --git a/sql/item.cc b/sql/item.cc index 0c03bd14d5f..c7935607130 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -6437,8 +6437,6 @@ Item_type_holder::Item_type_holder(THD *thd, Item *item) :Item(thd, item), enum_set_typelib(0), fld_type(get_real_type(item)) { DBUG_ASSERT(item->fixed); - - max_length= display_length(item); maybe_null= item->maybe_null; collation.set(item->collation); get_full_info(item); @@ -6610,11 +6608,17 @@ bool Item_type_holder::join_types(THD *thd, Item *item) { int delta1= max_length_orig - decimals_orig; int delta2= item->max_length - item->decimals; - if (fld_type == MYSQL_TYPE_DECIMAL) - max_length= max(delta1, delta2) + decimals; - else - max_length= min(max(delta1, delta2) + decimals, - (fld_type == MYSQL_TYPE_FLOAT) ? FLT_DIG+6 : DBL_DIG+7); + max_length= max(delta1, delta2) + decimals; + if (fld_type == MYSQL_TYPE_FLOAT && max_length > FLT_DIG + 2) + { + max_length= FLT_DIG + 6; + decimals= NOT_FIXED_DEC; + } + if (fld_type == MYSQL_TYPE_DOUBLE && max_length > DBL_DIG + 2) + { + max_length= DBL_DIG + 7; + decimals= NOT_FIXED_DEC; + } } else max_length= (fld_type == MYSQL_TYPE_FLOAT) ? FLT_DIG+6 : DBL_DIG+7; diff --git a/sql/item.h b/sql/item.h index dce03299929..87380b53bfb 100644 --- a/sql/item.h +++ b/sql/item.h @@ -2004,6 +2004,11 @@ public: { return depended_from ? OUTER_REF_TABLE_BIT : (*ref)->used_tables(); } + void update_used_tables() + { + if (!depended_from) + (*ref)->update_used_tables(); + } table_map not_null_tables() const { return (*ref)->not_null_tables(); } void set_result_field(Field *field) { result_field= field; } bool is_result_field() { return 1; } diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index ae66f292d0b..ffa6b4caf2a 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -2458,7 +2458,8 @@ void in_decimal::set(uint pos, Item *item) dec->len= DECIMAL_BUFF_LENGTH; dec->fix_buffer_pointer(); my_decimal *res= item->val_decimal(dec); - if (res != dec) + /* if item->val_decimal() is evaluated to NULL then res == 0 */ + if (!item->null_value && res != dec) my_decimal2decimal(res, dec); } diff --git a/sql/item_sum.cc b/sql/item_sum.cc index f34fc008186..cd67f83af17 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -61,6 +61,7 @@ bool Item_sum::init_sum_func_check(THD *thd) /* Save a pointer to object to be used in items for nested set functions */ thd->lex->in_sum_func= this; nest_level= thd->lex->current_select->nest_level; + nest_level_tables_count= thd->lex->current_select->join->tables; ref_by= 0; aggr_level= -1; max_arg_level= -1; @@ -176,6 +177,7 @@ bool Item_sum::check_sum_func(THD *thd, Item **ref) */ set_if_bigger(in_sum_func->max_sum_func_level, aggr_level); } + update_used_tables(); thd->lex->in_sum_func= in_sum_func; return FALSE; } @@ -267,12 +269,13 @@ bool Item_sum::register_sum_func(THD *thd, Item **ref) sl= sl->master_unit()->outer_select() ) sl->master_unit()->item->with_sum_func= 1; } + thd->lex->current_select->mark_as_dependent(aggr_sl); return FALSE; } -Item_sum::Item_sum(List<Item> &list) - :arg_count(list.elements) +Item_sum::Item_sum(List<Item> &list) :arg_count(list.elements), + forced_const(FALSE) { if ((args=(Item**) sql_alloc(sizeof(Item*)*arg_count))) { @@ -296,7 +299,10 @@ Item_sum::Item_sum(List<Item> &list) Item_sum::Item_sum(THD *thd, Item_sum *item): Item_result_field(thd, item), arg_count(item->arg_count), - quick_group(item->quick_group) + nest_level(item->nest_level), aggr_level(item->aggr_level), + quick_group(item->quick_group), used_tables_cache(item->used_tables_cache), + forced_const(item->forced_const), + nest_level_tables_count(item->nest_level_tables_count) { if (arg_count <= 2) args=tmp_args; @@ -429,6 +435,26 @@ Field *Item_sum::create_tmp_field(bool group, TABLE *table, } +void Item_sum::update_used_tables () +{ + if (!forced_const) + { + used_tables_cache= 0; + for (uint i=0 ; i < arg_count ; i++) + { + args[i]->update_used_tables(); + used_tables_cache|= args[i]->used_tables(); + } + + used_tables_cache&= PSEUDO_TABLE_BITS; + + /* the aggregate function is aggregated into its local context */ + if (aggr_level == nest_level) + used_tables_cache |= (1 << nest_level_tables_count) - 1; + } +} + + String * Item_sum_num::val_str(String *str) { @@ -488,7 +514,7 @@ Item_sum_num::fix_fields(THD *thd, Item **ref) Item_sum_hybrid::Item_sum_hybrid(THD *thd, Item_sum_hybrid *item) :Item_sum(thd, item), value(item->value), hybrid_type(item->hybrid_type), hybrid_field_type(item->hybrid_field_type), cmp_sign(item->cmp_sign), - used_table_cache(item->used_table_cache), was_values(item->was_values) + was_values(item->was_values) { /* copy results from old value */ switch (hybrid_type) { @@ -1082,7 +1108,6 @@ void Item_sum_count::cleanup() DBUG_ENTER("Item_sum_count::cleanup"); count= 0; Item_sum_int::cleanup(); - used_table_cache= ~(table_map) 0; DBUG_VOID_RETURN; } @@ -1105,8 +1130,10 @@ void Item_sum_avg::fix_length_and_dec() f_scale= args[0]->decimals; dec_bin_size= my_decimal_get_binary_size(f_precision, f_scale); } - else + else { decimals= min(args[0]->decimals + prec_increment, NOT_FIXED_DEC); + max_length= args[0]->max_length + prec_increment; + } } @@ -1572,7 +1599,7 @@ void Item_sum_hybrid::cleanup() { DBUG_ENTER("Item_sum_hybrid::cleanup"); Item_sum::cleanup(); - used_table_cache= ~(table_map) 0; + forced_const= FALSE; /* by default it is TRUE to avoid TRUE reporting by diff --git a/sql/item_sum.h b/sql/item_sum.h index 4cf16fc79af..5756402c1b1 100644 --- a/sql/item_sum.h +++ b/sql/item_sum.h @@ -215,7 +215,9 @@ TODO: to catch queries where the limit is exceeded to make the code clean here. -*/ +*/ + +class st_select_lex; class Item_sum :public Item_result_field { @@ -237,19 +239,26 @@ public: int8 max_sum_func_level;/* max level of aggregation for embedded functions */ bool quick_group; /* If incremental update of fields */ +protected: + table_map used_tables_cache; + bool forced_const; + byte nest_level_tables_count; + +public: + void mark_as_sum_func(); - Item_sum() :arg_count(0), quick_group(1) + Item_sum() :arg_count(0), quick_group(1), forced_const(FALSE) { mark_as_sum_func(); } - Item_sum(Item *a) - :args(tmp_args), arg_count(1), quick_group(1) + Item_sum(Item *a) :args(tmp_args), arg_count(1), quick_group(1), + forced_const(FALSE) { args[0]=a; mark_as_sum_func(); } - Item_sum( Item *a, Item *b ) - :args(tmp_args), arg_count(2), quick_group(1) + Item_sum( Item *a, Item *b ) :args(tmp_args), arg_count(2), quick_group(1), + forced_const(FALSE) { args[0]=a; args[1]=b; mark_as_sum_func(); @@ -319,10 +328,20 @@ public: virtual const char *func_name() const= 0; virtual Item *result_item(Field *field) { return new Item_field(field); } - table_map used_tables() const { return ~(table_map) 0; } /* Not used */ - bool const_item() const { return 0; } + table_map used_tables() const { return used_tables_cache; } + void update_used_tables (); + void cleanup() + { + Item::cleanup(); + forced_const= FALSE; + } bool is_null() { return null_value; } - void update_used_tables() { } + void make_const () + { + used_tables_cache= 0; + forced_const= TRUE; + } + virtual bool const_item() const { return forced_const; } void make_field(Send_field *field); void print(String *str); void fix_num_length_and_dec(); @@ -509,23 +528,23 @@ public: class Item_sum_count :public Item_sum_int { longlong count; - table_map used_table_cache; public: Item_sum_count(Item *item_par) - :Item_sum_int(item_par),count(0),used_table_cache(~(table_map) 0) + :Item_sum_int(item_par),count(0) {} Item_sum_count(THD *thd, Item_sum_count *item) - :Item_sum_int(thd, item), count(item->count), - used_table_cache(item->used_table_cache) + :Item_sum_int(thd, item), count(item->count) {} - table_map used_tables() const { return used_table_cache; } - bool const_item() const { return !used_table_cache; } enum Sumfunctype sum_func () const { return COUNT_FUNC; } void clear(); void no_rows_in_result() { count=0; } bool add(); - void make_const(longlong count_arg) { count=count_arg; used_table_cache=0; } + void make_const(longlong count_arg) + { + count=count_arg; + Item_sum::make_const(); + } longlong val_int(); void reset_field(); void cleanup(); @@ -805,28 +824,22 @@ protected: Item_result hybrid_type; enum_field_types hybrid_field_type; int cmp_sign; - table_map used_table_cache; bool was_values; // Set if we have found at least one row (for max/min only) public: Item_sum_hybrid(Item *item_par,int sign) :Item_sum(item_par), sum(0.0), sum_int(0), hybrid_type(INT_RESULT), hybrid_field_type(MYSQL_TYPE_LONGLONG), - cmp_sign(sign), used_table_cache(~(table_map) 0), - was_values(TRUE) + cmp_sign(sign), was_values(TRUE) { collation.set(&my_charset_bin); } Item_sum_hybrid(THD *thd, Item_sum_hybrid *item); bool fix_fields(THD *, Item **); - table_map used_tables() const { return used_table_cache; } - bool const_item() const { return !used_table_cache; } - void clear(); double val_real(); longlong val_int(); my_decimal *val_decimal(my_decimal *); void reset_field(); String *val_str(String *); - void make_const() { used_table_cache=0; } bool keep_field_type(void) const { return 1; } enum Item_result result_type () const { return hybrid_type; } enum enum_field_types field_type() const { return hybrid_field_type; } diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h index 3a59abf1bda..ea93619e59a 100644 --- a/sql/item_timefunc.h +++ b/sql/item_timefunc.h @@ -351,7 +351,7 @@ public: enum_field_types field_type() const { return MYSQL_TYPE_DATE; } String *val_str(String *str); longlong val_int(); - double val_real() { DBUG_ASSERT(fixed == 1); return (double) val_int(); } + double val_real() { return val_real_from_decimal(); } const char *func_name() const { return "date"; } void fix_length_and_dec() { @@ -389,6 +389,7 @@ public: return tmp_table_field_from_field_type(table, 0); } bool result_as_longlong() { return TRUE; } + double val_real() { return (double) val_int(); } my_decimal *val_decimal(my_decimal *decimal_value) { DBUG_ASSERT(fixed == 1); @@ -411,13 +412,14 @@ public: enum_field_types field_type() const { return MYSQL_TYPE_TIME; } void fix_length_and_dec() { - decimals=0; + decimals= DATETIME_DEC; max_length=MAX_TIME_WIDTH*MY_CHARSET_BIN_MB_MAXLEN; } Field *tmp_table_field(TABLE *table) { return tmp_table_field_from_field_type(table, 0); } + double val_real() { return val_real_from_decimal(); } my_decimal *val_decimal(my_decimal *decimal_value) { DBUG_ASSERT(fixed == 1); @@ -521,7 +523,6 @@ public: Item_func_now() :Item_date_func() {} Item_func_now(Item *a) :Item_date_func(a) {} enum Item_result result_type () const { return STRING_RESULT; } - double val_real() { DBUG_ASSERT(fixed == 1); return (double) value; } longlong val_int() { DBUG_ASSERT(fixed == 1); return value; } int save_in_field(Field *to, bool no_conversions); String *val_str(String *str); @@ -611,11 +612,6 @@ class Item_func_from_unixtime :public Item_date_func THD *thd; public: Item_func_from_unixtime(Item *a) :Item_date_func(a) {} - double val_real() - { - DBUG_ASSERT(fixed == 1); - return (double) Item_func_from_unixtime::val_int(); - } longlong val_int(); String *val_str(String *str); const char *func_name() const { return "from_unixtime"; } @@ -653,7 +649,6 @@ class Item_func_convert_tz :public Item_date_func Item_func_convert_tz(Item *a, Item *b, Item *c): Item_date_func(a, b, c), from_tz_cached(0), to_tz_cached(0) {} longlong val_int(); - double val_real() { return (double) val_int(); } String *val_str(String *str); const char *func_name() const { return "convert_tz"; } void fix_length_and_dec(); @@ -678,7 +673,6 @@ public: Item_str_timefunc::fix_length_and_dec(); collation.set(&my_charset_bin); maybe_null=1; - decimals= DATETIME_DEC; } const char *func_name() const { return "sec_to_time"; } bool result_as_longlong() { return TRUE; } @@ -700,7 +694,6 @@ public: const char *func_name() const { return "date_add_interval"; } void fix_length_and_dec(); enum_field_types field_type() const { return cached_field_type; } - double val_real() { DBUG_ASSERT(fixed == 1); return (double) val_int(); } longlong val_int(); bool get_date(TIME *res, uint fuzzy_date); bool eq(const Item *item, bool binary_cmp) const; @@ -805,6 +798,7 @@ public: } bool result_as_longlong() { return TRUE; } longlong val_int(); + double val_real() { return (double) val_int(); } my_decimal *val_decimal(my_decimal *decimal_value) { DBUG_ASSERT(fixed == 1); @@ -832,6 +826,7 @@ public: } bool result_as_longlong() { return TRUE; } longlong val_int(); + double val_real() { return val_real_from_decimal(); } my_decimal *val_decimal(my_decimal *decimal_value) { DBUG_ASSERT(fixed == 1); @@ -856,8 +851,15 @@ public: { return tmp_table_field_from_field_type(table, 0); } + void fix_length_and_dec() + { + Item_typecast_maybe_null::fix_length_and_dec(); + decimals= DATETIME_DEC; + } bool result_as_longlong() { return TRUE; } longlong val_int(); + double val_real() { return val_real_from_decimal(); } + double val() { return (double) val_int(); } my_decimal *val_decimal(my_decimal *decimal_value) { DBUG_ASSERT(fixed == 1); @@ -906,6 +908,7 @@ public: void print(String *str); const char *func_name() const { return "add_time"; } bool check_partition_func_processor(byte *int_arg) {return FALSE;} + double val_real() { return val_real_from_decimal(); } my_decimal *val_decimal(my_decimal *decimal_value) { DBUG_ASSERT(fixed == 1); diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 52faaf25b42..03d97a294ad 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -8985,7 +8985,8 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree) else DBUG_RETURN(NULL); - Item *expr= min_max_item->args[0]; /* The argument of MIN/MAX. */ + /* The argument of MIN/MAX. */ + Item *expr= min_max_item->args[0]->real_item(); if (expr->type() == Item::FIELD_ITEM) /* Is it an attribute? */ { if (! min_max_arg_item) @@ -9356,6 +9357,7 @@ check_group_min_max_predicates(COND *cond, Item_field *min_max_arg_item, DBUG_ENTER("check_group_min_max_predicates"); DBUG_ASSERT(cond && min_max_arg_item); + cond= cond->real_item(); Item::Type cond_type= cond->type(); if (cond_type == Item::COND_ITEM) /* 'AND' or 'OR' */ { @@ -9393,7 +9395,7 @@ check_group_min_max_predicates(COND *cond, Item_field *min_max_arg_item, DBUG_PRINT("info", ("Analyzing: %s", pred->func_name())); for (uint arg_idx= 0; arg_idx < pred->argument_count (); arg_idx++) { - cur_arg= arguments[arg_idx]; + cur_arg= arguments[arg_idx]->real_item(); DBUG_PRINT("info", ("cur_arg: %s", cur_arg->full_name())); if (cur_arg->type() == Item::FIELD_ITEM) { diff --git a/sql/sql_base.cc b/sql/sql_base.cc index e0b60b875d5..4d7c2c485ab 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -5772,11 +5772,13 @@ bool setup_tables_and_check_access(THD *thd, TABLE_LIST *leaves_tmp= NULL; bool first_table= true; + thd->leaf_count= 0; if (setup_tables(thd, context, from_clause, tables, &leaves_tmp, select_insert)) return TRUE; - *leaves= leaves_tmp; + if (leaves) + *leaves= leaves_tmp; for (; leaves_tmp; leaves_tmp= leaves_tmp->next_leaf) { @@ -5788,6 +5790,7 @@ bool setup_tables_and_check_access(THD *thd, return TRUE; } first_table= 0; + thd->leaf_count++; } return FALSE; } diff --git a/sql/sql_class.h b/sql/sql_class.h index 11bf0f05d13..a9915fce4ef 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -1411,7 +1411,10 @@ public: #ifdef WITH_PARTITION_STORAGE_ENGINE partition_info *work_part_info; #endif - + + /* pass up the count of "leaf" tables in a JOIN out of setup_tables() */ + byte leaf_count; + THD(); ~THD(); diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index d24230eb379..773383c2c2d 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -2466,12 +2466,14 @@ bool mysql_insert_select_prepare(THD *thd) DBUG_ASSERT(select_lex->leaf_tables != 0); lex->leaf_tables_insert= select_lex->leaf_tables; /* skip all leaf tables belonged to view where we are insert */ - for (first_select_leaf_table= select_lex->leaf_tables->next_leaf; + for (first_select_leaf_table= select_lex->leaf_tables->next_leaf, + thd->leaf_count --; first_select_leaf_table && first_select_leaf_table->belong_to_view && first_select_leaf_table->belong_to_view == lex->leaf_tables_insert->belong_to_view; - first_select_leaf_table= first_select_leaf_table->next_leaf) + first_select_leaf_table= first_select_leaf_table->next_leaf, + thd->leaf_count --) {} select_lex->leaf_tables= first_select_leaf_table; DBUG_RETURN(FALSE); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index e7ade996d87..582df11c8c1 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -408,12 +408,14 @@ JOIN::prepare(Item ***rref_pointer_array, /* Check that all tables, fields, conds and order are ok */ - if ((!(select_options & OPTION_SETUP_TABLES_DONE) && - setup_tables_and_check_access(thd, &select_lex->context, join_list, - tables_list, - &select_lex->leaf_tables, FALSE, - SELECT_ACL, SELECT_ACL)) || - setup_wild(thd, tables_list, fields_list, &all_fields, wild_num) || + if (!(select_options & OPTION_SETUP_TABLES_DONE) && + setup_tables_and_check_access(thd, &select_lex->context, join_list, + tables_list, &select_lex->leaf_tables, + FALSE, SELECT_ACL, SELECT_ACL)) + DBUG_RETURN(-1); + tables= thd->leaf_count; + + if (setup_wild(thd, tables_list, fields_list, &all_fields, wild_num) || select_lex->setup_ref_array(thd, og_num) || setup_fields(thd, (*rref_pointer_array), fields_list, MARK_COLUMNS_READ, &all_fields, 1) || @@ -518,11 +520,6 @@ JOIN::prepare(Item ***rref_pointer_array, DBUG_RETURN(-1); } } - TABLE_LIST *table_ptr; - for (table_ptr= select_lex->leaf_tables; - table_ptr; - table_ptr= table_ptr->next_leaf) - tables++; } { /* Caclulate the number of groups */ @@ -6635,7 +6632,8 @@ static void update_depend_map(JOIN *join, ORDER *order) order->item[0]->update_used_tables(); order->depend_map=depend_map=order->item[0]->used_tables(); // Not item_sum(), RAND() and no reference to table outside of sub select - if (!(order->depend_map & (OUTER_REF_TABLE_BIT | RAND_TABLE_BIT))) + if (!(order->depend_map & (OUTER_REF_TABLE_BIT | RAND_TABLE_BIT)) + && !order->item[0]->with_sum_func) { for (JOIN_TAB **tab=join->map2table; depend_map ; diff --git a/sql/sql_select.h b/sql/sql_select.h index ca93179f9d9..ca37c7bd274 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -527,15 +527,11 @@ extern "C" int refpos_order_cmp(void* arg, const void *a,const void *b); class store_key :public Sql_alloc { - protected: - Field *to_field; // Store data here - char *null_ptr; - char err; public: bool null_key; /* TRUE <=> the value of the key has a null part */ enum store_key_result { STORE_KEY_OK, STORE_KEY_FATAL, STORE_KEY_CONV }; store_key(THD *thd, Field *field_arg, char *ptr, char *null, uint length) - :null_ptr(null), err(0), null_key(0) + :null_key(0), null_ptr(null), err(0) { if (field_arg->type() == MYSQL_TYPE_BLOB) { @@ -550,8 +546,35 @@ public: ptr, (uchar*) null, 1); } virtual ~store_key() {} /* Not actually needed */ - virtual enum store_key_result copy()=0; virtual const char *name() const=0; + + /** + @brief sets ignore truncation warnings mode and calls the real copy method + + @details this function makes sure truncation warnings when preparing the + key buffers don't end up as errors (because of an enclosing INSERT/UPDATE). + */ + enum store_key_result copy() + { + enum store_key_result result; + enum_check_fields saved_count_cuted_fields= + to_field->table->in_use->count_cuted_fields; + + to_field->table->in_use->count_cuted_fields= CHECK_FIELD_IGNORE; + + result= copy_inner(); + + to_field->table->in_use->count_cuted_fields= saved_count_cuted_fields; + + return result; + } + + protected: + Field *to_field; // Store data here + char *null_ptr; + char err; + + virtual enum store_key_result copy_inner()=0; }; @@ -571,7 +594,10 @@ class store_key_field: public store_key copy_field.set(to_field,from_field,0); } } - enum store_key_result copy() + const char *name() const { return field_name; } + + protected: + enum store_key_result copy_inner() { TABLE *table= copy_field.to_field->table; my_bitmap_map *old_map= dbug_tmp_use_all_columns(table, @@ -581,7 +607,6 @@ class store_key_field: public store_key null_key= to_field->is_null(); return err != 0 ? STORE_KEY_FATAL : STORE_KEY_OK; } - const char *name() const { return field_name; } }; @@ -596,7 +621,10 @@ public: null_ptr_arg ? null_ptr_arg : item_arg->maybe_null ? &err : NullS, length), item(item_arg) {} - enum store_key_result copy() + const char *name() const { return "func"; } + + protected: + enum store_key_result copy_inner() { TABLE *table= to_field->table; my_bitmap_map *old_map= dbug_tmp_use_all_columns(table, @@ -606,7 +634,6 @@ public: null_key= to_field->is_null() || item->null_value; return (err != 0 || res > 2 ? STORE_KEY_FATAL : (store_key_result) res); } - const char *name() const { return "func"; } }; @@ -622,7 +649,10 @@ public: &err : NullS, length, item_arg), inited(0) { } - enum store_key_result copy() + const char *name() const { return "const"; } + +protected: + enum store_key_result copy_inner() { int res; if (!inited) @@ -637,7 +667,6 @@ public: null_key= to_field->is_null() || item->null_value; return (err > 2 ? STORE_KEY_FATAL : (store_key_result) err); } - const char *name() const { return "const"; } }; bool cp_buffer_from_ref(THD *thd, TABLE *table, TABLE_REF *ref); diff --git a/sql/sql_view.cc b/sql/sql_view.cc index b619e451b22..32a21e377ba 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -224,6 +224,9 @@ bool mysql_create_view(THD *thd, TABLE_LIST *views, { LEX *lex= thd->lex; bool link_to_local; +#ifndef NO_EMBEDDED_ACCESS_CHECKS + bool definer_check_is_needed= mode != VIEW_ALTER || lex->definer; +#endif /* first table in list is target VIEW name => cut off it */ TABLE_LIST *view= lex->unlink_first_table(&link_to_local); TABLE_LIST *tables= lex->query_tables; @@ -256,8 +259,9 @@ bool mysql_create_view(THD *thd, TABLE_LIST *views, /* DEFINER-clause is missing; we have to create default definer in persistent arena to be PS/SP friendly. + If this is an ALTER VIEW then the current user should be set as + the definer. */ - Query_arena original_arena; Query_arena *ps_arena = thd->activate_stmt_arena_if_needed(&original_arena); @@ -277,11 +281,11 @@ bool mysql_create_view(THD *thd, TABLE_LIST *views, - same as current user - current user has SUPER_ACL */ - if (strcmp(lex->definer->user.str, - thd->security_ctx->priv_user) != 0 || - my_strcasecmp(system_charset_info, - lex->definer->host.str, - thd->security_ctx->priv_host) != 0) + if (definer_check_is_needed && + (strcmp(lex->definer->user.str, thd->security_ctx->priv_user) != 0 || + my_strcasecmp(system_charset_info, + lex->definer->host.str, + thd->security_ctx->priv_host) != 0)) { if (!(thd->security_ctx->master_access & SUPER_ACL)) { diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 01e91814dac..b7894e9ce04 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -11075,7 +11075,7 @@ union_list: UNION_SYM union_option { LEX *lex=Lex; - if (lex->exchange) + if (lex->result) { /* Only the last SELECT can have INTO...... */ my_error(ER_WRONG_USAGE, MYF(0), "UNION", "INTO"); |