diff options
Diffstat (limited to 'mysql-test/r')
25 files changed, 884 insertions, 12 deletions
diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result index d8e50128902..6eceeff87e2 100644 --- a/mysql-test/r/cast.result +++ b/mysql-test/r/cast.result @@ -103,7 +103,7 @@ Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'a' select 10.0+cast('a' as decimal); 10.0+cast('a' as decimal) -10.00 +10.0 Warnings: Warning 1292 Truncated incorrect DECIMAL value: 'a' select 10E+0+'a'; @@ -351,6 +351,12 @@ DROP TABLE t1; select isnull(date(NULL)), isnull(cast(NULL as DATE)); isnull(date(NULL)) isnull(cast(NULL as DATE)) 1 1 +SELECT CAST(cast('01-01-01' as date) AS UNSIGNED); +CAST(cast('01-01-01' as date) AS UNSIGNED) +20010101 +SELECT CAST(cast('01-01-01' as date) AS SIGNED); +CAST(cast('01-01-01' as date) AS SIGNED) +20010101 End of 4.1 tests select cast('1.2' as decimal(3,2)); cast('1.2' as decimal(3,2)) @@ -372,7 +378,9 @@ create table t1(s1 time); insert into t1 values ('11:11:11'); select cast(s1 as decimal(7,2)) from t1; cast(s1 as decimal(7,2)) -111111.00 +99999.99 +Warnings: +Error 1264 Out of range value adjusted for column 'cast(s1 as decimal(7,2))' at row 1 drop table t1; CREATE TABLE t1 (v varchar(10), tt tinytext, t text, mt mediumtext, lt longtext); @@ -380,7 +388,7 @@ INSERT INTO t1 VALUES ('1.01', '2.02', '3.03', '4.04', '5.05'); SELECT CAST(v AS DECIMAL), CAST(tt AS DECIMAL), CAST(t AS DECIMAL), CAST(mt AS DECIMAL), CAST(lt AS DECIMAL) from t1; CAST(v AS DECIMAL) CAST(tt AS DECIMAL) CAST(t AS DECIMAL) CAST(mt AS DECIMAL) CAST(lt AS DECIMAL) -1.01 2.02 3.03 4.04 5.05 +1 2 3 4 5 DROP TABLE t1; select cast(NULL as decimal(6)) as t1; t1 diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index afa005e74c0..e1262c7d2c2 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -769,6 +769,100 @@ t1 CREATE TABLE `t1` ( `i` int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=4294967295 drop table t1; +create table t1 select * from t2; +ERROR 42S02: Table 'test.t2' doesn't exist +create table t1 select * from t1; +ERROR HY000: You can't specify target table 't1' for update in FROM clause +create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin); +ERROR HY000: Illegal mix of collations (latin1_swedish_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'coalesce' +create table t1 (primary key(a)) select "b" as b; +ERROR 42000: Key column 'a' doesn't exist in table +create table t1 (a int); +create table if not exists t1 select 1 as a, 2 as b; +ERROR 21S01: Column count doesn't match value count at row 1 +drop table t1; +create table t1 (primary key (a)) (select 1 as a) union all (select 1 as a); +ERROR 23000: Duplicate entry '1' for key 1 +create table t1 (i int); +create table t1 select 1 as i; +ERROR 42S01: Table 't1' already exists +create table if not exists t1 select 1 as i; +Warnings: +Note 1050 Table 't1' already exists +select * from t1; +i +1 +create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin); +ERROR HY000: Illegal mix of collations (latin1_swedish_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'coalesce' +select * from t1; +i +1 +alter table t1 add primary key (i); +create table if not exists t1 (select 2 as i) union all (select 2 as i); +ERROR 23000: Duplicate entry '2' for key 1 +select * from t1; +i +1 +2 +drop table t1; +create temporary table t1 (j int); +create table if not exists t1 select 1; +Warnings: +Note 1050 Table 't1' already exists +select * from t1; +j +1 +drop temporary table t1; +select * from t1; +ERROR 42S02: Table 'test.t1' doesn't exist +drop table t1; +ERROR 42S02: Unknown table 't1' +create table t1 (i int); +insert into t1 values (1), (2); +lock tables t1 read; +create table t2 select * from t1; +ERROR HY000: Table 't2' was not locked with LOCK TABLES +create table if not exists t2 select * from t1; +ERROR HY000: Table 't2' was not locked with LOCK TABLES +unlock tables; +create table t2 (j int); +lock tables t1 read; +create table t2 select * from t1; +ERROR HY000: Table 't2' was not locked with LOCK TABLES +create table if not exists t2 select * from t1; +ERROR HY000: Table 't2' was not locked with LOCK TABLES +unlock tables; +lock table t1 read, t2 read; +create table t2 select * from t1; +ERROR HY000: Table 't2' was locked with a READ lock and can't be updated +create table if not exists t2 select * from t1; +ERROR HY000: Table 't2' was locked with a READ lock and can't be updated +unlock tables; +lock table t1 read, t2 write; +create table t2 select * from t1; +ERROR 42S01: Table 't2' already exists +create table if not exists t2 select * from t1; +Warnings: +Note 1050 Table 't2' already exists +select * from t1; +i +1 +2 +unlock tables; +drop table t2; +lock tables t1 read; +create temporary table t2 select * from t1; +create temporary table if not exists t2 select * from t1; +Warnings: +Note 1050 Table 't2' already exists +select * from t2; +i +1 +2 +1 +2 +unlock tables; +drop table t1, t2; create table t1 (upgrade int); drop table t1; End of 5.0 tests diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index 87855091699..a3e0773649f 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -467,6 +467,7 @@ CREATE TABLE t4 (a DATE); INSERT INTO t4 VALUES ('1972-02-06'), ('1972-07-29'); SELECT * FROM t4 WHERE a IN ('1972-02-06','19772-07-29'); a +1972-02-06 Warnings: Warning 1292 Incorrect date value: '19772-07-29' for column 'a' at row 1 DROP TABLE t1,t2,t3,t4; diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index 6d014fbb71b..a4c51cca277 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -1105,4 +1105,23 @@ ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table ' DROP DATABASE mysqltest1; DROP DATABASE mysqltest2; DROP USER mysqltest_1@localhost; +use test; +CREATE TABLE t1 (f1 int, f2 int); +INSERT INTO t1 VALUES(1,1), (2,2); +CREATE DATABASE db27878; +GRANT UPDATE(f1) ON t1 TO 'mysqltest_1'@'localhost'; +GRANT SELECT ON `test`.* TO 'mysqltest_1'@'localhost'; +GRANT ALL ON db27878.* TO 'mysqltest_1'@'localhost'; +use db27878; +CREATE SQL SECURITY INVOKER VIEW db27878.v1 AS SELECT * FROM test.t1; +use db27878; +UPDATE v1 SET f2 = 4; +ERROR HY000: View 'db27878.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SELECT * FROM test.t1; +f1 f2 +1 1 +2 2 +DROP DATABASE db27878; +use test; +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 009ae8776c3..48e01d8dd6f 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -544,4 +544,56 @@ id c counter 3 b 2 4 a 2 drop table t1; +CREATE TABLE t1( +id int AUTO_INCREMENT PRIMARY KEY, +stat_id int NOT NULL, +acct_id int DEFAULT NULL, +INDEX idx1 (stat_id, acct_id), +INDEX idx2 (acct_id) +) ENGINE=MyISAM; +CREATE TABLE t2( +id int AUTO_INCREMENT PRIMARY KEY, +stat_id int NOT NULL, +acct_id int DEFAULT NULL, +INDEX idx1 (stat_id, acct_id), +INDEX idx2 (acct_id) +) ENGINE=InnoDB; +INSERT INTO t1(stat_id,acct_id) VALUES +(1,759), (2,831), (3,785), (4,854), (1,921), +(1,553), (2,589), (3,743), (2,827), (2,545), +(4,779), (4,783), (1,597), (1,785), (4,832), +(1,741), (1,833), (3,788), (2,973), (1,907); +INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; +INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; +INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; +INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; +INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; +INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; +INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; +INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; +INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; +INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; +INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; +UPDATE t1 SET acct_id=785 +WHERE MOD(stat_id,2)=0 AND MOD(id,stat_id)=MOD(acct_id,stat_id); +OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +SELECT COUNT(*) FROM t1; +COUNT(*) +40960 +SELECT COUNT(*) FROM t1 WHERE acct_id=785; +COUNT(*) +8702 +EXPLAIN SELECT COUNT(*) FROM t1 WHERE stat_id IN (1,3) AND acct_id=785; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx1,idx2 idx1 9 NULL 2 Using where; Using index +INSERT INTO t2 SELECT * FROM t1; +OPTIMIZE TABLE t2; +Table Op Msg_type Msg_text +test.t2 optimize status OK +EXPLAIN SELECT COUNT(*) FROM t2 WHERE stat_id IN (1,3) AND acct_id=785; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range idx1,idx2 idx1 9 NULL 2 Using where; Using index +DROP TABLE t1,t2; End of 5.0 tests diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result index 7900e0b7695..fa6e23d09f9 100644 --- a/mysql-test/r/insert.result +++ b/mysql-test/r/insert.result @@ -346,3 +346,119 @@ f1 f2 12 NULL drop view v1; drop table t1,t2; +DROP TABLE IF EXISTS t1; +DROP FUNCTION IF EXISTS f1; +DROP FUNCTION IF EXISTS f2; +CREATE TABLE t1 (i INT); +CREATE FUNCTION f1() RETURNS INT +BEGIN +INSERT INTO t1 VALUES (1); +RETURN 1; +END | +CREATE FUNCTION f2() RETURNS INT +BEGIN +INSERT DELAYED INTO t1 VALUES (2); +RETURN 1; +END | +SELECT f1(); +f1() +1 +SELECT f2(); +f2() +1 +INSERT INTO t1 VALUES (3); +INSERT DELAYED INTO t1 VALUES (4); +INSERT INTO t1 VALUES (f1()); +ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. +INSERT DELAYED INTO t1 VALUES (f1()); +ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. +INSERT INTO t1 VALUES (f2()); +ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. +INSERT DELAYED INTO t1 VALUES (f2()); +ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW +INSERT INTO t1 VALUES (NEW.i); +INSERT INTO t1 VALUES (1); +ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. +INSERT DELAYED INTO t1 VALUES (1); +ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. +SELECT * FROM t1; +i +1 +2 +3 +4 +DROP FUNCTION f2; +DROP FUNCTION f1; +DROP TABLE t1; +DROP TABLE IF EXISTS t1, t2; +CREATE TABLE t1 (i INT); +CREATE TABLE t2 (i INT); +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW +INSERT DELAYED INTO t2 VALUES (NEW.i); +CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW +INSERT DELAYED INTO t2 VALUES (NEW.i); +CREATE TRIGGER t1_bd BEFORE DELETE ON t1 FOR EACH ROW +INSERT DELAYED INTO t2 VALUES (OLD.i); +INSERT INTO t1 VALUES (1); +INSERT DELAYED INTO t1 VALUES (2); +SELECT * FROM t1; +i +1 +2 +UPDATE t1 SET i = 3 WHERE i = 1; +SELECT * FROM t1; +i +3 +2 +DELETE FROM t1 WHERE i = 3; +SELECT * FROM t1; +i +2 +SELECT * FROM t2; +i +1 +2 +3 +3 +DROP TABLE t1, t2; +DROP TABLE IF EXISTS t1, t2; +CREATE TABLE t1 (i INT); +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW +SET @a= NEW.i; +SET @a= 0; +INSERT DELAYED INTO t1 VALUES (1); +SELECT @a; +@a +1 +INSERT DELAYED INTO t1 VALUES (2); +SELECT @a; +@a +2 +DROP TABLE t1; +CREATE TABLE t1 (i INT); +CREATE TABLE t2 (i INT); +CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW +INSERT INTO t2 VALUES (NEW.i); +CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW +INSERT DELAYED INTO t2 VALUES (NEW.i); +CREATE TRIGGER t1_ad AFTER DELETE ON t1 FOR EACH ROW +INSERT DELAYED INTO t2 VALUES (OLD.i); +INSERT DELAYED INTO t1 VALUES (1); +SELECT * FROM t1; +i +1 +UPDATE t1 SET i = 2 WHERE i = 1; +SELECT * FROM t1; +i +2 +DELETE FROM t1 WHERE i = 2; +SELECT * FROM t1; +i +SELECT * FROM t2; +i +1 +2 +2 +DROP TABLE t1, t2; +End of 5.0 tests. diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result index fd70fcb9084..375961292a3 100644 --- a/mysql-test/r/insert_update.result +++ b/mysql-test/r/insert_update.result @@ -336,3 +336,60 @@ id f1 0 test1 DROP TABLE t1; SET SQL_MODE=''; +CREATE TABLE t1 ( +id INT AUTO_INCREMENT PRIMARY KEY, +c1 CHAR(1) UNIQUE KEY, +cnt INT DEFAULT 1 +); +INSERT INTO t1 (c1) VALUES ('A'), ('B'), ('C'); +SELECT * FROM t1; +id c1 cnt +1 A 1 +2 B 1 +3 C 1 +INSERT INTO t1 (c1) VALUES ('A'), ('X'), ('Y'), ('Z') +ON DUPLICATE KEY UPDATE cnt=cnt+1; +SELECT * FROM t1; +id c1 cnt +1 A 2 +2 B 1 +3 C 1 +4 X 1 +5 Y 1 +6 Z 1 +DROP TABLE t1; +CREATE TABLE t1 ( +id INT AUTO_INCREMENT PRIMARY KEY, +c1 INT NOT NULL, +cnt INT DEFAULT 1 +); +INSERT INTO t1 (id,c1) VALUES (1,10); +SELECT * FROM t1; +id c1 cnt +1 10 1 +CREATE TABLE t2 (id INT, c1 INT); +INSERT INTO t2 VALUES (1,NULL), (2,2); +INSERT INTO t1 (id,c1) SELECT 1,NULL +ON DUPLICATE KEY UPDATE c1=NULL; +ERROR 23000: Column 'c1' cannot be null +SELECT * FROM t1; +id c1 cnt +1 10 1 +INSERT IGNORE INTO t1 (id,c1) SELECT 1,NULL +ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1; +Warnings: +Warning 1263 Column was set to data type implicit default; NULL supplied for NOT NULL column 'c1' at row 1 +Error 1048 Column 'c1' cannot be null +SELECT * FROM t1; +id c1 cnt +1 0 2 +INSERT IGNORE INTO t1 (id,c1) SELECT * FROM t2 +ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1; +Warnings: +Warning 1263 Column was set to data type implicit default; NULL supplied for NOT NULL column 'c1' at row 1 +Error 1048 Column 'c1' cannot be null +SELECT * FROM t1; +id c1 cnt +1 0 3 +2 2 1 +DROP TABLE t1; diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index c785c3be379..80dd055a170 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -391,6 +391,56 @@ i i i 2 NULL 4 2 2 2 drop table t1,t2,t3; +CREATE TABLE t1 (a int, b int default 0, c int default 1); +INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8); +INSERT INTO t1 (a) SELECT a + 8 FROM t1; +INSERT INTO t1 (a) SELECT a + 16 FROM t1; +CREATE TABLE t2 (a int, d int, e int default 0); +INSERT INTO t2 (a, d) VALUES (1,1),(2,2),(3,3),(4,4); +INSERT INTO t2 (a, d) SELECT a+4, a+4 FROM t2; +INSERT INTO t2 (a, d) SELECT a+8, a+8 FROM t2; +EXPLAIN +SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e +ORDER BY t1.b, t1.c; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 32 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 16 Using where +SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e +ORDER BY t1.b, t1.c; +e +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +DROP TABLE t1,t2; create table t1 (c int, b int); create table t2 (a int, b int); create table t3 (b int, c int); diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index df66336bd81..c62601946c2 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1214,3 +1214,28 @@ SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla'; f1 f2 f3 bla blah sheep DROP TABLE t1,t2; +CREATE TABLE t1 (id int PRIMARY KEY, a varchar(8)); +CREATE TABLE t2 (id int NOT NULL, b int NOT NULL, INDEX idx(id)); +INSERT INTO t1 VALUES +(1,'aaaaaaa'), (5,'eeeeeee'), (4,'ddddddd'), (2,'bbbbbbb'), (3,'ccccccc'); +INSERT INTO t2 VALUES +(3,10), (2,20), (5,30), (3,20), (5,10), (3,40), (3,30), (2,10), (2,40); +EXPLAIN +SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 +1 SIMPLE t2 ref idx idx 4 test.t1.id 2 Using where; Not exists +flush status; +SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL; +id a +1 aaaaaaa +4 ddddddd +show status like 'Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 5 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 6 +DROP TABLE t1,t2; diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result index 0478e48025f..a02aaccf8f6 100644 --- a/mysql-test/r/loaddata.result +++ b/mysql-test/r/loaddata.result @@ -165,3 +165,22 @@ f2 2 SET @@SQL_MODE=@OLD_SQL_MODE; drop table t1,t2; +create table t1(f1 int, f2 timestamp not null default current_timestamp); +create table t2(f1 int); +insert into t2 values(1),(2); +Warnings: +Warning 1261 Row 1 doesn't contain data for all columns +Warning 1261 Row 2 doesn't contain data for all columns +select f1 from t1 where f2 <> '0000-00-00 00:00:00' order by f1; +f1 +1 +2 +delete from t1; +Warnings: +Warning 1261 Row 1 doesn't contain data for all columns +Warning 1261 Row 2 doesn't contain data for all columns +select f1 from t1 where f2 <> '0000-00-00 00:00:00' order by f1; +f1 +1 +2 +drop table t1,t2; diff --git a/mysql-test/r/ndb_basic.result b/mysql-test/r/ndb_basic.result index fab10867acd..75ccc6cd39e 100644 --- a/mysql-test/r/ndb_basic.result +++ b/mysql-test/r/ndb_basic.result @@ -667,6 +667,12 @@ counter datavalue 57 newval 58 newval drop table t1; +create table t1 (a int primary key auto_increment) engine = ndb; +insert into t1() values (),(),(),(),(),(),(),(),(),(),(),(); +insert into t1(a) values (20),(28); +insert into t1() values (),(),(),(),(),(),(),(),(),(),(),(); +insert into t1() values (21), (22); +drop table t1; CREATE TABLE t1 ( b INT ) PACK_KEYS = 0 ENGINE = ndb; select * from t1; b diff --git a/mysql-test/r/ndb_insert.result b/mysql-test/r/ndb_insert.result index 0818f9ce9ac..e7275bde2b8 100644 --- a/mysql-test/r/ndb_insert.result +++ b/mysql-test/r/ndb_insert.result @@ -649,3 +649,11 @@ pk a 6 NULL 7 4 DROP TABLE t1; +create table t1(a int primary key, b int, unique key(b)) engine=ndb; +insert ignore into t1 values (1,0), (2,0), (2,null), (3,null); +select * from t1 order by a; +a b +1 0 +2 NULL +3 NULL +drop table t1; diff --git a/mysql-test/r/outfile.result b/mysql-test/r/outfile.result Binary files differindex 023c4ea205f..8503df545d2 100644 --- a/mysql-test/r/outfile.result +++ b/mysql-test/r/outfile.result diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index 20bff6bda1c..96abff8d7f6 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -26,11 +26,11 @@ ERROR HY000: Unknown prepared statement handler (no_such_statement) given to DEA execute stmt1; ERROR HY000: Incorrect arguments to EXECUTE prepare stmt2 from 'prepare nested_stmt from "select 1"'; -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 '"select 1"' at line 1 +ERROR HY000: This command is not supported in the prepared statement protocol yet prepare stmt2 from 'execute stmt1'; -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 'stmt1' at line 1 +ERROR HY000: This command is not supported in the prepared statement protocol yet prepare stmt2 from 'deallocate prepare z'; -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 'z' at line 1 +ERROR HY000: This command is not supported in the prepared statement protocol yet prepare stmt3 from 'insert into t1 values (?,?)'; set @arg1=5, @arg2='five'; execute stmt3 using @arg1, @arg2; diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result index ac8ae6def9f..df4ec793325 100644 --- a/mysql-test/r/ps_1general.result +++ b/mysql-test/r/ps_1general.result @@ -391,11 +391,11 @@ drop table t5 ; deallocate prepare stmt_do ; deallocate prepare stmt_set ; prepare stmt1 from ' prepare stmt2 from '' select 1 '' ' ; -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 '' select 1 '' at line 1 +ERROR HY000: This command is not supported in the prepared statement protocol yet prepare stmt1 from ' execute stmt2 ' ; -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 'stmt2' at line 1 +ERROR HY000: This command is not supported in the prepared statement protocol yet prepare stmt1 from ' deallocate prepare never_prepared ' ; -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 'never_prepared' at line 1 +ERROR HY000: This command is not supported in the prepared statement protocol yet prepare stmt4 from ' use test ' ; ERROR HY000: This command is not supported in the prepared statement protocol yet prepare stmt3 from ' create database mysqltest '; diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index 151ddd95f84..79471ee5c02 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -1315,3 +1315,97 @@ insert into t1(c1) select c1 from v1; drop table t1, t2, t3; drop view v1; set global query_cache_size=0; +create table t1 (a int); +insert into t1 values (1),(2),(3); +set GLOBAL query_cache_type=1; +set GLOBAL query_cache_limit=10000; +set GLOBAL query_cache_min_res_unit=0; +set GLOBAL query_cache_size= 100000; +reset query cache; +set LOCAL default_week_format = 0; +select week('2007-01-04'); +week('2007-01-04') +0 +select week('2007-01-04') from t1; +week('2007-01-04') +0 +0 +0 +select extract(WEEK FROM '2007-01-04') from t1; +extract(WEEK FROM '2007-01-04') +0 +0 +0 +set LOCAL default_week_format = 2; +select week('2007-01-04'); +week('2007-01-04') +53 +select week('2007-01-04') from t1; +week('2007-01-04') +53 +53 +53 +select extract(WEEK FROM '2007-01-04') from t1; +extract(WEEK FROM '2007-01-04') +53 +53 +53 +reset query cache; +set LOCAL div_precision_increment=2; +select 1/7; +1/7 +0.14 +select 1/7 from t1; +1/7 +0.14 +0.14 +0.14 +set LOCAL div_precision_increment=4; +select 1/7; +1/7 +0.1429 +select 1/7 from t1; +1/7 +0.1429 +0.1429 +0.1429 +drop table t1; +CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)); +INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'), +('Full-text indexes', 'are called collections'), +('Only MyISAM tables','support collections'), +('Function MATCH ... AGAINST()','is used to do a search'), +('Full-text search in MySQL', 'implements vector space model'); +set GLOBAL ft_boolean_syntax='+ -><()~*:""&|'; +select *, MATCH(a,b) AGAINST("+called +collections" IN BOOLEAN MODE) as x from t1; +a b x +MySQL has now support for full-text search 0 +Full-text indexes are called collections 1 +Only MyISAM tables support collections 0 +Function MATCH ... AGAINST() is used to do a search 0 +Full-text search in MySQL implements vector space model 0 +set GLOBAL ft_boolean_syntax='- +><()~*:""&|'; +select *, MATCH(a,b) AGAINST("+called +collections" IN BOOLEAN MODE) as x from t1; +a b x +MySQL has now support for full-text search 0 +Full-text indexes are called collections 0 +Only MyISAM tables support collections 0 +Function MATCH ... AGAINST() is used to do a search 0 +Full-text search in MySQL implements vector space model 0 +create function change_global() returns integer +begin +set global ft_boolean_syntax='+ -><()~*:""&|'; +return 1; +end| +select *, change_global() from t1; +a b change_global() +MySQL has now support for full-text search 1 +Full-text indexes are called collections 1 +Only MyISAM tables support collections 1 +Function MATCH ... AGAINST() is used to do a search 1 +Full-text search in MySQL implements vector space model 1 +drop function change_global; +set GLOBAL query_cache_type=default; +set GLOBAL query_cache_limit=default; +set GLOBAL query_cache_min_res_unit=default; +set GLOBAL query_cache_size= default; diff --git a/mysql-test/r/sp-code.result b/mysql-test/r/sp-code.result index 9d86a6bc08d..219f3c9b37a 100644 --- a/mysql-test/r/sp-code.result +++ b/mysql-test/r/sp-code.result @@ -620,4 +620,117 @@ SHOW PROCEDURE CODE p1; Pos Instruction 0 stmt 2 "CREATE INDEX idx ON t1 (c1)" DROP PROCEDURE p1; +drop table if exists t1; +drop procedure if exists proc_26977_broken; +drop procedure if exists proc_26977_works; +create table t1(a int unique); +create procedure proc_26977_broken(v int) +begin +declare i int default 5; +declare continue handler for sqlexception +begin +select 'caught something'; +retry: +while i > 0 do +begin +set i = i - 1; +select 'looping', i; +end; +end while retry; +end; +select 'do something'; +insert into t1 values (v); +select 'do something again'; +insert into t1 values (v); +end// +create procedure proc_26977_works(v int) +begin +declare i int default 5; +declare continue handler for sqlexception +begin +select 'caught something'; +retry: +while i > 0 do +begin +set i = i - 1; +select 'looping', i; +end; +end while retry; +select 'optimizer: keep hreturn'; +end; +select 'do something'; +insert into t1 values (v); +select 'do something again'; +insert into t1 values (v); +end// +show procedure code proc_26977_broken; +Pos Instruction +0 set i@1 5 +1 hpush_jump 8 2 CONTINUE +2 stmt 0 "select 'caught something'" +3 jump_if_not 7(7) (i@1 > 0) +4 set i@1 (i@1 - 1) +5 stmt 0 "select 'looping', i" +6 jump 3 +7 hreturn 2 +8 stmt 0 "select 'do something'" +9 stmt 5 "insert into t1 values (v)" +10 stmt 0 "select 'do something again'" +11 stmt 5 "insert into t1 values (v)" +12 hpop 1 +show procedure code proc_26977_works; +Pos Instruction +0 set i@1 5 +1 hpush_jump 9 2 CONTINUE +2 stmt 0 "select 'caught something'" +3 jump_if_not 7(7) (i@1 > 0) +4 set i@1 (i@1 - 1) +5 stmt 0 "select 'looping', i" +6 jump 3 +7 stmt 0 "select 'optimizer: keep hreturn'" +8 hreturn 2 +9 stmt 0 "select 'do something'" +10 stmt 5 "insert into t1 values (v)" +11 stmt 0 "select 'do something again'" +12 stmt 5 "insert into t1 values (v)" +13 hpop 1 +call proc_26977_broken(1); +do something +do something +do something again +do something again +caught something +caught something +looping i +looping 4 +looping i +looping 3 +looping i +looping 2 +looping i +looping 1 +looping i +looping 0 +call proc_26977_works(2); +do something +do something +do something again +do something again +caught something +caught something +looping i +looping 4 +looping i +looping 3 +looping i +looping 2 +looping i +looping 1 +looping i +looping 0 +optimizer: keep hreturn +optimizer: keep hreturn +drop table t1; +drop procedure proc_26977_broken; +drop procedure proc_26977_works; End of 5.0 tests. diff --git a/mysql-test/r/sp-vars.result b/mysql-test/r/sp-vars.result index 6090dfdf737..b112c6bece6 100644 --- a/mysql-test/r/sp-vars.result +++ b/mysql-test/r/sp-vars.result @@ -690,12 +690,12 @@ END| CALL p1(NOW()); Table Create Table t1 CREATE TABLE "t1" ( - "x" varbinary(19) default NULL + "x" datetime default NULL ) CALL p1('test'); Table Create Table t1 CREATE TABLE "t1" ( - "x" varbinary(19) default NULL + "x" datetime default NULL ) Warnings: Warning 1264 Out of range value adjusted for column 'x' at row 1 diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 9ba6a356db2..b5b79af031e 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -6118,6 +6118,13 @@ Warning 1265 Data truncated for column 'bug5274_f1' at row 1 Warning 1265 Data truncated for column 'bug5274_f1' at row 1 DROP FUNCTION bug5274_f1| DROP FUNCTION bug5274_f2| +drop procedure if exists proc_21513| +create procedure proc_21513()`my_label`:BEGIN END| +show create procedure proc_21513| +Procedure sql_mode Create Procedure +proc_21513 CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_21513`() +`my_label`:BEGIN END +drop procedure proc_21513| End of 5.0 tests. drop table t1,t2; CREATE TABLE t1 (a int auto_increment primary key) engine=MyISAM; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 06ff23b32b7..2e82d948edb 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -4034,4 +4034,11 @@ SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) ) FROM t1; ERROR HY000: Invalid use of group function DROP TABLE t1,t2; +CREATE TABLE t1 (a int, b int, KEY (a)); +INSERT INTO t1 VALUES (1,1),(2,1); +EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref a a 5 const 1 Using where; Using index +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +DROP TABLE t1; End of 5.0 tests. diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index f52249db8a1..9bbfdc6c5f9 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -711,3 +711,34 @@ a 1 4 DROP TABLE t1,t2; +CREATE TABLE t1 (id int); +CREATE TABLE t2 (id int PRIMARY KEY); +CREATE TABLE t3 (id int PRIMARY KEY, name varchar(10)); +INSERT INTO t1 VALUES (2), (NULL), (3), (1); +INSERT INTO t2 VALUES (234), (345), (457); +INSERT INTO t3 VALUES (222,'bbb'), (333,'ccc'), (111,'aaa'); +EXPLAIN +SELECT * FROM t1 +WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3 +WHERE t3.name='xxx' AND t2.id=t3.id); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 func 1 Using where; Using index; Full scan on NULL key +2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 func 1 Using where; Full scan on NULL key +SELECT * FROM t1 +WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3 +WHERE t3.name='xxx' AND t2.id=t3.id); +id +2 +NULL +3 +1 +SELECT (t1.id IN (SELECT t2.id FROM t2,t3 +WHERE t3.name='xxx' AND t2.id=t3.id)) AS x +FROM t1; +x +0 +0 +0 +0 +DROP TABLE t1,t2,t3; diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index 0a0be41927a..3e6a901dc00 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -1414,4 +1414,39 @@ id val DROP TRIGGER trg27006_a_insert; DROP TRIGGER trg27006_a_update; drop table t1,t2; +drop table if exists t1, t2, t3; +create table t1 (i int); +create trigger t1_bi before insert on t1 for each row set new.i = 7; +create trigger t1_ai after insert on t1 for each row set @a := 7; +create table t2 (j int); +insert into t2 values (1), (2); +set @a:=""; +create table if not exists t1 select * from t2; +Warnings: +Note 1050 Table 't1' already exists +select * from t1; +i +7 +7 +select @a; +@a +7 +drop trigger t1_bi; +drop trigger t1_ai; +create table t3 (isave int); +create trigger t1_bi before insert on t1 for each row insert into t3 values (new.i); +create table if not exists t1 select * from t2; +Warnings: +Note 1050 Table 't1' already exists +select * from t1; +i +7 +7 +1 +2 +select * from t3; +isave +1 +2 +drop table t1, t2, t3; End of 5.0 tests diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index d86404aa97f..d2a611d2413 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -299,3 +299,85 @@ f2 SELECT 1 from dual where NOW() BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND CURRENT_DATE(); 1 drop table t1; +create table t1 (f1 date); +insert into t1 values('01-01-01'),('01-01-02'),('01-01-03'); +select * from t1 where f1 in ('01-01-01','2001-01-02','2001-01-03 00:00:00'); +f1 +2001-01-01 +2001-01-02 +2001-01-03 +create table t2(f2 datetime); +insert into t2 values('01-01-01 00:00:00'),('01-02-03 12:34:56'),('02-04-06 11:22:33'); +select * from t2 where f2 in ('01-01-01','01-02-03 12:34:56','01-02-03'); +f2 +2001-01-01 00:00:00 +2001-02-03 12:34:56 +select * from t1,t2 where '01-01-02' in (f1, cast(f2 as date)); +f1 f2 +2001-01-02 2001-01-01 00:00:00 +2001-01-02 2001-02-03 12:34:56 +2001-01-02 2002-04-06 11:22:33 +select * from t1,t2 where '01-01-01' in (f1, '01-02-03'); +f1 f2 +2001-01-01 2001-01-01 00:00:00 +2001-01-01 2001-02-03 12:34:56 +2001-01-01 2002-04-06 11:22:33 +select * from t1,t2 where if(1,'01-02-03 12:34:56','') in (f1, f2); +f1 f2 +2001-01-01 2001-02-03 12:34:56 +2001-01-02 2001-02-03 12:34:56 +2001-01-03 2001-02-03 12:34:56 +create table t3(f3 varchar(20)); +insert into t3 select * from t2; +select * from t2,t3 where f2 in (f3,'03-04-05'); +f2 f3 +2001-01-01 00:00:00 2001-01-01 00:00:00 +2001-02-03 12:34:56 2001-02-03 12:34:56 +2002-04-06 11:22:33 2002-04-06 11:22:33 +select f1,f2,f3 from t1,t2,t3 where (f1,'1') in ((f2,'1'),(f3,'1')); +f1 f2 f3 +2001-01-01 2001-01-01 00:00:00 2001-01-01 00:00:00 +2001-01-01 2001-02-03 12:34:56 2001-01-01 00:00:00 +2001-01-01 2002-04-06 11:22:33 2001-01-01 00:00:00 +2001-01-01 2001-01-01 00:00:00 2001-02-03 12:34:56 +2001-01-01 2001-01-01 00:00:00 2002-04-06 11:22:33 +select f1 from t1 where ('1',f1) in (('1','01-01-01'),('1','2001-1-1 0:0:0'),('1','02-02-02')); +f1 +2001-01-01 +drop table t1,t2,t3; +select least(cast('01-01-01' as date), '01-01-02'); +least(cast('01-01-01' as date), '01-01-02') +2001-01-01 +select greatest(cast('01-01-01' as date), '01-01-02'); +greatest(cast('01-01-01' as date), '01-01-02') +01-01-02 +select least(cast('01-01-01' as date), '01-01-02') + 0; +least(cast('01-01-01' as date), '01-01-02') + 0 +20010101 +select greatest(cast('01-01-01' as date), '01-01-02') + 0; +greatest(cast('01-01-01' as date), '01-01-02') + 0 +20010102 +select least(cast('01-01-01' as datetime), '01-01-02') + 0; +least(cast('01-01-01' as datetime), '01-01-02') + 0 +20010101000000 +select cast(least(cast('01-01-01' as datetime), '01-01-02') as signed); +cast(least(cast('01-01-01' as datetime), '01-01-02') as signed) +20010101000000 +select cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal(20,2)); +cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal(20,2)) +20010101000000.00 +DROP PROCEDURE IF EXISTS test27759 ; +CREATE PROCEDURE test27759() +BEGIN +declare v_a date default '2007-4-10'; +declare v_b date default '2007-4-11'; +declare v_c datetime default '2004-4-9 0:0:0'; +select v_a as a,v_b as b, +least( v_a, v_b ) as a_then_b, +least( v_b, v_a ) as b_then_a, +least( v_c, v_a ) as c_then_a; +END;| +call test27759(); +a b a_then_b b_then_a c_then_a +2007-04-10 2007-04-11 2007-04-10 2007-04-10 2004-04-09 00:00:00 +drop procedure test27759; diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result index c103de81bd7..cbcab126439 100644 --- a/mysql-test/r/type_newdecimal.result +++ b/mysql-test/r/type_newdecimal.result @@ -1430,4 +1430,39 @@ select * from t1; a 123456789012345678 drop table t1; +select cast(11.1234 as DECIMAL(3,2)); +cast(11.1234 as DECIMAL(3,2)) +9.99 +Warnings: +Error 1264 Out of range value adjusted for column 'cast(11.1234 as DECIMAL(3,2))' at row 1 +select * from (select cast(11.1234 as DECIMAL(3,2))) t; +cast(11.1234 as DECIMAL(3,2)) +9.99 +Warnings: +Error 1264 Out of range value adjusted for column 'cast(11.1234 as DECIMAL(3,2))' at row 1 +select cast(a as DECIMAL(3,2)) +from (select 11.1233 as a +UNION select 11.1234 +UNION select 12.1234 +) t; +cast(a as DECIMAL(3,2)) +9.99 +9.99 +9.99 +Warnings: +Error 1264 Out of range value adjusted for column 'cast(a as DECIMAL(3,2))' at row 1 +Error 1264 Out of range value adjusted for column 'cast(a as DECIMAL(3,2))' at row 1 +Error 1264 Out of range value adjusted for column 'cast(a as DECIMAL(3,2))' at row 1 +select cast(a as DECIMAL(3,2)), count(*) +from (select 11.1233 as a +UNION select 11.1234 +UNION select 12.1234 +) t group by 1; +cast(a as DECIMAL(3,2)) count(*) +9.99 3 +Warnings: +Error 1264 Out of range value adjusted for column 'cast(a as DECIMAL(3,2))' at row 1 +Error 1264 Out of range value adjusted for column 'cast(a as DECIMAL(3,2))' at row 1 +Error 1264 Out of range value adjusted for column 'cast(a as DECIMAL(3,2))' at row 1 +Error 1264 Out of range value adjusted for column 'cast(a as DECIMAL(3,2))' at row 1 End of 5.0 tests diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 70dd6b2550f..8d9d802949d 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -1789,7 +1789,7 @@ drop table t1; create view v1 as select cast(1 as decimal); select * from v1; cast(1 as decimal) -1.00 +1 drop view v1; create table t1(f1 int); create table t2(f2 int); @@ -3354,4 +3354,17 @@ id select_type table type possible_keys key key_len ref rows Extra NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL Using filesort DROP VIEW v1; DROP TABLE t1; +CREATE VIEW v1 AS SELECT CAST( 1.23456789 AS DECIMAL( 7,5 ) ) AS col; +SELECT * FROM v1; +col +1.23457 +DESCRIBE v1; +Field Type Null Key Default Extra +col decimal(7,5) NO 0.00000 +DROP VIEW v1; +CREATE VIEW v1 AS SELECT CAST(1.23456789 AS DECIMAL(8,0)) AS col; +SHOW CREATE VIEW v1; +View Create View +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(1.23456789 as decimal(8,0)) AS `col` +DROP VIEW v1; End of 5.0 tests. |