diff options
Diffstat (limited to 'mysql-test')
51 files changed, 1980 insertions, 32 deletions
diff --git a/mysql-test/r/csv.result b/mysql-test/r/csv.result index 6456eb1d1ba..d253d121e99 100644 --- a/mysql-test/r/csv.result +++ b/mysql-test/r/csv.result @@ -5239,3 +5239,11 @@ id string 0.67 string 9.67 string drop table float_test; +CREATE TABLE `bug21328` ( +`col1` int(11) DEFAULT NULL, +`col2` int(11) DEFAULT NULL, +`col3` int(11) DEFAULT NULL +) ENGINE=CSV; +insert into bug21328 values (1,NULL,NULL); +alter table bug21328 engine=myisam; +drop table bug21328; diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index 72c040cb25f..e1a8a3151e9 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -607,3 +607,40 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a; a a DROP TABLE t1; +CREATE TABLE t1 (a CHAR(1)); +INSERT INTO t1 VALUES('A'), (0); +SELECT a FROM t1 WHERE a=0; +a +A +0 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'A' +SELECT DISTINCT a FROM t1 WHERE a=0; +a +A +0 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'A' +DROP TABLE t1; +CREATE TABLE t1 (a DATE); +INSERT INTO t1 VALUES ('1972-07-29'), ('1972-02-06'); +EXPLAIN SELECT (SELECT DISTINCT a FROM t1 WHERE a = '2002-08-03'); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +EXPLAIN SELECT (SELECT DISTINCT ADDDATE(a,1) FROM t1 +WHERE ADDDATE(a,1) = '2002-08-03'); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where; Using temporary +CREATE TABLE t2 (a CHAR(5) CHARACTER SET latin1 COLLATE latin1_general_ci); +INSERT INTO t2 VALUES (0xf6); +INSERT INTO t2 VALUES ('oe'); +SELECT COUNT(*) FROM (SELECT DISTINCT a FROM t2) dt; +COUNT(*) +2 +SELECT COUNT(*) FROM +(SELECT DISTINCT a FROM t2 WHERE a='oe' COLLATE latin1_german2_ci) dt; +COUNT(*) +2 +DROP TABLE t1, t2; diff --git a/mysql-test/r/gis-rtree.result b/mysql-test/r/gis-rtree.result index 95211ad9133..138360edc49 100644 --- a/mysql-test/r/gis-rtree.result +++ b/mysql-test/r/gis-rtree.result @@ -881,3 +881,14 @@ ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field INSERT INTO t1(foo) VALUES (''); ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field DROP TABLE t1; +CREATE TABLE t1 (a INT AUTO_INCREMENT, b POINT NOT NULL, KEY (a), SPATIAL KEY (b)); +INSERT INTO t1 (b) VALUES (GeomFromText('POINT(1 2)')); +INSERT INTO t1 (b) SELECT b FROM t1; +INSERT INTO t1 (b) SELECT b FROM t1; +INSERT INTO t1 (b) SELECT b FROM t1; +INSERT INTO t1 (b) SELECT b FROM t1; +INSERT INTO t1 (b) SELECT b FROM t1; +OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +DROP TABLE t1; diff --git a/mysql-test/r/information_schema_inno.result b/mysql-test/r/information_schema_inno.result index 9bb3185c6fb..b9c0cae5efb 100644 --- a/mysql-test/r/information_schema_inno.result +++ b/mysql-test/r/information_schema_inno.result @@ -56,3 +56,25 @@ test t3 FOREIGN KEY A2 test t2 NONE SET NULL RESTRICT test t4 FOREIGN KEY A3 test t3 NONE NO ACTION SET NULL test t5 FOREIGN KEY A4 test t4 NONE RESTRICT CASCADE drop tables t5, t4, t3, t2, t1; +create database `db-1`; +use `db-1`; +create table `t-2` ( +id int(10) unsigned not null auto_increment, +primary key (id) +) engine=innodb; +create table `t-1` ( +id int(10) unsigned not null auto_increment, +idtype int(10) unsigned not null, +primary key (id), +key fk_t1_1 (idtype), +constraint fk_t1_1 foreign key (idtype) references `t-2` (id) +) engine=innodb; +use test; +select referenced_table_schema, referenced_table_name +from information_schema.key_column_usage +where constraint_schema = 'db-1'; +referenced_table_schema referenced_table_name +NULL NULL +db-1 t-2 +NULL NULL +drop database `db-1`; diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index 897ec4f7a6a..fbddc6ffeab 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -804,3 +804,19 @@ select '^^: The above should be ~= 20 + cost(select * from t1). Value less than Z ^^: The above should be ~= 20 + cost(select * from t1). Value less than 20 is an error drop table t1, t2; +CREATE TABLE t1 (ID INTEGER, Name VARCHAR(50)); +CREATE TABLE t2 (Test_ID INTEGER); +CREATE VIEW v1 (Test_ID, Description) AS SELECT ID, Name FROM t1; +CREATE TABLE tv1 SELECT Description AS Name FROM v1 JOIN t2 +USING (Test_ID); +DESCRIBE tv1; +Field Type Null Key Default Extra +Name varchar(50) YES NULL +CREATE TABLE tv2 SELECT Description AS Name FROM v1 JOIN t2 +ON v1.Test_ID = t2.Test_ID; +DESCRIBE tv2; +Field Type Null Key Default Extra +Name varchar(50) YES NULL +DROP VIEW v1; +DROP TABLE t1,t2,tv1,tv2; +End of 5.0 tests. diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result index 6b9bea7aa21..75caa6b45a4 100644 --- a/mysql-test/r/join_nested.result +++ b/mysql-test/r/join_nested.result @@ -1562,3 +1562,46 @@ id ngroupbynsa 2 1 2 1 DROP TABLE t1,t2,t3,t4,t5; +CREATE TABLE t1 ( +id int NOT NULL PRIMARY KEY, +ct int DEFAULT NULL, +pc int DEFAULT NULL, +INDEX idx_ct (ct), +INDEX idx_pc (pc) +); +INSERT INTO t1 VALUES +(1,NULL,NULL),(2,NULL,NULL),(3,NULL,NULL),(4,NULL,NULL),(5,NULL,NULL); +CREATE TABLE t2 ( +id int NOT NULL PRIMARY KEY, +sr int NOT NULL, +nm varchar(255) NOT NULL, +INDEX idx_sr (sr) +); +INSERT INTO t2 VALUES +(2441905,4308,'LesAbymes'),(2441906,4308,'Anse-Bertrand'); +CREATE TABLE t3 ( +id int NOT NULL PRIMARY KEY, +ct int NOT NULL, +ln int NOT NULL, +INDEX idx_ct (ct), +INDEX idx_ln (ln) +); +CREATE TABLE t4 ( +id int NOT NULL PRIMARY KEY, +nm varchar(255) NOT NULL +); +INSERT INTO t4 VALUES (4308,'Guadeloupe'),(4309,'Martinique'); +SELECT t1.* +FROM t1 LEFT JOIN +(t2 LEFT JOIN t3 ON t3.ct=t2.id AND t3.ln='5') ON t1.ct=t2.id +WHERE t1.id='5'; +id ct pc +5 NULL NULL +SELECT t1.*, t4.nm +FROM t1 LEFT JOIN +(t2 LEFT JOIN t3 ON t3.ct=t2.id AND t3.ln='5') ON t1.ct=t2.id +LEFT JOIN t4 ON t2.sr=t4.id +WHERE t1.id='5'; +id ct pc nm +5 NULL NULL NULL +DROP TABLE t1,t2,t3,t4; diff --git a/mysql-test/r/log_tables.result b/mysql-test/r/log_tables.result index 3e8b7d32e88..8264f252287 100644 --- a/mysql-test/r/log_tables.result +++ b/mysql-test/r/log_tables.result @@ -111,9 +111,6 @@ slow_log CREATE TABLE `slow_log` ( ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' alter table mysql.general_log engine=myisam; alter table mysql.slow_log engine=myisam; -Warnings: -Warning 1366 Incorrect integer value: '' for column 'last_insert_id' at row 0 -Warning 1366 Incorrect integer value: '' for column 'insert_id' at row 0 show create table mysql.general_log; Table Create Table general_log CREATE TABLE `general_log` ( diff --git a/mysql-test/r/ndb_lock.result b/mysql-test/r/ndb_lock.result index 668c26aad03..44eb5c8b3f3 100644 --- a/mysql-test/r/ndb_lock.result +++ b/mysql-test/r/ndb_lock.result @@ -87,11 +87,27 @@ x y z rollback; commit; begin; +select * from t1 where y = 'one' or y = 'three' for update; +x y z +# # # +# # # +begin; +select * from t1 where x = 2 for update; +x y z +2 two 2 +select * from t1 where x = 1 for update; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +rollback; +commit; +begin; select * from t1 where y = 'one' or y = 'three' order by x for update; x y z 1 one 1 3 three 3 begin; +select * from t1 where x = 2 for update; +x y z +2 two 2 select * from t1 where x = 1 for update; ERROR HY000: Lock wait timeout exceeded; try restarting transaction rollback; @@ -124,6 +140,22 @@ ERROR HY000: Lock wait timeout exceeded; try restarting transaction rollback; commit; begin; +select * from t1 where y = 'one' or y = 'three' lock in share mode; +x y z +# # # +# # # +begin; +select * from t1 where y = 'one' lock in share mode; +x y z +1 one 1 +select * from t1 where x = 2 for update; +x y z +2 two 2 +select * from t1 where x = 1 for update; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +rollback; +commit; +begin; select * from t1 where y = 'one' or y = 'three' order by x lock in share mode; x y z 1 one 1 @@ -132,6 +164,9 @@ begin; select * from t1 where y = 'one' lock in share mode; x y z 1 one 1 +select * from t1 where x = 2 for update; +x y z +2 two 2 select * from t1 where x = 1 for update; ERROR HY000: Lock wait timeout exceeded; try restarting transaction rollback; diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index e5cc47fd9d2..8d09686b7d3 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -29,14 +29,14 @@ INSERT INTO t2 VALUES (7,'Liste des t2','t2_liste_form.phtml',51060,'link.gif'); INSERT INTO t2 VALUES (8,'Consulter les soumissions','consulter_soumissions.phtml',200,'link.gif'); INSERT INTO t2 VALUES (9,'Ajouter un type de materiel','typeMateriel_ajoute_form.phtml',51000,'link.gif'); INSERT INTO t2 VALUES (10,'Lister/modifier un type de materiel','typeMateriel_liste_form.phtml',51010,'link.gif'); -INSERT INTO t2 VALUES (3,'Crer une fiche de client','clients_ajoute_form.phtml',40000,'link.gif'); +INSERT INTO t2 VALUES (3,'Créer une fiche de client','clients_ajoute_form.phtml',40000,'link.gif'); INSERT INTO t2 VALUES (4,'Modifier des clients','en_construction.html',40010,'link.gif'); INSERT INTO t2 VALUES (5,'Effacer des clients','en_construction.html',40020,'link.gif'); INSERT INTO t2 VALUES (6,'Ajouter un service','t2_ajoute_form.phtml',51050,'link.gif'); select t1.id,t1.idservice,t2.ordre,t2.description from t1, t2 where t1.id = 2 and t1.idservice = t2.id order by t2.ordre; id idservice ordre description 2 1 10 Emettre un appel d'offres -2 3 40000 Crer une fiche de client +2 3 40000 Créer une fiche de client 2 4 40010 Modifier des clients 2 5 40020 Effacer des clients 2 6 51050 Ajouter un service @@ -874,6 +874,14 @@ num (select num + 2 FROM t1 LIMIT 1) SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a; ERROR 42S22: Unknown column 'num' in 'on clause' DROP TABLE t1; +CREATE TABLE t1 (a int); +SELECT p.a AS val, q.a AS val1 FROM t1 p, t1 q ORDER BY val > 1; +val val1 +SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val; +ERROR 23000: Column 'val' in order clause is ambiguous +SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val > 1; +ERROR 23000: Column 'val' in order clause is ambiguous +DROP TABLE t1; create table t1 (a int not null, b int not null, c int not null); insert t1 values (1,1,1),(1,1,2),(1,2,1); select a, b from t1 group by a, b order by sum(c); diff --git a/mysql-test/r/parser.result b/mysql-test/r/parser.result index 49008eb818b..cb44a235f25 100644 --- a/mysql-test/r/parser.result +++ b/mysql-test/r/parser.result @@ -49,7 +49,7 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp create table GROUP_CONCAT (a int); drop table GROUP_CONCAT; create table GROUP_UNIQUE_USERS(a int); -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 'GROUP_UNIQUE_USERS(a int)' at line 1 +drop table GROUP_UNIQUE_USERS; create table GROUP_UNIQUE_USERS (a int); drop table GROUP_UNIQUE_USERS; create table MAX(a int); @@ -121,7 +121,7 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp create table TRIM (a int); drop table TRIM; create table UNIQUE_USERS(a int); -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 'UNIQUE_USERS(a int)' at line 1 +drop table UNIQUE_USERS; create table UNIQUE_USERS (a int); drop table UNIQUE_USERS; create table VARIANCE(a int); @@ -186,9 +186,9 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp create table GROUP_CONCAT (a int); 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 'GROUP_CONCAT (a int)' at line 1 create table GROUP_UNIQUE_USERS(a int); -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 'GROUP_UNIQUE_USERS(a int)' at line 1 +drop table GROUP_UNIQUE_USERS; create table GROUP_UNIQUE_USERS (a int); -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 'GROUP_UNIQUE_USERS (a int)' at line 1 +drop table GROUP_UNIQUE_USERS; create table MAX(a int); 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 'MAX(a int)' at line 1 create table MAX (a int); @@ -258,9 +258,9 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp create table TRIM (a int); 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 'TRIM (a int)' at line 1 create table UNIQUE_USERS(a int); -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 'UNIQUE_USERS(a int)' at line 1 +drop table UNIQUE_USERS; create table UNIQUE_USERS (a int); -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 'UNIQUE_USERS (a int)' at line 1 +drop table UNIQUE_USERS; create table VARIANCE(a int); 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 'VARIANCE(a int)' at line 1 create table VARIANCE (a int); diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index 5a8f5a3137d..55ba8380665 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -1128,4 +1128,14 @@ a 18446744073709551613 18446744073709551614 drop table t1; +CREATE TABLE t1 ( +num int(11) NOT NULL, cs int(11) NOT NULL) +PARTITION BY RANGE (num) SUBPARTITION BY HASH ( +cs) SUBPARTITIONS 2 (PARTITION p_X VALUES LESS THAN MAXVALUE); +ALTER TABLE t1 +REORGANIZE PARTITION p_X INTO ( +PARTITION p_100 VALUES LESS THAN (100), +PARTITION p_X VALUES LESS THAN MAXVALUE +); +drop table t1; End of 5.1 tests diff --git a/mysql-test/r/partition_federated.result b/mysql-test/r/partition_federated.result new file mode 100644 index 00000000000..2d98e366c95 --- /dev/null +++ b/mysql-test/r/partition_federated.result @@ -0,0 +1,6 @@ +drop table if exists t1; +create table t1 (s1 int) engine=federated +connection='mysql://root@localhost/federated/t1' partition by list (s1) +(partition p1 values in (1), partition p2 values in (2)); +ERROR HY000: Engine cannot be used in partitioned tables +End of 5.1 tests diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index 4988b1139e9..d82b07fa7d3 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -1522,6 +1522,28 @@ Variable_name Value Slow_queries 1 deallocate prepare no_index; deallocate prepare sq; +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (1), (2); +CREATE TABLE t2 (b int); +INSERT INTO t2 VALUES (NULL); +SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL; +a +1 +2 +PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL'; +EXECUTE stmt; +a +1 +2 +DEALLOCATE PREPARE stmt; +PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2 limit ?) IS NULL'; +SET @arg=1; +EXECUTE stmt USING @arg; +a +1 +2 +DEALLOCATE PREPARE stmt; +DROP TABLE t1,t2; End of 5.0 tests. create procedure proc_1() reset query cache; call proc_1(); diff --git a/mysql-test/r/read_only.result b/mysql-test/r/read_only.result index 69d25fbef6f..f270f1ed5ad 100644 --- a/mysql-test/r/read_only.result +++ b/mysql-test/r/read_only.result @@ -39,11 +39,61 @@ delete t1 from t1,t3 where t1.a=t3.a; drop table t1; insert into t1 values(1); ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement +set global read_only=0; +lock table t1 write; +lock table t2 write; +set global read_only=1; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +unlock tables ; +set global read_only=1; +select @@global.read_only; +@@global.read_only +0 +unlock tables ; +select @@global.read_only; +@@global.read_only +1 +set global read_only=0; +lock table t1 read; +lock table t2 read; +set global read_only=1; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +unlock tables ; +set global read_only=1; +select @@global.read_only; +@@global.read_only +0 +unlock tables ; +select @@global.read_only; +@@global.read_only +1 +set global read_only=0; +BEGIN; +BEGIN; +set global read_only=1; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +ROLLBACK; +set global read_only=1; +select @@global.read_only; +@@global.read_only +1 +ROLLBACK; +set global read_only=0; +flush tables with read lock; +set global read_only=1; +unlock tables; +set global read_only=0; +flush tables with read lock; +set global read_only=1; +select @@global.read_only; +@@global.read_only +1 +unlock tables; drop temporary table ttt; ERROR 42S02: Unknown table 'ttt' drop temporary table if exists ttt; Warnings: Note 1051 Unknown table 'ttt' +set global read_only=0; drop table t1,t2; drop user test@localhost; -set global read_only=0; diff --git a/mysql-test/r/read_only_innodb.result b/mysql-test/r/read_only_innodb.result new file mode 100644 index 00000000000..d028e3cc207 --- /dev/null +++ b/mysql-test/r/read_only_innodb.result @@ -0,0 +1,18 @@ +DROP TABLE IF EXISTS table_11733 ; +grant CREATE, SELECT, DROP on *.* to test@localhost; +set global read_only=0; +create table table_11733 (a int) engine=InnoDb; +BEGIN; +insert into table_11733 values(11733); +set global read_only=1; +select @@global.read_only; +@@global.read_only +1 +select * from table_11733 ; +a +11733 +COMMIT; +ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement +set global read_only=0; +drop table table_11733 ; +drop user test@localhost; diff --git a/mysql-test/r/rpl_read_only.result b/mysql-test/r/rpl_read_only.result new file mode 100644 index 00000000000..0b06a3d414a --- /dev/null +++ b/mysql-test/r/rpl_read_only.result @@ -0,0 +1,113 @@ +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; +create table t1(a int) engine=InnoDB; +create table t2(a int) engine=MyISAM; +insert into t1 values(1001); +insert into t2 values(2001); +set global read_only=1; +select @@read_only; +@@read_only +1 +select * from t1; +a +1001 +select * from t2; +a +2001 +select @@read_only; +@@read_only +0 +select * from t1; +a +1001 +select * from t2; +a +2001 +set global read_only=0; +BEGIN; +insert into t1 values(1002); +insert into t2 values(2002); +BEGIN; +insert into t1 values(1003); +insert into t2 values(2003); +set global read_only=1; +COMMIT; +COMMIT; +ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement +set global read_only=0; +insert into t1 values(1004); +insert into t2 values(2004); +select * from t1; +a +1001 +1002 +1004 +select * from t2; +a +2001 +2002 +2003 +2004 +select * from t1; +a +1001 +1002 +1004 +select * from t2; +a +2001 +2002 +2003 +2004 +set global read_only=1; +select @@read_only; +@@read_only +1 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +insert into t1 values(1005); +insert into t2 values(2005); +select * from t1; +a +1001 +1002 +1004 +1005 +select * from t2; +a +2001 +2002 +2003 +2004 +2005 +select * from t1; +a +1001 +1002 +1004 +1005 +select * from t2; +a +2001 +2002 +2003 +2004 +2005 +insert into t1 values(1006); +ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement +insert into t2 values(2006); +ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement +drop table t1; +drop table t2; diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 15937a4a6f5..074e991296d 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -3611,3 +3611,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range si,ai si 5 NULL 2 Using where 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where DROP TABLE t1,t2,t3; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1(a int); +INSERT into t1 values (1), (2), (3); +SELECT * FROM t1 LIMIT 2, -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 '-1' at line 1 +DROP TABLE t1; diff --git a/mysql-test/r/sp-code.result b/mysql-test/r/sp-code.result index 4ae38861d29..0b0ad802b54 100644 --- a/mysql-test/r/sp-code.result +++ b/mysql-test/r/sp-code.result @@ -199,6 +199,421 @@ Pos Instruction 44 jump 14 45 stmt 9 "drop temporary table sudoku_work, sud..." drop procedure sudoku_solve; +DROP PROCEDURE IF EXISTS proc_19194_simple; +DROP PROCEDURE IF EXISTS proc_19194_searched; +DROP PROCEDURE IF EXISTS proc_19194_nested_1; +DROP PROCEDURE IF EXISTS proc_19194_nested_2; +DROP PROCEDURE IF EXISTS proc_19194_nested_3; +DROP PROCEDURE IF EXISTS proc_19194_nested_4; +CREATE PROCEDURE proc_19194_simple(i int) +BEGIN +DECLARE str CHAR(10); +CASE i +WHEN 1 THEN SET str="1"; +WHEN 2 THEN SET str="2"; +WHEN 3 THEN SET str="3"; +ELSE SET str="unknown"; +END CASE; +SELECT str; +END| +CREATE PROCEDURE proc_19194_searched(i int) +BEGIN +DECLARE str CHAR(10); +CASE +WHEN i=1 THEN SET str="1"; +WHEN i=2 THEN SET str="2"; +WHEN i=3 THEN SET str="3"; +ELSE SET str="unknown"; +END CASE; +SELECT str; +END| +CREATE PROCEDURE proc_19194_nested_1(i int, j int) +BEGIN +DECLARE str_i CHAR(10); +DECLARE str_j CHAR(10); +CASE i +WHEN 10 THEN SET str_i="10"; +WHEN 20 THEN +BEGIN +set str_i="20"; +CASE +WHEN j=1 THEN SET str_j="1"; +WHEN j=2 THEN SET str_j="2"; +WHEN j=3 THEN SET str_j="3"; +ELSE SET str_j="unknown"; +END CASE; +select "i was 20"; +END; +WHEN 30 THEN SET str_i="30"; +WHEN 40 THEN SET str_i="40"; +ELSE SET str_i="unknown"; +END CASE; +SELECT str_i, str_j; +END| +CREATE PROCEDURE proc_19194_nested_2(i int, j int) +BEGIN +DECLARE str_i CHAR(10); +DECLARE str_j CHAR(10); +CASE +WHEN i=10 THEN SET str_i="10"; +WHEN i=20 THEN +BEGIN +set str_i="20"; +CASE j +WHEN 1 THEN SET str_j="1"; +WHEN 2 THEN SET str_j="2"; +WHEN 3 THEN SET str_j="3"; +ELSE SET str_j="unknown"; +END CASE; +select "i was 20"; +END; +WHEN i=30 THEN SET str_i="30"; +WHEN i=40 THEN SET str_i="40"; +ELSE SET str_i="unknown"; +END CASE; +SELECT str_i, str_j; +END| +CREATE PROCEDURE proc_19194_nested_3(i int, j int) +BEGIN +DECLARE str_i CHAR(10); +DECLARE str_j CHAR(10); +CASE i +WHEN 10 THEN SET str_i="10"; +WHEN 20 THEN +BEGIN +set str_i="20"; +CASE j +WHEN 1 THEN SET str_j="1"; +WHEN 2 THEN SET str_j="2"; +WHEN 3 THEN SET str_j="3"; +ELSE SET str_j="unknown"; +END CASE; +select "i was 20"; +END; +WHEN 30 THEN SET str_i="30"; +WHEN 40 THEN SET str_i="40"; +ELSE SET str_i="unknown"; +END CASE; +SELECT str_i, str_j; +END| +CREATE PROCEDURE proc_19194_nested_4(i int, j int) +BEGIN +DECLARE str_i CHAR(10); +DECLARE str_j CHAR(10); +CASE +WHEN i=10 THEN SET str_i="10"; +WHEN i=20 THEN +BEGIN +set str_i="20"; +CASE +WHEN j=1 THEN SET str_j="1"; +WHEN j=2 THEN SET str_j="2"; +WHEN j=3 THEN SET str_j="3"; +ELSE SET str_j="unknown"; +END CASE; +select "i was 20"; +END; +WHEN i=30 THEN SET str_i="30"; +WHEN i=40 THEN SET str_i="40"; +ELSE SET str_i="unknown"; +END CASE; +SELECT str_i, str_j; +END| +SHOW PROCEDURE CODE proc_19194_simple; +Pos Instruction +0 set str@1 NULL +1 set_case_expr (12) 0 i@0 +2 jump_if_not 5(12) (case_expr@0 = 1) +3 set str@1 _latin1'1' +4 jump 12 +5 jump_if_not 8(12) (case_expr@0 = 2) +6 set str@1 _latin1'2' +7 jump 12 +8 jump_if_not 11(12) (case_expr@0 = 3) +9 set str@1 _latin1'3' +10 jump 12 +11 set str@1 _latin1'unknown' +12 stmt 0 "SELECT str" +SHOW PROCEDURE CODE proc_19194_searched; +Pos Instruction +0 set str@1 NULL +1 jump_if_not 4(11) (i@0 = 1) +2 set str@1 _latin1'1' +3 jump 11 +4 jump_if_not 7(11) (i@0 = 2) +5 set str@1 _latin1'2' +6 jump 11 +7 jump_if_not 10(11) (i@0 = 3) +8 set str@1 _latin1'3' +9 jump 11 +10 set str@1 _latin1'unknown' +11 stmt 0 "SELECT str" +SHOW PROCEDURE CODE proc_19194_nested_1; +Pos Instruction +0 set str_i@2 NULL +1 set str_j@3 NULL +2 set_case_expr (27) 0 i@0 +3 jump_if_not 6(27) (case_expr@0 = 10) +4 set str_i@2 _latin1'10' +5 jump 27 +6 jump_if_not 20(27) (case_expr@0 = 20) +7 set str_i@2 _latin1'20' +8 jump_if_not 11(18) (j@1 = 1) +9 set str_j@3 _latin1'1' +10 jump 18 +11 jump_if_not 14(18) (j@1 = 2) +12 set str_j@3 _latin1'2' +13 jump 18 +14 jump_if_not 17(18) (j@1 = 3) +15 set str_j@3 _latin1'3' +16 jump 18 +17 set str_j@3 _latin1'unknown' +18 stmt 0 "select "i was 20"" +19 jump 27 +20 jump_if_not 23(27) (case_expr@0 = 30) +21 set str_i@2 _latin1'30' +22 jump 27 +23 jump_if_not 26(27) (case_expr@0 = 40) +24 set str_i@2 _latin1'40' +25 jump 27 +26 set str_i@2 _latin1'unknown' +27 stmt 0 "SELECT str_i, str_j" +SHOW PROCEDURE CODE proc_19194_nested_2; +Pos Instruction +0 set str_i@2 NULL +1 set str_j@3 NULL +2 jump_if_not 5(27) (i@0 = 10) +3 set str_i@2 _latin1'10' +4 jump 27 +5 jump_if_not 20(27) (i@0 = 20) +6 set str_i@2 _latin1'20' +7 set_case_expr (18) 0 j@1 +8 jump_if_not 11(18) (case_expr@0 = 1) +9 set str_j@3 _latin1'1' +10 jump 18 +11 jump_if_not 14(18) (case_expr@0 = 2) +12 set str_j@3 _latin1'2' +13 jump 18 +14 jump_if_not 17(18) (case_expr@0 = 3) +15 set str_j@3 _latin1'3' +16 jump 18 +17 set str_j@3 _latin1'unknown' +18 stmt 0 "select "i was 20"" +19 jump 27 +20 jump_if_not 23(27) (i@0 = 30) +21 set str_i@2 _latin1'30' +22 jump 27 +23 jump_if_not 26(27) (i@0 = 40) +24 set str_i@2 _latin1'40' +25 jump 27 +26 set str_i@2 _latin1'unknown' +27 stmt 0 "SELECT str_i, str_j" +SHOW PROCEDURE CODE proc_19194_nested_3; +Pos Instruction +0 set str_i@2 NULL +1 set str_j@3 NULL +2 set_case_expr (28) 0 i@0 +3 jump_if_not 6(28) (case_expr@0 = 10) +4 set str_i@2 _latin1'10' +5 jump 28 +6 jump_if_not 21(28) (case_expr@0 = 20) +7 set str_i@2 _latin1'20' +8 set_case_expr (19) 1 j@1 +9 jump_if_not 12(19) (case_expr@1 = 1) +10 set str_j@3 _latin1'1' +11 jump 19 +12 jump_if_not 15(19) (case_expr@1 = 2) +13 set str_j@3 _latin1'2' +14 jump 19 +15 jump_if_not 18(19) (case_expr@1 = 3) +16 set str_j@3 _latin1'3' +17 jump 19 +18 set str_j@3 _latin1'unknown' +19 stmt 0 "select "i was 20"" +20 jump 28 +21 jump_if_not 24(28) (case_expr@0 = 30) +22 set str_i@2 _latin1'30' +23 jump 28 +24 jump_if_not 27(28) (case_expr@0 = 40) +25 set str_i@2 _latin1'40' +26 jump 28 +27 set str_i@2 _latin1'unknown' +28 stmt 0 "SELECT str_i, str_j" +SHOW PROCEDURE CODE proc_19194_nested_4; +Pos Instruction +0 set str_i@2 NULL +1 set str_j@3 NULL +2 jump_if_not 5(26) (i@0 = 10) +3 set str_i@2 _latin1'10' +4 jump 26 +5 jump_if_not 19(26) (i@0 = 20) +6 set str_i@2 _latin1'20' +7 jump_if_not 10(17) (j@1 = 1) +8 set str_j@3 _latin1'1' +9 jump 17 +10 jump_if_not 13(17) (j@1 = 2) +11 set str_j@3 _latin1'2' +12 jump 17 +13 jump_if_not 16(17) (j@1 = 3) +14 set str_j@3 _latin1'3' +15 jump 17 +16 set str_j@3 _latin1'unknown' +17 stmt 0 "select "i was 20"" +18 jump 26 +19 jump_if_not 22(26) (i@0 = 30) +20 set str_i@2 _latin1'30' +21 jump 26 +22 jump_if_not 25(26) (i@0 = 40) +23 set str_i@2 _latin1'40' +24 jump 26 +25 set str_i@2 _latin1'unknown' +26 stmt 0 "SELECT str_i, str_j" +CALL proc_19194_nested_1(10, 1); +str_i str_j +10 NULL +CALL proc_19194_nested_1(25, 1); +str_i str_j +unknown NULL +CALL proc_19194_nested_1(20, 1); +i was 20 +i was 20 +str_i str_j +20 1 +CALL proc_19194_nested_1(20, 2); +i was 20 +i was 20 +str_i str_j +20 2 +CALL proc_19194_nested_1(20, 3); +i was 20 +i was 20 +str_i str_j +20 3 +CALL proc_19194_nested_1(20, 4); +i was 20 +i was 20 +str_i str_j +20 unknown +CALL proc_19194_nested_1(30, 1); +str_i str_j +30 NULL +CALL proc_19194_nested_1(40, 1); +str_i str_j +40 NULL +CALL proc_19194_nested_1(0, 0); +str_i str_j +unknown NULL +CALL proc_19194_nested_2(10, 1); +str_i str_j +10 NULL +CALL proc_19194_nested_2(25, 1); +str_i str_j +unknown NULL +CALL proc_19194_nested_2(20, 1); +i was 20 +i was 20 +str_i str_j +20 1 +CALL proc_19194_nested_2(20, 2); +i was 20 +i was 20 +str_i str_j +20 2 +CALL proc_19194_nested_2(20, 3); +i was 20 +i was 20 +str_i str_j +20 3 +CALL proc_19194_nested_2(20, 4); +i was 20 +i was 20 +str_i str_j +20 unknown +CALL proc_19194_nested_2(30, 1); +str_i str_j +30 NULL +CALL proc_19194_nested_2(40, 1); +str_i str_j +40 NULL +CALL proc_19194_nested_2(0, 0); +str_i str_j +unknown NULL +CALL proc_19194_nested_3(10, 1); +str_i str_j +10 NULL +CALL proc_19194_nested_3(25, 1); +str_i str_j +unknown NULL +CALL proc_19194_nested_3(20, 1); +i was 20 +i was 20 +str_i str_j +20 1 +CALL proc_19194_nested_3(20, 2); +i was 20 +i was 20 +str_i str_j +20 2 +CALL proc_19194_nested_3(20, 3); +i was 20 +i was 20 +str_i str_j +20 3 +CALL proc_19194_nested_3(20, 4); +i was 20 +i was 20 +str_i str_j +20 unknown +CALL proc_19194_nested_3(30, 1); +str_i str_j +30 NULL +CALL proc_19194_nested_3(40, 1); +str_i str_j +40 NULL +CALL proc_19194_nested_3(0, 0); +str_i str_j +unknown NULL +CALL proc_19194_nested_4(10, 1); +str_i str_j +10 NULL +CALL proc_19194_nested_4(25, 1); +str_i str_j +unknown NULL +CALL proc_19194_nested_4(20, 1); +i was 20 +i was 20 +str_i str_j +20 1 +CALL proc_19194_nested_4(20, 2); +i was 20 +i was 20 +str_i str_j +20 2 +CALL proc_19194_nested_4(20, 3); +i was 20 +i was 20 +str_i str_j +20 3 +CALL proc_19194_nested_4(20, 4); +i was 20 +i was 20 +str_i str_j +20 unknown +CALL proc_19194_nested_4(30, 1); +str_i str_j +30 NULL +CALL proc_19194_nested_4(40, 1); +str_i str_j +40 NULL +CALL proc_19194_nested_4(0, 0); +str_i str_j +unknown NULL +DROP PROCEDURE proc_19194_simple; +DROP PROCEDURE proc_19194_searched; +DROP PROCEDURE proc_19194_nested_1; +DROP PROCEDURE proc_19194_nested_2; +DROP PROCEDURE proc_19194_nested_3; +DROP PROCEDURE proc_19194_nested_4; DROP PROCEDURE IF EXISTS p1; CREATE PROCEDURE p1() CREATE INDEX idx ON t1 (c1); SHOW PROCEDURE CODE p1; diff --git a/mysql-test/r/sp_stress_case.result b/mysql-test/r/sp_stress_case.result new file mode 100644 index 00000000000..8ec68363c8d --- /dev/null +++ b/mysql-test/r/sp_stress_case.result @@ -0,0 +1,120 @@ +DROP PROCEDURE IF EXISTS proc_19194_codegen; +DROP PROCEDURE IF EXISTS bug_19194_simple; +DROP PROCEDURE IF EXISTS bug_19194_searched; +CREATE PROCEDURE proc_19194_codegen( +IN proc_name VARCHAR(50), +IN count INTEGER, +IN simple INTEGER, +OUT body MEDIUMTEXT) +BEGIN +DECLARE code MEDIUMTEXT; +DECLARE i INT DEFAULT 1; +SET code = concat("CREATE PROCEDURE ", proc_name, "(i INT)\n"); +SET code = concat(code, "BEGIN\n"); +SET code = concat(code, " DECLARE str CHAR(10);\n"); +IF (simple) +THEN +SET code = concat(code, " CASE i\n"); +ELSE +SET code = concat(code, " CASE\n"); +END IF; +WHILE (i <= count) +DO +IF (simple) +THEN +SET code = concat(code, " WHEN ", i, " THEN SET str=\"", i, "\";\n"); +ELSE +SET code = concat(code, " WHEN i=", i, " THEN SET str=\"", i, "\";\n"); +END IF; +SET i = i + 1; +END WHILE; +SET code = concat(code, " ELSE SET str=\"unknown\";\n"); +SET code = concat(code, " END CASE;\n"); +SET code = concat(code, " SELECT str;\n"); +SET code = concat(code, "END\n"); +SET body = code; +END| +set @body=""; +call proc_19194_codegen("test_simple", 10, 1, @body); +select @body; +@body +CREATE PROCEDURE test_simple(i INT) +BEGIN + DECLARE str CHAR(10); + CASE i + WHEN 1 THEN SET str="1"; + WHEN 2 THEN SET str="2"; + WHEN 3 THEN SET str="3"; + WHEN 4 THEN SET str="4"; + WHEN 5 THEN SET str="5"; + WHEN 6 THEN SET str="6"; + WHEN 7 THEN SET str="7"; + WHEN 8 THEN SET str="8"; + WHEN 9 THEN SET str="9"; + WHEN 10 THEN SET str="10"; + ELSE SET str="unknown"; + END CASE; + SELECT str; +END + +call proc_19194_codegen("test_searched", 10, 0, @body); +select @body; +@body +CREATE PROCEDURE test_searched(i INT) +BEGIN + DECLARE str CHAR(10); + CASE + WHEN i=1 THEN SET str="1"; + WHEN i=2 THEN SET str="2"; + WHEN i=3 THEN SET str="3"; + WHEN i=4 THEN SET str="4"; + WHEN i=5 THEN SET str="5"; + WHEN i=6 THEN SET str="6"; + WHEN i=7 THEN SET str="7"; + WHEN i=8 THEN SET str="8"; + WHEN i=9 THEN SET str="9"; + WHEN i=10 THEN SET str="10"; + ELSE SET str="unknown"; + END CASE; + SELECT str; +END + +CALL bug_19194_simple(1); +str +1 +CALL bug_19194_simple(2); +str +2 +CALL bug_19194_simple(1000); +str +1000 +CALL bug_19194_simple(4998); +str +4998 +CALL bug_19194_simple(4999); +str +4999 +CALL bug_19194_simple(9999); +str +unknown +CALL bug_19194_searched(1); +str +1 +CALL bug_19194_searched(2); +str +2 +CALL bug_19194_searched(1000); +str +1000 +CALL bug_19194_searched(4998); +str +4998 +CALL bug_19194_searched(4999); +str +4999 +CALL bug_19194_searched(9999); +str +unknown +DROP PROCEDURE proc_19194_codegen; +DROP PROCEDURE bug_19194_simple; +DROP PROCEDURE bug_19194_searched; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index ee057695cd1..377870696e1 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -3033,6 +3033,19 @@ t3 CREATE TABLE `t3` ( `a` datetime DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1,t2,t3; +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (1), (2); +SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) > 0; +a +SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL; +a +1 +2 +EXPLAIN SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +DROP TABLE t1; create table t1 (df decimal(5,1)); insert into t1 values(1.1); insert into t1 values(2.2); diff --git a/mysql-test/r/type_binary.result b/mysql-test/r/type_binary.result index 0b340502eaf..432c58272a2 100644 --- a/mysql-test/r/type_binary.result +++ b/mysql-test/r/type_binary.result @@ -136,4 +136,13 @@ insert into t1 values(NULL, 0x412020); ERROR 22001: Data too long for column 'vb' at row 1 drop table t1; set @@sql_mode= @old_sql_mode; +create table t1(f1 int, f2 binary(2) not null, f3 char(2) not null); +insert into t1 set f1=1; +Warnings: +Warning 1364 Field 'f2' doesn't have a default value +Warning 1364 Field 'f3' doesn't have a default value +select hex(f2), hex(f3) from t1; +hex(f2) hex(f3) +0000 +drop table t1; End of 5.0 tests diff --git a/mysql-test/r/type_enum.result b/mysql-test/r/type_enum.result index b7db1d95587..70ef98af420 100644 --- a/mysql-test/r/type_enum.result +++ b/mysql-test/r/type_enum.result @@ -1754,3 +1754,29 @@ t1 CREATE TABLE `t1` ( `f2` enum('') DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +End of 4.1 tests +create table t1(russian enum('E','F','EF','FE') NOT NULL DEFAULT'E'); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `russian` enum('E','F','EF','FE') NOT NULL DEFAULT 'E' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1(denormal enum('E','F','E,F','F,E') NOT NULL DEFAULT'E'); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `denormal` enum('E','F','E,F','F,E') NOT NULL DEFAULT 'E' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1(russian_deviant enum('E','F','EF','F,E') NOT NULL DEFAULT'E'); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `russian_deviant` enum('E','F','EF','F,E') NOT NULL DEFAULT 'E' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1(exhausting_charset enum('ABCDEFGHIJKLMNOPQRSTUVWXYZ',' +
!"','#$%&\'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\\]^_`abcdefghijklmnopqrstuvwxyz{|}~','xx\','yy\','zz')); +ERROR 42000: Field separator argument is not what is expected; check the manual +End of 5.1 tests diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result index abc654c4b74..3e2cceddbe5 100644 --- a/mysql-test/r/udf.result +++ b/mysql-test/r/udf.result @@ -232,6 +232,17 @@ DROP FUNCTION sequence; DROP FUNCTION lookup; DROP FUNCTION reverse_lookup; DROP FUNCTION avgcost; +select * from mysql.func; +name ret dl type +CREATE FUNCTION is_const RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; +select IS_const(3); +IS_const(3) +const +drop function IS_const; +select * from mysql.func; +name ret dl type +select is_const(3); +ERROR 42000: FUNCTION test.is_const does not exist CREATE FUNCTION is_const RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; select is_const(3) as const, diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result index 8382f521b84..3a70dcddd24 100644 --- a/mysql-test/r/user_var.result +++ b/mysql-test/r/user_var.result @@ -301,7 +301,14 @@ select @var:=f2 from t1 group by f1 order by f2 desc limit 1; select @var; @var 3 -drop table t1; +create table t2 as select @var:=f2 from t1 group by f1 order by f2 desc limit 1; +select * from t2; +@var:=f2 +3 +select @var; +@var +3 +drop table t1,t2; insert into city 'blah'; 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 ''blah'' at line 1 SHOW COUNT(*) WARNINGS; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 4fbe0176c57..13c310c4df2 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -3014,6 +3014,15 @@ i j 6 3 DROP VIEW v1, v2; DROP TABLE t1; +DROP VIEW IF EXISTS v1; +CREATE VIEW v1 AS SELECT 'The\ZEnd'; +SELECT * FROM v1; +TheEnd +TheEnd +SHOW CREATE VIEW v1; +View Create View +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _latin1'The\ZEnd' AS `TheEnd` +DROP VIEW v1; End of 5.0 tests. DROP DATABASE IF EXISTS `d-1`; CREATE DATABASE `d-1`; diff --git a/mysql-test/t/csv.test b/mysql-test/t/csv.test index 369b01b4ab7..c7d34a43d95 100644 --- a/mysql-test/t/csv.test +++ b/mysql-test/t/csv.test @@ -1620,3 +1620,16 @@ insert into float_test values(9.67,'string'); select * from float_test; drop table float_test; +# +# Bug #21328 mysqld issues warnings on ALTER CSV table to MyISAM +# + +CREATE TABLE `bug21328` ( + `col1` int(11) DEFAULT NULL, + `col2` int(11) DEFAULT NULL, + `col3` int(11) DEFAULT NULL +) ENGINE=CSV; + +insert into bug21328 values (1,NULL,NULL); +alter table bug21328 engine=myisam; +drop table bug21328; diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index b2cc42cc0ff..8734b940241 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -504,3 +504,24 @@ DROP TABLE t1; #DROP TABLE t1; #DROP TABLE t2; +# +# Bug #15881: cast problems +# +CREATE TABLE t1 (a CHAR(1)); INSERT INTO t1 VALUES('A'), (0); +SELECT a FROM t1 WHERE a=0; +SELECT DISTINCT a FROM t1 WHERE a=0; +DROP TABLE t1; +CREATE TABLE t1 (a DATE); +INSERT INTO t1 VALUES ('1972-07-29'), ('1972-02-06'); +EXPLAIN SELECT (SELECT DISTINCT a FROM t1 WHERE a = '2002-08-03'); +EXPLAIN SELECT (SELECT DISTINCT ADDDATE(a,1) FROM t1 + WHERE ADDDATE(a,1) = '2002-08-03'); +CREATE TABLE t2 (a CHAR(5) CHARACTER SET latin1 COLLATE latin1_general_ci); +INSERT INTO t2 VALUES (0xf6); +INSERT INTO t2 VALUES ('oe'); + +SELECT COUNT(*) FROM (SELECT DISTINCT a FROM t2) dt; +SELECT COUNT(*) FROM + (SELECT DISTINCT a FROM t2 WHERE a='oe' COLLATE latin1_german2_ci) dt; + +DROP TABLE t1, t2; diff --git a/mysql-test/t/gis-rtree.test b/mysql-test/t/gis-rtree.test index e34dd14dbfc..1704fe7dc80 100644 --- a/mysql-test/t/gis-rtree.test +++ b/mysql-test/t/gis-rtree.test @@ -254,3 +254,20 @@ INSERT INTO t1() VALUES (); --error 1416 INSERT INTO t1(foo) VALUES (''); DROP TABLE t1; + +# +# Bug #23578: Corruption prevents Optimize table from working properly with a +# spatial index +# + +CREATE TABLE t1 (a INT AUTO_INCREMENT, b POINT NOT NULL, KEY (a), SPATIAL KEY (b)); + +INSERT INTO t1 (b) VALUES (GeomFromText('POINT(1 2)')); +INSERT INTO t1 (b) SELECT b FROM t1; +INSERT INTO t1 (b) SELECT b FROM t1; +INSERT INTO t1 (b) SELECT b FROM t1; +INSERT INTO t1 (b) SELECT b FROM t1; +INSERT INTO t1 (b) SELECT b FROM t1; + +OPTIMIZE TABLE t1; +DROP TABLE t1; diff --git a/mysql-test/t/information_schema_inno.test b/mysql-test/t/information_schema_inno.test index 195bf57a880..014bdacfeea 100644 --- a/mysql-test/t/information_schema_inno.test +++ b/mysql-test/t/information_schema_inno.test @@ -1,3 +1,4 @@ +-- source include/testdb_only.inc -- source include/have_innodb.inc --disable_warnings DROP TABLE IF EXISTS t1,t2,t3; @@ -53,3 +54,26 @@ from information_schema.TABLE_CONSTRAINTS a, where a.CONSTRAINT_SCHEMA = 'test' and a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA and a.CONSTRAINT_NAME = b.CONSTRAINT_NAME; drop tables t5, t4, t3, t2, t1; + +# +# Bug#25026 `information_schema.KEY_COLUMN_USAGE`.`REFERENCED_TABLE_NAME` returns garbage +# +create database `db-1`; +use `db-1`; +create table `t-2` ( + id int(10) unsigned not null auto_increment, + primary key (id) +) engine=innodb; + +create table `t-1` ( + id int(10) unsigned not null auto_increment, + idtype int(10) unsigned not null, + primary key (id), + key fk_t1_1 (idtype), + constraint fk_t1_1 foreign key (idtype) references `t-2` (id) +) engine=innodb; +use test; +select referenced_table_schema, referenced_table_name +from information_schema.key_column_usage +where constraint_schema = 'db-1'; +drop database `db-1`; diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index e277981e626..72d78dd7074 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -631,3 +631,21 @@ select '^^: The above should be ~= 20 + cost(select * from t1). Value less than drop table t1, t2; +# BUG#25106: A USING clause in combination with a VIEW results in column +# aliases ignored +# +CREATE TABLE t1 (ID INTEGER, Name VARCHAR(50)); +CREATE TABLE t2 (Test_ID INTEGER); +CREATE VIEW v1 (Test_ID, Description) AS SELECT ID, Name FROM t1; + +CREATE TABLE tv1 SELECT Description AS Name FROM v1 JOIN t2 + USING (Test_ID); +DESCRIBE tv1; +CREATE TABLE tv2 SELECT Description AS Name FROM v1 JOIN t2 + ON v1.Test_ID = t2.Test_ID; +DESCRIBE tv2; + +DROP VIEW v1; +DROP TABLE t1,t2,tv1,tv2; + +--echo End of 5.0 tests. diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test index 69886d035bf..e7405418be7 100644 --- a/mysql-test/t/join_nested.test +++ b/mysql-test/t/join_nested.test @@ -994,3 +994,54 @@ SELECT t1.id1 AS id, t5.id1 AS ngroupbynsa DROP TABLE t1,t2,t3,t4,t5; +# +# Test for bug #24345: crash with nested left outer join when outer table is substituted +# for a row that happens to have a null value for the join attribute. +# + +CREATE TABLE t1 ( + id int NOT NULL PRIMARY KEY, + ct int DEFAULT NULL, + pc int DEFAULT NULL, + INDEX idx_ct (ct), + INDEX idx_pc (pc) +); +INSERT INTO t1 VALUES + (1,NULL,NULL),(2,NULL,NULL),(3,NULL,NULL),(4,NULL,NULL),(5,NULL,NULL); + +CREATE TABLE t2 ( + id int NOT NULL PRIMARY KEY, + sr int NOT NULL, + nm varchar(255) NOT NULL, + INDEX idx_sr (sr) +); +INSERT INTO t2 VALUES + (2441905,4308,'LesAbymes'),(2441906,4308,'Anse-Bertrand'); + +CREATE TABLE t3 ( + id int NOT NULL PRIMARY KEY, + ct int NOT NULL, + ln int NOT NULL, + INDEX idx_ct (ct), + INDEX idx_ln (ln) +); + +CREATE TABLE t4 ( + id int NOT NULL PRIMARY KEY, + nm varchar(255) NOT NULL +); + +INSERT INTO t4 VALUES (4308,'Guadeloupe'),(4309,'Martinique'); + +SELECT t1.* + FROM t1 LEFT JOIN + (t2 LEFT JOIN t3 ON t3.ct=t2.id AND t3.ln='5') ON t1.ct=t2.id + WHERE t1.id='5'; + +SELECT t1.*, t4.nm + FROM t1 LEFT JOIN + (t2 LEFT JOIN t3 ON t3.ct=t2.id AND t3.ln='5') ON t1.ct=t2.id + LEFT JOIN t4 ON t2.sr=t4.id + WHERE t1.id='5'; + +DROP TABLE t1,t2,t3,t4; diff --git a/mysql-test/t/log.sh b/mysql-test/t/log.sh index 20b265087cc..29cf8d3e1a3 100755 --- a/mysql-test/t/log.sh +++ b/mysql-test/t/log.sh @@ -1,4 +1,4 @@ -#!/bin/bash +#!/bin/sh ########################################################################### diff --git a/mysql-test/t/ndb_lock.test b/mysql-test/t/ndb_lock.test index 474155e06f4..b036522ad15 100644 --- a/mysql-test/t/ndb_lock.test +++ b/mysql-test/t/ndb_lock.test @@ -102,16 +102,36 @@ connection con1; commit; # table scan +# +# Note that there are two distinct execution paths in which we unlock +# non-matching rows inspected during table scan - one that is used in +# case of filesort and one that used in rest of cases. Below we cover +# the latter (Bug #20390 "SELECT FOR UPDATE does not release locks of +# untouched rows in full table scans"). connection con1; begin; -select * from t1 where y = 'one' or y = 'three' order by x for update; +# We can't use "order by x" here as it will cause filesort +--replace_column 1 # 2 # 3 # +select * from t1 where y = 'one' or y = 'three' for update; connection con2; begin; # Have to check with pk access here since scans take locks on # all rows and then release them in chunks -# Bug #20390 SELECT FOR UPDATE does not release locks of untouched rows in full table scans -#select * from t1 where x = 2 for update; +select * from t1 where x = 2 for update; +--error 1205 +select * from t1 where x = 1 for update; +rollback; + +connection con1; +commit; + +# And now the test for case with filesort +begin; +select * from t1 where y = 'one' or y = 'three' order by x for update; +connection con2; +begin; +select * from t1 where x = 2 for update; --error 1205 select * from t1 where x = 1 for update; rollback; @@ -157,15 +177,32 @@ commit; # table scan connection con1; begin; -select * from t1 where y = 'one' or y = 'three' order by x lock in share mode; +# We can't use "order by x" here as it will cause filesort +--replace_column 1 # 2 # 3 # +select * from t1 where y = 'one' or y = 'three' lock in share mode; connection con2; begin; select * from t1 where y = 'one' lock in share mode; # Have to check with pk access here since scans take locks on # all rows and then release them in chunks -# Bug #20390 SELECT FOR UPDATE does not release locks of untouched rows in full table scans -#select * from t1 where x = 2 for update; +select * from t1 where x = 2 for update; +--error 1205 +select * from t1 where x = 1 for update; +rollback; + +connection con1; +commit; + +# And the same test for case with filesort +connection con1; +begin; +select * from t1 where y = 'one' or y = 'three' order by x lock in share mode; + +connection con2; +begin; +select * from t1 where y = 'one' lock in share mode; +select * from t1 where x = 2 for update; --error 1205 select * from t1 where x = 1 for update; rollback; diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index d7cf0e2a375..012b38ff8b7 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -44,7 +44,7 @@ INSERT INTO t2 VALUES (7,'Liste des t2','t2_liste_form.phtml',51060,'link.gif'); INSERT INTO t2 VALUES (8,'Consulter les soumissions','consulter_soumissions.phtml',200,'link.gif'); INSERT INTO t2 VALUES (9,'Ajouter un type de materiel','typeMateriel_ajoute_form.phtml',51000,'link.gif'); INSERT INTO t2 VALUES (10,'Lister/modifier un type de materiel','typeMateriel_liste_form.phtml',51010,'link.gif'); -INSERT INTO t2 VALUES (3,'Crer une fiche de client','clients_ajoute_form.phtml',40000,'link.gif'); +INSERT INTO t2 VALUES (3,'Créer une fiche de client','clients_ajoute_form.phtml',40000,'link.gif'); INSERT INTO t2 VALUES (4,'Modifier des clients','en_construction.html',40010,'link.gif'); INSERT INTO t2 VALUES (5,'Effacer des clients','en_construction.html',40020,'link.gif'); INSERT INTO t2 VALUES (6,'Ajouter un service','t2_ajoute_form.phtml',51050,'link.gif'); @@ -588,6 +588,21 @@ SELECT a + 1 AS num, (select num + 2 FROM t1 LIMIT 1) FROM t1; SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a; DROP TABLE t1; +# +# Bug #25427: crash when order by expression contains a name +# that cannot be resolved unambiguously +# + +CREATE TABLE t1 (a int); + +SELECT p.a AS val, q.a AS val1 FROM t1 p, t1 q ORDER BY val > 1; +--error 1052 +SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val; +--error 1052 +SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val > 1; + +DROP TABLE t1; + # End of 4.1 tests create table t1 (a int not null, b int not null, c int not null); insert t1 values (1,1,1),(1,1,2),(1,2,1); diff --git a/mysql-test/t/parser.test b/mysql-test/t/parser.test index 13b4338701e..65aa9dbb89b 100644 --- a/mysql-test/t/parser.test +++ b/mysql-test/t/parser.test @@ -77,8 +77,9 @@ create table GROUP_CONCAT(a int); create table GROUP_CONCAT (a int); drop table GROUP_CONCAT; ---error ER_PARSE_ERROR +# Limitation removed in 5.1 create table GROUP_UNIQUE_USERS(a int); +drop table GROUP_UNIQUE_USERS; create table GROUP_UNIQUE_USERS (a int); drop table GROUP_UNIQUE_USERS; @@ -167,8 +168,9 @@ create table TRIM(a int); create table TRIM (a int); drop table TRIM; ---error ER_PARSE_ERROR +# Limitation removed in 5.1 create table UNIQUE_USERS(a int); +drop table UNIQUE_USERS; create table UNIQUE_USERS (a int); drop table UNIQUE_USERS; @@ -249,10 +251,11 @@ create table GROUP_CONCAT(a int); --error ER_PARSE_ERROR create table GROUP_CONCAT (a int); ---error ER_PARSE_ERROR +# Limitation removed in 5.1 create table GROUP_UNIQUE_USERS(a int); ---error ER_PARSE_ERROR +drop table GROUP_UNIQUE_USERS; create table GROUP_UNIQUE_USERS (a int); +drop table GROUP_UNIQUE_USERS; --error ER_PARSE_ERROR create table MAX(a int); @@ -339,10 +342,11 @@ create table TRIM(a int); --error ER_PARSE_ERROR create table TRIM (a int); ---error ER_PARSE_ERROR +# Limitation removed in 5.1 create table UNIQUE_USERS(a int); ---error ER_PARSE_ERROR +drop table UNIQUE_USERS; create table UNIQUE_USERS (a int); +drop table UNIQUE_USERS; --error ER_PARSE_ERROR create table VARIANCE(a int); diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index 8cb5e76d85b..02c7ca0b05c 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -1354,4 +1354,20 @@ delete from t1 where a = 18446744073709551615; select * from t1; drop table t1; +# +# Bug 24502 reorganize partition closes connection +# +CREATE TABLE t1 ( + num int(11) NOT NULL, cs int(11) NOT NULL) +PARTITION BY RANGE (num) SUBPARTITION BY HASH ( +cs) SUBPARTITIONS 2 (PARTITION p_X VALUES LESS THAN MAXVALUE); + +ALTER TABLE t1 +REORGANIZE PARTITION p_X INTO ( + PARTITION p_100 VALUES LESS THAN (100), + PARTITION p_X VALUES LESS THAN MAXVALUE + ); + +drop table t1; + --echo End of 5.1 tests diff --git a/mysql-test/t/partition_federated.test b/mysql-test/t/partition_federated.test new file mode 100644 index 00000000000..c8483291228 --- /dev/null +++ b/mysql-test/t/partition_federated.test @@ -0,0 +1,21 @@ +# +# Tests for partitioned FEDERATED +# +-- source include/have_partition.inc +-- source include/not_embedded.inc +-- source include/have_federated_db.inc + +--disable_warnings +drop table if exists t1; +--enable_warnings + +# +# Bug #22451 Partitions: duplicate results with engine=federated +# + +--error ER_PARTITION_MERGE_ERROR +create table t1 (s1 int) engine=federated +connection='mysql://root@localhost/federated/t1' partition by list (s1) +(partition p1 values in (1), partition p2 values in (2)); + +--echo End of 5.1 tests diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index f38af9644c9..d65da7f3018 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -1576,6 +1576,29 @@ execute sq; deallocate prepare no_index; deallocate prepare sq; +# +# Bug 25027: query with a single-row non-correlated subquery +# and IS NULL predicate +# + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (1), (2); +CREATE TABLE t2 (b int); +INSERT INTO t2 VALUES (NULL); + +SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL; +PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL'; + +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + +PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2 limit ?) IS NULL'; +SET @arg=1; +EXECUTE stmt USING @arg; +DEALLOCATE PREPARE stmt; + +DROP TABLE t1,t2; + --echo End of 5.0 tests. # diff --git a/mysql-test/t/read_only.test b/mysql-test/t/read_only.test index 8e14b310f4c..709238c3d76 100644 --- a/mysql-test/t/read_only.test +++ b/mysql-test/t/read_only.test @@ -102,6 +102,111 @@ drop table t1; insert into t1 values(1); # +# BUG#11733: COMMITs should not happen if read-only is set +# + +# LOCK TABLE ... WRITE / READ_ONLY +# - is an error in the same connection +# - is ok in a different connection + +connection default; +set global read_only=0; +lock table t1 write; + +connection con1; +lock table t2 write; + +connection default; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +set global read_only=1; +unlock tables ; +# The following call blocks until con1 releases the write lock. +# Blocking is expected. +send set global read_only=1; + +connection con1; +--sleep 1 +select @@global.read_only; +unlock tables ; +--sleep 1 +select @@global.read_only; + +connection default; +reap; + +# LOCK TABLE ... READ / READ_ONLY +# - is an error in the same connection +# - is ok in a different connection + +connection default; +set global read_only=0; +lock table t1 read; + +connection con1; +lock table t2 read; + +connection default; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +set global read_only=1; +unlock tables ; +# The following call blocks until con1 releases the read lock. +# Blocking is a limitation, and could be improved. +send set global read_only=1; + +connection con1; +--sleep 1 +select @@global.read_only; +unlock tables ; +--sleep 1 +select @@global.read_only; + +connection default; +reap; + +# pending transaction / READ_ONLY +# - is an error in the same connection +# - is ok in a different connection + +connection default; +set global read_only=0; +BEGIN; + +connection con1; +BEGIN; + +connection default; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +set global read_only=1; +ROLLBACK; +set global read_only=1; + +connection con1; +select @@global.read_only; +ROLLBACK; + +# Verify that FLUSH TABLES WITH READ LOCK do not block READ_ONLY +# - in the same SUPER connection +# - in another SUPER connection + +connection default; +set global read_only=0; +flush tables with read lock; +set global read_only=1; +unlock tables; + +connect (root2,localhost,root,,test); + +connection default; +set global read_only=0; +flush tables with read lock; + +connection root2; +set global read_only=1; + +connection default; +select @@global.read_only; +unlock tables; + # BUG #22077 "DROP TEMPORARY TABLE fails with wrong error if read_only is set" # # check if DROP TEMPORARY on a non-existing temporary table returns the right @@ -113,8 +218,10 @@ drop temporary table ttt; # check if DROP TEMPORARY TABLE IF EXISTS produces a warning with read_only set drop temporary table if exists ttt; +# +# Cleanup +# connection default; +set global read_only=0; drop table t1,t2; drop user test@localhost; - -set global read_only=0; diff --git a/mysql-test/t/read_only_innodb.test b/mysql-test/t/read_only_innodb.test new file mode 100644 index 00000000000..76d9748aa60 --- /dev/null +++ b/mysql-test/t/read_only_innodb.test @@ -0,0 +1,43 @@ +# should work with embedded server after mysqltest is fixed +-- source include/not_embedded.inc +-- source include/have_innodb.inc + +# +# BUG#11733: COMMITs should not happen if read-only is set +# + +--disable_warnings +DROP TABLE IF EXISTS table_11733 ; +--enable_warnings + +# READ_ONLY does nothing to SUPER users +# so we use a non-SUPER one: + +grant CREATE, SELECT, DROP on *.* to test@localhost; + +connect (con1,localhost,test,,test); + +connection default; +set global read_only=0; + +# Any transactional engine will do +create table table_11733 (a int) engine=InnoDb; + +connection con1; +BEGIN; +insert into table_11733 values(11733); + +connection default; +set global read_only=1; + +connection con1; +select @@global.read_only; +select * from table_11733 ; +-- error ER_OPTION_PREVENTS_STATEMENT +COMMIT; + +connection default; +set global read_only=0; +drop table table_11733 ; +drop user test@localhost; + diff --git a/mysql-test/t/rpl_read_only-slave.opt b/mysql-test/t/rpl_read_only-slave.opt new file mode 100644 index 00000000000..627becdbfb5 --- /dev/null +++ b/mysql-test/t/rpl_read_only-slave.opt @@ -0,0 +1 @@ +--innodb diff --git a/mysql-test/t/rpl_read_only.test b/mysql-test/t/rpl_read_only.test new file mode 100644 index 00000000000..659c3d10044 --- /dev/null +++ b/mysql-test/t/rpl_read_only.test @@ -0,0 +1,105 @@ +# Test case for BUG #11733 +-- source include/master-slave.inc +-- source include/have_innodb.inc + +# Setting the master readonly : +# - the variable @@readonly is not replicated on the slave + +connect (master2,127.0.0.1,test,,test,$MASTER_MYPORT,); +connect (slave2,127.0.0.1,test,,test,$SLAVE_MYPORT,); + +connection master1; + +create table t1(a int) engine=InnoDB; +create table t2(a int) engine=MyISAM; +insert into t1 values(1001); +insert into t2 values(2001); + +connection master; +set global read_only=1; + +connection master1; +select @@read_only; +select * from t1; +select * from t2; + +sync_slave_with_master; +select @@read_only; +select * from t1; +select * from t2; + +# - replication of transactions +connection master; +set global read_only=0; + +connection master1; +BEGIN; +insert into t1 values(1002); +insert into t2 values(2002); + +connection master2; +BEGIN; +insert into t1 values(1003); +insert into t2 values(2003); + +connection master; +set global read_only=1; + +connection master1; +## works even with read_only=1, because master1 is root +COMMIT; + +connection master2; +--error ER_OPTION_PREVENTS_STATEMENT +COMMIT; + +connection master; +set global read_only=0; + +connection master1; +insert into t1 values(1004); +insert into t2 values(2004); + +select * from t1; +select * from t2; + +sync_slave_with_master; +select * from t1; +select * from t2; + +# Setting the slave readonly : replication will pass +# +connection slave1; +set global read_only=1; + +connection slave; +select @@read_only; +# Make sure the replicated table is also transactional +show create table t1; +# Make sure the replicated table is not transactional +show create table t2; + +connection master; +insert into t1 values(1005); +insert into t2 values(2005); +select * from t1; +select * from t2; + +sync_slave_with_master; +connection slave; +select * from t1; +select * from t2; + +# Non root user can not write on the slave +connection slave2; +--error ER_OPTION_PREVENTS_STATEMENT +insert into t1 values(1006); +--error ER_OPTION_PREVENTS_STATEMENT +insert into t2 values(2006); + +## Cleanup +connection master; +drop table t1; +drop table t2; +sync_slave_with_master; + diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 79049353950..3290f2e7e68 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -3093,3 +3093,23 @@ SELECT t3.a FROM t1,t2,t3 t3.c IN ('bb','ee'); DROP TABLE t1,t2,t3; + +# +# Bug#6298: LIMIT #, -1 no longer works to set start with no end limit +# + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1(a int); +INSERT into t1 values (1), (2), (3); + +# LIMIT N, -1 was accepted by accident in 4.0, but was not intended. +# This test verifies that this illegal construct is now properly detected. + +--error ER_PARSE_ERROR +SELECT * FROM t1 LIMIT 2, -1; + +DROP TABLE t1; + diff --git a/mysql-test/t/sp-code.test b/mysql-test/t/sp-code.test index 72efa831059..97bc29fcad2 100644 --- a/mysql-test/t/sp-code.test +++ b/mysql-test/t/sp-code.test @@ -191,6 +191,241 @@ show procedure code sudoku_solve; drop procedure sudoku_solve; +# +# Bug#19194 (Right recursion in parser for CASE causes excessive stack +# usage, limitation) +# This bug also exposed a flaw in the generated code with nested case +# statements +# + +--disable_warnings +DROP PROCEDURE IF EXISTS proc_19194_simple; +DROP PROCEDURE IF EXISTS proc_19194_searched; +DROP PROCEDURE IF EXISTS proc_19194_nested_1; +DROP PROCEDURE IF EXISTS proc_19194_nested_2; +DROP PROCEDURE IF EXISTS proc_19194_nested_3; +DROP PROCEDURE IF EXISTS proc_19194_nested_4; +--enable_warnings + +delimiter |; + +CREATE PROCEDURE proc_19194_simple(i int) +BEGIN + DECLARE str CHAR(10); + + CASE i + WHEN 1 THEN SET str="1"; + WHEN 2 THEN SET str="2"; + WHEN 3 THEN SET str="3"; + ELSE SET str="unknown"; + END CASE; + + SELECT str; +END| + +CREATE PROCEDURE proc_19194_searched(i int) +BEGIN + DECLARE str CHAR(10); + + CASE + WHEN i=1 THEN SET str="1"; + WHEN i=2 THEN SET str="2"; + WHEN i=3 THEN SET str="3"; + ELSE SET str="unknown"; + END CASE; + + SELECT str; +END| + +# Outer SIMPLE case, inner SEARCHED case +CREATE PROCEDURE proc_19194_nested_1(i int, j int) +BEGIN + DECLARE str_i CHAR(10); + DECLARE str_j CHAR(10); + + CASE i + WHEN 10 THEN SET str_i="10"; + WHEN 20 THEN + BEGIN + set str_i="20"; + CASE + WHEN j=1 THEN SET str_j="1"; + WHEN j=2 THEN SET str_j="2"; + WHEN j=3 THEN SET str_j="3"; + ELSE SET str_j="unknown"; + END CASE; + select "i was 20"; + END; + WHEN 30 THEN SET str_i="30"; + WHEN 40 THEN SET str_i="40"; + ELSE SET str_i="unknown"; + END CASE; + + SELECT str_i, str_j; +END| + +# Outer SEARCHED case, inner SIMPLE case +CREATE PROCEDURE proc_19194_nested_2(i int, j int) +BEGIN + DECLARE str_i CHAR(10); + DECLARE str_j CHAR(10); + + CASE + WHEN i=10 THEN SET str_i="10"; + WHEN i=20 THEN + BEGIN + set str_i="20"; + CASE j + WHEN 1 THEN SET str_j="1"; + WHEN 2 THEN SET str_j="2"; + WHEN 3 THEN SET str_j="3"; + ELSE SET str_j="unknown"; + END CASE; + select "i was 20"; + END; + WHEN i=30 THEN SET str_i="30"; + WHEN i=40 THEN SET str_i="40"; + ELSE SET str_i="unknown"; + END CASE; + + SELECT str_i, str_j; +END| + +# Outer SIMPLE case, inner SIMPLE case +CREATE PROCEDURE proc_19194_nested_3(i int, j int) +BEGIN + DECLARE str_i CHAR(10); + DECLARE str_j CHAR(10); + + CASE i + WHEN 10 THEN SET str_i="10"; + WHEN 20 THEN + BEGIN + set str_i="20"; + CASE j + WHEN 1 THEN SET str_j="1"; + WHEN 2 THEN SET str_j="2"; + WHEN 3 THEN SET str_j="3"; + ELSE SET str_j="unknown"; + END CASE; + select "i was 20"; + END; + WHEN 30 THEN SET str_i="30"; + WHEN 40 THEN SET str_i="40"; + ELSE SET str_i="unknown"; + END CASE; + + SELECT str_i, str_j; +END| + +# Outer SEARCHED case, inner SEARCHED case +CREATE PROCEDURE proc_19194_nested_4(i int, j int) +BEGIN + DECLARE str_i CHAR(10); + DECLARE str_j CHAR(10); + + CASE + WHEN i=10 THEN SET str_i="10"; + WHEN i=20 THEN + BEGIN + set str_i="20"; + CASE + WHEN j=1 THEN SET str_j="1"; + WHEN j=2 THEN SET str_j="2"; + WHEN j=3 THEN SET str_j="3"; + ELSE SET str_j="unknown"; + END CASE; + select "i was 20"; + END; + WHEN i=30 THEN SET str_i="30"; + WHEN i=40 THEN SET str_i="40"; + ELSE SET str_i="unknown"; + END CASE; + + SELECT str_i, str_j; +END| + +delimiter ;| + +SHOW PROCEDURE CODE proc_19194_simple; +SHOW PROCEDURE CODE proc_19194_searched; +SHOW PROCEDURE CODE proc_19194_nested_1; +SHOW PROCEDURE CODE proc_19194_nested_2; +SHOW PROCEDURE CODE proc_19194_nested_3; +SHOW PROCEDURE CODE proc_19194_nested_4; + +CALL proc_19194_nested_1(10, 1); + +# +# Before 19194, the generated code was: +# 20 jump_if_not 23(27) 30 +# 21 set str_i@2 _latin1'30' +# As opposed to the expected: +# 20 jump_if_not 23(27) (case_expr@0 = 30) +# 21 set str_i@2 _latin1'30' +# +# and as a result, this call returned "30", +# because the expression 30 is always true, +# masking the case 40, case 0 and the else. +# +CALL proc_19194_nested_1(25, 1); + +CALL proc_19194_nested_1(20, 1); +CALL proc_19194_nested_1(20, 2); +CALL proc_19194_nested_1(20, 3); +CALL proc_19194_nested_1(20, 4); +CALL proc_19194_nested_1(30, 1); +CALL proc_19194_nested_1(40, 1); +CALL proc_19194_nested_1(0, 0); + +CALL proc_19194_nested_2(10, 1); + +# +# Before 19194, the generated code was: +# 20 jump_if_not 23(27) (case_expr@0 = (i@0 = 30)) +# 21 set str_i@2 _latin1'30' +# As opposed to the expected: +# 20 jump_if_not 23(27) (i@0 = 30) +# 21 set str_i@2 _latin1'30' +# and as a result, this call crashed the server, because there is no +# such variable as "case_expr@0". +# +CALL proc_19194_nested_2(25, 1); + +CALL proc_19194_nested_2(20, 1); +CALL proc_19194_nested_2(20, 2); +CALL proc_19194_nested_2(20, 3); +CALL proc_19194_nested_2(20, 4); +CALL proc_19194_nested_2(30, 1); +CALL proc_19194_nested_2(40, 1); +CALL proc_19194_nested_2(0, 0); + +CALL proc_19194_nested_3(10, 1); +CALL proc_19194_nested_3(25, 1); +CALL proc_19194_nested_3(20, 1); +CALL proc_19194_nested_3(20, 2); +CALL proc_19194_nested_3(20, 3); +CALL proc_19194_nested_3(20, 4); +CALL proc_19194_nested_3(30, 1); +CALL proc_19194_nested_3(40, 1); +CALL proc_19194_nested_3(0, 0); + +CALL proc_19194_nested_4(10, 1); +CALL proc_19194_nested_4(25, 1); +CALL proc_19194_nested_4(20, 1); +CALL proc_19194_nested_4(20, 2); +CALL proc_19194_nested_4(20, 3); +CALL proc_19194_nested_4(20, 4); +CALL proc_19194_nested_4(30, 1); +CALL proc_19194_nested_4(40, 1); +CALL proc_19194_nested_4(0, 0); + +DROP PROCEDURE proc_19194_simple; +DROP PROCEDURE proc_19194_searched; +DROP PROCEDURE proc_19194_nested_1; +DROP PROCEDURE proc_19194_nested_2; +DROP PROCEDURE proc_19194_nested_3; +DROP PROCEDURE proc_19194_nested_4; # # Bug#19207: Final parenthesis omitted for CREATE INDEX in Stored diff --git a/mysql-test/t/sp_stress_case.test b/mysql-test/t/sp_stress_case.test new file mode 100644 index 00000000000..1b5bd8991a9 --- /dev/null +++ b/mysql-test/t/sp_stress_case.test @@ -0,0 +1,89 @@ +# +# Bug#19194 (Right recursion in parser for CASE causes excessive stack +# usage, limitation) +# + +--disable_warnings +DROP PROCEDURE IF EXISTS proc_19194_codegen; +DROP PROCEDURE IF EXISTS bug_19194_simple; +DROP PROCEDURE IF EXISTS bug_19194_searched; +--enable_warnings + +delimiter |; + +CREATE PROCEDURE proc_19194_codegen( + IN proc_name VARCHAR(50), + IN count INTEGER, + IN simple INTEGER, + OUT body MEDIUMTEXT) +BEGIN + DECLARE code MEDIUMTEXT; + DECLARE i INT DEFAULT 1; + + SET code = concat("CREATE PROCEDURE ", proc_name, "(i INT)\n"); + SET code = concat(code, "BEGIN\n"); + SET code = concat(code, " DECLARE str CHAR(10);\n"); + + IF (simple) + THEN + SET code = concat(code, " CASE i\n"); + ELSE + SET code = concat(code, " CASE\n"); + END IF; + + WHILE (i <= count) + DO + IF (simple) + THEN + SET code = concat(code, " WHEN ", i, " THEN SET str=\"", i, "\";\n"); + ELSE + SET code = concat(code, " WHEN i=", i, " THEN SET str=\"", i, "\";\n"); + END IF; + + SET i = i + 1; + END WHILE; + + SET code = concat(code, " ELSE SET str=\"unknown\";\n"); + SET code = concat(code, " END CASE;\n"); + SET code = concat(code, " SELECT str;\n"); + + SET code = concat(code, "END\n"); + + SET body = code; +END| + +delimiter ;| + +set @body=""; +call proc_19194_codegen("test_simple", 10, 1, @body); +select @body; +call proc_19194_codegen("test_searched", 10, 0, @body); +select @body; + +--disable_query_log +call proc_19194_codegen("bug_19194_simple", 5000, 1, @body); +let $proc_body = `select @body`; +eval $proc_body; +call proc_19194_codegen("bug_19194_searched", 5000, 1, @body); +let $proc_body = `select @body`; +eval $proc_body; +--enable_query_log + +CALL bug_19194_simple(1); +CALL bug_19194_simple(2); +CALL bug_19194_simple(1000); +CALL bug_19194_simple(4998); +CALL bug_19194_simple(4999); +CALL bug_19194_simple(9999); + +CALL bug_19194_searched(1); +CALL bug_19194_searched(2); +CALL bug_19194_searched(1000); +CALL bug_19194_searched(4998); +CALL bug_19194_searched(4999); +CALL bug_19194_searched(9999); + +DROP PROCEDURE proc_19194_codegen; +DROP PROCEDURE bug_19194_simple; +DROP PROCEDURE bug_19194_searched; + diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 2f65ee44d3c..eefa2528a17 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -1988,6 +1988,18 @@ SHOW CREATE TABLE t3; DROP TABLE t1,t2,t3; +# +# Bug 24670: subquery witout tables but with a WHERE clause +# + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (1), (2); + +SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) > 0; +SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL; +EXPLAIN SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL; + +DROP TABLE t1; # End of 4.1 tests # diff --git a/mysql-test/t/type_binary.test b/mysql-test/t/type_binary.test index 1639aff4711..91eba9b328e 100644 --- a/mysql-test/t/type_binary.test +++ b/mysql-test/t/type_binary.test @@ -91,4 +91,12 @@ insert into t1 values(NULL, 0x412020); drop table t1; set @@sql_mode= @old_sql_mode; +# +# Bug#14171: Wrong default value for a BINARY field +# +create table t1(f1 int, f2 binary(2) not null, f3 char(2) not null); +insert into t1 set f1=1; +select hex(f2), hex(f3) from t1; +drop table t1; + --echo End of 5.0 tests diff --git a/mysql-test/t/type_enum.test b/mysql-test/t/type_enum.test index 68f5664c36d..9be1b7fa10f 100644 --- a/mysql-test/t/type_enum.test +++ b/mysql-test/t/type_enum.test @@ -136,4 +136,26 @@ alter table t1 add f2 enum(0xFFFF); show create table t1; drop table t1; -# End of 4.1 tests +--echo End of 4.1 tests + +# +# Bug#24660 "enum" field type definition problem +# +create table t1(russian enum('E','F','EF','FE') NOT NULL DEFAULT'E'); +show create table t1; +drop table t1; + +create table t1(denormal enum('E','F','E,F','F,E') NOT NULL DEFAULT'E'); +show create table t1; +drop table t1; + +create table t1(russian_deviant enum('E','F','EF','F,E') NOT NULL DEFAULT'E'); +show create table t1; +drop table t1; + +# ER_WRONG_FIELD_TERMINATORS +--error 1083 +create table t1(exhausting_charset enum('ABCDEFGHIJKLMNOPQRSTUVWXYZ',' +
!"','#$%&\'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\\]^_`abcdefghijklmnopqrstuvwxyz{|}~','xx\','yy\','zz')); + +--echo End of 5.1 tests diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test index b0580faea3d..010a532f48b 100644 --- a/mysql-test/t/udf.test +++ b/mysql-test/t/udf.test @@ -240,6 +240,23 @@ DROP FUNCTION reverse_lookup; DROP FUNCTION avgcost; # +# Bug #15439: UDF name case handling forces DELETE FROM mysql.func to remove +# the UDF +# +select * from mysql.func; +--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB +eval CREATE FUNCTION is_const RETURNS STRING SONAME "$UDF_EXAMPLE_LIB"; + +select IS_const(3); + +drop function IS_const; + +select * from mysql.func; + +--error 1305 +select is_const(3); + +# # Bug#18761: constant expression as UDF parameters not passed in as constant # --replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test index 65ca1b2c1b7..70f57fdf283 100644 --- a/mysql-test/t/user_var.test +++ b/mysql-test/t/user_var.test @@ -210,7 +210,10 @@ create table t1(f1 int, f2 int); insert into t1 values (1,2),(2,3),(3,1); select @var:=f2 from t1 group by f1 order by f2 desc limit 1; select @var; -drop table t1; +create table t2 as select @var:=f2 from t1 group by f1 order by f2 desc limit 1; +select * from t2; +select @var; +drop table t1,t2; # # Bug#19024 - SHOW COUNT(*) WARNINGS not return Errors diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 7175db1db4e..10ebfcfb697 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -2906,6 +2906,7 @@ DROP FUNCTION f1; DROP VIEW v1; DROP TABLE t1; +# # Bug #16813 (WITH CHECK OPTION doesn't work with UPDATE) # CREATE TABLE t1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, val INT UNSIGNED NOT NULL); @@ -2919,7 +2920,6 @@ UPDATE v1 SET val=6 WHERE id=2; DROP VIEW v1; DROP TABLE t1; - # # BUG#22584: last_insert_id not updated after inserting a record # through a updatable view @@ -2957,6 +2957,20 @@ SELECT * FROM t1; DROP VIEW v1, v2; DROP TABLE t1; +# +# BUG#24293: '\Z' token is not handled correctly in views +# + +--disable_warnings +DROP VIEW IF EXISTS v1; +--enable_warnings + +CREATE VIEW v1 AS SELECT 'The\ZEnd'; +SELECT * FROM v1; + +SHOW CREATE VIEW v1; + +DROP VIEW v1; --echo End of 5.0 tests. |