diff options
Diffstat (limited to 'mysql-test')
31 files changed, 1404 insertions, 14 deletions
diff --git a/mysql-test/r/alias.result b/mysql-test/r/alias.result index 6f0315da234..3190e8994e5 100644 --- a/mysql-test/r/alias.result +++ b/mysql-test/r/alias.result @@ -73,3 +73,142 @@ UPDATE t1 SET t1.xstatus_vor = Greatest(t1.xstatus_vor,1) WHERE t1.aufnr = "40004712" AND t1.plnfl = "000001" AND t1.vornr > "0010" ORDER BY t1.vornr ASC LIMIT 1; drop table t1; +drop table if exists t1,t2,t3; +create table t1 (a int, b int, c int); +create table t2 (d int); +create table t3 (a1 int, b1 int, c1 int); +insert into t1 values(1,2,3); +insert into t1 values(11,22,33); +insert into t2 values(99); +select t1.* as 'with_alias' from t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'with_alias' from t1' at line 1 +select t2.* as 'with_alias' from t2; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'with_alias' from t2' at line 1 +select t1.*, t1.* as 'with_alias' from t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'with_alias' from t1' at line 1 +select t1.* as 'with_alias', t1.* from t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'with_alias', t1.* from t1' at line 1 +select t1.* as 'with_alias', t1.* as 'alias2' from t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'with_alias', t1.* as 'alias2' from t1' at line 1 +select t1.* as 'with_alias', a, t1.* as 'alias2' from t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'with_alias', a, t1.* as 'alias2' from t1' at line 1 +select a, t1.* as 'with_alias' from t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'with_alias' from t1' at line 1 +select t1.* as 'with_alias', a from t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'with_alias', a from t1' at line 1 +select a, t1.* as 'with_alias', b from t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'with_alias', b from t1' at line 1 +select (select d from t2 where d > a), t1.* as 'with_alias' from t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'with_alias' from t1' at line 1 +select t1.* as 'with_alias', (select a from t2 where d > a) from t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'with_alias', (select a from t2 where d > a) from t1' at line 1 +select a as 'x', t1.* as 'with_alias' from t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'with_alias' from t1' at line 1 +select t1.* as 'with_alias', a as 'x' from t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'with_alias', a as 'x' from t1' at line 1 +select a as 'x', t1.* as 'with_alias', b as 'x' from t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'with_alias', b as 'x' from t1' at line 1 +select (select d from t2 where d > a) as 'x', t1.* as 'with_alias' from t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'with_alias' from t1' at line 1 +select t1.* as 'with_alias', (select a from t2 where d > a) as 'x' from t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'with_alias', (select a from t2 where d > a) as 'x' from t1' at line 1 +select (select t2.* as 'x' from t2) from t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'x' from t2) from t1' at line 1 +select a, (select t2.* as 'x' from t2) from t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'x' from t2) from t1' at line 1 +select t1.*, (select t2.* as 'x' from t2) from t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'x' from t2) from t1' at line 1 +insert into t3 select t1.* as 'with_alias' from t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'with_alias' from t1' at line 1 +insert into t3 select t2.* as 'with_alias', 1, 2 from t2; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'with_alias', 1, 2 from t2' at line 1 +insert into t3 select t2.* as 'with_alias', d as 'x', d as 'z' from t2; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'with_alias', d as 'x', d as 'z' from t2' at line 1 +insert into t3 select t2.*, t2.* as 'with_alias', 3 from t2; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'with_alias', 3 from t2' at line 1 +create table t3 select t1.* as 'with_alias' from t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'with_alias' from t1' at line 1 +create table t3 select t2.* as 'with_alias', 1, 2 from t2; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'with_alias', 1, 2 from t2' at line 1 +create table t3 select t2.* as 'with_alias', d as 'x', d as 'z' from t2; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'with_alias', d as 'x', d as 'z' from t2' at line 1 +create table t3 select t2.*, t2.* as 'with_alias', 3 from t2; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'with_alias', 3 from t2' at line 1 +select t1.* from t1; +a b c +1 2 3 +11 22 33 +select t2.* from t2; +d +99 +select t1.*, t1.* from t1; +a b c a b c +1 2 3 1 2 3 +11 22 33 11 22 33 +select t1.*, a, t1.* from t1; +a b c a a b c +1 2 3 1 1 2 3 +11 22 33 11 11 22 33 +select a, t1.* from t1; +a a b c +1 1 2 3 +11 11 22 33 +select t1.*, a from t1; +a b c a +1 2 3 1 +11 22 33 11 +select a, t1.*, b from t1; +a a b c b +1 1 2 3 2 +11 11 22 33 22 +select (select d from t2 where d > a), t1.* from t1; +(select d from t2 where d > a) a b c +99 1 2 3 +99 11 22 33 +select t1.*, (select a from t2 where d > a) from t1; +a b c (select a from t2 where d > a) +1 2 3 1 +11 22 33 11 +select a as 'x', t1.* from t1; +x a b c +1 1 2 3 +11 11 22 33 +select t1.*, a as 'x' from t1; +a b c x +1 2 3 1 +11 22 33 11 +select a as 'x', t1.*, b as 'x' from t1; +x a b c x +1 1 2 3 2 +11 11 22 33 22 +select (select d from t2 where d > a) as 'x', t1.* from t1; +x a b c +99 1 2 3 +99 11 22 33 +select t1.*, (select a from t2 where d > a) as 'x' from t1; +a b c x +1 2 3 1 +11 22 33 11 +select (select t2.* from t2) from t1; +(select t2.* from t2) +99 +99 +select a, (select t2.* from t2) from t1; +a (select t2.* from t2) +1 99 +11 99 +select t1.*, (select t2.* from t2) from t1; +a b c (select t2.* from t2) +1 2 3 99 +11 22 33 99 +insert into t3 select t1.* from t1; +insert into t3 select t2.*, 1, 2 from t2; +insert into t3 select t2.*, d as 'x', d as 'z' from t2; +insert into t3 select t2.*, t2.*, 3 from t2; +create table t4 select t1.* from t1; +drop table t4; +create table t4 select t2.*, 1, 2 from t2; +drop table t4; +create table t4 select t2.*, d as 'x', d as 'z' from t2; +drop table t4; +drop table t1,t2,t3; diff --git a/mysql-test/r/delete.result b/mysql-test/r/delete.result index 0124a7da35a..03a3165aad0 100644 --- a/mysql-test/r/delete.result +++ b/mysql-test/r/delete.result @@ -259,8 +259,8 @@ ERROR 42S02: Unknown table 't2' in MULTI DELETE DELETE FROM db1.t1 alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a' at line 1 DELETE FROM alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a; -ERROR 42S02: Unknown table 'alias' in MULTI DELETE DELETE FROM db2.alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a; +ERROR 42S02: Unknown table 'alias' in MULTI DELETE DELETE FROM t1 USING t1 WHERE a = 1; SELECT * FROM t1; a @@ -279,6 +279,147 @@ ERROR 42000: Incorrect number of arguments for FUNCTION test.f1; expected 0, got DROP TABLE t1; DROP FUNCTION f1; End of 5.0 tests +DROP DATABASE IF EXISTS db1; +DROP DATABASE IF EXISTS db2; +DROP DATABASE IF EXISTS db3; +DROP DATABASE IF EXISTS db4; +DROP TABLE IF EXISTS t1, t2; +DROP PROCEDURE IF EXISTS count; +USE test; +CREATE DATABASE db1; +CREATE DATABASE db2; +CREATE TABLE db1.t1 (a INT, b INT); +INSERT INTO db1.t1 VALUES (1,1),(2,2),(3,3); +CREATE TABLE db1.t2 AS SELECT * FROM db1.t1; +CREATE TABLE db2.t1 AS SELECT * FROM db1.t2; +CREATE TABLE db2.t2 AS SELECT * FROM db2.t1; +CREATE TABLE t1 AS SELECT * FROM db2.t2; +CREATE TABLE t2 AS SELECT * FROM t1; +CREATE PROCEDURE count_rows() +BEGIN +SELECT COUNT(*) AS "COUNT(db1.t1)" FROM db1.t1; +SELECT COUNT(*) AS "COUNT(db1.t2)" FROM db1.t2; +SELECT COUNT(*) AS "COUNT(db2.t1)" FROM db2.t1; +SELECT COUNT(*) AS "COUNT(db2.t2)" FROM db2.t2; +SELECT COUNT(*) AS "COUNT(test.t1)" FROM test.t1; +SELECT COUNT(*) AS "COUNT(test.t2)" FROM test.t2; +END| +CREATE DATABASE db3; +USE db3; +DROP DATABASE db3; +SELECT * FROM t1; +ERROR 3D000: No database selected +DELETE a1,a2 FROM db1.t1, db2.t2; +ERROR 3D000: No database selected +DELETE a1,a2 FROM db1.t1, db2.t2; +ERROR 3D000: No database selected +DELETE a1,a2 FROM db1.t1 AS a1, db2.t2; +ERROR 3D000: No database selected +DELETE a1,a2 FROM db1.t1, db2.t2 AS a2; +ERROR 3D000: No database selected +DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2; +ERROR 3D000: No database selected +DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2; +ERROR 3D000: No database selected +DELETE FROM a1,a2 USING db1.t1, db2.t2; +ERROR 3D000: No database selected +DELETE FROM a1,a2 USING db1.t1, db2.t2; +ERROR 3D000: No database selected +DELETE FROM a1,a2 USING db1.t1 AS a1, db2.t2; +ERROR 3D000: No database selected +DELETE FROM a1,a2 USING db1.t1, db2.t2 AS a2; +ERROR 3D000: No database selected +DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2; +ERROR 3D000: No database selected +DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2; +ERROR 3D000: No database selected +DELETE a1 FROM db1.t1 AS a1, db2.t2 AS a1; +ERROR 3D000: No database selected +DELETE a1 FROM db1.a1, db2.t2 AS a1; +ERROR 3D000: No database selected +DELETE a1 FROM a1, db1.t1 AS a1; +ERROR 3D000: No database selected +DELETE t1 FROM db1.t1, db2.t1 AS a1; +ERROR 3D000: No database selected +DELETE t1 FROM db1.t1 AS a1, db2.t1 AS a2; +ERROR 3D000: No database selected +DELETE t1 FROM db1.t1, db2.t1; +ERROR 3D000: No database selected +USE test; +DELETE a1,a2 FROM db1.t1, db2.t2; +ERROR 42S02: Unknown table 'a1' in MULTI DELETE +DELETE a1,a2 FROM db1.t1, db2.t2; +ERROR 42S02: Unknown table 'a1' in MULTI DELETE +DELETE a1,a2 FROM db1.t1 AS a1, db2.t2; +ERROR 42S02: Unknown table 'a2' in MULTI DELETE +DELETE a1,a2 FROM db1.t1, db2.t2 AS a2; +ERROR 42S02: Unknown table 'a1' in MULTI DELETE +DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2; +ERROR 42S02: Table 'db3.t1' doesn't exist +DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2; +ERROR 42S02: Table 'db3.t1' doesn't exist +DELETE FROM a1,a2 USING db1.t1, db2.t2; +ERROR 42S02: Unknown table 'a1' in MULTI DELETE +DELETE FROM a1,a2 USING db1.t1, db2.t2; +ERROR 42S02: Unknown table 'a1' in MULTI DELETE +DELETE FROM a1,a2 USING db1.t1 AS a1, db2.t2; +ERROR 42S02: Unknown table 'a2' in MULTI DELETE +DELETE FROM a1,a2 USING db1.t1, db2.t2 AS a2; +ERROR 42S02: Unknown table 'a1' in MULTI DELETE +DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2; +ERROR 42S02: Table 'db3.t1' doesn't exist +DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2; +ERROR 42S02: Table 'db3.t1' doesn't exist +DELETE a1 FROM db1.t1 AS a1, db2.t2 AS a1; +ERROR 42000: Not unique table/alias: 'a1' +DELETE a1 FROM db1.a1, db2.t2 AS a1; +ERROR 42S02: Table 'db1.a1' doesn't exist +DELETE a1 FROM a1, db1.t1 AS a1; +ERROR 42000: Not unique table/alias: 'a1' +DELETE t1 FROM db1.t1, db2.t1 AS a1; +ERROR 42S02: Unknown table 't1' in MULTI DELETE +DELETE t1 FROM db1.t1 AS a1, db2.t1 AS a2; +ERROR 42S02: Unknown table 't1' in MULTI DELETE +DELETE t1 FROM db1.t1, db2.t1; +ERROR 42S02: Unknown table 't1' in MULTI DELETE +DELETE t1 FROM db1.t2 AS t1, db2.t2 AS t2 WHERE t2.a = 1 AND t1.a = t2.a; +SELECT ROW_COUNT(); +ROW_COUNT() +1 +CALL count_rows(); +COUNT(db1.t1) +3 +COUNT(db1.t2) +2 +COUNT(db2.t1) +3 +COUNT(db2.t2) +3 +COUNT(test.t1) +3 +COUNT(test.t2) +3 +DELETE a1, a2 FROM db2.t1 AS a1, t2 AS a2 WHERE a1.a = 2 AND a2.a = 2; +SELECT ROW_COUNT(); +ROW_COUNT() +2 +CALL count_rows(); +COUNT(db1.t1) +3 +COUNT(db1.t2) +2 +COUNT(db2.t1) +2 +COUNT(db2.t2) +3 +COUNT(test.t1) +3 +COUNT(test.t2) +2 +DROP DATABASE db1; +DROP DATABASE db2; +DROP PROCEDURE count_rows; +DROP TABLE t1, t2; # # Bug#46958: Assertion in Diagnostics_area::set_ok_status, trigger, # merge table diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 80f04ffd455..53cd89c13c1 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -277,7 +277,7 @@ select * from t1; N M 3 0 delete P1.*,p2.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS p2 ON P1.N = p2.N; -ERROR 42S02: Unknown table 'p2' in MULTI DELETE +ERROR HY000: The target table p2 of the DELETE is not updatable delete P1.* from `t1` AS P1 INNER JOIN (SELECT aaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N; ERROR 42S22: Unknown column 'aaa' in 'field list' drop table t1; diff --git a/mysql-test/r/dirty_close.result b/mysql-test/r/dirty_close.result index b49b72f1b95..f7012ff9c01 100644 --- a/mysql-test/r/dirty_close.result +++ b/mysql-test/r/dirty_close.result @@ -7,3 +7,13 @@ n 2 3 DROP TABLE t1; +SELECT GET_LOCK("dangling", 0); +GET_LOCK("dangling", 0) +1 +SELECT GET_LOCK('dangling', 3600);; +SELECT GET_LOCK('dangling', 3600);; +SELECT RELEASE_LOCK('dangling'); +RELEASE_LOCK('dangling') +1 +GET_LOCK('dangling', 3600) +1 diff --git a/mysql-test/r/func_sapdb.result b/mysql-test/r/func_sapdb.result index 87b88692a34..2d6154cd1f7 100644 --- a/mysql-test/r/func_sapdb.result +++ b/mysql-test/r/func_sapdb.result @@ -282,3 +282,33 @@ TIMEDIFF(TIME('17:59:00'),TIME('17:00:00')), TIMEDIFF(TIME('17:00:00'),TIME('17:59:00')); 1Eq 1NEq1 1NEq2 2Eq 2NEq1 2NEq2 3Eq 3NEq1 3NEq2 Time0 Time00 Literal0000 TIMEDIFF(TIME('17:59:00'),TIME('17:00:00')) TIMEDIFF(TIME('17:00:00'),TIME('17:59:00')) 1 0 0 1 0 0 1 0 0 00:00:00 00:00:00 00:00:00 00:59:00 -00:59:00 +SELECT sec_to_time(3020399)=TIME('838:59:59'); +sec_to_time(3020399)=TIME('838:59:59') +1 +SELECT sec_to_time(-3020399)=TIME('-838:59:59'); +sec_to_time(-3020399)=TIME('-838:59:59') +1 +SELECT sec_to_time(-3020399)='-838:59:59'; +sec_to_time(-3020399)='-838:59:59' +1 +SELECT time(sec_to_time(-3020399))=TIME('-838:59:59'); +time(sec_to_time(-3020399))=TIME('-838:59:59') +1 +SELECT time(sec_to_time(-3020399))=TIME('-838:59:58'); +time(sec_to_time(-3020399))=TIME('-838:59:58') +0 +SELECT maketime(-1,0,1)='-01:00:01'; +maketime(-1,0,1)='-01:00:01' +1 +SELECT TIME(maketime(-1,0,1))=CAST('-01:00:01' AS TIME); +TIME(maketime(-1,0,1))=CAST('-01:00:01' AS TIME) +1 +SELECT maketime(-1,0,1)=CAST('-01:00:01' AS TIME); +maketime(-1,0,1)=CAST('-01:00:01' AS TIME) +1 +SELECT maketime(1,0,1)=CAST('01:00:01' AS TIME); +maketime(1,0,1)=CAST('01:00:01' AS TIME) +1 +SELECT maketime(1,0,1)=CAST('01:00:02' AS TIME); +maketime(1,0,1)=CAST('01:00:02' AS TIME) +0 diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 5e72b69c41d..59527e0b418 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -1301,6 +1301,12 @@ SELECT '2008-02-18' + INTERVAL 1 FRAC_SECOND; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FRAC_SECOND' at line 1 SELECT '2008-02-18' - INTERVAL 1 FRAC_SECOND; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FRAC_SECOND' at line 1 +select date_add('1000-01-01 00:00:00', interval '1.03:02:01.05' day_microsecond); +date_add('1000-01-01 00:00:00', interval '1.03:02:01.05' day_microsecond) +1000-01-02 03:02:01.050000 +select date_add('1000-01-01 00:00:00', interval '1.02' day_microsecond); +date_add('1000-01-01 00:00:00', interval '1.02' day_microsecond) +1000-01-01 00:00:01.020000 End of 5.0 tests select date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND); date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND) diff --git a/mysql-test/r/mysql.result b/mysql-test/r/mysql.result index 70e93c3e01f..ee64f60c759 100644 --- a/mysql-test/r/mysql.result +++ b/mysql-test/r/mysql.result @@ -201,6 +201,202 @@ COUNT (*) ERROR 2005 (HY000) at line 1: Unknown MySQL server host 'invalid_hostname' (errno) End of 5.0 tests WARNING: --server-arg option not supported in this configuration. +*************************** 1. row *************************** +1: 1 +2: 2 +3: 3 +4: 4 +5: 5 +6: 6 +7: 7 +8: 8 +9: 9 +0: 0 +1: 1 +2: 2 +3: 3 +4: 4 +5: 5 +6: 6 +7: 7 +8: 8 +9: 9 +0: 0 +1: 1 +2: 2 +3: 3 +4: 4 +5: 5 +6: 6 +7: 7 +8: 8 +9: 9 +0: 0 +1: 1 +2: 2 +3: 3 +4: 4 +5: 5 +6: 6 +7: 7 +8: 8 +9: 9 +0: 0 +1: 1 +2: 2 +3: 3 +4: 4 +5: 5 +6: 6 +7: 7 +8: 8 +9: 9 +0: 0 +1: 1 +2: 2 +3: 3 +4: 4 +5: 5 +6: 6 +7: 7 +8: 8 +9: 9 +0: 0 +1: 1 +2: 2 +3: 3 +4: 4 +5: 5 +6: 6 +7: 7 +8: 8 +9: 9 +0: 0 +1: 1 +2: 2 +3: 3 +4: 4 +5: 5 +6: 6 +7: 7 +8: 8 +9: 9 +0: 0 +1: 1 +2: 2 +3: 3 +4: 4 +5: 5 +6: 6 +7: 7 +8: 8 +9: 9 +0: 0 +1: 1 +2: 2 +3: 3 +4: 4 +5: 5 +6: 6 +7: 7 +8: 8 +9: 9 +0: 0 +1: 1 +2: 2 +3: 3 +4: 4 +5: 5 +6: 6 +7: 7 +8: 8 +9: 9 +0: 0 +1: 1 +2: 2 +3: 3 +4: 4 +5: 5 +6: 6 +7: 7 +8: 8 +9: 9 +0: 0 +1: 1 +2: 2 +3: 3 +4: 4 +5: 5 +6: 6 +7: 7 +8: 8 +9: 9 +0: 0 +1: 1 +2: 2 +3: 3 +4: 4 +5: 5 +6: 6 +7: 7 +8: 8 +9: 9 +0: 0 +1: 1 +2: 2 +3: 3 +4: 4 +5: 5 +6: 6 +7: 7 +8: 8 +9: 9 +0: 0 +1: 1 +2: 2 +3: 3 +4: 4 +5: 5 +6: 6 +7: 7 +8: 8 +9: 9 +0: 0 +1: 1 +2: 2 +3: 3 +4: 4 +5: 5 +6: 6 +7: 7 +8: 8 +9: 9 +0: 0 +1: 1 +2: 2 +3: 3 +4: 4 +5: 5 +6: 6 +7: 7 +8: 8 +9: 9 +0: 0 +1: 1 +2: 2 +3: 3 +4: 4 +5: 5 +6: 6 +7: 7 +8: 8 +9: 9 +0: 0 ++---+ +| 1 | ++---+ +| 1 | ++---+ Warning (Code 1286): Unknown table engine 'nonexistent' Warning (Code 1266): Using storage engine MyISAM for table 't2' Warning (Code 1286): Unknown table engine 'nonexistent2' diff --git a/mysql-test/r/partition_sync.result b/mysql-test/r/partition_sync.result new file mode 100644 index 00000000000..31cf0569464 --- /dev/null +++ b/mysql-test/r/partition_sync.result @@ -0,0 +1,25 @@ +# +# Bug #43867 ALTER TABLE on a partitioned table +# causes unnecessary deadlocks +# +CREATE TABLE t1 (a int) PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (1), +PARTITION p1 VALUES LESS THAN (2)); +INSERT INTO t1 VALUES (0),(1); +# Connection 2 +BEGIN; +SELECT * FROM t1; +a +0 +1 +# Connection 1 +ALTER TABLE t1 DROP PARTITION p3; +ERROR HY000: Error in list of partitions to DROP +# Connection 2 +# This failed with deadlock and should not do so. +SELECT * FROM t1; +a +0 +1 +# Connection 1 +DROP TABLE t1; diff --git a/mysql-test/r/rpl_mysqldump_slave.result b/mysql-test/r/rpl_mysqldump_slave.result new file mode 100644 index 00000000000..158a43a658c --- /dev/null +++ b/mysql-test/r/rpl_mysqldump_slave.result @@ -0,0 +1,17 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +# +# New --dump-slave, --apply-slave-statements functionality +# +use test; +CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=107; +STOP SLAVE; +CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=107; +START SLAVE; +STOP SLAVE; +CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT='MASTER_MYPORT', MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=107; +START SLAVE; diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index b3968ea7eb6..499506957fa 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -1659,6 +1659,18 @@ begin declare continue handler for sqlstate '00000' set @x=0; end$$ ERROR 42000: Bad SQLSTATE: '00000' +drop procedure if exists p1; +set @old_recursion_depth = @@max_sp_recursion_depth; +set @@max_sp_recursion_depth = 255; +create procedure p1(a int) +begin +declare continue handler for 1436 -- ER_STACK_OVERRUN_NEED_MORE +select 'exception'; +call p1(a+1); +end| +call p1(1); +set @@max_sp_recursion_depth = @old_recursion_depth; +drop procedure p1; LOAD DATA INFILE '../../tmp/proc.txt' INTO TABLE mysql.proc; CREATE TABLE t1 (a INT, b INT); INSERT INTO t1 VALUES (1,1), (2,2); diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 45dac2f5523..26ba2af8f68 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -6941,6 +6941,101 @@ SELECT * FROM t1 WHERE a = f1(); ERROR 42S02: Table 'test.t_non_existing' doesn't exist DROP FUNCTION f1; DROP TABLE t1; +DROP PROCEDURE IF EXISTS p1; +CREATE PROCEDURE p1(a INT, b CHAR) +BEGIN +IF a > 0 THEN +CALL p1(a-1, 'ab'); +ELSE +SELECT 1; +END IF; +END| +SET @save_max_sp_recursion= @@max_sp_recursion_depth; +SET @@max_sp_recursion_depth= 5; +CALL p1(4, 'a'); +1 +1 +Warnings: +Warning 1265 Data truncated for column 'b' at row 1 +Warning 1265 Data truncated for column 'b' at row 1 +Warning 1265 Data truncated for column 'b' at row 1 +Warning 1265 Data truncated for column 'b' at row 1 +SET @@max_sp_recursion_depth= @save_max_sp_recursion; +DROP PROCEDURE p1; +DROP PROCEDURE IF EXISTS p1; +CREATE PROCEDURE p1(a CHAR) +BEGIN +SELECT 1; +SELECT CAST('10 ' as UNSIGNED INTEGER); +SELECT 1; +END| +CALL p1('data truncated parameter'); +1 +1 +CAST('10 ' as UNSIGNED INTEGER) +10 +1 +1 +Warnings: +Warning 1265 Data truncated for column 'a' at row 1 +Warning 1292 Truncated incorrect INTEGER value: '10 ' +DROP PROCEDURE p1; +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +DROP PROCEDURE IF EXISTS p3; +DROP PROCEDURE IF EXISTS p4; +CREATE PROCEDURE p1() +CALL p2()| +CREATE PROCEDURE p2() +CALL p3()| +CREATE PROCEDURE p3() +CALL p4()| +CREATE PROCEDURE p4() +BEGIN +SELECT 1; +SELECT CAST('10 ' as UNSIGNED INTEGER); +SELECT 2; +END| +CALL p1(); +1 +1 +CAST('10 ' as UNSIGNED INTEGER) +10 +2 +2 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: '10 ' +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP PROCEDURE p3; +DROP PROCEDURE p4; +DROP FUNCTION IF EXISTS f1; +DROP FUNCTION IF EXISTS f2; +DROP FUNCTION IF EXISTS f3; +DROP FUNCTION IF EXISTS f4; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (a CHAR(2)); +INSERT INTO t1 VALUES ('aa'); +CREATE FUNCTION f1() RETURNS CHAR +RETURN (SELECT f2())| +CREATE FUNCTION f2() RETURNS CHAR +RETURN (SELECT f3())| +CREATE FUNCTION f3() RETURNS CHAR +RETURN (SELECT f4())| +CREATE FUNCTION f4() RETURNS CHAR +BEGIN +RETURN (SELECT a FROM t1); +END| +SELECT f1(); +f1() +a +Warnings: +Warning 1265 Data truncated for column 'f4()' at row 1 +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP FUNCTION f3; +DROP FUNCTION f4; +DROP TABLE t1; # # Bug#34197: CREATE PROCEDURE fails when COMMENT truncated in non # strict SQL mode diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index 1a6494e03ad..47b50b233b3 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -2115,3 +2115,50 @@ s1 DELETE FROM t1; DROP TABLE t1; DROP TEMPORARY TABLE t2; +DROP TRIGGER IF EXISTS trg1; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (a INT); +CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW +BEGIN +DECLARE a CHAR; +SELECT 'ab' INTO a; +SELECT 'ab' INTO a; +SELECT 'a' INTO a; +END| +INSERT INTO t1 VALUES (1); +Warnings: +Warning 1265 Data truncated for column 'a' at row 1 +DROP TRIGGER trg1; +DROP TABLE t1; +DROP TRIGGER IF EXISTS trg1; +DROP TRIGGER IF EXISTS trg2; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (a INT); +CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW +BEGIN +DECLARE trg1 CHAR; +SELECT 'ab' INTO trg1; +END| +CREATE TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW +BEGIN +DECLARE trg2 CHAR; +SELECT 'ab' INTO trg2; +END| +INSERT INTO t1 VALUES (0); +Warnings: +Warning 1265 Data truncated for column 'trg1' at row 1 +Warning 1265 Data truncated for column 'trg2' at row 1 +SELECT * FROM t1; +a +0 +SHOW WARNINGS; +Level Code Message +INSERT INTO t1 VALUES (1),(2); +Warnings: +Warning 1265 Data truncated for column 'trg1' at row 1 +Warning 1265 Data truncated for column 'trg2' at row 1 +Warning 1265 Data truncated for column 'trg1' at row 1 +Warning 1265 Data truncated for column 'trg2' at row 1 +DROP TRIGGER trg1; +DROP TRIGGER trg2; +DROP TABLE t1; diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result index e4b90196c2d..a5880f2b452 100644 --- a/mysql-test/r/type_time.result +++ b/mysql-test/r/type_time.result @@ -85,6 +85,7 @@ sec_to_time(time_to_sec(t)) 13:00:00 09:00:00 drop table t1; +End of 4.1 tests select cast('100:55:50' as time) < cast('24:00:00' as time); cast('100:55:50' as time) < cast('24:00:00' as time) 0 @@ -138,3 +139,27 @@ CAST(c AS TIME) 00:00:00 DROP TABLE t1; End of 5.0 tests +CREATE TABLE t1 (f1 TIME); +INSERT INTO t1 VALUES ('24:00:00'); +SELECT '24:00:00' = (SELECT f1 FROM t1); +'24:00:00' = (SELECT f1 FROM t1) +1 +SELECT CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1); +CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1) +1 +SELECT CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1); +CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1) +0 +TRUNCATE t1; +INSERT INTO t1 VALUES ('-24:00:00'); +SELECT CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1); +CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1) +0 +SELECT CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1); +CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1) +1 +SELECT '-24:00:00' = (SELECT f1 FROM t1); +'-24:00:00' = (SELECT f1 FROM t1) +1 +DROP TABLE t1; +End of 6.0 tests diff --git a/mysql-test/suite/rpl/r/rpl_multi_delete2.result b/mysql-test/suite/rpl/r/rpl_multi_delete2.result index 87abe60b268..8e6a6a3d4d0 100644 --- a/mysql-test/suite/rpl/r/rpl_multi_delete2.result +++ b/mysql-test/suite/rpl/r/rpl_multi_delete2.result @@ -16,7 +16,7 @@ DELETE alias FROM a alias WHERE alias.i=1; SELECT * FROM a; i insert into a values(2),(3); -delete a alias FROM a alias where alias.i=2; +delete alias FROM a alias where alias.i=2; select * from a; i 3 diff --git a/mysql-test/suite/rpl/t/rpl_multi_delete2.test b/mysql-test/suite/rpl/t/rpl_multi_delete2.test index 81379d4056b..e91fad1872a 100644 --- a/mysql-test/suite/rpl/t/rpl_multi_delete2.test +++ b/mysql-test/suite/rpl/t/rpl_multi_delete2.test @@ -25,7 +25,7 @@ INSERT INTO a VALUES(1); DELETE alias FROM a alias WHERE alias.i=1; SELECT * FROM a; insert into a values(2),(3); -delete a alias FROM a alias where alias.i=2; +delete alias FROM a alias where alias.i=2; select * from a; save_master_pos; connection slave; diff --git a/mysql-test/suite/sys_vars/r/group_concat_max_len_func.result b/mysql-test/suite/sys_vars/r/group_concat_max_len_func.result index 52d67b8d274..f04dfee583d 100644 --- a/mysql-test/suite/sys_vars/r/group_concat_max_len_func.result +++ b/mysql-test/suite/sys_vars/r/group_concat_max_len_func.result @@ -41,7 +41,10 @@ id rollno GROUP_CONCAT(name) 4 3 Reco 7 4 Reco Warnings: -Warning 1260 4 line(s) were cut by GROUP_CONCAT() +Warning 1260 Row 1 was cut by GROUP_CONCAT() +Warning 1260 Row 2 was cut by GROUP_CONCAT() +Warning 1260 Row 3 was cut by GROUP_CONCAT() +Warning 1260 Row 4 was cut by GROUP_CONCAT() ## Changing session value of variable and verifying its behavior, ## ## warning should come here ## SET @@session.group_concat_max_len = 10; @@ -52,7 +55,9 @@ id rollno GROUP_CONCAT(name) 4 3 Record_4,R 7 4 Record_7,R Warnings: -Warning 1260 3 line(s) were cut by GROUP_CONCAT() +Warning 1260 Row 2 was cut by GROUP_CONCAT() +Warning 1260 Row 5 was cut by GROUP_CONCAT() +Warning 1260 Row 7 was cut by GROUP_CONCAT() '#--------------------FN_DYNVARS_034_03-------------------------#' ## Connecting with new connection test_con2 ## ## Verifying initial value of variable. It should be 4 ## @@ -71,7 +76,7 @@ id rollno GROUP_CONCAT(name) 4 3 Record_4,Record_6 7 4 Record_7,Record_8 Warnings: -Warning 1260 1 line(s) were cut by GROUP_CONCAT() +Warning 1260 Row 3 was cut by GROUP_CONCAT() '#--------------------FN_DYNVARS_034_04-------------------------#' ## Setting session value of variable to 26. No warning should appear here ## ## because the value after concatination is less than 30 ## diff --git a/mysql-test/t/alias.test b/mysql-test/t/alias.test index 6546581eef2..0e2d57598e2 100644 --- a/mysql-test/t/alias.test +++ b/mysql-test/t/alias.test @@ -86,3 +86,132 @@ ASC LIMIT 1; drop table t1; # End of 4.1 tests + +# +# Bug#27249 table_wild with alias: select t1.* as something +# + +--disable_warnings +drop table if exists t1,t2,t3; +--enable_warnings + +create table t1 (a int, b int, c int); +create table t2 (d int); +create table t3 (a1 int, b1 int, c1 int); +insert into t1 values(1,2,3); +insert into t1 values(11,22,33); +insert into t2 values(99); + +# Invalid queries with alias on wild +--error ER_PARSE_ERROR +select t1.* as 'with_alias' from t1; +--error ER_PARSE_ERROR +select t2.* as 'with_alias' from t2; +--error ER_PARSE_ERROR +select t1.*, t1.* as 'with_alias' from t1; +--error ER_PARSE_ERROR +select t1.* as 'with_alias', t1.* from t1; +--error ER_PARSE_ERROR +select t1.* as 'with_alias', t1.* as 'alias2' from t1; +--error ER_PARSE_ERROR +select t1.* as 'with_alias', a, t1.* as 'alias2' from t1; + +# other fields without alias +--error ER_PARSE_ERROR +select a, t1.* as 'with_alias' from t1; +--error ER_PARSE_ERROR +select t1.* as 'with_alias', a from t1; +--error ER_PARSE_ERROR +select a, t1.* as 'with_alias', b from t1; +--error ER_PARSE_ERROR +select (select d from t2 where d > a), t1.* as 'with_alias' from t1; +--error ER_PARSE_ERROR +select t1.* as 'with_alias', (select a from t2 where d > a) from t1; + +# other fields with alias +--error ER_PARSE_ERROR +select a as 'x', t1.* as 'with_alias' from t1; +--error ER_PARSE_ERROR +select t1.* as 'with_alias', a as 'x' from t1; +--error ER_PARSE_ERROR +select a as 'x', t1.* as 'with_alias', b as 'x' from t1; +--error ER_PARSE_ERROR +select (select d from t2 where d > a) as 'x', t1.* as 'with_alias' from t1; +--error ER_PARSE_ERROR +select t1.* as 'with_alias', (select a from t2 where d > a) as 'x' from t1; + +# some more subquery +--error ER_PARSE_ERROR +select (select t2.* as 'x' from t2) from t1; +--error ER_PARSE_ERROR +select a, (select t2.* as 'x' from t2) from t1; +--error ER_PARSE_ERROR +select t1.*, (select t2.* as 'x' from t2) from t1; + +# insert +--error ER_PARSE_ERROR +insert into t3 select t1.* as 'with_alias' from t1; +--error ER_PARSE_ERROR +insert into t3 select t2.* as 'with_alias', 1, 2 from t2; +--error ER_PARSE_ERROR +insert into t3 select t2.* as 'with_alias', d as 'x', d as 'z' from t2; +--error ER_PARSE_ERROR +insert into t3 select t2.*, t2.* as 'with_alias', 3 from t2; + +# create +--error ER_PARSE_ERROR +create table t3 select t1.* as 'with_alias' from t1; +--error ER_PARSE_ERROR +create table t3 select t2.* as 'with_alias', 1, 2 from t2; +--error ER_PARSE_ERROR +create table t3 select t2.* as 'with_alias', d as 'x', d as 'z' from t2; +--error ER_PARSE_ERROR +create table t3 select t2.*, t2.* as 'with_alias', 3 from t2; + +# +# Valid queries without alias on wild +# (proof the above fail due to invalid aliasing) +# + +select t1.* from t1; +select t2.* from t2; +select t1.*, t1.* from t1; +select t1.*, a, t1.* from t1; + +# other fields without alias +select a, t1.* from t1; +select t1.*, a from t1; +select a, t1.*, b from t1; +select (select d from t2 where d > a), t1.* from t1; +select t1.*, (select a from t2 where d > a) from t1; + +# other fields with alias +select a as 'x', t1.* from t1; +select t1.*, a as 'x' from t1; +select a as 'x', t1.*, b as 'x' from t1; +select (select d from t2 where d > a) as 'x', t1.* from t1; +select t1.*, (select a from t2 where d > a) as 'x' from t1; + +# some more subquery +select (select t2.* from t2) from t1; +select a, (select t2.* from t2) from t1; +select t1.*, (select t2.* from t2) from t1; + +# insert +insert into t3 select t1.* from t1; +insert into t3 select t2.*, 1, 2 from t2; +insert into t3 select t2.*, d as 'x', d as 'z' from t2; +insert into t3 select t2.*, t2.*, 3 from t2; + +# create +create table t4 select t1.* from t1; +drop table t4; +create table t4 select t2.*, 1, 2 from t2; +drop table t4; +create table t4 select t2.*, d as 'x', d as 'z' from t2; +drop table t4; + +# end +drop table t1,t2,t3; + +# End of 5.2 tests diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test index d77f5eb128b..81cadb432af 100644 --- a/mysql-test/t/delete.test +++ b/mysql-test/t/delete.test @@ -265,8 +265,8 @@ DELETE FROM t1, alias USING t1, t2 alias WHERE t1.a = alias.a; DELETE FROM t1, t2 USING t1, t2 alias WHERE t1.a = alias.a; --error ER_PARSE_ERROR DELETE FROM db1.t1 alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a; ---error ER_UNKNOWN_TABLE DELETE FROM alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a; +--error ER_UNKNOWN_TABLE DELETE FROM db2.alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a; DELETE FROM t1 USING t1 WHERE a = 1; SELECT * FROM t1; @@ -293,6 +293,159 @@ DROP FUNCTION f1; --echo End of 5.0 tests +# +# Bug#27525: table not found when using multi-table-deletes with aliases over +# several databas +# Bug#21148: MULTI-DELETE fails to resolve a table by alias if it's from a +# different database +# + +--disable_warnings +DROP DATABASE IF EXISTS db1; +DROP DATABASE IF EXISTS db2; +DROP DATABASE IF EXISTS db3; +DROP DATABASE IF EXISTS db4; +DROP TABLE IF EXISTS t1, t2; +DROP PROCEDURE IF EXISTS count; +--enable_warnings +USE test; +CREATE DATABASE db1; +CREATE DATABASE db2; + +CREATE TABLE db1.t1 (a INT, b INT); +INSERT INTO db1.t1 VALUES (1,1),(2,2),(3,3); +CREATE TABLE db1.t2 AS SELECT * FROM db1.t1; +CREATE TABLE db2.t1 AS SELECT * FROM db1.t2; +CREATE TABLE db2.t2 AS SELECT * FROM db2.t1; +CREATE TABLE t1 AS SELECT * FROM db2.t2; +CREATE TABLE t2 AS SELECT * FROM t1; + +delimiter |; +CREATE PROCEDURE count_rows() +BEGIN + SELECT COUNT(*) AS "COUNT(db1.t1)" FROM db1.t1; + SELECT COUNT(*) AS "COUNT(db1.t2)" FROM db1.t2; + SELECT COUNT(*) AS "COUNT(db2.t1)" FROM db2.t1; + SELECT COUNT(*) AS "COUNT(db2.t2)" FROM db2.t2; + SELECT COUNT(*) AS "COUNT(test.t1)" FROM test.t1; + SELECT COUNT(*) AS "COUNT(test.t2)" FROM test.t2; +END| +delimiter ;| + +# +# Testing without a selected database +# + +CREATE DATABASE db3; +USE db3; +DROP DATABASE db3; +--error ER_NO_DB_ERROR +SELECT * FROM t1; + +# Detect missing table references + +--error ER_NO_DB_ERROR +DELETE a1,a2 FROM db1.t1, db2.t2; +--error ER_NO_DB_ERROR +DELETE a1,a2 FROM db1.t1, db2.t2; +--error ER_NO_DB_ERROR +DELETE a1,a2 FROM db1.t1 AS a1, db2.t2; +--error ER_NO_DB_ERROR +DELETE a1,a2 FROM db1.t1, db2.t2 AS a2; +--error ER_NO_DB_ERROR +DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2; +--error ER_NO_DB_ERROR +DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2; + +--error ER_NO_DB_ERROR +DELETE FROM a1,a2 USING db1.t1, db2.t2; +--error ER_NO_DB_ERROR +DELETE FROM a1,a2 USING db1.t1, db2.t2; +--error ER_NO_DB_ERROR +DELETE FROM a1,a2 USING db1.t1 AS a1, db2.t2; +--error ER_NO_DB_ERROR +DELETE FROM a1,a2 USING db1.t1, db2.t2 AS a2; +--error ER_NO_DB_ERROR +DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2; +--error ER_NO_DB_ERROR +DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2; + +# Ambiguous table references + +--error ER_NO_DB_ERROR +DELETE a1 FROM db1.t1 AS a1, db2.t2 AS a1; +--error ER_NO_DB_ERROR +DELETE a1 FROM db1.a1, db2.t2 AS a1; +--error ER_NO_DB_ERROR +DELETE a1 FROM a1, db1.t1 AS a1; +--error ER_NO_DB_ERROR +DELETE t1 FROM db1.t1, db2.t1 AS a1; +--error ER_NO_DB_ERROR +DELETE t1 FROM db1.t1 AS a1, db2.t1 AS a2; +--error ER_NO_DB_ERROR +DELETE t1 FROM db1.t1, db2.t1; + +# Test all again, now with a selected database + +USE test; + +# Detect missing table references + +--error ER_UNKNOWN_TABLE +DELETE a1,a2 FROM db1.t1, db2.t2; +--error ER_UNKNOWN_TABLE +DELETE a1,a2 FROM db1.t1, db2.t2; +--error ER_UNKNOWN_TABLE +DELETE a1,a2 FROM db1.t1 AS a1, db2.t2; +--error ER_UNKNOWN_TABLE +DELETE a1,a2 FROM db1.t1, db2.t2 AS a2; +--error ER_NO_SUCH_TABLE +DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2; +--error ER_NO_SUCH_TABLE +DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2; + +--error ER_UNKNOWN_TABLE +DELETE FROM a1,a2 USING db1.t1, db2.t2; +--error ER_UNKNOWN_TABLE +DELETE FROM a1,a2 USING db1.t1, db2.t2; +--error ER_UNKNOWN_TABLE +DELETE FROM a1,a2 USING db1.t1 AS a1, db2.t2; +--error ER_UNKNOWN_TABLE +DELETE FROM a1,a2 USING db1.t1, db2.t2 AS a2; +--error ER_NO_SUCH_TABLE +DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2; +--error ER_NO_SUCH_TABLE +DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2; + +# Ambiguous table references + +--error ER_NONUNIQ_TABLE +DELETE a1 FROM db1.t1 AS a1, db2.t2 AS a1; +--error ER_NO_SUCH_TABLE +DELETE a1 FROM db1.a1, db2.t2 AS a1; +--error ER_NONUNIQ_TABLE +DELETE a1 FROM a1, db1.t1 AS a1; +--error ER_UNKNOWN_TABLE +DELETE t1 FROM db1.t1, db2.t1 AS a1; +--error ER_UNKNOWN_TABLE +DELETE t1 FROM db1.t1 AS a1, db2.t1 AS a2; +--error ER_UNKNOWN_TABLE +DELETE t1 FROM db1.t1, db2.t1; + +# Test multiple-table cross database deletes + +DELETE t1 FROM db1.t2 AS t1, db2.t2 AS t2 WHERE t2.a = 1 AND t1.a = t2.a; +SELECT ROW_COUNT(); +CALL count_rows(); +DELETE a1, a2 FROM db2.t1 AS a1, t2 AS a2 WHERE a1.a = 2 AND a2.a = 2; +SELECT ROW_COUNT(); +CALL count_rows(); + +DROP DATABASE db1; +DROP DATABASE db2; +DROP PROCEDURE count_rows; +DROP TABLE t1, t2; + --echo # --echo # Bug#46958: Assertion in Diagnostics_area::set_ok_status, trigger, --echo # merge table diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index d28c19bbd18..27e85ee237b 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -158,7 +158,7 @@ UPDATE `t1` AS P1 INNER JOIN (SELECT aaaa FROM `t1` GROUP BY N HAVING Count(M) > delete P1.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N; select * from t1; --replace_result P2 p2 ---error ER_UNKNOWN_TABLE +--error ER_NON_UPDATABLE_TABLE delete P1.*,P2.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N; -- error 1054 delete P1.* from `t1` AS P1 INNER JOIN (SELECT aaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N; diff --git a/mysql-test/t/dirty_close.test b/mysql-test/t/dirty_close.test index 1bbd53e8c06..e49618170ab 100644 --- a/mysql-test/t/dirty_close.test +++ b/mysql-test/t/dirty_close.test @@ -1,3 +1,4 @@ +--source include/not_embedded.inc # Save the initial number of concurrent sessions --source include/count_sessions.inc @@ -22,6 +23,38 @@ disconnect con2; # End of 4.1 tests +# +# Bug#10374 GET_LOCK does not let connection to close on the server side if it's aborted +# + +connection default; +SELECT GET_LOCK("dangling", 0); +connect(con1, localhost, root,,); +connection con1; +--send SELECT GET_LOCK('dangling', 3600); +connection default; +let $wait_condition= + SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE STATE = "User lock" + AND INFO = "SELECT GET_LOCK('dangling', 3600)"; +--source include/wait_condition.inc +dirty_close con1; +let $wait_condition= + SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE STATE = "User lock" + AND INFO = "SELECT GET_LOCK('dangling', 3600)"; +--source include/wait_condition.inc +connect(con1, localhost, root,,); +--send SELECT GET_LOCK('dangling', 3600); +connection default; +let $wait_condition= + SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE STATE = "User lock" + AND INFO = "SELECT GET_LOCK('dangling', 3600)"; +--source include/wait_condition.inc +SELECT RELEASE_LOCK('dangling'); +connection con1; +--reap +connection default; +disconnect con1; + # Wait till all disconnects are completed --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/func_sapdb.test b/mysql-test/t/func_sapdb.test index 1292c475732..89eae5955aa 100644 --- a/mysql-test/t/func_sapdb.test +++ b/mysql-test/t/func_sapdb.test @@ -169,4 +169,26 @@ SELECT TIMEDIFF(TIME('17:00:00'),TIME('17:00:00'))=TIME('00:00:00') AS 1Eq, TIMEDIFF(TIME('17:59:00'),TIME('17:00:00')), TIMEDIFF(TIME('17:00:00'),TIME('17:59:00')); +# +# Bug#42661 - sec_to_time() and signedness +# + +SELECT sec_to_time(3020399)=TIME('838:59:59'); +SELECT sec_to_time(-3020399)=TIME('-838:59:59'); +SELECT sec_to_time(-3020399)='-838:59:59'; +SELECT time(sec_to_time(-3020399))=TIME('-838:59:59'); +SELECT time(sec_to_time(-3020399))=TIME('-838:59:58'); + +# +# Bug#42662 - maketime() and signedness +# + +# TIME(...) and CAST(... AS TIME) go through the same code-path here, +# but we'll explicitly show show that both work in case the ever changes. +SELECT maketime(-1,0,1)='-01:00:01'; +SELECT TIME(maketime(-1,0,1))=CAST('-01:00:01' AS TIME); +SELECT maketime(-1,0,1)=CAST('-01:00:01' AS TIME); +SELECT maketime(1,0,1)=CAST('01:00:01' AS TIME); +SELECT maketime(1,0,1)=CAST('01:00:02' AS TIME); + # End of 5.0 tests diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index b9da946a55f..95b8a8ec38d 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -819,6 +819,16 @@ SELECT '2008-02-18' + INTERVAL 1 FRAC_SECOND; --error ER_PARSE_ERROR SELECT '2008-02-18' - INTERVAL 1 FRAC_SECOND; +# +# Bug #36466: +# Adding days to day_microsecond changes interpretation of microseconds +# + +# show that we treat fractions of seconds correctly (zerofill from right to +# six places) even if we left out fields on the left. +select date_add('1000-01-01 00:00:00', interval '1.03:02:01.05' day_microsecond); +select date_add('1000-01-01 00:00:00', interval '1.02' day_microsecond); + --echo End of 5.0 tests # diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test index 47b5aa0292b..dbaa96b0374 100644 --- a/mysql-test/t/lock_multi.test +++ b/mysql-test/t/lock_multi.test @@ -1,5 +1,3 @@ --- source include/not_embedded.inc - # Save the initial number of concurrent sessions --source include/count_sessions.inc diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test index 6e74e065720..8cc12c6a41f 100644 --- a/mysql-test/t/mysql.test +++ b/mysql-test/t/mysql.test @@ -367,6 +367,14 @@ remove_file $MYSQLTEST_VARDIR/tmp/bug31060.sql; --enable_query_log # +# Bug#26780: patch to add auto vertical output option to the cli. +# +# Make this wide enough that it will wrap almost everywhere. +--exec $MYSQL test --auto-vertical-output --table -e "SELECT 1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0;" +# Too short to wrap. +--exec $MYSQL test --auto-vertical-output --table -e "SELECT 1;" + +# # Bug #25146: Some warnings/errors not shown when using --show-warnings # diff --git a/mysql-test/t/partition_sync.test b/mysql-test/t/partition_sync.test new file mode 100644 index 00000000000..a732b35b8b9 --- /dev/null +++ b/mysql-test/t/partition_sync.test @@ -0,0 +1,41 @@ +--source include/have_partition.inc +# Save the initial number of concurrent sessions. +--source include/count_sessions.inc + +--echo # +--echo # Bug #43867 ALTER TABLE on a partitioned table +--echo # causes unnecessary deadlocks +--echo # + +CREATE TABLE t1 (a int) PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (1), + PARTITION p1 VALUES LESS THAN (2)); + +INSERT INTO t1 VALUES (0),(1); + +connect(con1,localhost,root); + +--echo # Connection 2 +connection con1; +BEGIN; +SELECT * FROM t1; + +--echo # Connection 1 +connection default; +--error ER_DROP_PARTITION_NON_EXISTENT +ALTER TABLE t1 DROP PARTITION p3; + +--echo # Connection 2 +connection con1; +--echo # This failed with deadlock and should not do so. +SELECT * FROM t1; + +--echo # Connection 1 +connection default; +disconnect con1; +DROP TABLE t1; + + +# Check that all connections opened by test cases in this file are really +# gone so execution of other tests won't be affected by their presence. +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/rpl_mysqldump_slave.test b/mysql-test/t/rpl_mysqldump_slave.test new file mode 100644 index 00000000000..5723f1282aa --- /dev/null +++ b/mysql-test/t/rpl_mysqldump_slave.test @@ -0,0 +1,25 @@ +source include/master-slave.inc; + +--echo # +--echo # New --dump-slave, --apply-slave-statements functionality +--echo # + +# There is a gap between when START SLAVE returns and when MASTER_LOG_FILE and +# MASTER_LOG_POS are set. Ensure that we don't call SHOW SLAVE STATUS during +# that gap. +--sync_slave_with_master + +connection master; +use test; + +connection slave; + +# Execute mysqldump with --dump-slave +--exec $MYSQL_DUMP_SLAVE --compact --dump-slave test + +# Execute mysqldump with --dump-slave and --apply-slave-statements +--exec $MYSQL_DUMP_SLAVE --compact --dump-slave --apply-slave-statements test + +--replace_result $MASTER_MYPORT MASTER_MYPORT +# Execute mysqldump with --dump-slave ,--apply-slave-statements and --include-master-host-port +--exec $MYSQL_DUMP_SLAVE --compact --dump-slave --apply-slave-statements --include-master-host-port test diff --git a/mysql-test/t/shm.test b/mysql-test/t/shm.test index 88e96ae7b45..567caa4989a 100644 --- a/mysql-test/t/shm.test +++ b/mysql-test/t/shm.test @@ -7,10 +7,17 @@ let $shm= query_get_value("SHOW VARIABLES LIKE 'shared_memory'", Value, 1); if (`SELECT '$shm' != 'ON'`){ skip No shm support; } +let $shm_name= query_get_value("SHOW GLOBAL VARIABLES LIKE 'shared_memory_base_name'", Value, 1); + +# Connect using SHM for testing +connect(shm_con,localhost,root,,,,$shm_name,SHM); # Source select test case -- source include/common-tests.inc +connection default; +disconnect shm_con; + # # Bug #24924: shared-memory-base-name that is too long causes buffer overflow # @@ -20,7 +27,6 @@ if (`SELECT '$shm' != 'ON'`){ # Bug #33899: Deadlock in mysql_real_query with shared memory connections # -let $name= query_get_value("SHOW GLOBAL VARIABLES LIKE 'shared_memory_base_name'", Value, 1); let $stmt= `SELECT REPEAT('a', 2048)`; SET @max_allowed_packet= @@global.max_allowed_packet; @@ -30,7 +36,7 @@ SET GLOBAL max_allowed_packet= 1024; SET GLOBAL net_buffer_length= 1024; --error 1 ---exec echo SELECT '$stmt'| $MYSQL --protocol=memory --shared-memory-base-name=$name 2>&1 +--exec echo SELECT '$stmt'| $MYSQL --protocol=memory --shared-memory-base-name=$shm_name 2>&1 SET GLOBAL max_allowed_packet= @max_allowed_packet; SET GLOBAL net_buffer_length= @net_buffer_length; diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index 18a4a117939..b022ca4b0e1 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -2419,6 +2419,27 @@ end$$ delimiter ;$$ +# +# Bug#15192: "fatal errors" are caught by handlers in stored procedures +# + +--disable_warnings +drop procedure if exists p1; +--enable_warnings +set @old_recursion_depth = @@max_sp_recursion_depth; +set @@max_sp_recursion_depth = 255; +delimiter |; +create procedure p1(a int) +begin + declare continue handler for 1436 -- ER_STACK_OVERRUN_NEED_MORE + select 'exception'; + call p1(a+1); +end| +delimiter ;| +--error 0,ER_STACK_OVERRUN_NEED_MORE,ER_SP_RECURSION_LIMIT +call p1(1); +set @@max_sp_recursion_depth = @old_recursion_depth; +drop procedure p1; # # BUG#NNNN: New bug synopsis diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index eef843ded59..a9825d13f66 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -8295,6 +8295,119 @@ SELECT * FROM t1 WHERE a = f1(); DROP FUNCTION f1; DROP TABLE t1; +# +# Bug#36649: Condition area is not properly cleaned up after stored routine invocation +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +delimiter |; +CREATE PROCEDURE p1(a INT, b CHAR) +BEGIN + IF a > 0 THEN + CALL p1(a-1, 'ab'); + ELSE + SELECT 1; + END IF; +END| +delimiter ;| + +SET @save_max_sp_recursion= @@max_sp_recursion_depth; +SET @@max_sp_recursion_depth= 5; +CALL p1(4, 'a'); +SET @@max_sp_recursion_depth= @save_max_sp_recursion; + +DROP PROCEDURE p1; + +# +# Ensure that rules for message list clean up are being respected. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +delimiter |; +CREATE PROCEDURE p1(a CHAR) +BEGIN + SELECT 1; + SELECT CAST('10 ' as UNSIGNED INTEGER); + SELECT 1; +END| +delimiter ;| + +CALL p1('data truncated parameter'); + +DROP PROCEDURE p1; + +# +# Cascading stored procedure/function calls. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +DROP PROCEDURE IF EXISTS p3; +DROP PROCEDURE IF EXISTS p4; +--enable_warnings + +delimiter |; +CREATE PROCEDURE p1() + CALL p2()| +CREATE PROCEDURE p2() + CALL p3()| +CREATE PROCEDURE p3() + CALL p4()| +CREATE PROCEDURE p4() +BEGIN + SELECT 1; + SELECT CAST('10 ' as UNSIGNED INTEGER); + SELECT 2; +END| +delimiter ;| + +CALL p1(); + +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP PROCEDURE p3; +DROP PROCEDURE p4; + +--disable_warnings +DROP FUNCTION IF EXISTS f1; +DROP FUNCTION IF EXISTS f2; +DROP FUNCTION IF EXISTS f3; +DROP FUNCTION IF EXISTS f4; +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (a CHAR(2)); + +INSERT INTO t1 VALUES ('aa'); + +delimiter |; +CREATE FUNCTION f1() RETURNS CHAR + RETURN (SELECT f2())| +CREATE FUNCTION f2() RETURNS CHAR + RETURN (SELECT f3())| +CREATE FUNCTION f3() RETURNS CHAR + RETURN (SELECT f4())| +CREATE FUNCTION f4() RETURNS CHAR +BEGIN + RETURN (SELECT a FROM t1); +END| +delimiter ;| + +SELECT f1(); + +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP FUNCTION f3; +DROP FUNCTION f4; +DROP TABLE t1; + --echo # --echo # Bug#34197: CREATE PROCEDURE fails when COMMENT truncated in non --echo # strict SQL mode diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test index 3ab724a835a..f3b9d6bb91e 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -2425,3 +2425,67 @@ DELETE FROM t1; DROP TABLE t1; DROP TEMPORARY TABLE t2; + +# +# Bug#36649: Condition area is not properly cleaned up after stored routine invocation +# + +--disable_warnings +DROP TRIGGER IF EXISTS trg1; +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (a INT); + +delimiter |; +CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW +BEGIN + DECLARE a CHAR; + SELECT 'ab' INTO a; + SELECT 'ab' INTO a; + SELECT 'a' INTO a; +END| +delimiter ;| + +INSERT INTO t1 VALUES (1); + +DROP TRIGGER trg1; +DROP TABLE t1; + +# +# Successive trigger actuations +# + +--disable_warnings +DROP TRIGGER IF EXISTS trg1; +DROP TRIGGER IF EXISTS trg2; +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (a INT); + +delimiter |; + +CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW +BEGIN + DECLARE trg1 CHAR; + SELECT 'ab' INTO trg1; +END| + +CREATE TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW +BEGIN + DECLARE trg2 CHAR; + SELECT 'ab' INTO trg2; +END| + +delimiter ;| + +INSERT INTO t1 VALUES (0); +SELECT * FROM t1; +SHOW WARNINGS; +INSERT INTO t1 VALUES (1),(2); + +DROP TRIGGER trg1; +DROP TRIGGER trg2; +DROP TABLE t1; + diff --git a/mysql-test/t/type_time.test b/mysql-test/t/type_time.test index 5bb521601e5..3cec27d7782 100644 --- a/mysql-test/t/type_time.test +++ b/mysql-test/t/type_time.test @@ -39,7 +39,7 @@ drop table t1; # SELECT CAST(0.2359591234567e+30 AS TIME); # ########################################################## -# End of 4.1 tests +--echo End of 4.1 tests # # Bug#29555: Comparing time values as strings may lead to a wrong result. @@ -90,3 +90,22 @@ DROP TABLE t1; --echo End of 5.0 tests + + +# +# Bug#42664 - Sign ignored for TIME types when not comparing as longlong +# + +CREATE TABLE t1 (f1 TIME); +INSERT INTO t1 VALUES ('24:00:00'); +SELECT '24:00:00' = (SELECT f1 FROM t1); +SELECT CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1); +SELECT CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1); +TRUNCATE t1; +INSERT INTO t1 VALUES ('-24:00:00'); +SELECT CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1); +SELECT CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1); +SELECT '-24:00:00' = (SELECT f1 FROM t1); +DROP TABLE t1; + +--echo End of 6.0 tests |